Dynamically Generate C# Data Access Code for Microsoft SQL and Other Databases

• Download source (local copy) - 147.5 KB

Introduction

I have made the entire program and its code available. Please use the following links for the downloads:

• Documentation
• Binaries
• Source code (updated 2008.12.19)

Using the Program

This section is a "quick start guide", as the Word document (link above) goes into much more detail about its usage. I encourage you to, at least, download the Word document to understand the program in more detail.

However, this is a good start.

SQLCODEGEN.EXE is a command line program. It uses the input on the command-line to start the code generation process.

Call it like this:

sqlcodegen -tables Customer,Address -classtemplate FullAccess 
			-server homesqlserver -database TestDB1 

In this case, sqlcodegen will generate full CRUD for Customer and Address tables, creating classes called Customer and Address, respectively, in files called Customer.cs and Address.cs. It knows this from the -table argument.

The code will be patterned on a template named "FullAccess" as denoted by the -classtemplate argument. (The sqlcodegen.exe.config file specifies where the templates are located and the sources to use for the templates).

The server is named homesqlserver and the database to find the tables is called TestDB1, as indicated by the -server and -database arguments.

Points of Interest

SQLCODEGEN.EXE is a very flexible, easily configured program.

The program is driven by the command line input and configuration file data. It uses templates and "snippets" files as well as XML files to control the content of the output. The templates and snippet files are text files. You can easily change the templates and snippets to match your requirements by using any text editor.

In addition to the templates and snippets, SQLCODEGEN.EXE uses a few XML files for additional configuration. While I refer you to the Word documentation for more details on these files, I did want to bring your attention to these files by pointing out that these files exist. I encourage you to explore the program to see how you can modify the configuration for your needs.

One final note: I have exposed a couple of interfaces that allow you to extend the functionality of SQLCODEGEN.EXE. The interfaces are defined in the SQLCODEGENSUPPORT.DLL. One interface will allow you to extend SQLCODEGEN.EXE to work against different databases. Another allows you to customize some of the code generation so that the generated output matches the coding standards for yourself or your organization.

Final Note

Just one small note: While I do refer you to the Word document for more details, I do understand that the document is not totally complete. However, there is plenty of good information in the documentation to get you going. Along with the source code, and through contacting me, I am reasonably confident you will be able to use SQLCODEGEN.EXE immediately.

While I am not a huge fan of code generation, I do believe it has its place in software engineering. It is perfect for generating repetitive and mundane code, like CRUD (create, retrieve, update, delete) functionality. It can help reduce coding errors and save time for the fun stuff, like working on business requirements.

I know there's other tools out there that also do this, as well as others who've made their own code generation tools. But I like writing my own tools. And, I did not want to spend a significant amount of money or time learning commercially available code generation software.

So I came up with SQLCODEGEN.EXE to generate C# data access code.

As delivered, it only works for Microsoft SQL Server. However, as noted below and in the Word document, there are ways to make it work for other data sources as well.

Updates

• 2007-11-13: I developed SQLCODEGEN.EXE against Microsoft SQL 2005. I know that system table schema for previous versions of Microsoft SQL differs so this tool, fresh from the zip file or a compile, may not work against earlier versions of Microsoft SQL. All is not lost however! You can implement your own version ITableInquiry interface, as demonstrated in the included example, in place of using the default implementation.
• 2008-12-19: Updated source code only