向数据集中加载数据
更新：November 2007
DataSet 对象只有在填充后才能使用 LINQ to DataSet 进行查询。 填充 DataSet 有多种不同的方式。 例如，您可以使用 LINQ to SQL 来查询数据库并将结果加载到 DataSet 中。 有关更多信息，请参见 LINQ to SQL。
另一种将数据加载到 DataSet 中的常见方式是使用 DataAdapter 类从数据库中检索数据。 下面的示例阐释了这一过程。
示例
此示例使用 DataAdapter 查询 AdventureWorks 数据库中从 2002 年起的销售信息，并将结果加载到 DataSet 中。 填充 DataSet 以后，可以通过使用 LINQ to DataSet 对其编写查询。 LINQ to DataSet 示例的示例查询中使用了本示例中的 FillDataSet 方法。 有关更多信息，请参见查询数据集 (LINQ to DataSet)。
Try
Dim connectionString As String
connectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;" & _
"Integrated Security=true;"
' Create a new adapter and give it a query to fetch sales order, contact,
' address, and product information for sales in the year 2002. Point connection
' information to the configuration setting "AdventureWorks".
Dim da = New SqlDataAdapter( _
"SELECT SalesOrderID, ContactID, OrderDate, OnlineOrderFlag, " & _
"TotalDue, SalesOrderNumber, Status, ShipToAddressID, BillToAddressID " & _
"FROM Sales.SalesOrderHeader " & _
"WHERE DATEPART(YEAR, OrderDate) = @year; " & _
"SELECT d.SalesOrderID, d.SalesOrderDetailID, d.OrderQty, " & _
"d.ProductID, d.UnitPrice " & _
"FROM Sales.SalesOrderDetail d " & _
"INNER JOIN Sales.SalesOrderHeader h " & _
"ON d.SalesOrderID = h.SalesOrderID " & _
"WHERE DATEPART(YEAR, OrderDate) = @year; " & _
"SELECT p.ProductID, p.Name, p.ProductNumber, p.MakeFlag, " & _
"p.Color, p.ListPrice, p.Size, p.Class, p.Style " & _
"FROM Production.Product p; " & _
"SELECT DISTINCT a.AddressID, a.AddressLine1, a.AddressLine2, " & _
"a.City, a.StateProvinceID, a.PostalCode " & _
"FROM Person.Address a " & _
"INNER JOIN Sales.SalesOrderHeader h " & _
"ON a.AddressID = h.ShipToAddressID OR a.AddressID = h.BillToAddressID " & _
"WHERE DATEPART(YEAR, OrderDate) = @year; " & _
"SELECT DISTINCT c.ContactID, c.Title, c.FirstName, " & _
"c.LastName, c.EmailAddress, c.Phone " & _
"FROM Person.Contact c " & _
"INNER JOIN Sales.SalesOrderHeader h " & _
"ON c.ContactID = h.ContactID " & _
"WHERE DATEPART(YEAR, OrderDate) = @year;", _
connectionString)
' Add table mappings.
da.SelectCommand.Parameters.AddWithValue("@year", 2002)
da.TableMappings.Add("Table", "SalesOrderHeader")
da.TableMappings.Add("Table1", "SalesOrderDetail")
da.TableMappings.Add("Table2", "Product")
da.TableMappings.Add("Table3", "Address")
da.TableMappings.Add("Table4", "Contact")
da.Fill(ds)
' Add data relations.
Dim orderHeader As DataTable = ds.Tables("SalesOrderHeader")
Dim orderDetail As DataTable = ds.Tables("SalesOrderDetail")
Dim co As DataRelation = New DataRelation("SalesOrderHeaderDetail", _
orderHeader.Columns("SalesOrderID"), _
orderDetail.Columns("SalesOrderID"), True)
ds.Relations.Add(co)
Dim contact As DataTable = ds.Tables("Contact")
Dim orderContact As DataRelation = New DataRelation("SalesOrderContact", _
contact.Columns("ContactID"), _
orderHeader.Columns("ContactID"), True)
ds.Relations.Add(orderContact)
Catch ex As SqlException
Console.WriteLine("SQL exception occurred: " & ex.Message)
End Try
try
{
// Create a new adapter and give it a query to fetch sales order, contact,
// address, and product information for sales in the year 2002. Point connection
// information to the configuration setting "AdventureWorks".
string connectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;"
+ "Integrated Security=true;";
SqlDataAdapter da = new SqlDataAdapter(
"SELECT SalesOrderID, ContactID, OrderDate, OnlineOrderFlag, " +
"TotalDue, SalesOrderNumber, Status, ShipToAddressID, BillToAddressID " +
"FROM Sales.SalesOrderHeader " +
"WHERE DATEPART(YEAR, OrderDate) = @year; " +
"SELECT d.SalesOrderID, d.SalesOrderDetailID, d.OrderQty, " +
"d.ProductID, d.UnitPrice " +
"FROM Sales.SalesOrderDetail d " +
"INNER JOIN Sales.SalesOrderHeader h " +
"ON d.SalesOrderID = h.SalesOrderID " +
"WHERE DATEPART(YEAR, OrderDate) = @year; " +
"SELECT p.ProductID, p.Name, p.ProductNumber, p.MakeFlag, " +
"p.Color, p.ListPrice, p.Size, p.Class, p.Style, p.Weight " +
"FROM Production.Product p; " +
"SELECT DISTINCT a.AddressID, a.AddressLine1, a.AddressLine2, " +
"a.City, a.StateProvinceID, a.PostalCode " +
"FROM Person.Address a " +
"INNER JOIN Sales.SalesOrderHeader h " +
"ON a.AddressID = h.ShipToAddressID OR a.AddressID = h.BillToAddressID " +
"WHERE DATEPART(YEAR, OrderDate) = @year; " +
"SELECT DISTINCT c.ContactID, c.Title, c.FirstName, " +
"c.LastName, c.EmailAddress, c.Phone " +
"FROM Person.Contact c " +
"INNER JOIN Sales.SalesOrderHeader h " +
"ON c.ContactID = h.ContactID " +
"WHERE DATEPART(YEAR, OrderDate) = @year;",
connectionString);
// Add table mappings.
da.SelectCommand.Parameters.AddWithValue("@year", 2002);
da.TableMappings.Add("Table", "SalesOrderHeader");
da.TableMappings.Add("Table1", "SalesOrderDetail");
da.TableMappings.Add("Table2", "Product");
da.TableMappings.Add("Table3", "Address");
da.TableMappings.Add("Table4", "Contact");
// Fill the DataSet.
da.Fill(ds);
// Add data relations.
DataTable orderHeader = ds.Tables["SalesOrderHeader"];
DataTable orderDetail = ds.Tables["SalesOrderDetail"];
DataRelation order = new DataRelation("SalesOrderHeaderDetail",
orderHeader.Columns["SalesOrderID"],
orderDetail.Columns["SalesOrderID"], true);
ds.Relations.Add(order);
DataTable contact = ds.Tables["Contact"];
DataTable orderHeader2 = ds.Tables["SalesOrderHeader"];
DataRelation orderContact = new DataRelation("SalesOrderContact",
contact.Columns["ContactID"],
orderHeader2.Columns["ContactID"], true);
ds.Relations.Add(orderContact);
}
catch (SqlException ex)
{
Console.WriteLine("SQL exception occurred: " + ex.Message);
}