public static classSQLHelp
{private static readonly string conStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;///
///返回执行增加、删除、修改操作后造成影响的行数///
/// 要执行的Sql语句
/// 要执行的命令类型
/// 传入的参数
///
public static int ExecuteNonQuery(string sql, CommandType cmdType, paramsSqlParameter[] pms)
{using (SqlConnection con = newSqlConnection(conStr))
{using (SqlCommand cmd = newSqlCommand(sql, con))
{
cmd.CommandType=cmdType;if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
con.Open();returncmd.ExecuteNonQuery();
}
}
}///
///返回数据库查询结果首行首列的值///
/// 要执行的Sql语句
/// 要执行的命令类型
/// 传入的参数
///
public static object ExecuteScalar(string sql, CommandType cmdType, paramsSqlParameter[] pms)
{using (SqlConnection con = newSqlConnection(conStr))
{using (SqlCommand cmd = newSqlCommand(sql, con))
{
cmd.CommandType=cmdType;if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
con.Open();returncmd.ExecuteScalar();
}
}
}///
///返回SqlDataReader对象///
/// 要执行的Sql语句
/// 要执行的命令类型
/// 传入的参数
///
public static SqlDataReader ExecuteReader(string sql, CommandType cmdType, paramsSqlParameter[] pms)
{
SqlConnection con= newSqlConnection(conStr);using (SqlCommand cmd = newSqlCommand(sql, con))
{
cmd.CommandType=cmdType;if (pms != null)
{
cmd.Parameters.AddRange(pms);
}try{
con.Open();returncmd.ExecuteReader(CommandBehavior.CloseConnection);
}catch(Exception)
{
con.Close();
con.Dispose();throw;
}
}
}///
///封装一个返回DataTable对象的方法///
/// 要执行的Sql语句
/// 要执行的命令类型
/// 传入的参数
///
public static DataTable ExecuteDataTable(string sql, CommandType cmdType, paramsSqlParameter[] pms)
{
DataTable dt= newDataTable();using (SqlDataAdapter adapter = newSqlDataAdapter(sql, conStr))
{
adapter.SelectCommand.CommandType=cmdType;if (pms != null)
{
adapter.SelectCommand.Parameters.AddRange(pms);
}
adapter.Fill(dt);
}returndt;
}
}