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()};");
}
