Click here to Skip to main content
15,860,859 members
Articles / Web Development / ASP.NET
Article

Dynamically access a database through a Web Application

Rate me:
Please Sign up or sign in to vote.
4.70/5 (22 votes)
24 Nov 20028 min read 284.1K   4K   119   35
Using an ASP.NET Web application to access your database remotely.

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

Image 1

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.

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

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

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

Image 2

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.

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

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

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

C#
private void BindCheckBoxData(object sender, EventArgs e)
{
    CheckBox box = (CheckBox) sender;
    DataGridItem container = (DataGridItem) box.NamingContainer;
    box.Checked = false;
    string data = ((DataRowView)container.DataItem)<box.ID>.ToString();
    Type t = ((DataRowView)
     container.DataItem).DataView.Table.Columns<box.ID>.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.

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

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
United States United States
Jim is a programmer with 22 years experience starting with UNIX/C, assembly, then DOS, OS/2, AIX, Windows Visual Studio and learning .NET.

Comments and Discussions

 
GeneralMy vote of 5 Pin
madhu bhardwaj4-Oct-10 1:00
madhu bhardwaj4-Oct-10 1:00 
GeneralDownloading access database Pin
Rohit Dixit30-Jun-09 0:53
Rohit Dixit30-Jun-09 0:53 
GeneralMy vote of 1 Pin
peter1111198820-Jun-09 3:46
peter1111198820-Jun-09 3:46 
Questionany updates? Pin
Abdul Rahman El Habboub11-Feb-09 2:45
Abdul Rahman El Habboub11-Feb-09 2:45 
Generaldll file is missing Pin
mandeepkaur3-Jul-08 3:08
mandeepkaur3-Jul-08 3:08 
GeneralRe: dll file is missing Pin
James Constable4-Jul-08 10:18
James Constable4-Jul-08 10:18 
GeneralA lot of code which does not work Pin
gerhard4515-Feb-06 21:05
gerhard4515-Feb-06 21:05 
GeneralRe: A lot of code which does not work Pin
James Constable4-Mar-06 14:02
James Constable4-Mar-06 14:02 
GeneralThere is a a missing Dll Pin
oakcool1-Mar-05 6:55
oakcool1-Mar-05 6:55 
GeneralRe: There is a a missing Dll Pin
James Constable4-Mar-06 13:59
James Constable4-Mar-06 13:59 
GeneralRegarding listbox in JSP Pin
Member 896807612-Feb-05 18:46
Member 896807612-Feb-05 18:46 
QuestionWhat about MS Access! Pin
Anonymous11-Jun-03 2:36
Anonymous11-Jun-03 2:36 
QuestionHow I can get the type length Pin
RoliS30-Apr-03 19:13
RoliS30-Apr-03 19:13 
GeneralComplicated program to run a simple job Pin
Member 12733322-Apr-03 12:32
Member 12733322-Apr-03 12:32 
GeneralNot a simple job, just an example Pin
James Constable24-Apr-03 5:33
James Constable24-Apr-03 5:33 
GeneralConnecting ACCESS database remotely Pin
Intrumov3-Dec-02 3:31
Intrumov3-Dec-02 3:31 
GeneralRe: Connecting ACCESS database remotely Pin
GreyGremlin3-Dec-02 4:25
GreyGremlin3-Dec-02 4:25 
GeneralRe: Connecting ACCESS database remotely Pin
James Constable3-Dec-02 12:02
James Constable3-Dec-02 12:02 
GeneralRe: Connecting ACCESS database remotely Pin
Andi Fleischmann23-May-03 12:46
Andi Fleischmann23-May-03 12:46 
GeneralRuntime Error Pin
zoomba30-Nov-02 8:40
zoomba30-Nov-02 8:40 
GeneralRe: Runtime Error Pin
James Constable30-Nov-02 13:14
James Constable30-Nov-02 13:14 
Generalgreat thing Pin
Jacek M Glen27-Nov-02 15:32
professionalJacek M Glen27-Nov-02 15:32 
GeneralThanks Pin
James Constable30-Nov-02 13:12
James Constable30-Nov-02 13:12 
GeneralFor MS SQL Server only Pin
Anonymous11-Oct-02 4:40
Anonymous11-Oct-02 4:40 
GeneralRe: For MS SQL Server only Pin
James Constable20-Oct-02 3:22
James Constable20-Oct-02 3:22 

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.