public static class SqlHelper
{
public static string connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
public static int ExecuteNonQuery(string cmdText)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
return cmd.ExecuteNonQuery();
}
}
}
public static int ExecuteNonQuery(string cmdText, CommandType cmdType, SqlParameter[] cmdParms)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
if (cmdParms != null)
cmd.Parameters.AddRange(cmdParms);
cmd.CommandType = cmdType;
conn.Open();
return cmd.ExecuteNonQuery();
}
}
}
public static int ExecuteNonQuery(SqlCommand cmd, CommandType cmdType)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
cmd.CommandType = cmdType;
cmd.Connection = conn;
conn.Open();
return cmd.ExecuteNonQuery();
}
}
public static object ExecuteScalar(string cmdText)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
conn.Open();
return cmd.ExecuteScalar();
}
}
}
public static object ExecuteScalar(string cmdText, CommandType cmdType, SqlParameter[] cmdParms)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
cmd.CommandType = cmdType;
if (cmdParms != null)
cmd.Parameters.AddRange(cmdParms);
conn.Open();
return cmd.ExecuteScalar();
}
}
}
public static object ExecuteScalar(SqlCommand cmd, CommandType cmdType)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
cmd.CommandType = cmdType;
cmd.Connection = conn;
conn.Open();
return cmd.ExecuteScalar();
}
}
public static SqlDataReader ExecuteReader(string cmdText)
{
SqlConnection conn = new SqlConnection(connstr);
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
try
{
conn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch
{//可能会出现异常,没有using,必须手动关闭
conn.Close();
conn.Dispose();
throw;
}
}
}
public static SqlDataReader ExecuteReader(string cmdText, CommandType cmdType, SqlParameter[] cmdParms)
{
SqlConnection conn = new SqlConnection(connstr);
using (SqlCommand cmd = new SqlCommand(cmdText, conn))
{
cmd.CommandType = cmdType;
if (cmdParms != null)
cmd.Parameters.AddRange(cmdParms);
try
{
conn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch
{//可能会出现异常,没有using,必须手动关闭
conn.Close();
conn.Dispose();
throw;
}
}
}
public static SqlDataReader ExecuteReader(SqlCommand cmd, CommandType cmdType)
{
SqlConnection conn = new SqlConnection(connstr);
cmd.CommandType = cmdType;
cmd.Connection = conn;
try
{
conn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch
{
conn.Close();
conn.Dispose();
throw;
}
}
public static DataTable GetDataTable(string cmdText)
{
DataTable set = new DataTable();
using (SqlConnection conn = new SqlConnection(connstr))
{
using (SqlDataAdapter adp = new SqlDataAdapter(cmdText, conn))
{
adp.Fill(set);
return set;
}
}
}
public static DataTable GetDataTable(string cmdText, CommandType cmdType, SqlParameter[] cmdParms)
{
DataTable set = new DataTable();
using (SqlConnection conn = new SqlConnection(connstr))
{
using (SqlDataAdapter adp = new SqlDataAdapter(cmdText, conn))
{
adp.SelectCommand.CommandType = cmdType;
if(cmdParms != null)
adp.SelectCommand.Parameters.AddRange(cmdParms);
adp.Fill(set);
return set;
}
}
}
public static DataTable GetDataTable(SqlCommand cmd, CommandType cmdType)
{
DataTable set = new DataTable();
using (SqlConnection conn = new SqlConnection(connstr))
{
cmd.Connection = conn;
cmd.CommandType = cmdType;
using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
{
adp.Fill(set);
return set;
}
}
}
public static DataSet GetDataSet(string sqlstr)
{
DataSet set = new DataSet();
using (SqlConnection conn = new SqlConnection(connstr))
{
using (SqlDataAdapter adp = new SqlDataAdapter(sqlstr, conn))
{
adp.Fill(set);
return set;
}
}
}
public static DataSet GetDataSet(string sqlstr, string tableName)
{
DataSet set = new DataSet();
using (SqlConnection conn = new SqlConnection(connstr))
{
using (SqlDataAdapter adp = new SqlDataAdapter(sqlstr, conn))
{
adp.Fill(set, tableName);
return set;
}
}
}
public static DataSet GetDataSet(SqlCommand cmd, CommandType cmdType, string tableName)
{
DataSet set = new DataSet();
using (SqlConnection conn = new SqlConnection(connstr))
{
cmd.Connection = conn;
cmd.CommandType = cmdType;
using (SqlDataAdapter adp = new SqlDataAdapter(cmd))
{
adp.Fill(set, tableName);
}
}
return set;
}
}