/// <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); }