LINQ

1. Creates a class DataContext referring to the Database under which the table is present.
eg: CSharp7DataContext

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




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

4. Provides methods using which u can perform the select and dml opertions on the tables.

Note: If you want to view all the above things open the solution explorer and expand the .dbml file node which shows u a file desinger.cs, where we can find each and every thing we have discussed.
-------------------------------------------------------------------------------------
   Empno (PK) Int
   Ename Varchar
   Job Varchar
   Salary Money











-------------------------------------------------------------------------------------
Under Form Load:
CSharp11DataContext dc = new CSharp11DataContext();
dataGridView1.DataSource = dc.GetTable<Emp>();

-Create the object of the class that refers to the DB (CSharp11DataContext) and then call the GetTable method by specifying the name of the Table we want to access, which returns the table that gets bound to DataGridView.
-------------------------------------------------------------------------------------

Performing DML Operations thru Linq:
-If u want to perform any DML Operations using Linq we adopt the following process:

-Steps For Inserting:
1. Create the object of the class (Table) into which u want to insert the record where each object is considered as a record.
2. Referring to the properties of the object assign the values, as we were aware a property is a column.
3. Call the method InsertOnSubmit on the DataContext object referring to the records (Emps) of the table which adds the record under the table.
4. Call the SubmitChanges method on the DataContext object for commiting the changes to the Database server.

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

-Steps for Deleting:
1. Identify the Record that has to be Deleted by calling the method SingleOrDefault on the DataContext object referring to the records(Emps).
2. Call the method DeleteOnSubmit on the DataContext object referring to the records (Emps) which deletes the record from the table.
3. Call the SubmitChanges method on the DataContext object for commiting the changes to the DB server.
-------------------------------------------------------------------------------------
Code Under First Form:

Class Declarations:
   CSharp7DataContext dc;
-------------------------------------------------------------------------------------
private void LoadData()
{
  dc = new CSharp7DataContext();
  dataGridView1.DataSource = dc.GetTable<Emp>();
}
-------------------------------------------------------------------------------------
Under Form Load:
   LoadData();
-------------------------------------------------------------------------------------
Under Insert Button:
   Form3 f = new Form3(); //Form3 is second form
   f.button1.Text = "Insert";
   f.ShowDialog();
   LoadData();
-------------------------------------------------------------------------------------
Under Update Button:
if (dataGridView1.SelectedRows.Count > 0)
{
Form3 f = new Form3();
f.textBox1.Text=dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
f.textBox1.ReadOnly = true;
f.textBox2.Text=dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
f.textBox3.Text=dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
f.textBox4.Text=dataGridView1.SelectedRows[0].Cells[3].Value.ToString();
f.button1.Text = "Update";
f.ShowDialog();
LoadData();
}
else
   MessageBox.Show("Select a record to update");
-------------------------------------------------------------------------------------
Under Delete Button:
if (dataGridView1.SelectedRows.Count > 0)
{
int eno = Convert.ToInt32(dataGridView1.SelectedRows[0].Cells[0].Value);
Emp obj = dc.Emps.SingleOrDefault(
E => E.Empno == eno);
dc.Emps.DeleteOnSubmit(obj);
dc.SubmitChanges();
LoadData();
}
else
   MessageBox.Show("Select a record to delete");
-------------------------------------------------------------------------------------
Under Close Button:
   this.Close();
-------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------
Code Under Second Form:
Under Save Button:
CSharp7DataContext dc = new             CSharp7DataContext();
if (button1.Text == "Insert")
{
   Emp obj = new Emp();
   obj.Empno = int.Parse(textBox1.Text);
   obj.Ename = textBox2.Text;
   obj.Job = textBox3.Text;
   obj.Sal = decimal.Parse(textBox4.Text);
   dc.Emps.InsertOnSubmit(obj);
   dc.SubmitChanges();              
}
else
{
   Emp obj = dc.Emps.SingleOrDefault(
             E => E.Empno == int.Parse(textBox1.Text));
   obj.Ename = textBox2.Text;
   obj.Job = textBox3.Text;
   obj.Sal = decimal.Parse(textBox4.Text);
   dc.SubmitChanges();
}
this.Close();
-------------------------------------------------------------------------------------
Under Close Button:
   this.Close();
-------------------------------------------------------------------------------------

Calling Stored Procedures thru Linq:
-If u want to call any existing SP of the database using Linq, u need to first drag and drop the procedures on the RHS panel of the OR designer so that a method gets created under the DataContext class with the same name of the procedure.

-Now u can create the object of the DataContext class and call the method by passing the required values.

-If the procedure is executed successfully it returns u a status value that will be 0 or else -1.

To test this first create a procedure under your database as following using the Server Explorer:

CREATE PROCEDURE Ins_Upd_Emp(@Empno Int, @Ename Varchar(50), @Job Varchar(50), @Salary Money)
As
Begin
If Not Exists(Select Empno From Emp Where
Empno=@Empno)
   Insert Into Emp (Empno, Ename, Job, Salary)
   Values(@Empno, @Ename, @Job, @Salary)
Else
   Update Emp Set Ename=@Ename, Job=@Job,
   Salary=@Salary Where Empno=@Empno
End
-------------------------------------------------------------------------------------
Declarations:
CSharp11DataContext dc;
-------------------------------------------------------------------------------------
Under Form Load:
dc = new CSharp11DataContext();
-------------------------------------------------------------------------------------
Under Get Button:
Emp obj = dc.Emps.SingleOrDefault(
             E => E.Empno == int.Parse(textBox1.Text));
textBox2.Text = obj.Ename;
textBox3.Text = obj.Job;
textBox4.Text = obj.Salary.ToString();
-------------------------------------------------------------------------------------
Under Clear Button:
textBox1.Text = textBox2.Text = "";
textBox3.Text = textBox4.Text = "";
textBox1.Focus();
-------------------------------------------------------------------------------------
Under Insert Button:
int stat =                dc.Ins_Upd_Emp(int.Parse(textBox1.Text),                     textBox2.Text, textBox3.Text,                                              decimal.Parse(textBox4.Text));
if (stat == 0)
   MessageBox.Show("Executed");
else
   MessageBox.Show("Failed");
-------------------------------------------------------------------------------------
Under Update Button:
   button3.PerformClick(); //button3 is Insert Button
   dc = new CSharp11DataContext();
-------------------------------------------------------------------------------------
Under Close Button:
   this.Close();
-------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------
-After the procedure was created open the OR designer and drag & drop the procedure from the Server Explorer to the RHS panel of the OR designer which will immediately create a methods under the DataContext class with the same name of the procedure as following:

public int Ins_Upd_Emp(int? Empno, string Ename, string Job, decimal? Salary)
-------------------------------------------------------------------------------------
Calling a SP with Output parameters:
-If a SP contains any output parameters in it, to create a mapping method under the DataContext class it will use the reference parameters approach in our C#.Net Language.
Create Procedure Get_SDetails(@Sno Int,
@Sname Varchar(50) Output, @Fees Money Output)

public int Get_SDetails(int? Sno, ref string Sname, ref decimal? Fees)

Under Get_SDetails Button:
CSharp11DataContext dc = new CSharp11DataContext();
string sname = null; decimal? fees = null;
dc.Get_SDetails(int.Parse(textBox1.Text),
ref sname, ref fees);
textBox2.Text = sname;
textBox3.Text = fees.ToString();
-------------------------------------------------------------------------------------
Filtering the data using Linq:
-If u want to filter the data using Linq, we can use the same type of query statement we used for quering on collections as following:

from <alias> in <tname> [<clauses>] select <alias>

Note: Clauses can be where, group by, having & order by.
-------------------------------------------------------------------------------------
Class Declarations:
   CSharp11DataContext dc;
-------------------------------------------------------------------------------------
Under Form Load:
   dc = new CSharp11DataContext();
   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;