Generate class from database table in C#

Using SQL Query:

DECLARE @TableName VARCHAR(MAX) = 'Users'
DECLARE @TableSchema VARCHAR(MAX) = 'dbo'
DECLARE @result varchar(max) = ''
 
SET @result = @result + 'using System;' + CHAR(13) + CHAR(13) 
 
IF (@TableSchema IS NOT NULL) 
BEGIN
    SET @result = @result + 'namespace ' + @TableSchema  + CHAR(13) + '{' + CHAR(13) 
END
 
SET @result = @result + '    public class ' + @TableName + CHAR(13) + '    {' + CHAR(13) 
 
SET @result = @result + '        #region Instance Properties' + CHAR(13)  
 
SELECT @result = @result + CHAR(13) 
    + '        public ' + ColumnType + ' ' + ColumnName + ' { get; set; } ' + CHAR(13) 
FROM
(
    SELECT  c.COLUMN_NAME   AS ColumnName 
        , CASE c.DATA_TYPE   
            WHEN 'bigint' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Int64?' ELSE 'Int64' END
            WHEN 'binary' THEN 'Byte[]'
            WHEN 'bit' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Boolean?' ELSE 'Boolean' END            
            WHEN 'char' THEN 'String'
            WHEN 'date' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                        
            WHEN 'datetime' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                        
            WHEN 'datetime2' THEN  
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                        
            WHEN 'datetimeoffset' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTimeOffset?' ELSE 'DateTimeOffset' END                                    
            WHEN 'decimal' THEN  
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Decimal?' ELSE 'Decimal' END                                    
            WHEN 'float' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Single?' ELSE 'Single' END                                    
            WHEN 'image' THEN 'Byte[]'
            WHEN 'int' THEN  
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Int32?' ELSE 'Int32' END
            WHEN 'money' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Decimal?' ELSE 'Decimal' END                                                
            WHEN 'nchar' THEN 'String'
            WHEN 'ntext' THEN 'String'
            WHEN 'numeric' THEN
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Decimal?' ELSE 'Decimal' END                                                            
            WHEN 'nvarchar' THEN 'String'
            WHEN 'real' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Double?' ELSE 'Double' END                                                                        
            WHEN 'smalldatetime' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                                    
            WHEN 'smallint' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Int16?' ELSE 'Int16'END            
            WHEN 'smallmoney' THEN  
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Decimal?' ELSE 'Decimal' END                                                                        
            WHEN 'text' THEN 'String'
            WHEN 'time' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'TimeSpan?' ELSE 'TimeSpan' END                                                                                    
            WHEN 'timestamp' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'DateTime?' ELSE 'DateTime' END                                    
            WHEN 'tinyint' THEN 
                CASE C.IS_NULLABLE
                    WHEN 'YES' THEN 'Byte?' ELSE 'Byte' END                                                
            WHEN 'uniqueidentifier' THEN 'Guid'
            WHEN 'varbinary' THEN 'Byte[]'
            WHEN 'varchar' THEN 'String'
            ELSE 'Object'
        END AS ColumnType
        , c.ORDINAL_POSITION 
FROM    INFORMATION_SCHEMA.COLUMNS c
WHERE   c.TABLE_NAME = @TableName and ISNULL(@TableSchema, c.TABLE_SCHEMA) = c.TABLE_SCHEMA  
) t
ORDER BY t.ORDINAL_POSITION
 
SET @result = @result + CHAR(13) + '        #endregion Instance Properties' + CHAR(13)  
 
SET @result = @result  + '    }' + CHAR(13)
 
IF (@TableSchema IS NOT NULL) 
BEGIN
    SET @result = @result + '}' 
END
 
PRINT @result

Output:

using System;

namespace dbo
{
    public class Users
    {
        #region Instance Properties

        public Int32 SysID { get; set; } 

        public String UserID { get; set; } 

        public String UserName { get; set; } 

        public String Password { get; set; } 

        public String emailID { get; set; } 

        public Boolean IsActive { get; set; } 

        public DateTime CreatedWhenDtm { get; set; } 

        public String CreatedBy { get; set; } 

        public DateTime UpdatedWhenDtm { get; set; } 

        public String UpdatedBy { get; set; } 

        #endregion Instance Properties
    }
}

Get display name of enum

Problem:
How to get Display name or Description of Enum.

Solution:
I made on extension helper to get display name or description  of Enum.


Usage:

var weekofday = Days.Sun; 

Console.WriteLine(weekofday.ToDisplayName()); 

Console.WriteLine(weekofday.ToDescription());

public enum Days
{
    [Display(Name = "Sunday")]
    [Description("First day of week.")]
    Sun,
    [Display(Name = "Monday")]
    Mon,
    [Display(Name = "Tuesday")]
    Tue,
    [Display(Name = "Wednesday")]
    Wed,
    [Display(Name = "Thursday")]
    Thu,
    [Display(Name = "Friday")]
    Fri,
    [Display(Name = "Saturday")]
    Sat
}

Output:

Sunday
First day of week. 

Enum Helper:

using System;
using System.Linq;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel;
 
namespace TestWinApp
{
    public static class EnumHelper
    {
        #region Public Method
 
        // This extension method is broken out so you can use a similar pattern with 
        // other MetaData elements in the future. This is your base method for each.
        //In short this is generic method to get any type of attribute.
        public static T GetAttribute<T>(this Enum value) where T : Attribute
        {
            var type = value.GetType();
            var memberInfo = type.GetMember(value.ToString());
            var attributes = memberInfo[0].GetCustomAttributes(typeof(T), false);
            return (T)attributes.FirstOrDefault();//attributes.Length > 0 ? (T)attributes[0] : null;
        }
 
        // This method creates a specific call to the above method, requesting the
        // Display MetaData attribute.
        //e.g. [Display(Name = "Sunday")]
        public static string ToDisplayName(this Enum value)
        {
            var attribute = value.GetAttribute<DisplayAttribute>();
            return attribute == null ? value.ToString() : attribute.Name;
        }
 
        // This method creates a specific call to the above method, requesting the
        // Description MetaData attribute.
        //e.g. [Description("Day of week. Sunday")]
        public static string ToDescription(this Enum value)
        {
            var attribute = value.GetAttribute<DescriptionAttribute>();
            return attribute == null ? value.ToString() : attribute.Description;
        }
 
        #endregion
    }
}

No set method for property in WCF service

Problem:

I have some class that I'm passing as a result of a service method, and that class has a get-only property:
 

public string PropertyName
{
    get
    {
        return "Some Value";
    }    
}

 I'm getting an exception on service side "No set method for property"

Solution:

Remember that WCF needs to create an instance of the object from its serialized representation (often XML) and if the property has no setter it cannot assign a value. Objects are not transferred between the client and the server but only serialized representations, so the object needs to be reconstructed at each end.

Make setter property private. So, client can never see the set method on your property acting much like a read only property. 

public string PropertyName
{
    get
    {
        return "Some Value";
    }
    private set
    { }
}

C# datetime formatting separators issue

Problem:

Console.WriteLine(DateTime.Now.ToString("MM/dd/yyyy"));  

Output:

08-28-2013 

Expected output: 

08/28/2013

Solution:

Console.WriteLine(DateTime.Now.ToString("MM/dd/yyyy", CultureInfo.InvariantCulture));

Reason,

If a CultureInfo is not specified, the current culture will be used.

If this is a culture that doesn't use slashes as separators in dates and 

the format string specifies the date separator to be a /, that is replaced 

by whatever the actual culture date separator is –

Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information

Problem:
Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information.

Solution:
To know in details what exactly happen add one more catch block like below.

catch (ReflectionTypeLoadException ex)
{
    StringBuilder sb = new StringBuilder();
    foreach (Exception exSub in ex.LoaderExceptions)
    {
        sb.AppendLine(exSub.Message);
        if (exSub is FileNotFoundException)
        {
            FileNotFoundException exFileNotFound = exSub as FileNotFoundException;
            if (!string.IsNullOrEmpty(exFileNotFound.FusionLog))
            {
                sb.AppendLine("Fusion Log:");
                sb.AppendLine(exFileNotFound.FusionLog);
            }
        }
        sb.AppendLine();
    }
    string errorMessage = sb.ToString();
    //Display or log the error based on your application.
}

Extension method to check Null or Not Null in C#

we follow to check null reference.

if (o != null)
{    //Do Something}else{    //Do Something completely different}

but we follow extension method then make it easy coding like.

public static class ExtensionCommon  {    public static bool IsNull(this object obj)    {      return obj == null;    }     public static bool IsNotNull(this object obj)    {      return obj != null;    }     public static bool HasValue(this string obj)    {      return !string.IsNullOrWhiteSpace(obj);    }  }

and do code like that

if (obj.IsNotNull)
{    //Do Something.}else{    //Do Something.}

OR

if (!obj.IsNull)
{    //Do Something.}else{    //Do Something.}

Regular Expression for US Phone Number

>>  Criteria


Validates a U.S. phone number. It must consist of 3 numeric characters,
optionally enclosed in parentheses, followed by a set of 3 numeric characters and
then a set of 4 numeric characters.


>>  For Example


(425) 555-0123

425-555-0123

425 555 0123

1-425-555-0123

>>  Regular Expression

"^[01]?[- .]?(\([2-9]\d{2}\)|[2-9]\d{2})[- .]?\d{3}[- .]?\d{4}$"

Refence:

http://msdn.microsoft.com/en-us/library/ff650303.aspx