This tutorial will show you how to use LINQ to SQL in ASP.NET 3.5 to manipulate database data without using SQL Statements. VB version.
With ASP.NET 2.0 we were limited in our ways of connecting to a database, with SQL and Access databases the most popular to use. Not many developers enjoy working with SQL statements - having to translate between that and the scripting language. But now, with ASP.NET 3.5, we have been introduced to LINQ. LINQ provides us with a way of communicating directly with a database in the code - no need to translate into SQL. LINQ also comes in a variety of flavors, allowing us to connect to numerous data sources, not only databases.
If you're looking for a really good web host, try Server Intellect - we found the setup procedure and control panel, very easy to adapt to and their IT team is awesome!
In this tutorial, we will be using LINQ to SQL to interact with a SQL Server Database. We will add the ability to add new records to the database, as well as edit and delete existing records - all without using any SQL statements. To do this, we will need to create a LINQ to SQL Class to represent our database, but luckily for us, Visual Studio does the hard work for us.
Let's start by creating a new VB.NET web application project in Visual Studio .NET 2008, and then add a new database by right-clicking the App_Data folder in Solution Explorer and choosing Add New Item.. SQL Server Database. We will add one table, tblEmployees, with three columns - id, name, and position. We will make the id the Primary Key and also the Identity Specification (in the Properties Window).
Once we have done this, save the table and make sure the Connection String is in the Web.config:
|
<connectionStrings>
<add name="DatabaseConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
</connectionStrings>
|
Next, right-click the 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. Give the name Employees. This is the class that will represent our database and that we will use LINQ to interact with.
We should be provided with a design view of the Object Relation Designer. Drag onto the designer the table from Server Explorer, then save.
Once saved, we can close the class - VS will create the methods based on our graphical structure. Next, we will extend this class with our own methods, which we will use to add, edit and delete database data. Right-click the App_Code folder once more and choose Add New Item.. Class. Give it the name Employees, and we should be provided with a new, empty VB.NET class. Let's start with the Select statement. You will notice that LINQ statements are similar to SQL, but they interact with the class we created earlier, and then commit changes back to the database.
|
Public Shared Function [Select]() As IEnumerable(Of tblEmployee)
Dim db As New EmployeesDataContext()
Return db.tblEmployees.OrderBy(Function(e) e.id)
End Function
|
We used over 10 web hosting companies before we found Server Intellect. Their dedicated servers and add-ons were setup swiftly, in less than 24 hours. We were able to confirm our order over the phone. They respond to our inquiries within an hour. Server Intellect's customer support and assistance are the best we've ever experienced.
Instead of interacting directly with the database, we instantiate the DataContext class and make a selection of all the data, ordered by the id column using a Lambda Expression.
Now we can move onto the Insert method:
|
Public Shared Sub Insert(ByVal employeeToAdd As tblEmployee)
Dim db As New EmployeesDataContext()
db.tblEmployees.InsertOnSubmit(employeeToAdd)
db.SubmitChanges()
End Sub
|
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. All we need to tell it to do is to add, and what to add.
Now we can add the method that will allow us to update records using the built-in GridView update feature:
|
Public Shared Sub Update(ByVal oldEmployee As tblEmployee, ByVal newEmployee As tblEmployee)
Dim db As New EmployeesDataContext()
db.tblEmployees.Attach(newEmployee, oldEmployee)
db.SubmitChanges()
End Sub
|
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.
Using the Attach method of the table class, we are able to supply the data necessary to update the database, then SubmitChanges commits changes back to the database.
Finally, we can write the method to delete records from the database:
|
Public Shared Sub Delete(ByVal employeeToDelete As tblEmployee)
Dim db As New EmployeesDataContext()
db.tblEmployees.Attach(employeeToDelete)
db.tblEmployees.DeleteOnSubmit(employeeToDelete)
db.SubmitChanges()
End Sub
|
The entire code is as follows:
Imports Microsoft.VisualBasic
Imports System.Linq
Imports System.Data.Linq
Imports System.Collections.Generic
Partial Public Class Employees
Public Shared Sub Insert(ByVal employeeToAdd As tblEmployee)
Dim db As New EmployeesDataContext()
db.tblEmployees.InsertOnSubmit(employeeToAdd)
db.SubmitChanges()
End Sub
Public Shared Function [Select]() As IEnumerable(Of tblEmployee)
Dim db As New EmployeesDataContext()
Return db.tblEmployees.OrderBy(Function(e) e.id)
End Function
Public Shared Sub Update(ByVal oldEmployee As tblEmployee, ByVal newEmployee As tblEmployee)
Dim db As New EmployeesDataContext()
db.tblEmployees.Attach(newEmployee, oldEmployee)
db.SubmitChanges()
End Sub
Public Shared Sub Delete(ByVal employeeToDelete As tblEmployee)
Dim db As New EmployeesDataContext()
db.tblEmployees.Attach(employeeToDelete)
db.tblEmployees.DeleteOnSubmit(employeeToDelete)
db.SubmitChanges()
End Sub |
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.
Now that we are now done extending the LINQ to SQL Class, we can work on implementing the functionality into our ASPX page. Firstly, let's 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 method attributes to the ones we just created in the class. 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" />
|
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.
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 C# Version? Click Here!
Looking for more ASP.NET Tutorials? Click Here!