LINQ to SQL in ASP.NET 3.5
Introduction
In this article, we will be looking at one flavor of Microsoft's new introduction to the .NET Framework, LINQ to SQL. LINQ (Language-Integrated Query) comes in a variety of forms, including LINQ to XML and LINQ to Objects. In this article, we will be looking at LINQ to SQL in Visual Studio.NET 2008. If you do not have 2008, you can download the LINQ Preview for VS.NET 2005 direct from Microsoft.
Server Intellect assists companies of all sizes with their hosting needs by offering fully configured server solutions coupled with proactive server management services. Server Intellect specializes in providing complete internet-ready server solutions backed by their expert 24/365 proactive support team.
In this article, we will create a LINQ to SQL Class to represent our SQL database, and will be dealing with LINQ statements instead of working directly with SQL.
What we will learn in this article:
- How to connect to and retrieve data from a SQL database using LINQ;
- How to delete data from a SQL database using LINQ;
- How to add data to a SQL database using LINQ.
Please Note:
LINQ is integrated into ASP.NET 3.5 and does not require any additional downloads. If you are using ASP.NET 2.0, certain extra steps may need to be taken that are not covered in this article.
Getting Started
Before we begin coding, we will need to start a new project and create our database. Open Visual Studio, and then goto File > New Website. Then choose your location and give the website a name - LINQtoSQL will do.
When you click Ok, Visual Studio will create the necessary files and we will eventually be provided with our ASPX page. To the right, we have our Solution Explorer. Right-click the App_Data folder and choose Add New Item, then choose SQL Server Database. Give it a name.
[Click to enlarge]
Next, open up the Server Explorer on the left (or View > Server Explorer), and right-click the Tables folder on the database we just added, then click Add New Table..
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!
We will then be provided with the table designer. Let's add three columns: id, name, city. We will set the data types as int, varchar(50), varchar(50) respectively. We will also make id the Primary Key and set its Identity Specification to Yes in the Column Properties at the bottom. This will make the id column the unique identifier for each record, which makes it easier for us to let .NET do all the work for us.
Now we can save the table, name it tblPeople, and then close it, which will bring us back to our ASPX page.
Now we have our database ready, we will add some sample data. Right-click the table in the Server Explorer and choose Show Table Data. Now let's add some sample data - notice we don't need to complete the id column as it is auto-generated:
[Click to enlarge]
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.
Now we have our database set up, we can build our LINQ to SQL Class. Right-click on your project in Solution Explorer, and choose Add New Item. Then from the list, choose LINQ to SQL Classes. Give it a name if you want, and when you click Ok it may ask you if you want to place in the App_Code folder. If so, choose Yes.
[Click to enlarge]
We should then be provided with the Object Relational Designer:
[Click to enlarge]
This is where LINQ creates its class based on our input. We can add our database tables and stored procedures to this designer and modify the relationships between the data, and then Visual Studio will create the class or classes based upon the layout.
For this example, we only have one table to work with, so the design is going to be very simple. From Server Explorer, drag the table onto the main area of the designer. Our table should be graphically represented like so:
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.
Now we can save the changes we made and close the designer.
Let's now drag a LinqDataSource control onto the ASPX page, and we can use the Smart Tag in design view to configure the data source. We want to use the DataClassesDataContext we just created:
[Click to enlarge]
[Click to enlarge]
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!
When we are done with the LinqDataSource Wizard, we should be presented with a new Smart Tag. Check Enable Delete and Enable Insert:
[Click to enlarge]
Now we have created our LINQ to SQL Class and linked it to our LinqDataSource, and enabled Deletions and Inserts. Notice that we only need to specify what we want, and Visual Studio will do the hard work for us, behind the scenes.
Next up, let's create a means for us to add new records to the database. We'll modify the ASPX page to include two textboxes and a submit button (remember, we don't need to input the id field, as it's auto-generated):
We can use a DataGrid to display the records from the database. We can either drag one on from the toolbox and use the Smart Tag to assign the datasource, or simply type it in ourselves:
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 if we right-click our Default.aspx in the Solution Explorer and choose View in Browser, we will be presented with an input form and a neat table displaying the data from the database. This is similar to using the SqlDataSource and DataGrid or GridView controls, but LINQ has so much more to offer and makes it a lot easier for us to communicate with and manipulate data, as we will see in the rest of this article.
[Click to enlarge]
At present, the web application is just displaying data - no real functionality. Let us now add the functionality to add new records to the database.
Go into the design view of the ASPX page and double-click on the button to create the event handler for the click event. We will use LINQ to add the contents of the textboxes into the SQL database. We use the connection string that was added to our Web.config when we created the LinqDataSource, and we use the LINQ to SQL Class to reference our database. The class can be seen as a representation of our database, which we can make changes to without making changes to the original database. This allows us to make multiple changes without making multiple calls to the database. We can then commit changes back to the database with the SubmitChanges() method, which is a member of the DataContext class.
{
DataClassesDataContext dataContext = new DataClassesDataContext(connectionString);
tblPeople tblP = new tblPeople();
tblP.name = txtName.Text;
tblP.city = txtCity.Text;
dataContext.tblPeoples.InsertOnSubmit(tblP);
dataContext.SubmitChanges();
DataGrid1.DataBind();
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.
Note that our database isn't modified until the SubmitChanges() method is called. This means we can make multiple changes without even making a call to the database, which reduces server load and processing time.
In our last line, we call the DataGrid to bind the data source again, so that we can see the newest data.
We can now move onto deletions. We want to add the ability to delete any record in the database. We will do this by populating a DropDownList with the names in the database, and then use a delete button to delete the selected record. We can use the same DataSource for the DropDown, so the code would look something like this: