Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2010-11-29
5
Medium Priority
?
557 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 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
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 Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

810 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