Thursday, August 6, 2009

Using output parameter of SQL Server stored procedure in C#

Recently I need to write a small piece of code for one small module that tracks different tasks of a group of production support worker.
In the first part of this post, I would like to write some part of the code to depict how to use output parameter in store proc. The name of the store proc is

Alter procedure sp_check_dupl_rec
(
@ticketID
nvarchar(80),
@ErrorFlag char(1) out,
@ErrorText nvarchar(4000) out

)
As
BEGIN
set @ErrorFlag
= 0
Select DISTINCT TicketId from tbl_TaskTracker where TicketId = @ticketID
IF @@ROWCOUNT > 0
begin
set @ErrorFlag =1
set @ErrorText ='Ticket that you are going to enter already exists.'

--print @ErrorText
RETURN
end
END

In the second part of the code , I like to write C# code that is showing how do we use output parameter of SQL SERVER stored proc in our C# code to get relevant information.

bool DuplicateData = false;
string strCon = null;
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader rdr = null;
try
{
strCon = System.Configuration.ConfigurationSettings.AppSettings["dbtest"];
conn = new SqlConnection(strCon);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
cmd = new SqlCommand("sp_check_dupl_rec", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter oParam = cmd.Parameters.AddWithValue("@ticketID", txtTicketNo.Text);
oParam = cmd.Parameters.AddWithValue("@ErrorFlag", 'a');
oParam.Direction = ParameterDirection.Output;
oParam = cmd.Parameters.AddWithValue("@ErrorText", '0');
oParam.Size = 4000;
oParam.Direction = ParameterDirection.Output;
rdr = cmd.ExecuteReader();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
if (conn != null)
conn.Close();
if (rdr != null)
rdr.Close();
}
int intErrCode = Convert.ToInt16(cmd.Parameters["@ErrorFlag"].Value);
if (intErrCode == 1)
{
string strErrTxt = Convert.ToString(cmd.Parameters["@ErrorText"].Value);
MessageBox.Show(strErrTxt);
DuplicateData = false;
}
else
DuplicateData = true;
return DuplicateData;
}

Please have a look specially at the highlighted part of the above code.
Kindly check prior to open the connection whether the connection has already been opened and don't forget to close the datareader after using it!

No comments:

FEEDJIT Live Traffic Feed