Monday 29 April 2013

LINQ-Hour 2 (LINQ to SQL)


Written By:-Isha Malhotra(malhotra.isha3388@gmail.com)
if you find this article useful then leave your comment

LINQ to SQL
SQL is a structured Query Language. When we need to interact with database using LINQ then we mapped our relational database to object oriented language using LINQ to SQL.
To interact with database we use Datacontext class which is in System.Data.LINQ. this class contains the constructor in which we have to pass the connection string.

Steps to work with LINQ to SQL

Step 1:-
Add LINQ to SQL Class by using Add New Item. It will add .dbml file which is database management language.



Figure 1

Step 2:-

Now add class by using Add New Item. These classes are used to mapped your database with object oriented. After adding the class, add the namespace System.Data.LINQ.Mapping. Then add [Table] tag to the class and [Column] to the variable which is as follows:-
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Linq.Mapping;


[Table(Name="prod_rep")]
public class Class1
{
    [Column]
    public int prod_year;
    [Column]
    public string dept;
    [Column]
    public int no_of_prod;

}

Prod_rep is table name in the database and others are columns.

Step 3:-

Now create the object of DataContext class and pass the connection string in the constructor. Now create the query for selecting the data from the table.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Linq;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        //create the object of data context class and pass the connection string
        DataContext dc = new DataContext("Data Source=ISHA-PC;Initial Catalog=TechAltum;Integrated Security=True");

        //create the query for selection
        var x = from data in dc.GetTable<Class1>()
                select new { Prod_year = data.prod_year, Dept = data.dept, No_of_Prod = data.no_of_prod };

        //bind the data in gridview
        GridView1.DataSource = x;
        GridView1.DataBind();
    }
}



The output of this code as follows:-



Figure 2

Insert data in Database using LINQ

When we need to insert the data in SQL server using LINQ to SQL then we use the following code:-
When we insert using LINQ then your table must have primary key. So the table which I was using not contain primary key. So I added one more column name id in the table which is primary and auto generated (used identity). So I have to make following changes in the class which maps my table to object oriented language which is as follows:-
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Linq.Mapping;


[Table(Name="prod_rep")]
public class Class1
{
    [Column]
    public int prod_year;
    [Column]
    public string dept;
    [Column]
    public int no_of_prod;

    //isdbgenerated is used for auto generated column which is identity
    [Column(IsPrimaryKey=true,IsDbGenerated=true)]
    public int id;
}



Now use the following code to insert data in the table:-
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Linq;

public partial class Insert_LINQ_To_SQL : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        //create the object of data context class and pass the connection string
        DataContext dc = new DataContext("Data Source=ISHA-PC;Initial Catalog=TechAltum;Integrated Security=True");

        //create object of class which mapped the table in database
        Class1 cal_data = new Class1();

        //add data to be inserted
        cal_data.prod_year=Convert.ToInt32(TextBox1.Text);
        cal_data.dept = TextBox2.Text;
        cal_data.no_of_prod = Convert.ToInt32(TextBox3.Text);

        //add data into the data context class for insertion
        dc.GetTable<Class1>().InsertOnSubmit(cal_data);
        dc.SubmitChanges();
     
      
    }
}

Delete data from database using LINQ


Use the following code to delete the data:-
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Linq;

public partial class Insert_LINQ_To_SQL : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        //create the object of data context class and pass the connection string
        DataContext dc = new DataContext("Data Source=ISHA-PC;Initial Catalog=TechAltum;Integrated Security=True");

        //create object of class which mapped the table in database
        Class1 cal_data;
       

        //set the where clause for delete the data
        cal_data = dc.GetTable<Class1>().Single(res => res.id == Convert.ToInt32(TextBox1.Text));

        //add object to the delete method
        dc.GetTable<Class1>().DeleteOnSubmit(cal_data);
        dc.SubmitChanges();
      
      
    }
}
                                           
Update data in database using LINQ

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Linq;

public partial class Insert_LINQ_To_SQL : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        //create the object of data context class and pass the connection string
        DataContext dc = new DataContext("Data Source=ISHA-PC;Initial Catalog=TechAltum;Integrated Security=True");

        //create object of class which mapped the table in database
        Class1 cal_data;
       

        //set the where clause for update the data
        cal_data = dc.GetTable<Class1>().Single(res => res.id == 4);

        //add updated data
        cal_data.dept = "changeDept";

       
        dc.GetTable<Class1>();
        dc.SubmitChanges();
      
      
    }
}

hope you enjoyed the article

3 comments:

  1. I want to alias the column name in this query because grid view showing the column name "Item" bydefault but i want to named it student.

    IEnumerable student = from res in techaltum_student select res;
    GridView1.DataSource = student;
    GridView1.DataBind();


    Thanks in advance

    ReplyDelete
  2. I got it Isha by R&D and now i know that it can be achieve using var instead of IEnumerable<>.

    ReplyDelete
    Replies
    1. In my hour 1 article i discussed the concept of var. you can go through it.
      thanks

      Delete