Dynamically access a database through a Web Application
4.70/5 (19 votes)
Sep 6, 2002
8 min read
287697
4022
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
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