c# 下 根据Datatable的结构动态创建表

点击查看代码

/// <summary>
        /// 根据DataTable,生成建表语句
        /// </summary>
        /// <param name="table"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private 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;
 
            }
        }
 
      
    }