using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
namespace LinqToDataTable
{
class Program
{
static void Main(string[] args)
{
Random randomSalary = new Random();
Random randomEmpCode = new Random();
DataTable dataTable = new DataTable();
dataTable.Columns.Add(new DataColumn("Id",Type.GetType("System.Int32")));
dataTable.Columns.Add(new DataColumn("EmployeeCode", Type.GetType("System.String")));
dataTable.Columns.Add(new DataColumn("Salary", Type.GetType("System.Decimal")));
DataRow row = null;
for (int i = 1; i < 21; i++)
{
row = dataTable.NewRow();
row["Id"] = i;
row["EmployeeCode"] = String.Format("Emp{0}", randomEmpCode.Next(101, 201));
row["Salary"] = randomSalary.Next(10000, 500000);
dataTable.Rows.Add(row);
}
IEnumerable<DataRow> empdata = null;
Console.WriteLine("---Reading all employee records.---");
Console.WriteLine(Environment.NewLine);
empdata= from employee in dataTable.AsEnumerable()
select employee;
foreach (DataRow item in empdata)
{
Console.WriteLine("Id : {0} | EmployeeCode : {1} | Salary : {2}"
,item.ItemArray[0]
, item.ItemArray[1]
, item.ItemArray[2]);
}
/*
//If you do not want to use ItemArray you can use Field.
foreach (DataRow item in empdata)
{
Console.WriteLine("Id : {0} | EmployeeCode : {1} | Salary : {2}"
, item.Field<Int32>("Id")
, item.Field<String>("EmployeeCode")
, item.Field<Decimal>("Salary")
);
}
* */
Console.WriteLine(Environment.NewLine);
Console.WriteLine("---Reading employee record order by salary (ascending).---");
Console.WriteLine(Environment.NewLine);
empdata = from employee in dataTable.AsEnumerable()
orderby employee.Field<Decimal>("Salary") ascending
select employee;
foreach (DataRow item in empdata)
{
Console.WriteLine("Id : {0} | EmployeeCode : {1} | Salary : {2}"
, item.ItemArray[0]
, item.ItemArray[1]
, item.ItemArray[2]);
}
Console.WriteLine(Environment.NewLine);
Console.WriteLine("---Reading employee record order by salary (ascending).---");
Console.WriteLine(Environment.NewLine);
empdata = from employee in dataTable.AsEnumerable()
orderby employee.Field<Decimal>("Salary") descending
select employee;
foreach (DataRow item in empdata)
{
Console.WriteLine("Id : {0} | EmployeeCode : {1} | Salary : {2}"
, item.ItemArray[0]
, item.ItemArray[1]
, item.ItemArray[2]);
}
Console.WriteLine(Environment.NewLine);
Console.WriteLine("---Reading single record.---");
Console.WriteLine(Environment.NewLine);
Console.WriteLine("Enter id between 1 to 20.");
Int32 id = Convert.ToInt32(Console.ReadLine());
empdata = from employee in dataTable.AsEnumerable()
where employee.Field<Int32>("Id")==id
select employee;
foreach (DataRow item in empdata)
{
Console.WriteLine("Id : {0} | EmployeeCode : {1} | Salary : {2}"
, item.ItemArray[0]
, item.ItemArray[1]
, item.ItemArray[2]);
}
Console.WriteLine(Environment.NewLine);
Console.WriteLine("---Reading selected columns.---");
Console.WriteLine(Environment.NewLine);
var records = from employee in dataTable.AsEnumerable()
where employee.Field<Int32>("Id") == id
select new { EmployeeId=employee.ItemArray[1],
EmployeeSalary=employee.ItemArray[2]
};
foreach (var item in records)
{
Console.WriteLine("Employee Id : {0} | Salary : {1}"
, item.EmployeeId
, item.EmployeeSalary);
}
Console.WriteLine(Environment.NewLine);
Console.WriteLine("---Upade data in DataTable.---");
Console.WriteLine(Environment.NewLine);
Console.WriteLine("Enter Id to update record.");
Int32 idToUpdate = Convert.ToInt32(Console.ReadLine());
Console.WriteLine("Enter employee new salary.");
Decimal salary = Convert.ToDecimal(Console.ReadLine());
DataRow employeeRecord = dataTable.AsEnumerable().SingleOrDefault(q => q.Field<Int32>("Id") == idToUpdate);
employeeRecord["Salary"] = salary;
empdata = from employee in dataTable.AsEnumerable()
select employee;
foreach (DataRow item in empdata)
{
Console.WriteLine("Id : {0} | EmployeeCode : {1} | Salary : {2}"
, item.ItemArray[0]
, item.ItemArray[1]
, item.ItemArray[2]);
}
Console.WriteLine(Environment.NewLine);
Console.WriteLine("---Delete data in DataTable.---");
Console.WriteLine(Environment.NewLine);
Console.WriteLine("Enter Id to delete record.");
Int32 idToDelete = Convert.ToInt32(Console.ReadLine());
DataRow employeeToDelete = dataTable.AsEnumerable().SingleOrDefault(q => q.Field<Int32>("Id") == idToDelete);
employeeToDelete.Delete();
empdata = from employee in dataTable.AsEnumerable()
select employee;
foreach (DataRow item in empdata)
{
Console.WriteLine("Id : {0} | EmployeeCode : {1} | Salary : {2}"
, item.ItemArray[0]
, item.ItemArray[1]
, item.ItemArray[2]);
}
Console.Read();
}
}
}
No comments:
Post a Comment