分享自己存取資料庫使用的SqlHelper類別,ADO.net技術

此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)
                {
                    foreach (DbParameter param in cmdParms) { cmd.Parameters.Add(param); }
                }
            }

            #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();
                    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 connecttionString, 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檔,還有兩個步驟,請見下圖

這樣前置作業都完成,可以開始使用

使用方法

我弄了一份展示用的資料,這樣會比較好理解

記得使用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); 

留意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