C#调用存储过程的几种方法介绍 （转）

View Code 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; }

转载于:https://www.cnblogs.com/Mr0909/archive/2011/05/12/2044788.html