Over the past few years I've been using and developing a tool that I wrote to help me generate SQL Server stored procedures and the corresponding C# object code to access the stored procedures.  I thought I would release the tool here under GPL so that other might find it useful in their projects.  The goals I wanted to accomplish with this tool are as follows:

1) Remove the tedium of writing stored procedures.
2) Remove the tedium of writing C# data access layer code.
3) Make writing business logic easier.
4) Make any tool used in the process work for about 80% of the code I encounter.

The last point is the most significant one.  It also happens to be why I can never get my hands around things like the Entity Framework or LINQ.  These frameworks end up trying to solve every edge case so that they can maintain some pretty diagram inside the Visual Studio IDE.  In the process, they generate code that is more complex, difficult to use and difficult to debug.  Maybe I'm just an old programmer guy now, but I like simplicity, ease of use and clean code. 

Before I set out to build the tool, I needed to know what problems I needed to solve and over the past two years the tool has adapted to what it is today to accommodate my needs.  Feel free to do the same with the code and make it work for you.  Remember, the goal is not to make the application program for you, but to make your programming easier because of the application.  The tool has evolved to cover the following features:

* SqlTransaction support to string C# data access objects together under a single transaction.
* Auditing of before and after data.  (Database SQL and common code objects are in the resources folder)
* Generation of disable/enable code for check boxes.
* Support for a common base class that helps eliminate redundant code.  (Set this option in the application's setting file.)
* Support for generating code using foreign keys and partial composite primary key lookups.
* Support for SQL Server 2000/2005/2008.

The best I can tell you is to play with the code builder and see what the output is.  All of the code is generated in MainForm.cs and is properly commented so that you can find the generating pieces and change them according to your needs.  Couple of assumptions.  The application requires .Net 2.0 or higher.  Indexes need to start with IX_* in order to be recognized as secondary indexes.  Primary keys need to start with PK_* in order to be recognized properly as primary keys.  The latter is important because that's how the code determines parameters to pass to certain methods like delete and update.  The last thing the application assumes is that every table has a DateCreated and a DateModified datetime column with a default getdate()/getutcdate().  Minor edits to the stored procedures will have to happen if these rules are not followed.  Other than that, most anything you can build in SQL Server can be recognized by the application and of course, feel free to edit away the assumed coding practices that I use if they do not fit your situation.

Download the source for the application here: Elysian Productions Inc. Code Builder 1.0 (428 KB)

Unzip the contents to a folder under My Documents or along side your other Visual Studio 2008 projects.

Open the solution file, compile and execute the program and you'll see the following screen:

From there select Database->Connect and you'll see the connection dialog.

The connection dialog supports integrated authentication, username/password authentication and shows SQL Server 2000/2005/2008 support.  Click Connect and the database drop down will be populated.  Select the database you're interested in and click Ok to continue on to the following screen:

Notice that all of the tables in the application are enumerated on the left.  Selecting one of those tables generates a list of stored procedures that one can choose for code generation.  The only thing you really need to do here is choose which columns you want to exclude and click generate.  The stored procedure code will be presented in an editor dialog for you.  I typically cut and paste that code into SQL Management Studio, make the appropriate edits to the code and then save the stored procedure as is to the database.

If you select the Object tab on the right, you'll be presented with the object configuration settings view.  It's roughly the same as the stored procedure screen except that there are different options available for the C# layer that make sense to the C# object layer.  Of note here is the "Add Audit logging", "Add SqlTransaction" and "Add method passing SqlTransaction" check boxes.  They have the following meanings to the generated code:

* Add Audit logging - This will add a call to a class called AuditDatabase and will use the local method's SqlTransaction to make the call.  The AuditDatabase stored procedures and object code must be included in your application and can be found in the resources directory of the project.
* Add SqlTransaction - Before using C# for data access code, I was accustomed to putting transaction information into the stored procedures themselves.  I do not do this using the code generated from this application because building business logic was much more difficult when the transaction code is buried in the stored procedure.  Instead, I rely on the .Net class SqlTransaction to manage all of my transactions.  By checking this check box, it will ensure that the method call uses a transaction and rolls back errors that may occur.
* Add method passing SqlTransaction - I quickly ran into a scenario where I needed to pass a transaction from one method to another and so I created an overloaded version of the base method that accepts a transaction.  To generate this additional method, select this check box.

So what did all this accomplish?  Well, here's how simple it is to get a list of all the Jobs in the current database I am working on.

		 1: JobData jquery = new JobData();
		 2: DataSet jds = jquery.Job_GetList();

That's it.  Take the DataSet and bind it to a control or use it however you would like. Simple, effective and readable.  One of the nice things that the objects accomplish is that they work very well with the asp:ObjectDataSource.  Consider the following:

		 1: <asp:ObjectDataSource runat="server" ID="JobScheduleListSearchSource" 
		 2: TypeName="MyObjectNamespace.JobScheduleData" SelectMethod="JobSchedule_GetListBySearchCriteria">
		 3: <SelectParameters>
		 4: <asp:ControlParameter Type="String" Name="ShortDescription" ControlID="ShortDescriptionTextBox" />
		 5: <asp:ControlParameter Type="DateTime" Name="ExecuteDate" ControlID="ExecuteDateTextBox" />
		 6: <asp:ControlParameter Type="Boolean" Name="Active" ControlID="ActiveCheckBox" />
		 7: <asp:ControlParameter Type="Boolean" Name="ManualJobsOnly" ControlID="ManualCheckBox" />
		 8: </SelectParameters>
		 9: </asp:ObjectDataSource>

The above code could be used with a DetailsView, GridView or any other data bound control in ASP.Net.  The method called above is the following method that was generated by the application, for the most part. (remember some customization after generation is expected.)

		 1: public class JobScheduleData : DatabaseObjectBase
		 2: {
		 3: public JobScheduleData()
		 4: {
		 5: Initialize(true);
		 6: }
		 7: public JobScheduleData(Boolean newConnection)
		 8: {
		 9: Initialize(newConnection);
		 10: }
		 11:  
		 12: public DataSet JobSchedule_GetListBySearchCriteria(String ShortDescription, DateTime ExecuteDate, Boolean Active, Boolean ManualJobsOnly)
		 13: {
		 14: try
		 15: {
		 16: if (_dbconn.State != ConnectionState.Open)
		 17: _dbconn.Open();
		 18: _dbadapter.SelectCommand = new SqlCommand("JobSchedule_GetListBySearchCriteria");
		 19: _dbadapter.SelectCommand.CommandType = CommandType.StoredProcedure;
		 20: _dbadapter.SelectCommand.Connection = _dbconn;
		 21: _dbadapter.SelectCommand.Parameters.Clear();
		 22: _dbadapter.SelectCommand.Parameters.Add(new SqlParameter("@varcharShortDescription", SqlDbType.VarChar, 200));
		 23: _dbadapter.SelectCommand.Parameters.Add(new SqlParameter("@datetimeExecuteDate", SqlDbType.DateTime));
		 24: _dbadapter.SelectCommand.Parameters.Add(new SqlParameter("@bitActive", SqlDbType.Bit));
		 25: _dbadapter.SelectCommand.Parameters.Add(new SqlParameter("@bitManualJobsOnly", SqlDbType.Bit));
		 26: _dbadapter.SelectCommand.Prepare();
		 27: SqlString ShortDescriptionSQL = ShortDescription;
		 28: _dbadapter.SelectCommand.Parameters["@varcharShortDescription"].Value = ShortDescriptionSQL;
		 29: SqlDateTime ExecuteDateSQL = SqlDateTime.Null;
		 30: if (ExecuteDate > DateTime.MinValue)
		 31: {
		 32: try
		 33: {
		 34: ExecuteDateSQL = ExecuteDate;
		 35: }
		 36: catch (Exception eExecuteDate)
		 37: {
		 38: String ExecuteDateMessage = eExecuteDate.Message;
		 39: ExecuteDateSQL = SqlDateTime.Null;
		 40: }
		 41: }
		 42: _dbadapter.SelectCommand.Parameters["@datetimeExecuteDate"].Value = ExecuteDateSQL;
		 43: SqlBoolean ActiveSQL = Active;
		 44: _dbadapter.SelectCommand.Parameters["@bitActive"].Value = ActiveSQL;
		 45: SqlBoolean ManualJobsOnlySQL = ManualJobsOnly;
		 46: _dbadapter.SelectCommand.Parameters["@bitManualJobsOnly"].Value = ManualJobsOnlySQL;
		 47: _dbadapter.Fill(_data);
		 48:  
		 49: return _data;
		 50: }
		 51: catch (SqlException e)
		 52: {
		 53: throw new ApplicationException("SQL Error: JobSchedule_GetListBySearchCriteria: " + e.Message, e);
		 54: }
		 55: finally
		 56: {
		 57: if (_dbadapter.SelectCommand != null)
		 58: _dbadapter.SelectCommand.Dispose();
		 59: CloseConnection();
		 60: }
		 61: }
		 62: }

Couple of things to notice about the above code and why I use the generator for even simple stored procedure calls.  The generator takes care of converting .Net types to SQL Server types.  The generator also takes care of making sure that DateTime.MinValue is not passed to SQL Server because SQL Server cannot handle 01010001 as a date.  Hopefully, you're beginning to see what can and cannot be done with the code generated from this application.  If this application helps you then I am glad that it worked for you.  If it does not, and it inspires you to create tools to make your life easier then fantastic.  Please comment on this application in the comments section of this post and if you find bugs, drop me an email or use the contact form on this site to let me know about them.  Most of all, enjoy, adapt and reduce tedium.

UPDATE: Aug 7th, 2009:

I added and refactored a bit of code to make this easier to read and to fix a couple of bugs. 
* Fixed a bug in the Delete stored procedure where the parameters did not have a space between the "and" and the parameter name.
* Added code to handle the proper bounding of smalldatetime in SQL Server.  The dates will now be limited to 01-01-1900 and 12-31-2079 as they should be.  You can still pass DateTime.MinValue to the C# code to get it to pass NULL to the stored procedure but if you pass any value greater than DateTime.MinValue, it will be min and max bounded by acceptable values for the smalldatetime SQL type.
* Refactored the parameter code generation to make additional edits along the lines of the work done for smalldatetime easier to perform in the future.
* Fixed a bug where the update and save stored procedures were not getting the proper parameter lists generated.
* Changed the *_Save stored procedure code to use if EXISTS() logic instead of COUNT(*) logic to decide if the record needs updating or inserting.