/// <summary>
/// This helper converts the input list of native pararmeters to the
/// equivalent SqlParameter.
/// </summary>
/// <returns></returns>
private static SqlParameter[] ConvertToSqlParams(TVPProcInfo aProcInfo, List <object> aParameters, int aTVPCount, out SqlParameter aResultParam)
{
// Iterate the input parameters and convert to SqlParameters.
List <SqlParameter> result = new List <SqlParameter>();
for (int i = 0; i < aParameters.Count; i++)
{
// When i < aTypes.Length, the parameter will be a 'Structured' type.
// If not a table, then make special allowance for a null value being passed
// and set the parameter type to VarChar, since this can accept a DbNull value.
SqlParameter p = new SqlParameter(String.Format("@P{0}", i), (i < aTVPCount) ? SqlDbType.Structured : (aParameters[i] != null) ? _SqlDataTypes[aParameters[i].GetType()] : SqlDbType.VarChar)
{
Direction = ParameterDirection.Input
};
// For TableType parameters, the SQL TypeName is required.
if (i < aTVPCount)
{
p.TypeName = aProcInfo.Details[i].SqlTypeName;
}
// If value is present then set it, else set DbNull and the length (mini optimization).
if (aParameters[i] != null)
{
p.Value = aParameters[i];
}
else
{
p.Value = DBNull.Value;
p.Size = 1;
}
result.Add(p);
}
// Add a final parameter representing the ReturnValue.
aResultParam = new SqlParameter("@Result", SqlDbType.Int)
{
Value = result,
Direction = ParameterDirection.Output
};
result.Add(aResultParam);
// Return result as an array, as this is required by ExecuteSqlCommand().
return(result.ToArray());
}
private static TVPProcInfo _RegisterProcedure(string aProcName = null, string[] aTVPTypeNames = null, params Type[] aTypes)
{
// Auto-registration is possible, but only for single-type invocations.
Validation.AssertArgument(!String.IsNullOrWhiteSpace(aProcName) || (aTypes.Length == 1), "The 'aProcName' parameter cannot be null or empty when more than a single Type is being Registered!");
if (String.IsNullOrWhiteSpace(aProcName))
{
// For auto-registration, determine the sproc name based on the pluralization of the type
// and using the default schema.
aProcName = String.Format("dbo.Save{0}", aTypes[0].Name.Pluralize());
}
var KeyName = DetermineKeyName(aProcName, aTypes);
var EmptyKeyName = DetermineKeyName(null, aTypes);
// Ensure only a single registration per ordered TVP-type combination and sproc name.
if (_Registrations.TryGetValue(KeyName, out TVPProcInfo found))
{
throw new AlreadyRegisteredException(String.Format("A stored procedure named '{0}' has already been registered for the Type set [{1}].", found.ProcedureName, KeyName));
}
// If an EmptyKeyName registration already exists, reuse the TVPProcInfo. This
// will happen with multiple RegisterProcedure() calls for the same TVP-type set.
if (_Registrations.TryGetValue(EmptyKeyName, out TVPProcInfo TPI))
{
// Register it under the additional sproc name and return it.
_Registrations[KeyName] = TPI;
return(TPI);
}
// Create and populate a TVPProcInfo instance.
TPI = new TVPProcInfo()
{
ProcedureName = aProcName
};
// Iterate the Types and create a TVPProcInfo per instance.
for (int i = 0; i < aTypes.Length; i++)
{
// See if we have a user-defined name or should use the convention, including any custom schema or prefix.
var STN = ((aTVPTypeNames != null) && (aTVPTypeNames.Length > i) && (!String.IsNullOrWhiteSpace(aTVPTypeNames[i]))) ?
aTVPTypeNames[i] :
String.Format("{0}{1}{2}", (C_CustomTVPSchema ?? "dbo."), C_CustomTVPPrefix, aTypes[i].Name);
var TD = new TVPDetails()
{
SqlTypeName = STN, Properties = GetProperties(aTypes[i]).ToList()
};
// Add it to the sproc tracker.
TPI.Details.Add(TD);
}
// Register with the sproc name as part of the key.
_Registrations[KeyName] = TPI;
// Attempt to register a reference for the TVP-type set with an empty sproc
// name as the default for when executing without passing sproc name.
// It is okay if this does not succeed when registering a second sproc
// using the same types.
_Registrations.TryAdd(EmptyKeyName, TPI);
// Return the created ProcInfo instance.
return(TPI);
}
