?
Solved

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

Posted on 2010-11-29
5
Medium Priority
?
548 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 10

Accepted Solution

by:
P1ST0LPETE earned 2000 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:Greg Gamble
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

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

777 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