/// <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); } }