using System;
using System
.Collections;
using System
.Collections
.Specialized;
using System
.Data;
using System
.Data
.SqlClient;
using System
.Configuration;
using System
.Data
.Common;
using System
.Collections
.Generic;
namespace Common
{
/// <summary>
/// 資料訪問抽象基礎類
/// Copyright (C) 2004-2008 By LiTianPing
/// </summary>
public abstract class DbHelper
SQL
{
//資料庫連接字串(web
.config來配置)，可以動態更改
connectionString支援多資料庫
.
public static
string connectionString = PubConstant
.ConnectionString;
public DbHelper
SQL()
{
}
#region 公用方法
/// <summary>
/// 判斷是否存在某表的某個欄位
/// </summary>
/// <param name="tableName">表名稱</param>
/// <param name="columnName">列名稱</param>
/// <returns>是否存在</returns>
public static bool ColumnExists(
string tableName,
string columnName)
{
string sql = "select count(1) from syscolumns where [id]=object_id('" + tableName + "') and [name]='" + columnName + "'";
object res = GetSingle(
sql);
if (res == null)
{
return false;
}
return Convert
.ToInt32(res) > 0;
}
public static int GetMaxID(
string FieldName,
string TableName)
{
string str
sql = "select max(" + FieldName + ")+1 from " + TableName;
object obj = GetSingle(str
sql);
if (obj == null)
{
return 1;
}
else
{
return int
.Parse(obj
.ToString());
}
}
public static bool Exists(
string str
Sql)
{
object obj = GetSingle(str
Sql);
int cmdresult;
if ((Object
.Equals(obj, null)) || (Object
.Equals(obj, System
.DBNull
.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int
.Parse(obj
.ToString());
}
if (cmdresult == 0)
{
return false;
}
else
{
return true;
}
}
/// <summary>
/// 表是否存在
/// </summary>
/// <param name="TableName"></param>
/// <returns></returns>
public static bool TabExists(
string TableName)
{
string str
sql = "select count(*) from sysobjects where id = object_id(N'[" + TableName + "]') and OBJECTPROPERTY(id, N'Is
UserTable') = 1";
object obj = GetSingle(str
sql);
int cmdresult;
if ((Object
.Equals(obj, null)) || (Object
.Equals(obj, System
.DBNull
.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int
.Parse(obj
.ToString());
}
if (cmdresult == 0)
{
return false;
}
else
{
return true;
}
}
public static bool Exists(
string str
Sql, params
SqlParameter[] cmdParms)
{
object obj = GetSingle(str
Sql, cmdParms);
int cmdresult;
if ((Object
.Equals(obj, null)) || (Object
.Equals(obj, System
.DBNull
.Value)))
{
cmdresult = 0;
}
else
{
cmdresult = int
.Parse(obj
.ToString());
}
if (cmdresult == 0)
{
return false;
}
else
{
return true;
}
}
#endregion
#region 執行簡單
SQL語句
/// <summary>
/// 執行
SQL語句，返回影響的記錄數
/// </summary>
/// <param name="
SQLString">
SQL語句</param>
/// <returns>影響的記錄數</returns>
public static int Execute
Sql(
string SQLString)
{
using (
SqlConnection connection = new
SqlConnection(
connectionString))
{
using (
SqlCommand cmd = new
SqlCommand(
SQLString,
connection))
{
try
{
connection.Open();
int rows = cmd
.ExecuteNonQuery();
return rows;
}
catch (System
.Data
.SqlClient
.SqlException e)
{
connection.Close();
throw e;
}
}
}
}
public static int Execute
SqlByTime(
string SQLString, int Times)
{
using (
SqlConnection connection = new
SqlConnection(
connectionString))
{
using (
SqlCommand cmd = new
SqlCommand(
SQLString,
connection))
{
try
{
connection.Open();
cmd
.CommandTimeout = Times;
int rows = cmd
.ExecuteNonQuery();
return rows;
}
catch (System
.Data
.SqlClient
.SqlException e)
{
connection.Close();
throw e;
}
}
}
}
/// <summary>
/// 執行多條
SQL語句，實現資料庫事務。
/// </summary>
/// <param name="
SQLStringList">多條
SQL語句</param>
public static int Execute
SqlTran(List<
String>
SQLStringList)
{
using (
SqlConnection conn = new
SqlConnection(
connectionString))
{
conn
.Open();
SqlCommand cmd = new
SqlCommand();
cmd
.Connection = conn;
SqlTransaction tx = conn
.BeginTransaction();
cmd
.Transaction = tx;
try
{
int count = 0;
for (int n = 0; n <
SQLStringList
.Count; n++)
{
string str
sql =
SQLStringList[n];
if (str
sql.Trim()
.Length > 1)
{
cmd
.CommandText = str
sql;
count += cmd
.ExecuteNonQuery();
}
}
tx
.Commit();
return count;
}
catch
{
tx
.Rollback();
return 0;
}
}
}
/// <summary>
/// 執行帶一個存儲過程參數的的
SQL語句。
/// </summary>
/// <param name="
SQLString">
SQL語句</param>
/// <param name="content">參數內容,比如一個欄位是格式複雜的文章，有特殊符號，可以通過這個方式添加</param>
/// <returns>影響的記錄數</returns>
public static int Execute
Sql(
string SQLString,
string content)
{
using (
SqlConnection connection = new
SqlConnection(
connectionString))
{
SqlCommand cmd = new
SqlCommand(
SQLString,
connection);
System
.Data
.SqlClient
.SqlParameter myParameter = new System
.Data
.SqlClient
.SqlParameter("@content",
SqlDbType
.NText);
myParameter
.Value = content;
cmd
.Parameters
.Add(myParameter);
try
{
connection.Open();
int rows = cmd
.ExecuteNonQuery();
return rows;
}
catch (System
.Data
.SqlClient
.SqlException e)
{
throw e;
}
finally
{
cmd
.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 執行帶一個存儲過程參數的的
SQL語句。
/// </summary>
/// <param name="
SQLString">
SQL語句</param>
/// <param name="content">參數內容,比如一個欄位是格式複雜的文章，有特殊符號，可以通過這個方式添加</param>
/// <returns>影響的記錄數</returns>
public static object Execute
SqlGet(
string SQLString,
string content)
{
using (
SqlConnection connection = new
SqlConnection(
connectionString))
{
SqlCommand cmd = new
SqlCommand(
SQLString,
connection);
System
.Data
.SqlClient
.SqlParameter myParameter = new System
.Data
.SqlClient
.SqlParameter("@content",
SqlDbType
.NText);
myParameter
.Value = content;
cmd
.Parameters
.Add(myParameter);
try
{
connection.Open();
object obj = cmd
.ExecuteScalar();
if ((Object
.Equals(obj, null)) || (Object
.Equals(obj, System
.DBNull
.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System
.Data
.SqlClient
.SqlException e)
{
throw e;
}
finally
{
cmd
.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 向資料庫裡插入圖像格式的欄位(和上面情況類似的另一種實例)
/// </summary>
/// <param name="str
SQL">
SQL語句</param>
/// <param name="fs">圖像位元組,資料庫的欄位類型為image的情況</param>
/// <returns>影響的記錄數</returns>
public static int Execute
SqlInsertImg(
string str
SQL, byte[] fs)
{
using (
SqlConnection connection = new
SqlConnection(
connectionString))
{
SqlCommand cmd = new
SqlCommand(str
SQL,
connection);
System
.Data
.SqlClient
.SqlParameter myParameter = new System
.Data
.SqlClient
.SqlParameter("@fs",
SqlDbType
.Image);
myParameter
.Value = fs;
cmd
.Parameters
.Add(myParameter);
try
{
connection.Open();
int rows = cmd
.ExecuteNonQuery();
return rows;
}
catch (System
.Data
.SqlClient
.SqlException e)
{
throw e;
}
finally
{
cmd
.Dispose();
connection.Close();
}
}
}
/// <summary>
/// 執行一條計算查詢結果語句，返回查詢結果（object）。
/// </summary>
/// <param name="
SQLString">計算查詢結果語句</param>
/// <returns>查詢結果（object）</returns>
public static object GetSingle(
string SQLString)
{
using (
SqlConnection connection = new
SqlConnection(
connectionString))
{
using (
SqlCommand cmd = new
SqlCommand(
SQLString,
connection))
{
try
{
connection.Open();
object obj = cmd
.ExecuteScalar();
if ((Object
.Equals(obj, null)) || (Object
.Equals(obj, System
.DBNull
.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System
.Data
.SqlClient
.SqlException e)
{
connection.Close();
throw e;
}
}
}
}
public static object GetSingle(
string SQLString, int Times)
{
using (
SqlConnection connection = new
SqlConnection(
connectionString))
{
using (
SqlCommand cmd = new
SqlCommand(
SQLString,
connection))
{
try
{
connection.Open();
cmd
.CommandTimeout = Times;
object obj = cmd
.ExecuteScalar();
if ((Object
.Equals(obj, null)) || (Object
.Equals(obj, System
.DBNull
.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System
.Data
.SqlClient
.SqlException e)
{
connection.Close();
throw e;
}
}
}
}
/// <summary>
/// 執行查詢語句，返回
SqlDataReader ( 注意：調用該方法後，一定要對
SqlDataReader進行Close )
/// </summary>
/// <param name="str
SQL">查詢語句</param>
/// <returns>
SqlDataReader</returns>
public static
SqlDataReader ExecuteReader(
string str
SQL)
{
SqlConnection connection = new
SqlConnection(
connectionString);
SqlCommand cmd = new
SqlCommand(str
SQL,
connection);
try
{
connection.Open();
SqlDataReader myReader = cmd
.ExecuteReader(CommandBehavior
.Close
Connection);
return myReader;
}
catch (System
.Data
.SqlClient
.SqlException e)
{
throw e;
}
}
/// <summary>
/// 執行查詢語句，返回DataSet
/// </summary>
/// <param name="
SQLString">查詢語句</param>
/// <returns>DataSet</returns>
public static DataSet Query(
string SQLString)
{
using (
SqlConnection connection = new
SqlConnection(
connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
SqlDataAdapter command = new
SqlDataAdapter(
SQLString,
connection);
command
.Fill(ds, "ds");
}
catch (System
.Data
.SqlClient
.SqlException ex)
{
throw new Exception(ex
.Message);
}
return ds;
}
}
/// <summary>
/// 執行查詢語句，返回pageDataSet
/// </summary>
/// <param name="
SQLString">查詢語句</param>
/// <returns>DataSet</returns>
public static DataSet pageQuery(
string SQLString, int PageIndex, int PageSize)
{
using (
SqlConnection connection = new
SqlConnection(
connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
SqlDataAdapter command = new
SqlDataAdapter(
SQLString,
connection);
command
.Fill(ds, PageIndex, PageSize, "ds");
}
catch (System
.Data
.SqlClient
.SqlException ex)
{
throw new Exception(ex
.Message);
}
return ds;
}
}
public static DataSet Query(
string SQLString, int Times)
{
using (
SqlConnection connection = new
SqlConnection(
connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
SqlDataAdapter command = new
SqlDataAdapter(
SQLString,
connection);
command
.SelectCommand
.CommandTimeout = Times;
command
.Fill(ds, "ds");
}
catch (System
.Data
.SqlClient
.SqlException ex)
{
throw new Exception(ex
.Message);
}
return ds;
}
}
#endregion
#region 執行帶參數的
SQL語句
/// <summary>
/// 執行
SQL語句，返回影響的記錄數
/// </summary>
/// <param name="
SQLString">
SQL語句</param>
/// <returns>影響的記錄數</returns>
public static int Execute
Sql(
string SQLString, params
SqlParameter[] cmdParms)
{
using (
SqlConnection connection = new
SqlConnection(
connectionString))
{
using (
SqlCommand cmd = new
SqlCommand())
{
try
{
PrepareCommand(cmd,
connection, null,
SQLString, cmdParms);
int rows = cmd
.ExecuteNonQuery();
cmd
.Parameters
.Clear();
return rows;
}
catch (System
.Data
.SqlClient
.SqlException e)
{
throw e;
}
}
}
}
/// <summary>
/// 執行多條
SQL語句，實現資料庫事務。
/// </summary>
/// <param name="
SQLStringList">
SQL語句的雜湊表（key為
sql語句，value是該語句的
SqlParameter[]）</param>
public static void Execute
SqlTran(Hashtable
SQLStringList)
{
using (
SqlConnection conn = new
SqlConnection(
connectionString))
{
conn
.Open();
using (
SqlTransaction trans = conn
.BeginTransaction())
{
SqlCommand cmd = new
SqlCommand();
try
{
//迴圈
foreach (DictionaryEntry myDE in
SQLStringList)
{
string cmdText = myDE
.Key
.ToString();
SqlParameter[] cmdParms = (
SqlParameter[])myDE
.Value;
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
int val = cmd
.ExecuteNonQuery();
cmd
.Parameters
.Clear();
}
trans
.Commit();
}
catch
{
trans
.Rollback();
throw;
}
}
}
}
/// <summary>
/// 執行多條
SQL語句，實現資料庫事務。
/// </summary>
/// <param name="
SQLStringList">
SQL語句的雜湊表（key為
sql語句，value是該語句的
SqlParameter[]）</param>
public static int Execute
SqlTran(System
.Collections
.Generic
.List<CommandInfo> cmdList)
{
using (
SqlConnection conn = new
SqlConnection(
connectionString))
{
conn
.Open();
using (
SqlTransaction trans = conn
.BeginTransaction())
{
SqlCommand cmd = new
SqlCommand();
try
{
int count = 0;
//迴圈
foreach (CommandInfo myDE in cmdList)
{
string cmdText = myDE
.CommandText;
SqlParameter[] cmdParms = (
SqlParameter[])myDE
.Parameters;
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
if (myDE
.EffentNextType == EffentNextType
.WhenHaveContine || myDE
.EffentNextType == EffentNextType
.WhenNoHaveContine)
{
if (myDE
.CommandText
.ToLower()
.IndexOf("count(") == -1)
{
trans
.Rollback();
return 0;
}
object obj = cmd
.ExecuteScalar();
bool isHave = false;
if (obj == null && obj == DBNull
.Value)
{
isHave = false;
}
isHave = Convert
.ToInt32(obj) > 0;
if (myDE
.EffentNextType == EffentNextType
.WhenHaveContine && !isHave)
{
trans
.Rollback();
return 0;
}
if (myDE
.EffentNextType == EffentNextType
.WhenNoHaveContine && isHave)
{
trans
.Rollback();
return 0;
}
continue;
}
int val = cmd
.ExecuteNonQuery();
count += val;
if (myDE
.EffentNextType == EffentNextType
.ExcuteEffectRows && val == 0)
{
trans
.Rollback();
return 0;
}
cmd
.Parameters
.Clear();
}
trans
.Commit();
return count;
}
catch
{
trans
.Rollback();
throw;
}
}
}
}
/// <summary>
/// 執行多條
SQL語句，實現資料庫事務。
/// </summary>
/// <param name="
SQLStringList">
SQL語句的雜湊表（key為
sql語句，value是該語句的
SqlParameter[]）</param>
public static void Execute
SqlTranWithIndentity(System
.Collections
.Generic
.List<CommandInfo>
SQLStringList)
{
using (
SqlConnection conn = new
SqlConnection(
connectionString))
{
conn
.Open();
using (
SqlTransaction trans = conn
.BeginTransaction())
{
SqlCommand cmd = new
SqlCommand();
try
{
int indentity = 0;
//迴圈
foreach (CommandInfo myDE in
SQLStringList)
{
string cmdText = myDE
.CommandText;
SqlParameter[] cmdParms = (
SqlParameter[])myDE
.Parameters;
foreach (
SqlParameter q in cmdParms)
{
if (q
.Direction == ParameterDirection
.Inpu
tOutput)
{
q
.Value = indentity;
}
}
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
int val = cmd
.ExecuteNonQuery();
foreach (
SqlParameter q in cmdParms)
{
if (q
.Direction == ParameterDirection
.Output)
{
indentity = Convert
.ToInt32(q
.Value);
}
}
cmd
.Parameters
.Clear();
}
trans
.Commit();
}
catch
{
trans
.Rollback();
throw;
}
}
}
}
/// <summary>
/// 執行多條
SQL語句，實現資料庫事務。
/// </summary>
/// <param name="
SQLStringList">
SQL語句的雜湊表（key為
sql語句，value是該語句的
SqlParameter[]）</param>
public static void Execute
SqlTranWithIndentity(Hashtable
SQLStringList)
{
using (
SqlConnection conn = new
SqlConnection(
connectionString))
{
conn
.Open();
using (
SqlTransaction trans = conn
.BeginTransaction())
{
SqlCommand cmd = new
SqlCommand();
try
{
int indentity = 0;
//迴圈
foreach (DictionaryEntry myDE in
SQLStringList)
{
string cmdText = myDE
.Key
.ToString();
SqlParameter[] cmdParms = (
SqlParameter[])myDE
.Value;
foreach (
SqlParameter q in cmdParms)
{
if (q
.Direction == ParameterDirection
.Inpu
tOutput)
{
q
.Value = indentity;
}
}
PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
int val = cmd
.ExecuteNonQuery();
foreach (
SqlParameter q in cmdParms)
{
if (q
.Direction == ParameterDirection
.Output)
{
indentity = Convert
.ToInt32(q
.Value);
}
}
cmd
.Parameters
.Clear();
}
trans
.Commit();
}
catch
{
trans
.Rollback();
throw;
}
}
}
}
/// <summary>
/// 執行一條計算查詢結果語句，返回查詢結果（object）。
/// </summary>
/// <param name="
SQLString">計算查詢結果語句</param>
/// <returns>查詢結果（object）</returns>
public static object GetSingle(
string SQLString, params
SqlParameter[] cmdParms)
{
using (
SqlConnection connection = new
SqlConnection(
connectionString))
{
using (
SqlCommand cmd = new
SqlCommand())
{
try
{
PrepareCommand(cmd,
connection, null,
SQLString, cmdParms);
object obj = cmd
.ExecuteScalar();
cmd
.Parameters
.Clear();
if ((Object
.Equals(obj, null)) || (Object
.Equals(obj, System
.DBNull
.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System
.Data
.SqlClient
.SqlException e)
{
throw e;
}
}
}
}
/// <summary>
/// 執行查詢語句，返回
SqlDataReader ( 注意：調用該方法後，一定要對
SqlDataReader進行Close )
/// </summary>
/// <param name="str
SQL">查詢語句</param>
/// <returns>
SqlDataReader</returns>
public static
SqlDataReader ExecuteReader(
string SQLString, params
SqlParameter[] cmdParms)
{
SqlConnection connection = new
SqlConnection(
connectionString);
SqlCommand cmd = new
SqlCommand();
try
{
PrepareCommand(cmd,
connection, null,
SQLString, cmdParms);
SqlDataReader myReader = cmd
.ExecuteReader(CommandBehavior
.Close
Connection);
cmd
.Parameters
.Clear();
return myReader;
}
catch (System
.Data
.SqlClient
.SqlException e)
{
// 如果連線已開啟則關閉，避免資源洩漏
if (
connection.State !=
ConnectionState
.Closed)
connection.Close();
throw; // 讓例外拋回給呼叫端處理
}
}
/// <summary>
/// 執行查詢語句，返回DataSet
/// </summary>
/// <param name="
SQLString">查詢語句</param>
/// <returns>DataSet</returns>
public static DataSet Query(
string SQLString, params
SqlParameter[] cmdParms)
{
using (
SqlConnection connection = new
SqlConnection(
connectionString))
{
SqlCommand cmd = new
SqlCommand();
PrepareCommand(cmd,
connection, null,
SQLString, cmdParms);
using (
SqlDataAdapter da = new
SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
try
{
da
.Fill(ds, "ds");
cmd
.Parameters
.Clear();
}
catch (System
.Data
.SqlClient
.SqlException ex)
{
throw new Exception(ex
.Message);
}
return ds;
}
}
}
/// <summary>
/// 執行查詢語句，返回DataSet
/// </summary>
/// <param name="
SQLString">查詢語句</param>
/// <returns>DataSet</returns>
public static DataSet PageQuery(
string SQLString, int PageIndex, int PageSize, params
SqlParameter[] cmdParms)
{
using (
SqlConnection connection = new
SqlConnection(
connectionString))
{
SqlCommand cmd = new
SqlCommand();
PrepareCommand(cmd,
connection, null,
SQLString, cmdParms);
using (
SqlDataAdapter da = new
SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
try
{
da
.Fill(ds, PageIndex, PageSize, "ds");
cmd
.Parameters
.Clear();
}
catch (System
.Data
.SqlClient
.SqlException ex)
{
throw new Exception(ex
.Message);
}
return ds;
}
}
}
private static void PrepareCommand(
SqlCommand cmd,
SqlConnection conn,
SqlTransaction trans,
string cmdText,
SqlParameter[] cmdParms)
{
if (conn
.State !=
ConnectionState
.Open)
conn
.Open();
cmd
.Connection = conn;
cmd
.CommandText = cmdText;
if (trans != null)
cmd
.Transaction = trans;
cmd
.CommandType = CommandType
.Text;//cmdType;
if (cmdParms != null)
{
foreach (
SqlParameter parameter in cmdParms)
{
if ((parameter
.Direction == ParameterDirection
.Inpu
tOutput || parameter
.Direction == ParameterDirection
.Input) &&
(parameter
.Value == null))
{
parameter
.Value = DBNull
.Value;
}
cmd
.Parameters
.Add(parameter);
}
}
}
#endregion
#region 存儲過程操作
/// <summary>
/// 執行存儲過程，返回
SqlDataReader ( 注意：調用該方法後，一定要對
SqlDataReader進行Close )
/// </summary>
/// <param name="s
toredProcName">存儲過程名</param>
/// <param name="parameters">存儲過程參數</param>
/// <returns>
SqlDataReader</returns>
public static
SqlDataReader RunProcedure(
string s
toredProcName, IDataParameter[] parameters)
{
SqlConnection connection = new
SqlConnection(
connectionString);
SqlDataReader returnReader;
connection.Open();
SqlCommand command = BuildQueryCommand(
connection, s
toredProcName, parameters);
command
.CommandType = CommandType
.S
toredProcedure;
returnReader = command
.ExecuteReader(CommandBehavior
.Close
Connection);
return returnReader;
}
/// <summary>
/// 執行存儲過程
/// </summary>
/// <param name="s
toredProcName">存儲過程名</param>
/// <param name="parameters">存儲過程參數</param>
/// <param name="tableName">DataSet結果中的表名</param>
/// <returns>DataSet</returns>
public static DataSet RunProcedure(
string s
toredProcName, IDataParameter[] parameters,
string tableName)
{
using (
SqlConnection connection = new
SqlConnection(
connectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
SqlDataAdapter
sqlDA = new
SqlDataAdapter();
sqlDA
.SelectCommand = BuildQueryCommand(
connection, s
toredProcName, parameters);
sqlDA
.Fill(dataSet, tableName);
connection.Close();
return dataSet;
}
}
public static DataSet RunProcedure(
string s
toredProcName, IDataParameter[] parameters,
string tableName, int Times)
{
using (
SqlConnection connection = new
SqlConnection(
connectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
SqlDataAdapter
sqlDA = new
SqlDataAdapter();
sqlDA
.SelectCommand = BuildQueryCommand(
connection, s
toredProcName, parameters);
sqlDA
.SelectCommand
.CommandTimeout = Times;
sqlDA
.Fill(dataSet, tableName);
connection.Close();
return dataSet;
}
}
/// <summary>
/// 構建
SqlCommand 物件(用來返回一個結果集，而不是一個整數值)
/// </summary>
/// <param name="
connection">資料庫連接</param>
/// <param name="s
toredProcName">存儲過程名</param>
/// <param name="parameters">存儲過程參數</param>
/// <returns>
SqlCommand</returns>
private static
SqlCommand BuildQueryCommand(
SqlConnection connection,
string s
toredProcName, IDataParameter[] parameters)
{
SqlCommand command = new
SqlCommand(s
toredProcName,
connection);
command
.CommandType = CommandType
.S
toredProcedure;
foreach (
SqlParameter parameter in parameters)
{
if (parameter != null)
{
// 檢查未分配值的輸出參數,將其分配以DBNull
.Value
.
if ((parameter
.Direction == ParameterDirection
.Inpu
tOutput || parameter
.Direction == ParameterDirection
.Input) &&
(parameter
.Value == null))
{
parameter
.Value = DBNull
.Value;
}
command
.Parameters
.Add(parameter);
}
}
return command;
}
/// <summary>
/// 執行存儲過程，返回影響的行數
/// </summary>
/// <param name="s
toredProcName">存儲過程名</param>
/// <param name="parameters">存儲過程參數</param>
/// <param name="rowsAffected">影響的行數</param>
/// <returns></returns>
public static int RunProcedure(
string s
toredProcName, IDataParameter[] parameters, out int rowsAffected)
{
using (
SqlConnection connection = new
SqlConnection(
connectionString))
{
int result;
connection.Open();
SqlCommand command = BuildIntCommand(
connection, s
toredProcName, parameters);
rowsAffected = command
.ExecuteNonQuery();
result = (int)command
.Parameters["ReturnValue"]
.Value;
//
Connection.Close();
return result;
}
}
/// <summary>
/// 創建
SqlCommand 物件實例(用來返回一個整數值)
/// </summary>
/// <param name="s
toredProcName">存儲過程名</param>
/// <param name="parameters">存儲過程參數</param>
/// <returns>
SqlCommand 物件實例</returns>
private static
SqlCommand BuildIntCommand(
SqlConnection connection,
string s
toredProcName, IDataParameter[] parameters)
{
SqlCommand command = BuildQueryCommand(
connection, s
toredProcName, parameters);
command
.Parameters
.Add(new
SqlParameter("ReturnValue",
SqlDbType
.Int, 4, ParameterDirection
.ReturnValue,
false, 0, 0,
string.Empty, DataRowVersion
.Default, null));
return command;
}
#endregion
}
}
優化
本文详细介绍了 SqlConnection 的 ConnectionString 中用于连接池的有效参数。同时，提到了 .NET Framework 数据提供程序针对 SQL Server 的连接池机制。
最低0.47元/天 解锁文章
扫一扫
493
到【灌水乐园】发言
