Querying Loading Returning dataset using linq Asp.Net C#

Posted by on September 18, 2010 Leave a comment (0) Go to comments

Introduction:
In this article,i am going to explain about how to returning a dataset using linq query.

Main:
We allready knows,

DataSets are objects that contain internal data tables where data is temporarily stored and is available for use by your application.
DataSets are, in essence, a local in-memory cache of data that is typically retrieved from a database. This cache lets you work in a disconnected mode, providing the capability to make changes to the data within the DataSet, track those changes, and save those changes back to the database when the application reconnects.

A DataSet is a representation of the tables and relationships found in the database, exposing a hierarchical object model
made of all the objects such as tables, rows, columns, constraints, and relationships. Much of the functionality that populates the DataSet and saves the changes within the DataSet back to the database is found in ADO.NET.

The DataSet itself is extremely flexible and powerful. It provides the capability for applications to efficiently work with a subset of data found in a database and to manipulate the data as needed by the application, all while in a disconnected state, and then user the changes back to the database.

For linq to dataset we need atleast the below 4 namespaces,

System.Core,
System.Data.DataSetExtensions,
and other default namespaces (system.data and system.data.linq),

The very big disappointment we cannot fill data into dataset using linq,we need dataadapter for loading data’s into linq,

for ex,

The following example uses a DataAdapter to populate a DataSet with sales order header information for a particular salesperson. A LINQ query expression is then defined and used to query the DataSet for all orders for the year 2003.

try
{
    int salesPersonID = Convert.ToInt32(textBox3.Text);
 
    DataSet ds = new DataSet();
 
    string connectionInfo = "Data Source=NetPrgHelp;Initial Catalog=NetProgrammingHelp;
Integrated Security=true";
 
    SqlDataAdapter da = new SqlDataAdapter(
        "SELECT SalesOrderID, OrderDate, " +
    "SalesOrderNumber, SalesPersonID, ContactID, TotalDue " +
    "FROM sales.salesorderheader " +
    "WHERE SalesPersonID = @ID; ", connectionInfo);
 
    da.SelectCommand.Parameters.AddWithValue("@ID", salesPersonID);
    da.TableMappings.Add("Table", "SalesOrderHeader");
 
 
    da.Fill(ds);
 
    DataTable header = ds.Tables["SalesOrderHeader"];
 
    textBox1.Text = ds.Tables[0].Rows.Count.ToString();
 
    var orderHeader = from oh in header.AsEnumerable()
                      where oh.Field<DateTime>("OrderDate").Year == 2003
                      select new {SalesOrderID = oh.Field<int>("SalesOrderID"),
                           SalesOrderNumber = oh.Field<string>("SalesOrderNumber"),
                           OrderDate = oh.Field<DateTime>("OrderDate"),
                           Total = oh.Field<decimal>("TotalDue")};
 
    foreach (var order in orderHeader)
    {
        listBox1.Items.Add(order.SalesOrderID + " " +
            order.SalesOrderNumber + " " +
            order.OrderDate + " " +
            order.Total);
    }
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}