LINQ to DataSet
A DataSet is an in-memory representation of relational data and is widely used to access data in the .NET Framework. It acts as a core of a wide range of data-based applications. LINQ to DataSet provides rich query capabilities to developers. This feature has made querying the data in a DataSet much faster and easier. Instead of using a separate query language, you can now write queries from the programming language itself. Since the queries are not just embedded strings but a part of the application code, you can also take advantage of static typing, compile-time checking, and IntelliSense support offered by Visual Studio.
Using LINQ to DataSet, you can also query data that is gathered from more than one data source. This is helpful in many scenarios, such as querying locally aggregated data and middle-tier caching in web applications, where you need flexibility in the way data is represented. You can use LINQ queries to access a single table in a DataSet or to access more than one table by using standard query operators like Join and GroupJoin.
LINQ to DataSet works with both typed and untyped DataSets. In a typed DataSet, for each column, the tables and rows have typed members, which makes queries easier to write and understand. For untyped DataSets, LINQ to DataSet provides extensions, such as the Field<T> method, that allow strong typing inside the query.
Apart from the standard query operators, there are many DataSet-specific extensions that make it quite easy to query over a set of DataRow objects. These extensions can be used to access column values of a DataRow or to compare sequences of rows.
The LINQ to DataSet queries use the standard query operators to access data in a DataTable or a DataSet. This allows you to utilize the full power of the .NET Framework while writing queries. You can write some interesting queries very easily that otherwise would be quite difficult to write using the traditional method.
In practice, a DataSet must be populated in order for it to be queried using LINQ to DataSet. To populate your DataSet, create some DataTables and DataRows manually and add them to the DataSet in a loop, or just query some data from a database and fill the DataSet by using the Fill() method of a SQLDataAdapter object. In the following example, we are going to pull back every customer from our database and then query the DataSet:
Dim conn = New SqlConnection("Data Source=(local); " & _
"Integrated Security=True; Initial Catalog=OurCustomers; " & _
"Pooling=false;")
conn.Open()
Dim cmd = New SqlCommand("SELECT ID,Name,Address,City " & _
"FROM Customers WHERE City = 'Seattle'")
cmd.Connection = conn
Dim CustomersDS = New DataSet
Dim DataAdapter = New SqlDataAdapter(cmd)
DataAdapter.Fill(CustomersDS)
Dim AddressList = From customer In CustomersDS.Tables(0) _
Select customer("Address")
For Each Address In AddressList
Console.WriteLine(Address)
Next
Once we have filled our CustomersDS DataSet using the SqlDataAdapter, we can query the DataSet’s table using some LINQ queries. We are only querying the first table [i.e. Table(0)] because we’re only returning one set of results from the database. So the query above will print out the address of any customer living in Seattle.
Another new language feature that we discussed previously was anonymous types. We get full anonymous type support in our queries. Take a look at the following example:
Dim conn = New SqlConnection("Data Source=(local); " & _
"Integrated Security=True; Initial Catalog=OurCustomers; " & _
"Pooling=false;")
conn.Open()
Dim cmd = New SqlCommand("SELECT ID,Name,Address,City " & _
"FROM Customers WHERE City = 'Seattle'")
cmd.Connection = conn
Dim CustomersDS = New DataSet
Dim DataAdapter = New SqlDataAdapter(cmd)
DataAdapter.Fill(CustomersDS)
Dim AddressList = From customer In CustomersDS.Tables(0) _
Select New With {.ID = customer("ID"), _
.Name = customer("Name"), _
.Address = customer("Address"), _
.City = customer("City")}
For Each Customer In AddressList
Console.WriteLine("{0} lives at {1}", Customer.Name, _
Customer.Address)
Next
How many times have you returned data from a database into a DataSet, and then extracted the data out from it into some object instance? In the preceding example, you can see how we can automatically extract out information from the DataSet and into an object instance, and code against that object instance as though it were a fully defined class.
|
No comments:
Post a Comment