public void TestDataTable()
{
var result = _dbContext.GetListBySql <Student>("SELECT * FROM STUDENT");
_output.WriteLine(result.ToJson());
DataTable data = _dbContext.GetDataTable("SELECT * FROM STUDENT");
data.Rows.Cast <DataRow>().ForEach(x =>
{
_output.WriteLine(x["id"].ToString());
_output.WriteLine(x["name"].ToString());
_output.WriteLine(x["sex"].ToString());
});
}
/// <summary>
/// 获取所有的表
/// </summary>
/// <param name="context"></param>
/// <returns></returns>
public static DataTable GetCurrentDatabaseAllTables(this IDbContextCore context)
{
if (context == null)
{
throw new ArgumentNullException(nameof(context));
}
var db = context.GetDatabase();
var sql = string.Empty;
if (db.IsSqlServer())
{
sql = "select * from (SELECT (case when a.colorder=1 then d.name else '' end) as TableName," +
"(case when a.colorder=1 then isnull(f.value,'') else '' end) as TableComment" +
" FROM syscolumns a" +
" inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'" +
" left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0) t" +
" where t.TableName!=''";
}
else
{
throw new NotImplementedException("This method does not support current database yet.");
}
return(context.GetDataTable(sql));
}
/// <summary>
/// 获取当前表的所有列和类型
/// </summary>
/// <param name="context"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public static DataTable GetTableColumns(this IDbContextCore context, string tableName)
{
if (context == null)
{
throw new ArgumentNullException(nameof(context));
}
var db = context.GetDatabase();
var sql = string.Empty;
if (db.IsSqlServer())
{
sql = "SELECT a.name as ColName," +
"CONVERT(bit,(case when COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 then 1 else 0 end)) as IsIdentity, " +
"CONVERT(bit,(case when (SELECT count(*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid in (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK'))>0 then 1 else 0 end)) as IsPrimaryKey," +
"b.name as ColumnType," +
"COLUMNPROPERTY(a.id,a.name,'PRECISION') as ColumnLength," +
"CONVERT(bit,(case when a.isnullable=1 then 1 else 0 end)) as IsNullable, " +
"isnull(e.text,'') as DefaultValue," +
"isnull(g.[value], ' ') AS Comments " +
"FROM syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id left join sys.extended_properties f on d.id=f.class and f.minor_id=0 " +
$"where b.name is not null and d.name='{tableName}' order by a.id,a.colorder";
}
else
{
throw new NotImplementedException("This method does not support current database yet.");
}
return(context.GetDataTable(sql));
}
public static DataTable GetCurrentDatabaseAllTables(this IDbContextCore context)
{
if (context == null)
{
throw new ArgumentNullException(nameof(context));
}
var db = context.GetDatabase();
var sql = string.Empty;
if (db.IsSqlServer())
{
sql = "select * from (SELECT (case when a.colorder=1 then d.name else '' end) as TableName," +
"(case when a.colorder=1 then isnull(f.value,'') else '' end) as TableComment" +
" FROM syscolumns a" +
" inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'" +
" left join sys.extended_properties f on d.id=f.major_id and f.minor_id=0) t" +
" where t.TableName!=''";
}
else if (db.IsMySql())
{
sql =
"SELECT TABLE_NAME as TableName," +
" Table_Comment as TableComment" +
" FROM INFORMATION_SCHEMA.TABLES" +
$" where TABLE_SCHEMA = '{db.GetDbConnection().Database}'";
}
else if (db.IsNpgsql())
{
sql =
"select relname as TableName," +
" cast(obj_description(relfilenode,'pg_class') as varchar) as TableComment" +
" from pg_class c" +
" where relkind = 'r' and relname not like 'pg_%' and relname not like 'sql_%'" +
" order by relname";
}
else if (db.IsOracle())
{
sql =
"select \"a\".TABLE_NAME as \"TableName\",\"b\".COMMENTS as \"TableComment\" from USER_TABLES \"a\" JOIN user_tab_comments \"b\" on \"b\".TABLE_NAME=\"a\".TABLE_NAME";
}
else
{
throw new NotImplementedException("This method does not support current database yet.");
}
return(context.GetDataTable(sql));
}
public void Test()
{
var hj = new Core_WebAPI.ServerTest(_context).GetGroup();
//_context.GetDbSet<OtherCredit>().ConfigureLogging(p => sql.Add(p), null);
//var list = _context.GetDbSet<OtherCredit>().ToList();
//var list2 = _context.GetDbSet<TrainOtherCredit>().ToList();
var relationDbSet = _context.GetDbSet <Relation>();
var hg = (from c in relationDbSet
where c.RelationExtend.Address == "l"
select c)
.Include(p => p.RelationExtend)
.ToList();
var list = _context.GetDbSet <Relation>().Include(p => p.RelationExtend).ToList();
var list2 = _context.GetDbSet <RelationExtend>().Include(p => p.Relation).ToList();
//_context.GetDbSet<Group>().AttachRange(groupList);
//groupList.ForEach(p => p.Name += "3");
//int i = _context.SaveChanges();
var datatable = _context.GetDataTable("select * from [user].[group]", null);
}
public static IList <DbTable> GetCurrentDatabaseTableList(this IDbContextCore context)
{
var tables = context.GetCurrentDatabaseAllTables().ToList <DbTable>();
var db = context.GetDatabase();
DatabaseType dbType;
if (db.IsSqlServer())
{
dbType = DatabaseType.MSSQL;
}
else if (db.IsMySql())
{
dbType = DatabaseType.MySQL;
}
else if (db.IsNpgsql())
{
dbType = DatabaseType.PostgreSQL;
}
else if (db.IsOracle())
{
dbType = DatabaseType.Oracle;
}
else
{
throw new NotImplementedException("This method does not support current database yet.");
}
var columns = context.GetTableColumns(tables.Select(m => m.TableName).ToArray()).ToList <DbTableColumn>();
tables.ForEach(item =>
{
var dt = context.GetDataTable($"select * from {item.TableName} where 1 != 1");
item.Columns = columns.Where(m => m.TableName == item.TableName).ToList();
item.Columns.ForEach(x =>
{
x.CSharpType = dt.Columns[x.ColName].DataType.Name;
});
});
return(tables);
}
public static DataTable GetTableColumns(this IDbContextCore context, string tableName)
{
if (context == null)
{
throw new ArgumentNullException(nameof(context));
}
var db = context.GetDatabase();
var sql = string.Empty;
if (db.IsSqlServer())
{
sql = "SELECT a.name as ColName," +
"CONVERT(bit,(case when COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 then 1 else 0 end)) as IsIdentity, " +
"CONVERT(bit,(case when (SELECT count(*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid in (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK'))>0 then 1 else 0 end)) as IsPrimaryKey," +
"b.name as ColumnType," +
"COLUMNPROPERTY(a.id,a.name,'PRECISION') as ColumnLength," +
"CONVERT(bit,(case when a.isnullable=1 then 1 else 0 end)) as IsNullable, " +
"isnull(e.text,'') as DefaultValue," +
"isnull(g.[value], ' ') AS Comments " +
"FROM syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=g.major_id AND a.colid=g.minor_id left join sys.extended_properties f on d.id=f.class and f.minor_id=0 " +
$"where b.name is not null and d.name='{tableName}' order by a.id,a.colorder";
}
else if (db.IsMySql())
{
sql =
"select column_name as ColName, " +
" column_default as DefaultValue," +
" IF(extra = 'auto_increment','TRUE','FALSE') as IsIdentity," +
" IF(is_nullable = 'YES','TRUE','FALSE') as IsNullable," +
" DATA_TYPE as ColumnType," +
" CHARACTER_MAXIMUM_LENGTH as ColumnLength," +
" IF(COLUMN_KEY = 'PRI','TRUE','FALSE') as IsPrimaryKey," +
" COLUMN_COMMENT as Comments " +
$" from information_schema.columns where table_schema = '{db.GetDbConnection().Database}' and table_name = '{tableName}'";
}
else if (db.IsNpgsql())
{
sql =
"select column_name as ColName," +
"data_type as ColumnType," +
"coalesce(character_maximum_length, numeric_precision, -1) as ColumnLength," +
"CAST((case is_nullable when 'NO' then 0 else 1 end) as bool) as IsNullable," +
"column_default as DefaultValue," +
"CAST((case when position('nextval' in column_default)> 0 then 1 else 0 end) as bool) as IsIdentity, " +
"CAST((case when b.pk_name is null then 0 else 1 end) as bool) as IsPrimaryKey," +
"c.DeText as Comments" +
" from information_schema.columns" +
" left join " +
" (select pg_attr.attname as colname,pg_constraint.conname as pk_name from pg_constraint " +
" inner join pg_class on pg_constraint.conrelid = pg_class.oid" +
" inner join pg_attribute pg_attr on pg_attr.attrelid = pg_class.oid and pg_attr.attnum = pg_constraint.conkey[1]" +
$" inner join pg_type on pg_type.oid = pg_attr.atttypid where pg_class.relname = '{tableName}' and pg_constraint.contype = 'p') b on b.colname = information_schema.columns.column_name " +
" left join " +
" (select attname, description as DeText from pg_class " +
" left join pg_attribute pg_attr on pg_attr.attrelid = pg_class.oid" +
" left join pg_description pg_desc on pg_desc.objoid = pg_attr.attrelid and pg_desc.objsubid = pg_attr.attnum " +
$" where pg_attr.attnum > 0 and pg_attr.attrelid = pg_class.oid and pg_class.relname = '{tableName}') c on c.attname = information_schema.columns.column_name" +
$" where table_schema = 'public' and table_name = '{tableName}' order by ordinal_position asc";
}
else if (db.IsOracle())
{
sql = "select "
+ "a.DATA_LENGTH as ColumnLength,"
+ "a.COLUMN_NAME as ColName,"
+ "a.DATA_PRECISION as DataPrecision,"
+ "a.DATA_SCALE as DataScale,"
+ "a.DATA_TYPE as ColumnType,"
+ "decode(a.NULLABLE, 'Y', 'TRUE', 'N', 'FALSE') as IsNullable,"
+ "case when d.COLUMN_NAME is null then 'FALSE' else 'TRUE' end as IsPrimaryKey,"
+ "decode(a.IDENTITY_COLUMN, 'YES', 'TRUE', 'NO', 'FALSE') as IsIdentity,"
+ "b.COMMENTS as Comments "
+ "from user_tab_columns a "
+ "left join user_tab_comments b on b.TABLE_NAME = a.COLUMN_NAME "
+ "left join user_constraints c on c.TABLE_NAME = a.TABLE_NAME and c.CONSTRAINT_TYPE = 'P' "
+ "left join user_cons_columns d on d.CONSTRAINT_NAME = c.CONSTRAINT_NAME and d.COLUMN_NAME = a.COLUMN_NAME "
+ $"where a.Table_Name = '{tableName.ToUpper()}'";
}
else
{
throw new NotImplementedException("This method does not support current database yet.");
}
return(context.GetDataTable(sql));
}
public DataTable GetDataTable(string sql, object parameters = null)
{
return(DbContext.GetDataTable(sql, parameters));
}