Microsoft Enterprise Library Data Access Application Block simplifies development tasks that implement common data access functionality. Applications can use this application block in a variety of situations, such as reading data for display, passing data through application layers, and submitting changed data back to the database system. The application block includes support for both stored procedures and in-line SQL. Common application tasks, such as managing connections and creating and caching parameters, are encapsulated in the application block's methods.
The Data Access Application Block provides the following benefits:
It uses the functionality provided by ADO.NET 2.0 and with it, developer can use ADO.NET functionality along with the application block's functionality.
It reduces the need to write boilerplate code to perform standard tasks.
It helps maintain consistent data access practices, both within an application and across the enterprise.
It reduces difficulties in changing the database type.
It relieves developers from learning different programming models for different types of databases.
It reduces the amount of code that developers must write when they port applications to different types of databases.
URL- http://aspalliance.com/1725
Monday, August 25, 2008
Saturday, August 16, 2008
Programming Language-Integrated Query (LINQ) with ADO.NET
Programming Language-Integrated Query (LINQ) with ADO.NET (Part –1)
Introduction
Language-Integrated Query (LINQ) defines a set of general-purpose standard query operators that developer can use in .NET Framework 3.0 programming languages. These standard query operators enable developer to project, filter, and traverse in-memory collections or tables in a database. The LINQ queries are expressed in the programming language itself, and not as string literals embedded in the application code. This is a significant change from the way most applications have been written on earlier versions of the .NET Framework. Writing queries from within programming language offers several key advantages. It simplifies querying by eliminating the need to use a separate query language. And if developer uses the Visual Studio 2008 IDE, LINQ also lets developer take advantage of compile-time checking, static typing, and IntelliSense.
LINQ is integrated into various aspects of data access in the .NET Framework, including the DataSet disconnected programming model and existing SQL Server database schemas.
LINQ to ADO.NET consists of two separate technologies: LINQ to DataSet and LINQ to SQL. LINQ to DataSet provides richer, optimized querying over the DataSet and LINQ to SQL enables developers to directly query SQL Server database schemas.
Transferring data from SQL tables into objects in memory is often tedious and error-prone. The LINQ provider implemented by LINQ to DataSet and LINQ to SQL converts the source data into IEnumerable-based object collections. The programmer always views the data as an IEnumerable collection, both when developer query and when developer update. Full IntelliSense support is provided for writing queries against those collections in .NET IDE.
The following figure provides an overview of how LINQ to ADO.NET relates to high-level programming languages, other LINQ technologies, and LINQ-enabled data sources.
Overview of LINQ & ADO.NET Programming
Many business developers must use two (or more) programming languages: a high-level language for the business logic and presentation layers (such as Visual C# or Visual Basic), and a query language to interact with the database (such as Transact-SQL). This requires the developer to be proficient in several languages to be effective, and also causes language mismatches in the development environment. For example, an application that uses a data access API to execute a query against a database specifies the query as a string literal by using quotation marks. This query string is un-readable to the compiler and is not checked for errors, such as invalid syntax or whether the columns or rows it references actually exist. There is no type checking of the query parameters and no IntelliSense support, either.
Language-Integrated Query (LINQ) enables developers to form set-based queries in their application code, without having to use a separate query language. Developer can write LINQ queries against various enumerable data sources (that is, a data source that implements the IEnumerable interface), such as in-memory data structures, XML documents, SQL databases, and DataSet objects. Although these enumerable data sources are implemented in various ways, they all expose the same syntax and language constructs. Because queries can be formed in the programming language itself, developer do not have to use another query language that is embedded as string literals that cannot be understood or verified by the compiler. Integrating queries into the programming language also enables Visual Studio programmers to be more productive by providing compile-time type and syntax checking, and IntelliSense in IDE. These features reduce the need for query debugging and error fixing.
LINQ to Dataset
ADO.NET has two different part of programming. The DataSet is a key element of the disconnected programming model and is widely used. LINQ to DataSet enables developers to build richer query capabilities into DataSet by using the same query formulation mechanism that is available for many other data sources.
LINQ to Dataset overview
LINQ to DataSet makes it easier and faster to query over data cached in a DataSet object. Specifically, LINQ to DataSet simplifies querying by enabling developers to write queries from the programming language itself, instead of by using a separate query language. This is especially useful for Visual Studio developers, who can now take advantage of the compile-time syntax checking, static typing, and IntelliSense support provided by the Visual Studio in their queries.
LINQ to DataSet can also be used to query over data that has been consolidated from one or more data sources. This enables many scenarios that require flexibility in how data is represented and handled, such as querying locally aggregated data and middle-tier caching in Web applications. In particular, generic reporting, analysis, and business intelligence applications require this method of manipulation.
The LINQ to DataSet functionality is exposed primarily through the extension methods in the DataRowExtensions and DataTableExtensions classes. LINQ to DataSet builds on and uses the existing ADO.NET 2.0 architecture, and is not meant to replace ADO.NET 2.0 in application code. Existing ADO.NET 2.0 code will continue to function in a LINQ to DataSet application.
The LINQ to DataSet functionality is exposed primarily through the extension methods in the DataRowExtensions and DataTableExtensions classes. LINQ to DataSet builds on and uses the existing ADO.NET 2.0 architecture, and is not meant to replace ADO.NET 2.0 in application code. Existing ADO.NET 2.0 code will continue to function in a LINQ to DataSet application.
The DataSet is one of the more widely used components of ADO.NET. It is a key element of the disconnected programming model that ADO.NET is based on, and it enables developer to explicitly cache data from different data sources. For the presentation tier, the DataSet is tightly integrated with GUI controls for data-binding. For the middle-tier, it provides a cache that preserves the relational shape of data, and includes fast simple query and hierarchy navigation services. A common technique used to lower the number of requests on a database is to use the DataSet for caching in the middle-tier. For example, consider a data-driven ASP.NET Web application. Often, a significant portion of the application data does not change frequently and is common across sessions or users. This data can be kept in memory on the Web server, which reduces the number of requests against the database and speeds up the user’s interactions. Another useful aspect of the DataSet is that it allows an application to bring subsets of data from one or more data source into the application space. The application can then manipulate the data in-memory, while retaining its relational shape.
Despite its prominence, the DataSet has limited query capabilities. The Select method can be used for filtering and sorting, and the GetChildRows and GetParentRow methods can be used for hierarchy navigation. For anything more complex, however, the developer must write a custom query. This can result in applications that perform poorly and are difficult to maintain.
LINQ to DataSet makes it easier and faster to query over data cached in a DataSet object. These queries are expressed in the programming language itself, rather than as string literals embedded in the application code. This means that developers do not have to learn a separate query language. Additionally, LINQ to DataSet enables Visual Studio developers to work more productively, because the Visual Studio IDE provides compile-time syntax checking, static typing, and IntelliSense support for LINQ. LINQ to DataSet can also be used to query over data that has been consolidated from one or more data sources. This enables many scenarios that require flexibility in how data is represented and handled. In particular, generic reporting, analysis, and business intelligence applications require this method of manipulation.
Querying DataSets Using LINQ to DataSet
Before developer can begin querying a DataSet object using LINQ to DataSet, developer must populate the DataSet. There are several ways to load data into a DataSet, such as using the DataAdapter class or LINQ to SQL. After the data has been loaded into a DataSet object, developer can begin to query it. Formulating queries using LINQ to DataSet is similar to using Language-Integrated Query (LINQ) against other LINQ-enabled data sources. LINQ queries can be performed against single tables in a DataSet or against more than one table by using the Join and GroupJoin standard query operators.
LINQ queries are supported against both typed and untyped DataSet objects. If the schema of the DataSet is known at application design time, a typed DataSet is recommended. In a typed DataSet, the tables and rows have typed members for each of the columns, which makes queries simpler and more readable.
In addition to the standard query operators implemented in System.Core.dll, LINQ to DataSet adds several DataSet-specific extensions that make it easier to query over a set of DataRow objects. These DataSet-specific extensions include operators for comparing sequences of rows, as well as methods that provide access to the column values of a DataRow.
N-tier Applications and LINQ to DataSet
N-tier data applications are data-centric applications that are separated into multiple logical layers (or tiers). A typical N-tier application includes a presentation tier, a middle tier, and a data tier. Separating application components into separate tiers increases the maintainability and scalability of the application. In N-tier applications, the DataSet is often used in the middle-tier to cache information for a Web application. The LINQ to DataSet querying functionality is implemented through extension methods and extends the existing ADO.NET 2.0 DataSet.
How to work with LINQ & Dataset (Code Snipped)
A DataSet object must first be populated before we can query over it with LINQ to DataSet. There are several different ways to populate the DataSet 1) LINQ to SQL to query the database and load the results into the DataSet 2) Common way to load data into a DataSet is to use the DataAdapter class to retrieve data from the database. As example following code -
// code in C#
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". Default database in SQL server 2000
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);
}
A query is an expression that retrieves data from a data source. Language-Integrated Query (LINQ) offers a simpler, consistent model for working with data across various kinds of data sources and formats. In a LINQ query, developer always works with programming objects.
A LINQ query operation consists of three actions: obtain the data source or sources, create the query, and execute the query.
Data sources that implement the IEnumerable<(Of <(T>)>) generic interface can be queried through LINQ. Calling AsEnumerable on a DataTable returns an object which implements the generic IEnumerable<(Of <(T>)>) interface, which serves as the data source for LINQ to DataSet queries.
In the query, developer specifies exactly the information that developer wants to retrieve from the data source. A query can also specify how that information should be sorted, grouped, and shaped before it is returned. In LINQ, a query is stored in a variable. If the query is designed to return a sequence of values, the query variable itself must be an enumerable type. This query variable takes no action and returns no data; it only stores the query information. After developer creates a query developer must execute that query to retrieve any data.
In a query that returns a sequence of values, the query variable itself never holds the query results and only stores the query commands. Execution of the query is deferred until the query variable is iterated over in a foreach or For Each loop. This is called deferred execution; that is, query execution occurs some time after the query is constructed. This means that developer can execute a query as often as developer want to. This is useful when, for example, developer have a database that is being updated by other applications. In your application, developer can create a query to retrieve the latest information and repeatedly execute the query, returning the updated information every time.
In contrast to deferred queries, which return a sequence of values, queries that return a singleton value are executed immediately. Some examples of singleton queries are Count, Max, Average, and First. These execute immediately because the query results are required to calculate the singleton result. For example, in order to find the average of the query results the query must be executed so that the averaging function has input data to work with. Developer can also use the ToList<(Of <(TSource>)>) or ToArray<(Of <(TSource>)>) methods on a query to force immediate execution of a query that does not produce a singleton value. These techniques to force immediate execution can be useful when developer want to cache the results of a query.
LINQ to DataSet queries can be formulated in two different syntaxes: query expression syntax and method-based query syntax.
Query expressions are declarative query syntax. This syntax enables a developer to write queries in C# or Visual Basic in a format similar to SQL. By using query expression syntax, developer can perform even complex filtering, ordering, and grouping operations on data sources with minimal code.
Query expression syntax is new in C# 3.0 and Visual Basic 2008. As a developer have the option of calling them directly by using method syntax, instead of using query syntax.
The following example uses select to return all the rows from Product table and display the product names.
//Code in C#
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable products = ds.Tables["Product"];
IEnumerable query =
from product in products.AsEnumerable()
select product;
Console.WriteLine("Product Names:");
foreach (DataRow p in query)
{
Console.WriteLine(p.Field("Name"));
}
This example uses Select to return all the rows from Product and display the product names.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable products = ds.Tables["Product"];
var query = products.AsEnumerable().
Select(product => new
{
ProductName = product.Field("Name"),
ProductNumber = product.Field("ProductNumber"),
Price = product.Field("ListPrice")
});
Console.WriteLine("Product Info:");
foreach (var productInfo in query)
{
Console.WriteLine("Product name: {0} Product number: {1} List price: ${2} ",
productInfo.ProductName, productInfo.ProductNumber, productInfo.Price);
}
In the following example, the first query returns all the products ordered by list price. The ToArray<(Of <(TSource>)>) method is used to force immediate query execution:
//Code in C#
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable products = ds.Tables["Product"];
IEnumerable query =
from product in products.AsEnumerable()
orderby product.Field("ListPrice") descending
select product;
// Force immediate execution of the query.
IEnumerable productsArray = query.ToArray();
Console.WriteLine("Every price from highest to lowest:");
foreach (DataRow prod in productsArray)
{
Console.WriteLine(prod.Field("ListPrice"));
}
Language-Integrated Query (LINQ) queries work on data sources that implement the IEnumerable<(Of <(T>)>) interface or the IQueryable interface. The DataTable class does not implement either interface, so you must call the AsEnumerable method if you want to use the DataTable as a source in the From clause of a LINQ query.
The following example gets all the online orders from the SalesOrderHeader table and outputs the order ID, order date, and order number to the console.
//Code in C#
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
var query =
from order in orders.AsEnumerable()
where order.Field("OnlineOrderFlag") == true
select new
{
SalesOrderID = order.Field("SalesOrderID"),
OrderDate = order.Field("OrderDate"),
SalesOrderNumber = order.Field("SalesOrderNumber")
};
foreach (var onlineOrder in query)
{
Console.WriteLine("Order ID: {0} Order date: {1:d} Order number: {2}",
onlineOrder.SalesOrderID,
onlineOrder.OrderDate,
onlineOrder.SalesOrderNumber);
}
In addition to querying a single table, developer can also perform cross-table queries in LINQ to DataSet. This is done by using a join. A join is the association of objects in one data source with objects that share a common attribute in another data source, such as a product or contact ID. In object-oriented programming, relationships between objects are relatively easy to navigate because each object has a member that references another object. In external database tables, however, navigating relationships is not as straightforward. Database tables do not contain built-in relationships.
The Language-Integrated Query (LINQ) framework provides two join operators, Join and GroupJoin. These operators perform equi-joins: that is, joins that match two data sources only when their keys are equal. (By contrast, Transact-SQL supports join operators other than equals, such as the less than operator.)
In relational database terms, Join implements an inner join. An inner join is a type of join in which only those objects that have a match in the opposite data set are returned.
The GroupJoin operators have no direct equivalent in relational database terms; they implement a superset of inner joins and left outer joins. A left outer join is a join that returns each element of the first (left) collection, even if it has no correlated elements in the second collection.
The following example performs a traditional join of the SalesOrderHeader and SalesOrderDetail tables from the AdventureWorks sample database to obtain online orders from the month of August.
// Code in C#
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
DataTable details = ds.Tables["SalesOrderDetail"];
var query =
from order in orders.AsEnumerable()
join detail in details.AsEnumerable()
on order.Field("SalesOrderID") equals
detail.Field("SalesOrderID")
where order.Field("OnlineOrderFlag") == true
&& order.Field("OrderDate").Month == 8
select new
{
SalesOrderID =
order.Field("SalesOrderID"),
SalesOrderDetailID =
detail.Field("SalesOrderDetailID"),
OrderDate =
order.Field("OrderDate"),
ProductID =
detail.Field("ProductID")
};
foreach (var order in query)
{
Console.WriteLine("{0}\t{1}\t{2:d}\t{3}",
order.SalesOrderID,
order.SalesOrderDetailID,
order.OrderDate,
order.ProductID);
}
The following example shows a query over a typed DataSet:
//Code in C#
var query = from o in orders
where o.OnlineOrderFlag == true
select new { o.SalesOrderID,
o.OrderDate,
o.SalesOrderNumber };
foreach(var order in query)
{
Console.WriteLine("{0}\t{1:d}\t{2}",
order.SalesOrderID,
order.OrderDate,
order.SalesOrderNumber);
}
Language-Integrated Query (LINQ) defines various set operators to compare source elements to see if they are equal. LINQ provides set operators like Distinct,Union,Intersect,Except etc.
This example uses Intersect to return contacts that appear in both tables.
‘ Code in VB.NET
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
' Get two rows from the SalesOrderHeader table.
Dim table As DataTable = ds.Tables("SalesOrderHeader")
Dim left = table.Rows(0)
Dim right = table.Rows(1)
' Compare the two different rows.
Dim comparer As IEqualityComparer(Of DataRow) = DataRowComparer.Default
Dim bEqual = comparer.Equals(left, right)
If (bEqual = True) Then
Console.WriteLine("Two rows are equal")
Else
Console.WriteLine("Two rows are not equal")
End If
' Output the hash codes of the two rows.
Console.WriteLine("The hashcodes for the two rows are {0}, {1}", _
comparer.GetHashCode(left), _
comparer.GetHashCode(right))
Data binding is a common use of DataTable object. The CopyToDataTable method takes the results of a query and copies the data into a DataTable, which can then be used for data binding. When the data operations have been performed, the new DataTable is merged back into the source DataTable.
The following example queries the SalesOrderHeader table for orders after August 8, 2001 and uses the CopyToDataTable method to create a DataTable from that query. The DataTable is then bound to a BindingSource, which acts as proxy for a DataGridView.
// Code in C#
// Bind the System.Windows.Forms.DataGridView object
// to the System.Windows.Forms.BindingSource object.
dataGridView.DataSource = bindingSource;
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
// Query the SalesOrderHeader table for orders placed
// after August 8, 2001.
IEnumerable query =
from order in orders.AsEnumerable()
where order.Field("OrderDate") > new DateTime(2001, 8, 1)
select order;
// Create a table from the query.
DataTable boundTable = query.CopyToDataTable();
// Bind the table to a System.Windows.Forms.BindingSource object,
// which acts as a proxy for a System.Windows.Forms.DataGridView object.
bindingSource.DataSource = boundTable;
Conclusion
This is not end of LINQ. Next 3 articles will describe remaining part of LINQ to Dataset, LINQ to SQL & Advantage of using LINQ.
Introduction
Language-Integrated Query (LINQ) defines a set of general-purpose standard query operators that developer can use in .NET Framework 3.0 programming languages. These standard query operators enable developer to project, filter, and traverse in-memory collections or tables in a database. The LINQ queries are expressed in the programming language itself, and not as string literals embedded in the application code. This is a significant change from the way most applications have been written on earlier versions of the .NET Framework. Writing queries from within programming language offers several key advantages. It simplifies querying by eliminating the need to use a separate query language. And if developer uses the Visual Studio 2008 IDE, LINQ also lets developer take advantage of compile-time checking, static typing, and IntelliSense.
LINQ is integrated into various aspects of data access in the .NET Framework, including the DataSet disconnected programming model and existing SQL Server database schemas.
LINQ to ADO.NET consists of two separate technologies: LINQ to DataSet and LINQ to SQL. LINQ to DataSet provides richer, optimized querying over the DataSet and LINQ to SQL enables developers to directly query SQL Server database schemas.
Transferring data from SQL tables into objects in memory is often tedious and error-prone. The LINQ provider implemented by LINQ to DataSet and LINQ to SQL converts the source data into IEnumerable-based object collections. The programmer always views the data as an IEnumerable collection, both when developer query and when developer update. Full IntelliSense support is provided for writing queries against those collections in .NET IDE.
The following figure provides an overview of how LINQ to ADO.NET relates to high-level programming languages, other LINQ technologies, and LINQ-enabled data sources.
Overview of LINQ & ADO.NET Programming
Many business developers must use two (or more) programming languages: a high-level language for the business logic and presentation layers (such as Visual C# or Visual Basic), and a query language to interact with the database (such as Transact-SQL). This requires the developer to be proficient in several languages to be effective, and also causes language mismatches in the development environment. For example, an application that uses a data access API to execute a query against a database specifies the query as a string literal by using quotation marks. This query string is un-readable to the compiler and is not checked for errors, such as invalid syntax or whether the columns or rows it references actually exist. There is no type checking of the query parameters and no IntelliSense support, either.
Language-Integrated Query (LINQ) enables developers to form set-based queries in their application code, without having to use a separate query language. Developer can write LINQ queries against various enumerable data sources (that is, a data source that implements the IEnumerable interface), such as in-memory data structures, XML documents, SQL databases, and DataSet objects. Although these enumerable data sources are implemented in various ways, they all expose the same syntax and language constructs. Because queries can be formed in the programming language itself, developer do not have to use another query language that is embedded as string literals that cannot be understood or verified by the compiler. Integrating queries into the programming language also enables Visual Studio programmers to be more productive by providing compile-time type and syntax checking, and IntelliSense in IDE. These features reduce the need for query debugging and error fixing.
LINQ to Dataset
ADO.NET has two different part of programming. The DataSet is a key element of the disconnected programming model and is widely used. LINQ to DataSet enables developers to build richer query capabilities into DataSet by using the same query formulation mechanism that is available for many other data sources.
LINQ to Dataset overview
LINQ to DataSet makes it easier and faster to query over data cached in a DataSet object. Specifically, LINQ to DataSet simplifies querying by enabling developers to write queries from the programming language itself, instead of by using a separate query language. This is especially useful for Visual Studio developers, who can now take advantage of the compile-time syntax checking, static typing, and IntelliSense support provided by the Visual Studio in their queries.
LINQ to DataSet can also be used to query over data that has been consolidated from one or more data sources. This enables many scenarios that require flexibility in how data is represented and handled, such as querying locally aggregated data and middle-tier caching in Web applications. In particular, generic reporting, analysis, and business intelligence applications require this method of manipulation.
The LINQ to DataSet functionality is exposed primarily through the extension methods in the DataRowExtensions and DataTableExtensions classes. LINQ to DataSet builds on and uses the existing ADO.NET 2.0 architecture, and is not meant to replace ADO.NET 2.0 in application code. Existing ADO.NET 2.0 code will continue to function in a LINQ to DataSet application.
The LINQ to DataSet functionality is exposed primarily through the extension methods in the DataRowExtensions and DataTableExtensions classes. LINQ to DataSet builds on and uses the existing ADO.NET 2.0 architecture, and is not meant to replace ADO.NET 2.0 in application code. Existing ADO.NET 2.0 code will continue to function in a LINQ to DataSet application.
The DataSet is one of the more widely used components of ADO.NET. It is a key element of the disconnected programming model that ADO.NET is based on, and it enables developer to explicitly cache data from different data sources. For the presentation tier, the DataSet is tightly integrated with GUI controls for data-binding. For the middle-tier, it provides a cache that preserves the relational shape of data, and includes fast simple query and hierarchy navigation services. A common technique used to lower the number of requests on a database is to use the DataSet for caching in the middle-tier. For example, consider a data-driven ASP.NET Web application. Often, a significant portion of the application data does not change frequently and is common across sessions or users. This data can be kept in memory on the Web server, which reduces the number of requests against the database and speeds up the user’s interactions. Another useful aspect of the DataSet is that it allows an application to bring subsets of data from one or more data source into the application space. The application can then manipulate the data in-memory, while retaining its relational shape.
Despite its prominence, the DataSet has limited query capabilities. The Select method can be used for filtering and sorting, and the GetChildRows and GetParentRow methods can be used for hierarchy navigation. For anything more complex, however, the developer must write a custom query. This can result in applications that perform poorly and are difficult to maintain.
LINQ to DataSet makes it easier and faster to query over data cached in a DataSet object. These queries are expressed in the programming language itself, rather than as string literals embedded in the application code. This means that developers do not have to learn a separate query language. Additionally, LINQ to DataSet enables Visual Studio developers to work more productively, because the Visual Studio IDE provides compile-time syntax checking, static typing, and IntelliSense support for LINQ. LINQ to DataSet can also be used to query over data that has been consolidated from one or more data sources. This enables many scenarios that require flexibility in how data is represented and handled. In particular, generic reporting, analysis, and business intelligence applications require this method of manipulation.
Querying DataSets Using LINQ to DataSet
Before developer can begin querying a DataSet object using LINQ to DataSet, developer must populate the DataSet. There are several ways to load data into a DataSet, such as using the DataAdapter class or LINQ to SQL. After the data has been loaded into a DataSet object, developer can begin to query it. Formulating queries using LINQ to DataSet is similar to using Language-Integrated Query (LINQ) against other LINQ-enabled data sources. LINQ queries can be performed against single tables in a DataSet or against more than one table by using the Join and GroupJoin standard query operators.
LINQ queries are supported against both typed and untyped DataSet objects. If the schema of the DataSet is known at application design time, a typed DataSet is recommended. In a typed DataSet, the tables and rows have typed members for each of the columns, which makes queries simpler and more readable.
In addition to the standard query operators implemented in System.Core.dll, LINQ to DataSet adds several DataSet-specific extensions that make it easier to query over a set of DataRow objects. These DataSet-specific extensions include operators for comparing sequences of rows, as well as methods that provide access to the column values of a DataRow.
N-tier Applications and LINQ to DataSet
N-tier data applications are data-centric applications that are separated into multiple logical layers (or tiers). A typical N-tier application includes a presentation tier, a middle tier, and a data tier. Separating application components into separate tiers increases the maintainability and scalability of the application. In N-tier applications, the DataSet is often used in the middle-tier to cache information for a Web application. The LINQ to DataSet querying functionality is implemented through extension methods and extends the existing ADO.NET 2.0 DataSet.
How to work with LINQ & Dataset (Code Snipped)
A DataSet object must first be populated before we can query over it with LINQ to DataSet. There are several different ways to populate the DataSet 1) LINQ to SQL to query the database and load the results into the DataSet 2) Common way to load data into a DataSet is to use the DataAdapter class to retrieve data from the database. As example following code -
// code in C#
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". Default database in SQL server 2000
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);
}
A query is an expression that retrieves data from a data source. Language-Integrated Query (LINQ) offers a simpler, consistent model for working with data across various kinds of data sources and formats. In a LINQ query, developer always works with programming objects.
A LINQ query operation consists of three actions: obtain the data source or sources, create the query, and execute the query.
Data sources that implement the IEnumerable<(Of <(T>)>) generic interface can be queried through LINQ. Calling AsEnumerable on a DataTable returns an object which implements the generic IEnumerable<(Of <(T>)>) interface, which serves as the data source for LINQ to DataSet queries.
In the query, developer specifies exactly the information that developer wants to retrieve from the data source. A query can also specify how that information should be sorted, grouped, and shaped before it is returned. In LINQ, a query is stored in a variable. If the query is designed to return a sequence of values, the query variable itself must be an enumerable type. This query variable takes no action and returns no data; it only stores the query information. After developer creates a query developer must execute that query to retrieve any data.
In a query that returns a sequence of values, the query variable itself never holds the query results and only stores the query commands. Execution of the query is deferred until the query variable is iterated over in a foreach or For Each loop. This is called deferred execution; that is, query execution occurs some time after the query is constructed. This means that developer can execute a query as often as developer want to. This is useful when, for example, developer have a database that is being updated by other applications. In your application, developer can create a query to retrieve the latest information and repeatedly execute the query, returning the updated information every time.
In contrast to deferred queries, which return a sequence of values, queries that return a singleton value are executed immediately. Some examples of singleton queries are Count, Max, Average, and First. These execute immediately because the query results are required to calculate the singleton result. For example, in order to find the average of the query results the query must be executed so that the averaging function has input data to work with. Developer can also use the ToList<(Of <(TSource>)>) or ToArray<(Of <(TSource>)>) methods on a query to force immediate execution of a query that does not produce a singleton value. These techniques to force immediate execution can be useful when developer want to cache the results of a query.
LINQ to DataSet queries can be formulated in two different syntaxes: query expression syntax and method-based query syntax.
Query expressions are declarative query syntax. This syntax enables a developer to write queries in C# or Visual Basic in a format similar to SQL. By using query expression syntax, developer can perform even complex filtering, ordering, and grouping operations on data sources with minimal code.
Query expression syntax is new in C# 3.0 and Visual Basic 2008. As a developer have the option of calling them directly by using method syntax, instead of using query syntax.
The following example uses select to return all the rows from Product table and display the product names.
//Code in C#
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable products = ds.Tables["Product"];
IEnumerable
from product in products.AsEnumerable()
select product;
Console.WriteLine("Product Names:");
foreach (DataRow p in query)
{
Console.WriteLine(p.Field
}
This example uses Select to return all the rows from Product and display the product names.
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable products = ds.Tables["Product"];
var query = products.AsEnumerable().
Select(product => new
{
ProductName = product.Field
ProductNumber = product.Field
Price = product.Field
});
Console.WriteLine("Product Info:");
foreach (var productInfo in query)
{
Console.WriteLine("Product name: {0} Product number: {1} List price: ${2} ",
productInfo.ProductName, productInfo.ProductNumber, productInfo.Price);
}
In the following example, the first query returns all the products ordered by list price. The ToArray<(Of <(TSource>)>) method is used to force immediate query execution:
//Code in C#
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable products = ds.Tables["Product"];
IEnumerable
from product in products.AsEnumerable()
orderby product.Field
select product;
// Force immediate execution of the query.
IEnumerable
Console.WriteLine("Every price from highest to lowest:");
foreach (DataRow prod in productsArray)
{
Console.WriteLine(prod.Field
}
Language-Integrated Query (LINQ) queries work on data sources that implement the IEnumerable<(Of <(T>)>) interface or the IQueryable interface. The DataTable class does not implement either interface, so you must call the AsEnumerable method if you want to use the DataTable as a source in the From clause of a LINQ query.
The following example gets all the online orders from the SalesOrderHeader table and outputs the order ID, order date, and order number to the console.
//Code in C#
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
var query =
from order in orders.AsEnumerable()
where order.Field
select new
{
SalesOrderID = order.Field
OrderDate = order.Field
SalesOrderNumber = order.Field
};
foreach (var onlineOrder in query)
{
Console.WriteLine("Order ID: {0} Order date: {1:d} Order number: {2}",
onlineOrder.SalesOrderID,
onlineOrder.OrderDate,
onlineOrder.SalesOrderNumber);
}
In addition to querying a single table, developer can also perform cross-table queries in LINQ to DataSet. This is done by using a join. A join is the association of objects in one data source with objects that share a common attribute in another data source, such as a product or contact ID. In object-oriented programming, relationships between objects are relatively easy to navigate because each object has a member that references another object. In external database tables, however, navigating relationships is not as straightforward. Database tables do not contain built-in relationships.
The Language-Integrated Query (LINQ) framework provides two join operators, Join and GroupJoin. These operators perform equi-joins: that is, joins that match two data sources only when their keys are equal. (By contrast, Transact-SQL supports join operators other than equals, such as the less than operator.)
In relational database terms, Join implements an inner join. An inner join is a type of join in which only those objects that have a match in the opposite data set are returned.
The GroupJoin operators have no direct equivalent in relational database terms; they implement a superset of inner joins and left outer joins. A left outer join is a join that returns each element of the first (left) collection, even if it has no correlated elements in the second collection.
The following example performs a traditional join of the SalesOrderHeader and SalesOrderDetail tables from the AdventureWorks sample database to obtain online orders from the month of August.
// Code in C#
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
DataTable details = ds.Tables["SalesOrderDetail"];
var query =
from order in orders.AsEnumerable()
join detail in details.AsEnumerable()
on order.Field
detail.Field
where order.Field
&& order.Field
select new
{
SalesOrderID =
order.Field
SalesOrderDetailID =
detail.Field
OrderDate =
order.Field
ProductID =
detail.Field
};
foreach (var order in query)
{
Console.WriteLine("{0}\t{1}\t{2:d}\t{3}",
order.SalesOrderID,
order.SalesOrderDetailID,
order.OrderDate,
order.ProductID);
}
The following example shows a query over a typed DataSet:
//Code in C#
var query = from o in orders
where o.OnlineOrderFlag == true
select new { o.SalesOrderID,
o.OrderDate,
o.SalesOrderNumber };
foreach(var order in query)
{
Console.WriteLine("{0}\t{1:d}\t{2}",
order.SalesOrderID,
order.OrderDate,
order.SalesOrderNumber);
}
Language-Integrated Query (LINQ) defines various set operators to compare source elements to see if they are equal. LINQ provides set operators like Distinct,Union,Intersect,Except etc.
This example uses Intersect to return contacts that appear in both tables.
‘ Code in VB.NET
' Fill the DataSet.
Dim ds As New DataSet()
ds.Locale = CultureInfo.InvariantCulture
' See the FillDataSet method in the Loading Data Into a DataSet topic.
FillDataSet(ds)
' Get two rows from the SalesOrderHeader table.
Dim table As DataTable = ds.Tables("SalesOrderHeader")
Dim left = table.Rows(0)
Dim right = table.Rows(1)
' Compare the two different rows.
Dim comparer As IEqualityComparer(Of DataRow) = DataRowComparer.Default
Dim bEqual = comparer.Equals(left, right)
If (bEqual = True) Then
Console.WriteLine("Two rows are equal")
Else
Console.WriteLine("Two rows are not equal")
End If
' Output the hash codes of the two rows.
Console.WriteLine("The hashcodes for the two rows are {0}, {1}", _
comparer.GetHashCode(left), _
comparer.GetHashCode(right))
Data binding is a common use of DataTable object. The CopyToDataTable method takes the results of a query and copies the data into a DataTable, which can then be used for data binding. When the data operations have been performed, the new DataTable is merged back into the source DataTable.
The following example queries the SalesOrderHeader table for orders after August 8, 2001 and uses the CopyToDataTable method to create a DataTable from that query. The DataTable is then bound to a BindingSource, which acts as proxy for a DataGridView.
// Code in C#
// Bind the System.Windows.Forms.DataGridView object
// to the System.Windows.Forms.BindingSource object.
dataGridView.DataSource = bindingSource;
// Fill the DataSet.
DataSet ds = new DataSet();
ds.Locale = CultureInfo.InvariantCulture;
FillDataSet(ds);
DataTable orders = ds.Tables["SalesOrderHeader"];
// Query the SalesOrderHeader table for orders placed
// after August 8, 2001.
IEnumerable
from order in orders.AsEnumerable()
where order.Field
select order;
// Create a table from the query.
DataTable boundTable = query.CopyToDataTable
// Bind the table to a System.Windows.Forms.BindingSource object,
// which acts as a proxy for a System.Windows.Forms.DataGridView object.
bindingSource.DataSource = boundTable;
Conclusion
This is not end of LINQ. Next 3 articles will describe remaining part of LINQ to Dataset, LINQ to SQL & Advantage of using LINQ.
Friday, August 15, 2008
Article on ASP.NET Configuration Encryption
Web.config file is a configuration file for the ASP.NET web application. An ASP.NET application has one web.config file on root, which keeps the configurations required for the corresponding Web application. Developers can keep one configuration file for each folder, which overrides properties of root configuration file. Web.config file is written in XML with specific tags having specific meanings. As this file keeps sensitive information of a Web application, it's need to be encrypted. Please check the following url to find more in details with code snipped.
http://aspalliance.com/1695
http://aspalliance.com/1695
Subscribe to:
Posts (Atom)