菜鳥學習Ado.net筆記一：Ado.net學習之SqlHelper類

2019-11-14 15:51:51

字體：大中小

來源：轉載

供稿：網友

  1 using System;  2 using System.Collections.Generic;  3 using System.Text;  4 using System.Data.SqlClient;  5 using System.Data;  6 using Microsoft.Win32;  7   8 namespace SqlHelp  9 { 10     /// <summary> 11     /// 定義SqlParameter所需的參數對象 12     /// </summary> 13     public class Parameter 14     { 15         /// <summary> 16         /// 參數集合構造函數 17         /// </summary> 18         /// <param name="paramname">參數名稱</param> 19         /// <param name="value">參數所對應的對象的值</param> 20         public Parameter(string paramname, object value) 21         { 22             this.ParamName = paramname; 23             this.Obj = value; 24         } 25         /// <summary> 26         /// 參數名稱 27         /// </summary> 28         public string ParamName 29         { 30             get; 31             set; 32         } 33         /// <summary> 34         /// 參數名稱所對應的對象的值 35         /// </summary> 36         public object Obj 37         { 38             get; 39             set; 40         } 41     } 42     /// <summary> 43     /// SqlHelper  ^_^ ! 44     /// </summary> 45     public class SqlHelper 46     { 47         /// <summary> 48         /// 連接字符串字段 49         /// </summary> 50         PRivate static string connStr; 51  52         /// <summary> 53         /// SQL連接字符串屬性 54         /// </summary>        55         public static string ConnStr 56         { 57             get { return SqlHelper.connStr; } 58             set { SqlHelper.connStr = value; } 59         } 60  61         private static SqlParameter[] GetSqlParameterToArr(List<Parameter> listP) 62         { 63             List<SqlParameter> list = new List<SqlParameter>(); 64             foreach (var item in listP) 65             { 66                 list.Add(new SqlParameter(item.ParamName, item.Obj)); 67             } 68             return list.ToArray(); 69         } 70  71         /// <summary> 72         /// 執行TSQL 語句并返回受影響的行 73         /// </summary> 74         /// <param name="sql">需要執行的sql語句</param> 75         /// <returns></returns> 76  77         public static int ExecuteNonQuery(string sql) 78         { 79             try 80             { 81                 using (SqlConnection conn = new SqlConnection(connStr)) 82                 { 83                     conn.Open(); 84                     using (SqlCommand cmd = conn.CreateCommand()) 85                     { 86                         cmd.CommandText = sql; 87                         return cmd.ExecuteNonQuery(); 88                     } 89                 } 90             } 91             catch (Exception ex) 92             { 93                 throw new Exception(ex.Message); 94             } 95         } 96  97         /// <summary> 98         /// 執行TSQL 語句并返回受影響的行  99         /// </summary>100         /// <param name="sql">需要執行的sql語句</param>101         /// <param name="paramList">參數的泛型集合</param>102         /// <returns></returns>103         public static int ExecuteNonQuery(string sql, List<Parameter> paramList)104         {105             try106             {107                 using (SqlConnection conn = new SqlConnection(connStr))108                 {109                     conn.Open();110                     using (SqlCommand cmd = conn.CreateCommand())111                     {112                         cmd.CommandText = sql;113                         cmd.Parameters.AddRange(GetSqlParameterToArr(paramList));114                         return cmd.ExecuteNonQuery();115                     }116                 }117             }118             catch (Exception ex)119             {120                 throw new Exception(ex.Message);121             }122         }123 124 125         /// <summary>126         /// 執行查詢，并返回查詢所返回的結果集中第一行的第一列 127         /// </summary>128         /// <param name="sql">需要執行的sql語句</param>129         /// <returns></returns>130 131         public static object ExecuteScalar(string sql)132         {133             try134             {135                 using (SqlConnection conn = new SqlConnection(connStr))136                 {137                     conn.Open();138                     using (SqlCommand cmd = conn.CreateCommand())139                     {140                         cmd.CommandText = sql;141                         return cmd.ExecuteScalar();142                     }143                 }144             }145             catch (Exception ex)146             {147                 throw new Exception(ex.Message);148             }149         }150         /// <summary>151         /// 執行查詢，并返回查詢所返回的結果集中第一行的第一列 152         /// </summary>153         /// <param name="sql">需要執行的sql語句</param>154         /// <param name="paramList">參數的泛型集合</param>155         /// <returns></returns>156         public static object ExecuteScalar(string sql, List<Parameter> paramList)157         {158             try159             {160                 using (SqlConnection conn = new SqlConnection(connStr))161                 {162                     conn.Open();163                     using (SqlCommand cmd = conn.CreateCommand())164                     {165                         cmd.CommandText = sql;166                         cmd.Parameters.AddRange(GetSqlParameterToArr(paramList));167                         return cmd.ExecuteScalar();168                     }169                 }170             }171             catch (Exception ex)172             {173                 throw new Exception(ex.Message);174             }175         }176 177 178         /// <summary>179         /// 返回已經填充結果的DataSet 180         /// </summary>181         /// <param name="sql">需要執行的sql語句</param>182         /// <returns></returns>183 184         public static DataSet ExecuteDataSet(string sql)185         {186             try187             {188                 using (SqlConnection conn = new SqlConnection(connStr))189                 {190                     conn.Open();191                     using (SqlCommand cmd = conn.CreateCommand())192                     {193                         cmd.CommandText = sql;194                         SqlDataAdapter adapter = new SqlDataAdapter(cmd);195                         DataSet dataset = new DataSet();196                         adapter.Fill(dataset);197                         return dataset;198                     }199                 }200             }201             catch (Exception ex)202             {203                 throw new Exception(ex.Message);204             }205         }206 207         /// <summary>208         /// 返回已經填充結果的DataSet 209         /// </summary>210         /// <param name="sql">需要執行的sql語句</param>211         /// <param name="paramList">參數的泛型集合</param>212         /// <returns></returns>213         public static DataSet ExecuteDataSet(string sql, List<Parameter> paramList)214         {215             try216             {217                 using (SqlConnection conn = new SqlConnection(connStr))218                 {219                     conn.Open();220                     using (SqlCommand cmd = conn.CreateCommand())221                     {222                         cmd.CommandText = sql;223                         cmd.Parameters.AddRange(GetSqlParameterToArr(paramList));224                         SqlDataAdapter adapter = new SqlDataAdapter(cmd);225                         DataSet dataset = new DataSet();226                         adapter.Fill(dataset);227                         return dataset;228                     }229                 }230             }231             catch (Exception ex)232             {233                 throw new Exception(ex.Message);234             }235         }236 237 238         /// <summary>239         /// 返回查詢結果集所返回的字段值的泛型集合 240         /// </summary>241         /// <param name="sql">需要執行的sql語句</param>242         /// <returns></returns>243 244         public static List<object> ExecuteReader(string sql)245         {246             List<object> obj = new List<object>();247             try248             {249                 using (SqlConnection conn = new SqlConnection(connStr))250                 {251                     conn.Open();252                     using (SqlCommand cmd = conn.CreateCommand())253                     {254                         cmd.CommandText = sql;255                         using (SqlDataReader reader = cmd.ExecuteReader())256                         {257                             while (reader.Read())258                             {259                                 for (int i = 0; i < reader.FieldCount; i++)260                                 {261                                     obj.Add(reader.IsDBNull(i) ? "空值" : reader.GetValue(i));262                                 }263                             }264                             return obj;265                         }266                     }267                 }268             }269             catch (Exception ex)270             {271                 throw new Exception(ex.Message);272             }273         }274 275 276         /// <summary>277         /// 返回查詢結果集所返回的字段值的泛型集合 278         /// </summary>279         /// <param name="sql">需要執行的sql語句</param>280         /// <param name="paramList">參數的泛型集合</param>281         /// <returns></returns>282         public static List<object> ExecuteReader(string sql, List<Parameter> paramList)283         {284             List<object> obj = new List<object>();285             try286             {287                 using (SqlConnection conn = new SqlConnection(connStr))288                 {289                     conn.Open();290                     using (SqlCommand cmd = conn.CreateCommand())291                     {292                         cmd.CommandText = sql;293                         cmd.Parameters.AddRange(GetSqlParameterToArr(paramList));294                         using (SqlDataReader reader = cmd.ExecuteReader())295                         {296                             while (reader.Read())297                             {298                                 for (int i = 0; i < reader.FieldCount; i++)299                                 {300                                     obj.Add(reader.IsDBNull(i) ? "空值" : reader.GetValue(i));301                                 }302                             }303                             return obj;304                         }305                     }306                 }307             }308             catch (Exception ex)309             {310                 throw new Exception(ex.Message);311             }312         }313 314 315         /// <summary>316         /// 獲取SqlServer數據庫實例名數組 317         /// </summary>318         /// <returns></returns>319         public static string[] GetInstances()320         {321             RegistryKey reg = Registry.LocalMachine.OpenSubKey(@"SOFTWARE/Microsoft/Microsoft SQL Server");322             string[] instances = (string[])reg.GetValue("InstalledInstances", "");323             try324             {325                 if (instances.Length > 0)326                 {327                     for (int i = 0; i < instances.Length; i++)328                     {329                         if (instances[i] == "MSSQLSERVER")330                         {331                             instances[i] = System.Environment.MachineName;332                         }333                         else334                         {335                             instances[i] = System.Environment.MachineName + @"/" + instances[i];336                         }337                     }338                 }339                 return instances;340             }341             catch (Exception ex)342             {343                 throw new Exception(ex.Message);344             }345         }346     }347 }

測試：
1、獲取實例

窗體拖入ComboBox控件，設置name值為cbx_server

引入SqlHelper

using SqlHelp

窗體load事件加入：

1 cbx_server.Items .AddRange ( GetInstances());

2、執行帶參數查詢方法

窗體拖入按鈕，name為Bt_Test，并且拖入TextBox控件，name值為txt_Param

引入SqlHelper

using SqlHelp

在按鈕點擊事件中加入：

 1         private void Bt_Test_Click(object sender, EventArgs e) 2         { 3             SqlHelper.ConnStr = @"Data Source=localhost;Initial Catalog=UFsystem;Integrated Security=True"; 4             Parameter param = new Parameter("@id", txt_Param.Text); 5             List<Parameter> list = new List<Parameter>(); 6             list.Add(param); 7             List<object> obj = SqlHelper.ExecuteReader(@"select * from ua_user where cuser_id=@id", list); 8             foreach (var item in obj) 9             {10                 Console.WriteLine(item);11             }12         }