/// <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 TableValuedParameter(string webApiName,
DBObjectName userDefinedType,
NamingMappingHandler namingMappingHandler,
IReadOnlyCollection <SqlFieldDescription> fields
) : base(null, webApiName)
{
UserDefinedType = userDefinedType;
this.namingMappingHandler = namingMappingHandler;
this.fields = fields;
}
public TableValuedParameter(string webApiName,
DBObjectName userDefinedType,
SqlHelper sqlHelper,
NamingMappingHandler namingMappingHandler
) : base(null, webApiName)
{
UserDefinedType = userDefinedType;
this.namingMappingHandler = namingMappingHandler;
this.fields = sqlHelper.GetTableTypeFields(userDefinedType);
}
public void TestDBObjectName()
{
var tester1 = DBObjectName.FromString("Test.[dbo].hello");
var tester2 = DBObjectName.FromString("Test.dbo.\"hello\"");
Assert.AreEqual(tester1, tester2);
Assert.AreEqual("Test", tester1.DataBaseName);
Assert.AreEqual("dbo", tester1.Schema);
Assert.AreEqual("hello", tester1.Name);
var tester3 = DBObjectName.FromString("[hallo bla]");
var tester4 = DBObjectName.FromString("\"hallo bla\"");
Assert.AreEqual(tester3, tester4);
}
public SwaggerGeneration.WebApiParameter[] GetApiParameters(Entity ent, DBObjectName sp)
{
var spParams = sPParametersProvider.GetSPParameters(sp, dbConnection);
var webApiNames = namingMappingHandler.GetNames(spParams.Select(s => s.SqlName)).ToArray();
var apiParamsRaw = spParams.Select((s, index) =>
(SwaggerGeneration.WebApiParameter) new SwaggerGeneration.DbApiParameter(s.SqlName, webApiNames[index],
s.DbType, s.IsNullable, s.UserDefinedType, sqlHelper, namingMappingHandler)
);
var apiParams = new LinkedList <SwaggerGeneration.WebApiParameter>(apiParamsRaw);
foreach (var inter in parameterInterceptors)
{
inter.Intercept(apiParams);
}
return(apiParams.ToArray());
}
public DbApiParameter(string sqlName, string webApiName,
SqlType sqlType, bool isNullable,
DBObjectName userDefinedType,
SqlHelper sqlHelper,
NamingMappingHandler namingMappingHandler) : base(sqlName, webApiName)
{
this.DbType = sqlType;
this.IsNullable = isNullable;
UserDefinedType = userDefinedType;
if (userDefinedType != null)
{
this.TableParameter = new TableValuedParameter(
GetSqlTypeWebApiName(this.UserDefinedType),
this.UserDefinedType,
sqlHelper, namingMappingHandler);
}
}
/// <summary>
/// Gets the return fields of the first result set of a procecure
/// </summary>
/// <param name="model">The procedure</param>
/// <param name="persistSPResultSetPath">True to save those result sets in ResultSets Folder</param>
/// <param name="fallBackSPExecutionParameters">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 Task <IReadOnlyCollection <SqlFieldDescription> > GetResultSet(DbConnection dbConnection,
DBObjectName model,
DBObjectType dBObjectType,
string?persistSPResultSetPath,
IReadOnlyDictionary <string, object?>?fallBackSPExecutionParameters = null)
{
switch (dBObjectType)
{
case DBObjectType.StoredProcedure:
return(GetSPResultSet(dbConnection, model, persistSPResultSetPath, fallBackSPExecutionParameters));
case DBObjectType.TableOrView:
return(GetTableOrViewFields(dbConnection, model));
case DBObjectType.TableType:
return(GetTableTypeFields(dbConnection, model));
case DBObjectType.TableValuedFunction:
return(GetFunctionFields(dbConnection, model));
default:
throw new NotSupportedException($"Db Type {dBObjectType} not supported");
}
}
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());
}
private WebApiParameter[] GetBodyOrQueryStringParameters(Entity ent, DBObjectName sp)
{
return(parametersProvider.GetApiParameters(ent, sp)
.Where(s => s.WebApiName != null && !ent.ContainsPathParameter(s.WebApiName))
.ToArray());
}
public Task <IReadOnlyCollection <SqlFieldDescription> > GetFunctionFields(DbConnection dbConnection, DBObjectName tableOrView)
{
return(GetDatabaseColumnMetadata(dbConnection, tableOrView, "ROUTINE_COLUMNS"));
}
private static string GetSqlTypeWebApiName(DBObjectName userDefinedType)
{
return((userDefinedType.Schema == "dbo" ? "" :
userDefinedType.Schema + ".") + userDefinedType.Name);
}
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);
}
}
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);
}
}
public Task <IReadOnlyCollection <SqlFieldDescription> > GetTableOrViewFields(DbConnection dbConnection, DBObjectName tableOrView)
{
if (dbConnection.IsSQLite())
{
return(GetSqliteColumns(dbConnection, tableOrView));
}
return(GetDatabaseColumnMetadata(dbConnection, tableOrView, "COLUMNS"));
}
/// <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[] { });
}
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> > 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);
}
}
