Column.DataType not populated

Jun 2, 2014 at 9:56 PM
Hi,

I used the DatabaseReader to read the list of tables in a database:

var dbReader = new DatabaseReader(connectionString, providername);
var tables = dbReader.AllTables();

I want to iterate through the columns to find the NetType of each.

var table = tables[0];
var column = table.Columns[0];
var typeName = column.DataType.NetDataTypeCSharpName;

column.DataType was not populated. Did I miss something?

On a separate note, I created some T4 templates to generate POCO's. Would you like see whether it's something you'd like to add to the code base?

Thanks,
Candy
Coordinator
Jun 3, 2014 at 9:51 AM
dbReader.AllTables() doesn't load the DataTypes itself (dbReader.ReadAll() does).
If the datatypes are loaded, it links them up.

So do this:

var dbReader = new DatabaseReader(connectionString, providername);
dbReader.DataTypes();
var tables = dbReader.AllTables();

And the column.DataType should be populated.


Re: the T4 templates- yes, I'd be interested to see them. T4 can be a nice way to use the library.
You can use "upload patch" in the Source section.

Thanks

Martin
Jun 3, 2014 at 8:29 PM
That worked, thanks.

I checked in my T4 templates into https://github.com/candychiu/DbT4.
Jun 4, 2014 at 9:52 PM
Not sure if you got a chance to review the code. It's now more complete and generates poco and dbcontext. My experience didn't convince me that T4 is better than a self contained code gen because #1) T4 requires libraries to installed to GAC. #2) the indents and empty lines are very hard to get right. As a result, the template itself is hard to read.

Your feedback is appreciated. I will try out your code gen some time.
Coordinator
Jun 5, 2014 at 9:27 AM
Nice project!

I've used T4 before with the db Schema Reader, but never got something I was happy with.
One way that might make it easier to work with would be to use a Visual Studio extension (.vsix). Deployment is simple, user could right click the solution explorer to show a wizard to collect the connection string and properties, which then adds classes. You can still use T4, via run-time preprocessed templates.

There are some short-cuts you can make, using the code gen that's already in the library.
In particular, you can do a lot of what your EntityNameService does.
var settings = new CodeWriterSettings { Namespace = @namespace, CodeTarget = CodeTarget.PocoEntityCodeFirst, Namer = new PluralizingNamer() };
var cw = new CodeWriter(schema, settings); //no need to execute, it's just initializing
In the constructor, the codewriter has scanned through all the tables and columns and assigned the property "NetName".
After this initialization, you can always customize specific tables/properties...
schema.FindTableByName("country").NetName = "Pays";
When you reach across foreign key associations, the NetName is always there and consistent.

Instead of "class <#=tableWrapper.NameHumanCase#> " you can use directly "class <#=table.NetName#> "

The settings.Namer takes an INamer, and there are two implementations, Namer and PluralizingNamer. You can implement your own INamer, or override one of the implementations.
PluralizingNamer is my version of a very simple English pluralizer, and in the comments you'll see I mention using Entity Framework's naming service.

Hope this is helpful
Jun 5, 2014 at 2:49 PM
One problem I found is that different database provider has a different interpretation of the same specification. Although the data structure has been standardized by the Database Schema Reader, the meanings can still be different. For example, the max length of an primitive numeric value is the number of bytes in SQLite while it's not specified by the SQL Server. The meaning of it from the entity perspective can also be different from the providers. Feels like we need an extra layer to translate from provider values into entity specifications? Is this something you have planned to do?
Coordinator
Jun 6, 2014 at 7:44 AM
The big problem is that the datatypes are so different- especially SQLite!

The column.DataType object does has some features which make things easier- the IsString, IsInt, IsNumeric and IsDateTime properties, and the GetNetType() method.
You can use these to test if you need to take account of the length (IsString) or precision/scale (IsNumeric).

There's a fair bit of code (in SqlGen - the DataTypeWriters) which translates common datatypes between databases. I've done a fair amount of copying databases between Oracle, SqlServer, MySql and SQLite. SQLite and SqlExpress are common solutions for mobile/offline data. Sometimes it's just an organization change that has a different database policy.