/// <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;
}
TypeHelper:
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;
}
}
}
————————————————
版权声明:本文为CSDN博主「枫雨时空」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/fyshk/article/details/83650065