Call Stored Procedure With In/Out Parameter In 3-Tier

Introduction:

In this tutorial we will learn how to call the stored procedure with input/output parameter in tier model in asp.net. In 2-tier you can easily call the stored procedure with in/out parameter but some different tricks are used here to call the stored procedure in n-tier.

For calling a stored procedure with parameter we have to pass the parameter required to procedure. Just same here we are passing parameter as input as well as output. Follow the steps bellow.

Step 1:

Create DataBase Table

Create Table Tbl_Jobs(JobId Int Primary Key,Description Varchar(100))

Step 2:

Create Stored Procedure

Create Procedure Sp_GetJobDesc

(@jobid int input,@desc Varchar(100)output)

as Begin

Select Description From Tbl_Jobs Where JobId=@jobid

End

Step 3:

Write Down this in your Data Access Layer To Call The stored procedure

public static Array CallStoredProcedure(string[,] InParam, string[,] OutParam, string _procename)

{

try

{

string _result;

sqlcn.Open();

sqlcmd = new SqlCommand(_procename, sqlcn);

sqlcmd.CommandType = CommandType.StoredProcedure;

//InputParaMeter

for (int i = 0; i < InParam.Length / 2; i++)

{

sqlcmd.Parameters.AddWithValue(InParam[i, 0], InParam[i, 1]);

}

//OutPutParamater

for (int i = 0; i < OutParam.Length / 2; i++)

{

if (OutParam[i, 1] == "SqlDbType.Int")

{

sqlcmd.Parameters.Add(OutParam[i, 0], SqlDbType.Int);

}

else

{

sqlcmd.Parameters.Add(OutParam[i, 0], SqlDbType.VarChar, 50);

}

sqlcmd.Parameters[OutParam[i, 0]].Direction = ParameterDirection.Output;

}

sqlcmd.ExecuteNonQuery();

string[,] _temparray = new string[OutParam.Length / 2, 2];

for (int i = 0; i < OutParam.Length / 2; i++)

{

_result = sqlcmd.Parameters[OutParam[i, 0]].Value.ToString();

_temparray[i, 0] = OutParam[i, 0];

_temparray[i, 1] = _result;

}

return _temparray;

}

catch (Exception)

{

throw;

}

finally

{

sqlcn.Close();

}

This method will take the string array for input,output and procedure name to be called as argument. Now we have to pass those argument from our second layer i.e. Business Logic as follows step

Step 4:

Call the method written in DAL and pass the list of argument required for input and output string array as well as name of the stored procedure.

public void GetDesc(string __jobid)

{

string _sql = " Sp_GetJobDesc ";

string[,] Inparam = new string[1, 2]

{

{"@jopbid",_jobid}

};

string[,] OutParam = new string[1, 2]

{

{"@desc","SqlDbType.VarChar(100)"},

};

string[,] Outresult;

try

{

Outresult = (string[,])DataHelper.CallStoredProcedure(Inparam, OutParam, _sql);

_desc= Outresult[0, 1];//this is local varible to receive the output.

}

catch (Exception)

{

throw;

}

In this way you have to call the CallStoredProcedure Method of DAL class. Now you can Simply call the method of BAL class and can get the result like bellow.

Step 5:

This is your UI layer. Here you have to call the method GetDesc of BAL Class Like bellow.

BALClass obj=new BALClass();

Obj.GetDesc(“PassJobid Here”);

String _desc=obj._desc;

Conclusion:

In this manner by preparing the input/output parameter for stored procedure you can call the it in no.of tiers.