private static async Task <IReadOnlyCollection <string> > GetSqlitePrimaryKeys(DbConnection dbConnection, DBObjectName tableOrView)
{
await dbConnection.AssureOpenAsync();
string sql = $"PRAGMA table_info({tableOrView.ToString(false, true)}) ";
var cmd = dbConnection.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.Text;
using (var rdr = await cmd.ExecuteReaderAsync())
{
if (!rdr.HasRows)
{
return(Array.Empty <string>());
}
List <string> list = new List <string>();
while (rdr.Read())
{
bool isPk = rdr.GetBoolean("pk");
if (isPk)
{
list.Add(rdr.GetNString("name") !);
}
}
return(list);
}
}
public async Task <SqlFieldDescription[]> GetSPResultSetByUsingExecute(DbConnection dbConnection, DBObjectName model,
IReadOnlyDictionary <string, object?> fallBackExecutionParameters)
{
await dbConnection.AssureOpenAsync();
ValidateNoSuspicousSql(model.Schema, false);
ValidateNoSuspicousSql(model.Name, false);
string procName = model.ToString(false, true);
string paramText = string.Join(", ", fallBackExecutionParameters.Select(s => "@" + ValidateNoSuspicousSql(s.Key, true) + "=@" + s.Key));
string commandText =
$@"set xact_abort on
begin tran
exec {procName} {paramText}
rollback";
var cmd = dbConnection.CreateCommand();
cmd.CommandText = commandText;
cmd.CommandType = System.Data.CommandType.Text;
foreach (var prms in fallBackExecutionParameters)
{
cmd.AddCommandParameter(prms.Key, prms.Value);
}
using (var res = await cmd.ExecuteReaderAsync())
{
res.Read();
return(Enumerable.Range(0, res.FieldCount)
.Select(i => new SqlFieldDescription(
dbType: SqlType.GetSomeSqlType(res.GetFieldType(i)),
name: res.GetName(i),
isNullable: true,
maxLength: null
)).ToArray());
}
}
private static async Task <IReadOnlyCollection <SqlFieldDescription> > GetSqliteColumns(DbConnection dbConnection, DBObjectName tableOrView)
{
await dbConnection.AssureOpenAsync();
string sql = $"PRAGMA table_info({tableOrView.ToString(false, true)}) ";
var cmd = dbConnection.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.Text;
using (var rdr = await cmd.ExecuteReaderAsync())
{
if (!rdr.HasRows)
{
return(Array.Empty <SqlFieldDescription>());
}
List <SqlFieldDescription> list = new List <SqlFieldDescription>();
while (rdr.Read())
{
list.Add(new SqlFieldDescription(
isNullable: !rdr.GetBoolean("notnull"),
name: rdr.GetNString("name") !,
dbType: SqlType.GetSqlType(rdr.GetNString("type") !),
maxLength: -1
));
}
return(list);
}
}
/// <summary>
/// Get all parameter names of a Stored Procedure
/// </summary>
/// <returns></returns>
public async Task <IReadOnlyCollection <SPParameter> > GetSPParameters(DBObjectName storedProcedure, DbConnection con)
{
var cachedValue = await cache.TryGetValue(storedProcedure);
if (cachedValue != null)
{
return(cachedValue);
}
string command = @"SELECT PARAMETER_NAME, DATA_TYPE, USER_DEFINED_TYPE_SCHEMA,
USER_DEFINED_TYPE_NAME, PARAMETER_MODE, CHARACTER_MAXIMUM_LENGTH
FROM information_schema.parameters
WHERE SPECIFIC_NAME = @SPName AND SPECIFIC_SCHEMA=isnull(@Schema, schema_NAME()) AND PARAMETER_NAME<>''";
await con.AssureOpenAsync();
DbCommand cmd = con.CreateCommand();
cmd.CommandText = command;
cmd.AddCommandParameter("@SPName", storedProcedure.Name)
.AddCommandParameter("@Schema", storedProcedure.Schema);
List <SPParameter> resultL = new List <SPParameter>();
using (var reader = await cmd.ExecuteReaderAsync())
{
if (reader.HasRows)
{
while (reader.Read())
{
var name = reader.GetString(0);
name = name.StartsWith("@") ? name.Substring(1) : name;
string type = reader.GetString(1);
(string userDefinedSchema, string userDefinedName) = (reader.GetNString(2) !, reader.GetNString(3) !);
DBObjectName?userDefinedType = type == "table type" ?
new DBObjectName(userDefinedSchema, userDefinedName) : null;
string parameterMode = reader.GetString(4);
System.Data.ParameterDirection?parameterDirection = parameterMode.Equals("IN", System.StringComparison.CurrentCultureIgnoreCase)
? System.Data.ParameterDirection.Input:
parameterMode.Equals("OUT", System.StringComparison.CurrentCultureIgnoreCase) ? System.Data.ParameterDirection.Output:
parameterMode.Equals("INOUT", System.StringComparison.CurrentCultureIgnoreCase) ? System.Data.ParameterDirection.InputOutput
: (System.Data.ParameterDirection?)null;
if (parameterDirection == null)
{
logger.LogWarning($"Cannot parse Parameter mode {parameterMode} of {name} of {storedProcedure}");
}
int?maxLength = reader.GetNInt32(5);
resultL.Add(new SPParameter(name, type, userDefinedType, parameterDirection ?? System.Data.ParameterDirection.Input, maxLength));
}
}
}
var result = resultL.ToArray();
await cache.TryAdd(storedProcedure.ToString(), result);
return(result);
}
public async Task <IReadOnlyCollection <SqlFieldDescription> > GetTableTypeFields(DbConnection dbConnection, DBObjectName tableType)
{
string sql = $@"
SELECT c.name as ColumnName,
CASE WHEN t.name ='sysname' THEN 'nvarchar' ELSE t.name END AS TypeName,
c.is_nullable,
c.max_length
FROM sys.columns c
inner join sys.types t ON t.user_type_id=c.user_type_id
WHERE object_id IN (
SELECT tt.type_table_object_id
FROM sys.table_types tt
inner join sys.schemas sc ON sc.schema_id=tt.schema_id
WHERE tt.name = @Name and sc.name=isnull(@Schema, schema_NAME())
);";
await dbConnection.AssureOpenAsync();
var cmd = dbConnection.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.Text;
cmd.AddCommandParameter("@Name", tableType.Name);
cmd.AddCommandParameter("@Schema", tableType.Schema);
List <SqlFieldDescription> list = new List <SqlFieldDescription>();
using (var rdr = await cmd.ExecuteReaderAsync())
{
while (rdr.Read())
{
list.Add(new SqlFieldDescription(
isNullable: rdr.GetBoolean("is_nullable"),
name: rdr.GetNString("ColumnName") !,
dbType: SqlType.GetSqlType(rdr.GetNString("TypeName") !),
maxLength: getMaxLength(Convert.ToInt32(rdr.GetValue(3)), rdr.GetNString("TypeName"))
));
}
}
return(list.ToArray());
}
public async Task <IReadOnlyCollection <(DBObjectName Name, TableOrViewType Type)> > GetTablesAndViews(DbConnection dbConnection, TableOrViewType?typeFilter = null)
{
await dbConnection.AssureOpenAsync();
bool sqlite = dbConnection.IsSQLite();
string sql = sqlite ? $"SELECT name, type from sqlite_master" : "SELECT TABLE_NAME, TABLE_TYPE, TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES";
if (sqlite && typeFilter == null)
{
sql += " WHERE type = 'view' OR type = 'table'";
}
else if (sqlite)
{
sql += " WHERE type = @type";
}
if (!sqlite && typeFilter != null)
{
sql += " WHERE TABLE_TYPE=@Type";
}
string?filterType = null;
if (sqlite && typeFilter == TableOrViewType.Table)
{
filterType = "table";
}
else if (sqlite && typeFilter == TableOrViewType.View)
{
filterType = "view";
}
else if (typeFilter == TableOrViewType.Table)
{
filterType = "BASE TABLE";
}
else if (typeFilter == TableOrViewType.View)
{
filterType = "VIEW";
}
var cmd = dbConnection.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.Text;
cmd.AddCommandParameter("@type", filterType);
using (var rdr = await cmd.ExecuteReaderAsync())
{
if (!rdr.HasRows)
{
return(Array.Empty <(DBObjectName name, TableOrViewType type)>());
}
List <(DBObjectName name, TableOrViewType type)> list = new();
while (rdr.Read())
{
string name = rdr.GetString(0);
string type = rdr.GetString(1).ToUpper();
string? schema = sqlite ? null : rdr.GetString(2);
TableOrViewType typeT = type == "VIEW" ? TableOrViewType.View : TableOrViewType.Table;
list.Add((new DBObjectName(schema, name), typeT));
}
return(list);
}
}
/// <summary>
/// Gets the return fields of the first result set of a procecure
/// </summary>
/// <param name="model">The procedure</param>
/// <param name="persistResultSets">True to save those result sets in ResultSets Folder</param>
/// <param name="fallBackExecutionParameters">If you set this parameter and sp_describe_first_result_set does not work,
/// the procedure will get executed to retrieve results. Pay attention to provide wise options!</param>
/// <returns></returns>
public async Task <IReadOnlyCollection <SqlFieldDescription> > GetSPResultSet(DbConnection dbConnection,
DBObjectName model,
string?persistResultSetPath,
IReadOnlyDictionary <string, object?>?fallBackExecutionParameters = null)
{
SqlFieldDescription[]? dataToWrite = null;
var cachejsonFile = persistResultSetPath != null?System.IO.Path.Combine(persistResultSetPath,
model.ToString() + ".json") : null;
try
{
List <SqlFieldDescription> resultSet = new List <SqlFieldDescription>();
await dbConnection.AssureOpenAsync();
using (var rdr = await dbConnection.CreateSPCommand("sp_describe_first_result_set")
.AddCommandParameter("tsql", model.ToString())
.ExecuteReaderAsync())
{
while (rdr.Read())
{
resultSet.Add(new SqlFieldDescription(
name: rdr.GetNString("name") !,
dbType: SqlType.GetSqlType(rdr.GetNString("system_type_name") !),
isNullable: rdr.GetBoolean("is_nullable"),
maxLength: getMaxLength(Convert.ToInt32(rdr.GetValue(rdr.GetOrdinal("max_length")) !),
SqlType.GetSqlType(rdr.GetNString("system_type_name") !).DbType)
));
}
}
if (persistResultSetPath != null)
{
if (resultSet.Count > 0)
{
try
{
if (!System.IO.Directory.Exists(persistResultSetPath))
{
System.IO.Directory.CreateDirectory(persistResultSetPath);
}
var jsAr = resultSet.Select(s => s.Serialize()).ToArray();
string json = SerializeJson(jsAr);
System.IO.File.WriteAllText(cachejsonFile !, json);
}
catch (Exception ercache)
{
logger.LogWarning("Could not cache Results set of {0}. Reason:\r\n{1}", model, ercache);
}
}
}
dataToWrite = resultSet
.Cast <SqlFieldDescription>()
.ToArray();
}
catch (Exception err)
{
logger.LogError(err, $"Error getting result set from {model}");
if (fallBackExecutionParameters != null)
{
dataToWrite = await GetSPResultSetByUsingExecute(dbConnection, model, fallBackExecutionParameters);
if (cachejsonFile != null)
{
if (!System.IO.Directory.Exists(persistResultSetPath))
{
System.IO.Directory.CreateDirectory(persistResultSetPath !);
}
var jsAr = dataToWrite.Select(s => s.Serialize()).ToArray();
var json = SerializeJson(jsAr);
try
{
System.IO.File.WriteAllText(cachejsonFile, json);
}
catch
{
logger.LogWarning($"Cound not write cache file {cachejsonFile}");
}
}
}
else
{
dataToWrite = null;
}
}
if (dataToWrite == null && persistResultSetPath != null && System.IO.File.Exists(cachejsonFile))
{
try
{
// Not Sucessfully gotten data
var json = System.IO.File.ReadAllText(cachejsonFile);
var resJS = DeserializeJson <List <Dictionary <string, object> > >(json);
var res = resJS.Select(s => SqlFieldDescription.FromJson((IReadOnlyDictionary <string, object?>)s)).ToArray();
return(res.Cast <SqlFieldDescription>().ToArray());
}
catch (Exception err)
{
logger.LogWarning("Could not get cache {0}. Reason:\r\n{1}", model, err);
}
}
return(dataToWrite ?? new SqlFieldDescription[] { });
}
private static async Task <IReadOnlyCollection <SqlFieldDescription> > GetDatabaseColumnMetadata(DbConnection dbConnection, DBObjectName tableOrView,
string informationschema_table)
{
string sql = $@"
SELEcT IS_NULLABLE AS is_nullable,
COLUMN_NAME as ColumnName,
DATA_TYPE as TypeName,
CHARACTER_MAXIMUM_LENGTH as MaxLength,
TABLE_SCHEMA
FROM INFORMATION_SCHEMA.{informationschema_table}
WHERE TABLE_NAME= @Name AND (TABLE_SCHEMA=@Schema OR @Schema is null)" ;
await dbConnection.AssureOpenAsync();
var cmd = dbConnection.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = System.Data.CommandType.Text;
cmd.AddCommandParameter("@Name", tableOrView.Name);
cmd.AddCommandParameter("@Schema", tableOrView.Schema);
using (var rdr = await cmd.ExecuteReaderAsync())
{
if (!rdr.HasRows)
{
return(Array.Empty <SqlFieldDescription>());
}
List <SqlFieldDescription> list = new List <SqlFieldDescription>();
int schemaOrdinal = rdr.GetOrdinal("TABLE_SCHEMA");
int maxLengthOrdinal = rdr.GetOrdinal("MaxLength");
string?lastSchema = null;
while (rdr.Read())
{
string?schema = rdr.GetString(schemaOrdinal);
if (lastSchema == null)
{
lastSchema = schema;
}
else if (schema != lastSchema)
{
throw new InvalidOperationException("Schema Name is not given");
}
object nullable = rdr.GetValue(rdr.GetOrdinal("is_nullable"));
object?maxLength = rdr.IsDBNull(maxLengthOrdinal) ? null : rdr.GetValue(maxLengthOrdinal);
list.Add(new SqlFieldDescription(
isNullable: (nullable as string)?.ToUpperInvariant().Trim() == "YES" || nullable as Boolean? == true || nullable as int? == 1,
name: rdr.GetNString("ColumnName") !,
dbType: SqlType.GetSqlType(rdr.GetNString("TypeName") !),
maxLength:
maxLength == null ? null :
maxLength is int i ? i :
maxLength is long l && l > (long)int.MaxValue ? null :
maxLength is ulong l2 && l2 > (ulong)int.MaxValue ? null :
Convert.ToInt32(maxLength)
));
}
return(list);
}
}
