Dynamically access a database through a Web Application

• Download source - 37 KB

Introduction

Maintaining a database off-site can be difficult, especially if you are not at home. I wanted to learn C# and ASP.NET so I created a .NET Web Service, utilizing the OLE DB interface, that allows you to view and edit databases, and do it all dynamically!

It allows you to:-

• Log on to a database server (e.g. MS SQL, Access, Oracle).
• Build connection string dynamically.
• Get a list of Databases or Schemas.
• Get a list of Tables, Views or Procedures.
• C++ calls for Providers not fully implemented.
• View, edit, insert, delete and sort entries.
• Check-Boxes dynamically added in Edit mode.
• Access another database/table quickly.

Logon Web Page

The OLE DB connection string is built from the UserID and Password entry fields, the Provider ListItem box and the Server/Filename 'INPUT' field. I have added an extra field into the The Provider ListItem Collection Editor's 'Value' Property. If an extra parameter needs to be added to the connection string (eg. Oracle needs 'OLEDB.NET=TRUE'), it is inserted after the provider label delimited by the '|' character. The Server/Filename has a browse button to allow browsing for a MDB file. The Server/Filename 'INPUT' and Password fields do not save their values between trips for Security reasons.

The connection string is derived from a API call to a C++ routine, DBConnstr(), in the DLL WebAppUtil. This code was modified from some sample code found in Help. It gets the providers CSLID, the IDBProperties is populated with the User ID, Password, and Server/Filename; the DBPROMPT_NOPROMPT value is set to prevent the OLE DB Connection dialog from popping up. A database instance is created with IDataInitialize and then the interface is queried with IDBProperties pointer. The IDBProperties properties are set, the IDBInitialize property is initialized and then the Initialization string is extracted. This way the Connection String can be dynamically generated for each interface. The getHRtext() routine also came from the help and extracts a text message from the HRESULT return code if their is an error. The error message is put in a popup window utilizing an HIDDEN field on both pages that is invoked by a Java script in the HTML code.

The Get Databases/Schemas button will get a list of databases/schemas available on the server and insert them into the DBDropDownList box. Before we can fill in the DropDown ListBox, it needs to be determined if Schema's or Catalog's are supported. The code first checks for a Catalog name because Catalog's are not always supported. If Catalogs are supported, they are used, otherwise Schema's are loaded into the DropDown Listbox. The names are stored in a string array and then stored in a Session variable for page reloads and round trips.

OleDbConnection statConn = new OleDbConnection(connStr);
...

statConn.Open();
// get schema

schema = statConn.GetOleDbSchemaTable(
    OleDbSchemaGuid.Schemata,new object[] {null});
// check if Databases supported		

if (schema.Rows[0]["CATALOG_NAME"].ToString() != "")  
{   // there are catalogs, use instead of schema

    DB = statConn.GetOleDbSchemaTable(OleDbSchemaGuid.Catalogs,
                                      new object[] {null});
    LBNames = new string[DB.Rows.Count];    
    // insert Catalog names into drop down list 

    // and string array for Session variable

    while (j < DB.Rows.Count) 
    {	
       LBNames[j] = DB.Rows[j]["CATALOG_NAME"].ToString();
       DBDropDownList.Items.Add(LBNames[j++]);
    }
    ...
}
else    // use schema names

{   
    // need to re-inquire with user name

    schema.Dispose();	
    schema = statConn.GetOleDbSchemaTable(OleDbSchemaGuid.Schemata,
                new object[] {null,UserIDTextBox.Text.ToString()});

    // insert Schema names into drop down list 

    // and string array for Session variable

    LBNames = new string[schema.Rows.Count];
    while (j < schema.Rows.Count) 
    {
        LBNames[j] = schema.Rows[j]["SCHEMA_NAME"].ToString();
        DBDropDownList.Items.Add(LBNames[j++]);
    }
    ...
}
Session["myDBs"] = LBNames; // saved for return trip

The "Select Item" RadioButtonList causes the "Get ITEM" buttons text to change for that Item along with the Label for the ItemsDropDownList. The selected item determines which API will be called to fill in the ItemsDropDownList and the ArgumentsTextBox. When the procedure parameters are displayed, each text field should be replace with a parameter.

The "use C++ code to get Item info" Check-Box causes the "Get ITEMS" button to use the C++ routines in the DLL, DBGetTables(), DBGetViews() and DBGetProcs(). The DBGetIndexes() and DBQuote() are used by the "Execute" button. Some providers (e.g. MS Jet) do not support all the new API's, it seems. These routines use the CTable, CView, CProcedures, CProcedureParameters and the CIndexes classes to get the information. Also, GetLiteralInfo() of the CComQIPtr<IDBInfo> template is used to get the Quote character. I'll leave this code for you to explore.

The "Get ITEMS" button will get a list of items (e.g. tables, views or procedures) available for that database/schema and insert them into the ItemsDropDownList box. The code first gets the Quote Suffix and Prefix characters and then a list of items. Either the Catalog or the Schema name is null at this point. If there is a Space in the Item name, the Quote Characters are added. If the "System Tables" Check-Box is checked, only system items are retrieved (except for procedures).

queryType = TypeRadioButtonList.SelectedItem.ToString();
...

// get DB's Quote chars

schemaTable = statConn.GetOleDbSchemaTable(
                        OleDbSchemaGuid.DbInfoLiterals,null);
DataRow[] dr = schemaTable.Select("LiteralName LIKE 'Quote_*'");
quotePrefix = dr[0]["LiteralValue"].ToString();
quoteSuffix = dr[1]["LiteralValue"].ToString();
Session["myquoteprefix"] = quotePrefix;
Session["myquotesuffix"] = quoteSuffix;
schemaTable.Dispose();

// get item names

switch (queryType)
{
    case "Table" :
        columnName = "TABLE_NAME";
        schemaTable = statConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                       new object[] {selectedDB,selectedSchema,null, 
                        SysTblsCheckBox.Checked ? "SYSTEM TABLE" : "TABLE"});
        break;
    case "View" :
        columnName = "TABLE_NAME";
        schemaTable = statConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                       new object[] {selectedDB,selectedSchema,null,
                        SysTblsCheckBox.Checked ? "SYSTEM VIEW" : "VIEW"});
        break;
    case "Procedure" :
        columnName = "PROCEDURE_NAME";
        schemaTable = statConn.GetOleDbSchemaTable(OleDbSchemaGuid.Procedures,
                       new object[] {selectedDB,selectedSchema,null,null});
        break;
}
items = new string[schemaTable.Rows.Count];
int i = 0;
while (i < schemaTable.Rows.Count) 
{	
   items[i] = schemaTable.Rows[i][columnName].ToString();
   i++;
}
...
// fill in Item DD listbox

for (int i = 0; i < items.Length; i++) 
{
    itemName = items[i];
    if (queryType == "Procedure")
    {// it item name has a semicolon, reformat it

        index = itemName.IndexOf(';');
        if (index >= 0) 
            itemName = itemName.Substring(0,index);
    }
    // it item name has a space, reformat it

    index = itemName.IndexOf(' ');
    if (index >= 0)
        itemName = quotePrefix + itemName + quotePrefix;
    ItemsDropDownList.Items.Add(itemName);
    // save item names for return to page

    LBNames[i] = itemName;
}
itemName = ItemsDropDownList.Items[0].ToString();
ArgumentsTextBox.Text = "";
procParmNames = null;
switch (queryType)
{
    case "Table" :
    case "View" :						
        itemName = ItemsDropDownList.SelectedItem.Text;
        SQLstatement = "SELECT * FROM " + itemName;
        ArgumentsTextBox.Text = SQLstatement;
        ArgumentsLabel.Text = "SQL Statement";
        break;
    case "Procedure" :
        GetProcedureParms();
        break;
}
...

schemaTable.Dispose();

The "Execute" button finds a key in the item and loads the DataWebForm page. If no Key is found, the Insert DataGrid is not displayed and the "Update" and "Delete" buttons will perform no actions on the "DataWebForm" page. If two or more Key names are returned for in one key field, they are separated by a ',', only the first is used. If there is a Space in the Index name, the Quote Characters are added.

// find primary else secondary index name

indexName = "";
schemaTable = indexConn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys,
                new object[] {selectedDB,selectedSchema,itemName});
if (schemaTable.Rows.Count > 0) 
    indexName = schemaTable.Rows[0]["COLUMN_NAME"].ToString();
else
    indexName = "";
schemaTable.Dispose();
...

// remove multiple keys, only need one key 'cause 

// we keep track of individual rows

index = indexName.IndexOf(',');
if (index >= 0)
    indexName = indexName.Remove(index,indexName.Length - index);
// it KEY name has a space, reformat it

    index = indexName.IndexOf(' ');
if (index >= 0)
    indexName = quotePrefix + indexName + quotePrefix;  
...

// now display second web page

Server.Transfer("DataWebForm.aspx");

The SQL Statement or Procedure parameters are generated each time the Item selection is changed. The AutoPostBack is set True causing the ItemsDropDownList_SelectedIndexChanged() routine to be called where the Select statement/Procedure parms are rebuilt. The SQL Statement can be modified to limit the query before the "Execute" button is pushed, BUT, modify at your own risk. When the procedure parameters are displayed, each text field should be replace with a parameter.

Error popup window

There is an error popup window in HTML, embedded in a Java script, that utilizes a hidden Text field, on both pages. If there is text in the field, the error box is displayed.

Data Web Page

There are two DataGrids, one for inserting a new record and the other for Editing or Deleting records. There is also a read only field representing the SQL statement/procedure used to build the grids. The Edit/Delete DataGrid is filled from a OleDataAdapter initialized with the SQL Statement or Procedure from the Logon page. The Insert, Update and Delete Command are dynamically built using the OleCommandBuilder, if a index was found on the LogonWebPage. The Quote characters are retrieved from the Logon page and set into the OleCommandBuilder.

custCB.QuotePrefix = sourcepage.GetQuotePrefix;
custCB.QuoteSuffix = sourcepage.GetQuoteSuffix;
// create and set dynamically Insert, Update & Delete commands !!

oleDbDataAdapter.InsertCommand = custCB.GetInsertCommand();
oleDbDataAdapter.UpdateCommand = custCB.GetUpdateCommand();
oleDbDataAdapter.DeleteCommand = custCB.GetDeleteCommand();

The Update button causes the offset into the dataset to be calculated by multiplying the current page number times the data grid size page size and then adding the row index. While looping through each column of data for the row, the control type is checked to see if it is a "System.Web.UI.WebControls.TextBox" or "System.Web.UI.WebControls.CheckBox" so that the data can be extracted correctly from either a TextBox or a CheckBox. As the data is being put into the DataRow, fields (e.g.. binary, image, sql_variant, timestamp, uniqueID, varbinary) that were not displayed in the DataGrid, are skipped, there data remains unchanged. If the row has changed, an Update is performed. Note that the DataRow (for update) is now acquired from the DataViewManager because that is where the sorted view is found.

...
int dsItemIndexOffset = (DataGridDB.CurrentPageIndex * 
                          DataGridDB.PageSize) 
                         + e.Item.ItemIndex;

// Gets the value of the key field 

// of the row being updated

string key = DataGridDB.DataKeys[e.Item.ItemIndex].ToString();
...

TextBox tb;

// get original row from the view for update

DataRow dr = dataSet.DefaultViewManager.DataSet.Tables 
              [0].DefaultView[dsItemIndexOffset].Row;
// check type, looking for checkbox

for (int i = dbTableColumnOffset; i < e.Item.Cells.Count; i++)
{
    string c = e.Item.Controls[i].Controls[0].GetType().ToString();
    if (c == "System.Web.UI.WebControls.TextBox") 
    {
        tb = (TextBox)(e.Item.Cells[i].Controls[0]);
        categoryField = tb.Text;
    }
    else if (c == "System.Web.UI.WebControls.CheckBox")
    {
        bool b = 
            ((CheckBox)(
            e.Item.Cells[i].Controls[0])).Checked;
        if (b)
            categoryField = "True";
        else
            categoryField = "False";
    }
    else
    {
        LogErrorMsg("Unkown field type in Grid");   
            // shouldn't happen

        return;
    }

    // check if there is a non supported field 

    // (better allow NULLS), 

    // if so, skip over it in the DataRow

    do 
    {   // loop until a supported row is found

        fieldType = dataSet.Tables[0].Columns 
                     [i-dbTableColumnOffset].DataType.ToString();
        if (fieldType != "System.Byte[]" && 
            fieldType != "System.Object" &&
            fieldType != "System.Guid")
            break;
        i++;
    } while(i < dr.Table.Columns.Count+dbTableColumnOffset);

    // update only if field has changed

    if (!dataSet.Tables[0].Rows[dsItemIndexOffset].ItemArray 
               i-dbTableColumnOffset].Equals(categoryField))
        dr[i-dbTableColumnOffset] = categoryField;
}

if (dataSet.HasChanges())   
{	 // then do update to DataSet and DB

    try 
    {
        oleDbDataAdapter.Update(dataSet);
        dataSet.AcceptChanges();
    }
    catch (Exception er) 
        ...

The Insert DataGrid is initialized with the same query but does not get any records, this is done so that the column headings can dynamically be retreived. A new blank DataRow is added to it's associated dataset and the Grid is put in the edit mode. The Delete button is enabled but renamed to Insert. The Insert button creates a new DataRow and adds the data from the Insert grid into the DataRow. It skips over non-supported types so those data elements need to support nulls! The new DataRow is then inserted into the other DataGrids dataset and a Update is performed. The Check-Boxes are dynamically added when the grids are put in the Edit mode. The Grids column type is found and the TextBox is replaced with a CheckBox. The DataGridDB_ItemCreated is called at the end of the edit with no DataItem, do not know why, but putting in a dummy CheckBox keeps it from blowing up.

private void DataGridDB_ItemCreated(object sender, 
    System.Web.UI.WebControls.DataGridItemEventArgs e)
{
   // checkbox only on edit

   if (e.Item.ItemType == ListItemType.EditItem ) 
   {
      // after an edit is completed or canceled, 

      // get called with no DataItem's

      // found that a dummy CheckBox inserted at 

      // the same offset keeps

      // everyone happy ???

      if (e.Item.DataItem == null)    
      {               
         DataSet ds = (DataSet)Session["mydataset"];
         int i = 0;
         do { // if there is a boolean field 

              // in the dataset, add to Controls

            if (ds.Tables[0].Columns[i].DataType.ToString() == 
                  "System.Boolean")
            {
               CheckBox cb = new CheckBox();
               e.Item.Controls[i+dbTableColumnOffset].Controls.Add(cb);
               e.Item.Controls[i+dbTableColumnOffset].Controls.RemoveAt(0);
            }
         } while(++i < ds.Tables[0].Columns.Count);
      }
      else    // normal

      { 
         for (int i = 0; 
              i < e.Item.Controls.Count-insertTableColumnOffset; 
              i++)
         {
            try
            {
               string itemType = dataSet.DefaultViewManager.
                                   DataViewSettings[0].Table.
                                   Columns[i].DataType.ToString();
               if (itemType == "System.Boolean")
               {
                  CheckBox cb = new CheckBox();
                  // put data field name in ID field 

                  // for identification during binddata

                  cb.ID = ((DataTable)((DataView)((DataRowView)e.Item.
                              DataItem).DataView).Table).Columns[i].
                              ColumnName.ToString();
                  cb.DataBinding += new EventHandler(this.BindCheckBoxData);
                  e.Item.Controls[i+insertTableColumnOffset].Controls.Add(cb);
                  e.Item.Controls[i+insertTableColumnOffset].Controls.
                   RemoveAt(0);
                }
            }
            catch (Exception er) 
            {
               LogException(er);
            }
         }
      }
        ...

}

The this.BindCheckBoxData() came from Shaun Wildes 'Adding a CheckBox column to your DataGrid' CodeProject. The column name is stored in the check-boxes ID field.

private void BindCheckBoxData(object sender, EventArgs e)
{
    CheckBox box = (CheckBox) sender;
    DataGridItem container = (DataGridItem) box.NamingContainer;
    box.Checked = false;
    string data = ((DataRowView)container.DataItem).ToString();
    Type t = ((DataRowView)
     container.DataItem).DataView.Table.Columns.DataType;
    if (data.Length>0)
    {
        switch (t.ToString())
        {
        case "System.Boolean":
            if (( data == "True") || (data == "true"))
            {
                box.Checked = true;
            }
            break;
        default:
            break;
        }
    }
}

Sort: The DataGridDB_SortCommand() is called by clicking on a column name and then the Sort field is set in the DefaultViewManager. When updating a row, the row has to be found through the DefaultViewManager which has the sorted dataset that matched the data displayed in the DataGrid, see DataGridDB_UpdateCommand() by the "get original row from the view for update" comment.

private void DataGridDB_SortCommand(...)
{
    // dispose of dataset when changing sort 

    // seems to have problems otherwise, 

    // remembers old sort or else I do

    dataSet.Dispose();
    dataSet = new DataSet();
    oleDbDataAdapter.Fill(dataSet);
    dataSet.DefaultViewManager.DataViewSettings[0].Sort = 
        e.SortExpression.ToString();
    DataGridDB.DataSource = dataSet;
    DataGridDB.DataBind();
    Session["mydataset"] = dataSet;
    Session["mydataadapter"] = oleDbDataAdapter;
}

There is a "New Table" link that reloads the Logon Page with the Session variables reloaded.

Deployment

Included is a WebSetup project. It was created using Majid Shahabfar's Deploying Web Applications using Visual Studio .NET project.

Conclusion

This is my third and probably final incantation. I have delusions of putting this all into a template, maybe when it snows down here :-D:. The Oracle provider for Oracle is case sensative, FYI. You should be able to add your own provider to the Provider DropDown ListItems, don't forget the two '|'s at the end of the Value field. If you see how do get around the few work-arounds I made, let me know and I'll change the code. Let me know what you think about all this. If you think this article and code are useful, be sure to vote!

History

14 Sep 2002 - updated source code.
25 Nov 2002 - added insert DataGrid, selectable Provider
05 Dec 2002 - added views, procedures and sort