C# dotnet SQL editor (codebehind side)

In the first post C# dotnet SQL editor (webform side) I’ve explained the purpose of this “exercise” and suggested a webform proposal for this diy SQL Editor.

In this article I want to show the codebehind part.

As usual you can find the complete example in my bitbucket repository (click on the image):
 
 

This is a summar the main features that such a tool should have:

• list of database tables
• list of table fields
• list of database stored procedures
• list of database view
• store procedures definition
• view definition
• export in csv and/or excel
• info about errors

Let’s start from the beginning.

When I call the web page I want the list of all the database connections in order to chose the right one to work on.

Remember that I’ve placed a control in the aspx page that will store this list: lstConnections

So the “problem” to be solved is how to retrieve the list of databases present in our application.

The simple solution is to use the System.Configuration.ConfigurationManager.ConnectionStrings in order to retrieve all the connection string looping the Configuration manager:

protected void Page_Load(object sender, EventArgs e)
{
    // Load the connections only the first time the page is loaded
    if (!IsPostBack)
    {
        foreach (ConnectionStringSettings c in System.Configuration.ConfigurationManager.ConnectionStrings)
        {
            lstConnections.Items.Add(c.Name);
        }
    }
}

Here we are asking the ConnectionManager to give us a list ok all the connection strings.

I’ve placed this part in the Page_Load checking if a PostBack has been triggered because I want this list to be filled only once when a user open the SQl Editor page.

Good! We have a list of db, We have a textbox to write a query (txtQuery). We have a GridView to put the results.

Every time an user selects a dB we want to remember the choise. To reach the scope we can use a session variable or an hidden field.

My decision is to go with an hidden field (hdnConstring); of course you can do different.

When I click on the lstConnections for choosing the database text I want to:

• load the databases tables list
• load the Stored Procedures list
• load the View list

First of all let’s prepare a method for filling a ListBox with the result of a database query.

For this I’ve prepared a method for executing a query on the selected database:

protected void getDBinfo(string constring, string qry, ListBox lst)
{
    // get the db info
    SqlConnection myConn = new SqlConnection(constring);
    SqlDataReader reader;
    SqlCommand cmd = new SqlCommand(qry, myConn);
    cmd.CommandType = CommandType.Text;
    myConn.Open();
    try
    {
        lblInfo.Text = string.Empty;
        reader = cmd.ExecuteReader();
        lst.DataSource = reader;
        lst.DataTextField = "Name";
        lst.DataBind();
    }
    catch (Exception ex)
    {
        lblInfo.Text = ex.ToString();
    }
    finally
    {
        if (myConn != null)
            myConn.Dispose();
    }
}

This method gets three arguments:

1. the connection string to the db
2. the query to be executed
3. the ListBox to be filled

The scope is to fill the ListBox with the query result. In case of error the lblError will be filled with the reason code.

Focused this point, now it is time to fo forward to the next step.

Table list

<asp:ListBox ID="lstConnections" runat="server" OnSelectedIndexChanged="lstDBConnections" AutoPostBack="true"
</asp:ListBox>

When I click on the ListBox lstConnections control I call the lstDBConnections method:

protected void lstDBConnections(object sender, EventArgs e)
{
    string db = lstConnections.SelectedItem.Text;
    string constring = WebConfigurationManager.ConnectionStrings[db].ToString();
    string qry = "";
    string table = "%";
    hdnConstring.Value = constring;
    // fetch info from table
    qry = "SELECT Name from Sysobjects where xtype = 'u'";
    getDBinfo(constring, qry, lstTables);
    // fetch info from Fields
    qry = "select c.name as Name from sys.columns c inner join sys.tables t on t.object_id = c.object_id and t.name like '" + table + "' and t.type = 'U'";
    getDBinfo(constring, qry, lstFields);
    // fetch info from Stored Procedures
    qry = "select name from sys.procedures";
    getDBinfo(constring, qry, lstSP);
    // fetch info from Views
    qry = "select name from sys.views";
    getDBinfo(constring, qry, lstViews);
}

The trick is done by the first query:

SELECT Name from Sysobjects where xtype = 'u'

Here we are asking the sql db to give us the list of the user tables.

After I call the getDBinfo method in order to fill the related lstTables listbox:

getDBinfo(constring, qry, lstTables);

Doing the same for the list of fields, stored procedures and views, we will have al the listboxes filled with the all database info.

Going forward

The scope of this exercise is to gain a complete sql editor in order to maintain our database.

List of table fields

When I click on a table name I want the listbox lstFields to be filled with the only table related fields.

So let’s implement the method lstTableSelect called every time someone click on the lstTables listbox

<asp:ListBox ID="lstTables" runat="server" Height="200px" CssClass="small" AutoPostBack="true" OnSelectedIndexChanged="lstTableSelect">

protected void lstTableSelect(object sender, EventArgs e)
{
    string constring = hdnConstring.Value;
    // list all the table fields  
    string qry = "select c.name as Name from sys.columns c inner join sys.tables t on t.object_id = c.object_id and t.name like '" + lstTables.SelectedItem.Text + "' and t.type = 'U'";
    getDBinfo(constring, qry, lstFields);
}

Let’s do the same for the SP:

<asp:ListBox ID="lstSP" runat="server" Height="200px" CssClass="small-size" AutoPostBack="true" OnSelectedIndexChanged="lstSPSelect" >

protected void lstSPSelect(object sender, EventArgs e)
{
    string constring = hdnConstring.Value;
    // get the Stored Procedure definition  
    string qry = "select ROUTINE_DEFINITION from INFORMATION_SCHEMA.ROUTINES Where ROUTINE_NAME='" + lstSP.SelectedItem.Text + "'";
    getSchemaInfo(constring, qry, txtQuery);
}

and the views:

<asp:ListBox ID="lstViews" runat="server" Height="200px" CssClass="small-size" AutoPostBack="true" OnSelectedIndexChanged="lstViewsSelect">

protected void lstViewsSelect(object sender, EventArgs e)
{
    string constring = hdnConstring.Value;
    // get the View definition  
    string qry = "select VIEW_DEFINITION from INFORMATION_SCHEMA.VIEWS Where TABLE_NAME='" + lstViews.SelectedItem.Text + "'";
    getSchemaInfo(constring, qry, txtQuery);
    // fields: SELECT * FROM INFORMATION_SCHEMA.COLUMNS where table_name='exportdb' and column_name='country'
}

The getSchemaInfo(constring, qry, txtQuety) do a similar job of the getDbInfo() but filling a textbox instead of a listbox.

protected void getSchemaInfo(string constring, string qry, TextBox txt)
{
    // used for retrieving the db schema info rendering it in a label
    constring = hdnConstring.Value;
    SqlConnection myConn = new SqlConnection(constring);
    SqlDataReader reader;
    SqlCommand cmd = new SqlCommand(qry, myConn);
    cmd.CommandType = CommandType.Text;
    myConn.Open();
    try
    {
        lblInfo.Text = string.Empty;
        reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            txt.Text = reader.GetString(0);
        }
    }
    catch (Exception ex)
    {
        lblInfo.Text = ex.ToString();
    }
    finally
    {
        if (myConn != null)
            myConn.Dispose();
        if (cmd != null)
            cmd.Dispose();
    }        
        
}

Quering the DB

Last but not least this tool needs a way to query the database, so let’s do some work on this part.

The idea is to fill the textbox txtQuery with some sql commands, press the Query button getting the result from the database.

The query button calls the btnCommitClick method.

<asp:TextBox ID="txtQuery" TextMode="MultiLine" Width="100%" Height="200px" runat="server"></asp:TextBox>
...
<asp:Button ID="btnCommit" runat="server" OnClick="btnCommitClick" Text="Query" />

So let’s implement the methods for this task.

private void QueryDataBind(GridView grd)
{
    // grid databind
    string constring = hdnConstring.Value;
    SqlConnection myConn = new SqlConnection(constring);
    SqlDataReader reader;
    string qry = txtQuery.Text;
    SqlCommand cmd = new SqlCommand(qry, myConn);
    cmd.CommandType = CommandType.Text;
    myConn.Open();
    try
    {
        lblInfo.Text = string.Empty;
        reader = cmd.ExecuteReader();
        grd.DataSource = reader;
        grd.DataBind();
    }
    catch (Exception ex)
    {
        lblInfo.Text = ex.ToString();
        //throw;
    }
    finally
    {
        if (myConn != null)
            myConn.Dispose();
        if (cmd != null)
            cmd.Dispose();
    }
}

The QueryDataBind(GridView grd) executes the query written in the txtQuery textbox and make a databind of the result in the grid grd passed as argument.

If any error, the lblInfo will be filled with the reason cause.

This method is called by the btnCommitClick method:

protected void btnCommitClick(object sender, EventArgs e)
{
    this.QueryDataBind(grdResult);
}

This is the final result:

Excel Export

Just the last step!

protected void ExportToExcel(GridView grd)
{
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition", "attachment;filename=sqlTableExport.xls");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";
    using (StringWriter sw = new StringWriter())
    {
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        //To Export all pages
        grd.HeaderRow.BackColor = Color.White;
        foreach (TableCell cell in grd.HeaderRow.Cells)
        {
            cell.BackColor = grd.HeaderStyle.BackColor;
        }
        foreach (GridViewRow row in grd.Rows)
        {
            row.BackColor = Color.White;
            foreach (TableCell cell in row.Cells)
            {
                if (row.RowIndex % 2 == 0)
                {
                    cell.BackColor = grd.AlternatingRowStyle.BackColor;
                }
                else
                {
                    cell.BackColor = grd.RowStyle.BackColor;
                }
                cell.CssClass = "textmode";
            }
        }
        grd.RenderControl(hw);
        //style to format numbers to string
        string style = @"<style> .textmode { } </style>";
        Response.Write(style);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
 
    }
}

Notes

In order to let the excel export functioning, we need to:

disable the EnableEventValidation attribute in the webform in order to not validate the grid rendering event

<%@ Page Title="" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" EnableEventValidation="false"
 CodeBehind="sqlEditor.aspx.cs" Inherits="balsamino_com.sqlEditor" %>

and to override the grid rendering in the server form:

public override void VerifyRenderingInServerForm(Control control)
{
    /* Verifies that the control is rendered */
}

Update Panel considerations

Since this game is played inside an update panel, we can experiment problems with button events not properly fired.

For this it is important to place a postback trigger for each control that you want to render.

The right place (imho) is inside the Page_Load:

protected void Page_Load(object sender, EventArgs e)
{
    // register the triggers to avoid the control to not be triggered inside the updatepanel
    ScriptManager.GetCurrent(this).RegisterPostBackControl(btnExport);
    ScriptManager.GetCurrent(this).RegisterPostBackControl(lstFields);
    ScriptManager.GetCurrent(this).RegisterPostBackControl(lstSP);
    ScriptManager.GetCurrent(this).RegisterPostBackControl(lstConnections);
    ScriptManager.GetCurrent(this).RegisterPostBackControl(lstTables);
    ScriptManager.GetCurrent(this).RegisterPostBackControl(lstViews);
.....

That’s all.

• You can find all the code in my bitbucket repository: bitbucket code repository

I hope you can find useful this article.

Feel free to post comments or to ask me for additional info.