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();

    }

}