/// <summary>
/// 验证数据库表是否存在，如果不存在则创建表
/// </summary>
/// <param name="table"></param>
/// <param name="tbName"></param>
/// <returns></returns>
private static bool CheckTableAndCreate(DBTable table, string tbName)
{
bool isExist = false;
using (DBSession session = DBSession.TryGet())
{
try
{
session.ExecuteScalar <int>(string.Format("select count(1) from {0}", tbName));
isExist = true;
}
catch (Exception)
{
isExist = false;
}
//创建表
if (!isExist)
{
try
{
string createSql = table.CreateSql.ToUpper().
Replace(table.Name.ToUpper(), tbName.ToUpper());
string[] sqls = createSql.Split(new string[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
session.BeginTransaction();
foreach (string item in sqls)
{
if (!string.IsNullOrWhiteSpace(item.Trim()))
{
session.ExecuteNonQuery(item);
}
}
session.Commit();
return(true);
}
catch (Exception ex)
{
session.Rollback();
throw ex;
}
}
}
return(true);
}
/// <summary>
/// 检查表
/// </summary>
/// <param name="list"></param>
/// <param name="dbContext"></param>
private static void CheckTable(List <TBField> list, DBContext dbContext)
{
//对list按表名分组
List <IGrouping <string, TBField> > groups = list.GroupBy((x) => { return(x.TableName); }).ToList();
using (DBSession session = DBSession.TryGet(dbContext.DbKey))
{
foreach (DBTable dbTable in MapHelper.TableDictionary.Values)
{
//判断表是否存在
TBField table = list.FirstOrDefault(x => x.TableName.ToUpper() == dbTable.Name.ToUpper());
if (table == null)
{
//表不存在，则创建表
CreateTable(session, dbContext, dbTable);
}
//验证字段
if (table != null || dbTable.SeparateType != SeparateType.None)
{
foreach (DBColumn column in dbTable.ColumnList)
{
TBField field = list.FirstOrDefault(x => (x.TableName.ToUpper() == dbTable.Name.ToUpper() && x.Name.ToUpper() == column.Name.ToUpper()));
if (field == null && !string.IsNullOrWhiteSpace(column.DataType))
{
//为表添加字段
if (dbTable.SeparateType != SeparateType.None)
{
List <IGrouping <string, TBField> > sepTbs = groups.FindAll((x) => x.Key.ToUpper().StartsWith(dbTable.Name.ToUpper()));
foreach (var item in sepTbs)
{
CreateColumn(session, item.Key, column);
}
}
else
{
CreateColumn(session, dbTable.Name, column);
}
}
}
}
}
}
}
/// <summary>
/// 创建表
/// </summary>
/// <param name="dbTable"></param>
private static void CreateTable(DBSession session, DBContext dbContext, DBTable dbTable)
{
StringBuilder sql_colums = new StringBuilder();
StringBuilder sql_pri = new StringBuilder();
//添加主键字段
foreach (var item in dbTable.PrimaryKey)
{
sql_colums.AppendFormat(",{0} {1} NOT NULL", item.Name, item.DataType);
sql_pri.AppendFormat(",{0}", item.Name);
}
//添加字段
foreach (var item in dbTable.ColumnList)
{
sql_colums.AppendFormat(",{0} {1}", item.Name, item.DataType);
if (!string.IsNullOrWhiteSpace(item.Default))
{
sql_colums.AppendFormat(" default {0}", item.Default);
}
if (item.NotNull)
{
sql_colums.Append(" NOT NULL");
}
}
if (dbContext.DataType == DataBaseType.SQLServer)//添加sql server主键
{
sql_colums.AppendFormat(",constraint PK_{0} primary key ({1})", dbTable.Name, sql_pri.ToString().TrimStart(','));
}
//创建表
session.ExecuteNonQuery(string.Format("create table {0} ({1})", dbTable.Name, sql_colums.ToString().TrimStart(',')));
if (dbContext.DataType == DataBaseType.Oracle)//添加Oracle主键
{
session.ExecuteNonQuery(string.Format("alter table {0} add constraint PK_{0} primary key ({1})",
dbTable.Name, sql_pri.ToString().TrimStart(',')));
}
}