This tutorial will show how we can use Microsoft's new LINQ to add new records to a database, and how we can implement AJAX to make the application more dynamic and easier to use. The example was created with Visual Studio .NET 2008 - VB version.

Adding to Database using LINQ and AJAX in ASP.NET VB

This tutorial will show how we can use Microsoft's new LINQ to add new records to a database, and how we can implement AJAX to make the application more dynamic and easier to use. The example was created with Visual Studio .NET 2008 - VB version.

If you have Visual Studio.NET 2008, then LINQ capabilities are built-in.
We are going to create a sample database for this tutorial, and then display the contents in a DataGrid. We will also create TextBoxes and a Submit button to allow adding of new records to the database. First, we start by creating a new Web Project in Visual Studio - if 2005, we will need to choose the LINQ Template.

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.

Once we have our project opened up in Visual Studio, we will want to add a database - right-click the project in Solution Explorer and choose Add New Item, then choose SQL Server Database. If you are asked to place in the App_Data folder, choose Yes. Now we can goto Server Explorer and add a new table. We will create a very simple table of people, with three columns - id, name and city. The id column will be the Primary Key and identifier. Now save the table
Next, right-click the project again and choose Add New Item > LINQ to SQL Classes. If you are asked to place in the App_Code folder, click Yes. We are now faced with a design view for our DataContext Class. All we need to do here is to drag and drop from the Server Explorer the tables and stored procedures we will be using. This is for LINQ to build the relevant methods that we will be using to interact with our data. So we drag our table (tblPeople) from Server Explorer to the design view and save. Saving changes is very important. After we have saved, we can add some sample data to our database.

Now we can start building. We want to add a LinqDataSource to our ASPX page, and it should look something like this:

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!

Next, we can click the Smart Tag of the DataSource in the design view and choose to Configure Data Source. We choose from the DropDown the Data Context we just created a moment ago, and then move on to select the data we want to work with. For this example, we will select all (*). When we are done, we click Finish. Notice now that we get the option to Enable Delete, Insert and Update in the Smart Tag for the Data Source. We will choose to Enable Insert, which will generate the method for us.
We should now have something like this:

Now what we need is something to display this data. We are going to use a DataGrid, so let's add a DataGrid Control and set it to use the LinqDataSource. We will also add two TextBoxes and a Button to add new records to the database. Our ASPX page now looks like this:

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!

Notice that we have linked up the DataGrid to our LinqDataSource, and also added an event handler to the Submit Button. If we run this right now, the DataGrid will display the data from the database, but there would be no functionality. Let's add some functionality. The following would be the code-behind for the button onclick event:

This block of code is the LINQ functionality that has been added to ASP.NET 3.5

The first two lines get the connection string to the database from Web.config, so we can access the database. The connection string was added automatically when we created our DataSource. The third line instantiates a new instance of the table class that LINQ created for us, the columns of the table become attributes that we can reference through our instance of the table, and thus set with new data. The InsertOnSubmit line says that we want to commit this instance to the database when Submit is called. Then on the next line we call SubmitChanges to commit the changes to the database. Then finally, we update the DataGrid to display the changes.

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, to make the page more dynamic, we can add AJAX to it by adding a ScriptManager and an UpdatePanel. This is only for Visual Studio 2008. It can be done in 2005, but you need to install the AJAX Extensions for it. Our Web Application will now commit new records to the database quicker and not reload the whole page when the button is clicked. Our ASPX page will now look something like this: