SQLHelper

public static class SqlHelper
{
private static readonly string conStr = ConfigurationManager.ConnectionStrings["mssqlserver"].ConnectionString;

//insert delete update
public static int ExecuteNonQuery(string sql, CommandType cmdType, params SqlParameter[] pms)
{
using (SqlConnection con = new SqlConnection(conStr))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.CommandType = cmdType;
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
con.Open();
return cmd.ExecuteNonQuery();
}
}
}

//返回单个值
public static object ExecuteScalar(string sql, CommandType cmdType, params SqlParameter[] pms)
{
using (SqlConnection con = new SqlConnection(conStr))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.CommandType = cmdType;
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
con.Open();
return cmd.ExecuteScalar();
}
}
}

//执行返回DataReader
public static SqlDataReader ExecuteReader(string sql, CommandType cmdType, params SqlParameter[] pms)
{
SqlConnection con = new SqlConnection(conStr);
using (SqlCommand cmd = new SqlCommand(sql, con))
{
cmd.CommandType = cmdType;
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
//con.Open();
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch
{
con.Close();
con.Dispose();
throw;
}
}
}

public static DataTable ExecuteDataTable(string sql, CommandType cmdType, params SqlParameter[] pms)
{
DataTable dt = new DataTable();
using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conStr))
{
adapter.SelectCommand.CommandType = cmdType;
if (pms != null)
{
adapter.SelectCommand.Parameters.AddRange(pms);
}
adapter.Fill(dt);
}

return dt;
}

}

DataSet 
作用：DataSet，DataAdapter读取数据。 
问：什么是DataAdapter？
答：DataAdapter对象在DataSet与数据之间起桥梁作用

string strConn="uid=账号;pwd=密码;database=数据库;server=服务器";//SQL Server链接字符串
SqlConnection ConnSql=new SqlConnection (strConn); //Sql链接类的实例化
ConnSql.Open ();//打开数据库
string strSQL="SELECT * FROM 表名1 "; //要执行的SQL语句 
SqlDataAdapter da=new SqlDataAdapter(strSQL,ConnSql); //创建DataAdapter数据适配器实例
DataSet ds=new DataSet();//创建DataSet实例
da.Fill(ds,"自定义虚拟表名");//使用DataAdapter的Fill方法(填充)，调用SELECT命令
ConnSql.Close ();//关闭数据库

SQLHelper

标签：