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