ADO.NET vs result set from stored procedure

• Carl B.

  SSCertifiable

  Points: 7931

  More actions

  February 17, 2005 at 11:22 am

  #86907

  Hello Everyone,

  Never done that before so what is the way to get a result set from a stored procedure availlable for ADO.NET?

  Is it simply by issuing a query inside of the stored procedure?

  Is it by using IN and OUT parameter?

  Not sure but I think it is by issuing the query inside the stored proc.

  Thanks from a newby in this particular area.

  Carl

• John Jakob

  SSCommitted

  Points: 1720

  More actions

  February 17, 2005 at 12:42 pm

  #542917

  Well here's an example using code I use to fill a combo box ["lbDBA"].  May not be the most efficient, but it certainly works.  I take the easy approach and choose not to mess with INPUT and OUTPUT parameters.  I just call the SP with an "EXEC", and bind variables directly to the fields in the result set.  The stored proc. definition is included also, for reference.

  "glDBConnString" is a string containing the database connection information.

  HTH

  - john

   

  private void PopulateDBAComboBox()      

        {

           // Populates a ComboBox Pulling Data From the Database 

           // Set Up A SQL Connection String

           SqlConnection myConnection = new SqlConnection(glDBConnString);       

       

           // Set the SQL Command

           string SQL = "EXEC SI_spGetRecipients";

           // Set Up A SQL Command Using the SQL Connection

           SqlCommand myCommand = new SqlCommand(SQL, myConnection);

           // Set Up a SQL Data Adapter

           SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(myCommand);

           DataSet ds = new DataSet();

           mySqlDataAdapter.Fill(ds, "MyData");

           // Clear the List Box

           lbDBA.Items.Clear();

           // Bind the Combo Box to the Data Source

           int index = 0;

           foreach (DataRow datarow in ds.Tables["MyData"].Rows)

           {

              String NTDomainID  = datarow[0].ToString();  // ID Value is First Value (Index 0)

              String description = datarow[1].ToString();  // Desc. is Second Value (Index 1)

          

              // Add this Item to the Combo Box

              ListItem item = new ListItem(description, NTDomainID);

              lbDBA.Items.Add(item);

              index++;

           }

           // Add An "[All]" Option to Combo Box

           ListItem itemNone = new ListItem(zALL, zALL);

           lbDBA.Items.Add(itemNone);

           // Set the Initial Index

           lbDBA.SelectedIndex = index;        

        }

   

   

   

  CREATE     PROCEDURE SI_spGetRecipients

  AS

  BEGIN

    SELECT r.recipient_id,                     

            r.description

     FROM  SI_recipients r WITH (NOLOCK)   

     ORDER BY r.description

     

  END

  GO

• Carl B.

  SSCertifiable

  Points: 7931

  More actions

  February 17, 2005 at 12:56 pm

  #542920

  Thanks John,

  It is a detailled answer (more than I expect ) to my question.

  Best regards,

  Carl