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

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)

Linq to sql - crud operations

Overview on LINQ-to-SQL

Agenda

Database Changes

Steps for creating working environment

Steps for Developing employee.dbml

Steps for Developing FORM1

Steps for creating Form2

Attachments

Comments

About dotnetspider.com

Quick Links

General

Services