C#调用存储过程的几个方法

/
数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.        

public
 
static
 
string
 connectionString 
=
 System.Configuration.ConfigurationManager.ConnectionStrings[
"
ConnectionString
"
].ConnectionString;

         
///
 
<summary>

         
///
 执行存储过程，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
         
///
 
</summary>

         
///
 
<param name="storedProcName">
存储过程名
</param>

         
///
 
<param name="parameters">
存储过程参数
</param>

         
///
 
<returns>
SqlDataReader
</returns>


        
public
 
static
 SqlDataReader RunProcedure(
string
 storedProcName, IDataParameter[] parameters)
         {
             SqlConnection connection 
=
 
new
 SqlConnection(connectionString);
             SqlDataReader returnReader;
             connection.Open();
             SqlCommand command 
=
 BuildQueryCommand(connection, storedProcName, parameters);
             command.CommandType 
=
 CommandType.StoredProcedure;
             returnReader 
=
 command.ExecuteReader(CommandBehavior.CloseConnection);
             
return
 returnReader;

         }


         
///
 
<summary>

         
///
 执行存储过程
         
///
 
</summary>

         
///
 
<param name="storedProcName">
存储过程名
</param>

         
///
 
<param name="parameters">
存储过程参数
</param>

         
///
 
<param name="tableName">
DataSet结果中的表名
</param>

         
///
 
<returns>
DataSet
</returns>


        
public
 
static
 DataSet RunProcedure(
string
 storedProcName, IDataParameter[] parameters, 
string
 tableName)
         {
             
using
 (SqlConnection connection 
=
 
new
 SqlConnection(connectionString))
             {
                 DataSet dataSet 
=
 
new
 DataSet();
                 connection.Open();
                 SqlDataAdapter sqlDA 
=
 
new
 SqlDataAdapter();
                 sqlDA.SelectCommand 
=
 BuildQueryCommand(connection, storedProcName, parameters);
                 sqlDA.Fill(dataSet, tableName);
                 connection.Close();
                 
return
 dataSet;
             }
         }
         
public
 
static
 DataSet RunProcedure(
string
 storedProcName, IDataParameter[] parameters, 
string
 tableName, 
int
 Times)
         {
             
using
 (SqlConnection connection 
=
 
new
 SqlConnection(connectionString))
             {
                 DataSet dataSet 
=
 
new
 DataSet();
                 connection.Open();
                 SqlDataAdapter sqlDA 
=
 
new
 SqlDataAdapter();
                 sqlDA.SelectCommand 
=
 BuildQueryCommand(connection, storedProcName, parameters);
                 sqlDA.SelectCommand.CommandTimeout 
=
 Times;
                 sqlDA.Fill(dataSet, tableName);
                 connection.Close();
                 
return
 dataSet;
             }
         }


         
///
 
<summary>

         
///
 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值)
         
///
 
</summary>

         
///
 
<param name="connection">
数据库连接
</param>

         
///
 
<param name="storedProcName">
存储过程名
</param>

         
///
 
<param name="parameters">
存储过程参数
</param>

         
///
 
<returns>
SqlCommand
</returns>


        
private
 
static
 SqlCommand BuildQueryCommand(SqlConnection connection, 
string
 storedProcName, IDataParameter[] parameters)
         {
             SqlCommand command 
=
 
new
 SqlCommand(storedProcName, connection);
             command.CommandType 
=
 CommandType.StoredProcedure;
             
foreach
 (SqlParameter parameter 
in
 parameters)
             {
                 
if
 (parameter 
!=
 
null
)
                 {
                     
//
 检查未分配值的输出参数,将其分配以DBNull.Value.


                    
if
 ((parameter.Direction 
==
 ParameterDirection.InputOutput 
||
 parameter.Direction 
==
 ParameterDirection.Input) 
&&

                         (parameter.Value 
==
 
null
))
                     {
                         parameter.Value 
=
 DBNull.Value;
                     }
                     command.Parameters.Add(parameter);
                 }
             }

             
return
 command;
         }

         
///
 
<summary>

         
///
 执行存储过程，返回Output输出参数值        
         
///
 
</summary>

         
///
 
<param name="storedProcName">
存储过程名
</param>

         
///
 
<param name="parameters">
存储过程参数
</param>

         
///
 
<returns>
object
</returns>


        
public
 
static
 
object
 RunProcedure(
string
 storedProcName, IDataParameter[] paramenters)
         {
             
using
 (SqlConnection connection 
=
 
new
 SqlConnection(connectionString))
             {
                 connection.Open();
                 SqlCommand command 
=
 BuildQueryCommand(connection, storedProcName, paramenters);
                 command.ExecuteNonQuery();
                 
object
 obj
=
command.Parameters[
"
@Output_Value
"
].Value; 
//
@Output_Value和具体的存储过程参数对应


                
if
 ((Object.Equals(obj, 
null
)) 
||
 (Object.Equals(obj, System.DBNull.Value)))
                 {
                     
return
 
null
;
                 }
                 
else

                 {
                     
return
 obj;
                 }
             }
         }

         
///
 
<summary>

         
///
 执行存储过程，返回影响的行数        
         
///
 
</summary>

         
///
 
<param name="storedProcName">
存储过程名
</param>

         
///
 
<param name="parameters">
存储过程参数
</param>

         
///
 
<param name="rowsAffected">
影响的行数
</param>

         
///
 
<returns></returns>


        
public
 
static
 
int
 RunProcedure(
string
 storedProcName, IDataParameter[] parameters, 
out
 
int
 rowsAffected)
         {
             
using
 (SqlConnection connection 
=
 
new
 SqlConnection(connectionString))
             {
                 
int
 result;
                 connection.Open();
                 SqlCommand command 
=
 BuildIntCommand(connection, storedProcName, parameters);
                 rowsAffected 
=
 command.ExecuteNonQuery();
                 result 
=
 (
int
)command.Parameters[
"
ReturnValue
"
].Value;
                 
//
Connection.Close();


                
return
 result;
             }
         }

         
///
 
<summary>

         
///
 创建 SqlCommand 对象实例(用来返回一个整数值)    
         
///
 
</summary>

         
///
 
<param name="storedProcName">
存储过程名
</param>

         
///
 
<param name="parameters">
存储过程参数
</param>

         
///
 
<returns>
SqlCommand 对象实例
</returns>


        
private
 
static
 SqlCommand BuildIntCommand(SqlConnection connection, 
string
 storedProcName, IDataParameter[] parameters)
         {
             SqlCommand command 
=
 BuildQueryCommand(connection, storedProcName, parameters);
             command.Parameters.Add(
new
 SqlParameter(
"
ReturnValue
"
,
                 SqlDbType.Int, 
4
, ParameterDirection.ReturnValue,
                 
false
, 
0
, 
0
, 
string
.Empty, DataRowVersion.Default, 
null
));
             
return
 command;
         }