- /// <summary>
- /// 操作数据库存储过程类
- /// </summary>
- public class ExecProc
- {
- //取出数据库连接字符串
- //public static readonly string conStr = ConfigurationManager.AppSettings["ConnectionString"];
- public static readonly string conStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
- /// <summary>
- /// 得到数据库连接对象
- /// </summary>
- /// <returns>数据库连接对象</returns>
- public static SqlConnection GetConObject()
- {
- return new SqlConnection(conStr);
- }
- /// <summary>
- /// 执行操作数据库的存储过程
- /// </summary>
- /// <param name="procName">存储过程名称</param>
- /// <returns>存储过程执行后所影响的行数</returns>
- public static int ExecuteNonQuery(string procName)
- {
- SqlCommand cmd = new SqlCommand();
- using (SqlConnection con = GetConObject())
- {
- CreateCommand(cmd, con, null, procName, null);
- int val = cmd.ExecuteNonQuery();
- cmd.Parameters.Clear();
- cmd.Dispose();
- return val;
- }
- }
- /// <summary>
- /// 执行操作数据库的存储过程
- /// </summary>
- /// <param name="procName">存储过程名称</param>
- /// <param name="cmdParms">存储过程所使用的参数</param>
- /// <returns>存储过程执行后所影响的行数</returns>
- public static int ExecuteNonQuery(string procName, SqlParameter[] cmdParms)
- {
- SqlCommand cmd = new SqlCommand();
- using (SqlConnection con = GetConObject())
- {
- CreateCommand(cmd, con, null, procName, cmdParms);
- int val = cmd.ExecuteNonQuery();
- cmd.Parameters.Clear();
- cmd.Dispose();
- return val;
- }
- }
- /// <summary>
- /// 执行读数据集操作,以DataReader的形式返回
- /// </summary>
- /// <param name="procName">存储过程名称</param>
- /// <param name="cmdParms">存储过程所使用的参数</param>
- /// <returns>DataReader对象</returns>
- public static SqlDataReader ExecuteReader(string procName, SqlParameter[] cmdParms)
- {
- SqlCommand cmd = new SqlCommand();
- SqlConnection con = GetConObject();
- try
- {
- CreateCommand(cmd, con, null, procName, cmdParms);
- SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
- cmd.Parameters.Clear();
- return dr;
- }
- catch (Exception ex)
- {
- cmd.Dispose();
- Close(con);
- throw ex;
- }
- }
- /// <summary>
- /// 执行读数据集操作,以DataReader的形式返回
- /// </summary>
- /// <param name="procName">存储过程名称</param>
- /// <returns>DataReader对象</returns>
- public static SqlDataReader ExecuteReader(string procName)
- {
- SqlCommand cmd = new SqlCommand();
- SqlConnection con = GetConObject();
- try
- {
- CreateCommand(cmd, con, null, procName, null);
- SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
- cmd.Parameters.Clear();
- return dr;
- }
- catch (Exception ex)
- {
- cmd.Dispose();
- Close(con);
- throw ex;
- }
- }
- /// <summary>
- /// 执行读数据集操作,以DataSet的形式返回
- /// </summary>
- /// <param name="procName">存储过程名称</param>
- /// <param name="cmdParms">存储过程所使用的参数</param>
- /// <returns>DataSet对象</returns>
- public static DataSet ExecuteDataSet(string procName, SqlParameter[] cmdParms)
- {
- SqlCommand cmd = new SqlCommand();
- SqlConnection con = GetConObject();
- DataSet ds = new DataSet();
- try
- {
- CreateCommand(cmd, con, null, procName, cmdParms);
- SqlDataAdapter da = new SqlDataAdapter();
- da.SelectCommand = cmd;
- da.Fill(ds);
- cmd.Parameters.Clear();
- return ds;
- }
- catch (Exception ex)
- {
- throw ex;
- }
- finally
- {
- cmd.Dispose();
- Close(con);
- }
- }
- public static string ExecuteValue(string procName, SqlParameter[] cmdParms)
- {
- SqlCommand cmd = new SqlCommand();
- SqlConnection con = GetConObject();
- DataSet ds = new DataSet();
- try
- {
- CreateCommand(cmd, con, null, procName, cmdParms);
- SqlDataAdapter da = new SqlDataAdapter();
- da.SelectCommand = cmd;
- da.Fill(ds);
- cmd.Parameters.Clear();
- return ds.Tables[0].Rows[0][0].ToString();
- }
- catch (Exception ex)
- {
- throw ex;
- }
- finally
- {
- cmd.Dispose();
- Close(con);
- }
- }
- /// <summary>
- /// 执行读数据集操作,以DataSet的形式返回
- /// </summary>
- /// <param name="procName">存储过程名称</param>
- /// <returns>DataReader对象</returns>
- public static DataSet ExecuteDataSet(string procName)
- {
- SqlCommand cmd = new SqlCommand();
- SqlConnection con = GetConObject();
- DataSet ds = new DataSet();
- try
- {
- CreateCommand(cmd, con, null, procName, null);
- SqlDataAdapter da = new SqlDataAdapter();
- da.SelectCommand = cmd;
- da.Fill(ds);
- cmd.Parameters.Clear();
- return ds;
- }
- catch (Exception ex)
- {
- throw ex;
- }
- finally
- {
- cmd.Dispose();
- Close(con);
- }
- }
- /// <summary>
- /// 创建数据库执行命令
- /// </summary>
- /// <param name="cmd">数据库执行命令对象</param>
- /// <param name="con">数据库连接对象</param>
- /// <param name="trans">数据库事务对象</param>
- /// <param name="procName">存储过程名称</param>
- /// <param name="cmdParms">存储过程所使用的参数数组</param>
- public static void CreateCommand(SqlCommand cmd, SqlConnection con, SqlTransaction trans, string procName, SqlParameter[] cmdParms)
- {
- if (con.State != ConnectionState.Open)
- con.Open();
- cmd.Connection = con;
- cmd.CommandText = procName;
- if (trans != null)
- {
- cmd.Transaction = trans;
- }
- cmd.CommandType = CommandType.StoredProcedure;
- if (cmdParms != null)
- {
- foreach (SqlParameter parm in cmdParms)
- cmd.Parameters.Add(parm);
- }
- }
- /// <summary>
- /// --- LG
- /// </summary>
- /// <param name="procName"></param>
- /// <param name="cmdParms"></param>
- /// <returns></returns>
- public static int ExecuteReturnValue(string procName, SqlParameter[] cmdParms)
- {
- SqlConnection con = GetConObject();
- if (con.State != ConnectionState.Open)
- con.Open();
- SqlCommand myCommand = new SqlCommand(procName,con);
- myCommand.CommandType = CommandType.StoredProcedure;
- if (cmdParms.Length != 0)
- {
- for (int i = 0; i < cmdParms.Length; i++)
- {
- myCommand.Parameters.Add(cmdParms[i]);
- }
- }
- myCommand.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
- myCommand.ExecuteNonQuery();
- return Convert.ToInt32(myCommand.Parameters["@return"].Value.ToString());
- }
- /// <summary>
- /// 关闭数据库连接
- /// </summary>
- /// <param name="myConn">数据库连接对象</param>
- public static void Close(SqlConnection myConn)
- {
- if ((myConn != null) && (myConn.State == ConnectionState.Open))
- {
- myConn.Close();
- }
- }
- }
转载于:https://blog.51cto.com/liguo125/456108