This blog post is about writing a DatabaseProvider for the IIS Manager's Database Manager module. The module can be found here. The source for this article is available here.

This article will help walk you through the steps to create a database provider using an XML DataSet provider as an example.

Implementing DatabaseProvider

We’ll started by making a new project that implements DatabaseProvider.

  1. Start Visual Studio and create a new C# “Class Library” project
  2. Add a reference to “Microsoft.Web.Managment.DBManager”
  3. 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.
  4. 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”.
  5. 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.

  1. Install your database provider assembly in the GAC. This can be done using “gacutil”.
  2. Open the “%windir%\system32\inetsrv\config\administration.config” file.
  3. Find the <system.webServer/management/dbManager/DBProviders> collection.
  4. 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:

1.       The name of the column to which the data belongs

2.        The column’s original value (useful for the EditData method)

3.       The column’s new value

4.       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 reading 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 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");

    }

 

    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 located the column in EditTable() even if the column has been renamed;

Lastly the DataColumns don’t have an attribute that exactly corresponds to “identity” but we can set AutoIncrement and Unique on a column and get pretty much the same functionality (the UI should enforce that any column set to be an identity column also does not allow nulls). Therefore if we see a column with both those attributes set we’re calling it an “identity” column.

Now we’ll deal with the primary key issue. To DB Manager a primary key is just an index of type IndexType.PrimaryKey so if a PrimaryKey exists on the table we’ll create an index object for it. For each column in the index we’ll create an IndexColumn, fill out the column name and add it to index.Columns.

Therefore the final GetTableInfo() 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);

 

    // get the primary key and show it as an index

    if (table.PrimaryKey != null && table.PrimaryKey.Length > 0) {

        Index primaryKey = new Index();

        primaryKey.IndexType = IndexType.PrimaryKey;

        // primary key has no name so just make one up

        primaryKey.Name = "Primary Key";

        primaryKey.Unique = true;

        foreach (DataColumn dataColumn in table.PrimaryKey) {

            IndexColumn indexColumn = new IndexColumn();

            indexColumn.Name = dataColumn.ColumnName;

            primaryKey.Columns.Add(indexColumn);

        }

        tableInfo.Indexes.Add(primaryKey);

    }

 

    return tableInfo;

Now you can view the columns and primary keys of your tables. You’ll notice if you click on the “type” drop down for any of the columns you’ll get an error. We need to let the client know what types of columns our database supports before we can edit the table schema.

Implementing GetServerTypes

For each column type supported by the server we’ll create a TypeInfo and set the appropriate properties. This isn’t an exhaustive list of everything supported by our XML database but it’s enough for our example:

public override ICollection<TypeInfo> GetServerTypes(string connectionString)

{

    List<TypeInfo> typesArrayList = new List<TypeInfo>();

 

    TypeInfo intType = new TypeInfo();

    intType.Name = "Int";

    intType.Id = 0;

    intType.AllowIdentity = true;

    intType.AllowNulls = true;

    typesArrayList.Add(intType);

 

    TypeInfo stringType = new TypeInfo();

    stringType.Name = "String";

    stringType.Id = 1;

    stringType.AllowNulls = true;

    typesArrayList.Add(stringType);

 

    TypeInfo longType = new TypeInfo();

    longType.Name = "Long";

    longType.Id = 2;

    longType.AllowNulls = true;

    longType.AllowIdentity = true;

    typesArrayList.Add(longType);

 

    TypeInfo floatType = new TypeInfo();

    floatType.Name = "Float";

    floatType.Id = 3;

    floatType.AllowNulls = true;

    typesArrayList.Add(floatType);

 

    TypeInfo doubleType = new TypeInfo();

    doubleType.Name = "Double";

    doubleType.Id = 4;

    doubleType.AllowNulls = true;

    typesArrayList.Add(doubleType);

 

    TypeInfo decimalType = new TypeInfo();

    decimalType.Name = "Decimal";

    decimalType.Id = 5;

    doubleType.AllowNulls = true;

    typesArrayList.Add(decimalType);

 

    TypeInfo dateTimeType = new TypeInfo();

    dateTimeType.Name = "DateTime";

    dateTimeType.Id = 6;

    dateTimeType.AllowNulls = true;

    typesArrayList.Add(dateTimeType);

 

    TypeInfo boolType = new TypeInfo();

    boolType.Name = "Boolean";

    boolType.Id = 7;

    boolType.AllowNulls = true;

    typesArrayList.Add(boolType);

 

    TypeInfo byteType = new TypeInfo();

    byteType.Name = "Byte";

    byteType.Id = 8;

    byteType.AllowNulls = true;

    typesArrayList.Add(byteType);

 

    return typesArrayList;

}

We’ll use the “Id” property later. It’s never seen by the user and for now it’s enough to say you should just make each one unique somehow. In our case we’re using sequential integers.

Implementing EditTable

It’s time to let the user edit a table. Note this is also the function that lets the user drop a table. If TableInfo.IsMarkedForDelete is set then the table should be dropped. Our EditTable() function will look like:

public override void EditTable(string connectionString, string schema, TableInfo tableInfo) {

    if (tableInfo.IsDirty) {

        string databaseLocation = GetDatabaseLocationFromConnectionString(connectionString);

        DataSet dataSet = LoadDataSetFromXml(databaseLocation);

        if (tableInfo.IsMarkedForDelete) {

            dataSet.Tables.Remove(tableInfo.Name);

        }

        else {

            UpdateTable(dataSet, schema, tableInfo);

        }

 

        // if all went well we'll save the data set back to stroage

        SaveDataSetToXml(dataSet, databaseLocation);

    }

}

And we’ll create a helper method to do the updating of the table. To update the table we’ll have to crawl all the columns and indexes and check for changes/additions/removals. For each Column/Index/ForeignKey you can check for changes by calling GetChanges() and passing the flag for the property you want to check. For example to see if a column’s “AllowNulls” property has changed you would do:

if (column.GetChanges(ColumnChanges.AllowNull)) {

    // do something here

}

We’ll write a private method to add a column to a table since this will be useful for both editing and creating tables:

private DataColumn AddColumnToTable(DataTable table, Column column) {

    DataColumn dataColumn = new DataColumn(column.Name, GetSystemTypeFromColumnTypeId(column.ColumnTypeId));

    dataColumn.AllowDBNull = column.AllowNulls;

    if (!String.IsNullOrEmpty(column.DefaultValue)) {

        dataColumn.DefaultValue = column.DefaultValue;

    }

 

    // for identity we'll set unique, autoincrement

    dataColumn.AutoIncrement = column.IsIdentity;

    dataColumn.Unique = column.IsIdentity;

 

    table.Columns.Add(dataColumn);

    return dataColumn;

}

While updating the columns we’re also going to use the “Id” value we gave to each system type. Each Column has a “TypeId” property that will be whatever we set when we were specifying the database types. Based on the TypeId we get we know which kind of system type to assign to DataColumn.DataType.  We’ll make a method out of this since we’ll use it for CreateTable code as well:

private Type GetSystemTypeFromColumnTypeId(object typeId) {

    // we know we assigned an int to the typeId so cast it back

    int typeIdInt = (int)typeId;

 

    // these values come from the values set in GetServerTypes()

    switch (typeIdInt) {

        case 0:

            return typeof(int);

        case 1:

            return typeof(string);

        case 2:

            return typeof(long);

        case 3:

            return typeof(float);

        case 4:

            return typeof(double);

        case 5:

            return typeof(decimal);

        case 6:

            return typeof(DateTime);

        case 7:

            return typeof(bool);

        case 8:

            return typeof(byte);

        default:

            throw new InvalidOperationException("Invalid column data type");

    }

}

Lastly we know the user might have added or deleted a primary key so we’ll have to check the indexes in our UpdateTable as well.  We’ll create a little private method to create the primary key if one is needed because this can also be used from CreateTable():

private void CreatePrimaryKey(DataTable table, Index index) {

    ArrayList primaryKeyColumns = new ArrayList();

    foreach (IndexColumn indexColumn in index.Columns) {

        primaryKeyColumns.Add(table.Columns[indexColumn.Name]);

    }

    table.PrimaryKey = (DataColumn[])primaryKeyColumns.ToArray(typeof(DataColumn));

}

The full code for UpdateTable():

private void UpdateTable(DataSet dataSet, string schema, TableInfo tableInfo) {

    DataTable table = dataSet.Tables[tableInfo.Name];

 

    // update all the columns

    foreach (Column column in tableInfo.Columns) {

        if (column.GetChanges(ColumnChanges.IsMarkedForDelete)) {

            table.Columns.Remove(column.OriginalName);

        }

        if (column.GetChanges(ColumnChanges.IsNew)) {

            AddColumnToTable(table, column);

        }

        else {

            DataColumn dataColumn = table.Columns[column.OriginalName];

 

            if (column.GetChanges(ColumnChanges.AllowNull)) {

                dataColumn.AllowDBNull = column.AllowNulls;

            }

 

            if (column.GetChanges(ColumnChanges.DefaultValue)) {

                dataColumn.DefaultValue = column.DefaultValue;

            }

 

            if (column.GetChanges(ColumnChanges.IsIdentity)) {

                if (column.IsIdentity) {

                    dataColumn.AllowDBNull = false;

                    dataColumn.AutoIncrement = true;

                    dataColumn.Unique = true;

                }

                else {

                    // let them set allow null if they want

                    dataColumn.Unique = false;

                    dataColumn.AutoIncrement = false;

                }

            }

 

            if (column.GetChanges(ColumnChanges.Type)) {

                dataColumn.DataType = GetSystemTypeFromColumnTypeId(column.ColumnTypeId);

            }

 

            // not supporting size changes

 

            if (column.GetChanges(ColumnChanges.Name)) {

                dataColumn.ColumnName = column.Name;

            }

        }

    }

 

    // update primary key if needed

    // do any deletes before a create and since we're only supporting

    // primary key there should only be one

    Index primaryKeyIndex = null;

    foreach(Index index in tableInfo.Indexes) {

        if (index.GetChanges(IndexChanges.IsMarkedForDelete) &&

           (index.IndexType == IndexType.PrimaryKey)) {

            table.PrimaryKey = null;

        }

        else if (index.GetChanges(IndexChanges.IsNew) &&

                (index.IndexType == IndexType.PrimaryKey)) {

            primaryKeyIndex = index;

        }

    }

 

    if (primaryKeyIndex != null) {

        CreatePrimaryKey(table, primaryKeyIndex);

    }

}

 

Now we can go in and change our table schema.

Implementing CreateTable

We’ve already got most of the code for CreateTable() ready after doing EditTable:

public override void CreateTable(string connectionString, string schema, TableInfo tableInfo) {

    string databaseLocation = GetDatabaseLocationFromConnectionString(connectionString);

    DataSet dataSet = LoadDataSetFromXml(databaseLocation);

 

    // create a new DataTable that mirrors the structure of the tableInfo

    DataTable newTable = new DataTable(tableInfo.Name);

 

    // create a DataColumn for each Column in our tableInfo

    foreach (Column column in tableInfo.Columns) {

        DataColumn dataColumn = AddColumnToTable(newTable, column);

    }

 

    // create a primary key if one was specified

    foreach (Index index in tableInfo.Indexes) {

        if (index.IndexType == IndexType.PrimaryKey) {

            CreatePrimaryKey(newTable, index);

        }

    }

 

    // add our new table to the dataset

    dataSet.Tables.Add(newTable);

 

    // if all went well we'll save the data set back to stroage

    SaveDataSetToXml(dataSet, databaseLocation);

}

Implementing GetPrimaryKey

This function is used by the foreign key column chooser dialog. When you select a table from the primary key drop down we need to look up what columns (if any) are in the primary key. Even though we haven’t implemented foreign keys for our database provider we can implement this method as an example although there’s nothing conceptually new here:

public override string[] GetPrimaryKey(string connectionString, string tableName, string schema) {

    DataSet dataSet = LoadDataSetFromXml(GetDatabaseLocationFromConnectionString(connectionString));

 

    ArrayList columnsArrayList = new ArrayList();

    DataTable dataTable = dataSet.Tables[tableName];

    foreach (DataColumn dataColumn in dataTable.PrimaryKey) {

        columnsArrayList.Add(dataColumn.ColumnName);

    }

 

    return ((string[])columnsArrayList.ToArray(typeof(string)));

}

Implementing GetStoredProcedures

The XML database doesn’t support stored procedures but implementing this should be fairly straight forward by this point. For each stored procedure in your database you’ll have to create a StoredProcedure object and assign it a name, definition (body) and schema. Add each StoredProcedure object to a list and return it to the client.

Implementing DropProcedure

The XML database doesn’t support stored procedures but to implement this function you’d simply drop the stored procedure whose name corresponds to the name passed to this function.

Impelmenting ExecuteQuery

This allows the user to execute an arbitrary query against the database. Since our XML database doesn’t support this we won’t implement it. If you were to implement this function you’d construct and return a QueryResult object almost exactly like you would for GetData(). The only difference is you only need to fill out the “Name” attribute of each QueryColumnMetadata since the editing of rows returned by ExecuteQuery() is not allowed.