/// <summary>
/// Renders INSERT statement and code that retrieves the new ID.
/// </summary>
/// <param name="insert">INSERT statement that is being rendered.</param>
/// <param name="nextSequence">Ignored. SQL Server doesn't use sequences.</param>
/// <param name="dbms">Target DBMS. Different auto-id retrieval for SQL 7.0 then in newer versions.</param>
/// <param name="output">StringBuilder to which the SQL code is appended.</param>
/// <param name="parameters">SQL parameter collection to which the object's and its children's
/// parameters are added. After the rendering is done the collection contains all parameters with unique names.</param>
/// <returns>Parameter that contains the ID of the inserted row. <b>null</b> if auto-id field is not used.</returns>
public DbParameter RenderInsert(InsertStatement insert, DbParameter nextSequence, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// Renders INSERT statements for DBMSs that support an auto-identity fields.
// Auto-id field may or may not be in the column-value list.
// If auto-incremented field is in the column-value list it will be skipped.
// Table may have only one auto-identity field.
// Method expects that all errors have been identified and processed in the caller.
// Renders all fields except auto-id field; thus -1 if auto-id is contained in the column-value list.
int numberOfFieldsToRender = GetTotalNumberOfFieldsToRender(insert);
AppendInsertIntoTableName(insert, dbms, output);
if (numberOfFieldsToRender > 0)
{
AppendBracketsWithAllFieldsExceptAutoId(insert, dbms, output, numberOfFieldsToRender);
AppendValuesForAllFieldsExceptAutoId(insert, dbms, output, parameters, numberOfFieldsToRender);
}
else
{
AppendDefaultValuesExpression(output);
}
DbParameter autoId = null;
if (HasAutoIdField(insert.Table))
{
autoId = new DbParameter("___newAutoIdentity___", DbType.Int32) { Direction = ParameterDirection.Output };
if (dbms == DbmsType.SqlServer_7)
{
// @@IDENTITY
output.Append(" ; set ");
autoId.Render(dbms, output, parameters);
output.Append(" = @@IDENTITY");
}
else
{
// scope_identity()
output.Append(" ; set ");
autoId.Render(dbms, output, parameters);
output.Append(" = scope_identity()");
}
// BatchQuery (if ever implemented) reads this value to assign new ID value to InsertStatement.RetrievedData property.
//insert.NewIdOutputParameterIndexAfterRenderCompleted = parameters.Count - 1;
}
// Return auto-id DB parameter. Callers require it to retrieve the new ID value.
return autoId;
}
public DbParameter RenderInsert(InsertStatement insert, DbParameter nextSequence, DbmsType dbms, StringBuilder output, DbParameterCollection parameters)
{
// Auto ID: sequence if it is defined, or NULL and hope that a trigger will take care of auto ID field.
IDbColumn autoIdField = GetAutoIdField(insert.Table);
bool hasAutoIdField = (autoIdField != null);
bool autoIdIsAlreadyInInsertList = IsAutoIdFieldInColumnValueList(insert);
bool mustAppendAutoIdField = (hasAutoIdField && !autoIdIsAlreadyInInsertList);
AppendInsertIntoTableName(insert, dbms, output);
AppendColumnListInBrackets(insert, dbms, output, autoIdField, mustAppendAutoIdField);
RenderValueListAndNextSequenceExpression(insert, dbms, output, parameters, autoIdField, mustAppendAutoIdField);
if (autoIdField != null && nextSequence != null)
{
// RETURNING id
output.Append(" RETURNING ");
autoIdField.RenderColumnName(dbms, output);
output.Append(" INTO ");
nextSequence.Render(dbms, output, parameters);
}
return nextSequence;
}