using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

namespace DAL {   

  public class SqlHelper   

  {        //这里最好用配置文件

              private readonly static string connstr = "Data Source=【数据库连接的地址或名字,一般为localhost】;Initial Catalog=【数据库名字】;Integrated Security=True";

        public static int ExecuteNonQuery(string cmdText, params SqlParameter[] parameter)

        {   //使用using可以自动释放资源

              using (SqlConnection conn = new SqlConnection(connstr))   

             {                 conn.Open();      //打开数据库连接

           using (SqlCommand cmd= conn.CreateCommand())   //创建连接命令   

             {                  cmd.CommandText = cmdText;      //设置连接命令的SQL语句  

                                 cmd.Parameters.AddRange(parameter);//参数化使用

                                return cmd.ExecuteNonQuery();    //返回执行受影响的行数      

       }        

     }        

}

        public static object ExecuteScalar(string cmdText, params SqlParameter[] parameter)

        {    

               using (SqlConnection conn = new SqlConnection(connstr))         

              {                 conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())     

                {                     cmd.CommandText = cmdText;      

                                      cmd.Parameters.AddRange(parameter);    

                                      return cmd.ExecuteScalar();     

                }           

          }     

     }

 

public static DataTable ExecuteDataTable(string cmdText, params SqlParameter[] parameters)
{

using (SqlConnection conn = new SqlConnection(connstr))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
cmd.Parameters.AddRange(parameters);
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
}
}
}

public static SqlDataReader ExecuteDataReader(string cmdText, params SqlParameter[] parameters)
{

using (SqlConnection conn = new SqlConnection(connstr))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
cmd.Parameters.AddRange(parameters);
return cmd.EndExecuteReader();
}
}
}

        public static DataSet GetList(string cmdText, params SqlParameter[] parameter)

        {             using (SqlConnection conn = new SqlConnection(connstr))        

                      {                 conn.Open();

                            using (SqlCommand cmd = conn.CreateCommand())                

                            {           cmd.CommandText = cmdText;          

                                           cmd.Parameters.AddRange(parameter);    

                                             using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))         

                                             {           

                                                      DataSet ds = new DataSet();            

                                                       adapter.Fill(ds);             

                                                      return ds;              

                                               }                   

                  }  

           }     

    }  

   }

}