此SqlHelper.cs已經把ADO.net存取資料庫技術都封裝完整，算是Data Access Layer的東西
它簡化ADO.net複雜語法、讓人可以專注寫SQL指令來存取資料庫
雖然目前最潮的存取資料庫技術是Entity Framework，不過仍然有些維護案的資料存取層寫的眼花繚亂又很抽象
這時候我就會拿出這套類別，直搗黃龍地撈資料XD
程式碼已在我的工作專案上運行2年左右，該調整的都調過了
丟上來分享兼備份
前置作業
在專案裡的App.config或Web.config裡(沒有的話要先對專案加入新增項目→找組態檔)
先配置連線字串
<configuration>
<connectionStrings>
<!--給SqlHelper用的連線-->
<add name="dbConn" connectionString="Data Source=.\sqlexpress2012;Initial Catalog=TestDB;Integrated Security=True;MultipleActiveResultSets=True;Max Pool Size=1000;" />
</connectionStrings>
</configuration>
接著專案加入參考：System.Configuration，如果存取資料庫是Oracle的話，也要加入System.Data.OracleClient的參考
然後新增一個類別檔名為SqlHelper.cs，把以下程式碼都貼上去
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
/*引用*/
using System.Configuration;
using System.Data.Common;
using System.Data.Odbc;
using System.Data.OleDb;
//using System.Data.OracleClient;
//↑須另外加入System.Data.OracleClient參考，才能using此命名空間，不過通常很少用OracleClient，所以把它註解掉
namespace SystemDAO
{
/// <summary>
/// 此類別為抽象類別，不允許被new，在使用時直接調用即可
/// </summary>
public abstract class SqlHelper
{
/// <summary>
/// SqlHelper預設資料庫連線字串
/// </summary>
public static readonly string connectionString = ConfigurationManager.ConnectionStrings["dbConn"].ConnectionString.Trim();
#region 資料提供者
/// <summary>
/// 資料提供者，依據目標資料庫不同，須修改不同類型的DbProviderFactory
/// </summary>
private static readonly DbProviderFactory dbProviderFactory = SqlClientFactory.Instance;
//private static readonly DbProviderFactory dbProviderFactory = OdbcFactory.Instance;
//private static readonly DbProviderFactory dbProviderFactory = OleDbFactory.Instance;
//private static readonly DbProviderFactory dbProviderFactory = OracleClientFactory.Instance;
#endregion
/// <summary>
/// 為執行命令準備參數
/// </summary>
/// <param name="cmd">DbCommand 命令</param>
/// <param name="conn">資料庫連線</param>
/// <param name="trans">交易處理</param>
/// <param name="cmdType">DbCommand類型 (CommandType.StoredProcedure或CommandType.Text)</param>
/// <param name="cmdText">DbCommand的T-SQL语句 例如：Select * from Products</param>
/// <param name="cmdParms">使用到的參數集合</param>
private static void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, CommandType cmdType, string cmdText,
params DbParameter[] cmdParms)
{
//判斷資料庫連線狀態
if (conn.State != ConnectionState.Open) { conn.Open(); }
//判斷是否需要交易處理
if (trans != null) { cmd.Transaction = trans; }
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
if (cmdParms != null && cmdParms.Length > 0)
{
cmd.Parameters.Clear();//清除參數集合(防呆)
cmd.Parameters.AddRange(cmdParms);
}
}
#region 連線資料庫存取
#region ExecuteNonQuery 異動資料
/// <summary>
/// 執行新增、修改、刪除指令，透過指定連接字串。
/// </summary>
/// <param name="connectionString">工程師自行指定DB連線字串</param>
/// <param name="cmdType">DbCommand類型 (CommandType.StoredProcedure或CommandType.Text)</param>
/// <param name="cmdText">預存程式名稱 或 T-SQL 语句</param>
/// <param name="commandParameters">使用到的參數集合</param>
/// <returns>受影響的資料筆數</returns>
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
{
using (DbConnection conn = dbProviderFactory.CreateConnection())
{
conn.ConnectionString = connectionString;
DbCommand cmd = conn.CreateCommand();
//通過PrePareCommand方法將參數逐個加入到DbCommand的参數集合中
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
//清空SqlCommand中的参数列表
cmd.Parameters.Clear();
return val;
}//end using
}
/// <summary>
/// 執行新增、修改、刪除指令，使用SqlHelper預設連接字串。
/// </summary>
/// <param name="cmdType">DbCommand類型 (CommandType.StoredProcedure或CommandType.Text)</param>
/// <param name="cmdText">預存程式名稱 或 T-SQL 语句</param>
/// <param name="commandParameters">使用到的參數集合</param>
/// <returns>受影響的資料筆數</returns>
public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
{
return ExecuteNonQuery(connectionString, cmdType, cmdText, commandParameters);
}
/// <summary>
/// 執行新增、修改、刪除指令，使用SqlHelper預設連接字串、CommandType.Text
/// </summary>
/// <param name="cmdText">CommandType.Text的T-Sql語句</param>
/// <param name="commandParameters">使用到的參數集合</param>
/// <returns>受影響的資料筆數</returns>
public static int ExecuteNonQueryText(string cmdText, params DbParameter[] commandParameters)
{
return ExecuteNonQuery(CommandType.Text, cmdText, commandParameters);
}
#endregion
#region ExecuteScalar
/// <summary>
/// 取得第一行第一列的資料，通常使用在Select Count(*) From TableName 有聚合函數的Select指令
/// 工程師自行指定DB連線字串
/// </summary>
/// <param name="connectionString">工程師自行指定DB連線字串</param>
/// <param name="cmdType">DbCommand類型 (CommandType.StoredProcedure或CommandType.Text)</param>
/// <param name="cmdText">預存程式名稱 或 T-SQL 語句</param>
/// <param name="commandParameters">使用到的參數集合</param>
/// <returns>回傳第一行第一列的資料，型別不確定</returns>
public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
{
using (DbConnection conn = dbProviderFactory.CreateConnection())
{
conn.ConnectionString = connectionString;//連線字串
DbCommand cmd = conn.CreateCommand();
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
//為了讓同一份Parameters的參考可以重覆給其他指令使用，所以清除
cmd.Parameters.Clear();//清除參數集合
conn.Close();
return val;
}
}
/// <summary>
/// 取得第一行第一列的資料，通常使用在Select Count(*) From TableName 有聚合函數的Select指令
/// 使用SqlHelper預設DB連線字串
/// </summary>
/// <param name="cmdType">DbCommand類型 (CommandType.StoredProcedure或CommandType.Text)</param>
/// <param name="cmdText">預存程式名稱 或 T-SQL 語句</param>
/// <param name="commandParameters">使用到的參數集合</param>
/// <returns>回傳第一行第一列的資料，型別不確定</returns>
public static object ExecuteScalar(CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
{
return ExecuteScalar(connectionString, cmdType, cmdText, commandParameters);
}
/// <summary>
/// 取得第一行第一列的資料，通常使用在Select Count(*) From TableName 有聚合函數的Select指令
/// 使用SqlHelper預設DB連線字串、CommandType.Text
/// </summary>
/// <param name="cmdText">T-SQL 語句</param>
/// <param name="commandParameters">使用到的參數集合</param>
/// <returns>回傳第一行第一列的資料，型別不確定</returns>
public static object ExecuteScalarText(string cmdText, params DbParameter[] commandParameters)
{
return ExecuteScalar(connectionString, CommandType.Text, cmdText, commandParameters);
}
#endregion
#region ExecuteReader
/// <summary>
/// 執行Select查詢指令，工程師指定連線字串。
/// 前端呼叫時記得要用using包住回傳的DbDataReader變數來關閉連線
/// </summary>
/// <param name="connectionString">工程師自行指定DB連線字串</param>
/// <param name="cmdType">DbCommand類型 (CommandType.StoredProcedure或CommandType.Text)</param>
/// <param name="cmdText">預存程式名稱 或 T-SQL 語句</param>
/// <param name="commandParameters">使用到的參數集合</param>
/// <returns>回傳DbDataReader指標</returns>
public static DbDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
{
DbConnection conn = dbProviderFactory.CreateConnection();
conn.ConnectionString = connectionString;//連線字串
DbCommand cmd = conn.CreateCommand();
//↓不寫這行的話，由實作的Provider決定數值，OleDb、Odbc、SqlClient預設30秒，OracleClient為0不逾時
cmd.CommandTimeout = 0;//執行SQL指令時間，0為不逾時
try
{
//開啟連線
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();//清除參數集合
return reader;
}
catch (DbException ex)
{
conn.Close();//發生查詢例外就關閉連線
throw ex;
}
}
/// <summary>
/// 執行Select查詢指令，使用SqlHelper預設連線字串。
/// 前端呼叫時記得要用using包住回傳的DbDataReader變數來關閉連線
/// </summary>
/// <param name="cmdType">DbCommand類型 (CommandType.StoredProcedure或CommandType.Text)</param>
/// <param name="cmdText">預存程式名稱 或 T-SQL 語句</param>
/// <param name="commandParameters">使用到的參數集合</param>
/// <returns>回傳DbDataReader指標</returns>
public static DbDataReader ExecuteReader(CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
{
return ExecuteReader(connectionString, cmdType, cmdText, commandParameters);
}
/// <summary>
/// 執行Select查詢指令，使用SqlHelper預設連線字串、CommandType.Text
/// 前端呼叫時記得要用using包住回傳的DbDataReader變數來關閉連線
/// </summary>
/// <param name="cmdText">T-SQL 語句</param>
/// <param name="commandParameters">使用到的參數集合</param>
/// <returns>回傳DbDataReader指標</returns>
public static DbDataReader ExecuteReaderText(string cmdText, params DbParameter[] commandParameters)
{
return ExecuteReader(CommandType.Text, cmdText, commandParameters);
}
#endregion
#region 交易
/// <summary>
/// 執行多條SQL語句，實現資料庫交易，使用SqlHelper預設DB連線字串
/// </summary>
/// <param name="sqlStringList">參數集合</param>
public static void ExecuteSqlTran(Dictionary<string, DbParameter[]> sqlStringList)
{
ExecuteSqlTran(connectionString, CommandType.Text, sqlStringList);
}
/// <summary>
/// 執行多條SQL語句，實現資料庫交易
/// </summary>
/// <param name="sqlStringList">多條SQL語句</param>
public static void ExecuteSqlTran(string connectionString, CommandType cmdType, Dictionary<string, DbParameter[]> sqlStringList)
{
using (DbConnection conn = dbProviderFactory.CreateConnection())
{
conn.ConnectionString = connectionString;//指定連線字串
conn.Open();//開啟連線
DbTransaction trans = conn.BeginTransaction();//開始交易
DbCommand cmd = conn.CreateCommand();
try
{
foreach (KeyValuePair<string, DbParameter[]> item in sqlStringList)
{
PrepareCommand(cmd, conn, trans, cmdType, item.Key, item.Value);
cmd.ExecuteNonQuery();//執行一筆SQL異動語句
//清空DbCommand中的參數集合
cmd.Parameters.Clear();
}//end foreach
trans.Commit();//交易提交
}
catch (DbException ex)
{
trans.Rollback();//交易Rollback
throw ex;
}
}
}
#endregion
#endregion
#region 大量寫入 Modify by Shadow at 2014-08-02
/// <summary>
/// 大量批次新增(限對象為Sql Server)，使用SqlHelper的預設連線
/// </summary>
/// <param name="dtSource">資料來源的DataTable</param>
/// <param name="destDataTableName">目標資料庫的表格名稱</param>
/// <param name="optionSqlBulk">匯入時的選項</param>
public static void SqlBulkCopyFromDataTable(DataTable dtSource, string destDBTableName, SqlBulkCopyOptions optionSqlBulk = SqlBulkCopyOptions.Default)
{
SqlBulkCopyFromDataTable(connectionString, dtSource, destDBTableName, optionSqlBulk);
}
/// <summary>
/// 大量批次新增(限對象為Sql Server)，工程師自行指定DB連線
/// </summary>
/// <param name="connectionString">DB連線字串</param>
/// <param name="dtSource">資料來源的DataTable</param>
/// <param name="destDataTableName">目標資料庫的表格名稱</param>
/// <param name="optionSqlBulk">匯入時的選項</param>
public static void SqlBulkCopyFromDataTable(string connectionString, DataTable dtSource, string destDBTableName, SqlBulkCopyOptions optionSqlBulk = SqlBulkCopyOptions.Default)
{
if (string.IsNullOrEmpty(destDBTableName))
{
throw new Exception("缺少目標資料庫的表格名稱");
}
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();//開啟連線
//開始交易
SqlTransaction tran = conn.BeginTransaction();
//宣告SqlBulkCopy
using (SqlBulkCopy sqlBC = new SqlBulkCopy(conn, optionSqlBulk, tran))
{
//設定一個批次量寫入多少筆資料
sqlBC.BatchSize = 1000;
//設定逾時的秒數
sqlBC.BulkCopyTimeout = 30;
//設定要寫入的資料表
sqlBC.DestinationTableName = destDBTableName;
foreach (DataColumn dataCol in dtSource.Columns)
{
//對應資料行
sqlBC.ColumnMappings.Add(dataCol.ColumnName, dataCol.ColumnName);
}//end foreach
//開始寫入新增
try
{
sqlBC.WriteToServer(dtSource);
tran.Commit();//交易提交
}
catch (SqlException ex)
{
tran.Rollback();//交易Rollback
throw ex;
}
}//end using
}//end using
}
#endregion
#region 離線資料庫存取
#region GetDataTable
/// <summary>
/// 取得Select指令回傳的結果集，工程師自行指定DB連線字串
/// </summary>
/// <param name="connecttionString">DB連線字串</param>
/// <param name="cmdTye">DbCommand類型 (CommandType.StoredProcedure或CommandType.Text)</param>
/// <param name="cmdText">預存程式名稱 或 T-SQL 语句</param>
/// <param name="commandParameters">使用到的參數集合</param>
/// <returns>取得Select指令回傳的結果集，型別DataTable</returns>
public static DataTable GetDataTable(string connectionString, CommandType cmdTye, string cmdText,params DbParameter[] commandParameters)
{
DataTable dt = new DataTable();
using (DbConnection conn = dbProviderFactory.CreateConnection())
{
conn.ConnectionString = connectionString;//連線字串
DbCommand cmd = conn.CreateCommand();
//↓不寫這行的話，由實作的Provider決定數值，OleDb、Odbc、SqlClient預設30秒，OracleClient為0不逾時
cmd.CommandTimeout = 0;//執行SQL指令時間，0為不逾時
PrepareCommand(cmd, conn, null, cmdTye, cmdText, commandParameters);
DbDataAdapter adapter = dbProviderFactory.CreateDataAdapter();//DbDataAdapter自己會開/關DB連線
adapter.SelectCommand = cmd;
adapter.Fill(dt);
cmd.Parameters.Clear();
conn.Close();//自關連線
}
return dt;
}
/// <summary>
/// 取得Select指令回傳的結果集，使用SqlHelper預設DB連線字串
/// </summary>
/// <param name="cmdTye">DbCommand類型 (CommandType.StoredProcedure或CommandType.Text)</param>
/// <param name="cmdText">預存程式名稱 或 T-SQL 语句</param>
/// <param name="commandParameters">使用到的參數集合</param>
/// <returns>取得Select指令回傳的結果集，型別DataTable</returns>
public static DataTable GetDataTable(CommandType cmdTye, string cmdText,params DbParameter[] commandParameters)
{
return GetDataTable(connectionString, cmdTye, cmdText, commandParameters);
}
/// <summary>
/// 取得Select指令回傳的結果集，使用SqlHelper預設DB連線字串、CommandType.Text
/// </summary>
/// <param name="cmdText">T-SQL 语句</param>
/// <param name="commandParameters">使用到的參數集合</param>
/// <returns>取得Select指令回傳的結果集，型別DataTable</returns>
public static DataTable GetDataTableText(string cmdText,params DbParameter[] commandParameters)
{
return GetDataTable(CommandType.Text, cmdText, commandParameters);
}
#endregion
#endregion
}
}
編輯SqlHelper.cs檔，還有兩個步驟，請見下圖
這樣前置作業都完成，可以開始使用
2018-12-21 追加GetDataTable Retry抓資料
/// <summary>
/// 要重試次數的上限
/// </summary>
private static readonly int maxRetryCount = 10;//10次
/// <summary>
/// 第二次retry之後要等待的毫秒
/// </summary>
private static readonly int waitMilliSeconds = 6000;//6000毫秒
/// <summary>
/// 資料庫常常莫名斷線，所以retry抓資料
/// </summary>
/// <param name="connecttionString">DB連線字串</param>
/// <param name="cmdTye">DbCommand類型 (CommandType.StoredProcedure或CommandType.Text)</param>
/// <param name="cmdText">預存程式名稱 或 T-SQL 語句</param>
/// <param name="commandParameters">使用到的參數集合</param>
/// <returns>取得Select指令回傳的結果集，型別DataTable</returns>
public static DataTable GetDataTable_Retry(string connecttionString, CommandType cmdTye, string cmdText, DbParameter[] commandParameters)
{
DataTable dt = new DataTable();
using (DbConnection conn = dbProviderFactory.CreateConnection())
{
conn.ConnectionString = connectionString;//連線字串
DbCommand cmd = conn.CreateCommand();
cmd.CommandTimeout = 0;
bool isError = false;//是否錯誤
int retryCount = 0;//重試次數
#region 重試DB資料
do
{//retry 抓資料
retryCount++;
if (retryCount>=2)
{//重試第2次之後...
System.Threading.Thread.Sleep(waitMilliSeconds);
}
try
{
PrepareCommand(cmd, conn, null, cmdTye, cmdText, commandParameters);
DbDataAdapter adapter = dbProviderFactory.CreateDataAdapter();//DbDataAdapter自己會開/關DB連線
adapter.SelectCommand = cmd;
adapter.Fill(dt);
//成功抓到資料
isError = false;//離開retry
//break;
}//end try
catch (Exception ex)
{
isError = true;//可能進入retry
if (retryCount==maxRetryCount)
{//達retry上限次數
//todo 寫Log
}
}//end catch
finally
{
if (conn != null && conn.State == ConnectionState.Open)
{
conn.Close();//自關連線
}
}
} while (isError && retryCount<maxRetryCount);//retry超過上限就不再retry
#endregion
}//end using
return dt;
}
2019-02-26 追加ExecuteScalar Retry抓資料
/// <summary>
/// 要重試次數的上限
/// </summary>
private static readonly int maxRetryCount = 10;//10次
/// <summary>
/// 第二次retry之後要等待的毫秒
/// </summary>
private static readonly int waitMilliSeconds = 6000;//6000毫秒
/// <summary>
public static object ExecuteScalar_Retry(string connectionString, CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
{
object o = null;//要回傳的物件
using (DbConnection conn = dbProviderFactory.CreateConnection())
{
conn.ConnectionString = connectionString;//連線字串
DbCommand cmd = conn.CreateCommand();
cmd.CommandTimeout = 0;
bool isError = false;//是否錯誤
int retryCount = 0;//重試次數
#region 重試DB資料
do
{//retry 抓資料
retryCount++;
if (retryCount >= 2)
{//重試第2次之後...
System.Threading.Thread.Sleep(waitMilliSeconds);
}
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
o = cmd.ExecuteScalar();
//成功抓到資料
isError = false;//離開retry
//break;
}//end try
catch (Exception ex)
{
isError = true;//可能進入retry
if (retryCount == maxRetryCount)
{//達retry上限次數
//todo寫Log
StringBuilder sb_params = new StringBuilder();
if (commandParameters != null)
{
foreach (DbParameter param in commandParameters)
{
sb_params.AppendLine($@"ParameterName:{param.ParameterName} , Value:{Convert.ToString(param.Value)}");
}
}//end if
}//end catch
finally
{
if (conn != null && conn.State == ConnectionState.Open)
{
conn.Close();//自關連線
}
}
} while ( isError && retryCount < maxRetryCount);//retry超過上限就不再retry
#endregion
return o;
}
}
使用方法
我弄了一份展示用的資料，這樣會比較好理解
記得使用SqlHelper呼叫方法前，要先using以下命名空間
using System.Data.SqlClient;//本文範例使用SQL Server
using SystemDAO;
using System.Data;
using System.Data.Common;
DataTable呼叫方式：無參數
//抓全部資料，由於不是參數化查詢，第二個參數傳null
DataTable dt1 =
SqlHelper.GetDataTableText(@"Select ID,Name,Seq
From TestTable
Order by Seq ASC", null);
DataTable呼叫方式：參數化查詢
DataTable dt2 =
SqlHelper.GetDataTableText(@"Select Top 1 ID,Name,Seq
From TestTable
Where ID=@ID And Name=@Name
Order by Seq ASC", new SqlParameter[]{
new SqlParameter(){ ParameterName="@ID" ,SqlDbType=SqlDbType.BigInt,Value=1},
new SqlParameter(){ ParameterName="@Name" ,SqlDbType=SqlDbType.NVarChar,Value="Shadow"}});
↓工作上很常出現的表單查詢
//以下是常見查詢拼接語句
string sql = @"Select ID,Name,Seq
From TestTable
Where 1=1 ";
long Seq = 10;//要查詢的參數
string[] Names = { "Apple", "Banana" };//要查詢的參數，來源通常是使用者輸入
List<SqlParameter> para = new List<SqlParameter>();
if (Seq != -1)//判斷查詢的值
{
sql += " And Seq < @Seq ";
para.Add(new SqlParameter() { ParameterName = "@Seq", SqlDbType = SqlDbType.BigInt, Value = Seq });
}
if (Names != null && Names.Length > 0)//判斷查詢的值
{
//準備好各參數
string[] paraNames = Names.Select((s,i)=>"@p"+i.ToString()).ToArray();
for (int i = 0; i < paraNames.Length; i++)
{
para.Add(new SqlParameter() { ParameterName = paraNames[i], SqlDbType = SqlDbType.NVarChar, Value = Names[i]});
}
string inClause = string.Join(",", paraNames);
//拼接SQL語句
sql += " And Name in (" + inClause + ") ";
/* 以上動作相當於↓
SqlCommand cmd=new SqlCommand();
cmd.CommandText = @"Select ID,Name,Seq
From TestTable
Where 1=1 And Name in (@p0,@p1)";
cmd.Parameters["@p0"] = "Apple";
cmd.Parameters["@p1"] = "Banana";
*/
}//end if
//排序
sql += "Order by Seq ASC";
DataTable dt3 = SqlHelper.GetDataTableText(sql, para.ToArray());
//↑執行結果就是把Apple、Banana兩筆資料抓出來
為了方便展示預存程序呼叫方式，我建了兩個預存程序
--無參數預存程序
Create Procedure TestPro1
As
Begin
Select ID,Name,Seq
From TestTable
Order by Seq ASC
End
Go
--有參數預存程序
Create Procedure TestPro2
(
@ID bigint,
@Name nvarchar(6) OUTPUT
)
As
Begin
Select ID,Name,Seq
From TestTable
Where ID=@ID
Order by Seq ASC
Select @Name=Name
From TestTable
Where ID=@ID
Order by Seq ASC
Return 0
End
Go
呼叫預存程序，無參數
DataTable dt1 = SqlHelper.GetDataTable(CommandType.StoredProcedure, "TestPro1", null);
呼叫預存程序，傳入參數並取得OUTPUT參數值和Return Value
※注意預存程序有回傳資料集才用GetDataTable()方法去接，如果預存程序沒有回傳資料集的話，則改用ExecuteNonQuery()方法執行即可
long ID = 1;//查詢主鍵
//輸出參數
SqlParameter OutPutPara = new SqlParameter()
{
ParameterName = "@Name",
SqlDbType=SqlDbType.NVarChar,
Size=6,//OUTPUT參數，不指派Size會掛掉
Direction=ParameterDirection.Output,
};
//回傳值
SqlParameter returnPara = new SqlParameter()
{
SqlDbType = SqlDbType.Int,
Direction = ParameterDirection.ReturnValue,
};
//取得Select資料集
DataTable dt1 =
SqlHelper.GetDataTable(CommandType.StoredProcedure, "TestPro2", new SqlParameter[]
{ new SqlParameter(){ ParameterName="@ID",SqlDbType=SqlDbType.BigInt,Value=ID},
OutPutPara,
returnPara
});
//取得輸出參數值：Shadow
string Name = Convert.ToString(OutPutPara.Value);
//回傳值為：0
int returnValue = Convert.ToInt32(returnPara.Value);
ExecuteReader()呼叫方式
※跟GetDataTable()同樣在抓Select資料集使用，ExecuteReader()是連線資料庫存取、GetDataTable()是離線資料庫存取，兩者詳細差異就不在此贅述
※我之前有過經驗，使用Odbc Provider存取Informix資料庫，撈資料集時全部使用ExecuteReader()方法，後來似乎Connection Pool連線不夠用，程式當掉
最後改用GetDataTable()就好了，推測可能原先那種組合撈資料方式並無法完全關閉連線
using (DbDataReader reader = SqlHelper.ExecuteReaderText(@"Select ID,Name,Seq
From TestTable
Where Name Like @Name
Order by Seq ASC",
new SqlParameter[]{
new SqlParameter(){ ParameterName="@Name",SqlDbType=SqlDbType.NVarChar,Value="%"+"a"+"%"}
//找名字有包含a字串，'%'萬用字元必須是參數裡的值才是正確Like參數化查詢用法
}))
{//start using
while (reader.Read())
{
Console.WriteLine(reader["ID"] + "|" + reader["Name"]);//一筆一筆抓值出來
}
}//end using
ExecuteScalar()呼叫方式：用來取得彙總函式的值
//算出資料表的筆數
long count = Convert.ToInt64(SqlHelper.ExecuteScalarText(@"Select Count(*) As Count From TestTable", null));
↓ExecuteScalar()也可以執行新增資料再取得新資料的識別值(通常是主鍵)
//取得新增資料的ID主鍵
long ID =
Convert.ToInt64(
SqlHelper.ExecuteScalarText(@"Insert into TestTable(Name,Seq) Values (N'Cherry',4);
Select SCOPE_IDENTITY()", null));
ExecuteNonQuery()方法主要用在Insert、Update、Delete語句
//新增資料
SqlHelper.ExecuteNonQueryText("Insert into TestTable (Name,Seq) Values (@Name,@Seq)", new SqlParameter[] {
new SqlParameter(){ParameterName="@Name",SqlDbType=SqlDbType.NVarChar,Value="Test姓名"},
new SqlParameter(){ParameterName="@Seq",SqlDbType=SqlDbType.BigInt,Value=7}
});
//修改資料
SqlHelper.ExecuteNonQueryText("Update TestTable Set Name=@Name Where Seq=7", new SqlParameter[] {
new SqlParameter(){ParameterName="@Name",SqlDbType=SqlDbType.NVarChar,Value="更改姓名"}
});
//刪除資料
SqlHelper.ExecuteNonQueryText("Delete From TestTable Where Seq=7", null);
ExecuteReader()、ExecuteScalar()、ExecuteNonQuery()這三個方法也可以搭配預存程序使用，請參考上面GetDataTable()的用法
再來要提到的是，只有SQL Server資料庫才支援的功能：批次快速大量新增資料
通常資料來源都從Excel、CSV、TXT...等檔案先抓到DataTable裡儲存
然後再由DataTable直接灌入SQL Server，請見以下用法
DataTable dtExcel = new DataTable();
dtExcel.Columns.Add("ID",typeof(long));//欄位名稱需和DB裡的欄位名稱相同
dtExcel.Columns.Add("Name", typeof(string));//欄位名稱需和DB裡的欄位名稱相同
dtExcel.Columns.Add("Seq", typeof(long));//欄位名稱需和DB裡的欄位名稱相同
for (int i = 0; i < 10000; i++) //假裝這是從Excel抓進DataTable的10000筆資料
{
DataRow dr = dtExcel.NewRow();
dr["Name"] = "Name" + i.ToString().Substring(0,1);//姓名都給5個長度
dr["Seq"] = i;
dtExcel.Rows.Add(dr);
}
//批次大量新增資料
SqlHelper.SqlBulkCopyFromDataTable(dtExcel, "TestTable", SqlBulkCopyOptions.Default);
最後是交易，基本用法↓
//先建立一個SQL語句參數用的集合
Dictionary<string, DbParameter[]> sqlPara = new Dictionary<string, DbParameter[]>();
sqlPara.Add("Insert into TestTable (Name ,Seq) Values (@Name,@Seq)",
new SqlParameter[]{
new SqlParameter(){ ParameterName="@Name",SqlDbType=SqlDbType.NVarChar,Value="測試姓名"},
new SqlParameter(){ ParameterName="@Seq",SqlDbType=SqlDbType.BigInt,Value=78}});
sqlPara.Add("Update TestTable Set Name=@Name Where Seq=78",
new SqlParameter[]{
new SqlParameter(){ ParameterName="@Name",SqlDbType=SqlDbType.NVarChar,Value="來測姓名"}});
//由於Dictionary關係，每條SQL語句必須不一樣，因為Dictionary的Key不能重覆，其實加入SQL語句不重覆也很簡單，若遇到相同SQL的話，多打幾個空格就好，如下示範
sqlPara.Add("Insert into TestTable (Name ,Seq) Values (@Name,@Seq) ",
new SqlParameter[]{
new SqlParameter(){ ParameterName="@Name",SqlDbType=SqlDbType.NVarChar,Value="LastNa"},
new SqlParameter(){ ParameterName="@Seq",SqlDbType=SqlDbType.BigInt,Value=99}});
//執行交易
SqlHelper.ExecuteSqlTran(sqlPara);
交易過程中若發生例外情況，全部資料會Rollback，都不會異動到資料表
//先建立一個SQL語句參數用的集合
Dictionary<string, DbParameter[]> sqlPara = new Dictionary<string, DbParameter[]>();
sqlPara.Add("Insert into TestTable (Name ,Seq) Values (@Name,@Seq)",
new SqlParameter[]{
new SqlParameter(){ ParameterName="@Name",SqlDbType=SqlDbType.NVarChar,Value="測試姓名"},
new SqlParameter(){ ParameterName="@Seq",SqlDbType=SqlDbType.BigInt,Value=78}});
//Name欄位不允許NULL
sqlPara.Add("Update TestTable Set Name=@Name Where Seq=78",
new SqlParameter[]{
new SqlParameter(){ ParameterName="@Name",SqlDbType=SqlDbType.NVarChar,Value=DBNull.Value}});
try
{
//執行交易，資料會Rollback，第一筆資料不會被塞入到DB
SqlHelper.ExecuteSqlTran(sqlPara);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
如果交易有使用到取得識別值函式，例如SCOPE_IDENTITY()的話，得注意它的SQL語句要放在Dictionary的同一Key值裡
Dictionary<string, DbParameter[]> sqlPara = new Dictionary<string, DbParameter[]>();
//SCOPE_IDENTITY()放在同一道命令裡
sqlPara.Add(@"Insert into TestTable (Name ,Seq) Values (@Name1,@Seq)
Insert into TestTable (Name ,Seq) Values (@Name2,SCOPE_IDENTITY())",
new SqlParameter[]{
new SqlParameter(){ ParameterName="@Name1",SqlDbType=SqlDbType.NVarChar,Value="測試姓名"},
new SqlParameter(){ ParameterName="@Seq",SqlDbType=SqlDbType.BigInt,Value=44},
new SqlParameter(){ ParameterName="@Name2",SqlDbType=SqlDbType.NVarChar,Value="測試姓名2"}});
//執行交易
SqlHelper.ExecuteSqlTran(sqlPara);
/*以下是SCOPE_IDENTITY()錯誤用法*/
Dictionary<string, DbParameter[]> sqlPara = new Dictionary<string, DbParameter[]>();
//拆成兩道命令的話，SCOPE_IDENTITY()會是NULL
sqlPara.Add("Insert into TestTable (Name ,Seq) Values (@Name,@Seq)",
new SqlParameter[]{
new SqlParameter(){ ParameterName="@Name",SqlDbType=SqlDbType.NVarChar,Value="測試姓名"},
new SqlParameter(){ ParameterName="@Seq",SqlDbType=SqlDbType.BigInt,Value=44}});
sqlPara.Add("Insert into TestTable (Name ,Seq) Values (@Name,SCOPE_IDENTITY())",
new SqlParameter[]{
new SqlParameter(){ ParameterName="@Name",SqlDbType=SqlDbType.NVarChar,Value="測試姓名2"}
});
//交易Rollback，Seq資料行不允許NULL
SqlHelper.ExecuteSqlTran(sqlPara);
※ 2019-02-15追記：
現在才發覺資料庫交易其實不用包裝成ExecuteSqlTran()方法，不過Sample Code多寫就算了XD
只要呼叫ExecuteNonQuery()、ExecuteScalar()再搭配此篇文章 [C#] EntityFramework交易寫法 Sample Code 介紹的 TransactionScope物件即可
交易都成功就執行scope.Complete()，否則不執行scope.Complete()，同樣達到Commit、Rollback效果
留意SQL Injection
提醒一件事，如果查詢條件的參數，來自外部輸入(Url的QueryString)或使用者輸入(填表單欄位)
最好使用參數化查詢來執行SQL語法，以避免發生SQL Injection被駭客搞掉資料
以下就是未使用參數化查詢的錯誤示範
string Name="Shadow";//假裝此值是外部輸入
//字串拼接SQL容易發生SQL Injection問題
DataTable dt =
SqlHelper.GetDataTableText("Select ID,Name,Seq From TestTable Where Name='" + Name + "' Order by Seq ASC", null);
//只會顯示一筆資料
foreach (DataRow dr in dt.Rows)
{
Console.WriteLine("ok:" + dr["ID"] + "|" + dr["Name"]);
}
Name = "' Or '1'='1";//有心人亂輸入
dt =
SqlHelper.GetDataTableText("Select ID,Name,Seq From TestTable Where Name='" + Name + "' Order by Seq ASC", null);
//顯示全部資料
foreach (DataRow dr in dt.Rows)
{
Console.WriteLine("SQL Injection:" + dr["ID"] + "|" + dr["Name"]);
}
Name = @"'; Declare @Table_Name nvarchar(4000);
SELECT Top 1 @Table_Name = TABLE_NAME FROM information_schema.tables;
Exec('Delete From '+@Table_Name); --";//有心人亂輸入，刪除資料表全部資料
dt = SqlHelper.GetDataTableText("Select ID,Name,Seq from TestTable Where Name='" + Name + "' Order by Seq ASC", null);
還有一種情況，雖然使用參數化查詢，但本質上還是在拼接字串，所以仍然有SQL Injection問題，最常見在預存程序的查詢條件拼接
--參數拼接字串的錯誤示範
CREATE Procedure [dbo].[TestProcedure]
(
@Name nvarchar(6),
@Seq bigint
)
As
Begin
Declare @sql varchar(4000)
Set @sql='Select ID,Name,Seq
From TestTable
Where 1=1 '
IF IsNull(@Name,'')<>''
Begin
set @sql = @sql +' And Name Like ''%'+@Name+'%'' '
End
IF (@Seq Is Not Null)
Begin
set @sql = @sql +' And Seq < '+ Convert(varchar,@Seq)
End
set @sql = @sql +' Order by Seq ASC'
Exec(@sql)--執行SQL字串
End
GO
執行結果
以程式呼叫產生的SQL Injection
所以，想要撰寫純T-SQL來實作查詢條件篩選(例如在預存程序裡)，以下提供三種正確無SQL Injection風險的範例
Declare @ID bigint =NULL --給NULL或-1，相當於略過ID查詢條件
Declare @Seq bigint =5 --給NULL或-1，相當於略過Seq查詢條件
Declare @Name nvarchar(10) = '%Sha%' --'%%'萬用字元必須是參數值，而不要在SQL語句中拼接'%'萬用字元
--@Name給NULL或空字串，相當於略過Name查詢條件
--第一種，由於使用Or，查詢效能可能不大好
Select ID,Name,Seq from TestTable
Where ( Seq< @Seq Or IsNULL(@Seq,-1)=-1)
And
( Name Like @Name Or IsNull(@Name,'')='')
And
( ID = @ID Or IsNull(@ID,-1)=-1)
--第二種，和第三種概念類似
SELECT ID,Name,Seq
FROM TestTable
WHERE seq < IIF(IsNull(@Seq,-1) <>-1,@Seq,100000)
And Name Like IIF(isNUll(@Name,'') <>'',@Name,Name)
And ID = IIF(isNUll(@ID,-1) <>-1,@ID,ID)
--第三種
SELECT ID,Name,Seq
FROM TestTable
Where Seq < CASE WHEN IsNull(@Seq,-1) <>-1 THEN @Seq ELSE 100000 END --當Seq為-1或Null時就給100000，相當於撈全部資料
AND Name Like CASE WHEN isNUll(@Name,'') <>'' THEN @Name ELSE Name END --當@Name為Null或空字串時，Name=Name，相當於撈全部資料
AND ID = CASE WHEN isNUll(@ID,-1) <>-1 THEN @ID ELSE ID END --當@ID為Null或-1時，ID=ID，相當於撈全部資料
/***※補充：SQL In查詢語句和時間區間的查詢範例***/
Declare @Param nvarchar(4000) = 'Shadow,Sharon'
Declare @StartDate Datetime ='2015-12-01'
Declare @EndDate Datetime='2015-12-09'
--第一種寫法，我已經事先在資料表新增名為InsertDatetime時間欄位
Select ID,Name,Seq
from TestTable
where (IsNull(@Param,'')='' Or CharIndex(Name,@Param)>0)
And ( (@StartDate Is NULL And @EndDate Is NULL) Or (InsertDatetime Between @StartDate And @EndDate))
--第二種寫法
Select ID,Name,Seq
from TestTable
Where
@Param Like IIF(isNUll(@Param,'') <>'','%'+Name+'%',@Param)
And
( InsertDatetime>= IIF((@StartDate Is NULL And @EndDate Is NULL),InsertDatetime,@StartDate)
And InsertDatetime<=IIF((@StartDate Is NULL And @EndDate Is NULL),InsertDatetime,@EndDate) )
結語
從頭看下來，我好像把ADO.net技術全部講完了XD
此篇文章從早上9點寫到隔天凌晨2點，我也太熱血XDD