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.
- 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:
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.
