Aroh's Microsoft 365 Musings: Taking a crash course in LINQ

Hi all,

We know that LINQ dramatically simplifies the data access layer with an array of other benefits which I discussed in the previous post. ADO.NET is a bridge between our objects in ASP.NET and our database. ADO.NET provides an object oriented view to the database, encapsulating many of the database properties and relational within ADO.NET objects.

ADO.NET architecture diagram comes with connected (SqlConnection, SqlCommand, SqlDataReader, SqlBulkCopy, SqlDataAdpater etc.) and disconnected architecture (DataSet, DataTable etc).In this blog post, we will talk about LINQ to DataSet which is a disconnected architecture. These ADO.NET dataset and datatable are quite sophisticated objects and data tables is an iterative process through collections of rows and columns.

Advantages:

These ADO.NET object are extremely powerful and in development context, there are quite easy to develop.

Disadvantages:

1. DataSet objects do not expose rich query capabilities. Therefore developers have had to live with DataSet's limited query mechanism such as simple string operations for sorting and filtering which lead to a lot of extra work and custom applications.

2. Neither DataSet nor DataSet implements either IEnumerable<T> or IQueryable<T>.

LINQ to DataSet: (For Developers)

LINQ to DataSet lets us use the standard query operators and some operators that are specific to datasets and data tables. It treats data tables as enumerations of DataRow objects.It doesn’t care how data gets into a dataset.

a) Provides a consistent model for querying data irrespective of different data sources.
b) Write .NET Framework code rather than of SQL when working with data.
c) Compile-time syntax against our data.
d) Making programming data easier and more consistent
e) Intellisense

f) Unfortunately, the DataSet’s DataTable and the DataTable’s DataRowCollection do not implement IEnumerable<DataRow> so these types cannot be used as sources for LINQ query expressions. This means, we cannot query a DataSet in the same way we would query a database.

AsEnumerable:

LINQ leverages IEnumerable<T> sources. .But there is a workaround and LINQ provides an extension method called "AsEnumerable" and its added to DataTable type.

Field<T>:

We can use the DataRow column values via the Filed<T> method when developing LINQ to Data Set query expressions. This facilitates the query expression less error prone and thereby provides much cleaner code.

So, lets take a sample of LINQ to DataSet.Firstly, we will discuss typed DataSet and in second sample we will discuss Untyped DataSets.

-------------------------------------------------------------------------------------

LINQ to DataSet (Typed)

Step1: Create a new Visual Studio 2008/2010 project, File >> New >> Project >> Select "ASP.NET Web Application" and name the project: "LINQtoDataSet"

Step2: Right click on the "LINQtoDataSet" project >> Add >> New Item >> Select "Data" from installed templates >> Select the "DataSet" item and name it "Customers". We now have a Typed DataSet.

Step3: In the toolbar, go to "Tools" >> "Connect to Database" >> Add Connection >> Establish connection to "Northwind"

Step4: Go to "Server Explorer" >> Open the "Northwind" database >> Tables >> Drag the "Customers" table to DataSet Designer as illustrated below:

Step5: In the "TypedDataSet.aspx, go to ToolBox and drag/drop a GridView control on the designer interface.

Step6: In the "TypedDataSet.aspx", go to code view and in Page_Load method we bind the GridView to the Customer table via LINQ. We query the country (France) and in the GridView display customerId and company.

Typed DataSet is immensely helpful as we can leverage Visual Studio 2010 IntelliSense and therefore no mistakes in respect of columns names.

Sample code:

        protected void Page_Load(object sender, EventArgs e)
        {

            //Create an instance of the dataset
            Customers ds = new Customers();

            // connection string
            string connStr = @"Data Source=SP2010\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True";

            // create connection
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                //Command text
                string commStr = "select * from customers";

                // create data adapter
                SqlDataAdapter da = new SqlDataAdapter(commStr,conn);

                // fill data table
                da.Fill(ds, "Customers");

                //query the dataset via LINQ
                var query = from c in ds._Customers
                            where c.Country == "France"
                            select new
                            {
                                c.CustomerID,
                                c.CompanyName
                            };

                //Bindthe data to GridView
                GridView1.DataSource = query;
                GridView1.DataBind();
            }

        }

Step7: The output:

-------------------------------------------------------------------------------

LINQ to DataSet (UnTyped)

Step1: Create a new Visual Studio 2008/2010 project, File >> New >> Project >> Select "ASP.NET Web Application" and name the project: "LINQtoDataSet2"

Step2: In the "DataSet.aspx, go to ToolBox and drag/drop a GridView control on the designer interface.

Step3: In the "DataSet.aspx", go to code view and in Page_Load method we bind the GridView to the Customer table via LINQ. We query the customer id, company name and in the GridView display customerId and company.

UnTyped DataSet can be error prone as if by mistake we misspelled a column, then we will get an error. Furthermore, we have to us DataTable.AsEnumerable method and this no intellisense as well.

Sample code:

       protected void Page_Load(object sender, EventArgs e)
        {

            // connection string
            string connStr = @"Data Source=SP2010\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True";

            // create connection
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                //Create dataset
                DataSet ds = new DataSet();

                //Command text
                string commStr = "select * from customers";

                //Create data adapter
                SqlDataAdapter da = new SqlDataAdapter(commStr, conn);

                // fill data table
                da.Fill(ds, "Customers");

                DataTable customer = ds.Tables["Customers"];

                //Query the DataTable via LINQ
                var query = from c in customer.AsEnumerable()
                            select new
                            {
                                CustomerId = c.Field<string>("customerid"),
                                Company = c.Field<string>("companyname")
                            } ;


                //Bindthe data to GridView
                GridView1.DataSource = query;
                GridView1.DataBind();
            }
        }

Step7: The output: