LINQ to SQL with example

LINQ to SQL -

RELATIONAL DATABASES contain important data that developers want to query and retrieve in their programs. LINQ to SQL lets developers access relational data as strongly typed objects by translating LINQ to SQL.It also provides a simple API to save to the database all the changes made to an object graph. Both query and changes are implemented by translating LINQ and API calls to SQL commands. Hence, users get the productivity of LINQ and the efficiency of the database query processor.

LINQ to SQL was created to bridge the differences between relational data and CLR objects. SQL is a rich query language, but it is not well integrated with programming languages such as C# and VB.NET. As a result,database developers have always struggled with the “impedance mismatch”between the relational data in their databases and the objects used in their programs running on the .NET framework.

Now here,I am trying to describe LINQ to SQL with easy example,so let's start a quick tour of LINQ to SQL-

we have a 'employee' table-

CREATE TABLE [dbo].[Employee](
 [FName] [varchar](100) NOT NULL,
 [LName] [varchar](100) NOT NULL,
 [Email] [varchar](100) NOT NULL PRIMARY KEY,
 [mobile] [varchar](11) NULL,
 [phone] [varchar](20) NULL
)

Inserted data in following way-

INSERT [dbo].[Employee] ([FName], [LName], [Email], [mobile], [phone]) VALUES (N'subash', N'chandra', N'subash@company.com', N'9966553322', N'0542122558866')
INSERT [dbo].[Employee] ([FName], [LName], [Email], [mobile], [phone]) VALUES (N'Anoop', N'Singh', N'emp2@company.com', N'9966553322', N'0542122558866')
INSERT [dbo].[Employee] ([FName], [LName], [Email], [mobile], [phone]) VALUES (N'abhishek', N'sachan', N'emp3@company.com', NULL, N'0542122558866')
INSERT [dbo].[Employee] ([FName], [LName], [Email], [mobile], [phone]) VALUES (N'subash', N'chandra', N'emp4@company.com', NULL, N'0542122558866')
INSERT [dbo].[Employee] ([FName], [LName], [Email], [mobile], [phone]) VALUES (N'sachin', N'srivastava', N'emp5@company.com', N'9971885522', NULL)
INSERT [dbo].[Employee] ([FName], [LName], [Email], [mobile], [phone]) VALUES (N'brijesh', N'singh', N'emp6@company.com', NULL, N'0251499663322')
INSERT [dbo].[Employee] ([FName], [LName], [Email], [mobile], [phone]) VALUES (N'nitish', N'sharma', N'emp7@company.com', NULL, NULL)
INSERT [dbo].[Employee] ([FName], [LName], [Email], [mobile], [phone]) VALUES (N'alok', N'gupta', N'emp8@company.com', NULL, NULL)
INSERT [dbo].[Employee] ([FName], [LName], [Email], [mobile], [phone]) VALUES (N'shyam', N'yadav', N'abc@ss.com', N'9965655555', NULL)

Now, start Visual studio,then go to File->New->Project and take asp.net website from window.

Now ,right click on solution name and add new project 'class library' in the same solution with the name 'DataAccessLayer'.Now solution look like this-

Right click on DataAccessLayer project, click on 'add new item' and select 'Linq to SQL classes' and give the name 'Employee.dbml'.

Now connect your database in server explorer(if not able to see ,then go to view and open) ,after connecting   database server explorer look like this-

drag and drop 'employee'  table in Employee.dbml (you may drag and drop many tables according to your need).

Now build 'DataAccessLayer' project.After building database table is converted in object and can be seen  in Employee.Designer.cs.
Now we have to go asp.net project and take reference of this project; i.e. taking reference of DataAccessLayer in LINQtoSQLExample project, also take reference of 'System.data.linq' (skip if already has reference)
Now we are going to search employee by their first name and last name and make UI like this -

Add gridview below it to view employee data match with linq query.

Now write the following code in search button's click event-(write 'using DataAccessLayer' in using section) 

 protected void btnsearch_Click(object sender, EventArgs e)
        {
            using (EmployeeDataContext context = new EmployeeDataContext())
            {
                var employee = from c in context.Employees
                               where c.FName.Contains(txtempname.Text.Trim()) &&
                               c.LName.Contains(txtlastname.Text.Trim())
                               select c;
                gvEmployee.DataSource = employee;
                gvEmployee.DataBind();
            }
        }

Now, you are able to search employee through his/her first and last name,for example- type 'atul' and 'dubey' in first and last name textbox respectively.
Now we are going to insert new employee value in database through LINQ to SQL.First make UI like this -

and on save button's click event ,write the following code-(write 'using DataAccessLayer' in using section)

  protected void btnSave_Click(object sender, EventArgs e)
        {
            using (EmployeeDataContext context = new EmployeeDataContext())
            {
                Employee employee = new Employee
                {
                    FName = txtfname.Text,
                    LName = txtlname.Text,
                    Email = txtemail.Text,
                    mobile = txtmobile.Text,
                    phone = txtphone.Text
                };
                context.Employees.InsertOnSubmit(employee);
                context.SubmitChanges();
            }
        }

Now you are able to inserted data successfully in database. Thanks for reading this post.Your kind suggestions are always cordially welcome. Please send your feedback at atuldubey.87@gmail.com