locked
Is there a method filling of DataTable from StoredProcedure? (c#) RRS feed

  • Question

  • User1385195316 posted

    Is there a method filling of DataTable from StoredProcedure? (c#)

    I know that SP's always returns single value.But I need the SP to return datatable. Is this possible?

    Thanks.

     

    Tuesday, August 14, 2007 2:18 AM

Answers

  • User1191518856 posted

    There's no restriction that says you can only return a single value. If you just do a SELECT at then end then you will get these values back:
     

    create procedure all_employees 
    as select * from employees 
    go 
    
    exec all_employees 
     
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 14, 2007 2:45 AM
  • User945078486 posted

    Hi,

    Here is the example

    CREATE PROCEDURE FillDataTable
    AS
     select au_id,au_lname,au_fname from authors
     RETURN

    Use the procedure


            string sqlconnect = "Data Source=localhost\\sqlexpress;Initial Catalog=pubs;Integrated Security=True";
            System.Data.SqlClient.SqlConnection sqlconnection = new System.Data.SqlClient.SqlConnection(sqlconnect);

           
            System.Data.SqlClient.SqlCommand sqlcommand = new System.Data.SqlClient.SqlCommand();
            sqlcommand.Connection = sqlconnection;
            sqlcommand.CommandText = "FillDataTable";
            sqlcommand.CommandType = CommandType.StoredProcedure;

            System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter();
            DataSet ds = new DataSet();
            da.SelectCommand = sqlcommand;  
         

            sqlconnection.Open();
            sqlcommand.ExecuteNonQuery();
            da.Fill(ds);
            GridView1.DataSource = ds;
            GridView1.DataBind();
            sqlconnection.Close();

    Hope it helps,

    Jessica

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 15, 2007 10:58 PM

All replies

  • User-137329696 posted

    SP does return the table values, whats your sp is returning .Give some details

    Tuesday, August 14, 2007 2:34 AM
  • User1191518856 posted

    There's no restriction that says you can only return a single value. If you just do a SELECT at then end then you will get these values back:
     

    create procedure all_employees 
    as select * from employees 
    go 
    
    exec all_employees 
     
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, August 14, 2007 2:45 AM
  • User945078486 posted

    Hi,

    Here is the example

    CREATE PROCEDURE FillDataTable
    AS
     select au_id,au_lname,au_fname from authors
     RETURN

    Use the procedure


            string sqlconnect = "Data Source=localhost\\sqlexpress;Initial Catalog=pubs;Integrated Security=True";
            System.Data.SqlClient.SqlConnection sqlconnection = new System.Data.SqlClient.SqlConnection(sqlconnect);

           
            System.Data.SqlClient.SqlCommand sqlcommand = new System.Data.SqlClient.SqlCommand();
            sqlcommand.Connection = sqlconnection;
            sqlcommand.CommandText = "FillDataTable";
            sqlcommand.CommandType = CommandType.StoredProcedure;

            System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter();
            DataSet ds = new DataSet();
            da.SelectCommand = sqlcommand;  
         

            sqlconnection.Open();
            sqlcommand.ExecuteNonQuery();
            da.Fill(ds);
            GridView1.DataSource = ds;
            GridView1.DataBind();
            sqlconnection.Close();

    Hope it helps,

    Jessica

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, August 15, 2007 10:58 PM