根据DataTable,生成建表语句

点击查看代码

        /// <summary>
        /// 根据DataTable,生成建表语句
        /// </summary>
        /// <param name="table"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static string GetCreateTableSql(DataTable table, string tableName)
        {
            var colList = new List<string>();

            //遍历列，获取字段属性
            foreach (DataColumn col in table.Columns)
            {
                var ty = TypeHelper.ConvertTypeToSqlDbType(col.DataType) + "";
                var isautoIn = col.AutoIncrement ? $"IDENTITY({col.AutoIncrementSeed},{col.AutoIncrementStep})" : "";
                var isnull = col.AllowDBNull ? "NULL" : "NOT NULL";
                var colStr = $"[{col.ColumnName}] [{ty}] {isautoIn} {isnull} ";
                colList.Add(colStr);
            }
            //拼接建表sql
            var sql = string.Format(@" if object_id('{0}') is not null begin truncate table {0} drop table {0} end CREATE TABLE {0}(
                     {1}
                    ) ON [PRIMARY];
                    ", tableName, string.Join(",", colList));
            return sql;
        }

        public class TypeHelper
        {

            public static SqlDbType ConvertTypeToSqlDbType(Type t)
            {

                //判断convertsionType类型是否为泛型，因为nullable是泛型类, 
                //判断convertsionType是否为nullable泛型类
                if (t.IsGenericType && t.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
                {
                    //如果convertsionType为nullable类，声明一个NullableConverter类，该类提供从Nullable类到基础基元类型的转换
                    NullableConverter nullableConverter = new NullableConverter(t);
                    //将convertsionType转换为nullable对的基础基元类型
                    t = nullableConverter.UnderlyingType;
                }
                var code = Type.GetTypeCode(t);


                switch (code)
                {
                    case TypeCode.Boolean:
                        return SqlDbType.Bit;
                    case TypeCode.Byte:
                        return SqlDbType.TinyInt;
                    case TypeCode.DateTime:
                        return SqlDbType.DateTime;
                    //case TypeCode.DateTime:
                    //    return SqlDbType.DateTime2;
                    case TypeCode.Decimal:
                        return SqlDbType.Decimal;
                    case TypeCode.Double:
                        return SqlDbType.Float;
                    case TypeCode.Int16:
                        return SqlDbType.SmallInt;
                    case TypeCode.Int32:
                        return SqlDbType.Int;
                    case TypeCode.Int64:
                        return SqlDbType.BigInt;
                    case TypeCode.SByte:
                        return SqlDbType.TinyInt;
                    case TypeCode.Single:
                        return SqlDbType.Real;
                    case TypeCode.String:
                        return SqlDbType.NVarChar;
                    case TypeCode.UInt16:
                        return SqlDbType.SmallInt;
                    case TypeCode.UInt32:
                        return SqlDbType.Int;
                    case TypeCode.UInt64:
                        return SqlDbType.BigInt;
                    case TypeCode.Object:
                        return SqlDbType.Variant;
                    default:
                        if (t == typeof(byte[]))
                        {
                            return SqlDbType.Binary;
                        }
                        return SqlDbType.Variant;

                }
            }


        }