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

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 :)

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

 
Follow

Get every new post delivered to your Inbox.