protected override List <ModuleResultElement> GetResultElements(SqlConnection connection, string schema, string name)
{
var dtResult = new DataTable();
var result = new List <ModuleResultElement>();
var sql = $"exec dbo.sp_describe_first_result_set N'[{schema}].[{name}]';";
var adapter = new SqlDataAdapter
{
SelectCommand = new SqlCommand(sql, connection)
};
adapter.Fill(dtResult);
int rCounter = 0;
foreach (DataRow res in dtResult.Rows)
{
var parameter = new ModuleResultElement()
{
Name = string.IsNullOrEmpty(res["name"].ToString()) ? $"Col{rCounter}" : res["name"].ToString(),
StoreType = string.IsNullOrEmpty(res["system_type_name"].ToString()) ? res["user_type_name"].ToString() : res["system_type_name"].ToString(),
Ordinal = int.Parse(res["column_ordinal"].ToString()),
Nullable = (bool)res["is_nullable"],
};
result.Add(parameter);
rCounter++;
}
return(result);
}
public static Type ClrType(this ModuleResultElement moduleResultElement)
{
if (moduleResultElement is null)
{
throw new ArgumentNullException(nameof(moduleResultElement));
}
return(GetClrType(moduleResultElement.StoreType, moduleResultElement.Nullable));
}
protected override List <List <ModuleResultElement> > GetResultElementLists(SqlConnection connection, Routine module, bool multipleResults, bool useLegacyResultSetDiscovery)
{
if (module is null)
{
throw new ArgumentNullException(nameof(module));
}
using var dtResult = new DataTable();
var list = new List <ModuleResultElement>();
var sql = $@"
SELECT
c.name,
COALESCE(type_name(c.system_type_id), type_name(c.user_type_id)) AS type_name,
c.column_id AS column_ordinal,
c.is_nullable
FROM sys.columns c
WHERE object_id = OBJECT_ID('{module.Schema}.{module.Name}');";
#pragma warning disable CA2100 // Review SQL queries for security vulnerabilities
using var adapter = new SqlDataAdapter
{
SelectCommand = new SqlCommand(sql, connection),
};
#pragma warning restore CA2100 // Review SQL queries for security vulnerabilities
adapter.Fill(dtResult);
int rCounter = 0;
foreach (DataRow res in dtResult.Rows)
{
var parameter = new ModuleResultElement()
{
Name = string.IsNullOrEmpty(res["name"].ToString()) ? $"Col{rCounter}" : res["name"].ToString(),
StoreType = res["type_name"].ToString(),
Ordinal = int.Parse(res["column_ordinal"].ToString(), CultureInfo.InvariantCulture),
Nullable = (bool)res["is_nullable"],
};
list.Add(parameter);
rCounter++;
}
var result = new List <List <ModuleResultElement> >
{
list,
};
return(result);
}
private static List <List <ModuleResultElement> > GetFirstResultSet(SqlConnection connection, string schema, string moduleName)
{
using var dtResult = new DataTable();
var list = new List <ModuleResultElement>();
var sql = $"exec dbo.sp_describe_first_result_set N'[{schema}].[{moduleName}]';";
#pragma warning disable CA2100 // Review SQL queries for security vulnerabilities
using var adapter = new SqlDataAdapter
{
SelectCommand = new SqlCommand(sql, connection),
};
#pragma warning restore CA2100 // Review SQL queries for security vulnerabilities
adapter.Fill(dtResult);
int rCounter = 0;
foreach (DataRow row in dtResult.Rows)
{
var name = row["name"].ToString();
if (string.IsNullOrEmpty(name))
{
continue;
}
var parameter = new ModuleResultElement()
{
Name = name,
StoreType = string.IsNullOrEmpty(row["system_type_name"].ToString()) ? row["user_type_name"].ToString() : row["system_type_name"].ToString(),
Ordinal = int.Parse(row["column_ordinal"].ToString(), CultureInfo.InvariantCulture),
Nullable = (bool)row["is_nullable"],
};
list.Add(parameter);
rCounter++;
}
var result = new List <List <ModuleResultElement> >
{
list,
};
return(result);
}
protected override List <List <ModuleResultElement> > GetResultElementLists(SqlConnection connection, Routine module, bool multipleResultSets = false)
{
var dtResult = new DataTable();
var list = new List <ModuleResultElement>();
var sql = $@"
SELECT
c.name,
COALESCE(type_name(c.system_type_id), type_name(c.user_type_id)) AS type_name,
c.column_id AS column_ordinal,
c.is_nullable
FROM sys.columns c
WHERE object_id = OBJECT_ID('{module.Schema}.{module.Name}');";
var adapter = new SqlDataAdapter
{
SelectCommand = new SqlCommand(sql, connection)
};
adapter.Fill(dtResult);
int rCounter = 0;
foreach (DataRow res in dtResult.Rows)
{
var parameter = new ModuleResultElement()
{
Name = string.IsNullOrEmpty(res["name"].ToString()) ? $"Col{rCounter}" : res["name"].ToString(),
StoreType = res["type_name"].ToString(),
Ordinal = int.Parse(res["column_ordinal"].ToString()),
Nullable = (bool)res["is_nullable"],
};
list.Add(parameter);
rCounter++;
}
var result = new List <List <ModuleResultElement> >
{
list
};
return(result);
}
public static Type ClrType(this ModuleResultElement moduleResultElement)
{
return(GetClrType(moduleResultElement.StoreType, moduleResultElement.Nullable));
}