public void Test()
{
var json = System.IO.File.ReadAllText("dbo.spSearchPets.json");
var resJS = DeserializeJson <List <Dictionary <string, object> > >(json);
var res = resJS.Select(s => SqlFieldDescription.FromJson((IReadOnlyDictionary <string, object?>)s)).ToArray();
var ar = res.Cast <SqlFieldDescription>().ToArray();
Assert.AreEqual(4, ar.Length);
Assert.AreEqual("PetId", ar[0].Name);
Assert.AreEqual(SqlType.GetSqlType("int"), ar[0].DbType);
}
/// <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[] { });
}
