1usingSystem;2usingSystem.Collections.Generic;3usingSystem.Text;4usingSystem.Data.SqlClient;5usingSystem.Data;6usingMicrosoft.Win32;78namespaceSqlHelp9{10///<summary>11///定义SqlParameter所需的参数对象12///</summary>13publicclassParameter14{15///<summary>16///参数集合构造函数17///</summary>18///<param name="paramname">参数名称</param>19///<param name="value">参数所对应的对象的值</param>20publicParameter(stringparamname,objectvalue)21{22this.ParamName =paramname;23this.Obj =value;24}25///<summary>26///参数名称27///</summary>28publicstringParamName29{30get;31set;32}33///<summary>34///参数名称所对应的对象的值35///</summary>36publicobjectObj37{38get;39set;40}41}42///<summary>43///SqlHelper ^_^ !44///</summary>45publicclassSqlHelper46{47///<summary>48///连接字符串字段49///</summary>50PRivatestaticstringconnStr;5152///<summary>53///SQL连接字符串属性54///</summary>55publicstaticstringConnStr56{57get{returnSqlHelper.connStr; }58set{ SqlHelper.connStr =value; }59}6061privatestaticSqlParameter[] GetSqlParameterToArr(List<Parameter>listP)62{63List<SqlParameter> list =newList<SqlParameter>();64foreach(variteminlistP)65{66list.Add(newSqlParameter(item.ParamName, item.Obj));67}68returnlist.ToArray();69}7071///<summary>72///执行TSQL 语句并返回受影响的行73///</summary>74///<param name="sql">需要执行的sql语句</param>75///<returns></returns>7677publicstaticintExecuteNonQuery(stringsql)78{79try80{81using(SqlConnection conn =newSqlConnection(connStr))82{83conn.Open();84using(SqlCommand cmd =conn.CreateCommand())85{86cmd.CommandText =sql;87returncmd.ExecuteNonQuery();88}89}90}91catch(Exception ex)92{93thrownewException(ex.Message);94}95}9697///<summary>98///执行TSQL 语句并返回受影响的行99///</summary>100///<param name="sql">需要执行的sql语句</param>101///<param name="paramList">参数的泛型集合</param>102///<returns></returns>103publicstaticintExecuteNonQuery(stringsql, List<Parameter>paramList)104{105try106{107using(SqlConnection conn =newSqlConnection(connStr))108{109conn.Open();110using(SqlCommand cmd =conn.CreateCommand())111{112cmd.CommandText =sql;113cmd.Parameters.AddRange(GetSqlParameterToArr(paramList));114returncmd.ExecuteNonQuery();115}116}117}118catch(Exception ex)119{120thrownewException(ex.Message);121}122}123124125///<summary>126///执行查询，并返回查询所返回的结果集中第一行的第一列127///</summary>128///<param name="sql">需要执行的sql语句</param>129///<returns></returns>130131publicstaticobjectExecuteScalar(stringsql)132{133try134{135using(SqlConnection conn =newSqlConnection(connStr))136{137conn.Open();138using(SqlCommand cmd =conn.CreateCommand())139{140cmd.CommandText =sql;141returncmd.ExecuteScalar();142}143}144}145catch(Exception ex)146{147thrownewException(ex.Message);148}149}150///<summary>151///执行查询，并返回查询所返回的结果集中第一行的第一列152///</summary>153///<param name="sql">需要执行的sql语句</param>154///<param name="paramList">参数的泛型集合</param>155///<returns></returns>156publicstaticobjectExecuteScalar(stringsql, List<Parameter>paramList)157{158try159{160using(SqlConnection conn =newSqlConnection(connStr))161{162conn.Open();163using(SqlCommand cmd =conn.CreateCommand())164{165cmd.CommandText =sql;166cmd.Parameters.AddRange(GetSqlParameterToArr(paramList));167returncmd.ExecuteScalar();168}169}170}171catch(Exception ex)172{173thrownewException(ex.Message);174}175}176177178///<summary>179///返回已经填充结果的DataSet180///</summary>181///<param name="sql">需要执行的sql语句</param>182///<returns></returns>183184publicstaticDataSet ExecuteDataSet(stringsql)185{186try187{188using(SqlConnection conn =newSqlConnection(connStr))189{190conn.Open();191using(SqlCommand cmd =conn.CreateCommand())192{193cmd.CommandText =sql;194SqlDataAdapter adapter =newSqlDataAdapter(cmd);195DataSet dataset =newDataSet();196adapter.Fill(dataset);197returndataset;198}199}200}201catch(Exception ex)202{203thrownewException(ex.Message);204}205}206207///<summary>208///返回已经填充结果的DataSet209///</summary>210///<param name="sql">需要执行的sql语句</param>211///<param name="paramList">参数的泛型集合</param>212///<returns></returns>213publicstaticDataSet ExecuteDataSet(stringsql, List<Parameter>paramList)214{215try216{217using(SqlConnection conn =newSqlConnection(connStr))218{219conn.Open();220using(SqlCommand cmd =conn.CreateCommand())221{222cmd.CommandText =sql;223cmd.Parameters.AddRange(GetSqlParameterToArr(paramList));224SqlDataAdapter adapter =newSqlDataAdapter(cmd);225DataSet dataset =newDataSet();226adapter.Fill(dataset);227returndataset;228}229}230}231catch(Exception ex)232{233thrownewException(ex.Message);234}235}236237238///<summary>239///返回查询结果集所返回的字段值的泛型集合240///</summary>241///<param name="sql">需要执行的sql语句</param>242///<returns></returns>243244publicstaticList<object> ExecuteReader(stringsql)245{246List<object> obj =newList<object>();247try248{249using(SqlConnection conn =newSqlConnection(connStr))250{251conn.Open();252using(SqlCommand cmd =conn.CreateCommand())253{254cmd.CommandText =sql;255using(SqlDataReader reader =cmd.ExecuteReader())256{257while(reader.Read())258{259for(inti =0; i < reader.FieldCount; i++)260{261obj.Add(reader.IsDBNull(i) ?"空值": reader.GetValue(i));262}263}264returnobj;265}266}267}268}269catch(Exception ex)270{271thrownewException(ex.Message);272}273}274275276///<summary>277///返回查询结果集所返回的字段值的泛型集合278///</summary>279///<param name="sql">需要执行的sql语句</param>280///<param name="paramList">参数的泛型集合</param>281///<returns></returns>282publicstaticList<object> ExecuteReader(stringsql, List<Parameter>paramList)283{284List<object> obj =newList<object>();285try286{287using(SqlConnection conn =newSqlConnection(connStr))288{289conn.Open();290using(SqlCommand cmd =conn.CreateCommand())291{292cmd.CommandText =sql;293cmd.Parameters.AddRange(GetSqlParameterToArr(paramList));294using(SqlDataReader reader =cmd.ExecuteReader())295{296while(reader.Read())297{298for(inti =0; i < reader.FieldCount; i++)299{300obj.Add(reader.IsDBNull(i) ?"空值": reader.GetValue(i));301}302}303returnobj;304}305}306}307}308catch(Exception ex)309{310thrownewException(ex.Message);311}312}313314315///<summary>316///获取SqlServer数据库实例名数组317///</summary>318///<returns></returns>319publicstaticstring[] GetInstances()320{321RegistryKey reg = Registry.LocalMachine.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server");322string[] instances = (string[])reg.GetValue("InstalledInstances","");323try324{325if(instances.Length >0)326{327for(inti =0; i < instances.Length; i++)328{329if(instances[i] =="MSSQLSERVER")330{331instances[i] =System.Environment.MachineName;332}333else334{335instances[i] = System.Environment.MachineName +@"\"+instances[i];336}337}338}339returninstances;340}341catch(Exception ex)342{343thrownewException(ex.Message);344}345}346}347}
测试：
1、获取实例
窗体拖入ComboBox控件，设置name值为cbx_server
引入SqlHelper
using SqlHelp
窗体load事件加入：
1cbx_server.Items .AddRange ( GetInstances());
2、执行带参数查询方法
窗体拖入按钮，name为Bt_Test，并且拖入TextBox控件，name值为txt_Param
引入SqlHelper
using SqlHelp
在按钮点击事件中加入：
1privatevoidBt_Test_Click(objectsender, EventArgs e)2{3SqlHelper.ConnStr =@"Data Source=localhost;Initial Catalog=UFsystem;Integrated Security=True";4Parameter param =newParameter("@id", txt_Param.Text);5List<Parameter> list =newList<Parameter>();6list.Add(param);7List<object> obj = SqlHelper.ExecuteReader(@"select * from ua_user where cuser_id=@id", list);8foreach(variteminobj)9{10Console.WriteLine(item);11}12}
输出：
admin
admin
空值
True
空值
空值
空值
空值
正在学习c#，有什么地方不对或不合适的请指教。
