将数据加载到数据集中
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 方法。 有关详细信息，请参阅查询数据集。
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);
}
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