《学习笔记》基于SqlClient开发SQLServer工具类

注意：代码直接Copy调用即可,SQLServer工具类跟我上一个Oracle工具类有所不同，区别在于调用存储过程中时参数的使用，无需输入对应的存储游标名称
特点：根据用户传入的参数类型来识别存储中对应的参数数据类型
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
        /// <summary>
    ///基於.net（ 向下兼容4.0）開發 SQLServerDBHelper工具類
    ///<para>作者： 会害羞的青蛙</para>
    ///<para>時間： 2019-12-6</para>
    /// </summary>
    public class SQLServerDBHelper
    {
        /// <summary>
        /// 執行SQL語句返回DataTable
        /// </summary>
        /// <param name="SQL">SQL語句</param>
        /// <param name="DBUrl">數據庫鏈接地址</param>
        /// <returns></returns>
        public DataTable GetDataTableBySQL(string SQL, string DBUrl)
        {
            // 获取与数据库的连接对象並且绑定连接字符串
            SqlConnection conn = new SqlConnection(DBUrl);
            conn.Open();//打開資源
                        //获取数据库操作对象
            SqlCommand cmd = conn.CreateCommand();
            try
            {
                cmd.CommandText = SQL;
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                DataTable dataTable = new DataTable();
                dataTable.TableName = "數據集";
                adapter.Fill(dataTable);
                cmd.Dispose();//釋放資源
                conn.Dispose();//釋放資源
                conn.Close();//關閉
                return dataTable;
            }
            catch (Exception ex)
            {
                cmd.Dispose();//釋放資源
                conn.Dispose();//釋放資源
                conn.Close();//關閉
                throw ex;
            }
        }
        /// <summary>
        /// 執行非查詢的SQL語句
        /// </summary>
        /// <param name="SQL">SQL語句</param>
        /// <param name="DBUrl">數據庫鏈接地址</param>
        /// <returns></returns>
        public int GetNonQueryBySQL(string SQL, string DBUrl)
        {
            // 获取与数据库的连接对象並且绑定连接字符串
            SqlConnection conn = new SqlConnection(DBUrl);
            conn.Open();//打開資源
                        //获取数据库操作对象
            SqlCommand cmd = conn.CreateCommand();
            try
            {
                cmd.CommandText = SQL;
                int num = cmd.ExecuteNonQuery();
                cmd.Dispose();//釋放資源
                conn.Dispose();//釋放資源
                conn.Close();//關閉
                return num;
            }
            catch (Exception ex)
            {
                cmd.Dispose();//釋放資源
                conn.Dispose();//釋放資源
                conn.Close();//關閉
                throw ex;
            }
        }
        /// <summary>
        /// 執行多條SQL語句，實現數據庫事務。
        /// </summary>
        /// <param name="SQLStringList">多條SQL語句</param>        
        /// <param name="BDUrl">數據庫鏈接地址</param>     
        public int GetNonQueryByManySQL(ArrayList SQLStringList, string BDUrl)
        {
            using (SqlConnection conn = new SqlConnection(BDUrl))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    int num = 0;
                    for (int i = 0; i < SQLStringList.Count; i++)
                    {
                        string SQL = SQLStringList[i].ToString();//獲取SQL語句
                        if (SQL.Trim().Length > 1)
                        {
                            cmd.CommandText = SQL;
                            num = cmd.ExecuteNonQuery();
                        }
                        tx.Commit();//提交事務
                        cmd.Dispose();//釋放資源
                        conn.Dispose();//釋放資源
                        conn.Close();//關閉
 
                    }
                    return num;//返回執行結果數量
                }
                catch (SqlException E)
                {
                    tx.Rollback();//事務回滾
                    throw new Exception(E.Message);
                }
            }
        }
        /// <summary>
        /// 調用存儲返回單個結果集
        /// <para>obj使用方法：new{ v_data=value, v_data1=value1}</para>
        /// <para>注意：obj中v_data為存儲參數名稱，value為對應的值，在SQLServer中沒有游標所以不需要定義輸出參數</para>
        /// </summary>
        /// <param name="storageName">存儲名稱</param>
        /// <param name="DBUrl">數據庫鏈接地址</param>
        /// <param name="obj">存儲參數對象</param>
        /// <returns></returns>
        public DataTable GetDataTableByStorageName(string storageName, string DBUrl, object obj)
        {
            // 获取与数据库的连接对象並且绑定连接字符串
            SqlConnection conn = new SqlConnection(DBUrl);
            conn.Open();//打開資源
            //获取数据库操作对象
            SqlCommand cmd = conn.CreateCommand();
            try
            {
                cmd.CommandText = storageName;//存儲名稱
                cmd.CommandType = CommandType.StoredProcedure;
                PropertyInfo[] properties = obj.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);//獲取object中的字段名和值
                for (int i = 0; i < properties.Length; i++)
                {//設定輸入的類型和值
                    cmd.Parameters.Add(properties[i].Name, GetOracleDbType(properties[i], obj)).Direction = ParameterDirection.Input;
                    cmd.Parameters[properties[i].Name].Value = properties[i].GetValue(obj, null);//賦值
                }
                DataTable dataTable = new DataTable();
                SqlDataAdapter oda = new SqlDataAdapter(cmd);
                oda.Fill(dataTable);
                dataTable.TableName = "數據集";
                cmd.Dispose();//釋放資源
                conn.Dispose();//釋放資源
                conn.Close();//關閉
                return dataTable;
            }
            catch (Exception ex)
            {
                cmd.Dispose();//釋放資源
                conn.Dispose();//釋放資源
                conn.Close();//關閉
                throw ex;
            }
        }
        /// <summary>
        /// 調用存儲返回String字符串信息（最後一個位置必須為String類型字符，位置不能顛倒）
        /// <para>obj使用方法：new{ v_data=value, v_data1=value1,out_string=""}</para>
        /// <para>注意：obj中v_data為存儲參數名稱，value為對應的值，out_string為輸出參數不需要輸入值</para>
        /// </summary>
        /// <param name="storageName"></param>
        /// <param name="DBUrl"></param>
        /// <param name="obj"></param>
        /// <returns></returns>
        public string GetStringDataByStorageName(string storageName, string DBUrl, object obj)
        {
            SqlConnection conn = new SqlConnection(DBUrl);
            conn.Open();
            //获取数据库操作对象
            SqlCommand cmd = conn.CreateCommand();
            try
            {
                cmd.CommandText = storageName;//存儲名稱
                cmd.CommandType = CommandType.StoredProcedure;
                PropertyInfo[] properties = obj.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);//獲取object中的字段名和值
                for (int i = 0; i < properties.Length; i++)
                {
                    if (i == (properties.Length - 1))
                    { //設定輸出的類型和值
                        cmd.Parameters.Add("@" + properties[i].Name, SqlDbType.NVarChar, short.MaxValue).Direction = ParameterDirection.Output;
                        cmd.Parameters["@" + properties[i].Name].Value = DBNull.Value;//賦值
                    }
                    else
                    {//設定輸入的類型和值
                        cmd.Parameters.Add(properties[i].Name, GetOracleDbType(properties[i], obj)).Direction = ParameterDirection.Input;
                        cmd.Parameters["@" + properties[i].Name].Value = properties[i].GetValue(obj, null);//賦值
                    }
                }
                cmd.ExecuteNonQuery();
                string message = cmd.Parameters["@" + properties[properties.Length - 1].Name].Value.ToString();//獲取返回的值
                cmd.Dispose();//釋放資源
                conn.Dispose();//釋放資源
                conn.Close();//關閉
                return message;
            }
            catch (Exception ex)
            {
                cmd.Dispose();//釋放資源
                conn.Dispose();//釋放資源
                conn.Close();//關閉
                throw ex;
            }
        }
        /// <summary>
        /// 調用存儲返回String字符串信息和DataTable數據表格（最後一個為輸出字符串，位置不能顛倒）
        /// <para>obj使用方法：new{ v_data=value, v_data1=value1,out_string=""}</para>
        /// <para>注意：obj中v_data為存儲參數名稱，value為對應的值，out_string為輸出參數不需要輸入值， 在SQLServer中沒有游標所以不需要定義輸出參數</para>
        /// </summary>
        /// <param name="storageName">存儲名稱</param>
        /// <param name="DBUrl">數據庫鏈接地址</param>
        /// <param name="obj">存儲參數對象</param>
        /// <param name="dataTable">返回結果集</param>
        /// <returns></returns>
        public string GetStringAndDataTableByStorageName(string storageName, string DBUrl, object obj, out DataTable dataTable)
        {
            // 获取与数据库的连接对象並且绑定连接字符串
            SqlConnection conn = new SqlConnection(DBUrl);
            conn.Open();//打開資源
            //获取数据库操作对象
            SqlCommand cmd = conn.CreateCommand();
            try
            {
                cmd.CommandText = storageName;//存儲名稱
                cmd.CommandType = CommandType.StoredProcedure;
                PropertyInfo[] properties = obj.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);//獲取object中的字段名和值
                for (int i = 0; i < properties.Length; i++)
                {
                    if (i == (properties.Length - 1))
                    {//設定輸出的類型和值
                        cmd.Parameters.Add(properties[i].Name, SqlDbType.NVarChar, short.MaxValue).Direction = ParameterDirection.Output;
                        cmd.Parameters[properties[i].Name].Value = DBNull.Value;//賦值
                    }
                    else
                    {//設定輸入的類型和值
                        cmd.Parameters.Add(properties[i].Name, GetOracleDbType(properties[i], obj)).Direction = ParameterDirection.Input;
                        cmd.Parameters[properties[i].Name].Value = properties[i].GetValue(obj, null);//賦值
                    }
                }
                DataTable dt = new DataTable();
                SqlDataAdapter oda = new SqlDataAdapter(cmd);
                oda.Fill(dt);
                dt.TableName = "數據集";
                dataTable = dt;//返回數據結果集
                string message = cmd.Parameters[properties[properties.Length - 1].Name].Value.ToString();//獲取輸出的字符串
                cmd.Dispose();//釋放資源
                conn.Dispose();//釋放資源
                conn.Close();//關閉
                return message;
            }
            catch (Exception ex)
            {
                cmd.Dispose();//釋放資源
                conn.Dispose();//釋放資源
                conn.Close();//關閉
                throw ex;
            }
        }
        /// <summary>
        /// 將輸入參數的類型與SQLServer中的參數類型轉化一致
        /// </summary>
        /// <param name="propertyInfo">屬性特征</param>
        /// <param name="obj">對象</param>
        /// <returns></returns>
        private SqlDbType GetOracleDbType(PropertyInfo propertyInfo, object obj)
        {
            try
            {
                SqlDbType sqlDbType = new SqlDbType();
                switch (propertyInfo.GetValue(obj, null).GetType().Name)
                {
                    case "String":
                        sqlDbType = SqlDbType.NVarChar;
                        break;
                    case "Int16":
                        sqlDbType = SqlDbType.SmallInt;
                        break;
                    case "Int32":
                        sqlDbType = SqlDbType.Int;
                        break;
                    case "Int64":
                        sqlDbType = SqlDbType.BigInt;
                        break;
                    case "DateTime":
                        sqlDbType = SqlDbType.DateTime;
                        break;
                    case "Boolean":
                        sqlDbType = SqlDbType.Bit;
                        break;
                    case "Byte":
                        sqlDbType = SqlDbType.TinyInt;
                        break;
                    case "Decimal":
                        sqlDbType = SqlDbType.Decimal;
                        break;
                }
                return sqlDbType;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// 以現有表格數據批量添加數據
        /// </summary>
        /// <param name="dt">數據表格</param>
        /// <param name="FormName">數據表名</param>
        /// <param name="DBUrl">數據庫鏈接地址</param>
        /// <returns></returns>
        public bool AddInBatchesToDataTable(DataTable dt,string FormName, string DBUrl )
        {
            try
            {
                if (FormName.ToString().Trim() == "") throw new Exception("數據表名不能为空");
                System.Diagnostics.Debug.WriteLine("----------------------開始執行--------------------------");
                System.Diagnostics.Debug.WriteLine("執行表明：" + dt.TableName + "--------------------------");
                long star = Convert.ToInt64(System.DateTime.Now.ToString("yyyyMMddHHmmssfff"));
                SqlConnection conn = new SqlConnection(DBUrl);
                SqlBulkCopy bulkCopy = new SqlBulkCopy(DBUrl, SqlBulkCopyOptions.UseInternalTransaction);
                bulkCopy.BatchSize = 100000;
                bulkCopy.BulkCopyTimeout = 260;
                bulkCopy.DestinationTableName = FormName;    //服务器上目标表的名称
                bulkCopy.BatchSize = dt.Rows.Count;   //每一批次中的行数
                try
                {
                    conn.Open();
                    if (dt != null && dt.Rows.Count != 0)
                        bulkCopy.WriteToServer(dt);   //将提供的数据源中的所有行复制到目标表中
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    conn.Close();
                    if (bulkCopy != null)
                        bulkCopy.Close();
                }
                long end = Convert.ToInt64(System.DateTime.Now.ToString("yyyyMMddHHmmssfff"));
                System.Diagnostics.Debug.WriteLine("共添加：" + dt.Rows.Count + "條數數據----耗時：" + ((end - star) / 10000) + "." + ((end - star) % 10000) + "秒");
                System.Diagnostics.Debug.WriteLine("----------------------執行結束--------------------------");
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }