Database Admin Tool in 20 Lines of C# Code
page 1 of 1
Published: 01 Nov 2003
Unedited - Community Contributed
Abstract
Illustrates the ease with which ADO.NET and data-bound controls like the DataGrid are used to create web applications with very little programming effort.
by Steve Sharrock
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 14401/ 20

This example illustrates how to create a one page database administration tool with only 20 lines of C# code. Using this example, you can enter almost any SQL statement into a text box and execute the statement against any tables in your database. You can view the results of a SELECT in a DataGrid or the see the affects of UPDATE, INSERT and DELETE statements.

I'm not trying to replace my system's database tools; and for anyone who has worked much with the .NET database classes, there isn't too much new stuff presented here. However, having written this piece of code many times over the past 10-years for different platforms and databases, I was amazed to see that I could actually implement such a robust query-executer with so little code!.

I've tried to keep the example short and sweet, both for the challenge and for illustrative purposes. The ASPX page contains the ASP:TextBox into which the SQL query is entered, an unadorned DataGrid for the results of a SELECT, and an ASP:Label for showing the results (and possible errors) of the query. The code to run the whole thing executes in the page's Page_Load IsPostBack block once the Submit button is pressed.

<FORM id="DBMan" method="post" runat= "server">
  <ASP:TEXTBOX id="SqlCtrl" runat="server" Width="543px"
    Height="127px" TextMode="MultiLine"></ASP:TEXTBOX><BR>
  <INPUT TYPE="submit" VALUE="Submit"><BR>
  <ASP:LABEL id="ResultLbl" runat="server" Width="537px" Height="37px">
  </ASP:LABEL><BR>
  <ASP:DATAGRID id="ResultGrid" runat="server"></ASP:DATAGRID>
</FORM>

In the code below, you'll see that I hard-coded the OleDbConnection string. A more thorough implementation would allow the user to specify both a server and a database. The meat of the code checks to see if the Sql text begins with the word "SELECT" to determine which of the two methods will be performed using the data adapter. The SELECT statement uses a DataSet that gets bound to the DataGrid showing the results of the query. All other statements use the ExecuteNonQuery method of the OleDbCommand and show the number of rows affected in the ResultLbl.

private void Page_Load(object sender, System.EventArgs e)
{
  if ( this.IsPostBack )
  {
    string sql = SqlCtrl.Text.Trim();
    OleDbConnection db = new OleDbConnection(
    "Provider=SQLOLEDB;Data Source=MYSQLSVR;Database=Tester;User Id=sa;" );

    try
    {
      db.Open();
      OleDbDataAdapter adapter = new OleDbDataAdapter( sql, db );

      if ( sql.Length >= 6 && sql.Substring(0,6).ToLower() == "select" )
      {     
        DataSet dataset = new DataSet();
        adapter.Fill(dataset,"Table");
        ResultGrid.DataSource = dataset;
        ResultGrid.DataBind();
        ResultGrid.Visible = true;
        ResultLbl.Text = ResultGrid.Items.Count.ToString() + " rows selected";
      }
      else
      {
        OleDbCommand cmd = new OleDbCommand(sql, db);
        int count  = cmd.ExecuteNonQuery();
        ResultLbl.Text = count.ToString() + " rows affected";
      }
      db.Close();
    }
    catch( System.Exception ex )
    {
      ResultLbl.Text = ex.Message;
    }
  }
}

Conclusion

If you haven't written something like this, you really have to try it, especially if you've ever written one of  these by hand for another platform. Of course, the DataGrid is remarkable for effortlessly displaying the bound data. I've used grids in the past with C++ and VB, and they also make this step pretty easy. But, overall, I've never gotten so much from so little code.

I mentioned earlier about allowing the user to select both a server and a database. Some other enhancements might be to parse the Sql text a little to find individual statements that might be executed separately. For instance, I did discover that the ExecuteNonQuery was very nice about handling more than one statement at a time, like BEGIN and COMMIT transactions.

Downloads

You can download DBMan.zip if you want to use these two files and don't like to type.

Steve Sharrock -   www.AspAlliance.com/shark



User Comments

Title: geni   
Name: ous
Date: 2010-09-06 3:44:01 AM
Comment:
simple and free of errors :) thanks.
Title: Thanks!   
Name: Emmao
Date: 2009-03-27 9:44:52 AM
Comment:
Great! One of those hard to find examples that work straight out of the box. It's an invaluable tool for a database application programmer. Thanks.
Title: thanks   
Name: steve
Date: 2006-08-20 1:19:46 PM
Comment:
just what i was looking for, thanks mate, saves a bit of time
Title: Good   
Name: Amit Sood
Date: 2005-12-10 2:47:49 AM
Comment:
Good one keep them coming






Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-29 11:20:12 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search