C # calling a generic class of stored procedures

Using System;
Using System.Collections.Generic;
Using System.Text;
Using System.Data.SqlClient;
Using System.Collections;
Using System.Data;

Summary: Data access Assistant.
Author: Zhiqiao
Date: 2008/07/02

Namespace Zhiqiao.dataaccesshelper
{
The stored procedure call assistant.
public class Storeprocedure
{
The name of the stored procedure.
private string _name;
The database connection string.
private string _constr;

Constructors
sProcName: Stored procedure name;
Constr: Database connection string.
Public Storeprocedure (String sprocname, String constr) {
_constr = Constr;
_name = sProcName;
}

Executes a stored procedure and does not return a value.
Paravalues: List of parameter values.
Return:void
public void Executenoquery (params object [] paravalues) {
using (SqlConnection con = new SqlConnection (_constr)) {
SqlCommand comm = new SqlCommand (_name, con);
Comm.commandtype = CommandType.StoredProcedure;

Addinparavalues (comm, paravalues);

Con. Open ();
Comm. ExecuteNonQuery ();
Con. Close ();
}
}

Executes a stored procedure to return a table.
Paravalues: List of parameter values.
Return:datatable
Public DataTable executedatatable (params object [] paravalues) {
SqlCommand comm = new SqlCommand (_name, New SqlConnection (_constr));
Comm.commandtype = CommandType.StoredProcedure;
Addinparavalues (comm, paravalues);

SqlDataAdapter SDA = new SqlDataAdapter (comm);
DataTable dt = new DataTable ();
Sda. Fill (DT);

return DT;
}

Executes the stored procedure, returning the SqlDataReader object,
The database connection shuts down automatically while the SqlDataReader object is closed.
Paravalues: The Parameter Value class table to pass to the stored procedure.
Return:sqldatareader
Public SqlDataReader Executedatareader (params object [] paravalues) {
SqlConnection con = new SqlConnection (_CONSTR);
SqlCommand comm = new SqlCommand (_name, con);
Comm.commandtype = CommandType.StoredProcedure;
Addinparavalues (comm, paravalues);
Con. Open ();
Return COMM. ExecuteReader (commandbehavior.closeconnection);
}

Gets a list of parameters for the stored procedure.
Private ArrayList Getparas () {
SqlCommand comm = new SqlCommand ("Dbo.sp_sproc_columns_90",
New SqlConnection (_constr));
Comm.commandtype = CommandType.StoredProcedure;
Comm. Parameters.addwithvalue ("@procedure_name", (object) _name);
SqlDataAdapter SDA = new SqlDataAdapter (comm);

DataTable dt = new DataTable ();
Sda. Fill (DT);

ArrayList al = new ArrayList ();
for (int i = 0; i < dt. Rows.Count; i + +) {
Al. ADD (dt. Rows[i][3]. ToString ());
}
Return al;
}

Add parameters and assign values to SqlCommand.
private void Addinparavalues (SqlCommand comm, params object [] paravalues) {
Comm. Parameters.Add (New SqlParameter ("@RETURN_VALUE", SqlDbType.Int));
Comm. parameters["@RETURN_VALUE"]. Direction =
ParameterDirection.ReturnValue;
if (paravalues!= null) {
ArrayList al = Getparas ();
for (int i = 0; i < paravalues.length i + +) {
Comm. Parameters.addwithvalue (Al[i + 1]. ToString (),
Paravalues[i]);
}
}
}
}
}