SQL Server Stored Procedure Generator and .NET C# Wrapper as single-layer DAL

• Download example of wrapper usage - 248.65 KB
• Download wrapper - 61.62 KB

Introduction

I was looking for a very simple wrapper generator that can give me the ability to generate C# code for my SQL 2005 table. At the same time, I wanted flexibility to create various select queries based on various parameters. However, I couldn't find exactly what I was looking for. I also wanted optional capability to use Transactions. This led me to create this code. I hope you can use this code to generate simple wrappers for yourself.

What to Expect

The wrapper is attached with this article as sp_wrapper.zip & the example of wrapper usage in attached as adventureworks.zip. You'll need to run sqlscript (stored in app_code\DAL) on your Microsoft SQL 2005 adventureworks database.

Generator Creates: The generator creates 2 classes for each table, the Table class and the Controller class. One instance of the table class represents one row of the table. List<> is used to represent row collection. Controller allows you to execute select, insert, update and delete.

List<TableName> myrowlist1;
TableNameController mycontroller = new TableNameController();
myrowlist = mycontroller.SelectAll();

This is it. The generator creates 2 select queries by default.

1. SelectAll
2. Select(PrimaryKey) or Select(PrimaryKey1, primarykey2)

You can additionally generate select queries for your other column or combination of columns that are generated as follows:

SelectByColumn1Column2(column1value, column2value)

Update and insert:

int count = mycontroller.Update(TableNameObject, UseTransaction, sqlTransaction);
int count = mycontroller.Insert(TableNameObject, UseTransaction, sqlTransaction);
int count = mycontroller.Delete(TableNameObject, UseTransaction, sqlTransaction);
int count = mycontroller.InsertUpdate
		(TableNameObjectList, UseTransaction, sqlTransaction);

The error is returned as -1. The controller also exposes exception source and message.

string source = mycontroller.ErrorSource;
string message = mycontroller.ErrorMessage;

However, if you called InsertUpdate, then the errors in updating/inserting are checked as follows:

mycontroller.InsertUpdateErrorList //this is of type List<ErrorObj>

ErrorObj is a struct defined as follows:

struct ErrorObj
{
   Object obj; //this is the row InsertUpdate was trying to update/insert
   string source;
   string message;
}

However, mycontroller.InsertUpdate() still returns an integer showing number of records updated and -1 if there was an exception within the function. You need to check for both -1 and InsertUpdateErrorList. InsertUpdateErrorList is null if all updates/inserts were successful.

Optionally, the generated code will also support transactions. Insert, Update and Delete functions have function parameters: UseTransaction (bool) and SqlTransaction.

SqlTransaction myTransaction;
TableNameObject obj = new TableNameObject(constructors...);
TableNameObjectController cntrl = new TableNameObjectController();
int count = cntrl.Insert(obj, true, myTransaction)
//do other inserts, updates using same transaction
//int count = cntrl.Insert(obj, false, null) if not using transaction
if(count != -1)
   myTransaction.Rollback;
else
   myTransaction.Commit;

You can look at adventureworks.zip file. The code in DAL folder is 100% generated.

The Code Explained...

There are two main classes that do all the work. I'm going to start with showing you how to use these 2 classes to create an application around them to generate the code. I've already created a small Windows application within the solution showing how to use the 2 classes to generate the wrapper. Please note that struct for ErrorObj is defined in this application for now (GenSPWrapper.cs).

Let me give you a brief introduction to how the 2 classes can be used as is and then I'll show you how to use the Windows Form I created in this solution:

1. CStoredProcedures.cs - This class generates the SQL script for the given table information provided in the constructor:
   C#

   public CStoredProcedures(DataTable columnSchemaDt, 
   	string tableName, string tableOwner, string[] whereArray)

2. CWrapper.cs - This class generate the C# code. Below is the constructor where you provide the initial information.
   C#

   public CWrapper(string cnnString, string tablename, 
   	string projnamespace, DataTable schemaDt, string[] whereArray)

• columnSchemaDt/schemaDt is the schema datatable you get from DataReader. Please see the solution for an example.
• tableName is the name of the table in the database
• tableOwner is the tableowner - usually it's dbo
• projnamespace - Namespace of the generated code
• cnnString - This is the string that is used in the controller. You can also put a statement like:
  C#

  cnnString = "ConfigurationSettings.ConnectionStrings[0].ConnectionString" or
  cnnString = "\"Data Source=.;
  Initial Catalog=adventureworks;Integrated Security=True\"" 
  //notice how double quotes are placed. 

• string[] whereArray - By default, the object only generates 2 select statements, one to select all and other to select using primary key. If you need select statement w/ where expressions contain other columns, then you need to provide all those combinations in this whereArray.
• whereArray format: '|' (without ') delimited w/ first element being the table name and then columns. Each string in string[] represents one combination.
• example: To show you an example, let me assume that I've the following table in database:
• Table: MyTable
• columns: OrderNo (primary key), CustomerID, Status, Amount

Now, I know generator will generate SelectAll and Select(OrderNo).

I want 2 more selects:

1. SelectByCustomerID(int customerID)
2. SeelctByCustomerIDStatus(int customerID, int status) //I just made up data types

To generate these two select functions, I'll add the following 2 strings in the whereArray:

string [] whereArray = new String[2];
whereArray[0] = "MyTable|CustomerID";
whereArray[1] = "MyTable|CustomerID|Status";

Using Application WinForm GenSPWrapper.cs

As soon as you execute the form, the first thing you need to do is update form settings. You do that by clicking on the "settings" menu.

All four textboxes are very obvious. After updating, close the form which triggers restart of the application. Make sure you go once more to settings menu if you were running the application from Visual Studio or alternatively, close the application and run again from Visual Studio.

Once updated, the first form shows the list of tables from the database of your choice indicated in settings:

Use mouse + shift/control to select the tables for which you want to create the code. and then click Next button. Please do me a favor and ignore the tabs... I haven't brushed up the application.

The next screen you see is:

The left listview shows the list of tables you selected in the first screen. Clicking on any table shows the columns of that table in the middle listbox. This is where you can choose columns for "SelectBy" and create whereArray. Once done, click on Generate and that's it.

I leave the understanding of CWrapper and CStoredProcedures to the reader... Both classes should be fairly easy to understand after reading this article. It's very easy to change the 2 classes and tweak the code to what you really want...

Any questions, comments and suggestions are very welcome...

History

• 31^st May, 2008: Initial post