Linq to sql - crud operations


This Article is all about working with CRUD (CREATE-READ-UPDATE-DELETE) operations using LINQ-to-SQL (Language Integrated Query to SQL classes). I will be taking you over step-by-step to implement this feature and also will cover on invoking Stored Procedures (with and without parameter) through LINQ-to-SQL.

Overview on LINQ-to-SQL



1. It is Object/relational mapping implementation.

2. Class operations are translated to commands such as
Create-Read-Update-Delete (Termed as CRUD)

3. This model is called Data Context Model.

4. Data context as only plain containers.

5. Developers operate on Data context using LINQ

6. Classes in data context are partial. It can be extended when required.

7. All we need to tell them how we required the data.

8. Data Context takes care of
(i) Managing Connection
(ii) Executing and translating query
(iii) Tracking the changes

9. Due to this features it reduces the line of code.

Agenda



Now let’s step in to the code. All we are going to design two FORMS in windows application using c# code. Both forms will be consuming the feature of LINQ-to-SQL where we will be implementing our CRUD operations


FORM1
CRUD_LINQ_TO_SQL_1

FORM2
CRUD-LINQ-TO-SQL-2

Database Changes



Create the following Table and Stored Procedures in SqlServer

Table

create table employee
(
EMP_ID int primary key,
EMP_NAME varchar(1000),
EMP_DEPT varchar(1000),
EMP_LOCATION varchar(1000)
)



Stored Procedure


CREATE PROCEDURE sp_GetDepartment
AS
BEGIN
SET NOCOUNT ON;
SELECT distinct(EMP_DEPT) from employee;
END



CREATE PROCEDURE sp_GetEmpDetail (@Empid int)
AS
BEGIN
SET NOCOUNT ON;
SELECT * from employee where EMP_ID=@Empid;
END


This Project "CRUD_LINQ" contains 3 files

1. employee.dbml
2. Form1.cs
3. Form2.cs


Will go one by one in creating and explaining them

Steps for creating working environment


Step-1

Open a new project.
File --> New --> Project


CRUD-LINQ-TO-SQL_3

Step-2
Select "Windows Forms Application" under Visual C#

CRUD-LINQ-TO-SQL_4

Steps for Developing employee.dbml



Step-1

Right click on the
Project --> Add --> new item --> LINQ-to-SQL Classes
Name it "employee.dbml"


CRUD-LINQ-TO-SQL_6


Step-2

Now we need to Drag and Drop the Tables and Procedures from the Server Explorer tab to "employee.dbml" file

Click on the Menu --> View --> Server Explorer
Right Click on the Data Connections to add connection


CRUD-LINQ-TO-SQL_DATA_CONNECTION

Fill in the details highlighted in Red Color to establish database connection and finally click on OK button.

CRUD_LINQ_TO_SQL_NEW_CONNECTION

Once the connection is established successfully you will get a window mentioned in the below image.
Then drag and drop the tables and procedures that you have created.
Those are highlighted in Red color.

CRUD-LINQ-TO-SQL_SERVER_EXPLORER


Let us explore more on this file employeed.dbml.

When you expand "employeed.dbml” which is present in the project explorer
You will find out that "employee.designer.cs” a class file created.

If you open "employeed.dbml" file you will find out 4 partial classes created.

1. employeeDataContext - As said earlier . This class takes cares of establishing connection,
Query Handling and Tracking the changes

2. employee - This class contains the details of the table.

3. sp_GetDepartmentResult
4. sp_GetEmpDetailResult
3 and 4 classes contains the details of the stored procedure which we dragged and dropped in the employeed.dbml file. If we are going to add one more stored procedure that will also be converted into class.

As a whole the LINQ code depends on this "employee.designer.cs" class file to perform data operations

Steps for Developing FORM1



Step-3
Add new Form1 to your project

CRUD-LINQ-TO-SQL_5

Design the form like the image below.

CRUD_LINQ_TO_SQL_1

Now will go through the functionality of each button

SAVE
As the name indicated we will be inserting new record through this feature.
Code Explained below.


employeeDataContext ed;
public Form1()
{
InitializeComponent();
ed = new employeeDataContext(); // Creating object for DataContext
}

/// LINQ CODE TO ADD A RECORD INTO THE TABLE.
private void btn_Save_Click(object sender, EventArgs e)
{

employee empTable = new employee(); // calling the employee class in the employee.designer.cs file
empTable.EMP_ID = Int32.Parse(txtEmpId.Text); // Mapping the data to the column
empTable.EMP_NAME = txtEmpName.Text.ToString(); // Mapping the data to the column
empTable.EMP_DEPT = txtEmpDept.Text.ToString(); // Mapping the data to the column
empTable.EMP_LOCATION = txtEmpLocation.Text.ToString(); // Mapping the data to the column
ed.employees.InsertOnSubmit(empTable); // Inserting the record

// If the below code is not given your data changes will not be reflected
ed.SubmitChanges(); // This is very mandatory to get changes reflected in the database.
MessageBox.Show("Record Inserted Successfully");
}


UPDATE

LINQ code to update record.For a given employee id.



employeeDataContext ed;
public Form1()
{
InitializeComponent();
ed = new employeeDataContext();// Creating object for DataContext
}
/// CODE TO UPDATE RECORD IN THE TABLE

private void btnUpdate_Click(object sender, EventArgs e)
{
int empid = Int32.Parse(txtEmpId.Text);
employee emp = ed.employees.Single(e1 => e1.EMP_ID == empid); //To Retrieve one single record from the database for the given empid.

emp.EMP_ID = Int32.Parse(txtEmpId.Text); // Mapping the data to the column in the table
emp.EMP_NAME = txtEmpName.Text.ToString(); // Mapping the data to the column in the table
emp.EMP_DEPT = txtEmpDept.Text.ToString(); // Mapping the data to the column in the table
emp.EMP_LOCATION = txtEmpLocation.Text.ToString(); // Mapping the data to the column in the table
ed.SubmitChanges();
MessageBox.Show("Record Updated");
}


DELETE

LINQ Code to delete record form the table



employeeDataContext ed;
public Form1()
{
InitializeComponent();
ed = new employeeDataContext();// Creating object for DataContext
}

/// CODE TO DELETE RECORD FROM THE TABLE

private void btnDelete_Click(object sender, EventArgs e)
{
int empid = Int32.Parse(txtEmpId.Text);
//Here "ed" is the employeeDataContext
employee emp = ed.employees.Single(e1 => e1.EMP_ID == empid); //To Retrieve one single record from the database for the given empid.

if (emp != null)
{
ed.employees.DeleteOnSubmit(emp);
ed.SubmitChanges();
MessageBox.Show("Record Deleted");
}

}


FIND



employeeDataContext ed;
public Form1()
{
InitializeComponent();
ed = new employeeDataContext();// Creating object for DataContext
}
/// CODE TO FIND A RECORD IN THE TABLE

private void btnFind_Click(object sender, EventArgs e)
{
int empid = Int32.Parse(txtEmpId.Text);

var empresult = from data in ed.employees // Ling Query to retrive data from table
where data.EMP_ID == empid
select data;

// Data Binding
foreach (var emp in empresult)
{
txtEmpName.Text = emp.EMP_NAME.ToString(); // Retriving column data and binding to the textbox
txtEmpLocation.Text = emp.EMP_LOCATION.ToString(); // Retriving column data and binding to the textbox
txtEmpDept.Text = emp.EMP_DEPT.ToString(); // Retriving column data and binding to the textbox

}
}



Steps for creating Form2



Design the form like the image below.

CRUD-LINQ-TO-SQL-2



RETRIEVE RECORDS WITH OUT USING STORED PROC



employeeDataContext ed;
public Form2()
{
InitializeComponent();
ed = new employeeDataContext();// Creating object for DataContext
}
/// CRUD_LINQ - CODE TO RETRIEVE RECORDS WITH OUT USING STORED PROC AND BINDING IT TO DATAGRID

private void btnFetchRecords_Click(object sender, EventArgs e)
{
var result = from emp in ed.employees
select emp;

dataGridView1.DataSource = result.ToList();
}



RETRIEVE RECORDS WITH USING STORED PROCEDURE WITH PARAMETER


employeeDataContext ed;
public Form2()
{
InitializeComponent();
ed = new employeeDataContext();// Creating object for DataContext
}

/// CRUD_LINQ - CODE TO RETRIEVE RECORDS USING STORED PROC WITH PARAMETER

private void btnStoredProcWithParameter_Click(object sender, EventArgs e)
{
dataGridView1.DataSource = ed.sp_GetEmpDetail(6);
}


RETRIEVE RECORDS WITH OUT USING STORED PROCEDURE WITHOUT PARAMETER


employeeDataContext ed;
public Form2()
{
InitializeComponent();
ed = new employeeDataContext();// Creating object for DataContext
}
/// CRUD_LINQ - CODE TO RETRIEVE RECORDS USING STORED PROC WITHOUT PARAMETER

private void btnStoredProc_Click(object sender, EventArgs e)
{
//Simplest Way
dataGridView1.DataSource = ed.sp_GetDepartment(); // Calling the stored procedure
//or
//Other way of fetching the record
IEnumerable query =
ed.sp_GetDepartment().ToList();

MessageBox.Show("Total records : " + query.Count().ToString());

dataGridView1.DataSource = query;
}



I have attached the code for your practice. All you need to change the connection string based on your system configuration.


Attachments

  • Download - Project CRUD_LINQ (44833-195850-Download-Project-CRUD-LINQ.zip)
  • Comments



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: