int MaxObjectsPerBatch <TObject>(TObjectName tableName, TObject sampleObject, InsertOptions options)
where TObject : class
{
var metadata = DataSource.DatabaseMetadata;
var table = metadata.GetTableOrView(tableName);
var sqlBuilder = table.CreateSqlBuilder(false);
sqlBuilder.ApplyDesiredColumns(Materializer.NoColumns);
sqlBuilder.ApplyArgumentValue(DataSource, sampleObject, options);
sqlBuilder.GetInsertColumns(options.HasFlag(InsertOptions.IdentityInsert)).Count(); //Call .Count() to trigger needed side-effects
var parametersPerRow = DataSource.GetParameters(sqlBuilder).Count;
var maxParams = metadata.MaxParameters;
if (maxParams == null)
{
return(int.MaxValue);
}
var maxRows = maxParams.Value / parametersPerRow;
if (metadata.MaxRowsPerValuesClause.HasValue)
{
maxRows = Math.Min(metadata.MaxRowsPerValuesClause.Value, maxRows);
}
return(maxRows);
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer"></param>
/// <returns><see cref="PostgreSqlCommandExecutionToken" /></returns>
public override CommandExecutionToken <NpgsqlCommand, NpgsqlParameter> Prepare(Materializer <NpgsqlCommand, NpgsqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var identityInsert = m_Options.HasFlag(InsertOptions.IdentityInsert);
if (identityInsert)
{
throw new NotImplementedException("See issue 256. https://github.com/TortugaResearch/Tortuga.Chain/issues/256");
}
var sqlBuilder = Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, ArgumentValue, m_Options);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
if (KeyColumns.Count > 0)
{
sqlBuilder.OverrideKeys(KeyColumns);
}
var sql = new StringBuilder();
sqlBuilder.BuildInsertClause(sql, $"INSERT INTO {Table.Name.ToString()} (", null, ")", identityInsert);
sqlBuilder.BuildValuesClause(sql, " VALUES (", ")", identityInsert);
sqlBuilder.BuildSelectClause(sql, " RETURNING ", null, ";");
return(new PostgreSqlCommandExecutionToken(DataSource, "Insert into " + Table.Name, sql.ToString(), sqlBuilder.GetParameters()));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer"></param>
/// <returns><see cref="SQLiteCommandExecutionToken" /></returns>
public override CommandExecutionToken <SQLiteCommand, SQLiteParameter> Prepare(Materializer <SQLiteCommand, SQLiteParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var identityInsert = m_Options.HasFlag(InsertOptions.IdentityInsert);
var sqlBuilder = Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, ArgumentValue, m_Options);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
if (KeyColumns.Count > 0)
{
sqlBuilder.OverrideKeys(KeyColumns);
}
var sql = new StringBuilder();
sqlBuilder.BuildInsertStatement(sql, Table.Name.ToQuotedString(), ";", identityInsert);
sql.AppendLine();
sqlBuilder.BuildSelectClause(sql, "SELECT ", null, $" FROM {Table.Name.ToQuotedString()} WHERE ROWID=last_insert_rowid();");
return(new SQLiteCommandExecutionToken(DataSource, "Insert into " + Table.Name, sql.ToString(), sqlBuilder.GetParameters(), lockType: LockType.Write));
}
int MaxObjectsPerBatch <TObject>(AbstractObjectName tableName, TObject sampleObject, InsertOptions options)
where TObject : class
{
var table = DatabaseMetadata.GetTableOrView(tableName);
var sqlBuilder = table.CreateSqlBuilder(false);
sqlBuilder.ApplyDesiredColumns(Materializer.NoColumns);
sqlBuilder.ApplyArgumentValue(this, sampleObject, options);
sqlBuilder.GetInsertColumns(options.HasFlag(InsertOptions.IdentityInsert)).Count(); //Call .Count() to trigger needed side-effects
var parametersPerRow = sqlBuilder.GetParameters().Count;
var maxParams = DatabaseMetadata.MaxParameters;
if (maxParams == null)
{
return(int.MaxValue);
}
var maxRows = maxParams.Value / parametersPerRow;
#if SQL_SERVER_SDS || SQL_SERVER_MDS
//Max rows per VALUES clause is 1000.
maxRows = Math.Min(1000, maxRows);
#endif
return(maxRows);
}
public override CommandExecutionToken <SqlCommand, SqlParameter> Prepare(Materializer <SqlCommand, SqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
//This sets up the sqlBuilder. We're not actually going to build the parameters now
sqlBuilder.ApplyArgumentValue(DataSource, m_SourceList[0], m_Options);
var sql = new StringBuilder();
bool identityInsert = m_Options.HasFlag(InsertOptions.IdentityInsert);
if (identityInsert)
{
sql.AppendLine($"SET IDENTITY_INSERT {m_Table.Name.ToQuotedString()} ON;");
}
sqlBuilder.BuildInsertClause(sql, $"INSERT INTO {m_Table.Name.ToQuotedString()} (", null, ")", identityInsert);
sqlBuilder.BuildSelectClause(sql, " OUTPUT ", "Inserted.", null);
sql.AppendLine("VALUES");
var parameters = new List <SqlParameter>();
for (var i = 0; i < m_SourceList.Count; i++)
{
var parameterSuffix = "_" + i;
var footer = (i == m_SourceList.Count - 1) ? ");" : "),";
sqlBuilder.OverrideArgumentValue(DataSource, AuditRules.OperationTypes.Insert, m_SourceList[i]);
sqlBuilder.BuildValuesClause(sql, "(", footer, identityInsert, parameterSuffix, parameters, Utilities.ParameterBuilderCallback);
}
var maxParams = DataSource.DatabaseMetadata.MaxParameters !.Value;
if (parameters.Count > maxParams)
{
var parametersPerRow = parameters.Count / m_SourceList.Count;
var maxRows = maxParams / parametersPerRow;
throw new InvalidOperationException($"Batch insert exceeds SQL Server's parameter limit of {DataSource.DatabaseMetadata.MaxParameters}. Supply a table type, break the call into batches of {maxRows}, use InsertMultipleBatch, or use BulkInsert");
}
if (identityInsert)
{
sql.AppendLine($"SET IDENTITY_INSERT {m_Table.Name.ToQuotedString()} OFF;");
}
return(new SqlServerCommandExecutionToken(DataSource, "Insert batch into " + m_Table.Name, sql.ToString(), parameters));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer"></param>
/// <returns><see cref="MySqlCommandExecutionToken" /></returns>
public override CommandExecutionToken <MySqlCommand, MySqlParameter> Prepare(Materializer <MySqlCommand, MySqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var identityInsert = m_Options.HasFlag(InsertOptions.IdentityInsert);
var sqlBuilder = Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, ArgumentValue, m_Options);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
if (KeyColumns.Count > 0)
{
sqlBuilder.OverrideKeys(KeyColumns);
}
var sql = new StringBuilder();
sqlBuilder.BuildInsertClause(sql, $"INSERT INTO {Table.Name.ToString()} (", null, ")", identityInsert);
sqlBuilder.BuildValuesClause(sql, " VALUES (", ");", identityInsert);
if (sqlBuilder.HasReadFields)
{
var identityColumn = Table.Columns.Where(c => c.IsIdentity).SingleOrDefault();
if (!identityInsert && identityColumn != null)
{
sqlBuilder.BuildSelectClause(sql, "SELECT ", null, null);
sql.Append(" FROM " + Table.Name.ToQuotedString());
sql.Append(" WHERE " + identityColumn.QuotedSqlName + " = LAST_INSERT_ID()");
sql.Append(";");
}
else
{
var primaryKeys = Table.PrimaryKeyColumns;
if (primaryKeys.Count == 0)
{
throw new MappingException($"Insert operation cannot return any values for { Table.Name} because it doesn't have a primary key.");
}
sqlBuilder.BuildSelectClause(sql, "SELECT ", null, null);
sql.Append(" FROM " + Table.Name.ToQuotedString());
sqlBuilder.BuildWhereClause(sql, " WHERE ", null);
sql.Append(";");
}
}
return(new MySqlCommandExecutionToken(DataSource, "Insert into " + Table.Name, sql.ToString(), sqlBuilder.GetParameters()));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer"></param>
/// <returns><see cref="PostgreSqlCommandExecutionToken" /></returns>
public override CommandExecutionToken <NpgsqlCommand, NpgsqlParameter> Prepare(Materializer <NpgsqlCommand, NpgsqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var identityInsert = m_Options.HasFlag(InsertOptions.IdentityInsert);
if (identityInsert)
{
throw new NotImplementedException("See issue 256. https://github.com/docevaad/Chain/issues/256");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
//This sets up the sqlBuilder. We're not actually going to build the parameters now
sqlBuilder.ApplyArgumentValue(DataSource, m_SourceList[0], m_Options);
var sql = new StringBuilder();
sqlBuilder.BuildInsertClause(sql, $"INSERT INTO {m_Table.Name.ToQuotedString()} (", null, ")", identityInsert);
sql.AppendLine("VALUES");
var parameters = new List <NpgsqlParameter>();
for (var i = 0; i < m_SourceList.Count; i++)
{
var parameterSuffix = "_" + i;
var footer = (i == m_SourceList.Count - 1) ? ")" : "),";
sqlBuilder.OverrideArgumentValue(DataSource, AuditRules.OperationTypes.Insert, m_SourceList[i]);
sqlBuilder.BuildValuesClause(sql, "(", footer, identityInsert, parameterSuffix, parameters, Utilities.ParameterBuilderCallback);
}
sqlBuilder.BuildSelectClause(sql, " RETURNING ", null, ";");
var maxParams = DataSource.DatabaseMetadata.MaxParameters !.Value;
if (parameters.Count > maxParams)
{
var parametersPerRow = parameters.Count / m_SourceList.Count;
var maxRows = maxParams / parametersPerRow;
throw new InvalidOperationException($"Batch insert exceeds PostgreSql's parameter limit of {DataSource.DatabaseMetadata.MaxParameters}. Break the call into batches of {maxRows} or use InsertMultipleBatch");
}
return(new PostgreSqlCommandExecutionToken(DataSource, "Insert batch into " + m_Table.Name, sql.ToString(), parameters));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer">The materializer.</param>
/// <returns>ExecutionToken<TCommand>.</returns>
public override CommandExecutionToken <SqlCommand, SqlParameter> Prepare(Materializer <SqlCommand, SqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, ArgumentValue, m_Options);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
if (KeyColumns.Count > 0)
{
sqlBuilder.OverrideKeys(KeyColumns);
}
var sql = new StringBuilder();
string?header;
string?intoClause;
string?footer;
sqlBuilder.UseTableVariable(Table, out header, out intoClause, out footer);
sql.Append(header);
bool identityInsert = m_Options.HasFlag(InsertOptions.IdentityInsert);
if (identityInsert)
{
sql.AppendLine($"SET IDENTITY_INSERT {Table.Name.ToQuotedString()} ON;");
}
sqlBuilder.BuildInsertClause(sql, $"INSERT INTO {Table.Name.ToQuotedString()} (", null, ")", identityInsert);
sqlBuilder.BuildSelectClause(sql, " OUTPUT ", "Inserted.", intoClause);
sqlBuilder.BuildValuesClause(sql, " VALUES (", ")", identityInsert);
sql.Append(";");
sql.Append(footer);
if (identityInsert)
{
sql.AppendLine($"SET IDENTITY_INSERT {Table.Name.ToQuotedString()} OFF;");
}
return(new SqlServerCommandExecutionToken(DataSource, "Insert into " + Table.Name, sql.ToString(), sqlBuilder.GetParameters()));
}
/// <summary>
/// Prepares the command for execution by generating any necessary SQL.
/// </summary>
/// <param name="materializer"></param>
/// <returns><see cref="AccessCommandExecutionToken" /></returns>
public override CommandExecutionToken <OleDbCommand, OleDbParameter> Prepare(Materializer <OleDbCommand, OleDbParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var desiredColumns = materializer.DesiredColumns();
var identityInsert = m_Options.HasFlag(InsertOptions.IdentityInsert);
var sqlBuilder = Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyArgumentValue(DataSource, ArgumentValue, m_Options);
sqlBuilder.ApplyDesiredColumns(desiredColumns);
if (KeyColumns.Count > 0)
{
sqlBuilder.OverrideKeys(KeyColumns);
}
var sql = new StringBuilder();
sqlBuilder.BuildInsertClause(sql, $"INSERT INTO {Table.Name.ToQuotedString()} (", null, ")", identityInsert);
sqlBuilder.BuildValuesClause(sql, " VALUES (", ")", identityInsert);
sql.Append(";");
var result = new AccessCommandExecutionToken(DataSource, "Insert into " + Table.Name, sql.ToString(), sqlBuilder.GetParameters());
if (desiredColumns == Materializer.AutoSelectDesiredColumns)
{
result.ExecutionMode = AccessCommandExecutionMode.NonQuery;
result.NextCommand = new AccessCommandExecutionToken(DataSource, "Fetch autonumber", "SELECT @@IDENTITY", new List <OleDbParameter>());
}
else if (desiredColumns.Count > 0)
{
result.ExecutionMode = AccessCommandExecutionMode.NonQuery;
result.NextCommand = new AccessCommandExecutionToken(DataSource, "Fetch autonumber", "SELECT @@IDENTITY", new List <OleDbParameter>());
result.NextCommand.ExecutionMode = AccessCommandExecutionMode.ExecuteScalarAndForward;
result.NextCommand.ForwardResult = value => { result.NextCommand.NextCommand = PrepareNext(desiredColumns, value); };
}
return(result);
}
public override CommandExecutionToken <SqlCommand, SqlParameter> Prepare(Materializer <SqlCommand, SqlParameter> materializer)
{
if (materializer == null)
{
throw new ArgumentNullException(nameof(materializer), $"{nameof(materializer)} is null.");
}
var sqlBuilder = m_Table.CreateSqlBuilder(StrictMode);
sqlBuilder.ApplyTableType(DataSource, OperationTypes.Insert, m_TableType.Columns);
sqlBuilder.ApplyDesiredColumns(materializer.DesiredColumns());
var sql = new StringBuilder();
bool identityInsert = m_Options.HasFlag(InsertOptions.IdentityInsert);
if (identityInsert)
{
sql.AppendLine($"SET IDENTITY_INSERT {m_Table.Name.ToQuotedString()} ON;");
}
sqlBuilder.BuildInsertClause(sql, $"INSERT INTO {m_Table.Name.ToQuotedString()} (", null, ")", identityInsert);
sqlBuilder.BuildSelectClause(sql, " OUTPUT ", "Inserted.", null);
sqlBuilder.BuildSelectTvpForInsertClause(sql, " SELECT ", null, " FROM @ValuesParameter ", identityInsert);
sql.Append(";");
if (identityInsert)
{
sql.AppendLine($"SET IDENTITY_INSERT {m_Table.Name.ToQuotedString()} OFF;");
}
var parameters = sqlBuilder.GetParameters();
parameters.Add(new SqlParameter()
{
ParameterName = "@ValuesParameter",
Value = m_Source,
SqlDbType = SqlDbType.Structured,
TypeName = m_TableType.Name.ToQuotedString()
});
return(new SqlServerCommandExecutionToken(DataSource, "Insert batch into " + m_Table.Name, sql.ToString(), parameters));
}