-
Notifications
You must be signed in to change notification settings - Fork 127
Code Generation
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.
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();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);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.
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);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.
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 );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.
There is a subdirectory called "mapping" which contains Fluent NHibernate ClassMap mapping files.
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).
See EF Code First
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. The API is a little different in .net Core- see Procedure ResultSets.
//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.