So what’s the simplest way to build a Web-based interface for a basic database table? In this article I’ll provide a fully working example with a minimum of code.
This is a useful feature to be able to put together, especially when you are catering to power users who should be able to help maintain their own data without needing to call you each time.
In the past years ASP.NET Webforms have been much maligned, but for Rapid Application Development (RAD), they still are an amazing tool. Case in point: you have a simple database table that you want to quickly build a Web UI for. There are naturally many options, but for absolute speed of development, simplicity, non-proprietary-ness, and flexibility, few options can match hooking up a GridView via a SQLDataSource to your table.
Non-proprietary-ness?
As an aside, you may be wondering what I mean by non-proprietary-ness. Well, I mean that I do not like using third party components built by companies that might not be around in a year, or that might drop support for the component at any time. I definitely prefer using a solution such as the GridView component, which are built and supported by Microsoft which is a company that definitely isn’t a fly-by-night shop that will be gone tomorrow.
Explaining the Example
I’m providing a bare-bones example in the code below of attaching a GridView to a SQL Server table with full Add/Edit/Delete functionality.
In this case we have an imaginary table consisting of an identity column and two information columns (First and Last name). We will want our Web page to key off the identity column as a hidden value, and we will want to make the First and Last name columns editable through text-entry fields.
Here is the T-SQL CREATE statement example table that we will set our GridView control to bind to:
CREATE TABLE [dbo].[TestTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [FirstName] [varchar](500) NULL, [LastName] [varchar](500) NULL )
Setting up your Web.config
As part of setting up the GridView control, we’ll have to set up a Visual Studio Web site with a Web.config file that points at our SQL Server database table. Here is an example of setting the connection string in the Web.config
<configuration> <connectionStrings> <add name="MySQLConn" connectionString="uid=myUser;pwd=myPwd;server=DBServerName;database=DBName;Connect Timeout=60"/>
The Full C# Example Page
Once we have gone through the setup process for our Web site, it’s just a matter of putting together a C# WebForm page. As part of the setup of the page, we will use a SQLDataSource control and a GridView control that is attached to the SQLDataSource control.
We can add our Add/Delete/Update SQL to the SQLDataSource control and point our DataGrid to it. A quirk of the DataGrid control is that you will need to set up a separate row for adding new information in the footer of the grid. Furthermore if the grid has no data, then you’ll need to set up fields to add data in a DetailsView control that you embed in the EmptyDataTemplate section of your GridView.
That’s about as complex as it gets though. Once you have done your basic set-up, you are pretty much ready to run your page. I hope this example is useful to you!
<%@ Page Language="C#" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
protected void lbInsertData_Click(object sender, EventArgs e)
{
SqlDataSource1.Insert();
}
protected void SqlDataSource1_Inserting(object sender, SqlDataSourceCommandEventArgs e)
{
if (gvExampleGrid.Rows.Count > 0)
{
String strFirstName = ((TextBox)gvExampleGrid.FooterRow.FindControl("txtFirstNameA")).Text;
String strLastName = ((TextBox)gvExampleGrid.FooterRow.FindControl("txtLastNameA")).Text;
e.Command.Parameters["@FirstName"].Value = strFirstName;
e.Command.Parameters["@LastName"].Value = strLastName;
}
}
protected void Page_Load(object sender, EventArgs e)
{
Label1.Text = (gvExampleGrid.Rows.Count).ToString();
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<asp:Label ID="Label1" runat="server" Text="My Title Here" Font-Size="X-Large"></asp:Label>
<asp:GridView ID="gvExampleGrid" runat="server"
AutoGenerateColumns="False" DataKeyNames="Id"
DataSourceID="SqlDataSource1" ShowFooter="True">
<EmptyDataTemplate>
<asp:DetailsView ID="dvExampleBlankRow" runat="server" Width="450px" DefaultMode="Insert" DataSourceID="SqlDataSource1" AutoGenerateRows="false">
<Fields>
<asp:TemplateField>
<InsertItemTemplate>
<asp:LinkButton ID="lbInsertNewRow" runat="server" CommandName="Insert" Text="Add"></asp:LinkButton>
</InsertItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="FirstName" SortExpression="FirstName">
<InsertItemTemplate>
<asp:TextBox ID="txtFirstNameA" runat="server" Text='<%# Bind("FirstName") %>'></asp:TextBox>
</InsertItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="LastName" SortExpression="LastName">
<InsertItemTemplate>
<asp:TextBox ID="txtLastNameA" runat="server" Text='<%# Bind("LastName") %>' Width="100"></asp:TextBox>
</InsertItemTemplate>
</asp:TemplateField>
</Fields>
</asp:DetailsView>
</EmptyDataTemplate>
<Columns>
<asp:TemplateField ShowHeader="False">
<EditItemTemplate>
<asp:LinkButton ID="LinkButton1" runat="server" CommandName="Update" Text="Update" ></asp:LinkButton>
<asp:LinkButton ID="LinkButtonCancel2b" runat="server" CommandName="Cancel" Text="Cancel"></asp:LinkButton>
</EditItemTemplate>
<ItemTemplate>
<asp:LinkButton ID="LinkButton2" runat="server" CommandName="Edit" Text="Edit" Width="60"></asp:LinkButton>
</ItemTemplate>
<FooterTemplate>
<asp:LinkButton ID="lbInsertData" runat="server" CommandName="Insert"
OnClick="lbInsertData_Click">Add</asp:LinkButton>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField ShowHeader="False">
<ItemTemplate>
<asp:LinkButton ID="LinkButtonDelete2" runat="server" CommandName="Delete" Text="Delete"></asp:LinkButton>
</ItemTemplate>
<FooterTemplate>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="ID" InsertVisible="False" SortExpression="ID"
Visible="False">
<EditItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("ID") %>'></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("ID") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="First Name" SortExpression="FirstName" Visible="True">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("FirstName") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("FirstName") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtFirstNameA" runat="server" Text='<%# Bind("FirstName") %>' Width="100px"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Last Name" SortExpression="LastName">
<EditItemTemplate>
<asp:TextBox ID="tbLastNameUpdate" runat="server" Text='<%# Bind("LastName") %>' Width="100px"></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("LastName") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="txtLastNameA" runat="server" Text='<%# Bind("LastName") %>' Width="100px"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MySQLConn %>"
SelectCommand="SELECT [ID],[FirstName],[LastName] FROM [TestTable]"
UpdateCommand="UPDATE [TestTable] SET [FirstName]=@FirstName,[LastName]=@LastName WHERE [ID]=@ID"
InsertCommand="INSERT INTO [TestTable] (FirstName,LastName) VALUES (@FirstName,@LastName)"
DeleteCommand="DELETE FROM [TestTable] WHERE [ID]=@ID"
OnInserting="SqlDataSource1_Inserting">
<UpdateParameters>
<asp:Parameter Name="FirstName" />
<asp:Parameter Name="LastName" />
<asp:Parameter Name="ID" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="FirstName" />
<asp:Parameter Name="LastName" />
</InsertParameters>
<DeleteParameters>
<asp:Parameter Name="ID" />
</DeleteParameters>
</asp:SqlDataSource>
</form>
</body>
</html>
Justin Cooney 