/// <summary>
/// 创建列
/// </summary>
/// <param name="table"></param>
/// <param name="column"></param>
private static void CreateColumn(DBSession session, string tbName, DBColumn column)
{
string sql = string.Format("alter table {0} add {1} {2} {3} {4}",
tbName, column.Name, column.DataType,
string.IsNullOrWhiteSpace(column.Default) ? "" : "default " + column.Default,
column.NotNull ? "NOT NULL" : "");
session.ExecuteNonQuery(sql);
}
/// <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(',')));
}
}
/// <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);
}