Read multiple recordset with one connection 
Author Message
 Read multiple recordset with one connection

      Hi,

      I want to be able to open up one connection and fill one DataSet with
two tables coming from two separate StoredProcs (with their own input
parameters).

      I'm sure there's a way to do this. Would someone be able to show me a
code sample on how to do this? Here's my existing code that allows me to run
one stored procedure:

      SqlConnection conn = new
SqlConnection("server=MyServer;database=MyDB;uid=sa;pwd=12345;");
      SqlDataAdapter da = new SqlDataAdapter("sp_StoredProc1", conn);
      da.SelectCommand.CommandType = CommandType.StoredProcedure;
      SqlParameter pCompanyID, pDivisionID;


      pCompanyID.Direction = ParameterDirection.Input;
      pCompanyID.Value = CompanyID;
      da.SelectCommand.Parameters.Add(pCompanyID);


      pDivisionID.Direction = ParameterDirection.Input;
      pDivisionID.Value = "SP";
      da.SelectCommand.Parameters.Add(pDivisionID);

      DataSet ds = new DataSet();
      da.Fill(ds);

--------------------------------------------------------------------------
      Thanks,

      Sam



Tue, 29 Mar 2005 10:46:08 GMT  
 Read multiple recordset with one connection
This should do the trick:

SqlConnection _conn = new SqlConnection("Data Source=localhost;Initial
Catalog=Northwind;User Id=sa;Password=;");
_conn.Open();

SqlDataAdapter _da = new SqlDataAdapter();

DataSet _ds = new DataSet();

_da.SelectCommand = new SqlCommand();
_da.SelectCommand.Connection = _conn;

//Set the command text and add params
_da.SelectCommand.CommandText = "sp_GetCustomers";

"abc";

//Your first table
_da.Fill(_ds,"tblCustomers");

//Clear the params
_da.SelectCommand.Parameters.Clear();

//Reset the command text
_da.SelectCommand.CommandText = "sp_GetCompanies";

//Your second table
_da.Fill(_ds,"tblCompanies");

foreach(DataRow _dr in _ds.Tables["tblCustomers"].Rows)
{
Console.WriteLine((string)_dr["Name"]);

Quote:
}

foreach(DataRow _dr in _ds.Tables["tblCompanies"].Rows)
{
Console.WriteLine((string)_dr["CompanyID"]);

Quote:
}

_conn.Close();


Quote:
>       Hi,

>       I want to be able to open up one connection and fill one DataSet
with
> two tables coming from two separate StoredProcs (with their own input
> parameters).

>       I'm sure there's a way to do this. Would someone be able to show me
a
> code sample on how to do this? Here's my existing code that allows me to
run
> one stored procedure:

>       SqlConnection conn = new
> SqlConnection("server=MyServer;database=MyDB;uid=sa;pwd=12345;");
>       SqlDataAdapter da = new SqlDataAdapter("sp_StoredProc1", conn);
>       da.SelectCommand.CommandType = CommandType.StoredProcedure;
>       SqlParameter pCompanyID, pDivisionID;


>       pCompanyID.Direction = ParameterDirection.Input;
>       pCompanyID.Value = CompanyID;
>       da.SelectCommand.Parameters.Add(pCompanyID);


>       pDivisionID.Direction = ParameterDirection.Input;
>       pDivisionID.Value = "SP";
>       da.SelectCommand.Parameters.Add(pDivisionID);

>       DataSet ds = new DataSet();
>       da.Fill(ds);

> --------------------------------------------------------------------------
>       Thanks,

>       Sam



Tue, 29 Mar 2005 12:49:31 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Using a single database connection to access multiple snapshot recordsets

2. Using a single database connection to access multiple snapshot recordsets

3. Multiple Recordset in one form

4. Multiple views = multiple recordsets?

5. Synchronizing multiple forms with multiple recordsets

6. Dynaset with Bookmarks to SQL Server connection makes a CRecordset connection read-only

7. Multiple documents or multiple views one document

8. Multiple documents or multiple views one document

9. read hexadecimalvalues one by one out from a file

10. Connection to database and obtaining a recordset

11. Set connection point cause Recordset MoveFirst error

12. ReOpen ADO Recordset with a current Connection

 

 
Powered by phpBB® Forum Software