DBHelper
public class DBHelper
{
//using System.Data.SqlClient;需要导入相应的命名空间
//-------不带参数---------------------------------------------------------------------------------------------------
/// <summary>
/// 创建数据库连接的方法
/// </summary>
/// <returns>SqlConnection</returns>
public static SqlConnection createConn()
{
string conn_str = ConfigurationManager.ConnectionStrings["MyApplication"].ConnectionString;
SqlConnection conn = new SqlConnection(conn_str);
return conn;
}
/// <summary>
/// 获取执行查询获取DataReader的方法
/// </summary>
/// <param name="cmdText">sql字符串或存储过程名称</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader getDataReader(string cmdText)
{
SqlConnection conn = DBHelper.createConn();
try
{
conn.Open();
SqlCommand cmd = new SqlCommand(cmdText, conn);
SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return sdr;
}
catch
{
conn.Close();
throw;
}
}
/// <summary>
/// 执行sql语句的方法
/// </summary>
/// <param name="cmdText">sql字符串或存储过程名称</param>
public static int executeSql(string cmdText)
{
SqlConnection conn = DBHelper.createConn();
SqlCommand cmd = new SqlCommand(cmdText, conn);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
int temp = cmd.ExecuteNonQuery();
conn.Close();
return temp;
}
/// <summary>
/// 执行sql语句获得第一行第一列的字符串
/// </summary>
/// <param name="cmdText">sql字符串或存储过程名称</param>
/// <returns>查询的结果</returns>
public static string executeScalarStr(string cmdText)
{
SqlConnection conn = DBHelper.createConn();
SqlCommand cmd = new SqlCommand(cmdText, conn);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
string temp = Convert.ToString(cmd.ExecuteScalar());
conn.Close();
return temp;
}
/// <summary>
/// 执行sql语句获得第一行第一列的数字,用来获得count()结果
/// </summary>
/// <param name="cmdText">sql字符串或存储过程名称</param>
/// <returns>查询的结果数字</returns>
public static int executeScalar(string cmdText)
{
SqlConnection conn = DBHelper.createConn();
SqlCommand cmd = new SqlCommand(cmdText, conn);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
int temp = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
return temp;
}
/// <summary>
/// 无参数的存储过程的处理方法。
/// </summary>
/// <param name="conn">数据库连接</param>
/// <param name="cmd">根据数据库连接和存储过程创建的SqlCommand对象</param>
/// <param name="cmdText">存储过程名称</param>
private static void PrepareCommandPro(SqlConnection conn, SqlCommand cmd, string cmdText)
{
//判断连接的状态。如果是关闭状态，则打开
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
//cmd属性赋值
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.StoredProcedure;
}
/// <summary>
/// 返回一个数据集
/// </summary>
/// <param name="cmdStr">sql语句</param>
/// <returns></returns>
public static DataSet getDataSet(string cmdStr)
{
SqlConnection con = DBHelper.createConn();
try
{
con.Open();
SqlDataAdapter sda = new SqlDataAdapter(cmdStr, con);
DataSet ds = new DataSet();
sda.Fill(ds, "table");
return ds;
}
catch
{
throw;
}
finally
{
con.Close();
}
}
/// <summary>
/// 无参数的存储过程的执行
/// </summary>
/// <param name="cmdStr">存储过程</param>
/// <returns>一个数据集</returns>
public static DataSet getDataSetPro(string cmdStr)
{
SqlConnection con = DBHelper.createConn();
try
{
con.Open();
SqlCommand cmd = new SqlCommand();
PrepareCommandPro(con, cmd, cmdStr);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds, "table");
cmd.Parameters.Clear();
return ds;
}
catch
{
throw;
}
finally
{
con.Close();
}
}
/// <summary>
/// 获取执行查询获取DataReader的方法
/// </summary>
/// <param name="cmd">SqlCommand</param>
/// <returns></returns>
public static SqlDataReader getDataReader(SqlCommand cmd)
{
SqlConnection conn = DBHelper.createConn();
cmd.Connection = conn;
conn.Open();
SqlDataReader sda;
sda = cmd.ExecuteReader(CommandBehavior.CloseConnection);
conn.Close();
return sda;
}
//--------------带参数------------------------------------------------------------------
/// <summary>
/// 执行sql语句的方法
/// </summary>
/// <param name="cmdText">sql字符串</param>
/// <param name="cmdParms">sql字符串中的参数</param>
public static int executeSql(string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = DBHelper.createConn();
PrepareCommand(conn, cmd, cmdText, cmdParms);
int temp = cmd.ExecuteNonQuery();
conn.Close();
cmd.Parameters.Clear();
return temp;
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="cmdText">存储过程名称</param>
/// <param name="cmdParms">存储过程中的参数</param>
public static int executePro(string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = DBHelper.createConn();
PrepareCommandPro(conn, cmd, cmdText, cmdParms);
int temp = cmd.ExecuteNonQuery();
conn.Close();
cmd.Parameters.Clear();
return temp;
}
/// <summary>
/// 执行sql语句获得第一行第一列的字符串
/// </summary>
/// <param name="cmdText">sql字符串或存储过程名称</param>
/// <param name="cmdParms">sql字符串中的参数</param>
/// <returns>查询的结果</returns>
public static string executeScalarStr(string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = DBHelper.createConn();
PrepareCommand(conn, cmd, cmdText, cmdParms);
string result = Convert.ToString(cmd.ExecuteScalar());
conn.Close();
cmd.Parameters.Clear();
return result;
}
/// <summary>
/// 执行sql语句获得第一行第一列的数字,用来获得count()结果
/// </summary>
/// <param name="cmdText">sql字符串或存储过程名称</param>
/// <param name="cmdParms">sql字符串中的参数</param>
/// <returns>查询的结果数字</returns>
public static int executeScalar(string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = DBHelper.createConn();
PrepareCommand(conn, cmd, cmdText, cmdParms);
int result = Convert.ToInt32(cmd.ExecuteScalar());
conn.Close();
cmd.Parameters.Clear();
return result;
}
/// <summary>
/// 返回一个数据集
/// </summary>
/// <param name="cmdStr">存储过程</param>
/// <param name="cmdParms">存储过程中的参数</param>
/// <returns></returns>
public static DataSet getDataSetPro(string cmdStr, params SqlParameter[] cmdParms)
{
SqlConnection con = DBHelper.createConn();
try
{
con.Open();
SqlCommand cmd = new SqlCommand();
PrepareCommandPro(con, cmd, cmdStr, cmdParms);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds, "table");
cmd.Parameters.Clear();
return ds;
}
catch
{
throw;
}
finally
{
con.Close();
}
}
/// <summary>
/// 返回一个数据集
/// </summary>
/// <param name="cmdStr">sql语句</param>
/// <param name="cmdParms">sql语句中的参数</param>
/// <returns></returns>
public static DataSet getDataSet(string cmdStr, params SqlParameter[] cmdParms)
{
SqlConnection con = DBHelper.createConn();
try
{
con.Open();
SqlCommand cmd = new SqlCommand();
PrepareCommand(con, cmd, cmdStr, cmdParms);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds, "table");
cmd.Parameters.Clear();
return ds;
}
catch
{
throw;
}
finally
{
con.Close();
}
}
/// <summary>
/// 处理数据库连接以及参数的方法
/// </summary>
/// <param name="conn">数据库连接</param>
/// <param name="cmd">根据数据库连接和sql语句创建的SqlCommand对象</param>
/// <param name="cmdText">sql语句名称</param>
/// <param name="cmdParms">sql语句中的参数</param>
private static void PrepareCommand(SqlConnection conn, SqlCommand cmd, string cmdText, SqlParameter[] cmdParms)
{
//判断连接的状态。如果是关闭状态，则打开
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
//cmd属性赋值
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.Text;
//添加cmd需要的参数
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
{
cmd.Parameters.Add(parm);
}
}
}
/// <summary>
/// 处理数据库连接以及参数的方法
/// </summary>
/// <param name="conn">数据库连接</param>
/// <param name="cmd">根据数据库连接和存储过程创建的SqlCommand对象</param>
/// <param name="cmdText">存储过程名称</param>
/// <param name="cmdParms">存储过程中的参数</param>
private static void PrepareCommandPro(SqlConnection conn, SqlCommand cmd, string cmdText, SqlParameter[] cmdParms)
{
//判断连接的状态。如果是关闭状态，则打开
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
//cmd属性赋值
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = CommandType.StoredProcedure;
//添加cmd需要的存储过程参数
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
{
cmd.Parameters.Add(parm);
}
}
}
/// <summary>
/// 获取执行查询获取DataReader的方法
/// </summary>
/// <param name="cmdText">sql字符串或存储过程名称</param>
/// <param name="cmdParms">sql字符串中的参数</param>
/// <returns>SqlDataReader</returns>
public static SqlDataReader getDataReader(string cmdText, params SqlParameter[] cmdParms)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = DBHelper.createConn();
try
{
PrepareCommand(conn, cmd, cmdText, cmdParms);
SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return sdr;
}
catch
{
conn.Close();
throw;
}
}
}
