Click here to Skip to main content
15,798,592 members
Articles / Web Development / IIS
Article

Database Viewer and Updater for any Database

Rate me:
Please Sign up or sign in to vote.
4.29/5 (12 votes)
25 Jul 20061 min read 97.2K   2.1K   93   15
View and update any database table and metadata. Includes sample databases.

Image 1

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

Image 2

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.

C#
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

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
Israel Israel
Jazz Drummer (1½/2½/3.5++), Writer, Grandfather, Possibly World's Oldest Web Developer, Rebel, Anti-globalization, Prisoner of conscience

Comments and Discussions

 
QuestionGreate Pin
Aladár Horváth4-Aug-15 4:57
professionalAladár Horváth4-Aug-15 4:57 
GeneralGreat Pin
Vitali Halershtein4-Jun-08 6:39
Vitali Halershtein4-Jun-08 6:39 
Generalopen database Pin
georgeen29-May-07 11:11
georgeen29-May-07 11:11 
QuestionAccess databse connectivity Pin
hygeenams18-Jan-07 19:51
hygeenams18-Jan-07 19:51 
QuestionHow to connect to Oracle DB?? Pin
GsPandian27-Dec-06 23:52
GsPandian27-Dec-06 23:52 
GeneralWhen I substitute a different Access database I get an error Pin
Gixxer1k8-Oct-06 11:49
Gixxer1k8-Oct-06 11:49 
GeneralWhy you use SQLConnection in your example Pin
jpazgier26-Jul-06 7:45
jpazgier26-Jul-06 7:45 
GeneralRe: Why you use SQLConnection in your example Pin
avronp29-Jul-06 11:07
avronp29-Jul-06 11:07 
QuestionError when accessing Access Table: MSysAccessObjects Pin
chengkc25-Jul-06 4:41
chengkc25-Jul-06 4:41 
AnswerRe: Error when accessing Access Table: MSysAccessObjects Pin
avronp29-Jul-06 11:00
avronp29-Jul-06 11:00 
GeneralRe: Error when accessing Access Table: MSysAccessObjects Pin
chengkc30-Jul-06 7:16
chengkc30-Jul-06 7:16 
GeneralMissing Files Pin
-JeRoK-18-Jul-06 10:22
-JeRoK-18-Jul-06 10:22 
GeneralRe: Missing Files Pin
-JeRoK-18-Jul-06 12:49
-JeRoK-18-Jul-06 12:49 
GeneralRe: Missing Files Pin
yermalpavan24-Jul-06 20:46
yermalpavan24-Jul-06 20:46 
GeneralRe: Missing Files Pin
avronp29-Jul-06 10:55
avronp29-Jul-06 10:55 
If there is any reference to a masterpage remove that page from the application. It is an unnecessary page.

I uploaded a new zipped version of the application which doesn't include any references to a masterpage. Perhaps the server had not updated the zip file which you downloaded. You should try downloading again.

If such a reference still exists please post the error and the page on which it appears.

Avron Polakow

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.