Solved

How do I fill a .NET DataSet with 2 querys via Stored Procedures in C#?

Posted on 2010-11-29
5
528 Views
Last Modified: 2012-05-10
Hello,

I need to make 2 separate database calls using 2 stored procedures. Each call will return a different result set.  Currently I just make 1 call and bind the results to a datatable. I'm rather new to asp.net so I dont know the best way of going about this.  I googled it and saw that you can fill a dataset with 2 sets of results but I could not find any good examples.  I will attach the code im using to fill my datatable right now. If you could show me how I would convert that code to make 2 stored procedure calls to fill my dataset that would be great.

Thanks!
//Page is rendered for the First Time.
if (!IsPostBack)
{
	//initilize
   SqlCommand myCommand = null;

   //Database operations.
   try
   {
   	//Open a Connection to SQL Server.
      SqlConnection myConnection = new SqlConnection(ConnectionString);
      myConnection.Open();

      //Set up my Stored Procedure.
      myCommand = new SqlCommand("Media_GetMedia", myConnection);
      myCommand.CommandType = CommandType.StoredProcedure;
      myCommand.Parameters.AddWithValue("@MediaTypeID", 1);
      myCommand.Parameters.AddWithValue("@MediaID", 2);

      //Execute the Stored Procedure and Fill the datatable
		DataTable myDataTable = new DataTable();
      SqlDataAdapter myDataAdapter = new SqlDataAdapter(myCommand);
      myDataAdapter.Fill(myDataTable);

      //Bind the table to my FormView
      FormView1.DataSource = myDataTable;
      FormView1.DataBind();
	}
   
   //Close Database Connection
   finally
   {
   	//Close my connection.
      myCommand.Connection.Close();
      myCommand.Connection.Dispose();
	}
}

Open in new window

0
Comment
Question by:maddhacker24
5 Comments
 
LVL 10

Accepted Solution

by:
P1ST0LPETE earned 500 total points
ID: 34236078
A DataSet is basically just an array/collection of DataTables.

So this is one way you could do it:
 
DataTable table1 = new DataTable();
//Run code to fill table1....

DataTable table2 = new DataTable();
//Run code to fill table2....

DataSet dataset = new DataSet();
dataset.Tables.Add(table1);
dataset.Tables.Add(table2);


//Then you can access the data in each DataTable like this:
FormView1.DataSource = dataset.Tables[0];
FormView2.DataSource = dataset.Tables[1];

//Or:

foreach(DataRow row in dataset.Tables[0].Rows)
{
    if(row["ColumnName"] == "someString")
    {
        //do something with it....
    }
}

Open in new window

0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 34236089
I can't test ATM, but if memory serves, it should be something like:
//Page is rendered for the First Time.
if (!IsPostBack)
{
    //initilize
    SqlCommand myCommand = null;

    //Database operations.
    try
    {
        //Open a Connection to SQL Server.
        SqlConnection myConnection = new SqlConnection(ConnectionString);

        //Set up my Stored Procedure. Put both queries int the CommandText field
        myCommand = new SqlCommand("Media_GetMedia;Stored_Proc2;", myConnection);
        myCommand.CommandType = CommandType.StoredProcedure;
        myCommand.Parameters.AddWithValue("@MediaTypeID", 1);
        myCommand.Parameters.AddWithValue("@MediaID", 2);
        // add Stored_Proc2's parameters also

        //Execute the Stored Procedure and Fill the datatable
        DataSet myDataSet = new DataSet();
        SqlDataAdapter myDataAdapter = new SqlDataAdapter(myCommand);
        myDataAdapter.Fill(myDataSet);

        //Bind the table to my FormView
        FormView1.DataSource = myDataSet.Tables[0];
        FormView1.DataBind();

        // bind table 2 accordingly
    }

    //Close Database Connection
    finally
    {
        //Close my connection.
        myCommand.Connection.Close();
        myCommand.Connection.Dispose();
    }

Open in new window

0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 34236502
Hi,

You can merge two different results in to one using DataTable,Merge method. However, make sure columns are same for both results.
0
 
LVL 5

Expert Comment

by:bmxer
ID: 34236607
Combine the two sprocs into one and have two select statements instead.  Use this to fill a dataset.  The dataset will now have two tables, so all you need to do is rename them and set a relation ship ...
(Dataset name is ds for example below).

ds.Table[0].TableName = "Table1";
ds.Table[1].TableName = "Table2";

ds.Relations.Add("rel1", ds.Tables["Table1"].Columns["col1"], ds.Tables["Table2"].Columns["col1"]);

Now you have a master detail type of setup

You could continue with this and create a whole database, that would be held in memory.  Not the best, but some people use it to reduce the db calls
0
 

Author Closing Comment

by:maddhacker24
ID: 34254306
Perfect, Thank you!
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Deploying to Azure 3 27
Printing 1 51
HTML - Color not displaying correctly in EMAIL. 6 36
C# winforms programmitically move panels 6 28
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Creating and Managing Databases with phpMyAdmin in cPanel.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question