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.