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;
}
}
}
}
}
}