![joop codegen](https://i-blog.csdnimg.cn/blog_migrate/a97816b85e6f7bf6fc988fa7106aa866.png)
joop codegen
I mentioned CodeSmith in my podcast post earlier, and we'll talk about it on next week's show, but as a nice coincedence Brian Windheim, one of our architects, posted how he was using Code Generation on one of our internal Corillian (das)Blogs.
我之前在播客帖子中提到了CodeSmith ,我们将在下周的节目中讨论它,但是作为巧合,我们的一位架构师Brian Windheim在内部Corillian(das)上发布了他如何使用Code Generation。博客。
Here's what he had to say (some parts redacted):
这是他不得不说的内容(部分内容已删除):
Brian: I’ve become a huge fan of code generation for data access and object mapping. The [blank] team has been using a generated data access layer to interface with the [blank] database for a long time now, and the reward has been tremendous. Without trying to sell it too much, here’s what the client code looks like, inside a hand-coded product API method:
布莱恩:我已经成为数据访问和对象映射的代码生成的忠实拥护者。 [空白]团队已经使用生成的数据访问层与[空白]数据库进行交互已有很长时间了,获得的回报是巨大的。 在不尝试过多出售的情况下,这是手工编码的产品API方法中的客户端代码:
FooThing ReadFooThing(long fooID)
FooThing ReadFooThing( long fooID)
{
{
using (SomeDatabase db = new SomeDatabase())
使用(SomeDatabase db = new SomeDatabase())
{
{
return FooThing.CreateFromDataReader(db.ReadFooThing(fooID));
返回FooThing.CreateFromDataReader(db.ReadFooThing(fooID));
}
}
}
}
In the above code, the FooThing and SomeDatabase types are 100% generated code. Methods are generated for every stored procedure, and multiple sproc calls can be used inside the same using block, as the SqlConnection is maintained for the undisposed life of the SomeDatabase instance. Classes with single-record and multiple-record column-to-property converters are generated for each table in the database as well. The codegen implementation will work on just about any database (it is not team-specific), and if you adhere to a few basic rules with your sproc names, the generated code will be very nice.
在上面的代码中,FooThing和SomeDatabase类型是100%生成的代码。 为每个存储过程生成方法,并且可以在同一个using块内使用多个sproc调用,因为SqlConnection在SomeDatabase实例的未分配生命期内得以维护。 还会为数据库中的每个表生成具有单记录和多记录列到属性转换器的类。 代码生成实现几乎可以在任何数据库上运行(它不是特定于团队的),并且如果您遵循一些带有存储过程名称的基本规则,则生成的代码将非常不错。
Some history: last summer I built a CodeSmith template to do the codegen, based on sample code from CodeSmith and some trial and error. Start to finish was under three hours, and I had no CodeSmith experience before, other than poking around in the [Corillian Voyager SDK’s CodeGen templates]. There has since been some minor maintenance to it, but the overall time commitment has been exceedingly small. And the benefits? Here’s a start:
一段历史:去年夏天,我基于CodeSmith的示例代码和一些试验和错误,构建了一个CodeSmith模板来进行代码生成。 从头到尾都花了不到三个小时的时间,而且我以前没有[CodeSmith]的经验,只是在[Corillian Voyager SDK的CodeGen模板]中闲逛。 此后进行了一些小的维护,但是总的时间投入却非常少。 和好处? 这是一个开始:
1. Compile-time failures when a stored procedure interface has changed and the application code hasn’t.2. Type safety for sproc inputs.3. Automatic column mapping from sproc result sets to strongly typed domain objects.4. Automatic type mapping from SQL to CLR types and vice-versa. If the sproc takes in a smallint, you won’t get away with shoving a System.Int64 in there.5. Automatic mapping of SQL OUT and INOUT params to method return types.6. Awareness of DbNull and CLR value type collisions.
1.当存储过程接口已更改且应用程序代码未更改时,编译时失败。 存储输入的类型安全性3。 从sproc结果集到强类型域对象的自动列映射4。 从SQL自动类型映射到CLR类型,反之亦然。 如果sproc包含smallint,则无法避免将System.Int64插入其中。5。 SQL OUT和INOUT参数自动映射到方法返回类型6。 意识到DbNull和CLR值类型冲突。
So what does the generated stored procedure wrapper code look like? Here’s a sample for a read-single-record sproc:
那么,生成的存储过程包装器代码是什么样的呢? 这是读取单记录存储过程的示例:
public IDataReader ReadFooThinglong fooID)
公共IDataReader ReadFooThinglong fooID)
{
{
SqlCommand command = new SqlCommand("dbo.ReadFooThing", this._connection);
SqlCommand命令=新的SqlCommand(“ dbo.ReadFooThing”,此._connection);
command.CommandType = CommandType.StoredProcedure;
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@FooID", fooID));
command.Parameters.Add( new SqlParameter(“ @ FooID”,fooID));
return command.ExecuteReader();
返回命令。ExecuteReader();
}
}
… and another sample for a create-new-record sproc, which returns the ID of the new record:
…和另一个创建新记录存储过程的示例,该过程返回新记录的ID:
public long CreateFooThing(int batchID, long accountID, string checkNumber, decimal amount, string currencyCode,
public long CreateFooThing( int batchID, long accountID,字符串checkNumber,十进制数量,字符串currencyCode,
DateTime issuedDate, DateTime someDate, string reason, string payee)
DateTime发行日期,DateTime someDate,字符串原因,字符串收款人)
{
{
SqlCommand command = new SqlCommand("dbo.CreateFooThing", this._connection);
SqlCommand命令=新的SqlCommand(“ dbo.CreateFooThing”,此._connection);
command.CommandType = CommandType.StoredProcedure;
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@BatchID", batchID));
command.Parameters.Add( new SqlParameter(“ @ BatchID”,batchID));
command.Parameters.Add(new SqlParameter("@AccountID", accountID));
command.Parameters.Add( new SqlParameter(“ @ AccountID”,accountID));
command.Parameters.Add(new SqlParameter("@CheckNumber", (checkNumber == null) ? System.DBNull.Value : (object) checkNumber));
command.Parameters.Add( new SqlParameter(“ @ CheckNumber”,(checkNumber == null )?System.DBNull.Value:( object )checkNumber));
command.Parameters.Add(new SqlParameter("@Amount", amount));
command.Parameters.Add( new SqlParameter(“ @ Amount”,amount));
command.Parameters.Add(new SqlParameter("@CurrencyCode", (currencyCode == null) ? System.DBNull.Value : (object) currencyCode));
command.Parameters.Add( new SqlParameter(“ @ CurrencyCode”,(currencyCode == null )?System.DBNull.Value:( object )currencyCode));
command.Parameters.Add(new SqlParameter("@IssuedDate", (issuedDate == DateTime.MinValue) ? System.DBNull.Value : (object) issuedDate));
command.Parameters.Add( new SqlParameter(“ @ IssuedDate”,(issuedDate == DateTime.MinValue)?System.DBNull.Value :(对象)issueDate));
command.Parameters.Add(new SqlParameter("@SomeDate", (someDate == DateTime.MinValue) ? System.DBNull.Value : (object) someDate));
command.Parameters.Add( new SqlParameter(“ @ SomeDate”,(someDate == DateTime.MinValue)?System.DBNull.Value:( object )someDate));
command.Parameters.Add(new SqlParameter("@Reason", (reason == null) ? System.DBNull.Value : (object) reason));
command.Parameters.Add( new SqlParameter(“ @ Reason”,(原因== null )?System.DBNull.Value :(对象)原因));
command.Parameters.Add(new SqlParameter("@Payee", (payee == null) ? System.DBNull.Value : (object) payee));
command.Parameters.Add( new SqlParameter(“ @ Payee”,(payee == null )?System.DBNull.Value:( object )受款人));
SqlParameter outputParameter = null;
SqlParameter outputParameter = null ;
outputParameter = new SqlParameter("@FooID", new long());
outputParameter = new SqlParameter(“ @ FooID”, new long ());
outputParameter.Direction = ParameterDirection.Output;
outputParameter.Direction = ParameterDirection.Output;
command.Parameters.Add(outputParameter);
command.Parameters.Add(outputParameter);
command.ExecuteNonQuery();
command.ExecuteNonQuery();
return (long) outputParameter.Value;
return ( long )outputParameter.Value;
}
}
The generated domain types that correspond to tables are rather big, so I won’t include them here.
与表相对应的生成的域类型非常大,因此在此不再赘述。
I use the code generator in our builds in the following manner:
我以以下方式在构建中使用代码生成器:
1. Drop the existing database.2. Deploy the database (schema + sprocs).3. Run the code generator to produce SomeDatabase.g.cs.4. Compile SomeDatabase.g.cs into assembly Corillian.SomeDatabase.Facade.dll.5. Compile assemblies dependent upon the above.
1.删除现有数据库2。 部署数据库(模式+程序)3。 运行代码生成器以生成SomeDatabase.g.cs.4。 将SomeDatabase.g.cs编译为程序集Corillian.SomeDatabase.Facade.dll.5。 根据以上内容编译程序集。
There are some simple algorithms that I use to determine whether a stored procedure is a read-single, read-multiple, create-new, or something else entirely. I leave the discovery of this as an exercise to the reader.
我使用一些简单的算法来确定存储过程是单个读取,多个读取,新建还是完全读取。 我将这一发现留给读者练习。
My nant build target looks something like the following. I re-used the [Voyager SDK’s CodeSmith "codegen" task] to kick everything off. Note that all I need is a connection string …
我的nant构建目标看起来类似于以下内容。 我重新使用了[Voyager SDK的CodeSmith“ codegen”任务]来启动一切。 请注意,我只需要一个连接字符串...
<target name="codegenDatabaseWrappers">
<property name="databaseList" value="SomeDatabase"/>
<echo message="databaseList = ${databaseList}"/>
<foreach item="String" delim="," in="${databaseList}" property="database.name">
<do>
<property name="databaseWrapper.outputFile" value="${database.name}Database.g.cs"/>
<delete file="DatabaseFacade\DataMapper\${databaseWrapper.outputFile}" failοnerrοr="false"/>
<codegen template="DatabaseFacade\DataMapper\DatabaseWrapper.cst" outputdir="DatabaseFacade\DataMapper" outputfile="${databaseWrapper.outputFile}">
<properties>
<property name="ConnectionString" value="user=${DB_Login};password=${DB_Password};server=${DB_Server};database=${database.name}"/>
</properties>
</codegen>
</do>
</foreach>
</target>
<target name =“ codegenDatabaseWrappers”> <property name =“ databaseList” value =“ SomeDatabase” /> <echo message =“ databaseList = $ {databaseList}” /> <foreach item =“ String” delim =“,” in =“ $ {databaseList}” property =“ database.name”> <做> <property name =“ databaseWrapper.outputFile” value =“ $ {database.name} Database.g.cs” /> <删除文件=“ DatabaseFacade \ DataMapper \ $ {databaseWrapper.outputFile}” failonerror =“ false” /> <codegen template =“ DatabaseFacade \ DataMapper \ DatabaseWrapper.cst” outputdir =“ DatabaseFacade \ DataMapper” outputfile =“ $ {databaseWrapper.outputFile}”> <属性> <property name =“ ConnectionString” value =“ user = $ {DB_Login}; password = $ {DB_Password}; server = $ {DB_Server}; database = $ {database.name}” /> </ properties> </ codegen> </ do> </ foreach> </ target>
Scott: Certainly there are dozens (hundreds?) of ways to generate a Database Access Layer (DAL) as there's lots of opinions as to how they should look and what's the best style. The point here is that if your database is nice and regular and needs CRUD (Create, Read, Update, Delete) then there's really no reason you shouldn't be able to generate your DAL (and often even sprocs) from either your sprocs, tables and/or views. You'll lose nothing and gain so much more time, especially in a Continuous Integration environment.
斯科特:当然,生成数据库访问层(DAL)的方式有数十种(数百种),因为人们对其外观和最佳样式有很多看法。 这里的要点是,如果您的数据库良好且常规,并且需要CRUD(创建,读取,更新,删除),那么实际上就没有理由不应该从任一存储过程中生成DAL(甚至是存储过程),表格和/或视图。 您将一无所获,并获得更多的时间,尤其是在持续集成环境中。
File Attachment: DatabaseWrapper.cst.txt (14 KB)
附件:DatabaseWrapper.cst.txt(14 KB)
Unrelated Quote of the Day: "I can't be racist, I drive a Prius!"
今日无关的语录: “我不能成为种族主义者,我会驾驶普锐斯!”
翻译自: https://www.hanselman.com/blog/codegening-a-data-access-layer-with-codesmith
joop codegen