public override IEnumerable <ConnectionColumn> GetConnectionColumns()
{
var type = SearchText.GetType();
if (!_MapperDictionary.TryGetValue(type, out string conditionSql))
{
throw new NotSupportedException($"{type.FullName} not support");
}
if (SearchText is string && ComparisonOperator == "=") /*Equivalent queries do not require Text,NText*/
{
conditionSql = " 'varchar','char','nchar','nvarchar' ";
}
var sql = $@"
select
T2.TABLE_CATALOG
,T2.TABLE_SCHEMA
,T2.TABLE_NAME
,T1.COLUMN_NAME
,T1.DATA_TYPE
,T1.IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS T1 with (nolock)
left join INFORMATION_SCHEMA.TABLES T2 with (nolock) on T1.TABLE_NAME = T2.TABLE_NAME
where 1 =1 and Table_Type = 'BASE TABLE'
and T1.DATA_TYPE in ({conditionSql})
{((SearchText is string)
? $"and T1.CHARACTER_MAXIMUM_LENGTH >= {SearchText.ToString().Length} " /*If the maximum length is less than the data itself, it is not necessary to include the search*/
: ""
)}
";
Command.CommandText = sql;
var result = new List <ConnectionColumn>();
using (var reader = Command.ExecuteReader())
{
while (reader.Read())
{
var data = new ConnectionColumn()
{
TableCatalog = reader.GetString(0),
TableSchema = reader.GetString(1),
TableName = reader.GetString(2),
ColumnName = reader.GetString(3),
DataType = reader.GetString(4),
IsNullable = reader.GetString(5),
};
result.Add(data);
}
}
return(result);
}
public override IEnumerable <ConnectionColumn> GetConnectionColumns()
{
var type = SearchText.GetType();
if (!_MapperDictionary.TryGetValue(type, out string conditionSql))
{
throw new NotSupportedException($"{type.FullName} not support");
}
var sql = $@"
select
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
NULLABLE as IS_NULLABLE
from user_tab_columns
where 1=1
and table_name not in (select View_name from user_views)
and DATA_TYPE in ({conditionSql})
{((SearchText is string)
? $"and DATA_LENGTH >= {SearchText.ToString().Length} " /*If the maximum length is less than the data itself, it is not necessary to include the search*/
: ""
)}
";
Command.CommandText = sql;
var result = new List <ConnectionColumn>();
var connectionInfo = Connection.GetToStringValues();
connectionInfo.TryGetValue("DatabaseName", out string databaseName);
connectionInfo.TryGetValue("InstanceName", out string instanceName);
using (var reader = Command.ExecuteReader())
{
while (reader.Read())
{
var data = new ConnectionColumn()
{
TableCatalog = databaseName,
TableSchema = instanceName,
TableName = reader.GetString(0),
ColumnName = reader.GetString(1),
DataType = reader.GetString(2),
IsNullable = reader.GetString(3),
};
result.Add(data);
}
}
return(result);
}
public override string GetMatchCountSql(ConnectionColumn column, string tableName)
{
return($@"select count(1) MatchCount
from [{tableName}] with (nolock)
where [{column.ColumnName}] {ComparisonOperator} @p ");
}
public virtual string GetMatchCountSql(ConnectionColumn column, string tableName)
{
return($@"select count(1) MatchCount
from {LeftSymbol}{tableName}{RightSymbol}
where {LeftSymbol}{column.ColumnName}{RightSymbol} {ComparisonOperator} {ParameterSymbol}p ");
}
