Carregando dados em um DataSet

Um objeto DataSet deve ser populado primeiro para que você possa consultá-lo com o LINQ to DataSet. Há várias maneiras diferentes de popular o DataSet. Por exemplo, você pode usar o LINQ to SQL para consultar o banco de dados e carregar os resultados no DataSet. Para obter mais informações, consulte LINQ to SQL.

Outra maneira comum de carregar dados em um DataSet é usar a classe DataAdapter para recuperar dados do banco de dados. Isso é ilustrado no exemplo a seguir.

Exemplo

Esse exemplo usa DataAdapter para consultar o banco de dados AdventureWorks para obter informações sobre as vendas do ano 2002 e carrega os resultados em um DataSet. Depois que o DataSet foi populado, você pode escrever consultas nele usando o LINQ to DataSet. O método FillDataSet desse exemplo é usado em consultas de exemplo em Exemplos de LINQ to DataSet. Para obter mais informações, consulte Consultar conjuntos de dados.

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

Confira também