/// <summary>
/// 读取SQL Server
/// </summary>
/// <returns></returns>
private static List <TBField> ReaderSQLServer(DBContext dbContext)
{
#region sql
/// <summary>
/// 获取字段信息以及注释等sql语句
/// </summary>
string sql = @"select sysobjects.name as TableName,syscolumns.name as Name,systypes.name as DataType,
ispk= case when
exists(select 1 from sysobjects where xtype='PK' and parent_obj=syscolumns.id and name in
(select name from sysindexes where indid
in(select indid from sysindexkeys where id = syscolumns.id AND colid=syscolumns.colid)))
then 1 else 0 end,columndescription= isnull(sys.extended_properties.[value],''),
tabledescription=case when (select count(*) from sys.extended_properties
where major_id=sysobjects.id and minor_id=0)=1 then
(select [value] from sys.extended_properties where major_id=sysobjects.id and minor_id=0) else '' end
from sysobjects inner join syscolumns on sysobjects.id = syscolumns.id
left join systypes on syscolumns.xtype = systypes.xusertype
left join sys.extended_properties on syscolumns.id = sys.extended_properties.major_id
and syscolumns.colid = sys.extended_properties.minor_id
where sysobjects.xtype='U' and sysobjects.name <>'sysdiagrams'";
#endregion
using (DBSession db = DBSession.TryGet(dbContext.DbKey))
{
return(db.GetCustomerList <TBField>(sql));
}
}
/// <summary>
/// 读取Oracle
/// </summary>
/// <returns></returns>
private static List <TBField> ReaderOracle(DBContext dbContext)
{
using (DBSession db = DBSession.TryGet(dbContext.DbKey))
{
//获取所有table
return(db.GetCustomerList <TBField>("select TABLE_NAME TableName,COLUMN_NAME Name,data_type DataType from user_tab_cols"));
}
}
/// <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);
}
}
}
}
}
}
}