执行存储过程
using System; using System.Data; using System.Configuration; using System.Data.SqlClient; /// <summary> /// Summary description for BaseDB /// </summary> public class BaseDB { #region 相关数据库访问成员变量定义 /// <summary> /// 数据库连接对象 /// </summary> public SqlConnection conn = null; /// <summary> /// 命令操作 /// </summary> private SqlCommand m_Command = null; /// <summary> /// 异常错误标示 /// 0 无异常 1 严重异常 2 一般性异常，可以继续执行 /// </summary> public byte m_nExpErrorFlag = 0; /// <summary> /// 内部错误 /// </summary> public string m_InnerError = ""; /// <summary> /// 外部错误 /// </summary> public string m_OutError = "对不起，数据库操作异常，请将错误即时报告给我们。"; #endregion #region BaseDB /// <summary> /// 构造数据库连接 /// </summary> /// <param name="strConnectString">数据库连接语句</param> public BaseDB(string strConnectString) { if (conn == null) conn = new SqlConnection(strConnectString); if (conn.State != ConnectionState.Open) { int nCount = 0; while (nCount < 5) { try { nCount++; conn.Open(); if (conn.State == ConnectionState.Open) break; } catch (Exception exp) { nCount++; m_nExpErrorFlag = 1; m_InnerError = "错误对象名：" + exp.Source + " 异常消息：" + exp.Message; SystemError.CreateErrorLog(m_InnerError); } } } } public BaseDB() { } #endregion #region 相关方法定义 #region CreateCommand /// <summary> /// 创建使用存储过程的操作命令对象 /// </summary> /// <param name="procName">过程名</param> /// <param name="prams">参数集</param> /// <returns>返回值</returns> private SqlCommand CreateCommand(string procName, SqlParameter[] prams) { //创建数据库操作 if (m_Command == null) m_Command = new SqlCommand(procName, conn); else m_Command.CommandText = procName; m_Command.CommandType = CommandType.StoredProcedure; m_Command.Parameters.Clear();//清除参数 // add proc parameters if (prams != null) { foreach (SqlParameter parameter in prams) m_Command.Parameters.Add(parameter); } // return param 此处暂时可不要 /*m_Command.Parameters.Add( new SqlParameter("ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null)); */ return m_Command; } #endregion #region RunProc /// <summary> /// 运行存储过程 /// </summary> /// <param name="procName">过程名</param> /// <param name="prams">参数集</param> /// <returns>返回过程的返回值</returns> public int RunProc(string strProcName, SqlParameter[] prams) { m_nExpErrorFlag = 0; SqlCommand myCommand; try { myCommand = CreateCommand(strProcName, prams); myCommand.ExecuteNonQuery(); } catch (Exception exp) { m_nExpErrorFlag = 1; m_InnerError = "存储过程名：" + strProcName + " 错误对象名：" + exp.Source + " 异常消息：" + exp.Message; if (prams != null) { foreach (SqlParameter parameter in prams) { if (parameter.SqlDbType == SqlDbType.Int || parameter.SqlDbType == SqlDbType.BigInt || parameter.SqlDbType == SqlDbType.Decimal) { m_InnerError = m_InnerError + " " + parameter.ParameterName + "=" + parameter.Value + ",Direction." + parameter.Direction; } else { m_InnerError = m_InnerError + " " + parameter.ParameterName + "="" + parameter.Value + "",Direction." + parameter.Direction; } } } SystemError.CreateErrorLog(m_InnerError); return -9; } conn.Close(); return (int)myCommand.Parameters["@ReturnValue"].Value; } #endregion #region RunProcDT /// <summary> /// 运行存储过程,返回DataTable，用于查询类存储过程的操作 /// </summary> /// <param name="procName">过程名</param> public DataTable RunProcDT(string strProcName) { m_nExpErrorFlag = 0; SqlCommand myCommand; SqlDataAdapter dataAdapter; DataTable dtResult; try { myCommand = CreateCommand(strProcName, null); dataAdapter = new SqlDataAdapter(); dataAdapter.SelectCommand = myCommand; dtResult = new DataTable(); dataAdapter.Fill(dtResult); } catch (Exception exp) { m_nExpErrorFlag = 1; m_InnerError = "存储过程名：" + strProcName + " 错误对象名：" + exp.Source + " 异常消息：" + exp.Message; SystemError.CreateErrorLog(m_InnerError); return null; } conn.Close(); return dtResult; } /// <summary> /// 运行存储过程,返回DataTable /// </summary> /// <param name="procName">过程名</param> /// <param name="prams">参数集</param> /// <param name="strDataTableName">表别名</param> public DataTable RunProcDT(string strProcName, SqlParameter[] prams) { m_nExpErrorFlag = 0; SqlCommand myCommand; SqlDataAdapter dataAdapter; DataTable dtResult; try { myCommand = CreateCommand(strProcName, prams); dataAdapter = new SqlDataAdapter(); dataAdapter.SelectCommand = myCommand; dtResult = new DataTable(); dataAdapter.Fill(dtResult); } catch (Exception exp) { m_nExpErrorFlag = 1; m_InnerError = "存储过程名：" + strProcName + " 错误对象名：" + exp.Source + " 异常消息：" + exp.Message; if (prams != null) { foreach (SqlParameter parameter in prams) { if (parameter.SqlDbType == SqlDbType.Int || parameter.SqlDbType == SqlDbType.BigInt || parameter.SqlDbType == SqlDbType.Decimal) { m_InnerError = m_InnerError + " " + parameter.ParameterName + "=" + parameter.Value + ",Direction." + parameter.Direction; } else { m_InnerError = m_InnerError + " " + parameter.ParameterName + "="" + parameter.Value + "",Direction." + parameter.Direction; } } } SystemError.CreateErrorLog(m_InnerError); return null; } conn.Close(); return dtResult; } /// <summary> /// 运行存储过程,返回DataTable，用于查询类存储过程的操作 /// </summary> /// <param name="procName">过程名</param> /// <param name="strDataTableName">表别名</param> public DataTable RunProcDT(string strProcName, string strDataTableName) { m_nExpErrorFlag = 0; SqlCommand myCommand; SqlDataAdapter dataAdapter; DataTable dtResult; try { myCommand = CreateCommand(strProcName, null); dataAdapter = new SqlDataAdapter(); dataAdapter.SelectCommand = myCommand; dtResult = new DataTable(); dtResult.TableName = strDataTableName; dataAdapter.Fill(dtResult); } catch (Exception exp) { m_nExpErrorFlag = 1; m_InnerError = "存储过程名：" + strProcName + " 错误对象名：" + exp.Source + " 异常消息：" + exp.Message; SystemError.CreateErrorLog(m_InnerError); return null; } conn.Close(); return dtResult; } /// <summary> /// 运行存储过程,返回DataTable /// </summary> /// <param name="procName">过程名</param> /// <param name="prams">参数集</param> /// <param name="strDataTableName">表别名</param> public DataTable RunProcDT(string strProcName, SqlParameter[] prams, string strDataTableName) { m_nExpErrorFlag = 0; SqlCommand myCommand; SqlDataAdapter dataAdapter; DataTable dtResult; try { myCommand = CreateCommand(strProcName, prams); dataAdapter = new SqlDataAdapter(); dataAdapter.SelectCommand = myCommand; dtResult = new DataTable(); dtResult.TableName = strDataTableName; dataAdapter.Fill(dtResult); } catch (Exception exp) { m_nExpErrorFlag = 1; m_InnerError = "存储过程名：" + strProcName + " 错误对象名：" + exp.Source + " 异常消息：" + exp.Message; if (prams != null) { foreach (SqlParameter parameter in prams) { if (parameter.SqlDbType == SqlDbType.Int || parameter.SqlDbType == SqlDbType.BigInt || parameter.SqlDbType == SqlDbType.Decimal) { m_InnerError = m_InnerError + " " + parameter.ParameterName + "=" + parameter.Value + ",Direction." + parameter.Direction; } else { m_InnerError = m_InnerError + " " + parameter.ParameterName + "="" + parameter.Value + "",Direction." + parameter.Direction; } } } SystemError.CreateErrorLog(m_InnerError); return null; } conn.Close(); return dtResult; } #endregion #region RunProcScalar /// <summary> /// 运行存储过程,返回第一行第一列，用于查询类存储过程的操作 /// </summary> /// <param name="procName">过程名</param> public object RunProcScalar(string strProcName) { m_nExpErrorFlag = 0; SqlCommand myCommand; object o; try { myCommand = CreateCommand(strProcName, null); o = myCommand.ExecuteScalar(); } catch (Exception exp) { m_nExpErrorFlag = 1; m_InnerError = "存储过程名：" + strProcName + " 错误对象名：" + exp.Source + " 异常消息：" + exp.Message; SystemError.CreateErrorLog(m_InnerError); return null; } conn.Close(); return o; } /// <summary> /// 运行存储过程,返回第一行第一列，用于查询类存储过程的操作 /// </summary> /// <param name="procName">过程名</param> public object RunProcScalar(string strProcName, SqlParameter[] prams) { m_nExpErrorFlag = 0; SqlCommand myCommand; object o; try { myCommand = CreateCommand(strProcName, prams); o = myCommand.ExecuteScalar(); } catch (Exception exp) { m_nExpErrorFlag = 1; m_InnerError = "存储过程名：" + strProcName + " 错误对象名：" + exp.Source + " 异常消息：" + exp.Message; if (prams != null) { foreach (SqlParameter parameter in prams) { if (parameter.SqlDbType == SqlDbType.Int || parameter.SqlDbType == SqlDbType.BigInt || parameter.SqlDbType == SqlDbType.Decimal) { m_InnerError = m_InnerError + " " + parameter.ParameterName + "=" + parameter.Value + ",Direction." + parameter.Direction; } else { m_InnerError = m_InnerError + " " + parameter.ParameterName + "="" + parameter.Value + "",Direction." + parameter.Direction; } } } SystemError.CreateErrorLog(m_InnerError); return null; } conn.Close(); return o; } #endregion #region RunProcDS /// <summary> /// 运行存储过程,返回DataSet，用于查询类存储过程的操作 /// </summary> /// <param name="procName">过程名</param> public DataSet RunProcDS(string strProcName) { m_nExpErrorFlag = 0; SqlCommand myCommand; SqlDataAdapter dataAdapter; DataSet dsResult; try { myCommand = CreateCommand(strProcName, null); dataAdapter = new SqlDataAdapter(); dataAdapter.SelectCommand = myCommand; dsResult = new DataSet(); dataAdapter.Fill(dsResult); } catch (Exception exp) { m_nExpErrorFlag = 1; m_InnerError = "存储过程名：" + strProcName + " 错误对象名：" + exp.Source + " 异常消息：" + exp.Message; SystemError.CreateErrorLog(m_InnerError); return null; } conn.Close(); return dsResult; } /// <summary> /// 运行存储过程,返回DataSet /// </summary> /// <param name="procName">过程名</param> /// <param name="prams">参数集</param> /// <param name="strDataTableName">表别名</param> public DataSet RunProcDS(string strProcName, SqlParameter[] prams) { m_nExpErrorFlag = 0; SqlCommand myCommand; SqlDataAdapter dataAdapter; DataSet dsResult; try { myCommand = CreateCommand(strProcName, prams); dataAdapter = new SqlDataAdapter(); dataAdapter.SelectCommand = myCommand; dsResult = new DataSet(); dataAdapter.Fill(dsResult); } catch (Exception exp) { m_nExpErrorFlag = 1; m_InnerError = "存储过程名：" + strProcName + " 错误对象名：" + exp.Source + " 异常消息：" + exp.Message; if (prams != null) { foreach (SqlParameter parameter in prams) { if (parameter.SqlDbType == SqlDbType.Int || parameter.SqlDbType == SqlDbType.BigInt || parameter.SqlDbType == SqlDbType.Decimal) { m_InnerError = m_InnerError + " " + parameter.ParameterName + "=" + parameter.Value + ",Direction." + parameter.Direction; } else { m_InnerError = m_InnerError + " " + parameter.ParameterName + "="" + parameter.Value + "",Direction." + parameter.Direction; } } } SystemError.CreateErrorLog(m_InnerError); return null; } conn.Close(); return dsResult; } #endregion #region RunProcReader /// <summary> /// 运行存储过程，返回SqlDataReader（使用完须关闭SqlDataReader,释放使用SqlDataReader资源） /// </summary> /// <param name="strProcName">过程名</param> /// <returns></returns> public SqlDataReader RunProcReader(string strProcName) { m_nExpErrorFlag = 0; SqlDataReader myReader; SqlCommand myCommand; try { myCommand = CreateCommand(strProcName, null); myReader = myCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection); } catch (Exception exp) { m_nExpErrorFlag = 1; m_InnerError = "存储过程名：" + strProcName + " 错误对象名：" + exp.Source + "异常消息：" + exp.Message; SystemError.CreateErrorLog(m_InnerError); return null; } return myReader; } /// <summary> /// 运行存储过程，返回SqlDataReader（使用完须关闭SqlDataReader,释放使用SqlDataReader资源） /// </summary> /// <param name="strProcName">过程名</param> /// <param name="prams">参数集</param> /// <returns></returns> public SqlDataReader RunProcReader(string strProcName, SqlParameter[] prams) { m_nExpErrorFlag = 0; SqlDataReader myReader; SqlCommand myCommand; try { myCommand = CreateCommand(strProcName, prams); myReader = myCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection); } catch (Exception exp) { m_nExpErrorFlag = 1; m_InnerError = "存储过程名：" + strProcName + " 错误对象名：" + exp.Source + "异常消息：" + exp.Message; if (prams != null) { foreach (SqlParameter parameter in prams) { if (parameter.SqlDbType == SqlDbType.Int || parameter.SqlDbType == SqlDbType.BigInt || parameter.SqlDbType == SqlDbType.Decimal) { m_InnerError = m_InnerError + " " + parameter.ParameterName + "=" + parameter.Value + ",Direction." + parameter.Direction; } else { m_InnerError = m_InnerError + " " + parameter.ParameterName + "="" + parameter.Value + "",Direction." + parameter.Direction; } } } SystemError.CreateErrorLog(m_InnerError); return null; } return myReader; } #endregion #region GetDataSet public DataSet GetDataSet(string sql) { SqlDataAdapter sda = new SqlDataAdapter(sql, conn); DataSet ds = new DataSet(); sda.Fill(ds); if (conn.State != ConnectionState.Closed) conn.Close(); return ds; } #endregion #region 针对SqlParameters的一些构造模块 /// <summary> /// 构建存储过程调用参数 /// </summary> /// <param name="ParamName">存储过程名称</param> /// <param name="Direction">传入还是传出</param> /// <param name="Value">参数值</param> /// <returns>返回SqlParameter对象</returns> private SqlParameter MakeParam(string ParamName, ParameterDirection Direction, object Value) { SqlParameter param = new SqlParameter(); param.ParameterName = ParamName; param.Direction = Direction; if (!(Direction == ParameterDirection.Output && Value == null)) param.Value = Value; return param; } /// <summary> /// /// </summary> /// <param name="paramName"></param> /// <param name="Direction"></param> /// <param name="Type"></param> /// <param name="Size"></param> /// <param name="Value"></param> /// <returns></returns> private SqlParameter MakeParam(string paramName, ParameterDirection Direction, SqlDbType Type, int Size, object Value) { SqlParameter param = new SqlParameter(paramName, Type, Size); param.Direction = Direction; if (!(Direction == ParameterDirection.Output && Value == null)) param.Value = Value; return param; } /// <summary> /// 构建存储过程传入参数 /// </summary> /// <param name="ParamName">存储过程名称</param> /// <param name="Value">参数值</param> /// <returns>返回SqlParameter对象</returns> public SqlParameter MakeInParam(string ParamName, object Value) { return MakeParam(ParamName, ParameterDirection.Input, Value); } /// <summary> /// /// </summary> /// <param name="ParamName"></param> /// <param name="Type"></param> /// <param name="Size"></param> /// <param name="Value"></param> /// <returns></returns> public SqlParameter MakeInParam(string ParamName, SqlDbType Type, int Size, object Value) { return MakeParam(ParamName, ParameterDirection.Input, Type, Size, Value); } /// <summary> /// 构建存储过程 /// </summary> /// <param name="ParamName">存储过程名称</param> /// <param name="Value">参数值</param> /// <returns>返回SqlParameter对象</returns> public SqlParameter MakeReturnParam(string ParamName) { return MakeParam(ParamName, ParameterDirection.ReturnValue, null); } /// <summary> /// /// </summary> /// <param name="ParamName"></param> /// <param name="Type"></param> /// <param name="Size"></param> /// <param name="Value"></param> /// <returns></returns> public SqlParameter MakeReturnParam(string ParamName, SqlDbType Type, int Size) { return MakeParam(ParamName, ParameterDirection.ReturnValue, Type, Size, null); } /// <summary> /// 构建存储过程传出参数 /// </summary> /// <param name="ParamName">存储过程名称</param> /// <returns>返回SqlParameter对象</returns> public SqlParameter MakeOutParam(string ParamName) { return MakeParam(ParamName, ParameterDirection.Output, null); } /// <summary> /// /// </summary> /// <param name="ParamName"></param> /// <param name="Type"></param> /// <param name="Size"></param> /// <returns></returns> public SqlParameter MakeOutParam(string ParamName, SqlDbType Type, int Size) { return MakeParam(ParamName, ParameterDirection.Output, Type, Size, null); } #endregion #endregion }
快捷生成SqlParameter数组
using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; namespace DataAccess { /// <summary> /// 存储过程相关工具类 /// </summary> public class ProcHelper { #region 快捷生成一个 SqlParameter 数组 /// <summary> /// 快捷生成一个 SqlParameter 数组 /// </summary> /// <param name="paramArr">参数信息 /// 格式要求：参数名 类型(长度) 参数类型（in/out/inout/return）, [如果前一个参数是输入参数那么这个参数是前一个参数的值],参数名 类型(长度) 参数类型（in/out/inout/return）,... /// 例："id int(4) in", 1, "name varchar(20) out" /// 上述参数表示将创建两个 SqlParameter 对象 name 分别为 @id 和 @name，@ 可省略不写 /// 1 为名为 @id 的输入参数即 SqlParameter 对象的 Value 属性值 /// </param> /// <returns>返回生成的参数列表</returns> public static SqlParameter[] GenParams(params object[] paramArr) { var sqlParameters = new List<SqlParameter>(); bool isInDirection = false; // 记录上一个循环元素是否是输入参数 int currentLoopParamIndex = -1; // 记录上一个参数的索引 foreach (var param in paramArr) { // 如果上一个元素是输入参数，那么将后面一个元素赋值给上一个参数的 Value 属性 if (isInDirection) { sqlParameters.ToArray()[currentLoopParamIndex].Value = param; isInDirection = false; continue; } bool isParamInfo = param.GetType().Name.ToLower() == "string"; if (isParamInfo && !isInDirection) { try { var strs = param.ToString().Split(' '); string paramName = strs[0].Contains("@") ? strs[0] : "@" + strs[0]; string[] dataTypeArr = strs[1].Split('('); string dataTypeStr = dataTypeArr[0]; int dataLength = Convert.ToInt32(dataTypeArr[1].Replace(")", "")); string directionStr = strs[2]; SqlParameter sqlParameter = new SqlParameter(paramName, GetDataType(dataTypeStr), dataLength); sqlParameter.Direction = GetDirection(directionStr); isInDirection = sqlParameter.Direction == ParameterDirection.Input; sqlParameters.Add(sqlParameter); currentLoopParamIndex++; } catch (Exception e) { throw new Exception("[" + param + "]参数格式不正确"); } } } return sqlParameters.ToArray(); } #endregion #region 通过字符串获取参数对应数据类型的枚举 private static SqlDbType GetDataType(string dataTypeStr) { dataTypeStr = dataTypeStr.ToLower(); int typeCode = -1; switch (dataTypeStr) { case "bigint": typeCode = 0; break; case "binary": typeCode = 1; break; case "bit": typeCode = 2; break; case "char": typeCode = 3; break; case "datatime": typeCode = 4; break; case "decimal": typeCode = 5; break; case "float": typeCode = 6; break; case "image": typeCode = 7; break; case "int": typeCode = 8; break; case "money": typeCode = 9; break; case "nchar": typeCode = 10; break; case "ntext": typeCode = 11; break; case "nvarchar": typeCode = 12; break; case "real": typeCode = 13; break; case "uniqueidentifier": typeCode = 14; break; case "smalldatatime": typeCode = 15; break; case "smallint": typeCode = 16; break; case "smallmoney": typeCode = 17; break; case "text": typeCode = 18; break; case "timestamp": typeCode = 19; break; case "tinyint": typeCode = 20; break; case "varbinary": typeCode = 21; break; case "varchar": typeCode = 22; break; case "variant": typeCode = 23; break; case "xml": typeCode = 25; break; case "udt": typeCode = 29; break; case "structured": typeCode = 30; break; case "date": typeCode = 31; break; case "time": typeCode = 32; break; case "datatime2": typeCode = 33; break; case "datetimeoffset": typeCode = 34; break; } if (typeCode == -1) throw new Exception("无此数据类型[" + dataTypeStr + "]"); return (SqlDbType) typeCode; } #endregion #region 通过字符串获取参数输出类型 private static ParameterDirection GetDirection(string directionStr) { int directionCode = -1; switch (directionStr) { case "in": directionCode = 1; break; case "out": directionCode = 2; break; case "inout": directionCode = 3; break; case "return": directionCode = 6; break; } if (directionCode == -1) throw new Exception("无此输出类型[" + directionStr + "]"); return (ParameterDirection) directionCode; } #endregion } }