Database Viewer and Updater for any Database

• Download source files - 251 Kb

MyDbViewer

Introduction

This is a completely dynamic database viewer. It can be used to:

• View any database
• Navigate between tables
• Select data by simply entering the values in the fields
• Perform updates of table data
• Display data in grids
• Display data as single records
• View metadata

GridView

Purpose of the DbViewer

The purpose of this viewer is to enable direct manipulation of tables without having to use SQL Server or MS Access. It is mainly a tool for developers and not for end users although it could be customized for users.

Uses of DbViewer

• Quick manipulation of tables
• Content management

Using the code

Unzip and open in VS2005 as a Web Site. Run the code from the default.htm page.

(default.htm is a dummy page which loads the relevant aspx pages in the _Chapter13_databaseviewer folder.)

The database class DbData

All the database logic of the application has been placed in the database class, DbData.

Code snippets

The metadata information about tables is not obtained by reading tables but by using the GetSchema command. See the functions in the DbData class which shows how to retrieve columns.

private string dbFieldsSQL(string astrTable, string astrField, bool abReadOnly)
{
    DataTable oDataTable, oIndexTable;
    SqlConnection oConn;
    string        strRows;

    try
    {
        oConn       = dbSqlConnection();
        oConn.Open();
        oDataTable  = oConn.GetSchema("Columns");
        oIndexTable = oConn.GetSchema("IndexColumns");
        strRows     = dbFields(oDataTable, oIndexTable, 
                      astrTable, astrField, abReadOnly);
        oConn.Close();
    }
    catch (Exception err)
    {
        strRows = err.Message;
    }
    return strRows;
}

private string dbFields(DataTable oDataTable, DataTable oIndexTable, 
               string astrTable, string astrField, bool abReadOnly)
{
    int      intNo, intMax;
    string   strCol, strRows;
    string[] arrFields = new string[1000];
    
    strRows  = "\n\n";
    strRows += dbStyle();
    
    // SORT BY ORDINAL_POSITION
    intMax = 0;
    strRows += "<TABLE BORDER=0 CELLSPACING=1>\n";
    foreach (DataRow oRow in oDataTable.Rows)
    {
        if (astrTable == oRow["TABLE_NAME"].ToString())
        {
            if (astrField == "" || astrField.ToLower().Trim() 
                == oRow["COLUMN_NAME"].ToString().ToLower().Trim())
            {
                // intNo = (int) oRow["ORDINAL_POSITION"];
                intNo            = Convert.ToInt32(oRow["ORDINAL_POSITION"]);
                intMax           = (intNo > intMax) ? intNo : intMax;
                strCol           = oRow["COLUMN_NAME"].ToString();
                arrFields[intNo] = "<TR><TD CLASS=Smalls " + 
                                   "ALIGN='left'>" + strCol + 
                                   "<BR /><INPUT NAME=INPUT_" + 
                                   strCol + " ###" + strCol + "### ";
                // READONLY if Key/Index Field
                if (abReadOnly)
                {
                    foreach (DataRow oIndexRow in oIndexTable.Rows)
                    {
                        if (astrTable == oIndexRow["table_name"].ToString() && 
                            oRow["COLUMN_NAME"].ToString() == 
                            oIndexRow["column_name"].ToString())
                        {
                            arrFields[intNo] += 
                                " READONLY STYLE='background: #C0C0C0;' ";
                            break;
                        }
                    }
                }
                arrFields[intNo] += "></TD></TR>\n";
            }
        }
    }
    for (intNo=1;intNo<intMax+1;intNo++)
        {strRows += (arrFields[intNo] != "") ? arrFields[intNo] : "";}
    strRows += "</TABLE>\n";

    return strRows;
}

History

• 25 July 2006 - updated download