Introduction
This chapter demonstrates about the LINQ to Dataset is a technology of the LINQ to ADO.NET in which queries are performs on Datasets to enhance the productivity. Following are the concepts are covered in this chapter.
- LINQ to Dataset
- Querying Dataset
Description
LINQ to Dataset is used to query the data in dataset from the front end itself by using the programming language. Dataset is a standard object which is utilised as a part of ADO.Net to work with detached information sources and alternatively upgrade the information source at a time when changes are made working in separated mode. Linq to dataset gives a chance to inquiry dataset articles utilising LINQ inquiries. The figure below demonstrates the LINQ to Datsset.
LINQ to DataSet is usefulness uncovered essentially passed by augmentation strategies in the DataTableExtensions and DataRowExtensions classes.
LINQ to DataSet expands and utilises the current ADO.NET 2.0 building design, which is not intended to supplant to application code. Present ADO.NET 2.0 code will keep on working in a LINQ to DataSet application.The First step in a LINQ Query is to acquire an information of source. With Linq to dataset, Name clearly says that user need to fill the dataset object from the information source. The code below demonstrates the LINQ to Dataset.
[csharp]
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace LINQtoDataset
{
class Program
{
static void Main(string[] args)
{
string connectString = System.Configuration.ConfigurationManager.ConnectionStrings["LinqToSQLDBConnectionString"].ToString();
string sqlSelect = "SELECT * FROM Department;" +
"SELECT * FROM Employee;";
// Create the data adapter to retrieve data from the database
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, connectString);
// Create table mappings
da.TableMappings.Add("Table", "Department");
da.TableMappings.Add("Table1", "Employee");
// Create and fill the DataSet
DataSet ds = new DataSet();
da.Fill(ds);
DataRelation dr = ds.Relations.Add("FK_Employee_Department",
ds.Tables["Department"].Columns["DepartmentId"],
ds.Tables["Employee"].Columns["DepartmentId"]);
DataTable department = ds.Tables["Department"];
DataTable employee = ds.Tables["Employee"];
var query = from d in department.AsEnumerable()
join e in employee.AsEnumerable()
on d.Field<int>("DepartmentId") equals
e.Field<int>("DepartmentId")
select new
{
EmployeeId = e.Field<int>("EmployeeId"),
Name = e.Field<string>("Name"),
DepartmentId = d.Field<int>("DepartmentId"),
DepartmentName = d.Field<string>("Name")
};
foreach (var q in query)
{
Console.WriteLine("Employee Id = {0} , Name = {1} , Department Name = {2}",
q.EmployeeId, q.Name, q.DepartmentName);
}
Console.WriteLine("\nPress any key to continue.");
Console.ReadKey();
}
}
}
[/csharp]
The code below demonstrates the LINQ to dataset in VB.
[csharp]
Imports System.Data.SqlClient
Imports System.Linq
Module LinqToDataSet
Sub Main()
Dim connectString As String = System.Configuration.ConfigurationManager.ConnectionStrings("LinqToSQLDBConnectionString").ToString()
Dim sqlSelect As String = "SELECT * FROM Department;" + "SELECT * FROM Employee;"
Dim sqlCnn As SqlConnection = New SqlConnection(connectString)
sqlCnn.Open()
Dim da As New SqlDataAdapter
da.SelectCommand = New SqlCommand(sqlSelect, sqlCnn)
da.TableMappings.Add("Table", "Department")
da.TableMappings.Add("Table1", "Employee")
Dim ds As New DataSet()
da.Fill(ds)
Dim dr As DataRelation = ds.Relations.Add("FK_Employee_Department", ds.Tables("Department").Columns("DepartmentId"), ds.Tables("Employee").Columns("DepartmentId"))
Dim department As DataTable = ds.Tables("Department")
Dim employee As DataTable = ds.Tables("Employee")
Dim query = From d In department.AsEnumerable()
Join e In employee.AsEnumerable() On d.Field(Of Integer)("DepartmentId") Equals
e.Field(Of Integer)("DepartmentId")
Select New Person With{ _
.EmployeeId = e.Field(Of Integer)("EmployeeId"),
.EmployeeName = e.Field(Of String)("Name"),
.DepartmentId = d.Field(Of Integer)("DepartmentId"),
.DepartmentName = d.Field(Of String)("Name")
}
For Each e In query
Console.WriteLine("Employee Id = {0} , Name = {1} , Department Name = {2}", e.EmployeeId, e.EmployeeName, e.DepartmentName)
Next
Console.WriteLine(vbLf & "Press any key to continue.")
Console.ReadKey()
End Sub
Class Person
Public Property EmployeeId As Integer
Public Property EmployeeName As String
Public Property DepartmentId As Integer
Public Property DepartmentName As String
End Class
End Module
[/csharp]
Description
The DataSet object has been populated with information when the user can start the questioning on the Datasets. Defining inquiries with LINQ to DataSet like utilising Language-Integrated Query (LINQ) against the other LINQ-empowered information sources.
When User utilise LINQ inquiries over a DataSet object User need to questioning and identification of DataRow object, rather than a specification of a custom sort which implies user can utilise any individuals form DataRow class in LINQ questions which makes rich, complex inquiries. The Code below demonstrates the use of querying datsets is a shown below.
[csharp]
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace LinqToDataset
{
class SingleTable
{
static void Main(string[] args)
{
string connectString = System.Configuration.ConfigurationManager.ConnectionStrings["LinqToSQLDBConnectionString"].ToString();
string sqlSelect = "SELECT * FROM Department;";
// Create the data adapter to retrieve data from the database
SqlDataAdapter da = new SqlDataAdapter(sqlSelect, connectString);
// Create table mappings
da.TableMappings.Add("Table", "Department");
// Create and fill the DataSet
DataSet ds = new DataSet();
da.Fill(ds);
DataTable department = ds.Tables["Department"];
var query = from d in department.AsEnumerable()
select new
{
DepartmentId = d.Field<int>("DepartmentId"),
DepartmentName = d.Field<string>("Name")
};
foreach (var q in query)
{
Console.WriteLine("Department Id = {0} , Name = {1}",
q.DepartmentId, q.DepartmentName);
}
Console.WriteLine("\nPress any key to continue.");
Console.ReadKey();
}
}
}
[/csharp]
The code below demonstrates the use of Querying Datasets in VB.
[csharp]
Imports System.Data.SqlClient
Imports System.Linq
Module LinqToDataSet
Sub Main()
Dim connectString As String = System.Configuration.ConfigurationManager.ConnectionStrings("LinqToSQLDBConnectionString").ToString()
Dim sqlSelect As String = "SELECT * FROM Department;"
Dim sqlCnn As SqlConnection = New SqlConnection(connectString)
sqlCnn.Open()
Dim da As New SqlDataAdapter
da.SelectCommand = New SqlCommand(sqlSelect, sqlCnn)
da.TableMappings.Add("Table", "Department")
Dim ds As New DataSet()
da.Fill(ds)
Dim department As DataTable = ds.Tables("Department")
Dim query = From d In department.AsEnumerable()
Select New DepartmentDetail With {
.DepartmentId = d.Field(Of Integer)("DepartmentId"),
.DepartmentName = d.Field(Of String)("Name")
}
For Each e In query
Console.WriteLine("Department Id = {0} , Name = {1}", e.DepartmentId, e.DepartmentName)
Next
Console.WriteLine(vbLf & "Press any key to continue.")
Console.ReadKey()
End Sub
Public Class DepartmentDetail
Public Property DepartmentId As Integer
Public Property DepartmentName As String
End Class
End Module
[/csharp]
Points
- Dataset is the most widely used component of ADO.Net.
- By using Data adaptor use can load the data.
- User can use the advantages of Visual studio in queries.