Linq in C#.net with example

Linq in C#.net with example:

1. Creates a class DataContext referring to the DB under which the table was present. The class will have your dbml file name as a prefix:
eg: CSharp7DataContext

2. Creates a seperate class referring to each table or view that is placed on OR-Designer. The name of class will be same as table or view name.
eg: Students Or Customer Or Employee

3. Defines properties under table class where each property refers to each column of the table.

4. Provides methods using which we can perform CRUD operations.

Note: to view all the above goto, solution explorer & expand the .dbml file node which dispays a file desinger.cs, under it we can find all the above.
-------------------------------------------------------------------------------------
Customer:
   Custid(PK) Int
   Cname Varchar  
   City Varchar
   Balance Money
-------------------------------------------------------------------------------------
Under Form Load:
CSharp7DataContext dc = new CSharp7DataContext();
dataGridView1.DataSource = dc.GetTable<Customer>();

Note: GetTable is a generic method which will returns the table we requested for.
-------------------------------------------------------------------------------------
Performing DML Operations thru Linq:
-If we want to perform any DML operations using Linq we adopt the following process:

-Steps For Inserting:
1. Create the object of class (Table) into which we want to insert a record where each object is a record.
2. Referring to properties of the object assign vals, as we are aware a property is a column.
3. Call InsertOnSubmit method on DataContext object referring to records (Customers) of table that adds record under table in a pending state.
4. Call SubmitChanges method on DataContext object for commiting the changes to DB server.

-Steps for Updating:
1. Identify the Record that has to be Updated by calling SingleOrDefault method on DataContext object referring to the records (Customers).
2. Re-assign values to properties so that old values gets changed to new values.
3. Call SubmitChanges method.

-Steps for Deleting:
1. Identify the Record that has to be deleted same as in update.
2. Call DeleteOnSubmit method on DataContext object referring to the records (Customers) that deletes the record from table in a pending state.
3. Call SubmitChanges method.
-------------------------------------------------------------------------------------
         Code in first form
-------------------------------------------------------------------------------------
Declarations:
  CSharp7DataContext dc;
-------------------------------------------------------------------------------------
private void LoadData()
{
  dc = new CSharp7DataContext();
  dgView.DataSource = dc.GetTable<Customer>();
}
-------------------------------------------------------------------------------------
Under Form Load:
  LoadData();
-------------------------------------------------------------------------------------
Under Insert Button:
  Form3 f = new Form3(); //Form3 is second form
  f.button1.Text = "Insert";
  f.ShowDialog();
  LoadData(); //Re-loads the data after insert
-------------------------------------------------------------------------------------
Under Update Button:
if (dgView.SelectedRows.Count > 0)
{
 Form3 f = new Form3(); //Form3 is second form
 f.textBox1.Text = dgView.SelectedRows[0].Cells [0].Value.ToString();
 f.textBox2.Text = dgView.SelectedRows[0].Cells [1].Value.ToString();
 f.textBox3.Text = dgView.SelectedRows[0].Cells [2].Value.ToString();
 f.textBox4.Text = dgView.SelectedRows[0].Cells [3].Value.ToString();
 f.textBox1.ReadOnly = true;
 f.button1.Text = "Update";
 f.ShowDialog();
 LoadData(); //Re-loads the data after update
}
else
 MessageBox.Show("Select a record to update");  
-------------------------------------------------------------------------------------
Under Delete Button:
if (dgView.SelectedRows.Count > 0)
{
 if (MessageBox.Show(
     "Do you wish to delete the record?",                               "Confirmation", MessageBoxButtons.YesNo,                  MessageBoxIcon.Question)                              ==DialogResult.Yes)
 {
  int custid = Convert.ToInt32(
                 dgView.SelectedRows[0].Cells[0].Value);
  Customer obj = dc.Customers.SingleOrDefault(
C => C.Custid == custid);
  dc.Customers.DeleteOnSubmit(obj);
  dc.SubmitChanges();
  LoadData(); //Re-loads the data after delete
 }
}
else
  MessageBox.Show("Select a record to delete");  
-------------------------------------------------------------------------------------
Under Close Button:
  this.Close();
-------------------------------------------------------------------------------------
        Code in second form
-------------------------------------------------------------------------------------
Under Save Button:
CSharp7DataContext dc = new CSharp7DataContext();
if (button1.Text == "Insert")
{
  Customer obj = new Customer();
  obj.Custid = int.Parse(textBox1.Text);
  obj.Cname = textBox2.Text;
  obj.City = textBox3.Text;
  obj.Balance = decimal.Parse(textBox4.Text);
  dc.Customers.InsertOnSubmit(obj);
  dc.SubmitChanges();                
}
else
{
  Customer obj = dc.Customers.SingleOrDefault(
             C => C.Custid == int.Parse(textBox1.Text));
  obj.Cname = textBox2.Text;
  obj.City = textBox3.Text;
  obj.Balance = decimal.Parse(textBox4.Text);
  dc.SubmitChanges();
}
-------------------------------------------------------------------------------------
Under Close Button:
  this.Close();
-------------------------------------------------------------------------------------
Calling Stored Procedures thru Linq:
-If we want to call any SP of Sql Server DB using Linq we need to first drag & drop the SP on RHS panel of OR-designer, so that it get's converted into a method under DataContext class with same name of the procedure.

-If the SP has any params they will be defined for the method also, where input params of procedure becomes input params to method & output params of procedure becomes ref params to the method.

e.g.:
-If the below procedure was dropped on RHS panel of OR-designer:
  Create Procedure Add(@x int, @y int, @z int output)

-The method gets created as following:
  public int Add(int? x, int? y, ref int? z)

-If the SP contains Insert or Update or Delete or has any output params in such cases the return type of method will be int, where as if the SP has any select stmts in it that returns tables as result then the return type of method will be a ResultSet (Collection of Tables).

-Now we can call the method by creating object of DataContext class & pass required values for the method.
-------------------------------------------------------------------------------------
Declarations:
CSharp7DataContext dc;
-------------------------------------------------------------------------------------
Under Form Load:
dc = new CSharp7DataContext();
-------------------------------------------------------------------------------------
Under Execute Button:
string sname = null; decimal? afees = null;
dc.Get_SDetails(int.Parse(textBox1.Text),
ref sname, ref afees);
textBox2.Text = sname;
textBox3.Text = afees.ToString();
-------------------------------------------------------------------------------------
Under Close:
 this.Close();
-------------------------------------------------------------------------------------
Filtering the data using Linq:
-If we want to filter the data using Linq, we can use the "Linq To Sql" query stmt's, same as we used for quering on collections.

var tab = from <alias> in <table> [<Clauses>] select <alias> | new { <list of columns> }

Note: Clauses can be where, group by, having & order by.
-------------------------------------------------------------------------------------
Declarations:
 CSharp7DataContext dc;
-------------------------------------------------------------------------------------
Under Form Load:
 dc = new CSharp7DataContext();
 dgView.DataSource = from C in dc.Customers select C;                  
-------------------------------------------------------------------------------------
Under ComboBox SelectedIndexChanged:
if (comboBox1.Text != "All")
 dgView.DataSource = from C in dc.Customers                        where C.City == comboBox1.Text select C;
else
 dgView.DataSource = from C in dc.Customers select C;
-------------------------------------------------------------------------------------
Under Balance Asc Button:
 dgView.DataSource = from C in dc.Customers orderby C.Balance select C;
-------------------------------------------------------------------------------------
Under Balance Desc Button:
 dgView.DataSource = from C in dc.Customers orderby C.Balance descending select C;
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------



Class Declarations:
   CSharp7DataContext dc;
-------------------------------------------------------------------------------------
Under Form Load:
   dc = new CSharp7DataContext();
   var tab = from E in dc.Emps select E;
   dataGridView1.DataSource = tab;
-------------------------------------------------------------------------------------
Under ComboBox SelectedIndexChanged:
var tab = from E in dc.Emps select E;
if (comboBox1.Text != "All")
   tab = from E in dc.Emps where E.Job == comboBox1.Text select E;         dataGridView1.DataSource = tab;
-------------------------------------------------------------------------------------
Under Button1:
var tab = from E in dc.Emps orderby E.Salary select E;
dataGridView1.DataSource = tab;
-------------------------------------------------------------------------------------
Under Button2:
var tab = from E in dc.Emps orderby E.Salary descending select E;
dataGridView1.DataSource = tab;
-------------------------------------------------------------------------------------
Non-Ansi Join:
Select E.Empno, E.Ename, E.Job, E.Sal, E.Hiredate, D.Deptno, D.Dname, D.Loc From Emp E, Dept D Where E.Deptno=D.Deptno

Ansi Join:
Select E.Empno, E.Ename, E.Job, E.Sal, E.Hiredate, D.Deptno, D.Dname, D.Loc From Emp E Inner Join Dept D On E.Deptno=D.Deptno

Linq Join:
from E in dc.Emps join D in dc.Depts on E.Deptno equals D.Deptno select new {E.Empno, E.Ename, E.Job, E.Sal, E.Hiredate, D.Deptno, D.Dname, D.Loc };