Build a Script-Only ASP.NET 2.0 Data Editing Page

By Peter Bromberg
ASP.NET
Articles

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.
Popularity  (2478 Views)
View Peter Bromberg's Articles
Picture
Biography - Peter Bromberg
Peter Bromberg is a C# MVP, MCP, and .NET expert who has worked in banking, financial and telephony for over 20 years. Pete focuses exclusively on the .NET Platform, and currently develops SOA and other .NET applications for a Fortune 500 clientele. Peter enjoys producing digital photo collage with Maya,playing jazz flute, the beach, and fine wines. You can view Peter's UnBlog and IttyUrl sites. Follow Microsoft MVP
Create New Account
Article Discussion: Build a Script-Only ASP.NET 2.0 Data Editing Page
Peter Bromberg posted at Thursday, January 18, 2007 1:42 PM
Original Article
reply
?
Wiz Aus replied to Peter Bromberg at Thursday, January 18, 2007 1:42 PM

Does this work?

reply
What, are you trying to be funny?
Peter Bromberg replied to Wiz Aus at Thursday, January 18, 2007 1:42 PM
Why would somebody write a whole article and post a downloadable sample that didn't work?
reply
Very Cool
Ken Otto replied to Peter Bromberg at Thursday, January 18, 2007 1:42 PM
A great piece of code for a quick solution. A minor change I made was to switch SqlData to OleDB provider so I could connect to any database. Don't forget to set the ProviderName in the SqlDataSource to the appropriate database. Thanks again Peter!
reply
Glad you like it.
Peter Bromberg replied to Ken Otto at Thursday, January 18, 2007 1:42 PM
If you come up with any additional features, post them here!
reply
about code
kique net replied to Peter Bromberg at Thursday, January 18, 2007 1:42 PM
Hi mister, any asp.net 1.1 version about it ?

please, send me answer to my email, thanks in advance !!!
reply
Version for asp.net 1.1
kique net replied to Peter Bromberg at Thursday, January 18, 2007 1:42 PM

Hi, misters

any version for asp.net 1.1 ??

Thanks.

reply