Solved

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

Posted on 2010-11-29
5
519 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 74

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now