This tutorial will show how we can use Microsoft's new technology, LINQ, to easily add data to a database and how we can use AJAX to update the database in a more seamless way. This example was created in Visual Studio .NET 2008 and VB.NET
We migrated our web sites to Server Intellect over one weekend and the setup was so smooth that we were up and running right away. They assisted us with everything we needed to do for all of our applications. With Server Intellect's help, we were able to avoid any headaches!
Looking for the C# version? Click Here!
Looking for more .NET Database Tutorials? Click Here!
In this tutorial we will be taking a look at Microsoft's new baby, LINQ, and how we can use it to display data from a SQL Server Database and also how to add new recorsd to the database. We are going to create a sample database and use a DataGrid to display the data with a LinqDataSource Control and LINQ to SQL Classes. We will also add TextBoxes and a Button to enable us to add new records to the database, and then tidy it up with some AJAX to stop the whole page from posting back.
This example was created with Visual Studio .NET 2008. It can be recreated in 2005 with the LINQ Preview downloaded from Microsoft, but this tutorial is directed at Visual Studio .NET 2008.
The first thing that we will do is to create the SQL Server Database. We will be using one table with three columns - id, name, position. If you have your own database that you want to use, skip to the next step. To add a new database, right-click on your project in Solution Explorer and then choose Add New Item > SQL Server Database. Once we have designed the table, we will add some sample content - just a few records.
Once the database has been designed and is saved, with some sample records, we will need to add the LINQ to SQL Classes. We do this in the same way as we added the database, but instead, choose LINQ to SQL Classes. This will open up a design view, in which we will need to drag the tables we will be working with. The LINQ to SQL Classes will be for us to interact with our database. Visual Studio will create all the methods for us, so there is minimal coding for us to do. Once you have dragged onto the design area all the tables you need, make sure you save the Data Class.
Now we can start building our Web Application. We will add a LinqDataSource to our ASPX page from the Data Toolbox. It will look something like this:
<asp:LinqDataSource ID="LinqDataSource1" runat="server">
</asp:LinqDataSource> |
To configure the Data Source, click on the Smart Tag in design view and choose Configure Data Source. This will bring up a window with all of our Data Context objects. We choose the one that we just created, and click Next. Then we choose the columns and tables we wish to work with and click Finish. You will now see on the Smart Tag that there are more options - we can choose to Enable Delete, Insert and/or Update. We will need to enable Insert to allow us to add new records.
Next, we will add a DataGrid Control to display the records. Then we can click on its Smart Tag and set its Data Source to the one we just created. The ASPX page will now look something like this:
<form id="form1" runat="server">
<asp:DataGrid ID="DataGrid1" runat="server" DataSourceID="LinqDataSource1"
Width="326px"></asp:DataGrid> <br /> <asp:LinqDataSource ID="LinqDataSource1" runat="server"
ContextTypeName="DataClassesDataContext" EnableInsert="True"
TableName="tblEmployees"> </asp:LinqDataSource> |
Try Server Intellect for Windows Server Hosting. Quality and Quantity!
This page will now display the records in the DataGrid if we run it, but we want more functionality than that. We will add two text boxes and a submit button to allow additions to the database. The ASPX page will then look like this:
<form id="form1" runat="server">
<table>
<tr><th colspan="2">Add New Employee</th></tr> <tr><td>Name:</td><td><asp:TextBox ID="txtName" runat="server"></asp:TextBox></td></tr> <tr><td>Position:</td><td><asp:TextBox ID="txtPosition" runat="server"></asp:TextBox></td></tr> <tr><td colspan="2"><asp:Button ID="butAdd" runat="server" Text="Add to DB"
onclick="butAdd_Click" /></td></tr> </table> <asp:DataGrid ID="DataGrid1" runat="server" DataSourceID="LinqDataSource1"
Width="326px"></asp:DataGrid> <br /> <asp:LinqDataSource ID="LinqDataSource1" runat="server"
ContextTypeName="DataClassesDataContext" EnableInsert="True"
TableName="tblEmployees"> </asp:LinqDataSource> |
Notice that the button has the onclick event handler. In the code-behind, we will add the following code to this event:
| Protected Sub butAdd_Click(ByVal sender As Object, ByVal e As EventArgs)
Dim connectionString As String = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString()
Dim context As New DataClassesDataContext(connectionString)
Dim tblE As New tblEmployee()
tblE.name = txtName.Text
tblE.position = txtPosition.Text
context.tblEmployees.InsertOnSubmit(tblE)
context.SubmitChanges()
DataGrid1.DataBind() End Sub |
Now if we run the application, we will be allowed to make additions to the database. But we can extend it further by using validation and AJAX. To make the addition process more dynamic, we can add a ScriptManager and an UpdatePanel like so:
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server" /> <asp:UpdatePanel ID="UpdatePanel1" runat="server">
<Triggers>
<asp:AsyncPostBackTrigger ControlID="butAdd" EventName="Click" /> </Triggers> <ContentTemplate>
<table>
<tr><th colspan="2">Add New Employee</th></tr> <tr><td>Name:</td><td><asp:TextBox ID="txtName" runat="server"></asp:TextBox></td></tr> <tr><td>Position:</td><td><asp:TextBox ID="txtPosition" runat="server"></asp:TextBox></td></tr> <tr><td colspan="2"><asp:Button ID="butAdd" runat="server" Text="Add to DB"
onclick="butAdd_Click" /></td></tr> </table> <asp:DataGrid ID="DataGrid1" runat="server" DataSourceID="LinqDataSource1"
Width="326px"></asp:DataGrid> <br /> <asp:LinqDataSource ID="LinqDataSource1" runat="server"
ContextTypeName="DataClassesDataContext" EnableInsert="True"
TableName="tblEmployees"> </asp:LinqDataSource> </ContentTemplate> </asp:UpdatePanel> </form> |
This will make the DataGrid update without the whole page reloading when we add a new entry to the database. To add some simple validation, we can modify the code-behind like so:
Protected Sub butAdd_Click(ByVal sender As Object, ByVal e As EventArgs)
If txtName.Text <> "" AndAlso txtPosition.Text <> "" Then
Dim connectionString As String = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString()
Dim context As New DataClassesDataContext(connectionString)
Dim tblE As New tblEmployee()
tblE.name = txtName.Text
tblE.position = txtPosition.Text
context.tblEmployees.InsertOnSubmit(tblE)
context.SubmitChanges()
DataGrid1.DataBind() Else
txtName.Text = "Please enter some text." End If |
Now the data will only be added to the database if the textboxes are not blank.
Looking for the C# version? Click Here!
Looking for more .NET Database Tutorials? Click Here!
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.
|