-
October 9th, 2009, 05:34 AM
#1
Filling a DataSet using a returned value from StoredProcedure
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.
-
January 20th, 2010, 03:28 AM
#2
Re: Filling a DataSet using a returned value from StoredProcedure
Good Day valluvanl
First change this
Code:
craete procedure [dbo].[sample_procedure]
as
declare @count int
select @count = count(*) from dbo.Q_C_Users
return @count
to this
Code:
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
Code:
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
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|