Data Binding using linq Dataset Datagrid Gridview 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 do the databinding against any data controls using linq.

Main:
Data binding is the act of creating a link or connection between the user interface and the data. It’s been around for quite a while and is not specific to LINQ to DataSet. The concept behind data binding is that a user interface component can be bound to its corresponding field or column in the data layer such that when the user changes the value in the user interface, the change is automatically reflected in the data layer or database.

LINQ to SQL lets you bind data to many of the common controls such as combo boxes, grid controls, as well as a DataView. A DataView represents a customizable and bindable view of a DataTable, providing searching, sorting, editing, and filtering capabilities. Once the DataView is created, it can be bound to UI controls such as a DataGrid.

Keep in mind that a LINQ to DataSet query returns a DataRow enumeration, which is difficult to bind. The CopyToDataTable method is valuable in those instances. CopyToDataTable returns a DataTable that contains copies of the DataRow objects. The method is part of the DataTableExtensions class and takes an IEnumerable(Of T) object, where a parameter T is a generic DataRow.

The following example populates DataSet with data from the SalesOrderHeader table and then queries using a LINQ to DataSet query. The query returns an enumeration of DataRow objects that is used to populate a DataTable via the CopyToDataTable method. Once the DataTable is populated, a new DataView is created and populated with the DataTable. The DataView is then assigned to the DataSource property of a DataGridView.

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();
 
    IEnumerable<DataRow>  orderHeader =
                                from oh in header.AsEnumerable()
                                where oh.Field<DateTime>("OrderDate").Year == 2003
                                select oh;
 
    DataTable dt = orderHeader.CopyToDataTable<DataRow>();
 
    DataView dv = new DataView(dt);
    dataGridView1.DataSource = dv;
 
}
catch (Exception ex)
{
    MessageBox.Show(ex.Message);
}