Universal Database Admin for ASP.NET and SQL Server (Reloaded)

• Download the admin main project source files - 1.01 MB
• Download AdminLite (no login, no admin table, no customization required) - 978 Kb
• Download Admin2005 (ASP.NET 2.0) project source files - 1 MB

Online demo - UDA

Introduction (Seven pages is all it needs)

This article describes an ASP.NET web module which can be added /customized in any SQL driven web application as a database administration module. When I first started with an admin section for a web application, I thought why not try to make something more general, a plug-n-play admin section which can be used for any SQL driven dynamic web application, and here is the result of the effort.

The target was:

1. Create a self sufficient admin section which can be put into any SQL driven web application by changing the connection string for SQL Server in the web.config file.
2. User needs to login before doing CRUD (Create/Read/Update/Delete) operations (Not required for the AdminLite version).
3. There should be individual pages one for each operation (CRUD) which will take the tablename as the parameter.
4. A dataAccess class which wraps all the data access methods and returns the values or records as required.
5. CRUD operations for tables (you can create/read/update/delete tables).
6. Filter records using custom SQL queries.
7. Execute custom SQL statements on the database.
8. Support for paging/sorting.
9. Includes SQL injection workaround for the login page using regular expressions - latest update.
10. Support for multiple images (binary data in SQL tables).

"A good way for non-IT people to have a look at the database without having to know anything about SQL" CK Max

(The above comment is about the first phase of the DB Admin tool. The updated version is much more advanced and is focused on IT users only.)

Assumptions

These were the initial thoughts, but to make it a general module, I made some assumptions:

• Every table has a Primary Key (integer and identity) and the it will be the first field in the table.
• (Not required for the AdminLite version) There is atleast one AdminUser table in the SQL database which has a permission field with the value "Admin", as shown below, for the users to login to the admin section for CRUD operations.

Admin table structure

An Admin table with values as displayed in the web admin module:

The web.config has two keys, one for the connection string and the other for the name of the admin table:

<appSettings>
  <add key="ConnectionString" 
    value="Data Source=YourdatasrcIP;POOLING=FALSE;
           database=YourDatabasename;
           User ID=YouruserID;
           Password=YourPassword" />
  <add key="AdminTable" 
    value="YourAdminTableName_NOT_REQUIRED_IN_ADMINLITE" /> 
</appSettings>

Actions

These are the actions which make the whole module:

• Data access
• Login (Not required for the AdminLite version)
• Display all the tables in the database
• View list of records in a table
• View a record
• Edit a record
• Delete a record
• ------Latest updates--------------
• Create table
• Read table
• Update table
• Delete table
• Delete all the records in a table
• Filter records in a table using a custom SQL query
• Execute custom SQL statements

Data access class

There is a standard data access class, clsDataAccess.cs, which handles all the related actions.

Code: I have kept here only the names of the functions, just to give you a glimpse of the data access methods.

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient; 

namespace Admin
{
    public class clsDataAccess
    // Class defination
    {
        public clsDataAccess()
        { }
        SqlConnection mycon = new SqlConnection(
          ConfigurationSettings.AppSettings["ConnectionString"]);
        // Opens database connection with in SQL SERVER
        public bool openConnection()
        // Closes database connection with in SQL SERVER
        public void closeConnection()
        // Getdata from the table required(given in query).
        public SqlDataReader getData(string query)
        // Save data usually,inserts and updates the data in table.
        public void saveData(string query)
        // Save data usually,inserts and updates the data.
        public void saveNewData(string query)
        // Delete data in database depending on the tablename.
        public int DeleteData(string query)
        // Get data by paging using datagrid.
        public SqlDataAdapter getDataforUpdate(string query)
        // Get data by paging using datagrid.
        public DataSet getDatabyPaging(string query)
        // check a particular value to see the validity.
        public int getCheck(string query)
        // Get a value of limit from the database table.
        public string getValue(string query,int j)
        //Log in method 
        public SqlDataReader Login(string query)
        // dynamically get all table names
        public DataTable getTablenames()
        // For Table operations
        public int TableWrite(string query)
    }
}

Login

Login in action (Not required for the AdminLite version):

A basic authentication of the user login process checks if the user has admin permissions.

Code

string uname;
uname = txtemail.Text.ToString();

string pass;
pass = txtpwd.Text.ToString();

string s;

s = "SELECT * FROM " + 
    ConfigurationSettings.AppSettings["AdminTable"] +
    " where Username ='" + uname + 
    "' AND Password ='" + pass + "'"  ;
//Login class is called
clsDataAccess mylogin = new clsDataAccess();
SqlDataReader mydr1 = mylogin.Login(s);
    
while(mydr1.Read())
{
    if (mydr1.GetValue(6).ToString().Trim()  == "Admin")
    {
        Session["userfullname"] = 
           mydr1.GetValue(3).ToString().Trim() +
           " " + mydr1.GetValue(4).ToString().Trim();
        Session["Admin"] = "true";
        Response.Redirect("DBMain.aspx");
    }
    else
    {
        lblError.Text=  "Error ! - Invalid Login";
    }
}
lblError.Text= "Error ! - Invalid Login" ;
mydr1.Close();

Secondly, in all the pages, a check is done to see if the user has logged in, otherwise they are redirected to the login page.

SQL injection workaround

Code

private bool CheckSQLInjection(string s)
{
    bool flagSQLI = false;
    try
    {
        if ( !Regex.IsMatch(s, @"^[a-zA-Z._s]{1,50}$"))
        {
            flagSQLI = false;
        }
        else
        {
            flagSQLI = true;
        }
    }
    catch (Exception ex)
    {
        flagSQLI = true;
    }

    if (flagSQLI)
        return true;
    else
        return false;
}

The code is for checking the session for the presence of administrator rights.

Code

<%
{
    if ((string)Session["Admin"] == "true")
    {
    }
    else
        Response.Redirect("index.aspx");
}%>

Display all the tables in a database

Once the user logs in, he needs to see what tables are there in the database. This page can also be generalized (by querying the database for all the tables and excluding the system tables).

But normally, the table names in a database are named like APT_TABLE1 or XP_TABLE2. The names in such a case are not very helpful for an Admin user who might not be the technical person handling the tables, so let's make a simple HTML page with user friendly table names, with links to all the CRUD operations (Not required for the AdminLite version).

Added 29^th Nov for the AdminLite version only

In the AdminLite version, a schema is used to get all the table names.

Added Code for AdminLite

public DataTable getTablenames()
{
    SqlDataAdapter da = 
      new SqlDataAdapter("SELECT * FROM Information_Schema.Tables
      where Table_Type = 'BASE TABLE'", mycon);
    DataTable dt = new DataTable();
    da.Fill(dt);
    return dt;
}

foreach (DataRow row in myT.Rows)
{
    Response.Write (row["TABLE_NAME"] + "<hr>");
}

Select tables in action:

The output has links to the display details pages.

Code (No customization required for the AdminLite version).

<A href="db.aspx?table=EventsTableName">Events and News</A>

View list of records in a table

When you select a table, the following features are important for an administrator to view/browse the records and act on them:

• We need to display a list of records in a table.
• Links to CRUD operations for every record.
• Paging for the list.
• Sorting with column name (default primary key ID).

A DataGrid was the best choice as it has inbuilt paging and sorting. The result should look something like this:

List of records in action:

Code

You need to find out the first key which is the Primary Key which, and it can be stored in a session variable:

Session["FirstKey"] = GetFirstKeys();

private string GetFirstKeys()
{
    string returnvalue = "Node";
    DataTable dt = new DataTable();
    Session["tName"] = 
       Request.QueryString["table"].ToString().Trim();
    string query;
    query = "select * from " + 
       Request.QueryString["table"].ToString().Trim() ;
    clsDataAccess myclass = new clsDataAccess();
    myclass.openConnection();
    mydr = myclass.getDatabyPaging(query);
    returnvalue = mydr.Tables[0].Columns[0].Caption;
    mydr.Clear();
    return returnvalue;
}

At page load, bind the DataGrid to the data source:

if (!IsPostBack)
{
    BindDataGrid();
}

I added a collection to create a data source, with the parameter tablename which comes from the querystring, and added the table name to the session to be used later for display purposes. This table will be used in most of the pages.

ICollection CreateDataSource()   
{
    DataTable dt = new DataTable(); 
    Session["tName"] = 
         Request.QueryString["table"].ToString().Trim();
    string query;
    //query = "select * from " + 
    //       Request.QueryString["table"].ToString().Trim() ; 

    // added for custom sql query
    query = Session["qry"].ToString() ;
    clsDataAccess myclass = new clsDataAccess();
    myclass.openConnection();
    mydr = myclass.getDatabyPaging(query);
    TempTable = new DataTable();
    TempTable = mydr.Tables[0];
    TempTableView = new DataView(TempTable);
    TempTableView.Sort = SortField;
    return TempTableView;
}

DGTable is the name of the DataGrid. The other functions are for binding the DataGrid with the data source and for paging/sorting etc.

private void DGtable_PageIndexChanged(object source,
        System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{
    DGtable.CurrentPageIndex=e.NewPageIndex;
    BindDataGrid();
}

Add three columns to the DataGrid for View/Edit and Delete links. This has to be done dynamically because we don't know what the name of the Primary Key is beforehand. So, let's add it in the page load event.

A few of you might be thinking "why the first key?". Well, a quick answer is, when you give a parameter to the DataGrid:

Viewdata.aspx?id={0}

you have to give it at design time. You can obviously try to find the primary key, get the column count, and dynamically add the count as the parameter for the DataGrid. Of course, you won't win a Nobel prize for that. So this is an easy and quick solution.

private void BindDataGrid
{
    Session["FirstKey"] = GetFirstKeys();
    HyperLinkColumn urlView = new HyperLinkColumn();
    urlView.Text = "View";
    urlView.DataNavigateUrlField = Session["FirstKey"].ToString() ;
    urlView.HeaderText = "View";
    urlView.DataNavigateUrlFormatString = "Viewdata.aspx?id={0}";

    HyperLinkColumn urlEdit = new HyperLinkColumn();
    urlEdit.Text = "Edit";
    urlEdit.DataNavigateUrlField = Session["FirstKey"].ToString() ;
    urlEdit.HeaderText = "Edit";
    urlEdit.DataNavigateUrlFormatString = "Editdata.aspx?id={0}";

    HyperLinkColumn urlDelete= new HyperLinkColumn();
    urlDelete.Text = "Delete";
    urlDelete.DataNavigateUrlField = Session["FirstKey"].ToString() ;
    urlDelete.HeaderText = "Delete";
    urlDelete.DataNavigateUrlFormatString = "Deletedata.aspx?id={0}";
    DGtable.Columns.Add(urlView);
    DGtable.Columns.Add(urlEdit);
    DGtable.Columns.Add(urlDelete);
    DGtable.DataSource = CreateDataSource();
    DGtable.DataBind();   
}

For sorting, again here, once you click the field, it will get sorted in ascending order. We can further develop this for ascending as well as descending order.

private void DGtable_SortCommand(object source,
        System.Web.UI.WebControls.DataGridSortCommandEventArgs e)
{
    SortField = (string)e.SortExpression;
    lblstatus.Text = "VIEW RECORDS OF TABLE: [" + 
                     Request.QueryString["table"].ToString() + 
                     "] Sorted by '<i>"
                     + Session["FirstKey"]+ "</i>'";
    BindDataGrid();
}

View a record

View in action:

Code

I have used Literal controls to create the controls on the fly, to the TableCell object, and add methods to add it to the table.

TableCell c = new TableCell();
c.Controls.Add(new LiteralControl("<b>Value</b>"));

myReader.GetName(j).ToString();

gives the field name of the table.

myReader.GetValue(j).ToString();

gives the field value of the table:

private void ShowData()
{
    string s="";

    SqlDataReader myReader;
    int i =0, j=0,k=0;

    i = Convert.ToInt32(Request.QueryString["id"]);
    s = "SELECT * FROM " + Session["tName"].ToString() + 
        " where " + Session["FirstKey"] + " =" + i ;

    clsDataAccess myclass = new clsDataAccess();
    myclass.openConnection();
    myReader = myclass.getData(s);
   
    while (myReader.Read())
    {
        for(j=-1; j<myReader.FieldCount;j++)
        {
            TableRow r = new TableRow();
            for (k=0; k<3; k++)
            {
                TableCell c = new TableCell();

                if (j==-1)
                {
                    if (k == 0)
                    {
                        c.Width = 15;
                        c.Controls.Add(new LiteralControl("<b>Sno.lt;/b>"));
                    }
                    if (k == 1)
                    {
                        c.Width = 50;
                        c.Controls.Add(new LiteralControl("<b>Column</b>"));
                    }
                    if (k == 2)
                    {
                        c.Width = 50;
                        c.Controls.Add(new LiteralControl("<b>Value</b>"));
                    }
                }
                if(j>-1)
                {
                    if (k == 0)
                    {
                        c.Width = 15;
                        c.Controls.Add(new LiteralControl((j+1).ToString()));
                    }
                    if (k == 1)
                    {
                        c.Width = 50;
                        if (myReader.GetName(j).ToString()=="ID")
                            c.Controls.Add(new LiteralControl("<i>&nbsp;"  +
                                 myReader.GetName(j).ToString() + "</i>"));
                        else
                            c.Controls.Add(new LiteralControl("&nbsp;" +
                                 myReader.GetName(j).ToString() + "&nbsp;" ));
                    }
                    if (k == 2)
                    {
                        c.Width = 300;
                        c.Controls.Add(new LiteralControl("&nbsp;" +
                            myReader.GetValue(j).ToString() + "&nbsp;" ));
                    }
                }
                c.VerticalAlign = VerticalAlign.Top;
                c.Height = Unit.Pixel(22);
                r.Cells.Add(c);
            }
            Table2.Rows.Add(r);
        }
    }
    myReader.Close();
    myclass.closeConnection();
}

Edit a record

Editing a record is the tricky portion. Though I have taken care to have a general edit functionality, this portion can be further developed to include drop downs, radio buttons, check boxes etc.

If the field is the primary key, lock it:

if (myReader.GetName(j).ToString()==Session["FirstKey"])
{
    TextBox1.ReadOnly = true;
    TextBox1.BackColor = Color.GhostWhite ;
}

To get the type of the data:

myReader.GetFieldType(j).ToString()

If the data is a value, create a TextBox:

c.Controls.Add(TextBox1);

Edit in action:

Code

private void ShowData()
{
    string s="";

    SqlDataReader myReader;
    int i =0, j=0,k=0;

    i = Convert.ToInt32(Request.QueryString["id"]);
    s = "SELECT * FROM " + Session["tName"].ToString() + 
        " where " + Session["FirstKey"] + "=" + i ;
    clsDataAccess myclass = new clsDataAccess();
    myclass.openConnection();
    myReader = myclass.getData(s);
    while (myReader.Read())
    {
        for(j=-1; j<myReader.FieldCount;j++)
        {
            TableRow r = new TableRow();
            for (k=0; k<4; k++)
            {
                TableCell c = new TableCell();
                if (j==-1)
                {
                    if (k == 0)
                    {
                        c.Width = 15;
                        c.Controls.Add(new LiteralControl("<b>Sno.</b>"));
                    }
                    if (k == 1)
                    {
                        c.Width = 50;
                        c.Controls.Add(new LiteralControl("<b>Column</b>"));
                    }
                    if (k == 2)
                    {
                        c.Width = 50;
                        c.Controls.Add(new LiteralControl("<b>Value</b>"));
                    }
                    if (k == 3)
                    {
                        c.Width = 35;
                        c.Controls.Add(new LiteralControl("<b>&nbsp;Datatype</b>"));
                    }
                }
                if(j>-1)
                {
                    TextBox TextBox1 = new TextBox();
                    TextBox1.ID = "Text" + j;
                    TextBox1.Text =  myReader.GetValue(j).ToString();
                    if (myReader.GetName(j).ToString()==Session["FirstKey"])
                    {
                        TextBox1.ReadOnly = true;
                        TextBox1.BackColor = Color.GhostWhite;
                    }
                    TextBox1.Width = 200;
                    if (k == 0)
                    {
                        c.Width = 15;
                        c.Controls.Add(new LiteralControl((j+1).ToString()));
                    }
                    if (k == 1)
                    {
                        c.Width = 50;
                        if (myReader.GetName(j).ToString()==Session["FirstKey"])
                            c.Controls.Add(new LiteralControl("<i>&nbsp;"  + 
                                myReader.GetName(j).ToString() + "</i>"));
                        else
                            c.Controls.Add(new LiteralControl("&mp;nbsp;" + 
                                myReader.GetName(j).ToString() + "&nbsp;" ));
                    }
                    if (k == 2)
                    {
                        c.Width = 300;
                        c.Controls.Add(TextBox1);
                    }
                    if (k == 3)
                    {
                        c.Width = 35;
                        c.Controls.Add(new LiteralControl("&nbsp;" + 
                              myReader.GetFieldType(j).ToString() + "&nbsp;" ));
                    }
                }
                r.Cells.Add(c);
            }
            Table2.Rows.Add(r);
        }
    }
    myReader.Close();
    myclass.closeConnection();
}

Changes submitted:

private void btnSubmit_Click(object sender, System.EventArgs e)
{
    string tname;
    tname = Session["tName"].ToString();
    try
    {
        string s;
        SqlDataAdapter myDA = new SqlDataAdapter();
        DataSet myDataSet = new DataSet();
        int i =0;

        i = Convert.ToInt32(Request.QueryString["id"]);
        s = "SELECT * FROM " + Session["tName"].ToString() + 
            " where " + Session["FirstKey"] + "=" + i ;
        clsDataAccess myclass = new clsDataAccess();
        myclass.openConnection();
        myDA = myclass.getDataforUpdate(s);
        SqlCommandBuilder mySCB = new SqlCommandBuilder(myDA);
        myDA.Fill(myDataSet,tname);
        DataTable myTable;
        myTable =myDataSet.Tables[0];

        for (i =1;i < Request.Form.Count-2;i++)
        {
            myTable.Rows[0][i] = Request.Form[i+1].ToString();
        }
        myDA.Update(myDataSet,tname);
        myDataSet.AcceptChanges();
        lblstatus.Text = " Updated Successfully !!!";
    }
    catch(Exception f)
    {
        lblstatus.Text = f.Message + " Error while Updating";
        lblstatus.Font.Bold = true;
        lblstatus.ForeColor = Color.Red;
    }
    finally
    {
    }
}

Delete a record

Delete in action:

Code

string tname;
tname = Session["tName"].ToString();
try
{
    string s;
    SqlDataAdapter myDA = new SqlDataAdapter();
    DataSet myDataSet = new DataSet();
    int i =0;

    i = Convert.ToInt32(Request.QueryString["id"]);
    s = "SELECT * FROM " + Session["tName"].ToString() + 
        " where " + Session["FirstKey"] + "=" + i ;
    clsDataAccess myclass = new clsDataAccess();
    myclass.openConnection();
    myDA = myclass.getDataforUpdate(s);

    SqlCommandBuilder mySCB = new SqlCommandBuilder(myDA);
    myDA.Fill(myDataSet,tname);
    DataTable myTable;

    myTable =myDataSet.Tables[0];
    DataRowCollection rc = myTable.Rows;
    rc[0].Delete();

    myDA.Update(myDataSet,tname);
    myDataSet.AcceptChanges();
    lblstatus.Text = " Deleted Successfully !!!";
}
catch(Exception f)
{
    lblstatus.Text = f.Message + " Error while Updating";
    lblstatus.Font.Bold = true; 
    lblstatus.ForeColor = Color.Red; 
}
finally
{
}

Create table

Create table in action:

Code (as simple as it can be):

string s = "Create Table " + sTable + 
           " ([ID] INTEGER PRIMARY KEY IDENTITY)";
clsDataAccess myclass = new clsDataAccess();
myclass.openConnection();
int i = myclass.TableWrite(s);

public int TableWrite(string query)
{
    SqlCommand sqlCommand = new SqlCommand();
    sqlCommand.CommandText= query;
    sqlCommand.Connection=mycon;
    return sqlCommand.ExecuteNonQuery();
}

Read table

Read table in action:

Code

SqlDataReader myReader;

s = "SELECT * FROM " + sTable;    
clsDataAccess myclass = new clsDataAccess();

myclass.openConnection();    
myReader = myclass.getData(s);

DataTable myT; 
myT = myReader.GetSchemaTable();
    
foreach (DataRow myField in myT.Rows)
{
    //For each property of the field...
    foreach (DataColumn myProperty in myT.Columns)
    {
        //Display the field name and value.
        response.Write(myProperty.ColumnName + 
           " = " + myField[myProperty].ToString());
    }
}

• myField["ColumnName"].ToString()
• myField["DataType"].ToString()
• myField["ColumnSize"].ToString()
• myField["AllowDBNull"].ToString()

give the respective values of the field.

Delete table

Delete table in action:

Code

string s = "Drop Table " + sTable ; 
clsDataAccess myclass = new clsDataAccess();

myclass.openConnection();    
int i = myclass.TableWrite(s);

Delete all the records in a table

string s = "Truncate Table " + sTable ;

clsDataAccess myclass = new clsDataAccess();
myclass.openConnection();    
int i = myclass.TableWrite(s);

Filter records in a table using a custom SQL query

Filtering records in action:

Code

Session["qry"] = txtSQL.Text ; // simple :)

Execute custom SQL statements

Executing custom SQL statements in action:

Code

if (input.Substring(0,1) == "'")
else    
{
    clsDataAccess myclass = new clsDataAccess();
    myclass.openConnection();

    int j = myclass.TableWrite(input);
}

Add/Edit a field

Add/edit a field in action:

Code

sDatatype = (Request.Form["datatype"]).Trim();
sField = (Request.Form["field"]).Trim();
sLength = (Request.Form["length"]).Trim();
sDefault = (Request.Form["default"]).Trim();
sAllowNull = (Request.Form["allownull"]).Trim();

if ((String.Compare(sEdit,"true"))==0)
{
    s =  "Alter Table " + sTable + "  Alter Column  " + 
         sField + " " + sDatatype ;

    if (sLength != "")
    {
        s =  s + " (" + sLength + ")";
    }
    if (sDefault != "")
    {
        s = s + " DEFAULT '" + sDefault + "' ";
    }
    if (sAllowNull != "")
    {
        s = s + " " + sAllowNull + "";
    }
}
else
{
    s =  "Alter Table " + sTable + " ADD " + 
         sField + " " + sDatatype ;
    if (sLength != "")
    {
        s =  s + " (" + sLength + ")";
    }
    if (sDefault != "")
    {
        s = s + " DEFAULT '" + sDefault + "' ";
    }
    if (sAllowNull != "")
    {
        s = s + " " + sAllowNull + "";
    }
}

We are done :)

Universal DB Admin in action:

• Nov 25, 2005: First published.
• Nov 26, 2005: Removed the ID constraint as suggested by Marc Clifton. See assumptions above. New assumption: every table has one Primary Key which is the first field in the table.
• Nov 29, 2005: Added AdminLite - no login, no admin table, and no customization required. Just add your connection string and it's ready to go with a single assumption: every table has one Primary Key which is the first field in the table.
• Dec 1, 2005: Fixed the multiple paging and sorting bug (which crept in due to the first enhancement) reported by C K Max and jamesxd - thanks to both of you :).
• Dec 11, 2005:
  • Create/Read/Update/Delete and Truncate for tables after suggestion from jonny0.
  • Filters records based on a custom query, after suggestion from mkrawats.
  • Executes custom SQL statements on the database.
• March 23 2006:
  • SQL injection workaround for the login page, as suggested by Crap bag.
  • Added automatic insertion of date/time for adding new records.
• April 12 2006: Support for multiple images (binary sata in SQL tables).

And thanks

For coming so far! Being my first post, I was quite skeptical [^] before. Thanks a lot to all of you who reviewed and thought it was useful, and of course, for such brilliant suggestions and comments.

I hope you find this useful, and give me a vote/comment if you do and take care.