public static classSqlHelper
{///
///获取连接字符串///
/// 连接字符串
public static stringGetSqlConnectionString()
{return ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
}///
///封装一个执行的sql 返回受影响的行数///
/// 执行的sql脚本
/// 参数集合
/// 受影响的行数
public static int ExecuteNonQuery(string sqlText, paramsSqlParameter[] parameters)
{using (SqlConnection conn = newSqlConnection(GetSqlConnectionString()))
{using (SqlCommand cmd =conn.CreateCommand())
{
conn.Open();
cmd.CommandText=sqlText;
cmd.Parameters.AddRange(parameters);returncmd.ExecuteNonQuery();
}
}
}///
///执行sql,返回查询结果中的第一行第一列的值///
/// 执行的sql脚本
/// 参数集合
/// 查询结果中的第一行第一列的值
public static object ExecuteScalar(string sqlText, paramsSqlParameter[] parameters)
{using (SqlConnection conn = newSqlConnection(GetSqlConnectionString()))
{using (SqlCommand cmd =conn.CreateCommand())
{
conn.Open();
cmd.CommandText=sqlText;
cmd.Parameters.AddRange(parameters);returncmd.ExecuteScalar();
}
}
}///
///执行sql 返回一个DataTable///
/// 执行的sql脚本
/// 参数集合
/// 返回一个DataTable
public static DataTable ExecuteDataTable(string sqlText, paramsSqlParameter[] parameters)
{using (SqlDataAdapter adapter = newSqlDataAdapter(sqlText, GetSqlConnectionString()))
{
DataTable dt= newDataTable();
adapter.SelectCommand.Parameters.AddRange(parameters);
adapter.Fill(dt);returndt;
}
}///
///执行sql脚本///
/// 执行的sql脚本
/// 参数集合
/// 返回一个SqlDataReader
public static SqlDataReader ExecuteReader(string sqlText, paramsSqlParameter[] parameters)
{//SqlDataReader要求,它读取数据的时候有,它独占它的SqlConnection对象,而且SqlConnection必须是Open状态
SqlConnection conn = new SqlConnection(GetSqlConnectionString());//不要释放连接,因为后面还需要连接打开状态
SqlCommand cmd =conn.CreateCommand();
conn.Open();
cmd.CommandText=sqlText;
cmd.Parameters.AddRange(parameters);//CommandBehavior.CloseConnection当SqlDataReader释放的时候,顺便把SqlConnection对象也释放掉
returncmd.ExecuteReader(CommandBehavior.CloseConnection);
}
}