Helper
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();
}
}
///
<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
}
}
?
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();
}
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
