Solved

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

Posted on 2010-11-29
5
533 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: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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
"Emulate" TAB key when press Enter Key 3 49
Where on a calculated field 1 22
How to use NFS (Network File System) in Asp.net mvc 5? 4 45
asp.net mvc 2 25
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

830 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