Filling a DataSet using a returned value from StoredProcedure
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 2 of 2

Thread: Filling a DataSet using a returned value from StoredProcedure

  1. #1
    Join Date
    Nov 2007
    Posts
    6

    Question 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.

  2. #2
    Join Date
    Dec 2007
    Location
    South Africa
    Posts
    263

    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
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center