第一、sql中写事物
begin try
begin transaction
insert into shiwu (asd) values ('aasdasda');
commit transaction
end try
begin catch
select ERROR_NUMBER() as errornumber
rollback transaction
end catch
第二、c#中执行事物
SqlParameter[] paras =
new
SqlParameter[] { };
paras
=
list.ToArray();
Baitour.Utility.DBHelper db
=
Baitour.Utility.DBHelper.CreateMapping();
DbTransaction dbTran
=
db.CreateTran();
try
{
//
在这里将插入得到的子订单号返回
object
obj =
db.ExecuteScalar(sql.ToString(), paras, dbTran);
fsId
=
obj.ToString();
//
int index = db.ExecuteNonQuery(sql.ToString(), paras, dbTran);
if
(!
string
.IsNullOrWhiteSpace(fsId))
{
dbTran.Commit();
flag
=
true
;
}
}
catch
(Exception ex)
{
flag
=
false
;
dbTran.Rollback();
//
throw;
}
finally
{
db.CloseConn();
}
dbHelper
using
System;
using
System.Collections.Specialized;
using
System.Configuration;
using
System.Data;
using
System.Data.Common;
using
System.Data.SqlClient;
using
Microsoft.Practices.EnterpriseLibrary.Data;
using
Microsoft.Practices.EnterpriseLibrary.Data.Sql;
/*
* 作 者: zjf
* 创建时间: 2013-1-29 10:20:53
* 说明:
*/
namespace
Baitour.Utility
{
///
<summary>
///
数据库访问类
///
</summary>
public
sealed
class
DBHelper
{
public
static
int
ConnIndex =
0
;
public
static
DBHelper CreateMapping(
string
connStr =
""
)
{
DBHelper db
=
new
DBHelper(connStr);
return
db;
}
///
<summary>
///
增删改的数据库连接字符串
///
</summary>
string
conString =
""
;
///
<summary>
///
查询的数据库连接字符串
///
</summary>
string
queryConString =
""
;
DBHelper(
string
connStr =
""
)
{
if
(
string
.IsNullOrWhiteSpace(connStr))
{
conString
= ConfigurationManager.ConnectionStrings[
"
DBConfig
"
].ToString();
//
第一个链接字符串是ConfigurationManager.ConnectionStrings[0].Name LocalSqlServer不知道是谁，所以要从第二个取
//
如果连接字符串的数量和当前索引+1相同 则从第一个索引开始取
NameValueCollection connStrs =
new
NameValueCollection();
int
j =
0
;
for
(
int
i =
0
; i < ConfigurationManager.ConnectionStrings.Count; i++
)
{
if
(ConfigurationManager.ConnectionStrings[i].Name.StartsWith(
"
QueryDB
"
))
{
connStrs.Add(j.ToString(), ConfigurationManager.ConnectionStrings[i].ToString());
j
++
;
}
}
if
(connStrs.Count >
0
)
{
if
(connStrs.Count ==
ConnIndex)
{
ConnIndex
=
0
;
}
queryConString
=
connStrs[ConnIndex].ToString();
ConnIndex
++
;
}
else
{
queryConString
=
conString;
}
}
else
{
conString
=
connStr;
queryConString
=
connStr;
}
DBMapping
=
new
SqlDatabase(conString);
QueryDBMapping
=
new
SqlDatabase(queryConString);
}
#region
变量or属性
///
<summary>
///
增删改的企业库访问映射对象
///
</summary>
internal
Database DBMapping {
get
;
set
; }
///
<summary>
///
查询的企业库访问映射对象
///
</summary>
internal
Database QueryDBMapping {
get
;
set
; }
///
<summary>
///
增删改的数据库连接
///
</summary>
internal
DbConnection DBConnMapping {
get
;
set
; }
///
<summary>
///
查询的数据库连接
///
</summary>
internal
DbConnection QueryDBConnMapping {
get
;
set
; }
///
<summary>
///
数据库事务
///
</summary>
internal
DbTransaction DBTranMapping {
get
;
set
; }
#endregion
#region
方法
#region
准备方法
///
<summary>
///
创建数据库连接
///
</summary>
void
CreateDB()
{
DBMapping
=
new
SqlDatabase(conString);
QueryDBMapping
=
new
SqlDatabase(queryConString);
}
///
<summary>
///
创建并打开连接
///
</summary>
void
CreateConn()
{
#region
增删改
if
(DBMapping ==
null
)
{
CreateDB();
}
if
(DBConnMapping ==
null
)
{
DBConnMapping
=
DBMapping.CreateConnection();
DBConnMapping.Open();
//
打开连接
}
//
打开存在的连接
if
(DBConnMapping !=
null
&& DBConnMapping.State !=
ConnectionState.Open)
{
DBConnMapping.Open();
//
打开连接
}
#endregion
#region
查询
if
(QueryDBMapping ==
null
)
{
CreateDB();
}
if
(QueryDBConnMapping ==
null
)
{
QueryDBConnMapping
=
QueryDBMapping.CreateConnection();
QueryDBConnMapping.Open();
//
打开连接
}
//
打开存在的连接
if
(QueryDBConnMapping !=
null
&& QueryDBConnMapping.State !=
ConnectionState.Open)
{
QueryDBConnMapping.Open();
//
打开连接
}
#endregion
}
///
<summary>
///
关闭数据库连接
///
</summary>
public
void
CloseConn()
{
if
(DBConnMapping !=
null
&& DBConnMapping.State ==
ConnectionState.Open)
{
DBConnMapping.Close();
DBConnMapping.Dispose();
}
if
(QueryDBConnMapping !=
null
&& QueryDBConnMapping.State ==
ConnectionState.Open)
{
QueryDBConnMapping.Close();
QueryDBConnMapping.Dispose();
}
}
///
<summary>
///
创建并返回事务
///
</summary>
public
DbTransaction CreateTran()
{
if
(DBMapping ==
null
) { CreateDB(); }
CreateConn();
DBTranMapping
= DBTranMapping ??
DBConnMapping.BeginTransaction();
return
DBTranMapping;
}
///
<summary>
///
创建命令对象
///
</summary>
///
<param name="commandType">
sql语句/存储过程
</param>
///
<param name="commandText">
名称
</param>
///
<param name="commParameters">
sql参数
</param>
///
<param name="isQuery">
是否是创建查询命令
</param>
///
<returns></returns>
DbCommand CreateComm(CommandType commandType,
string
commandText, DbParameter[] commParameters =
null
,
bool
isQuery =
false
)
{
DbCommand command
=
null
;
if
(commandText ==
null
|| commandText.Length ==
0
)
throw
new
ArgumentNullException(
"
sql语句/存储过程为空
"
);
//
创建命令对象
if
(commandType ==
CommandType.StoredProcedure)
command
= isQuery ?
QueryDBMapping.GetStoredProcCommand(commandText) : DBMapping.GetStoredProcCommand(commandText);
else
command
= isQuery ?
QueryDBMapping.GetSqlStringCommand(commandText) : DBMapping.GetSqlStringCommand(commandText);
//
清空参数
command.Parameters.Clear();
//
填充参数
if
(commParameters !=
null
)
{
if
(!
string
.IsNullOrWhiteSpace(ConfigurationManager.AppSettings[
"
DBType
"
]) && ConfigurationManager.AppSettings[
"
DBType
"
].ToLower() ==
"
oracle
"
)
{
foreach
(DbParameter item
in
commParameters)
{
if
(!item.ParameterName.StartsWith(
"
!
"
))
item.ParameterName
=
"
!
"
+
item.ParameterName;
}
}
else
{
foreach
(DbParameter item
in
commParameters)
{
if
(!item.ParameterName.StartsWith(
"
@
"
))
item.ParameterName
=
"
@
"
+
item.ParameterName;
}
}
if
(commParameters.Length ==
1
) { command.Parameters.Add(commParameters[
0
]); }
else
{ command.Parameters.AddRange(commParameters); }
}
return
command;
}
#endregion
#region
执行方法
#region
sql、存储过程
///
<summary>
///
返回首列
///
</summary>
///
<param name="sql"></param>
///
<param name="paras"></param>
///
<returns></returns>
public
object
ExecuteScalar(
string
sql, DbParameter[] paras, CommandType cType =
CommandType.Text)
{
object
obj =
null
;
try
{
DbCommand command
= CreateComm(cType, sql, paras,
true
);
obj
=
DBMapping.ExecuteScalar(command);
}
catch
(Exception ex)
{
}
finally
{
CloseConn();
}
return
obj;
}
///
<summary>
///
返回首列
///
</summary>
///
<param name="sql"></param>
///
<param name="paras"></param>
///
<returns></returns>
public
object
ExecuteScalar(
string
sql, DbParameter[] paras, DbTransaction tran, CommandType cType =
CommandType.Text)
{
object
obj =
null
;
try
{
DbCommand command
=
CreateComm(cType, sql, paras);
obj
=
DBMapping.ExecuteScalar(command, tran);
}
catch
(Exception ex)
{
}
finally
{
}
return
obj;
}
///
<summary>
///
返回reader
///
</summary>
///
<param name="sql"></param>
///
<param name="paras"></param>
///
<returns></returns>
public
IDataReader ExecuteReader(
string
sql, DbParameter[] paras, CommandType cType =
CommandType.Text)
{
try
{
DbCommand command
= CreateComm(cType, sql, paras,
true
);
command.Connection
=
QueryDBMapping.CreateConnection();
command.Connection.Open();
return
command.ExecuteReader(CommandBehavior.CloseConnection);
}
catch
(Exception ex)
{
return
null
;
}
}
///
<summary>
///
返回数据集
///
</summary>
///
<returns></returns>
public
DataSet ExecuteDataSet(
string
sql, DbParameter[] paras, CommandType cType =
CommandType.Text)
{
try
{
DbCommand command
= CreateComm(cType, sql, paras,
true
);
return
QueryDBMapping.ExecuteDataSet(command);
}
catch
(Exception ex)
{
return
null
;
}
finally
{
CloseConn();
}
}
public
DataSet ExecuteDataSet(
string
sql)
{
try
{
DbCommand db
=
CreateComm(CommandType.Text, sql);
return
QueryDBMapping.ExecuteDataSet(CommandType.Text,sql);
}
catch
{
return
null
;
}
finally
{
CloseConn();
}
}
///
<summary>
///
得到影响行数
///
</summary>
///
<returns></returns>
public
int
ExecuteNonQuery(
string
sql, DbParameter[] paras, CommandType cType =
CommandType.Text)
{
try
{
DbCommand command
=
CreateComm(cType, sql, paras);
return
DBMapping.ExecuteNonQuery(command);
}
catch
(Exception ex)
{
return
-
1
;
}
finally
{
CloseConn();
}
}
///
<summary>
///
得到影响行数需要手动关闭连接
///
</summary>
///
<returns></returns>
public
int
ExecuteNonQuery(
string
sql, DbParameter[] paras, DbTransaction tran, CommandType cType =
CommandType.Text)
{
try
{
DbCommand command
=
CreateComm(cType, sql, paras);
return
DBMapping.ExecuteNonQuery(command, tran);
}
catch
(Exception ex)
{
return
-
1
;
}
finally
{
}
}
///
<summary>
///
数据库分页获取DataSet对象
///
</summary>
///
<param name="sTable_Name">
表名/视图名
</param>
///
<param name="sSign_Record">
显示的字段(以,分隔)/*表示全部字段
</param>
///
<param name="sFilter_Condition">
查询条件
</param>
///
<param name="iPage_Size">
每页显示条数
</param>
///
<param name="iPage_Index">
第几页
</param>
///
<param name="sTaxisField">
排序字段(以,分隔)
</param>
///
<param name="iTaxis_Sign">
排序0升序1降序
</param>
///
<param name="iPageCount">
返回总页数
</param>
///
<param name="iiRecord_Count">
返回总记录数
</param>
///
<returns>
DataSet对象
</returns>
public
DataSet ExecutePageDataSet(
string
sTable_Name,
string
sSign_Record,
string
sFilter_Condition,
int
iPage_Size,
int
iPage_Index,
string
sTaxisField,
int
iTaxis_Sign,
out
int
iPageCount,
out
int
iiRecord_Count)
{
DataSet ds
=
new
DataSet();
try
{
SqlParameter[] param
=
new
SqlParameter[]
{
new
SqlParameter(
"
@TableName
"
,sTable_Name),
new
SqlParameter(
"
@Fields
"
,sSign_Record),
new
SqlParameter(
"
@OrderField
"
,sTaxisField),
new
SqlParameter(
"
@Taxis_Sign
"
,iTaxis_Sign),
new
SqlParameter(
"
@sqlWhere
"
,sFilter_Condition),
new
SqlParameter(
"
@pageSize
"
,iPage_Size),
new
SqlParameter(
"
@pageIndex
"
,iPage_Index),
new
SqlParameter(
"
@TotalPage
"
, System.Data.SqlDbType.Int),
new
SqlParameter(
"
@totalCount
"
, System.Data.SqlDbType.Int)
};
param[
7
].Direction =
System.Data.ParameterDirection.Output;
param[
8
].Direction =
System.Data.ParameterDirection.Output;
//
执行
DbCommand command = CreateComm(CommandType.StoredProcedure,
"
up_ProcCustomPage2005
"
, param);
ds
=
QueryDBMapping.ExecuteDataSet(command);
iPageCount
=
0
;
iiRecord_Count
=
0
;
try
{
iPageCount
= Convert.ToInt32(command.Parameters[
"
@TotalPage
"
].Value);
iiRecord_Count
= Convert.ToInt32(command.Parameters[
"
@totalCount
"
].Value);
}
catch
(Exception ex)
{
iPageCount
=
0
;
iiRecord_Count
=
0
;
}
finally
{
command.Parameters.Clear();
}
}
catch
(Exception ex)
{
ds
=
null
;
iPageCount
=
0
;
iiRecord_Count
=
0
;
}
finally
{
}
return
ds;
}
#endregion
#region
不加上查询参数的
///
<summary>
///
返回首列
///
</summary>
///
<param name="sql"></param>
///
<param name="paras"></param>
///
<returns></returns>
public
object
ExecuteScalar(
string
sql)
{
object
obj =
null
;
try
{
DbCommand command
= CreateComm(CommandType.Text, sql,
null
,
true
);
return
DBMapping.ExecuteScalar(command);
}
catch
(Exception ex)
{
}
finally
{
CloseConn();
}
return
obj;
}
///
<summary>
///
返回reader
///
</summary>
///
<returns></returns>
public
IDataReader ExecuteReader(
string
sql)
{
try
{
DbCommand command
= CreateComm(CommandType.Text, sql,
null
,
true
);
return
QueryDBMapping.ExecuteReader(command);
}
catch
(Exception ex)
{
return
null
;
}
}
///
<summary>
///
得到影响行数
///
</summary>
///
<returns></returns>
public
int
ExecuteNonQuery(
string
sql)
{
try
{
DbCommand command
= CreateComm(CommandType.Text, sql,
null
,
true
);
return
DBMapping.ExecuteNonQuery(command);
}
catch
(Exception ex)
{
throw
;
}
}
#endregion
#endregion
#endregion
}
}
