CRUD Operations Using LINQ To SQL

Kindly refer to my previous article to learn about LINQ To SQL; we will work on the same sample. Download the complete code of this article.

So, let’s start.

Flip to Visual Studio.  We will have four button controls on the Webform - Getdata, Insert, Update, and Delete. So, let’s see how to achieve this. The code loads male employees only at the moment . We want to display all the employee details, so will modify that code.


Now, let’s extract this piece of code in its own method.


So, this will display the details of all Employees. Include a button control on Webform and name it GetData. Double click on the eventhandler and let’s call the Getdata() method in the button click event.



Now, let’s run the code and see the output.



When I click on GetData, the details are populated on the form.
 
Now, let’s include another button and name it Insert. Double click on the Insert button to generate the eventhandler. Now, we have to include samplecontxt because that is the entry point for our database. So, let’s wrap inside using clause. Let’s create the instance of an employee object.



Now, we don’t have to supply the value for ID column because in the employee table, ID column is auto-incremented. For identity columns, the SQL Server is automatically going to compute the value when we insert any new record. So, we don’t have to supply any value for this property. Let’s apply FirstName, LastName, Gender, and Salary.

 
The "dbcontext.Employees" object will have the collection of all Employees. In that collection, we will add employee object. For this, we add InsertOnsubmit method and we will pass newEmployee object in our parameter. So, this call is going to insert this object into this collection.
 
But, we want it to be saved in database table. In order to do that, we want to call the submitchanges method. We will refresh the GridView Control. When we submit or insert the data in the database table, we call this Getdata method. Getdata method loads all the employee details. So, with all these changes, let's run the solution.



As you can see, when I clicked on "Insert" button, Gopal has been added to the employee list . Now, let's check the DB.



So, in our DB, the data has been inserted successfully.
 
Now, let’s add button for "Update". We will update Gopal's salary. Add a button and name it as "Update." Double click on the Update button to generate the event handler.



We have used Singleordefault method. We need to specify the ID of the employee that we had specified using lambda expression. We want to change the salary, so employee.Salary= 65000 and submit the changes to our DB table. Now, let’s run the app and let's update the entry.


As you can see, Gopal's salary has been updated successfully.
 
Now again, let's add a button to our webform, name it "Delete", and double click on the button to generate the event handler. Now, we will be deleting the entry.




So, the only change here is we are using DeleteOnSubmit method and passing the employee object. So, let’s run this query and delete that employee which we had added before.


As you can see from the above screenshot, we have successfully deleted the employee details. Let’s quickly check the DB table.



So, the Gopal row has disappeared from the table as well.

Conclusion

This was all about Insert, Update and Delete operations using LINQ to SQL. In the next article, we will see how to view LINQ to SQL generated SQL queries.