aHmeD eL-kiLaNi

Codesmith tools is one of the powerful template-based code generation tool. For more info go to www.codesmithtools.com

Codesmith has various predefined templates for genrating domain model, N-tires, CSLA and NHibernate classes or mapping files.
In fact none of predefined templates are suitable for easy and quick use. NetTiers is the best but it needs a lot of customization and also generates a lot of code which is not necessary in most cases.
Today I come with a Generator template to generate domain model classes and data access layer and also CRUD stored procedures.

The final output of the template (generated for Northwind Category table) is figured below:

SqlDB class is the class containing Execute, ExecuteScalar, ExecuteReader, FillTable methods, which are responsible for creating database connection and execute queries and stored procedure on the database.

AbstractDataProvider is the base class for each Entity DataProvider class, it abstracts the definitions for the common base methods such as select, insert, update and delete.

AbstractEntity Class is the base class for each Entity class. IComparable interface is implemented to allow dynamic sorting for domain objects into lists; for more info about default comparer see :
http://blogs.a-h-m-e-d.com/Blogs/post/Another-dynamic-comparer-for-sorting-lists-(-very-simple-).aspx

Features:
- DeleteByFlag:
This feature allows you to specify a boolean column in a given table to be a flag for deleted rows instead of deleting them permenantly from database.
Select stored procedures select only rows of a table which has the DeleteFlagColumn set to 0 (False). Delete stored procedure just updates the colum to 1 (True) instead of the delete action.

-UseCollection:
Returned rows can be returned as generic lists if UseCollections option is set to True else a DataTable is returned.

-Generator template:
This template will generate all classes and stored procedures needed for interaction with database for given table(s).

Generator.rar (13.38 KB)

Code snippets is one of the coolest features and enhancements that Visual Studio 8 provides. Code snippets is XML-based templates for reusable code, it can help you write your code faster so that increases developer productivity.
I am going to demonstrate a custom code snippet template which can help you write your data access queries faster; The snippet will generate the following code: 

SqlConnection con = new SqlConnection("Connection String"); 
SqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text; 
cmd.CommandText = "Command Text"; 
try { 
    con.Open();
    cmd.ExecuteNonQuery(); 
} 
catch (SqlException ex) 
{ 
    //throw new Exception(ex.Message);
} 
finally 
{ 
    cmd.Dispose(); 
    con.Close(); 
}
   

To start out go to:
C:\Program Files\Microsoft Visual Studio 8\VC#\Snippets\1033\Visual C# 
*Change "c:/program files" to where you've installed visual studio
You will find a lot of *.snippet files which are shipped with MS Visual studio. To define our own snippet create a file named "SqlConnection.snippet" then write the following and save the file inside the current location:

<?xml encoding="utf-8" version="1.0" ?>
<CODESNIPPETS xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
<CODESNIPPET Format="1.0.0">
<HEADER>
<SHORTCUT>sqlcon</SHORTCUT>
<DESCRIPTION>Code snippet for Sql Connection with command execution</DESCRIPTION>
<AUTHOR>aHmeD eL-kiLaNi</AUTHOR>
<SNIPPETTYPES>
<SNIPPETTYPE>Expansion</SNIPPETTYPE>
<SNIPPETTYPE>SurroundsWith</SNIPPETTYPE>
</SNIPPETTYPES>
</HEADER>
<SNIPPET>
<DECLARATIONS>
<LITERAL>
<ID>con</ID>
<TOOLTIP>SqlConnection object.</TOOLTIP>
<DEFAULT>con</DEFAULT>
</LITERAL>
<LITERAL>
<ID>cmd</ID>
<TOOLTIP>SqlCommand object.</TOOLTIP>
<DEFAULT>cmd</DEFAULT>
</LITERAL>
</DECLARATIONS>
<CODE language=csharp>

</CODE>
</SNIPPET>
</CODESNIPPET>
</CODESNIPPETS>

Variable declaration:
The following lines defines variables that would be used in our snippet:
<Literal>
      <ID>con</ID>
      <ToolTip>SqlConnection object.</ToolTip>
       <Default>con</Default>
 </Literal>

Code snippet:
Inside  <Code> element write your custom code; use the literals (variables) - defined before - inside codesnippet putting $ right before and after variable names.

After finishing save the template. Now with your visual studio IDE go to where you want to generate this code and (press ctl+k+x); a snippet drop-down menu would be popped up, click SqlConnection and you're done!!

Note: if you've saved the .snippet file in another place you can import it using VS IDE: (press ctl+k+b) and import the *.snippet file under your desired language.