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
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
TableNameController mycontroller = new TableNameController();
myrowlist = mycontroller.SelectAll();
This is it. The generator creates 2
select queries by default.
You can additionally generate
select queries for your other column or combination of columns that are generated as follows:
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:
ErrorObj is a
struct defined as follows:
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
null if all
inserts were successful.
Optionally, the generated code will also support transactions.
Delete functions have function parameters:
UseTransaction (bool) and
TableNameObject obj = new TableNameObject(constructors...);
TableNameObjectController cntrl = new TableNameObjectController();
int count = cntrl.Insert(obj, true, myTransaction)
if(count != -1)
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
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:
- CStoredProcedures.cs - This class generates the SQL script for the given table information provided in the constructor:
public CStoredProcedures(DataTable columnSchemaDt,
string tableName, string tableOwner, string whereArray)
- CWrapper.cs - This class generate the C# code. Below is the constructor where you provide the initial information.
public CWrapper(string cnnString, string tablename,
string projnamespace, DataTable schemaDt, string whereArray)
Now, I know generator will generate
I want 2 more
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
string  whereArray = new String;
whereArray = "MyTable|CustomerID";
whereArray = "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:
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
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...
- 31st May, 2008: Initial post