DataSet is standard Ado.Net object of .Net Framework. It is disconnected in nature. It may contain one or more datatable filled
by fillDataset method. DataSet can be filled form DataAdaptor or
datatable can be dynamically populated and added to the DataSet. LINQ
offer query on DataSet also. First we need to include System.LINQ namespace in our
project. During query on DataSet , table are consider as Enumerable (AsEnumerable()). You can query simple select statement or complex joining
statement or update datatable value.
You should remember , you can query on datatable only as it is enumerable.You cannot query on data row collection.
Below the first example is of a simple query on a datatable.
Below the second the example of a with joining statement of two datatable.
Below the third example is of update statement.
Example
Output :
************************ Select Records 'John2'*******
Name = John2 , RollNumber = 13
************************ List of Student , whose marks above 85 *******
Name = John2 , RollNumber = 13, Fees = 13
Name = John3 , RollNumber = 14, Fees = 14
Name = John4 , RollNumber = 15, Fees = 15
Name = John5 , RollNumber = 16, Fees = 16
Name = John9 , RollNumber = 20, Fees = 20
************************ Example of update statement *******
Name = John1 , RollNumber = 999
Name = John2 , RollNumber = 13
Name = John3 , RollNumber = 14
Name = John4 , RollNumber = 15
Name = John5 , RollNumber = 16
Name = John6 , RollNumber = 17
Name = John7 , RollNumber = 18
Name = John8 , RollNumber = 19
Name = John9 , RollNumber = 20
You should remember , you can query on datatable only as it is enumerable.You cannot query on data row collection.
Below the first example is of a simple query on a datatable.
Below the second the example of a with joining statement of two datatable.
Below the third example is of update statement.
Example
using
System;
using
System.Collections.Generic;
using
System.Data.SqlClient;
using
System.Data.Sql;
using
System.Data;
using
System.Linq;
namespace
ConsoleApplication1
{
class
Program
{
static
void
Main(string[]
args)
{
DataSet
ds = new
DataSet();
DataTable
dt1 = new
DataTable();
dt1.Columns.Add("Name",
typeof(string));
dt1.Columns.Add("RollNumber",
typeof(string));
dt1.Columns.Add("RegistrationNumber",
typeof(int));
dt1.Rows.Add("John1",
"12",
122);
dt1.Rows.Add("John2",
"13",
123);
dt1.Rows.Add("John3",
"14",
124);
dt1.Rows.Add("John4",
"15",
125);
dt1.Rows.Add("John5",
"16",
126);
dt1.Rows.Add("John6",
"17",
127);
dt1.Rows.Add("John7",
"18",
128);
dt1.Rows.Add("John8",
"19",
129);
dt1.Rows.Add("John9",
"20",
130);
ds.Merge(dt1);
DataTable
dt2 = new
DataTable();
dt2.Columns.Add("RegistrationNumber",
typeof(int));
dt2.Columns.Add("Fees",
typeof(Decimal));
dt2.Columns.Add("Marks",
typeof(int));
dt2.Rows.Add(122,
215.57, 85);
dt2.Rows.Add(123,
315.57, 86);
dt2.Rows.Add(124,
250.25, 87);
dt2.Rows.Add(125,
275.50, 98);
dt2.Rows.Add(126,
295.85, 99);
dt2.Rows.Add(127,
260.40, 84);
dt2.Rows.Add(128,
250.45, 83);
dt2.Rows.Add(129,
275.65, 82);
dt2.Rows.Add(130,
285.57, 96);
ds.Merge(dt2);
/*****************Example
of a simple query ************/
Console.WriteLine("************************
Select Records 'John2'*******");
var
res = from
exp in
ds.Tables[0].AsEnumerable()
where
exp.Field<string>("Name")
== "John2"
select
new
{
MyName
= exp.Field<string>("Name"),
MyRoll
= exp.Field<string>("RollNumber"),
};
foreach
(var
q in
res)
{
Console.WriteLine("Name
= {0} , RollNumber = {1} ",
q.MyName,
q.MyRoll);
}
Console.ReadKey();
/*****************Example
with joining statement of two datatables ************/
Console.WriteLine("************************
List of Student , whose marks above 85 *******");
var
query = from
p in
ds.Tables[0].AsEnumerable()
join
i in
ds.Tables[1].AsEnumerable()
on
p.Field<int>("RegistrationNumber")
equals
i.Field<int>("RegistrationNumber")
where
i.Field<int>("Marks")
> 85
select
new
{
MyName
= p.Field<string>("Name"),
MyRoll
= p.Field<string>("RollNumber"),
MyFees
= i.Field<decimal>("Fees"),
};
foreach
(var
obj in
query)
{
Console.WriteLine("Name
= {0} , RollNumber = {1}, Fees = {2}",
obj.MyName,
obj.MyRoll, obj.MyRoll);
}
Console.ReadKey();
/*****************Example
of update statement ************/
Console.WriteLine("************************
Example of update statement *******");
ds.Tables[0].AsEnumerable().Where(row
=> row.Field<string>("Name")
== "John1")
.Select(b
=> b["RollNumber"]
= "999")
.ToList();
var
alldata = from
exp in
ds.Tables[0].AsEnumerable()
select
new
{
MyName
= exp.Field<string>("Name"),
MyRoll
= exp.Field<string>("RollNumber"),
};
foreach
(var
q in
alldata)
{
Console.WriteLine("Name
= {0} , RollNumber = {1} ",
q.MyName,
q.MyRoll);
}
Console.ReadKey();
}
}
}
************************ Select Records 'John2'*******
Name = John2 , RollNumber = 13
************************ List of Student , whose marks above 85 *******
Name = John2 , RollNumber = 13, Fees = 13
Name = John3 , RollNumber = 14, Fees = 14
Name = John4 , RollNumber = 15, Fees = 15
Name = John5 , RollNumber = 16, Fees = 16
Name = John9 , RollNumber = 20, Fees = 20
************************ Example of update statement *******
Name = John1 , RollNumber = 999
Name = John2 , RollNumber = 13
Name = John3 , RollNumber = 14
Name = John4 , RollNumber = 15
Name = John5 , RollNumber = 16
Name = John6 , RollNumber = 17
Name = John7 , RollNumber = 18
Name = John8 , RollNumber = 19
Name = John9 , RollNumber = 20
No comments:
Post a Comment