using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.IO;
public static class SqlHelper
{
//config配置文件
private static readonly string constr = ConfigurationManager.ConnectionStrings["myconnStr"].ConnectionString;
//执行增删改
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();
}
}
}
//返回单个值的方法 返回首行首列
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();
}
}
}
//封装返回DataReader的方法
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);
}
con.Open();
//当datareader关闭,对应的连接关闭
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}
//封装一个执行DataTable的方法 (SqlDataAdapter 不需要SqlConnection
public static DataTable ExecuteDataTable(string sql, params SqlParameter[] pms)
{
DataTable dt = new DataTable();
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, constr))
{
if (pms != null)
{
adapter.SelectCommand.Parameters.AddRange(pms);
}
adapter.FillSchema(dt, SchemaType.Source);//获得表信息必须要写,可以不写,加上无害
adapter.Fill(dt);