This tutorial will show you how to write your own LINQ methods to enable adding, editing and deleting of data in a SQL database. VB 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 VB.NET 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:

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:

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:

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:

The entire code for the partial class is as follows:

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:

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.

Download Source Files