- /// <summary>
- /// 数据库连接
- /// </summary>
- public static SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
- /// <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();
- try
- {
- PrepareCommand(conn, cmd, cmdText, cmdParms);
- SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
- cmd.Parameters.Clear();
- return sdr;
- }
- catch
- {
- conn.Close();
- throw;
- }
- }
- /// <summary>
- /// 获取执行查询获取DataReader的方法
- /// </summary>
- /// <param name="cmdText">sql字符串或存储过程名称</param>
- /// <returns>SqlDataReader</returns>
- public static SqlDataReader getDataReader(string cmdText)
- {
- 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>
- /// <param name="cmdParms">sql字符串中的参数</param>
- public static int executeSql(string cmdText, params SqlParameter[] cmdParms)
- {
- SqlCommand cmd = new SqlCommand();
- PrepareCommand(conn, cmd, cmdText, cmdParms);
- int temp = cmd.ExecuteNonQuery();
- conn.Close();
- cmd.Parameters.Clear();
- return temp;
- }
- /// <summary>
- /// 执行sql语句的方法
- /// </summary>
- /// <param name="cmdText">sql字符串或存储过程名称</param>
- public static int executeSql(string cmdText)
- {
- SqlCommand cmd = new SqlCommand();
- if (conn.State != ConnectionState.Open)
- {
- conn.Open();
- }
- int temp = cmd.ExecuteNonQuery();
- conn.Close();
- 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();
- PrepareCommandPro(conn, cmd, cmdText, cmdParms);
- int temp = cmd.ExecuteNonQuery();
- conn.Close();
- cmd.Parameters.Clear();
- return temp;
- }
- /// <summary>
- /// 执行sql语句获得第一行第一列的字符串
- /// </summary>
- /// <param name="cmdText">sql字符串或存储过程名称</param>
- /// <returns>查询的结果</returns>
- public static string executeScalarStr(string cmdText)
- {
- 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语句获得第一行第一列的字符串
- /// </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();
- 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();
- PrepareCommand(conn, cmd, cmdText, cmdParms);
- int result = Convert.ToInt32(cmd.ExecuteScalar());
- conn.Close();
- cmd.Parameters.Clear();
- return result;
- }
- /// <summary>
- /// 执行sql语句获得第一行第一列的数字,用来获得count()结果
- /// </summary>
- /// <param name="cmdText">sql字符串或存储过程名称</param>
- /// <returns>查询的结果数字</returns>
- public static int executeScalar(string cmdText)
- {
- 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">根据数据库连接和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>
- /// 无参数的存储过程的处理方法。
- /// </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)
- {
- try
- {
- conn.Open();
- SqlDataAdapter sda = new SqlDataAdapter(cmdStr,conn);
- DataSet ds = new DataSet();
- sda.Fill(ds, "table");
- return ds;
- }
- catch
- {
- throw;
- }
- finally
- {
- conn.Close();
- }
- }
- /// <summary>
- /// 返回一个数据集
- /// </summary>
- /// <param name="cmdStr">sql语句</param>
- /// <param name="cmdParms">sql语句中的参数</param>
- /// <returns></returns>
- public static DataSet getDataSet(string cmdStr, params SqlParameter[] cmdParms)
- {
- try
- {
- conn.Open();
- SqlCommand cmd = new SqlCommand();
- PrepareCommand(conn, cmd, cmdStr, cmdParms);
- SqlDataAdapter sda = new SqlDataAdapter(cmd);
- DataSet ds = new DataSet();
- sda.Fill(ds, "table");
- cmd.Parameters.Clear();
- return ds;
- }
- catch
- {
- throw;
- }
- finally
- {
- conn.Close();
- }
- }
- /// <summary>
- /// 无参数的存储过程的执行
- /// </summary>
- /// <param name="cmdStr">存储过程</param>
- /// <returns>一个数据集</returns>
- public static DataSet getDataSetPro(string cmdStr)
- {
- try
- {
- conn.Open();
- SqlCommand cmd = new SqlCommand();
- PrepareCommandPro(conn, cmd, cmdStr);
- SqlDataAdapter sda = new SqlDataAdapter(cmd);
- DataSet ds = new DataSet();
- sda.Fill(ds, "table");
- cmd.Parameters.Clear();
- return ds;
- }
- catch
- {
- throw;
- }
- finally
- {
- conn.Close();
- }
- }
- /// <summary>
- /// 返回一个数据集
- /// </summary>
- /// <param name="cmdStr">存储过程</param>
- /// <param name="cmdParms">存储过程中的参数</param>
- /// <returns></returns>
- public static DataSet getDataSetPro(string cmdStr, params SqlParameter[] cmdParms)
- {
- try
- {
- conn.Open();
- SqlCommand cmd = new SqlCommand();
- PrepareCommandPro(conn, cmd, cmdStr, cmdParms);
- SqlDataAdapter sda = new SqlDataAdapter(cmd);
- DataSet ds = new DataSet();
- sda.Fill(ds, "table");
- cmd.Parameters.Clear();
- return ds;
- }
- catch
- {
- throw;
- }
- finally
- {
- conn.Close();
- }
- }
- /// <summary>
- /// 获取执行查询获取DataReader的方法
- /// </summary>
- /// <param name="cmd">SqlCommand</param>
- /// <returns></returns>
- public static SqlDataReader getDataReader(SqlCommand cmd)
- {
- cmd.Connection= conn;
- conn.Open();
- SqlDataReader sda;
- sda = cmd.ExecuteReader(CommandBehavior.CloseConnection);
- conn.Close();
- return sda;
- }
转载于:https://blog.51cto.com/liguo125/456112