调用存储过程通用类 

  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 }

 

posted @ 2014-12-01 17:00  打倒挨踢者  阅读(178)  评论(0)    收藏  举报