Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Datatable and Dataset issue

Status
Not open for further replies.

princesszea

Technical User
Aug 11, 2008
33
GB
Hi,

I’m using the code below to get a datatable.

I need to get 2 datatables using this method and I want to end up with one dataset.

Can someone please tell me the best way to this. As I’m basically repeating the code below to and changing the stored procedure.

Thanks


Code:
 DataTable dt = new DataTable();

            try
            {
                using (SqlCommand cmd = sqlConn.CreateCommand())
                {
                   

                    Dictionary<string, string> parameters = (Dictionary<string, string>)HttpContext.Current.Session["SearchDictionary"];

                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = StoredProcedures.sp1;
                    foreach (KeyValuePair<string, string> p in parameters)
                    {
                        string key = p.Key;
                        string value = p.Value;

                        ///<remarks>Creates the parameter</remarks>
                        cmd.Parameters.AddWithValue(key, value);
                    }

                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        da.Fill(dt);
                    }
                    this._data = dt;
                }
 
Can you create a single stored procedure calling the two required procedures to return multiple result sets with one hit?



Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett
 
Thanks for the reply

Not sure if is this is the best way but the code below is what I have done and it returns a data set.

Before I was adding a data table to my control using

uc.DataSource = GetMyDATA().Data;

but this does not allow me to add a dataset. How do I pass the dataset to the control?
Code:
              cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = StoredProcedures.GetData;
                    foreach (KeyValuePair<string, string> p in parameters)
                    {
                        string key = p.Key;
                        string value = p.Value;

                        ///<remarks>Creates the parameter</remarks>
                        cmd.Parameters.AddWithValue(key, value);
                    }
                    SqlCommand cmdGender = sqlConn.CreateCommand();

                    Cmd2.CommandType = CommandType.StoredProcedure;
                    Cmd2.CommandText = StoredProcedures.GetData2;
                    foreach (KeyValuePair<string, string> p in parameters)
                    {
                        string key = p.Key;
                        string value = p.Value;

                        ///<remarks>Creates the parameter</remarks>
                        Cmd2Parameters.AddWithValue(key, value);
                    }

                    using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                    {
                        da.Fill(ds);
                    }
                    using (SqlDataAdapter da2 = new SqlDataAdapter(cmd2))
                    {
                        da2.Fill(ds);
                    }
                    this._data = ds;
                }

Thanks
 
How do I pass the dataset to the control?
That would depend entirely on the control. If, as I suspect from the local name 'uc', it is a user control, you'd need to change it.

I would say though that any other suggestions are going to be assumptions based on guesses at what you're trying to do. It appears you are trying to display additional data in a form, (web or windows?), based on the results of a second stored procedure. Can we go back a step and ask what you are trying to achieve though?

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett
 
Replying on your first post, you can probably do something like this if you don't want using just a single SP to get two recordsets:

Code:
public DataSet GetMe_As_DataSet()
{
  DataSet tmp = new DataSet();
  tmp.Add(innerGetMe_As_DataTable(sp1));
  tmp.Add(innerGetMe_As_DataTable(sp2));
  return tmp;
}

public DataTable GetMe_As_DataTable()
{
  return innerGetMe_As_DataTable(someSP);
}

private DataTable innerGetMe_As_DataTable(string spname)
{
  ...
}

Of course, this means 2 calls to the db server. Thought you should be aware of this.

hth [wink]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top