Run any Stored Procedure from .Net - version 2

Please change "(less than)" with it's sign.

In my last article I found some limitation and this code also quite large so I’ve write it’s modified code to run stored procedure. It’s more powerful and comparatively very low code and use is also very easy.

When you run a stored procedure by that code you need to call one function to run specific stored procedure and for every argument need to call just a function. Also here I’ve handle arraylist internally which was need to use externally by you. Also you can use ADO.Net ’s enum to provide table’s field data type and parameter direction also you can use ado.net enum.

Hove ever I’m giving one sample code which you need to write. It’s very easy, just create an object of that class, then invoke these functions. Codes are below:

Create object.

ExecSpClass objSp = new ExecSpClass("Connection string write here");

Pass arguments:

objSp.AddArgument("@nextName", "Suman Biswas", SqlDbType.VarChar, ParameterDirection.Input);

objSp.AddArgument("@id", "2",SqlDbType.Int,ParameterDirection.Input);

Now execute stored procedure:

objSp.ExecSpNonQuery("spProjectCategoryAdd");

Code is complete from your side to run a stored procedure.

I’m giving my code below which is doing the actual work, to learn it you can read it, I think it’s quite easy to understand.

public class ExecSpClass

{

internal class SPArgBuild

{

internal string parameterName = "";

internal string parameterValue = "";

internal SqlDbType pramValueType;

internal ParameterDirection parmDirection;

internal SPArgBuild(string pramName, string pramValue, SqlDbType pramValueType, ParameterDirection parmDirection )

{

this.parameterName = pramName;

this.parameterValue = pramValue;

this.pramValueType = pramValueType;

this.parmDirection = parmDirection;

}

}

SqlConnection conn;

SqlCommand cmd;

SqlDataAdapter adap;

ArrayList spPramArrList = new ArrayList();

public ExecSpClass(string dbConnStr)

{

conn = new SqlConnection(dbConnStr);

}

private void OpenConection()

{

cmd = new SqlCommand();

adap = new SqlDataAdapter(cmd);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Connection = conn;

if (conn.State == ConnectionState.Closed ||

conn.State == ConnectionState.Broken ||

conn.State == ConnectionState.Connecting ||

conn.State != ConnectionState.Executing ||

conn.State != ConnectionState.Fetching ||

conn.State != ConnectionState.Open)

{

conn.Open();

}

}

private void CloseConnection()

{

if (conn.State != ConnectionState.Closed ||

conn.State == ConnectionState.Broken ||

conn.State == ConnectionState.Connecting ||

conn.State != ConnectionState.Executing ||

conn.State != ConnectionState.Fetching ||

conn.State == ConnectionState.Open)

{

conn.Close();

cmd.Dispose();

adap.Dispose();

}

}

public void AddArgument(string spParmName, string spParmValue, SqlDbType spPramValueType, ParameterDirection parmDirection)

{

SPArgBuild spArgBuiltObj = new SPArgBuild(spParmName, spParmValue, spPramValueType,parmDirection);

spPramArrList.Add(spArgBuiltObj);

}

public DataTable ExecSpGetDataTable(string spName)

{

string spPramName = "";

string spPramValue = "";

SqlDbType spPramDataType;

DataSet ds=new DataSet();

OpenConection();

cmd.CommandText = spName;

for (int i = 0; i (less than) spPramArrList.Count; i++)

{

spPramName = ((SPArgBuild)spPramArrList[i]).parameterName;

spPramValue = ((SPArgBuild)spPramArrList[i]).parameterValue;

spPramDataType = ((SPArgBuild)spPramArrList[i]).pramValueType;

SqlParameter pram = null;

pram = cmd.Parameters.Add(spPramName, spPramDataType);

pram.Value = spPramValue;

pram.Direction = ((SPArgBuild)spPramArrList[i]).parmDirection;

}

SqlDataAdapter adap = new SqlDataAdapter(cmd);

adap.Fill(ds);

CloseConnection();

return ds.Tables[0];

}

public string ExecSpScalar(string spName)

{

string spPramName = "";

string spPramValue = "";

OpenConection();

cmd.CommandText = spName;

SqlDbType spPramDataType;

for (int i = 0; i (less than) spPramArrList.Count; i++)

{

spPramName = ((SPArgBuild)spPramArrList[i]).parameterName;

spPramValue = ((SPArgBuild)spPramArrList[i]).parameterValue;

spPramDataType = ((SPArgBuild)spPramArrList[i]).pramValueType;

SqlParameter pram = null;

pram = cmd.Parameters.Add(spPramName, spPramDataType);

pram.Value = spPramValue;

pram.Direction = ((SPArgBuild)spPramArrList[i]).parmDirection;

}

string noRowEfect = cmd.ExecuteScalar().ToString();

CloseConnection();

return noRowEfect;

}

public int ExecSpNonQuery(string spName)

{

string spPramName = "";

string spPramValue = "";

OpenConection();

cmd.CommandText = spName;

SqlDbType spPramDataType ;

for (int i = 0; i (less than) spPramArrList.Count; i++)

{

spPramName = ((SPArgBuild)spPramArrList[i]).parameterName;

spPramValue = ((SPArgBuild)spPramArrList[i]).parameterValue;

spPramDataType = ((SPArgBuild)spPramArrList[i]).pramValueType;

SqlParameter pram = null;

pram = cmd.Parameters.Add(spPramName, spPramDataType);

pram.Value = spPramValue;

pram.Direction = ((SPArgBuild)spPramArrList[i]).parmDirection;

}

int noRowEfect= cmd.ExecuteNonQuery();

CloseConnection();

return noRowEfect;

}

}

If you get any problem to run that code or understand please contact with me.