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 C#
Server Intellect offers Windows Hosting Dedicated Servers at affordable prices. I'm very pleased!
Looking for the VB.NET 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> |
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> |
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.
Notice that the button has the onclick event handler. In the code-behind, we will add the following code to this event:
protected void butAdd_Click(object sender, EventArgs e)
{
string connectionString = ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ToString();
DataClassesDataContext context = new DataClassesDataContext(connectionString);
tblEmployee tblE = new tblEmployee();
tblE.name = txtName.Text;
tblE.position = txtPosition.Text;
context.tblEmployees.InsertOnSubmit(tblE);
context.SubmitChanges();
DataGrid1.DataBind(); } |
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 void butAdd_Click(object sender, EventArgs e)
{
if(txtName.Text != "" && txtPosition.Text != "") {
string connectionString = ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ToString();
DataClassesDataContext context = new DataClassesDataContext(connectionString);
tblEmployee tblE = 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."; } |
Now the data will only be added to the database if the textboxes are not blank.
Looking for the VB.NET version? Click Here!
Looking for more .NET Database Tutorials? Click Here!
I just signed up at Server Intellect and couldn't be more pleased with my Windows Server! Check it out and see for yourself.
|