public void CreateTable(string tableName, List <DbColumnInfo> columns, bool isCreatePrimaryKey = true) { string sql = $"create table {tableName} ("; StringBuilder builder = new StringBuilder(); foreach (var column in columns) { string dataType = column.DataType.ToLower(); switch (column.DataType.ToLower()) { case "int": dataType = "INTEGER"; break; } builder.Append(string.Format("{0} {1}{2} {3} {4} {5} {6},", column.ColumnName, dataType, Utils.IsCharColumn(column.DataType) && column.Length > 0 ? $"({column.Length})" : "", column.IsNullable && !column.IsPrimarykey ? "null" : "not null", column.IsPrimarykey ? "primary key" : "", column.IsIdentity ? "autoincrement" : "", !string.IsNullOrEmpty(column.DefaultValue) ? $"default {column.DefaultValue}" : "" )); } sql = sql + builder.ToString().TrimEnd(',') + ")"; dbAccess.ExecSQL(sql); }
public void CreateTable(string tableName, List <DbColumnInfo> columns, bool isCreatePrimaryKey = true) { string sql = $"create table {tableName} ("; var builder = new StringBuilder(); var descBuilder = new StringBuilder(); foreach (var column in columns) { builder.Append(string.Format("{0} {1}{2} {3} {4} {5} {6},", column.ColumnName, column.DataType, Utils.IsCharColumn(column.DataType) && column.Length > 0 ? $"({column.Length})" : "", column.IsNullable && !column.IsPrimarykey ? "null" : "not null", column.IsIdentity ? "identity" : "", column.IsPrimarykey ? "primary key" : "", !string.IsNullOrEmpty(column.DefaultValue) ? $"default {column.DefaultValue}" : "" )); if (!column.ColumnDescription.IsNullOrEmpty()) { //添加数据库 注释说明 descBuilder.AppendFormat("EXECUTE sp_addextendedproperty N'MS_Description', N'{0}', N'user', N'dbo', N'table', N'{1}', N'column', N'{2}';", column.ColumnDescription, tableName, column.ColumnName); } } sql = sql + builder.ToString().TrimEnd(',') + ")"; sql = sql + ";" + descBuilder.ToString(); dbAccess.ExecSQL(sql); }
public void ClearTableData(params string[] tableNames) { StringBuilder sb = new StringBuilder(); foreach (var tableName in tableNames) { sb.AppendLine(string.Format("DELETE FROM [{0}]", tableName)); } _dbAccess.ExecSQL(sb.ToString()); }
private static void DoActionSql(ref StringBuilder sqlBuilder, IDbAccess dbAccess, Action <string> beginAction, Action <string> finishAction) { if (sqlBuilder.ToString().Length > 0) { try { if (beginAction != null) { beginAction(sqlBuilder.ToString()); } dbAccess.ExecSQL(sqlBuilder.ToString()); if (finishAction != null) { finishAction(sqlBuilder.ToString()); } sqlBuilder = new StringBuilder(); } catch (Exception ex) { throw new Exception("执行SQL失败:" + sqlBuilder, ex); } } }
public void DropTable(string tableName) { //删除表 并且删除序列 dbAccess.ExecSQL($"drop table {tableName};drop sequence SEQ_{tableName.ToUpper()};"); }