sqlserver封装类SqlHelper及使用

来源：互联网发布：js 读取数字证书编辑：程序博客网时间：2024/05/20 01:08

sqlserver封装类SqlHelper及使用

1.首先在App.config中添加链接数据库的字符串（没有App.config的话，添加应用程序配置文件）

configuration>  <connectionStrings>    <add name="sqlconnect" connectionString="Data Source=localhost;Initial catalog=数据库名;User Id=用户名;Password=密码"/>  </connectionStrings>    <startup>         <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />    </startup></configuration>

2.新建类SqlHelper.cs
添加引用
这里写图片描述

引入命名空间using System.Configuration;

  public static class SqlHelper    {       private static readonly string constr = ConfigurationManager.ConnectionStrings["sqlconnect"].ConnectionString;       //1.执行增删改       public static int ExecuteNonQuery(string sql,params SqlParameter[] pms)       {           using(SqlConnection con=new SqlConnection (constr))           {               using(SqlCommand cmd=new SqlCommand (sql,con))               {                    if (pms!=null)                   {                    cmd.Parameters.AddRange(pms);                    }                    con.Open();                    return cmd.ExecuteNonQuery();               }           }       }       //2.执行查询返回单个值的方法       public static object ExecuteScalar(string sql, params SqlParameter[] pms)       {           using (SqlConnection con = new SqlConnection(constr))           {               using (SqlCommand cmd = new SqlCommand(sql,con))               {                   if (pms != null)                   {                       cmd.Parameters.AddRange(pms);                   }                   con.Open();                   return cmd.ExecuteScalar();               }           }       }       //3.执行查询 返回多行多列的方法       public static  SqlDataReader ExecuteReader( string sql,params SqlParameter[] pms)       {               SqlConnection con = new SqlConnection(constr);               using(SqlCommand cmd=new SqlCommand (sql,con))               {                   if (pms!=null)                   {                       cmd.Parameters.AddRange(pms);                   }                   try                   {                       con.Open();                       //System.Data.CommandBehavior.CloseConnection,在关闭reader的同时，在sqlreader内部会将关联的Connection对象也关闭掉                       return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);                   }                   catch                   {                       con.Close();                       con.Dispose();                       throw;                   }               }           }      //4.查询数据返回DataTable       public static DataTable ExecuteDataTable(string sql, params SqlParameter[] pms)       {           DataTable datatable = new DataTable();           using(SqlDataAdapter adpter=new SqlDataAdapter (sql,constr))           {               if (pms!=null)               {                   adpter.SelectCommand.Parameters.AddRange(pms);               }               adpter.Fill(datatable);           }           return datatable;       }            }

3.模拟登陆
这里写图片描述

      private void login_Click(object sender, RoutedEventArgs e)        {            string sqlstring = "select count(*) from user_table where loginID=@loginID and loginPsw=@loginPsw ";            SqlParameter[] pms = new SqlParameter[]{                new SqlParameter("@loginID",SqlDbType.NVarChar,50){Value=loginuser.Text.Trim()},                new SqlParameter("@loginPsw",SqlDbType.NVarChar,50){Value=loginpsw.Password},            };          int r=  (int)SqlHelper.ExecuteScalar(sqlstring, pms);          if (r>0)          {               MessageBox.Show("登陆成功");          }          else          {              MessageBox.Show("登陆失败");          }

阅读全文

0 0