根据DataTable生成建表SQL语句【C#】原创
3人赞赏了该文章
312次浏览
编辑于2023年10月17日 19:50:13
美人卷珠帘,深坐蹙蛾眉。
/// <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) { string ty; if (Type.GetTypeCode(col.DataType) == TypeCode.String) { ty = TypeHelper.ConvertTypeToSqlDbType(col.DataType) + "(255)"; } else if (Type.GetTypeCode(col.DataType) == TypeCode.Decimal) { ty = TypeHelper.ConvertTypeToSqlDbType(col.DataType) + "(18,6)"; } else { ty = TypeHelper.ConvertTypeToSqlDbType(col.DataType) + ""; } var isautoIn = col.AutoIncrement ? $"IDENTITY({col.AutoIncrementSeed},{col.AutoIncrementStep})" : ""; var isnull = col.AllowDBNull ? "NULL" : "NOT NULL"; var colStr = ""; if (Type.GetTypeCode(col.DataType) == TypeCode.String || Type.GetTypeCode(col.DataType) == TypeCode.Decimal) { colStr = $"[{col.ColumnName}] {ty} {isautoIn} {isnull} "; } else { colStr = $"[{col.ColumnName}] [{ty}] {isautoIn} {isnull} "; } colList.Add(colStr); } if (tableName == "TMP_BomAndCostDetail") { colList.Add("[fyearperiod] NVarChar(255) NULL "); } //拼接建表sql var sql = string.Format(@"/*dialect*/ if object_id('{0}') is not null begin drop table {0} end CREATE TABLE {0}( {1} ) ON [PRIMARY]; ", tableName, string.Join(",", colList)); return sql; }