Filling a DataSet using a returned value from StoredProcedure

[valluvanl]

Hi all, I'm new to .net.

I'm trying to return a value from a StoredProcedure to a DataSet

----STORED PROCEDURE-------

craete procedure [dbo].[sample_procedure]
as
declare @count int
select @count = count(*) from dbo.Q_C_Users
return @count


-----------------c# FUNCTION-----------------------

public int returnCount()

{

int availableTables=0;

SqlConnection con = ConnectionManager.GetQueensDBConnection();
SqlCommand cmd = new SqlCommand("SAMPLE_PROCEDURE", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
dataAdapter.Fill(ds);
availableTables = System.Convert.ToInt16(ds.Tables[0].Rows[0].ItemArray[0].ToString());
return availableTables;

}


// by using the above code i coudn't get any output.

but once the stored procedure changed to a nore mal query its working i mean if the stored procedure written as


CREATE procedure [dbo].[SAMPLE_PROCEDURE]
AS
select count(*) from dbo.Q_C_Users


Its working fine, I guess if we return a value from Stored Proc we should add some parameters to SqlCommand but i have no idea about that.


PLS HELP, THANKS IN ADVANCED.

[vuyiswam]

Good Day valluvanl

First change this

craete procedure [dbo].[sample_procedure]
as
declare @count int
select @count = count(*) from dbo.Q_C_Users
return @count

to this

craete procedure [dbo].[sample_procedure]
(
@OutResult int OUTPUT
)
as
set @OutResult = (select  count(*) from dbo.Q_C_Users)

and your C# code change it to look like this

public int returnCount()

{

int availableTables=0;

SqlConnection con = ConnectionManager.GetQueensDBConnection();
SqlCommand cmd = new SqlCommand("SAMPLE_PROCEDURE", con);
cmd.CommandType = CommandType.StoredProcedure;
cmdselect.Parameters.Add("@OutResult", SqlDbType.Int, 4); 
cmdselect.Parameters["@OutResult"].Direction = ParameterDirection.Output;
try
{
     con.Open();
     cmd.ExecuteNonQuery();
      availableTables =(int) cmdselect.Parameters["@OutResult"].Value;
}
 catch (SqlException)
{
         throw;
}
finally
{
con.Close();
}
return availableTables;

and the Code will work.

Kind Regards

Vuyiswa Maseko