/// <summary>
/// 是否存在表
/// </summary>
/// <param name="query"></param>
/// <param name="tableName"></param>
/// <returns></returns>
private static bool IsExistsTable(DataQuery query, string tableName)
{
using (var db = new DataContext(query.Key))
{
var param = new List <DbParameter>();
var result = false;
var tempParam = DbProviderFactories.GetFactory(query.Config).CreateParameter();
tempParam.ParameterName = "name";
tempParam.Value = tableName.ToUpper();
param.Add(tempParam);
if (query.Config.DbType == DataDbType.Oracle)
{
var sql = "select count(0) count from user_tables where table_name=:name";
result = db.ExecuteSqlList(sql, param.ToArray(), query.Config.IsOutSql, false).DicList[0].GetValue("count").ToStr().ToInt(0) == 1;
}
if (query.Config.DbType == DataDbType.SqlServer)
{
var sql = "select count(0) count from dbo.sysobjects where upper(name)=@name";
result = db.ExecuteSqlList(sql, param.ToArray(), query.Config.IsOutSql, false).DicList[0].GetValue("count").ToStr().ToInt(0) == 1;
}
if (query.Config.DbType == DataDbType.MySql)
{
var sql = "select count(0) count from information_schema.tables where upper(table_name)=?name";
result = db.ExecuteSqlList(sql, param.ToArray(), query.Config.IsOutSql, false).DicList[0].GetValue("count").ToStr().ToInt(0) == 1;
}
return(result);
}
}
/// <summary>
/// 查询是否主键
/// </summary>
/// <param name="item"></param>
/// <param name="name"></param>
/// <returns></returns>
private static Dictionary <string, object> CheckKey(DataQuery item, string name, string tableName)
{
using (var db = new DataContext(item.Key))
{
var param = new List <DbParameter>();
var sql = "";
if (item.Config.DbType == DataDbType.SqlServer)
{
var tempParam = DbProviderFactories.GetFactory(item.Config).CreateParameter();
tempParam.ParameterName = "tableName";
tempParam.Value = tableName.ToUpper();
param.Add(tempParam);
tempParam = DbProviderFactories.GetFactory(item.Config).CreateParameter();
tempParam.ParameterName = "colName";
tempParam.Value = name.ToUpper();
param.Add(tempParam);
sql = "select CONSTRAINT_NAME PK from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where upper(TABLE_NAME)=@tableName and upper(COLUMN_NAME)=@colName";
}
if (item.Config.DbType == DataDbType.Oracle)
{
var tempParam = DbProviderFactories.GetFactory(item.Config).CreateParameter();
tempParam.ParameterName = "tableName";
tempParam.Value = tableName.ToUpper();
param.Add(tempParam);
tempParam = DbProviderFactories.GetFactory(item.Config).CreateParameter();
tempParam.ParameterName = "colName";
tempParam.Value = name.ToUpper();
param.Add(tempParam);
sql = @"select a.CONSTRAINT_NAME PK from all_constraints a
inner join all_cons_columns b on a.TABLE_NAME=b.TABLE_NAME and a.CONSTRAINT_NAME=b.CONSTRAINT_NAME
where a.table_name=:tableName and a.constraint_type = 'P' and b.COLUMN_NAME=:colName";
}
if (item.Config.DbType == DataDbType.MySql)
{
var tempParam = DbProviderFactories.GetFactory(item.Config).CreateParameter();
tempParam.ParameterName = "tableName";
tempParam.Value = tableName.ToUpper();
param.Add(tempParam);
tempParam = DbProviderFactories.GetFactory(item.Config).CreateParameter();
tempParam.ParameterName = "colName";
tempParam.Value = name.ToUpper();
param.Add(tempParam);
sql = "select constraint_name PK from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where upper(TABLE_NAME)=?tableName and constraint_name='PRIMARY' and upper(column_name)=?colName";
}
return(db.ExecuteSqlList(sql, param.ToArray(), item.Config.IsOutSql, false).DicList.FirstOrDefault() ?? new Dictionary <string, object>());
}
}
/// <summary>
/// 获取表结构
/// </summary>
private static TableModel GetTable(DataQuery item, string tableName)
{
var result = new TableModel();
result.Column = result.Column ?? new List <ColumnModel>();
using (var db = new DataContext(item.Key))
{
var param = new List <DbParameter>();
if (item.Config.DbType == DataDbType.Oracle)
{
#region oracle
//参数
var tempParam = DbProviderFactories.GetFactory(item.Config).CreateParameter();
tempParam.ParameterName = "name";
tempParam.Value = tableName.ToUpper();
param.Add(tempParam);
//表
var sql = "select a.table_name,comments from user_tables a inner join user_tab_comments b on a.TABLE_NAME = b.TABLE_NAME and a.table_name = :name";
var dic = db.ExecuteSqlList(sql, param.ToArray(), item.Config.IsOutSql, false).DicList[0];
result.Name = dic.GetValue("table_name").ToStr();
result.Comments = dic.GetValue("comments").ToStr();
//列
sql = string.Format(@"select a.column_name,data_type,data_length,b.comments,
(select count(0) from user_cons_columns aa, user_constraints bb where aa.constraint_name = bb.constraint_name and bb.constraint_type = 'P' and bb.table_name = :name and aa.column_name = a.column_name) iskey,
nullable,data_precision,data_scale
from user_tab_columns a inner join user_col_comments b
on a.table_name =:name and a.table_name = b.table_name and a.column_name = b.column_name order by a.column_id asc");
var dicList = db.ExecuteSqlList(sql, param.ToArray(), item.Config.IsOutSql, false).DicList;
dicList.ForEach(a =>
{
var model = new ColumnModel();
model.Comments = a.GetValue("comments").ToStr();
model.DataType = a.GetValue("data_type").ToStr();
model.IsKey = a.GetValue("iskey").ToStr() == "1" ? true : false;
model.IsNull = a.GetValue("nullable").ToStr() == "Y" ? true : false;
model.Length = a.GetValue("data_length").ToStr().ToInt(0);
model.Name = a.GetValue("column_name").ToStr();
model.Precision = a.GetValue("data_precision").ToStr().ToInt(0);
model.Scale = a.GetValue("data_scale").ToStr().ToInt(0);
result.Column.Add(model);
});
#endregion
}
if (item.Config.DbType == DataDbType.MySql)
{
#region MySql
//参数
var tempParam = DbProviderFactories.GetFactory(item.Config).CreateParameter();
tempParam.ParameterName = "name";
tempParam.Value = tableName.ToUpper();
param.Add(tempParam);
//表
var sql = "select table_name,table_comment count from information_schema.tables where upper(table_name)=?name";
var dic = db.ExecuteSqlList(sql, param.ToArray(), item.Config.IsOutSql, false).DicList[0];
result.Name = dic.GetValue("table_name").ToStr();
result.Comments = dic.GetValue("table_comment").ToStr();
//列
sql = string.Format(@"select column_name,data_type,character_maximum_length,column_comment,
(select count(0) from INFORMATION_SCHEMA.KEY_COLUMN_USAGE a where upper(TABLE_NAME)=?name and constraint_name='PRIMARY' and c.column_name=a.column_name) iskey,
is_nullable,numeric_precision,numeric_scale from information_schema.columns c where upper(table_name)=?name order by ordinal_position asc");
var dicList = db.ExecuteSqlList(sql, param.ToArray(), item.Config.IsOutSql, false).DicList ?? new List <Dictionary <string, object> >();
dicList.ForEach(a =>
{
var model = new ColumnModel();
model.Comments = a.GetValue("column_comment").ToStr();
model.DataType = a.GetValue("data_type").ToStr();
model.IsKey = a.GetValue("iskey").ToStr() == "1" ? true : false;
model.IsNull = a.GetValue("is_nullabl").ToStr() == "YES" ? true : false;
model.Length = a.GetValue("character_maximum_length").ToStr().ToInt(0);
model.Name = a.GetValue("column_name").ToStr();
model.Precision = a.GetValue("numeric_precision").ToStr().ToInt(0);
model.Scale = a.GetValue("numeric_scale").ToStr().ToInt(0);
result.Column.Add(model);
});
#endregion
}
if (item.Config.DbType == DataDbType.SqlServer)
{
#region SqlServer
//参数
var tempParam = DbProviderFactories.GetFactory(item.Config).CreateParameter();
tempParam.ParameterName = "name";
tempParam.Value = tableName.ToUpper();
param.Add(tempParam);
//表
var sql = "select name,(select top 1 value from sys.extended_properties where major_id=object_id(a.name) and minor_id=0) as value from sys.objects a where type = 'U'and upper(name) = @name";
var dic = db.ExecuteSqlList(sql, param.ToArray(), item.Config.IsOutSql, false).DicList[0];
result.Name = dic.GetValue("name").ToStr();
result.Comments = dic.GetValue("value").ToStr();
//列
sql = string.Format(@"select a.name,(select top 1 name from sys.systypes c where a.xtype=c.xtype) as type ,
length,b.value,(select count(0) from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where TABLE_NAME='@name' and COLUMN_NAME=a.name) as iskey,
isnullable,prec,scale
from syscolumns a left join sys.extended_properties b
on major_id = id and minor_id = colid and b.name ='MS_Description'
where a.id=object_id('@name') order by a.colid asc");
var dicList = db.ExecuteSqlList(sql, param.ToArray(), item.Config.IsOutSql, false).DicList;
dicList.ForEach(a =>
{
var model = new ColumnModel();
model.Comments = a.GetValue("value").ToStr();
model.DataType = a.GetValue("type").ToStr();
model.IsKey = a.GetValue("iskey").ToStr() == "1" ? true : false;
model.IsNull = a.GetValue("isnullable").ToStr() == "1" ? true : false;
model.Length = a.GetValue("length").ToStr().ToInt(0);
model.Name = a.GetValue("name").ToStr();
model.Precision = a.GetValue("prec").ToStr().ToInt(0);
model.Scale = a.GetValue("scale").ToStr().ToInt(0);
result.Column.Add(model);
});
#endregion
}
result.Column.ForEach(a =>
{
if (string.Compare(a.DataType, "nchar", true) == 0 || string.Compare(a.DataType, "nvarchar", true) == 0 ||
string.Compare(a.DataType, "nvarchar2", true) == 0 || string.Compare(a.DataType, "ntext", true) == 0 ||
string.Compare(a.DataType, "nclob", true) == 0)
{
a.Length = a.Length / 2;
}
});
return(result);
}
}
