Naveen's Weblog

Bridge to future

LINQ To SQL – Employee Sample

Posted by codingsense on December 16, 2008


Introduction:

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.

linqtosqlcreateproject

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.

linqtosqladdnewlinqtosql

New .dbml File

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

linqtosqlemployeedbmlblankscreen

.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.

linqtosqldragstoredprocedures

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:

linqtosqlselectemployee

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) ||
employee.LastName.Contains(SearchString)
orderby employee.ID
select employee;
grdSearch.DataSource = FilteredEmployees;

Insert or Update Employee:

linqtosqlinsertupdate

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)
{
edc.USP_Employee_InsertOrUpdate(
Convert.ToInt32(row.Cells[0].Value),
row.Cells[1].Value.ToString(),
row.Cells[2].Value.ToString());
}
}
grdInsertUpdate.DataSource = edc.Employees;

Delete an Employee:

linqtosqldelete

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)
{
edc.USP_Employee_Delete(Convert.ToInt32(row.Cells[0].Value));
}
}
grdDelete.DataSource = edc.Employees;
}

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

References:
http://msdn.microsoft.com/en-us/library/bb425822.aspx

Keep learing 🙂

Advertisements

5 Responses to “LINQ To SQL – Employee Sample”

  1. Chetan said

    Hi,

    Seems that there is copy-paste error in Form1.cs.

    On Line 56, it should be grdDelete instead of grdInsertUpdate.

    Rgds,
    Chetan

    • codingsense said

      Thanks Chetan,

      Yes i checked it, there was a copy paste error :).
      But it wont effect the functionality, since its for setting readonly property for the grid.

      Thanks,
      Naveen Prabhu
      🙂

  2. Raju Dutta said

    It’s very poor sample about LINQ to SQL and also don’t have any clearance.

  3. Gajin said

    Very useful article this one…
    Thanx….

  4. Gajin said

    Very useful article..
    thanx.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: