Creating a DatabaseProvider
This article will help walk you through the steps to create a database provider using an XML DataSet provider as an example. Full source for the code in this article is available here.
Implementing DatabaseProvider
We’ll start by making a new project that implements DatabaseProvider.
- Start Visual Studio and create a new C# “Class Library” project
- Add a reference to “Microsoft.Web.Managment.DBManager”
- Create a class that will be your database provider class. Have it implement “Microsoft.Web.Management.DBManager.DatabaseProvider”. Tell Visual Studio to implement the abstract class. It will fill out all the methods with “throw new NotImplementException()” which is fine for now.
- We’ll need to strongly type our assembly so go into the project properties and then the “Signing” tab. Click the “Sign the assembly” checkbox and create a new strong name key file. Give the key file a name and uncheck “Protect my key file with a password".
- Build the assembly.
Now we’ve got the skeleton of a database provider done. We’ll register our database provider with DB Manager and then start implementing functions.
Registering the DatabaseProvider
Once you’ve written a database provider for DB Manager you’ll have to register it before DB Manager will start using it.
- Install your database provider assembly in the GAC. This can be done using “gacutil”.
- Open the “%windir%\system32\inetsrv\config\administration.config” file.
- Find the <system.webServer/management/dbManager/DBProviders> collection.
- Create a new entry for your provider. You will have to specify two values.
The “type” will be the fully qualified type which will include the assembly name specification, a namespace specification and a type name.
The “name” for your provider should match the “providerName” attribute of any connection string that will use your provider. DB Manager uses the “providerName” attribute to map a connection string to a DB Manager database provider. If the “providerName” attribute isn’t specified for a connection string DB Manager will use the provider specified by the “defaultDBProvider” attribute of the DB Manager configuration section.
For example if we were writing a provider for Microsoft SQL Server we would use “System.Data.SqlClient” as our “name” because SQL Server connection string will use “System.Data.SqlClient” as their providerName. In our XML DataSet example we’ll just make something up because there is no XML DataSet ADO.NET provider (the providerName attribute really specifies the ADO.NET provider to use to access the underlying data store). So our entry will look something like:
<provider name="XMLProvider" type="XMLDatabaseProvider.XMLDataSetProvider, XMLDatabaseProvider, Version=0.0.0.0, Culture=neutral, PublicKeyToken=d6c672ae03e4c650" />
If all was successful you should be able to start up Inetmgr, navigate to DB Manager, click “Add Connection” and you provider should show up under the “Database provider type” drop down. If you try selecting your database provider you’ll get an error message telling you the method or operation isn’t implemented. We’ll fix that next.
Implementing ConnectionStringArguments
For almost every call your database provider will receive you’ll also get a connection string. The connection string specifies how to connect to a database. For example a SQL Server connection string might look like:
Server=server1;Database=customer;User ID=sqlServerUser;Password=test
The highlighted words are the keywords for which a user must specify a value. This list of keywords is what your database provider should return when implementing the ConnectionStringArgument property.
For our XML DataSet provider we’ll just ask the user to specify one value which is the location of the database. Our implementation will look like:
public override ICollection<ConnectionArgument> ConnectionStringArguments {
get {
List<ConnectionArgument> connectionArgList = new List<ConnectionArgument>();
ConnectionArgument serverArg = new ConnectionArgument();
serverArg.Name = "dataFile";
connectionArgList.Add(serverArg);
return connectionArgList;
}
}
Build your DLL again, update the version in the GAC and start Inetmgr again. Add a new connection string and select our provider. Now instead of receiving an error you’ll see the connection asks for the “dataFile”. Fill in “C:\ CustomProviderDb.xml” and drop the CustomProviderDb.xml file on your C: drive. Give the connection string a name and click OK.
Now if you try to expand your newly created connection your should see a not implemented error again.
Implementing TestConnection
It’s time to implement the test connection method. TestConnection() receives a connection string and makes sure it’s valid. If the string is not valid TestConnection should throw an exception. If it is valid then we simply return with no error.
For our XML DataSet provider we’ll verify there’s a file at the location specified by the user. Our TestConnection() then looks like:
public override void TestConnection(string connectionString) {
if (!File.Exists(GetDatabaseLocationFromConnectionString(connectionString))) {
throw new InvalidOperationException("Could not find XML database");
}
}
Where GetDatabaseLocationFromConnectionString() is a private helper method used to extract the file location from the entire connection string:
private static string GetDatabaseLocationFromConnectionString(string connectionString) {
int indexOfDatabaseName = connectionString.IndexOf("dataFile=") + 9;
if (indexOfDatabaseName < 9 || connectionString.Length <= indexOfDatabaseName) {
throw new InvalidOperationException("Invalid connection string");
}
return connectionString.Substring(indexOfDatabaseName);
}
Some additional validation should probably be done on the connection string but for now this will work.
Build again, update the GAC with your newest version and start Inetmgr again. Navigate to DB Manager and expand your user created connection string again. This time instead of a not implemented exception you should see the database connection expand if you’ve set up everything correctly. However trying to expand the “Tables” node will give you an exception again.
Implementing GetTables
Now it’s time to return a list of the tables available to the client. We’ll define a helper method to load the dataset specified by the connection string: private DataSet LoadDataSetFromXml(string xmlLocation)
{
// Read the XML file into a new DataSet
using (FileStream fsIn =
new FileStream(Environment.ExpandEnvironmentVariables(xmlLocation),
FileMode.Open, FileAccess.Read)) {
using (XmlTextReader xtr = new XmlTextReader(fsIn)) {
DataSet dataSet = new DataSet();
dataSet.ReadXml(xtr, XmlReadMode.ReadSchema);
xtr.Close();
fsIn.Close();
return dataSet;
}
}
}
Now we can implement GetTables(). Get tables returns a list of the database in the database but doesn’t return any information about the internal structure of the table itself or any of the table data. When the client needs that information it will call GetTableInfo() or GetData() on a specific table.
public override ICollection<Table> GetTables(string connectionString)
{
DataSet dataSet = LoadDataSetFromXml(GetDatabaseLocationFromConnectionString(connectionString));
List<Table> tables = new List<Table>();
// loop through all dataTables and construct a Table object for each
// the Table object doesn't contain any information about the table
// structure itself that information is returned by GetTableInfo()
foreach (DataTable dataTable in dataSet.Tables) {
Table table = new Table();
table.Name = dataTable.TableName;
// for the example we won't use the schema property at all
tables.Add(table);
}
return tables;
}
Build your DLL again and update the version in the GAC. Now when we connect to our database and expand he tables node you should see a list of the tables in your database assuming your XML database has at least one table already in it.
Implementing GetData
When the user right clicks on one of the databases and selects “Show Table Data” your database provider’s GetData() function will be called. This should read the data from the database and return it to the client.
GetData returns a QueryResult which is made up of two parts. The first is information about the columns of table which is the ColumnMetadata property of the query result. The ColumnMetadata has information like whether the column is read only, the maximum length of the column and the column name. The second member is the QueryResults member which is an ArrayList of object arrays. Each object array corresponds to a row in the database.
One thing to remember is that all the values you return to the client must be serializable by Inetmgr. For example a Decimal value would need to be converted to a double or else you’ll receive an error. One type that you’ll frequently encounter that isn’t serializable is DBNull. DB Manager uses a null value in place of DBNull when passing values between the client and the server.
Therefore our GetData() method looks like:
// Get the rows of the specified table. Called when "Show Table Data" is clicked.
public override QueryResult GetData(string connectionString, string tableName, string schema)
{
DataSet dataSet = LoadDataSetFromXml(GetDatabaseLocationFromConnectionString(connectionString));
// the result will be a QueryResult
QueryResult queryResult = new QueryResult();
DataTable table = dataSet.Tables[tableName];
if (table == null) {
throw new InvalidOperationException("Could not find table " + tableName);
}
// now we need to build up the QueryColumnMetadata part of the QueryResult
foreach (DataColumn dataColumn in table.Columns) {
QueryColumnMetadata m = new QueryColumnMetadata();
m.Name = dataColumn.ColumnName;
// if set > 0 this will stop limit the user to ColWidth characters for data in this column
m.ColWidth = 0;
// whether the column allows nulls
m.IsNullable = dataColumn.AllowDBNull;
// this will prevent the user from entering data into this column
m.IsComputed = dataColumn.ReadOnly;
queryResult.ColumnMetadata.Add(m);
}
// get all the data in the table
foreach (DataRow row in table.Rows) {
// each row is passed as an object[] to the client
object[] itemData = new object[table.Columns.Count];
for (int i = 0; i < table.Columns.Count; i++) {
object value = row[i];
// DBNull isn't serializable so we just use null
// to signify a DBNull value
if (value is DBNull || value == null) {
continue;
}
// convert anything that isn't serializable to a
// serializable form to send to the client
if (value is Decimal) {
itemData[i] = (double)((Decimal)value);
}
else {
itemData[i] = value;
}
}
// add each row to the results
queryResult.QueryResults.Add(itemData);
}
return queryResult;
}
Implement this method, rebuild your DLL, update the GAC and start Inetmgr. Now when you select “Show Table Data” you can view the data in your table.
Implementing InsertRow
The next step is to allow users to add a new row. InsertData takes an argument that is a list of ColumnDataInfo’s. Each ColumnDataInfo represents data for one column in a row. The ColumnDataInfo contains:
- The name of the column to which the data belongs
- The column’s original value (useful for the EditData method)
- The column’s new value
- IsEdited which tells you if a user specified this value or not
The reason for the IsEdited property is because we’re already using null to signify DBNull. Without IsEdited you would not be able to tell if a null meant the user specified NULL or the user did not specify any value. It is important to know whether or not a user specified a value because for a particular column it may be an error to specify any value.
InsertRow() also returns and object[]. This return object array represents the values of the inserted column after the insert. This may be different than the values passed into the InsertRow function. For example if a column has a default value it will receive a value even if the user did not specify one. This information is given back to the client via the return value of InsertRow. If you don’t want the client to automatically update the values of the inserted row simply return null.
Now that we’re changing the table data we’ll also need to save the data back out to an XML file:
private void SaveDataSetToXml(DataSet dataSet, string xmlLocation) {
// commit any changes to the dataset
dataSet.AcceptChanges();
// Save the changed DataSet to a file.
using (FileStream fsOut =
new FileStream(Environment.ExpandEnvironmentVariables(xmlLocation),
FileMode.Truncate,
FileAccess.Write)) {
using (XmlTextWriter xtw = new XmlTextWriter(fsOut, Encoding.Unicode)) {
dataSet.WriteXml(xtw, XmlWriteMode.WriteSchema);
xtw.Close();
fsOut.Close();
}
}
}
Now we’re ready to implement InsertRow(). Notice again we’ll have to convert values we’re returning to the server that won’t serialize.
public override object[] InsertRow(string connectionString, string tableName, string schema, IList<ColumnDataInfo> columnDataInfoList)
{
string dataFile = GetDatabaseLocationFromConnectionString(connectionString);
DataSet dataSet = LoadDataSetFromXml(dataFile);
// Insert a new row in the table. First fill up the row.
DataRow newRow = dataSet.Tables[tableName].NewRow();
for (int i = 0; i < columnDataInfoList.Count; i++) {
// only set values the user actually specified
if (columnDataInfoList[i].IsEdited) {
newRow[columnDataInfoList[i].ColumnName] = columnDataInfoList[i].NewValue;
}
}
// Add the new row to the table
dataSet.Tables[tableName].Rows.Add(newRow);
// the Rows.Add call filled out any computed values for us so we
// can just read everything from the newRow object
object[] result = null;
result = new object[columnDataInfoList.Count];
// Move each cell in the row to the result parameter to return.
for (int i = 0; i < columnDataInfoList.Count; i++) {
object value = newRow[columnDataInfoList[i].ColumnName];
if (value is DBNull) {
result[i] = null;
}
else if (value is Decimal) {
result[i] = (double)((Decimal)value);
}
else {
result[i] = value;
}
}
//Save data to XML file
SaveDataSetToXml(dataSet, dataFile);
return result;
}
Implementing EditRow
Edit row is very similar to insert row but we’ll have to located the row in the dataset first. We’ll make a function that will find our row for us:
private DataRow[] GetMatchingRows(DataTable table, IList<ColumnDataInfo> columnDataInfoList) {
// build up select string
StringBuilder selectString = new StringBuilder();
foreach (ColumnDataInfo columnDataInfo in columnDataInfoList) {
if (selectString.Length > 0) {
selectString.Append(" AND ");
}
if (columnDataInfo.OriginalValue == null) {
selectString.Append(columnDataInfo.ColumnName + " is NULL");
}
else {
selectString.Append(columnDataInfo.ColumnName + "='" + columnDataInfo.OriginalValue + "'");
}
}
DataRow[] dataRowArray = table.Select(selectString.ToString());
return dataRowArray;
}
Notice we use all the original column values to locate the table but we’ll only update the ones the user touched.
When we implement EditRow we’ll want to make sure GetMatchingRows only returned one row. If there are two rows with the exact same values we don’t know which the user wanted to edit so we’ll just throw an error and tell the user we couldn’t do the update. The rest is straight forward:
public override void EditRow(string connectionString, string tableName, string schema, IList<ColumnDataInfo> columnDataInfoList)
{
string dataFile = GetDatabaseLocationFromConnectionString(connectionString);
DataSet dataSet = LoadDataSetFromXml(dataFile);
DataTable table = dataSet.Tables[tableName];
// this loops through every row in the table to make sure only one row matches
DataRow[] matchingRows = GetMatchingRows(table, columnDataInfoList);
// throw exception if we couldn't find the row or there were multiple rows
if (matchingRows.Length == 0) {
throw new InvalidOperationException("Could not find row to edit");
}
else if (matchingRows.Length > 1) {
throw new InvalidOperationException("No row was updated. The data was not committed. The row values(s) updated or deleted either do not make the row unique or they alter multiple rows");
}
for (int j = 0; j < columnDataInfoList.Count; j++) {
// don't specify values the user didn't touch
if (columnDataInfoList[j].IsEdited) {
matchingRows[0][columnDataInfoList[j].ColumnName] = columnDataInfoList[j].NewValue;
}
}
//Save data to the XML file
SaveDataSetToXml(dataSet, dataFile);
}
Implementing DeleteRow
The last method we need to write to deal with our table data is DeleteRow(). After writing the code to find a row for EditRow() this becomes trivial:
public override void DeleteRow(string connectionString, string tableName, string schema, IList<ColumnDataInfo> columnDataInfoList) {
string dataFile = GetDatabaseLocationFromConnectionString(connectionString);
DataSet dataSet = LoadDataSetFromXml(dataFile);
DataTable table = dataSet.Tables[tableName];
DataRow[] matchingRows = GetMatchingRows(table, columnDataInfoList);
// throw exception if we couldn't find the row or there were multiple rows
if (matchingRows.Length == 0) {
throw new InvalidOperationException("Could not find row to delete");
}
else if (matchingRows.Length > 1) {
throw new InvalidOperationException("No row was updated. The data was not committed. The row values(s) updated or deleted either do not make the row unique or they alter multiple rows");
}
table.Rows.Remove(matchingRows[0]);
//Save data to the XML file
SaveDataSetToXml(dataSet, dataFile);
}
Implementing GetTableInfo
Now that we can show the table data we should implement “Open Table Definition”. To do this we need to create a TableInfo, fill it out with all the appropriate information such as table names, columns, indexes, etc… and send it to the client. We’ll start out with a GetTableInfo that looks like:
public override TableInfo GetTableInfo(string connectionString, string tableName, string schema) {
DataSet dataSet = LoadDataSetFromXml(GetDatabaseLocationFromConnectionString(connectionString));
DataTable table = dataSet.Tables[tableName];
TableInfo tableInfo = new TableInfo();
tableInfo.Name = tableName;
GetColumns(table, tableInfo.Columns);
// we’ll do something with the primary key here later
return tableInfo;
}
Now we need to write the GetColumns function which will read the columns one by one from the XML DataTable, create a Column object for each and then add it to tableInfo.Columns. The mapping of attributes from DataColumn to Column is fairly straight forward but there are a few things worth pointing out. First the code:
// Get the columns in the table for display when "Open Table Definition is clicked".
private void GetColumns(DataTable table, IList<Column> columns) {
// build up a list of all the primary key columns
List<string> primaryKeyColumns = new List<string>();
foreach (DataColumn col in table.PrimaryKey) {
primaryKeyColumns.Add(col.ColumnName);
}
// loop through each column to get column info
foreach (DataColumn col in table.Columns) {
Column column = new Column();
string columnName = (string)col.ColumnName;
column.Name = columnName;
// old name will remain the same even if name changes
column.OriginalName = column.Name;
column.ColumnType = col.DataType.Name;
column.SetColumnIsInPrimaryKey(primaryKeyColumns.Contains(columnName));
column.Size = col.MaxLength;
object defaultValue = col.DefaultValue;
if (defaultValue is DBNull) {
column.DefaultValue = String.Empty;
}
else {
column.DefaultValue = defaultValue.ToString();
}
column.AllowNulls = col.AllowDBNull;
column.IsIdentity = col.AutoIncrement && col.Unique;
columns.Add(column);
}
}
One difference is the DataColumn doesn’t specify if a column is part of a primary key or not and Column would like that information so that it can display a key icon on that column. The first thing we do is build up a list of all the columns in the primary key so using DataTable.PrimaryKey so we can use it later.
Each Column has a property called OriginalName that will not be touched if the column is renamed. You can use it to loc