Build a Script-Only ASP.NET 2.0 Data Editing Page
By Peter Bromberg
Printer Friendly Version
|  |
Shows how to build a script-only database edit page with the GridView some textboxes and buttons, and the SqlDataSource control. No compilation, all you need is the connection string in web.config! |
Webmasters and developers who work with a shared hosting service for their sites know that while they usually provide access to your database via EnterpriseManager or Sql Server Management studio, often times it is useful to have your own separate data access page that allows you to access your database for minor work or checking things, and to be able to do this even from behind a firewall.
While there are some pretty good solutions for this (e.g., the free "Web Data Administrator" reference app), all you really need is a simple page that you can drop into your /admin protected web folder and access from anywhere, without having to rebuild your app and redeploy it.
The page I've put together here requires no building; it is a script-only page with no codebehind file, and a minimal amount of script as well, thanks to the GridView control and the SqlDataSource control for ASP.NET 2.0.
Here's the code - all of it!
<%@ Page Language="C#" %>
<%@ Import Namespace ="System.Data.SqlClient" %>
<%@ Import Namespace ="System.Data" %>
<script language="C#" runat=server>
void Page_Load(object sender, EventArgs e)
{
// "Poor man's Security" with your Forms Auth:
/*
if( !User.IsInRole("admin") )
{
Response.Write("Unauthorized Access Denied.");
Response.End();
}
*/
}
protected void Button2_Click(object sender, EventArgs e)
{
int res = 0;
SqlConnection cn =
new SqlConnection(ConfigurationManager.ConnectionStrings["db"].ConnectionString);
SqlCommand cmd = new SqlCommand(this.TextBox4.Text, cn);
cn.Open();
try
{
if (TextBox4.Text.ToUpper().IndexOf("SELECT") > -1)
{
SqlDataSource1.SelectCommand = TextBox4.Text;
GridView1.DataBind();
}
else
{
res = cmd.ExecuteNonQuery();
lblMessage.Text = res.ToString() + " rows affected.";
}
}
catch (SqlException ex)
{
lblMessage.Text=ex.Message+ex.StackTrace;
}
finally
{
cmd.Dispose();
cn.Close();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
SqlDataSource1.SelectCommand = TextBox1.Text;
SqlDataSource1.UpdateCommand = TextBox2.Text;
SqlDataSource1.DeleteCommand = TextBox3.Text;
}
protected void GridView1_PageIndexChanged(object sender, EventArgs e)
{
GridView1.DataSourceID = SqlDataSource1.ID;
SqlDataSource1.SelectCommand = this.TextBox1.Text;
SqlDataSource1.UpdateCommand = this.TextBox2.Text;
SqlDataSource1.DeleteCommand = this.TextBox3.Text;
GridView1.DataBind();
}
/* -----in your web.config:----
* <connectionStrings>
<add name="db" connectionString="server=(local);database=northwind;uid=sa;pwd=yourpass" providerName="SqlClient"/>
</connectionStrings>
<system.web>
*/
protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
BindIt();
}
protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
BindIt();
}
protected void BindIt()
{
GridView1.DataSourceID = SqlDataSource1.ID;
SqlDataSource1.SelectCommand = this.TextBox1.Text;
SqlDataSource1.UpdateCommand = this.TextBox2.Text;
SqlDataSource1.DeleteCommand = this.TextBox3.Text;
GridView1.DataBind();
}
protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
BindIt();
}
protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
BindIt();
}
</script>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Database Access Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table style="width: 657px">
<tr><td style="width: 160px">
SELECT COMMAND</td><td>
<asp:TextBox ID="TextBox1" runat="server" Width="469px" EnableViewState=true></asp:TextBox></td><td style="width: 3px"></td></tr>
<tr><td style="width: 160px">
UPDATE COMMAND</td><td>
<asp:TextBox ID="TextBox2" runat="server" Width="466px" EnableViewState=true></asp:TextBox></td><td style="width: 3px"></td></tr>
<tr><td style="width: 160px">
DELETE COMMAND</td><td>
<asp:TextBox ID="TextBox3" runat="server" Width="465px" EnableViewState=true></asp:TextBox></td><td style="width: 3px"></td></tr>
<tr><td style="width: 160px">
GO</td><td>
<asp:Button ID="Button1" runat="server" Text="Populate Grid" Width="110px" OnClick="Button1_Click" /></td><td style="width: 3px"></td></tr>
<tr><td style="height: 41px">
Free form SQL:</TD><TD style="height: 41px">
<asp:TextBox ID="TextBox4" runat="server" Width="469px" Height="144px" TextMode="MultiLine"></asp:TextBox></td><td style="width: 3px; height: 41px;">
<asp:Button ID="Button2" runat="server" Text="Execute" OnClick="Button2_Click" /></td></tr>
<tr><td colspan=3><asp:Label ID=lblMessage runat=server></asp:Label></td></tr>
</table>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateDeleteButton="True" AutoGenerateEditButton="True" CellPadding="4"
ForeColor="#333333" GridLines="None" Width="666px" DataSourceID="SqlDataSource1" EnableSortingAndPagingCallbacks="false" PageSize="5" OnPageIndexChanged="GridView1_PageIndexChanged" OnRowEditing="GridView1_RowEditing" OnRowCancelingEdit="GridView1_RowCancelingEdit" OnRowUpdating="GridView1_RowUpdating" OnRowDeleting="GridView1_RowDeleting" >
<FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
<SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" />
<PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" />
<HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" Runat="server" SelectCommand=""
UpdateCommand=""
DeleteCommand =""
ConnectionString="<%$ ConnectionStrings:db %>" />
</div>
</form>
</body>
</html>What the above will give you is a page where you enter in the SELECT, UPDATE AND DELETE command statements, and the Gridview and SqlDataSource take care of the rest - including edit, update, cancel, and paging! Here's an example:
I have also added an additional multiline textbox and button for "Free form SQL". If it is a select, you will get it in the GridView, and if its an update, insert or delete, you'll see the rows affected in the label. In sum, pretty much everything you need for basic database work.
You can download the zip file containing the single db.aspx page here. All you need to do is have the correct connection string in your application's web.config connectionStrings section; there is a sample inside comment markers in the code for the page. |
| Biography |
Peter Bromberg is a C# MVP, MCP, and .NET expert who has worked in banking ,financial and telephony for 20 years. Pete focuses exclusively on the .NET Platform, and his samples at GotDotNet.com have been downloaded over 56,000 times. Peter enjoys producing 3D raytraced digital photo collage with Maya, the beach, and fine wines. You can view Peter's UnBlog and IttyUrl sites.
Please post questions at forums, not via email! |  |
 |
| |