你的位置:首页 > ASP.net教程

[ASP.net教程]菜鸟学习Ado.net笔记一:Ado.net学习之SqlHelper类


 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     }

输出:

admin
admin
空值
True
空值
空值
空值
空值

正在学习c#,有什么地方不对或不合适的请指教。