Click here to Skip to main content
15,868,157 members
Articles / Programming Languages / C#
Article

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

Rate me:
Please Sign up or sign in to vote.
3.45/5 (13 votes)
19 Dec 2008CDDL3 min read 76.4K   1.5K   76   14
With this tool, dynamically generate C# data layer code (CRUD functions) for Microsoft SQL and other databases

Introduction

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

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:

C#
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

License

This article, along with any associated source code and files, is licensed under The Common Development and Distribution License (CDDL)


Written By
Architect
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Generalunable to run the code Pin
zahid026-Mar-10 23:42
zahid026-Mar-10 23:42 
GeneralRe: unable to run the code Pin
mattraffel27-Mar-10 3:36
mattraffel27-Mar-10 3:36 
GeneralI Get The Error Pin
yehuda13-Apr-08 0:25
yehuda13-Apr-08 0:25 
GeneralRe: I Get The Error Pin
mattraffel21-Apr-08 2:50
mattraffel21-Apr-08 2:50 
QuestionNo Username and Password option Pin
sides_dale24-Nov-07 14:54
sides_dale24-Nov-07 14:54 
AnswerRe: No Username and Password option Pin
mattraffel28-Nov-07 9:22
mattraffel28-Nov-07 9:22 
GeneralClassProperty Class is missing in source project Pin
Ayyanar15-Oct-07 20:54
Ayyanar15-Oct-07 20:54 
AnswerRe: ClassProperty Class is missing in source project [modified] Pin
mattraffel16-Oct-07 1:48
mattraffel16-Oct-07 1:48 
GeneralRe: ClassProperty Class is missing in source project Pin
Ayyanar16-Oct-07 18:03
Ayyanar16-Oct-07 18:03 
AnswerRe: ClassProperty Class is missing in source project Pin
mattraffel17-Oct-07 2:35
mattraffel17-Oct-07 2:35 
Sorry about the problems. I mistakenly included the wrong version of mattraffelnetcode.dll in the source zip file.

I have updated the source zip file with the correct version of the dll now. Try downloading the source again and compiling it. (FYI: the dll is correct in the binary download).

Again I apologize for the problems.

Let me know..

Matt
GeneralnVarChar Pin
Dewey10-Oct-07 20:51
Dewey10-Oct-07 20:51 
GeneralRe: nVarChar Pin
mattraffel11-Oct-07 2:35
mattraffel11-Oct-07 2:35 
GeneralRe: nVarChar Pin
zahid026-Mar-10 23:41
zahid026-Mar-10 23:41 
GeneralRe: nVarChar Pin
mattraffel27-Mar-10 3:35
mattraffel27-Mar-10 3:35 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.