/// <summary>
/// Derive the parameters for a command.
/// </summary>
/// <param name="command">The command to derive.</param>
/// <returns>The list of parameter names.</returns>
private static List<SqlParameter> DeriveParameters(IDbCommand command)
{
string sql = command.CommandText;
// for SqlServer stored procs, we can call the server to derive them
if (command.CommandType == CommandType.StoredProcedure)
{
// if we have a SqlCommand, use it
SqlCommand sqlCommand = command.UnwrapSqlCommand();
if (sqlCommand != null)
return DeriveParametersFromSqlProcedure(sqlCommand);
}
// otherwise we have to look at the text
// the text can even contain the parameters in a comment (e.g. "myproc -- @p, @q")
return DeriveParametersFromSqlText(sql);
}
/// <summary>
/// Add a list of objects as a table-valued parameter.
/// </summary>
/// <param name="command">The command to add parameters to.</param>
/// <param name="parameterName">The name of the parameter.</param>
/// <param name="tableTypeName">The name of the table type or null to assume TypeTable.</param>
/// <param name="listType">The type that the list contains.</param>
/// <param name="list">The list of objects.</param>
private static void AddEnumerableClassParameters(IDbCommand command, string parameterName, string tableTypeName, Type listType, IEnumerable list)
{
// table-valued parameters only work with sql
SqlCommand cmd = command.UnwrapSqlCommand();
// convert any nullable types to their underlying type
listType = Nullable.GetUnderlyingType(listType) ?? listType;
// if the table type name is null, then default to the name of the class
if (String.IsNullOrWhiteSpace(tableTypeName))
tableTypeName = String.Format(CultureInfo.InstalledUICulture, "[{0}Table]", listType.Name);
// let's see if we can get the schema table from the server
DataTable schema = _tvpSchemas.GetOrAdd(
listType,
type =>
{
SqlCommand schemaCommand = new SqlCommand(String.Format(CultureInfo.InvariantCulture, "DECLARE @schema {0} SELECT TOP 0 * FROM @schema", tableTypeName));
schemaCommand.Connection = cmd.Connection;
schemaCommand.Transaction = cmd.Transaction;
using (var reader = schemaCommand.ExecuteReader())
return reader.GetSchemaTable();
});
// create the structured parameter
SqlParameter p = new SqlParameter();
p.ParameterName = parameterName;
p.SqlDbType = SqlDbType.Structured;
p.TypeName = tableTypeName;
p.Value = new ObjectListDbDataReader(schema, listType, list);
cmd.Parameters.Add(p);
}
/// <summary>
/// Add a list of objects as a table-valued parameter.
/// </summary>
/// <param name="command">The command to add parameters to.</param>
/// <param name="parameterName">The name of the parameter.</param>
/// <param name="tableTypeName">The name of the table type or null to assume TypeTable.</param>
/// <param name="listType">The type that the list contains.</param>
/// <param name="list">The list of objects.</param>
private static void AddEnumerableClassParameters(IDbCommand command, string parameterName, string tableTypeName, Type listType, IEnumerable list)
{
// table-valued parameters only work with sql
SqlCommand cmd = command.UnwrapSqlCommand();
// convert any nullable types to their underlying type
listType = Nullable.GetUnderlyingType(listType) ?? listType;
// if the table type name is null, then default to the name of the class
if (String.IsNullOrWhiteSpace(tableTypeName))
tableTypeName = String.Format(CultureInfo.InstalledUICulture, "[{0}Table]", listType.Name);
// see if we already have a reader for the given type and table type name
// we can't use the schema cache because we don't have a schema yet
var key = Tuple.Create<string, Type>(tableTypeName, listType);
ObjectReader objectReader = _tvpReaders.GetOrAdd(
key,
k => cmd.Connection.ExecuteAndAutoClose(
_ => null,
(_, __) =>
{
// select a 0 row result set so we can determine the schema of the table
string sql = String.Format(CultureInfo.InvariantCulture, "DECLARE @schema {0} SELECT TOP 0 * FROM @schema", tableTypeName);
using (var sqlReader = cmd.Connection.GetReaderSql(sql, commandBehavior: CommandBehavior.SchemaOnly, transaction: cmd.Transaction))
return ObjectReader.GetObjectReader(sqlReader, listType);
},
CommandBehavior.Default));
// create the structured parameter
SqlParameter p = new SqlParameter();
p.ParameterName = parameterName;
p.SqlDbType = SqlDbType.Structured;
p.TypeName = tableTypeName;
p.Value = new ObjectListDbDataReader(objectReader, list);
cmd.Parameters.Add(p);
}
