
Build a Script-Only ASP.NET 2.0 Data Editing Page By Peter Bromberg Printer Friendly Version View My Articles 145 Views

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

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.
Please post questions at forums, not via email!

7Zip (LZMA) In-Memory Compression with C#

A Better CAPTCHA Control (Look Ma - NO IMAGE!)

Build A Self-Caching ASP.NET Web Site Thumbnail Control

Forms Auth: Membership, Roles and Profile with no Providers and no Session

C# .NET Yahoo Stock Download and Charting

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



?
Wiz Aus replied to Peter Bromberg at Tuesday, February 06, 2007 1:01 AM
Does this work?



What, are you trying to be funny?
Peter Bromberg replied to Wiz Aus at Tuesday, February 06, 2007 6:43 AM
	Why would somebody write a whole article and post a downloadable sample that didn't work?



Very Cool
Ken Otto replied to Peter Bromberg at Thursday, February 15, 2007 9:05 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!



Glad you like it.
Peter Bromberg replied to Ken Otto at Friday, February 16, 2007 7:22 AM
	If you come up with any additional features, post them here!



about code
kique net replied to Peter Bromberg at Thursday, November 29, 2007 3:10 PM
Hi mister, any asp.net 1.1 version about it ? please, send me answer to my email, thanks in advance !!!



Version for asp.net 1.1
kique net replied to Peter Bromberg at Friday, November 30, 2007 6:29 AM
Hi, misters any version for asp.net 1.1 ?? Thanks.

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

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!