Naveen's Weblog

Bridge to future

Archive for the ‘LINQ To SQL’ Category

Basic samples for LINQ to SQL.

LINQ To SQL – Employee Sample

Posted by codingsense on December 16, 2008


In LINQ to SQL, the data model of a relational database is mapped to an object model expressed in the programming language of the developer. When the application runs, LINQ to SQL translates into SQL the language-integrated queries in the object model and sends them to the database for execution. When the database returns the results, LINQ to SQL translates them back to objects that you can work with in your own programming language.

Employee Sample of LINQ to SQL

Download Code (With Database) – 414 Kb


First let me explain you how to create a project for using LINQ to SQL

  • Creating the project:
  • In VS 2008 editor go to FIle -> New -> Project -> Windows Application. Set the target framework to 3.5

  • Attach database file:
  • Attach database file by right clicking on project in project explorer -> Add -> Existing Item -> locate the database file and click ok

  • Create LINQ to SQL Class:
  • Right click on project in project explorer -> Add -> New Item -> Select LINQ to SQL Classes and click add

  • Link Tables to Data Context
  • Double click on attached database , it will get open in server explorer, double click on the .dbml file (LINQ to SQL file) in project explorer, then drag the tables or any item from the server explorer and drop it on the opened .dbml file. The datacontext will link the tables to your program.

By the above steps you will be able to work with LINQ to SQL.

Now let me brief on the sample project of employee.

First i created a windows application project with Target framework 3.5 and named it as LinqToSQl.


Create Project

Then added a new item of Linq to sql class and named it as Employee, after the creation the IDE will automatically create a Data Access layer with name EmployeeDataContext.


New .dbml File

Then i attached an employee database with name Employee.mdf.
Open the Employee.dbml file


.dbml file

Drag the database objects that you will use and drop on the Opened dbml file. I added 1 Employee table and 2 stored procedures named USP_Employee_Delete and USP_Employee_InsertOrUpdate. The tables will be placed in the left hand side and other functions views and stored procedures will be palced in the method pane on the right hand side.


Drag SQL Objects

In this project I have done 3 things Search an employe, Insert or Update an employee and delete an employee using DataGridView and LINQ to SQL combination.

Search an employee:


Search Employee

For searching a particular employee we can use

string SearchString = textBox1.Text;

EmployeeDataContext edc = new EmployeeDataContext();

var FilteredEmployees = from employee in edc.Employees
 where employee.FirstName.Contains(SearchString) ||
orderby employee.ID
select employee;
grdSearch.DataSource = FilteredEmployees;

Insert or Update Employee:


Insert Update Employee

For inserting or updating an employee we used a stored procedure which will be called using LINQ as

EmployeeDataContext edc = new EmployeeDataContext();
foreach (DataGridViewRow row in grdInsertUpdate.Rows)
if (!row.IsNewRow &&
row.Cells[1].Value.ToString().Trim() != string.Empty &&
row.Cells[2].Value.ToString().Trim() != string.Empty)
grdInsertUpdate.DataSource = edc.Employees;

Delete an Employee:


Delete Employee

Here we can give the option to delete the selected employee records. The stored procedure for deletion is called as follow

if (grdDelete.SelectedRows.Count > 0)
EmployeeDataContext edc = new EmployeeDataContext();
foreach (DataGridViewRow row in grdDelete.SelectedRows)
if (Convert.ToInt32(row.Cells[0].Value) > 0)
grdDelete.DataSource = edc.Employees;

and thats the sample for using LINQ to SQL. Framework 3.5 has made life much easier and faster.


Keep learing :)

Posted in LINQ To SQL | Tagged: , | 5 Comments »


Get every new post delivered to your Inbox.