Datatable and Dataset issue

[princesszea]

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

 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;
                }

 

[Rhys666]

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

 

[princesszea]

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?

              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

 

[Rhys666]

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

 

[phinoppix]

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:

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