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