You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
246 lines
12 KiB
246 lines
12 KiB
using Microsoft.EntityFrameworkCore;
|
|
using System;
|
|
using System.Collections.Generic;
|
|
using System.Data;
|
|
using System.Data.Common;
|
|
using System.Linq;
|
|
using Znyc.Cloudcar.Admin.Commons.Helpers;
|
|
using Znyc.Cloudcar.Admin.Commons.IDbContext;
|
|
|
|
namespace Znyc.Cloudcar.Admin.Commons.Extensions
|
|
{
|
|
public static class EntityFrameworkCoreExtensions
|
|
{
|
|
public static DataTable GetCurrentDatabaseAllTables(this IDbContextCore context)
|
|
{
|
|
if (context == null)
|
|
{
|
|
throw new ArgumentNullException(nameof(context));
|
|
}
|
|
|
|
Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade db = context.GetDatabase();
|
|
string 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 static DataTable GetTableColumns(this IDbContextCore context, params string[] tableNames)
|
|
{
|
|
if (tableNames == null || tableNames.Length == 0)
|
|
{
|
|
return null;
|
|
}
|
|
|
|
if (context == null)
|
|
{
|
|
throw new ArgumentNullException(nameof(context));
|
|
}
|
|
|
|
Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade db = context.GetDatabase();
|
|
string sql = string.Empty;
|
|
if (db.IsSqlServer())
|
|
{
|
|
sql = "SELECT d.name as TableName,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 in ({tableNames.Select(m => $"'{m}'").Join(",")}) order by a.id,a.colorder";
|
|
}
|
|
else if (db.IsMySql())
|
|
{
|
|
sql =
|
|
"select table_name as TableName,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 in ({tableNames.Select(m => $"'{m}'").Join(",")})";
|
|
}
|
|
else if (db.IsNpgsql())
|
|
{
|
|
sql =
|
|
"select table_name as TableName,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 in ({tableNames.Select(m => $"'{m}'").Join(",")}) 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 in ({tableNames.Select(m => $"'{m}'").Join(",")})) c on c.attname = information_schema.columns.column_name" +
|
|
$" where table_schema = 'public' and table_name in ({tableNames.Select(m => $"'{m}'").Join(",")}) order by ordinal_position asc";
|
|
}
|
|
else if (db.IsOracle())
|
|
{
|
|
sql = "select a.Table_Name as TableName,"
|
|
+ "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 in ({tableNames.Select(m => $"'{m.ToUpper()}'").Join(",")})";
|
|
}
|
|
else
|
|
{
|
|
throw new NotImplementedException("This method does not support current database yet.");
|
|
}
|
|
|
|
return context.GetDataTable(sql);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 执行SQL返回受影响的行数
|
|
/// </summary>
|
|
public static int ExecuteSqlNoQuery(this IDbContextCore context, string sql, DbParameter[] sqlParams = null)
|
|
{
|
|
return ExecuteNoQuery(context, sql, sqlParams);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 执行存储过程返回IEnumerable数据集
|
|
/// </summary>
|
|
public static IEnumerable<T> ExecuteStoredProcedure<T>(this IDbContextCore context, string sql,
|
|
DbParameter[] sqlParams = null) where T : new()
|
|
{
|
|
return Execute<T>(context, sql, CommandType.StoredProcedure, sqlParams);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 执行sql返回IEnumerable数据集
|
|
/// </summary>
|
|
public static IEnumerable<T> ExecuteSqlReader<T>(this IDbContextCore context, string sql,
|
|
DbParameter[] sqlParams = null) where T : new()
|
|
{
|
|
return Execute<T>(context, sql, CommandType.Text, sqlParams);
|
|
}
|
|
|
|
private static int ExecuteNoQuery(this IDbContextCore context, string sql, DbParameter[] sqlParams)
|
|
{
|
|
Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade db = context.GetDatabase();
|
|
DbConnection connection = db.GetDbConnection();
|
|
DbCommand cmd = connection.CreateCommand();
|
|
if (connection.State == ConnectionState.Closed)
|
|
{
|
|
connection.Open();
|
|
}
|
|
|
|
cmd.CommandText = sql;
|
|
cmd.CommandType = CommandType.Text;
|
|
if (sqlParams != null)
|
|
{
|
|
cmd.Parameters.AddRange(sqlParams);
|
|
}
|
|
|
|
int result = cmd.ExecuteNonQuery();
|
|
connection.Close();
|
|
return result;
|
|
}
|
|
|
|
private static IEnumerable<T> Execute<T>(this IDbContextCore context, string sql, CommandType type,
|
|
params DbParameter[] sqlParams) where T : new()
|
|
{
|
|
Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade db = context.GetDatabase();
|
|
DbConnection connection = db.GetDbConnection();
|
|
DbCommand cmd = connection.CreateCommand();
|
|
if (connection.State == ConnectionState.Closed)
|
|
{
|
|
connection.Open();
|
|
}
|
|
|
|
cmd.CommandText = sql;
|
|
cmd.CommandType = type;
|
|
if (sqlParams != null)
|
|
{
|
|
cmd.Parameters.AddRange(sqlParams);
|
|
}
|
|
|
|
DbDataReader reader = cmd.ExecuteReader();
|
|
IEnumerable<T> result = reader.ToList<T>();
|
|
connection.Close();
|
|
return result;
|
|
}
|
|
|
|
public static object ExecuteScalar(this IDbContextCore context, string sql, params DbParameter[] sqlParams)
|
|
{
|
|
Microsoft.EntityFrameworkCore.Infrastructure.DatabaseFacade db = context.GetDatabase();
|
|
DbConnection connection = db.GetDbConnection();
|
|
DbCommand cmd = connection.CreateCommand();
|
|
if (connection.State == ConnectionState.Closed)
|
|
{
|
|
connection.Open();
|
|
}
|
|
|
|
cmd.CommandText = sql;
|
|
cmd.CommandType = CommandType.Text;
|
|
if (sqlParams != null)
|
|
{
|
|
cmd.Parameters.AddRange(sqlParams);
|
|
}
|
|
|
|
object result = cmd.ExecuteScalar();
|
|
connection.Close();
|
|
return result;
|
|
}
|
|
}
|
|
}
|