RE: Multiple table dataset

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Cowboy (Gregory A. Beamer) - MVP (NoSpamMgbworld_at_comcast.netNoSpamM)
Date: 02/28/05 

Date: Mon, 28 Feb 2005 15:21:02 -0800

This is a bit beyond wizard, but here is a quick sample in C# and VB.NET
(check my VB.NET code if you go that route, as I normally code in C# and this
is done without testing the code):

C# 

---
string connString = "{your connection string here}";
string sql = "sprocName here";
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.StoredProcedure;
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
da.TableMappings.Add("Table", "Customers");
da.TableMappings.Add("Table1", "Orders");
da.TableMappings.Add("Table2", "OrderDetails");
try
{
	conn.Open();
	da.Fill(ds);
}
finally
{
	if(conn.State == ConnectionState.Open)
		conn.Close();
	conn.Dispose();
}
VB.NET
------
Dim connString As String = "{your connection string here}"
Dim sql As String = "sprocName here"
Dim conn As New SqlConnection(connString)
Dim cmd As New SqlCommand(sql, conn)
cmd.CommandType = CommandType.StoredProcedure
Dim ds As New DataSet()
Dim da As New SqlDataAdapter()
da.TableMappings.Add("Table", "Customers")
da.TableMappings.Add("Table1", "Orders")
da.TableMappings.Add("Table2", "OrderDetails")
Try
	conn.Open()
	da.Fill(ds)
Finally
	if(conn.State == ConnectionState.Open)
		conn.Close();
	conn.Dispose();
End Try
You should then be able to call the tables using the following
C#
DataTable customers = ds.Tables["Customers"];
DataTable orders = ds.Tables["Orders"];
DataTable orderDetails = ds.Tables["OrderDetails"];
VB.NET
Dim customers As DataTable = ds.Tables["Customers"];
Dim orders As DataTable  = ds.Tables["Orders"];
Dim  orderDetails As DataTable  = ds.Tables["OrderDetails"];
If you want an easier sollution, consider creating a strongly typed dataset, 
as it gives you dotted notation. Good luck!
---
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
***************************
Think Outside the Box!
***************************
"Bill K" wrote:
> Cowboy - I am using the data adapter wizard and I am only seeing the first 
> table in the dataset - with the title of the stored procedure.  is there a 
> way using the wizard - or in code to display all three table in the dataset?  
> if code - can you provide an example?
> 
> Thanks again.
> 
> Bill
> 
> "Cowboy (Gregory A. Beamer) - MVP" wrote:
> 
> > Northwind stored procedure (can be tailored for your DB):
> > 
> > CREATE PROCEDURE dbo.GetCustomerOrders
> > (
> > 	@CustomerID	nchar(10)
> > )
> > AS
> > 
> > SELECT c.CustomerID
> > 	, c.CompanyName
> > 	, c.ContactName
> > 	, c.ContactTitle
> > 	, c.Address
> > 	, c.City
> > 	, c.Region
> > 	, c.PostalCode
> > 	, c.Country
> > 	, c.Phone
> > 	, c.Fax
> > FROM Customers c
> > WHERE c.CustomerID = @CustomerID
> > 
> > SELECT o.OrderID
> > 	, o.CustomerID
> > 	, o.EmployeeID
> > 	, o.OrderDate
> > 	, o.RequiredDate
> > 	, o.ShippedDate
> > 	, o.ShipVia
> > 	, o.Freight
> > 	, o.ShipName
> > 	, o.ShipAddress
> > 	, o.ShipCity
> > 	, o.ShipRegion
> > 	, o.ShipPostalCode
> > 	, o.ShipCountry
> > FROM Orders o
> > WHERE o.CustomerID = @CustomerID
> > 
> > SELECT od.OrderID
> > 	, od.ProductID
> > 	, od.UnitPrice
> > 	, od.Quantity
> > 	, od.Discount
> > FROM [Order Details] od
> > JOIN Orders o
> > 	ON od.OrderID = o.OrderID
> > WHERE o.CustomerID = @CustomerID
> > 
> > In your dataset, add relationships for the tables to easily filter the 
> > information.
> > 
> > 
> > ---
> > 
> > Gregory A. Beamer
> > MVP; MCP: +I, SE, SD, DBA
> > 
> > ***************************
> > Think Outside the Box!
> > ***************************
> > 
> > "Bill K" wrote:
> > 
> > > I have a classic scenerio - simliar to the Northwind 
> > > Customer/Ordes/OrderDetails.  I want to select a customer from a combo box, 
> > > display pertinent info, and display in a hierarchical datagrid the orders and 
> > > ordertails (in to next layer).  I am creating 3 dataadapters, one for each 
> > > table.  I am creating the customers as a parameter query which I pass the ID 
> > > value from the combo box to the query and it pulls up the custumer info 
> > > nicely.  
> > > 
> > > Howerver, I can make the Orders dataadapter a parameter query using the 
> > > cutomer ID, but can't drill down to the Orders detail using that same ID.
> > > 
> > > I've put all three dataadapters into one dataset establishing the 
> > > relationships.  
> > > 
> > > When I run it, I get an error saying that All the rows can't find related 
> > > rows.  I know what is happening - it is pulling all the rows from subordinate 
> > > table and there is not related records in parent tables, thus violating the 
> > > referential integrity of the database.
> > > 
> > > It will work if I set constraints to false, but I can then not update 
> > > subordinate tables.
> > > 
> > > How can I make this classic senerio work - using winforms in vb.net????
> > > 
> > > It seems to me that there should be a way to force pulling the only the 
> > > orders and orderDetails associated with the customer.
> > > 
> > > Thanks for your help.

Relevant Pages

  • Exporting Report from Access into PDF
    ... report that I want to export into PDF for each individual customer. ... Dim qdf As DAO.QueryDef ... Dim strSQL As String, strTemp As String, strMgr As String ... ' with the real names of the EmployeesTable table and the ManagerID ...
    (microsoft.public.access.externaldata)
  • Re: Multi-tier ASP.net web application
    ... I have a table "Customer" in the database. ... Dim password As String ... Public Sub New ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Combo allows not in list but after update causes runtime error
    ... Dim intNewContact As Integer, strtitle As String ... ' Evaluate filter before it is passed. ... ' Display message box asking if the user wants to add a new customer. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Multi-tier ASP.net web application
    ... Usually in Java applications, I created objects out of the database, ... I have a table "Customer" in the database. ... Dim password As String ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: W2003, batch converter wizard - can it be ran from the command lin
    ... Dim strFilename As String ... Dim strDocName As String ... I would like to create a batch file that opens Word, calls the wizard and ...
    (microsoft.public.word.docmanagement)