SQLHelp帮助类

public readonly static string connStr = ConfigurationManager.ConnectionStrings["conn"].ConnectionString; //从 web.config中获得
/// <summary>
/// 执行查询，返回datatable
/// </summary>
/// <param name="text"></param>
/// <param name="ct"></param>
/// <param name="param"></param>
/// <returns></returns>
public static DataTable ExecuteTable(string text, CommandType ct, params SqlParameter[] param)
{
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlDataAdapter sda = new SqlDataAdapter(text, conn))
{
sda.SelectCommand.CommandType = ct;
sda.SelectCommand.Parameters.AddRange(param);
sda.Fill(dt);
return dt;
}
}
}
/// <summary>
/// 执行增删改类似于在数据库中执行了语句返回受影响的行数
/// </summary>
/// <param name="text"></param>
/// <param name="ct"></param>
/// <param name="param"></param>
/// <returns></returns>
public static int ExecuteNonQuery(string text, CommandType ct, params SqlParameter[] param)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand com = new SqlCommand(text, conn))
{
com.CommandType = ct;
com.Parameters.AddRange(param);
conn.Open();
return com.ExecuteNonQuery();
}
}
}
/// <summary>
/// 执行查询返回首行首列返回的首行首列数据类型还是object类型的
/// </summary>
/// <param name="text"></param>
/// <param name="ct"></param>
/// <param name="param"></param>
/// <returns></returns>
public static object ExecuteScalar(string text, CommandType ct, params SqlParameter[] param)
{
using (SqlConnection conn = new SqlConnection(connStr))
{
using (SqlCommand com = new SqlCommand(text, conn))
{
com.CommandType = ct;
com.Parameters.AddRange(param);
conn.Open();
return com.ExecuteScalar();
}
}
}
/// <summary>
///单向只读
/// </summary>
/// <param name="text"></param>
/// <param name="ct"></param>
/// <param name="param"></param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(string text, CommandType ct, params SqlParameter[] param)
{
SqlConnection conn = new SqlConnection();
using (SqlCommand com=new SqlCommand(text,conn))
{
com.CommandType = ct;
com.Parameters.AddRange(param);
conn.Open();
return com.ExecuteReader(CommandBehavior.CloseConnection);
}
}
}