Cargar datos en DataSet
Un objeto DataSet se debe rellenar primero antes de poderlo consultar con LINQ to DataSet. Existen varias formas de rellenar DataSet. Por ejemplo, se puede utilizar LINQ to SQL para consultar la base de datos y cargar los resultados en DataSet. Para obtener más información, vea LINQ to SQL.
Otra forma habitual de cargar datos en DataSet es utilizar la clase DataAdapter para recuperar datos desde la base de datos. Esto se muestra en el ejemplo siguiente.
Ejemplo
En este ejemplo se utiliza DataAdapter para consultar en la base de datos AdventureWorks la información de ventas del año 2002, y cargar los resultados en un DataSet. Una vez que se ha rellenado DataSet, se pueden escribir consultas en él mediante LINQ to DataSet. En este ejemplo, se utiliza el método FillDataSet en las consultas de ejemplo en Ejemplos de LINQ to DataSet. Para obtener más información, vea Consultar DataSets (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);
}
Vea también
Conceptos
Información general de LINQ to DataSet
Consultar DataSets (LINQ to DataSet)