This project has moved and is read-only. For the latest updates, please go here.

Once you have read a database schema, the schema model allows you to easily read tables and columns. You can then translate these into classes and properties for code generation - for instance, to create simple data transfer objects.

If you have specific requirements (for instance, base classes or interfaces that must be implemented), it is not difficult to create your own code generation using the model. You could, for instance, use it with T4.

The DatabaseSchemaReader contains a simple code generation tool which is very easy to use.

Load the database schema.

Here we load Northwind from a SqlServer Express database.

const string providername = "System.Data.SqlClient";
const string connectionString = @"Data Source=.\SQLEXPRESS;Integrated Security=true;Initial Catalog=Northwind";
var dbReader = new DatabaseReader(connectionString, providername);
var schema = dbReader.ReadAll();

Single table to class

Here we find the "categories" table and generate a string containing the class.

//just get the "Categories" table
var databaseTable = schema.FindTableByName("Categories");
 //create a CodeWriterSettings to specify the namespace var settings = new CodeWriterSettings { Namespace = "Northwind" }; //create a ClassWriter with the table and settings var cw = new ClassWriter(databaseTable, settings); //get a string containing the C# code var txt = cw.Write(); //write it to a file File.WriteAllText(@"C:/temp/categories.cs", txt);

 

Create a project

If you want to have classes for all tables, the CodeWriter will generate a complete project.

var directory = new DirectoryInfo(@"C:/temp/Northwind");
//create a CodeWriterSettings to specify the namespace
var settings = new CodeWriterSettings { Namespace = "Northwind" };
var codeWriter = new CodeWriter(schema, setings);
codeWriter.Execute(directory);

Because we're writing many files, you have to specify a directory (using a DirectoryInfo).

The namespace (here "Northwind") is also used as the name of the project.

Project types

You can create different types of project:

  • POCOs (plain old CLR objects). One class per table.
  • POCOs and NHibernate hbm mapping files.
  • POCOs and Fluent NHibernate mapping files.
  • POCOs and Entity Framework Code First (EF 4.1-5) DbContext and mapping files
  • RIA Services partial classes ("buddy" classes)

To specify the type of project, use the CodeTarget enumeration in the CodeWriterSettings.

var directory = new DirectoryInfo(@"C:/temp/Northwind");
//create a CodeWriterSettings to specify the namespace and target
var settings = new CodeWriterSettings 
{
Namespace = "Northwind",
CodeTarget= CodeTarget.PocoEntityCodeFirst
}; var codeWriter = new CodeWriter(schema, settings); codeWriter.Execute(directory );

What's generated

It writes a C# class for each table, with each column as an automatic property. For POCOs and NHibernate, all properties are virtual, so they can be overridden (for CodeFirst, navigation properties are virtual). Relations between classes reflect the foreign key constraints. Composite keys are handled by creating key classes. Overrides for ToString, Equals and GetHashCode are added (the last two are required for NHibernate). The properties are decorated with DataAnnotations validation attributes.

Class and property names

In the schema model, each DatabaseTable and DatabaseColumn contains a property called NetName. When you first load the schema from the database, these properties are blank. Before the CodeWriter runs, it finds each unassigned NetName does a simple translation to turn the database names into .Net names. CodeWriter calls PrepareSchemaNames.Prepare(schema) to set any names that have not been assigned (they are correctly cased and made singular, as far as possible). So a table named "categories" is named in .Net as "Category".

You can customize the names used in generated code by setting the NetName before you call CodeWriter.

There is also an INamer interface (and default Namer class that can be overridden) for customized naming.

The many-end of foreign keys (and the DbSets in a CodeFirst context) can be customized. The default naming strategy adds "Collection" to the end (eg "ProductCollection") but you can use the PluralizingNamer for plurals (eg "Products"). You can also write your own INamer (the PluralizingNamer source code comments explain how to use .net's Entity Framework PluralizingService).

var settings = new CodeWriterSettings 
{
Namespace = "Northwind",
CodeTarget= CodeTarget.PocoEntityCodeFirst,
Namer = new PluralizingNamer()
}; var codeWriter = new CodeWriter(schema, settings);
codeWriter.Execute(directory );

NHibernate (CodeTarget.PocoNHibernateHbm)

There is also a subdirectory called "mapping" which contains NHibernate hbm.xml mapping files. The mapping files are in the project file as embedded resources.

The mapping is simple, and you probably will want to change this. It's just to get you started. If you don't need NHibernate, simply ignore or delete this.

NHibernate (CodeTarget.PocoNHibernateFluent)

There is a subdirectory called "mapping" which contains Fluent NHibernate ClassMap mapping files.

EF Code First (CodeTarget.PocoEntityCodeFirst)

There is a subdirectory called "mapping" which contains Code First 4.1-5 EntityTypeConfiguration mapping files. In addition there is a DbContext file which exposes all tables as DbSets and overrides OnModelCreating to add each entity configuration. The DatabaseInitializer is set to null. If you are using EF 4.1 or EF 4.2, uncomment the line that removes the MetadataConvention (in EF 4.3 it is obsolete).

Stored procedures

There may also be a subdirectory called "procedures" which contains classes to call stored procedures.

There is an additional step to gather the result-set schemas from stored procedures. (This uses the DbDataAdaptor.FillSchema method, executing each stored procedure. It's all within a transaction, so it should be safe). Here's the full procedure.

//as we've seen before
const string providername = "System.Data.SqlClient";
const string connectionString = @"Data Source=.\SQLEXPRESS;Integrated Security=true;Initial Catalog=Northwind";
var dbReader = new DatabaseReader(connectionString, providername);
var schema = dbReader.ReadAll();

//get the stored procedures
var sprocRunner = 
  new DatabaseSchemaReader.Procedures.ResultSetReader(
    _databaseSchema);
sprocRunner.Execute();

//use the codewriter as before
var directory = new DirectoryInfo(Environment.CurrentDirectory);
//create a CodeWriterSettings
var settings = new CodeWriterSettings 
  { 
   Namespace = "Northwind.Domain",
   WriteStoredProcedures = true
  };
var codeWriter =  new CodeWriter(schema, settings);
codeWriter.Execute(directory);

For each stored procedure, it writes a class with a method (CreateCommand) to create the DbCommand with all the parameters exposed as simple .net parameters. It also creates a method (Execute) to execute the stored procedure.

If you have the result sets (you used ResultSetReader as above) the Execute method will return classes typed to the result sets and your output parameters, so the only ADO you need is to create the DbConnection. It only understands simple parameter types (numbers, string, dates) plus Oracle ref cursors; lobs and specialized data types are beyond the scope. If you use Oracle packages, the generated code is grouped with a folder/namespace that matches the package name. If a stored procedure has ResultSets (if you used ResultSetReader), a typed result class is generated, and the stored procedure class has an Execute method.

Last edited Mar 2, 2013 at 1:40 PM by Martinjw, version 4

Comments

jacc1232 May 13, 2014 at 2:37 PM 
Excellent project thank you very much for the contribution

good although I doubt if this is not bad but the project generating when genre with little option entity framework code first in the DbContext brings some errors not enuentra some classes and if classes are there.
if esque am doing some wrong step or something if you can tell me I would greatly appreciate