调用存储过程通用类
1 using System; 2 using System.Data; 3 using System.Data.SqlClient; 4 5 namespace Helper 6 { 7 public class ProcedureHelper 8 { 9 private string connectionString; 10 11 public ProcedureHelper(string strConn) 12 { 13 connectionString = strConn; 14 } 15 16 /// <summary> 17 /// 执行存储过程返回DataSet 18 /// </summary> 19 /// <param name="storedProcName">存储过程名</param> 20 /// <param name="parameters">存储过程参数</param> 21 /// <param name="tableName">DataSet结果中的表名</param> 22 /// <returns>DataSet</returns> 23 public DataSet ExecuteDataSet(string storedProcName, IDataParameter[] parameters, string tableName) 24 { 25 using (var connection = new SqlConnection(connectionString)) 26 { 27 var dataSet = new DataSet(); 28 connection.Open(); 29 var sqlDA = new SqlDataAdapter(); 30 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters); 31 sqlDA.Fill(dataSet, tableName); 32 connection.Close(); 33 return dataSet; 34 } 35 } 36 37 /// <summary> 38 /// 执行存储过程返回DataTable 39 /// </summary> 40 /// <param name="storedProcName">存储过程名</param> 41 /// <param name="parameters">存储过程参数</param> 42 /// <returns>DataTable</returns> 43 public DataTable ExecuteDataTable(string storedProcName, IDataParameter[] parameters) 44 { 45 using (var connection = new SqlConnection(connectionString)) 46 { 47 var dateTable = new DataTable(); 48 connection.Open(); 49 var sqlDA = new SqlDataAdapter(); 50 sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters); 51 sqlDA.Fill(dateTable); 52 connection.Close(); 53 return dateTable; 54 } 55 } 56 57 /// <summary> 58 /// 执行存储过程返回受影响行数 59 /// </summary> 60 /// <param name="storedProcName">存储过程名称</param> 61 /// <param name="parameters">存储过程参数</param> 62 /// <returns>int</returns> 63 public int ExecuteNonQuery(string storedProcName, IDataParameter[] parameters) 64 { 65 using (var connection = new SqlConnection(connectionString)) 66 { 67 connection.Open(); 68 var cmd = BuildQueryCommand(connection, storedProcName, parameters); 69 int affectedRowsCount = cmd.ExecuteNonQuery(); 70 return affectedRowsCount; 71 } 72 } 73 74 /// <summary> 75 /// 执行存储过程,并返回查询所返回的结果集中第一行的第一列 76 /// </summary> 77 /// <param name="storedProcName">存储过程名</param> 78 /// <param name="parameters">存储过程参数</param> 79 /// <returns>object</returns> 80 public object ExecuteScalar(string storedProcName, IDataParameter[] parameters) 81 { 82 using (var connection = new SqlConnection(connectionString)) 83 { 84 connection.Open(); 85 var cmd = BuildQueryCommand(connection, storedProcName, parameters); 86 object returnValue = cmd.ExecuteScalar(); 87 return returnValue; 88 } 89 } 90 91 /// <summary> 92 /// 执行查询语句,返回SqlDataReader 93 /// </summary> 94 /// <param name="storedProcName">存储过程名称</param> 95 /// <param name="parameters">存储过程参数</param> 96 /// <returns>SqlDataReader</returns> 97 public SqlDataReader ExecuteDataReader(string storedProcName, IDataParameter[] parameters) 98 { 99 using (var connection = new SqlConnection(connectionString)) 100 { 101 connection.Open(); 102 var cmd = BuildQueryCommand(connection, storedProcName, parameters); 103 SqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); 104 return myReader; 105 } 106 } 107 108 /// <summary> 109 /// 构建 SqlCommand 对象(用来返回一个结果集,而不是一个整数值) 110 /// </summary> 111 /// <param name="connection">数据库连接</param> 112 /// <param name="storedProcName">存储过程名</param> 113 /// <param name="parameters">存储过程参数</param> 114 /// <returns>SqlCommand</returns> 115 private SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, 116 IDataParameter[] parameters) 117 { 118 var command = new SqlCommand(storedProcName, connection); 119 command.CommandType = CommandType.StoredProcedure; 120 foreach (IDataParameter dataParameter in parameters) 121 { 122 var parameter = (SqlParameter) dataParameter; 123 if (parameter != null) 124 { 125 // 检查未分配值的输出参数,将其分配以DBNull.Value. 126 if ((parameter.Direction == ParameterDirection.InputOutput || 127 parameter.Direction == ParameterDirection.Input) && 128 (parameter.Value == null)) 129 { 130 parameter.Value = DBNull.Value; 131 } 132 command.Parameters.Add(parameter); 133 } 134 } 135 return command; 136 } 137 } 138 }
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步