This tutorial will show you how to write your own LINQ methods to enable adding, editing and deleting of data in a SQL database. C# version.
ASP.NET provides ample ways for us to connect to a database and manipulate database data. ASP.NET with VS.NET helps greatly when creating a system to allow adding, editing and deleting of database data. However, there are times when you want more control over this than the built-in controls and methods provide. Luckily, ASP.NET also provides us with the ability to customize the way we connect, and interact with our database(s).
With Microsoft's introduction of LINQ to the .NET Framework, they aim to create a unified way of connecting and interacting with data sources - including databases, XML, and even regular objects you'd not consider a data source.
In this tutorial, you will learn how to use LINQ to SQL Classes and develop your own methods that will allow adding new records, editing existing records and deleting records from the SQL database. This functionality comes built into the ASP.NET Framework, and we can achieve such results by writing no code whatsoever, but this example will give you a look behind LINQ and a better understanding of how we can customize our data access.
In this example, we will be using a SQL database, LINQ to SQL Classes, and FormView, GridView and ObjectDataSource controls.
We will be using a GridView to display, edit and delete database data, a FormView to add new records, and an ObjectDataSource to represent our database.
Our first step will be to create the database. Once you have a new C# web project opened in Visual Studio, right-click the App_Data folder in Solution Explorer and choose Add New Item.. SQL Server Database. You can leave the name as Database.mdf
Next, we will create one table with three columns - id, name and position. We will name the table tblEmployees, and once we have it set up, we can add data to it by right-clicking the table in Server Explorer and choosing Show Table Data.
Now we have the database ready, we should add the LINQ to SQL Classes to our project. This will represent our database so that we can then interact with the class instead of directly with the database. This means we can make multiple changes and then submit to the database in one call, instead of making multiple calls.
We do this by right-clicking our project in Solution Explorer and choose Add ASP.NET Folder > App_Code. Then right-click the App_Code folder and choose Add New Item.. LINQ to SQL Classes, name it Employees. This is our DataContext. Once named, you should be presented with the Object Relation Designer, which is used to create a graphical representation of our database. We simply drag from the Server Explorer the table(s) we will be working with; just one in this example; then we will save the changes. Visual Studio will then create the LINQ to SQL class to represent our database, doing all the work for us.
Now we want to extend this class with methods for adding, editing and deleting, which we are going to write ourselves. Right-click the App_Code folder and choose Add New Item.. Class.
We will start with the select method, to retrieve all the data within the database. The GridView will use this method to display data:
public static IEnumerable<tblEmployee> Select()
{
EmployeesDataContext db = new EmployeesDataContext();
return db.tblEmployees.OrderBy(e=>e.id); } |
We chose Server Intellect for its dedicated servers, for our web hosting. They have managed to handle virtually everything for us, from start to finish. And their customer service is stellar.
Notice the EmployeesDataContext refers to our LINQ to SQL class, and is a data type. We use the class to return data, instead of interacting directly with the database.
Next, we add the method to allow us to insert new records to the database:
public static void Insert(tblEmployee employeeToAdd)
{
EmployeesDataContext db = new EmployeesDataContext();
db.tblEmployees.InsertOnSubmit(employeeToAdd);
db.SubmitChanges(); } |
We moved our web sites to Server Intellect and have found them to be incredibly professional. Their setup is very easy and we were up and running in no time.
This method will be used by the FormView control to add a new record to the database. Using LINQ makes it so simple to do this, as it manages our database connection and data types, etc.
Now we can add the method that will allow us to update records using the built-in GridView update feature:
public static void Update(tblEmployee oldEmployee, tblEmployee newEmployee)
{
EmployeesDataContext db = new EmployeesDataContext();
db.tblEmployees.Attach(newEmployee,oldEmployee);
db.SubmitChanges(); } |
Using the Attach method of the table class, we are able to supply the data necessary to update the database. SubmitChanges commits changes back to the database.
Finally, we can add the method to delete records from the database:
public static void Delete(tblEmployee employeeToDelete)
{
EmployeesDataContext db = new EmployeesDataContext();
db.tblEmployees.Attach(employeeToDelete);
db.tblEmployees.DeleteOnSubmit(employeeToDelete);
db.SubmitChanges(); } |
Yes, it is possible to find a good web host. Sometimes it takes a while. After trying several, we went with Server Intellect and have been very happy. They are the most professional, customer service friendly and technically knowledgeable host we've found so far.
The entire code for the partial class is as follows:
using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Collections.Generic;
using System.Data.Linq;
/// <summary>
/// Extension of Employees.dbml
/// </summary>
public partial class Employees
{
public static void Insert(tblEmployee employeeToAdd) {
EmployeesDataContext db = new EmployeesDataContext();
db.tblEmployees.InsertOnSubmit(employeeToAdd);
db.SubmitChanges(); } public static IEnumerable<tblEmployee> Select() {
EmployeesDataContext db = new EmployeesDataContext();
return db.tblEmployees.OrderBy(e=>e.id); } public static void Update(tblEmployee oldEmployee, tblEmployee newEmployee) {
EmployeesDataContext db = new EmployeesDataContext();
db.tblEmployees.Attach(newEmployee,oldEmployee);
db.SubmitChanges(); } public static void Delete(tblEmployee employeeToDelete) {
EmployeesDataContext db = new EmployeesDataContext();
db.tblEmployees.Attach(employeeToDelete);
db.tblEmployees.DeleteOnSubmit(employeeToDelete);
db.SubmitChanges(); } |
We are using Server Intellect and have found that by far, they are the most friendly, responsive, and knowledgeable support team we've ever dealt with!
We are now done extending the LINQ to SQL Class, and can now work on implementing this into our ASPX page. First, we will add the controls we will be using:
<form id="form1" runat="server">
<asp:FormView ID="FormView1" runat="server" DefaultMode="Insert" DataSourceID="ODS1">
<InsertItemTemplate>
</InsertItemTemplate> </asp:FormView> <asp:GridView ID="GridView1" runat="server" DataSourceID="ODS1" Width="400px" /> <asp:ObjectDataSource ID="ODS1" runat="server" /> |
We set both controls DataSourceID to that of the ObjectDataSource, but we also need to set the methods to use the ones we just created. We do this by assigning the TypeName of the DataSource to our partial class, and then the methods:
| <asp:ObjectDataSource ID="ODS1" runat="server" TypeName="Employees" DataObjectTypeName="tblEmployee"
SelectMethod="Select" InsertMethod="Insert" UpdateMethod="Update" DeleteMethod="Delete"
ConflictDetection="CompareAllValues" OldValuesParameterFormatString="oldEmployee" /> |
If you're ever in the market for some great Windows web hosting, try Server Intellect. We have been very pleased with their services and most importantly, technical support.
Finally, we build the template of our FormView and also enable the Edit and Delete buttons on our GridView:
<form id="form1" runat="server">
<asp:FormView ID="FormView1" runat="server" DefaultMode="Insert" DataSourceID="ODS1">
<InsertItemTemplate>
<asp:Label ID="lblName" runat="server" Text="Employee Name:" AssociatedControlID="txtName" /><br /> <asp:TextBox ID="txtName" runat="server" Text='<%# Bind("name") %>' /><br /> <asp:Label ID="lblPosition" runat="server" Text="Position:" AssociatedControlID="txtPosition" /><br /> <asp:TextBox ID="txtPosition" runat="server" Text='<%# Bind("position") %>' /><br /> <asp:Button ID="btnInsert" runat="server" Text="Add" CommandName="Insert" /> </InsertItemTemplate> </asp:FormView> <asp:GridView ID="GridView1" runat="server" DataSourceID="ODS1" Width="400px"
DataKeyNames="id" AutoGenerateEditButton="true" AutoGenerateDeleteButton="true" /> <asp:ObjectDataSource ID="ODS1" runat="server" TypeName="Employees" DataObjectTypeName="tblEmployee"
SelectMethod="Select" InsertMethod="Insert" UpdateMethod="Update" DeleteMethod="Delete"
ConflictDetection="CompareAllValues" OldValuesParameterFormatString="oldEmployee" /> |
Now when we run this web application, we will be presented with a table of data from the database, and the ability to add new records, edit existing records and also delete records from the database. All this functionality without dealing directly with SQL.
Looking for the VB.NET Version? Click Here!
Looking for more .NET Tutorials? Click Here!