sqlserver封装类SqlHelper及使用

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("登陆失败");
          }