Shahed Khan (MVP C#)

Database Schema Provider for SmartCodeGenerator

Last couple of days I was busy writing the DBSchemaProvider for SmartCodeGenerator. I have kept the provider signature very similiar to CodeSmiths SchemaExplorer. As a result the current SchemaProviders that available for CodeSmith can be incorporated with SmartCodeGenerator with very minor changes. I already converted The SchemaProviders for MYSQL and Native Oracle and this will ship with the next release CTP2.2.

Here is the signature for SmartCodeGenerator.DBSchemaProvider :

public abstract ParameterSchemaCollection GetCommandParameters(CommandSchema command);
public abstract CommandResultSchemaCollection GetCommandResultSchemas(CommandSchema command);
public abstract CommandSchemaCollection GetCommands(DatabaseSchema database);
public abstract string GetCommandText(CommandSchema command);
public abstract string GetDatabaseName(DatabaseSchema database);
public abstract ExtendedPropertyCollection GetExtendedProperties(SchemaBase schemaObject);
public abstract ColumnSchemaCollection GetTableColumns(TableSchema table);
public abstract DataTable GetTableData(TableSchema table);
public abstract IndexSchemaCollection GetTableIndexes(TableSchema table);
public abstract TableKeySchemaCollection GetTableKeys(TableSchema table);
public abstract PrimaryKeySchema GetTablePrimaryKey(TableSchema table);
public abstract TableSchemaCollection GetTables(DatabaseSchema database);
public abstract ViewColumnSchemaCollection GetViewColumns(ViewSchema view);
public abstract DataTable GetViewData(ViewSchema view);
public abstract ViewSchemaCollection GetViews(DatabaseSchema database);
public abstract string GetViewText(ViewSchema view);
public abstract string GetDescription();
public abstract string GetName( );

Those who are familiar with CodeSmiths SchemaExplorer notice that I am not passing connectionString as parameter of the method. As all my objects like TableSchema, ColumnSchema etc has ConnectionString as Properties.

On the other hand Eric J Smiths SchemaExplorer which is used in CodeSmith has signature like this. Snapshot from .Net Reflector

public interface IDbSchemaProvider
{
      // Methods
      ParameterSchema[] GetCommandParameters(string connectionString, CommandSchema command);
      CommandResultSchema[] GetCommandResultSchemas(string connectionString, CommandSchema command);
      CommandSchema[] GetCommands(string connectionString, DatabaseSchema database);
      string GetCommandText(string connectionString, CommandSchema command);
      string GetDatabaseName(string connectionString);
      ExtendedProperty[] GetExtendedProperties(string connectionString, SchemaObjectBase schemaObject);
      ColumnSchema[] GetTableColumns(string connectionString, TableSchema table);
      DataTable GetTableData(string connectionString, TableSchema table);
      IndexSchema[] GetTableIndexes(string connectionString, TableSchema table);
      TableKeySchema[] GetTableKeys(string connectionString, TableSchema table);
      PrimaryKeySchema GetTablePrimaryKey(string connectionString, TableSchema table);
      TableSchema[] GetTables(string connectionString, DatabaseSchema database);
      ViewColumnSchema[] GetViewColumns(string connectionString, ViewSchema view);
      DataTable GetViewData(string connectionString, ViewSchema view);
      ViewSchema[] GetViews(string connectionString, DatabaseSchema database);
      string GetViewText(string connectionString, ViewSchema view);

      // Properties
      string Description { get; }
      string Name { get; }
}

The SchemaExplorer that ships with CodeSmith is Obfuscated 
so I had very hard time understanding how it works. Again .Net Reflector
was very helpful to detect the Class Signatures.

Anyway I have written my SmartCodeGenerator.DBSchemaProvider from scratch implemented 
Asp.Net Provider Pattern as you have seen in my articles at CodeProjects. 


In SmartCodeGenerator we will add DBSchemaProviders in our SmartCodeGenProject like this.

An early snapshot from my testcodes.

<configSections>
  <sectionGroup name="smartCodeGen.providers">
    <section name="dbSchema" type="SmartCodeGen.Providers.DBSchemaConfigurationHandler, Providers"/>
  </sectionGroup>
</configSections>

<smartCodeGen.providers>
  <dbSchema defaultProvider="MySqlDBSchemaProvider">
    <providers>
      <add name="MySqlDBSchemaProvider" type="SmartCodeGen.ImplementedProviders.MySqlDBSchemaProvider, SmartCodeGen.ImplementedProviders"/>
      <add name="OracleDBSchemaProvider2" type="SmartCodeGen.ImplementedProviders.OracleDBSchemaProvider, SmartCodeGen.ImplementedProviders"/>
    </providers>
  </dbSchema>
</smartCodeGen.providers>

I am preparing my next CTP2.2 release for SmartCodeGenerator with vstemplate etc. but those who do not want to wait,
and want to see the SmartCodeGenerator DBSchemaProvider, you are most welcome to download it fromCodePlex. Don't be surprised to see it very similiar CodeSmiths SchemaExplorer. I intentionally kept the 
signatures same to mimimize the learning curve. And also the Collection Classes at this stage 
implements mimimum features like, Add(), Contains() this[int], this[name]. In future I ll implement
other cool features (ie, sorting, compare, clone ) in all the collection class.