C# dotnet SQL Editor (webform side)

Every time I need to develop a small business application I always try to choose the fastest and simplest way to implement it.

This is why I often prefer to use the sql express localdb as datatabase solution.

I don’t need to install a sql express on the server, I can easy move the web application in our data centers.
 
 
The only big issue is the database maintenance that sill needs to involve a sql express solution.

For solving this issue I’ve developed a simple solution that helps me to make db maintenance directly in the web application.

The Idea

Using the SqlConnection and the SqlDataReader we can execute every kind of query.

So why not use a simple text box to write commands?

Essentially I want to write an sql command, to commit it to the db and to show the output in a GridView.

I think important also to have some facilities to help the administrator:

• 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

So let’s prepare a skeleton for the webform side.

First of all note that for this project I’m using the Metro UI CSS 2.0 framework. So all the <div> class are referred to this framework.

Important: Don’t forget to give a look at the secont part: C# dotnet SQL Editor (codebehind side)

For the code give a look at my bitbucket repository (click on the image)

Text box for the query

<div class="span2">
   <div>
      <small>Query</small>
   </div>        
   <div>
      <asp:TextBox ID="txtQuery" TextMode="MultiLine" Width="100%" Height="200px" runat="server">
      </asp:TextBox>
   </div>
</div>

Label for showing error info:

<asp:Label ID="lblInfo" runat="server"></asp:Label>

And two buttons, one for the query commiting and the second for clearing the textbox.

a button for the commit:

<div>
     <asp:Button ID="btnCommit" runat="server" OnClick="btnCommitClick" Text="Query">
     <asp:Button ID="btnClear" runat="server" OnClick="btnClearClick" Text="Clear" />
     <asp:Button ID="btnExport" runat="server" OnClick="btnClearExport" Text="Export" />
</div>

Output results

We need a GridView for showing the query result.

<div>
     <asp:GridView ID="grdResult" runat="server" AutoGenerateColumns="true">
     </asp:GridView>
</div>

This is the basic usage of the grid. In the project I’ve used more options (give a loot at the full code below)

Now it is time to place the controls for the facility part:

Facilities

List of databases

So place a listbox (or dropdown) in the webform in order to let users to select the db:

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

Note the OnSelectIndexChanged event handler.

List of database table

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

List of table fields

<div class="span2">
   <div>
       <small>Fields</small>
   </div>
   <div>
       <asp:ListBox ID="lstFields" runat="server" Height="200px" CssClass="small-size" AutoPostBack="true">
       </asp:ListBox> 
   </div>
</div>

List of Stored Procedures

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

 List of Views

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

Finally I place an hiddenfield for storing some info.

<asp:HiddenField ID="hdnConstring" runat="server" />

I’m explaining the usage of the click events and of the HiddenField in the codebehind article.

For closing this webform part I want to put all those elements in a update panel (I dont’t like the page refresh):

<asp:UpdatePanel ID="updPanel" runat="server">
    <ContentTemplate>
    ......
    ......
    all code above
    ......
    ......
    </ContentTemplate>
</asp:UpdatePanel>

And here is the whole web form code:

<asp:Content ID="Content3" ContentPlaceHolderID="MainContent" runat="server">
    <asp:UpdatePanel ID="updPanel" runat="server">
        <ContentTemplate>
            <asp:HiddenField ID="hdnConstring" runat="server" />
            <div class="grid">
                <div class="row">
                    <div class="span2">
                        <small>dB selection</small>
                        <div>
                            <asp:ListBox ID="lstConnections" runat="server" OnSelectedIndexChanged="lstDBConnections" AutoPostBack="true"></asp:ListBox>
                        </div>
                    </div>
                    <div class="span2">
                        <small>Tables</small>
                        <div>
                            <asp:ListBox ID="lstTables" runat="server" Height="200px" CssClass="small" AutoPostBack="true" OnSelectedIndexChanged="lstTableSelect"></asp:ListBox>
                        </div>
                    </div>
                    <div class="span2">
                        <small>Fields</small>
                        <div>
                            <asp:ListBox ID="lstFields" runat="server" Height="200px" CssClass="small-size" AutoPostBack="true"></asp:ListBox>
                        </div>
                    </div>
                    <div class="span2">
                        <small>StoredProcedures</small>
                        <div>
                            <asp:ListBox ID="lstSP" runat="server" Height="200px" CssClass="small-size" AutoPostBack="true" OnSelectedIndexChanged="lstSPSelect" ></asp:ListBox>
                        </div>
                    </div>
                    <div class="span2">
                        <small>Views</small>
                        <div>
                            <asp:ListBox ID="lstViews" runat="server" Height="200px" CssClass="small-size" AutoPostBack="true" OnSelectedIndexChanged="lstViewsSelect"></asp:ListBox>
                        </div>
                    </div>
                </div>
            </div>
            <div class="row">
                <div>
                    <small>Query</small>
                </div>
                <div>
                    <asp:TextBox ID="txtQuery" TextMode="MultiLine" Width="100%" Height="200px" runat="server"></asp:TextBox>
                </div>
            </div>
            <div class="row">
                <div>
                    <asp:Button ID="btnCommit" runat="server" OnClick="btnCommitClick" Text="Query" />
                    <asp:Button ID="btnClear" runat="server" OnClick="btnClearClick" Text="Clear" />
                    <asp:Button ID="btnExport" runat="server" OnClick="btnExportClick" Text="Export" />
                </div>
            </div>
            <asp:Label ID="lblInfo" runat="server" Text=""></asp:Label>
            <div class="row">
                <div>
                    <asp:GridView ID="grdResult" runat="server" AutoGenerateColumns="true" AllowSorting="true" HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White"
                        RowStyle-BackColor="#A1DCF2" AlternatingRowStyle-BackColor="White" AlternatingRowStyle-ForeColor="#000"
                        AllowCustomPaging="false" EnableSortingAndPagingCallbacks="true" AllowPaging="false">
                    </asp:GridView>
                </div>
            </div>
        </ContentTemplate>
    </asp:UpdatePanel>
</asp:Content>

For the moment that’s all.

Don’t forget to give a look at the secont part: C# dotnet SQL Editor (codebehind side)

For the code give a look at my bitbucket repository (click on the image)

Feel free to comment or to ask me additional info.