Learning MVC: Model Binders

A model binder is a powerful concept in MVC. Implementing a model binder allows to pass an object to the controller methods automatically by the framework. In my case, I was looking for a way to get the id of the currently logged in user. This has to be checked any time user-specific data is accessed - which is, essentially, always. Placing extra code into each and every method of every controller does not look like a good solution. Here's how a model binder can be used:

First, implement IModelBinder. I only need it to return an int, and I get this int from my Users table.

public class IUserModelBinder : IModelBinder
{
 public object BindModel(ControllerContext controllerContext, ModelBindingContext bindingContext)
 {
  int userID = 0;
  if (controllerContext == null) 
  {
   throw new ArgumentNullException("controllerContext");
  }
  if (bindingContext == null)
  {
   throw new ArgumentNullException("bindingContext");
  }
  if (Membership.GetUser() != null)
  { 
   MembershipUser member = Membership.GetUser();
   string guid = member.ProviderUserKey.ToString();

   using (modelGTDContainer db = new modelGTDContainer())
   {
    userID = db.Users.Single(t => t.UserGUID == guid).UserID;
   }
  }
  return userID;
 }
}

Next, I need to register the model binder when the application starts - in the global.asax.cs I only need to add one line to Application_Start

protected void Application_Start()
{
 AreaRegistration.RegisterAllAreas();

 RegisterGlobalFilters(GlobalFilters.Filters);
 RegisterRoutes(RouteTable.Routes);
 ModelBinders.Binders[typeof(int)] = new IUserModelBinder();
}

And finally, I add an int parameter to any controller method that needs a userID to be passed. And, by the magics of MVC, it is there! This is a huge saving of work by adding just a few lines of code.

//
// GET: /InBasket/
[Authorize]
public ViewResult Index(int userID)
{
 var inbasket = repository.FindUserInBasketItems(userID);
 return View(inbasket.ToList());
}

What's bad - I have to hit a database every time I access any data to get the user ID, and then hit a database again when I select the actual data for the user. How to fix it? Well, one way is to use the user Guid as a primary key for the Users table. However, it is a string, not an int. Performance may suffer anyway, and the indexing will be affected. But, thanks to a model binder, if I want to change this in the future, it will only have to be done in one place.

References:

IPrincipal (User) ModelBinder in ASP.NET MVC for easier testing

Multi User App with MVC3, ASP.NET Membership - User Authentication / Data Separation by Evgeny. Also posted on my website

Learning MVC: A Quick Note on Validation

What is the easiest way to validate class properties that have to be stored in a database - for example, if a database field "Title" has a limit of 50 characters, how do I enforce it best? I could set a "Required" attribute directly on the class property, but the Visual Studio Designer that generated this class may not like it. And anyway, if ever need to change the model and regenerate the database, the attribute is likely to be wiped anyway.

A better idea may be to specify a special class that will handle validation ("buddy class" - funny name which seems to be an official term). I can add a partial declaration to the existing class which will not be wiped if the model changes, and in this declaration I will specify the class that handles validation. As long as the property names of the buddy class exactly match those of the actual class, I should be fine and the valiation will be handled for me by my model!

The code looks like that:

[MetadataType(typeof(InBasket_Validation))]
public partial class InBasket
{

}

public class InBasket_Validation
{
 [Required(ErrorMessage = "Title is Required")]
 [StringLength(100, ErrorMessage = "Title can not be longer than 100 characters")]
 public string Title { get; set; }

 [Required(ErrorMessage = "Content is Required")]
 [StringLength(5000, ErrorMessage = "Content can not be longer than 5000 characters")]
 public string Content { get; set; }
}

The Metadata attribute specifies the buddy class, and the buddy class specifies validation requirements. The partial InBasket class is empty cause I don't want to add anything to the actual class functionality. The code builds (why wouldn't it? It's more important if it works), and I'll test it when I'm done with the views.

by Evgeny. Also posted on my website

Learning MVC: A Repository Pattern.

A repository is just a place where data querying is encapsulated. There are several main reasons for a repository:

• Avoid repetition. If I need to write a query, I will first check the repository - maybe it was already implemented
• Encapsulation. Keep all data related code in the same place. Makes refactoring easier and separates logic from data
• Unit testing. Tests can be written against the repository and, if necessary, in such way that the real database is not required

For the purpose of my sample application, which I explain later, I will now add a repository for the "In Basket". It's extremely simple: each user can have multiple items in the basket. A user can view, edit and delete any of his items. So I need a small number of methods:

public class InBasketRepository
{
    private modelGTDContainer db = new modelGTDContainer();

    //return all in basket items for a certain user
    public IQueryable FindUserInBasketItems(int userID)
    { 
        return db.InBaskets.Where(item => item.UserID == userID);
    }

    public InBasket GetInBasketItem(int id)
    {
        return db.InBaskets.Single(item => item.InBasketID == id);
    }

    public void AddInBasketItem(InBasket item)
    {
        db.InBaskets.AddObject(item);
    }

    public void DeleteInBasketItem(InBasket item)
    {
        db.InBaskets.DeleteObject(item);
    }

    //persistence
    public void Save()
    {
        db.SaveChanges();
    }
}

It seems logical for the repository to exist in the Models folder.

And that's it for now - the next step is to create view(s) which will use the repository.

by Evgeny. Also posted on my website

Learning MVC: A multi-user application concept.

As a first experiment with MVC framework, I decided to consider the application that has multiple users where each user has some information stored in the local database. I.e. his "To Do List", to which no one else should have access. The problem, then, is to find a way to uniquely identify the user when he logs on (and, on a later stage, to select data that belongs to this user). Here's a bit of a naive first approach.

Create a database to hold the users, with the GUID being the primary key and ID being an identity and autoincremental. I used SQL CE 4.

App_Data -> Add -> New Item -> SQL Server Compact 4.0 Local Database -> dbUsers.sdf

Tables -> Create Table

Create a model from database. Project -> Add New Item -> Data -> ADO.NET Entity Data Model -> modelUsers.edmx -> Add -> Generate From Database -> dbUsers.mdf -> specify the tblUsers table and Finish.

Create a Controller to work with the Users class

Some useful bits of code in the controller:

To create a user

[HttpPost]
public ActionResult Create(tblUser tbluser)
{
 if (ModelState.IsValid)
 {
  db.tblUsers.AddObject(tbluser);
  db.SaveChanges();
  return RedirectToAction("Index");  
 }

 return View(tbluser);
}

To get user details

public ViewResult Details(int id)
{
 tblUser tbluser = db.tblUsers.Single(t => t.UserID == id);
 return View(tbluser);
}

Next, I'm going to try and stick some code into the AccountController.cs provided by the MVC application template. I want to insert a new user into my database table when the new user is registered and I want to get the user ID from the database when the user is authenticated successfully. In the future, probably, user ID may not be required at all and I can make the User GUID a primary key.

So that's how it looks in the Register method of the AccountController:

if (createStatus == MembershipCreateStatus.Success)
{
 //Insert a user into the database

 tblUser user = new tblUser();

 MembershipUser mUser = Membership.GetUser(model.UserName);
 if (mUser != null)
 {
  user.UserGUID = mUser.ProviderUserKey.ToString();

  using (dbUsersEntities db = new dbUsersEntities())
  {
   db.tblUsers.AddObject(user);
   db.SaveChanges();
  }
 }

 FormsAuthentication.SetAuthCookie(model.UserName, false /* createPersistentCookie */);
 return RedirectToAction("Index", "Home");
}

And this is in the LogOn method of the AccountController:

if (Membership.ValidateUser(model.UserName, model.Password))
{
 //user is valid, find his ID in the tblUsers
 tblUser tbluser;
 using (dbUsersEntities db = new dbUsersEntities())
 {
  MembershipUser mUser = Membership.GetUser(model.UserName);
  if (mUser != null)
  {
   string guid = mUser.ProviderUserKey.ToString();
   tbluser = db.tblUsers.Single(t => t.UserGUID == guid);
  }
 }

 FormsAuthentication.SetAuthCookie(model.UserName, model.RememberMe);
 if (Url.IsLocalUrl(returnUrl) && returnUrl.Length > 1 && returnUrl.StartsWith("/")
  && !returnUrl.StartsWith("//") && !returnUrl.StartsWith("/\\"))
 {
  return Redirect(returnUrl);
 }
 else
 {
  return RedirectToAction("Index", "Home");
 }
}

And a quick Index view for the UsersController to verify that the users are actually inserted in the database:

@foreach (var item in Model) {
    <tr>
  <td>
   @Html.DisplayFor(modelItem => item.UserID)
  </td>
  <td>
   @Html.DisplayFor(modelItem => item.UserGUID)
  </td>
    </tr>
}

Register a user

And then verify that a user with that ID and GUID is now present in the tblUsers.

The concept looks feasible, now on to refining and improving it.

by Evgeny. Also posted on my website

NuGet, Entity Framework 4.1 and DbContext API

NuGet is a "Package Manager" that can and should be used with Visual Studio 2010 because it makes installing and updating the libraries, frameworks and extensions so much easier. To install NuGet, I go to Tools -> Extension Manager within Visual Studio and search for the NuGet in the online gallery. In the search results, all I have to do is click "Install".

Now, what if I have Entity Framework installed and want to update version 4 to 4.2? I don't have to search it somewhere on download.microsoft.com or elsewhere. Right within my project I go to References, right-click and select "Add library package reference".

The Entity Framework is installed, but it's version 4.1.

I select "Updates" from the menu on the left and immediately see that 4.2 is available. I select "Update", accept terms and conditions and I'm done.

Steps that are not required: searching for an update package, manual download of the package, manual uninstall of the previous version, manual install of the new version, verifying that I save the downloaded package in an easily accessible location in case anyone in my team also needs it ... Time saved per package update: anywhere between 3 and 30 minutes.

However, it does not always go smoothly. Just today I tried to add a package "on the fly". Right-click my model, go to "Add code generation item", select "ADO.NET C# DbContext Generator" and click "Install". And here Visual Studio stopped responding.

I killed it, repeated the sequence of actions and it stopped responding again. So I started it and added the package through the Tools -> Extension Manager as described above and it worked perfectly. So, don't ask too much from your favourite IDE.

by Evgeny. Also posted on my website

Tortoise SVN for Windows and Checking Out Code from Google.

While I did not have a chance to properly configure my GitHub access yet (I think my corporate network is blocking some connections, so I'll try from home) I needed to checkout some code from code.google.com.

Following the advice, I searched around for a Windows SVN client and downloaded Tortoise SVN

Tortoise SVN

It does not have a UI as such.

It is integrated into Windows Explorer and displays the menu on the right-click. To get code, I have to select "SVN Checkout".

The checkout screen is quite self-explanatory.

However, my first attempt was unsuccessful.

I immediately suspected the corporate proxy server. Tortoise SVN has settings that are accessed through Program Files, so after some digging around I came up with the correct network settings.

Things went smoothly from there on.

Much easier than GitHub/GitExtensions so far! From zero knowledge about the application (Tortoise SVN) to a checked out solution in, probably, about 10 minutes - about as much as this post took, and even less if I was accessing it from home without any proxies. Next time I'll try to add some of my code to code.google.com

Reference:

How do I download code using SVN/Tortoise from Google Code?

by Evgeny. Also posted on my website

Generating a C# Class Based on the Underlying SQL Server Database Table

If a class structure is based on the underlying database, it may be useful to be able to automatically generate a class "stub" based on the SQL Server table. I looked up the ways to do it without too much upfront time investment, and decided to follow one of the approaches.

First, a table have to be created to define the types in SQL Server and corresponding types in the language of choice - C# for me. The table is created by the following script:

/****** Object:  Table [dbo].[DbVsCSharpTypes]    Script Date: 03/20/2010 03:07:56 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DbVsCSharpTypes]') 
AND type in (N'U'))
DROP TABLE [dbo].[DbVsCSharpTypes]
GO

/****** Object:  Table [dbo].[DbVsCSharpTypes]    Script Date: 03/20/2010 03:07:56 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DbVsCSharpTypes](
 [DbVsCSharpTypesId] [int] IDENTITY(1,1) NOT NULL,
 [Sql2008DataType] [varchar](200) NULL,
 [CSharpDataType] [varchar](200) NULL,
 [CLRDataType] [varchar](200) NULL,
 [CLRDataTypeSqlServer] [varchar](2000) NULL,

 CONSTRAINT [PK_DbVsCSharpTypes] PRIMARY KEY CLUSTERED 
(
 [DbVsCSharpTypesId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET NOCOUNT ON;
SET XACT_ABORT ON;
GO

SET IDENTITY_INSERT [dbo].[DbVsCSharpTypes] ON;
BEGIN TRANSACTION;
INSERT INTO [dbo].[DbVsCSharpTypes]([DbVsCSharpTypesId], [Sql2008DataType], [CSharpDataType], [CLRDataType], [CLRDataTypeSqlServer])
SELECT 1, N'bigint', N'short', N'Int64, Nullable', N'SqlInt64' UNION ALL
SELECT 2, N'binary', N'byte[]', N'Byte[]', N'SqlBytes, SqlBinary' UNION ALL
SELECT 3, N'bit', N'bool', N'Boolean, Nullable', N'SqlBoolean' UNION ALL
SELECT 4, N'char', N'char', NULL, NULL UNION ALL
SELECT 5, N'cursor', NULL, NULL, NULL UNION ALL
SELECT 6, N'date', N'DateTime', N'DateTime, Nullable', N'SqlDateTime' UNION ALL
SELECT 7, N'datetime', N'DateTime', N'DateTime, Nullable', N'SqlDateTime' UNION ALL
SELECT 8, N'datetime2', N'DateTime', N'DateTime, Nullable', N'SqlDateTime' UNION ALL
SELECT 9, N'DATETIMEOFFSET', N'DateTimeOffset', N'DateTimeOffset', N'DateTimeOffset, Nullable' UNION ALL
SELECT 10, N'decimal', N'decimal', N'Decimal, Nullable', N'SqlDecimal' UNION ALL
SELECT 11, N'float', N'double', N'Double, Nullable', N'SqlDouble' UNION ALL
SELECT 12, N'geography', NULL, NULL, N'SqlGeography is defined in Microsoft.SqlServer.Types.dll, which is installed with SQL Server and can be downloaded from the SQL Server 2008 feature pack.' UNION ALL
SELECT 13, N'geometry', NULL, NULL, N'SqlGeometry is defined in Microsoft.SqlServer.Types.dll, which is installed with SQL Server and can be downloaded from the SQL Server 2008 feature pack.' UNION ALL
SELECT 14, N'hierarchyid', NULL, NULL, N'SqlHierarchyId is defined in Microsoft.SqlServer.Types.dll, which is installed with SQL Server and can be downloaded from the SQL Server 2008 feature pack.' UNION ALL
SELECT 15, N'image', NULL, NULL, NULL UNION ALL
SELECT 16, N'int', N'int', N'Int32, Nullable', N'SqlInt32' UNION ALL
SELECT 17, N'money', N'decimal', N'Decimal, Nullable', N'SqlMoney' UNION ALL
SELECT 18, N'nchar', N'string', N'String, Char[]', N'SqlChars, SqlString' UNION ALL
SELECT 19, N'ntext', NULL, NULL, NULL UNION ALL
SELECT 20, N'numeric', N'decimal', N'Decimal, Nullable', N'SqlDecimal' UNION ALL
SELECT 21, N'nvarchar', N'string', N'String, Char[]', N'SqlChars, SqlStrinG SQLChars is a better match for data transfer and access, and SQLString is a better match for performing String operations.' UNION ALL
SELECT 22, N'nvarchar(1), nchar(1)', N'string', N'Char, String, Char[], Nullable', N'SqlChars, SqlString' UNION ALL
SELECT 23, N'real', N'single', N'Single, Nullable', N'SqlSingle' UNION ALL
SELECT 24, N'rowversion', N'byte[]', N'Byte[]', NULL UNION ALL
SELECT 25, N'smallint', N'smallint', N'Int16, Nullable', N'SqlInt16' UNION ALL
SELECT 26, N'smallmoney', N'decimal', N'Decimal, Nullable', N'SqlMoney' UNION ALL
SELECT 27, N'sql_variant', N'object', N'Object', NULL UNION ALL
SELECT 28, N'table', NULL, NULL, NULL UNION ALL
SELECT 29, N'text', N'string', NULL, NULL UNION ALL
SELECT 30, N'time', N'TimeSpan', N'TimeSpan, Nullable', N'TimeSpan' UNION ALL
SELECT 31, N'timestamp', NULL, NULL, NULL UNION ALL
SELECT 32, N'tinyint', N'byte', N'Byte, Nullable', N'SqlByte' UNION ALL
SELECT 33, N'uniqueidentifier', N'Guid', N'Guid, Nullable', N'SqlGuidUser-defined type(UDT)The same class that is bound to the user-defined type in the same assembly or a dependent assembly.' UNION ALL
SELECT 34, N'varbinary ', N'byte[]', N'Byte[]', N'SqlBytes, SqlBinary' UNION ALL
SELECT 35, N'varbinary(1), binary(1)', N'byte', N'byte, Byte[], Nullable', N'SqlBytes, SqlBinary' UNION ALL
SELECT 36, N'varchar', N'string', N'String, Char[]', N'SqlChars, SqlStrinG SQLChars is a better match for data transfer and access, and SQLString is a better match for performing String operations.' UNION ALL
SELECT 37, N'xml', NULL, NULL, N'SqlXml'
COMMIT;
RAISERROR (N'[dbo].[DbVsCSharpTypes]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;
GO

SET IDENTITY_INSERT [dbo].[DbVsCSharpTypes] OFF;

Here is what results from the script:

Next, a function that will return the C# type when the SQL Server type is passed to it will be required. It will take it from that table that was just created. This is the script for the function:

/****** Object:  UserDefinedFunction [dbo].[funcGetCLRTypeBySqlType]    
Script Date: 03/23/2010 15:25:09 ******/
IF  EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[funcGetCLRTypeBySqlType]') 
AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[funcGetCLRTypeBySqlType]
GO 
/****** Object:  UserDefinedFunction [dbo].[funcGetCLRTypeBySqlType]     
Script Date: 03/23/2010 15:25:09 ******/
SET ANSI_NULLS ON
GO 
SET QUOTED_IDENTIFIER ON
GO 
CREATE FUNCTION [dbo].[funcGetCLRTypeBySqlType]
(@SqlType [nvarchar] (200)) 
RETURNS [varchar](200) 
WITH EXECUTE AS CALLER
AS 
BEGIN
declare @ClrType varchar(200) 
SET @ClrType = ( SELECT  TOP 1 CSharpDataType FROM DbVsCSharpTypes 
WHERE Sql2008DataType= @SqlType)
-- Return the result of the function
RETURN @ClrType END 
/*Used for automatic conversation between tsql and C# types */
GO

Sample of the usage - nothing hard yet.

A small function just because I want my private variable start from a lower case character

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE FUNCTION [dbo].[lowerCaseFirstCharacter]
(@Input [nvarchar] (200))
RETURNS [varchar](200) 
WITH EXECUTE AS CALLER
AS 
BEGIN
declare @Result varchar(200) 
declare @Len int
SET @Len = LEN(@Input)
SET @Result = LOWER(SUBSTRING(@Input, 1, 1)) + SUBSTRING(@Input, 2, @Len-1)

RETURN @Result 
END

And, finally, the stored procedure that generates some C# code:

IF  EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[procUtils_GenerateClass]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[procUtils_GenerateClass]
GO 
/****** Object:  StoredProcedure [dbo].[procUtils_GenerateClass]     
Script Date: 03/20/2010 13:10:40 ******/
SET ANSI_NULLS ON
GO 
SET QUOTED_IDENTIFIER ON
GO  
CREATE PROCEDURE [dbo].[procUtils_GenerateClass]
@TableName [varchar](50)
WITH EXECUTE AS CALLER
AS
BEGIN -- proc start                                      
SET NOCOUNT ON;                                       
DECLARE @DbName nvarchar(200 ) 
select @DbName = DB_NAME()               
declare @strCode nvarchar(max) 
set @strCode = ''  

BEGIN TRY        --begin try
set @strCode = @strCode +  'namespace ' + @DbName + '.Gen' + CHAR(13) + '{' + CHAR(13)
set @strCode = @strCode + CHAR(9) + 'public class ' + @TableName + CHAR(13) + CHAR(9) + '{ ' + CHAR(13)

DECLARE @ColNames TABLE                                
(                                
Number [int] IDENTITY(1,1), --Auto incrementing Identity column
ColName [varchar](300) , --The string value                        ,         
DataType varchar(50) ,  --the datatype                       
IS_NULLABLE nvarchar(5) , --should we add =null in front         
CHARACTER_MAXIMUM_LENGTH INT        
)                                
--Decalre a variable to remember the position of the current delimiter                                
DECLARE @CurrentDelimiterPositionVar INT                                 
DECLARE @PkColName varchar(200)      
set @PkColName = ''
declare @ColumnName varchar(200)  
--Decalre a variable to remember the number of rows in the table                                
DECLARE @Count INT                                

INSERT INTO @ColNames         
SELECT column_name ,  Data_type , IS_NULLABLE , CHARACTER_MAXIMUM_LENGTH  
from INFORMATION_SCHEMA.COLUMNS                             
where TABLE_NAME=@TableName                                
--Initialize the looper variable                                
SET @CurrentDelimiterPositionVar = 1                                
--Determine the number of rows in the Table                                
SELECT @Count=max(Number) from @ColNames                                
--A variable to hold the currently selected value from the table                                
DECLARE @ColName varchar(300);                                
DECLARE @DataType varchar(50)                      
DECLARE @IS_NULLABLE VARCHAR(5)        
DECLARE @CHARACTER_MAXIMUM_LENGTH INT        
--Loop through until all row processing is done              
WHILE @CurrentDelimiterPositionVar <= @Count --1st loop        
BEGIN                                
--Load current value from the Table                                
SELECT @ColName = ColName FROM @ColNames         
WHERE Number = @CurrentDelimiterPositionVar        
SELECT @DataType = DataType FROM @ColNames         
WHERE Number = @CurrentDelimiterPositionVar               
SELECT @IS_NULLABLE = IS_NULLABLE FROM @ColNames         
WHERE Number = @CurrentDelimiterPositionVar                    
SELECT @CHARACTER_MAXIMUM_LENGTH = CHARACTER_MAXIMUM_LENGTH FROM @ColNames         
WHERE Number = @CurrentDelimiterPositionVar                     
-- get the C# type based on the passed sqlType, )( needs the DbVsCSharpTypes table ) 
set @DataType =( SELECT  dbo.funcGetCLRTypeBySqlType(@DataType) ) 
IF @IS_NULLABLE = 'YES'        
set @DataType = @DataType + '?' 
DECLARE @varPrivate nvarchar(200) 
set @varPrivate = '_' + dbo.lowerCaseFirstCharacter(@ColName)

--GENERATE THE PRIVATE MEMBER 
SET @StrCode = @strCode + CHAR(9)+ CHAR(9) + 'private ' + @DataType + ' ' +  @varPrivate + ';' + CHAR(13) + CHAR(13) 
-- GENERATE THE PUBLIC MEMBER 
SET @StrCode = @strCode + CHAR(9)+ CHAR(9) + 'public ' + @DataType +  ' ' + @ColName + CHAR(13) + CHAR(9)+ CHAR(9) + '{' + CHAR(13)
SET @StrCode = @strCode + CHAR(9) + CHAR(9) + CHAR(9) + 'get { return ' + @varPrivate + ' } ' + CHAR(13)
SET @strCode = @strCode + CHAR(9) + CHAR(9) + CHAR(9) + 'set { ' + @varPrivate +' = value ; }' + CHAR(13) 
SET @strCode = @strCode + CHAR(9) + CHAR(9) + '}' + CHAR(13) 

if @CurrentDelimiterPositionVar != @Count         
SET @StrCode = @StrCode + ''        
IF @DataType != 'timestamp'        
 
set @strCode = @strCode + char(13)     
SET @CurrentDelimiterPositionVar = @CurrentDelimiterPositionVar + 1;                                
END                                
set @strCode = + @strCode + char(9) + ' } //class ' + @TableName + CHAR(13)
set @strCode = + @strCode + ' } //namespace  ' + CHAR(13)

PRINT @strCode
END TRY                                  
BEGIN CATCH                                  
print ' Error number: ' + CAST(ERROR_NUMBER() AS varchar(100)) +                         
'Error message: ' + ERROR_MESSAGE() + 'Error severity: ' +           
CAST(ERROR_SEVERITY() AS varCHAR(9)) +                         
'Error state: ' + CAST(ERROR_STATE() AS varchar(100)) +           
'XACT_STATE: ' + CAST(XACT_STATE() AS varchar(100))                                  
END CATCH                                   
END --procedure end                                       
/*  Generates a C# class base on DataType conversion*/
GO

Here's a test table I used to check the results:

Here's the stored procedure output sample:

References

How can I programatically convert SQL data-types to .Net data-types?

how-to generate classes based on tables in ms sql 2008

Function to get the C# type based on the tsql type

by Evgeny. Also posted on my website