Deprecated CreateTable for Linq to Sql

After taking a look to the latest version of SQLServer compact I realized out that this post is deprecated, because it creates automatically the tables

Maybe it is deprecated, may be not, I do not why but I think after taking a look that Linq2Sql is very interesting, the only thing that I did not see is the Create Table Command, So due to I did my custom few months ago here I present the code for generics Create Table, I hope you rate if it helps you:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using System.Data.SqlClient;
using System.Data.Linq.Mapping;
using System.Reflection;

namespace System.Data.Linq
{
    public static class Extensions
    {
        /// <summary>
        /// Creates a DataContext using the Sql Compact Server (sdf files) or SQL Server standard
        /// </summary>
        /// <param name="Server">Stardard or Compact Server Edition</param>
        /// <param name="Connection">Connection String</param>
        /// <returns></returns>
        public static DataContext CreateDataContext(ServerType Server,String Connection)
        {
            switch (Server)
            {
                case ServerType.Standard:
                    return new DataContext(new SqlConnection(Connection));
                case ServerType.Compact:
                    return new DataContext(new SqlCeConnection(Connection));
                default:
                    return null;
            }
        }

        /// <summary>
        /// Creates a Table for the Current DataContext for the type T that has TableAttribute and ColumnAttribute for its properties
        /// </summary>
        /// <typeparam name="T">The type of the class</typeparam>
        /// <param name="Current">The DataContext in use</param>
        /// <returns>0 Succesful, -1 Class bad defined, -2 Table already exists</returns>
        public static int CreateTable<T>(this DataContext Current) where T : class
        {
            if (typeof(T).GetTableAttribute() == null)
                return -1;

            if (Current.GetTable<T>() != null)
                return -2;

            string Table = typeof(T).GetTableAttribute().Name;
            StringBuilder query = new StringBuilder(String.Format("CREATE TABLE [{0}] (", Table));

            ColumnAttribute column;
            Type type;
            String typename;
            Activator.CreateInstance<T>().GetType().GetProperties().ToList().ForEach(pi =>
            {
                column = pi.GetColumnAttribute();
                if (column!=null)
                {
                    query.Append(String.Concat("[", column.Name, "]"));
                    type = pi.GetRealType();
                    typename = String.Empty;

                    #region Types
                    if (type == typeof(Int32) || type.IsEnum)
                    {
                        typename = " int";
                        if (column.IsDbGenerated)
                            typename = " int PRIMARY KEY IDENTITY(0,1)";
                    }
                    else if (type == typeof(String))
                        typename = " nvarchar(255)";
                    else if (type == typeof(Guid))
                        typename = " nvarchar(255)";
                    else if (type == typeof(XML))
                        typename = " text";
                    else if (type == typeof(DateTime))
                        typename = " datetime";
                    else if (type == typeof(Boolean))
                        typename = " bit";
                    else if (type == typeof(Single))
                        typename = " float";
                    else if (type == typeof(Byte[]))
                        typename = " varbinary(MAX)";
                    #endregion

                    #region Nullable
                    if (pi.PropertyType.IsGenericType && pi.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
                        query.Append(String.Concat(typename, " NULL,"));
                    else
                        query.Append(String.Concat(typename, " NOT NULL,"));
                    #endregion
                }
            });

            query.Replace(",", ")", query.Length - 1, 1);
            return Current.ExecuteCommand(query.ToString(), String.Empty);
        }

        #region Attributes Helper
        private static TableAttribute GetTableAttribute(this Type type)
        {
            if (type.GetCustomAttributes(typeof(TableAttribute), false).Length == 0)
                return null;
            return type.GetCustomAttributes(typeof(TableAttribute), false)[0] as TableAttribute;
        }

        public static ColumnAttribute GetColumnAttribute(this PropertyInfo pi)
        {
            if (pi.GetCustomAttributes(typeof(ColumnAttribute), false).Length == 0)
                return null;
            return pi.GetCustomAttributes(typeof(ColumnAttribute), false)[0] as ColumnAttribute;
        }
        #endregion

        #region Types Helper
        public static Type GetRealType(this PropertyInfo pi)
        {
            if (pi.PropertyType.IsGenericType && pi.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
                return Nullable.GetUnderlyingType(pi.PropertyType);
            else
                return pi.PropertyType;
        }
        #endregion
    }

    public enum ServerType
    {
        Standard,
        Compact
    }

    public class XML
    {
        public String Value;
    }
}

Ah LinqtoSql works with SqlServer compact too, so it is amazing.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s