/// <summary>
/// Renders INSERT statement.
/// </summary>
/// <param name="insert">INSERT statement that is being rendered.</param>
/// <param name="nextSequence">Ignored. SQLite 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><b>null</b> because automatically generated ID must be fetched via SELECT after INSERT.</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);
}
// Auto ID must be fetched via SELECT after INSERT.
DbParameter autoId = null;
return autoId;
}
/// <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>Ouput parameter that will contain the value retrieved by RETURNING clause.</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);
}
IDbColumn autoIdField = GetAutoIdField(insert.Table);
DbParameter autoId = null;
if (autoIdField != null)
{
// RETURNING id
output.Append(" RETURNING ");
autoIdField.RenderColumnName(dbms, output);
autoId = new DbParameter("?", DbType.Int32) { Direction = ParameterDirection.Output };
parameters.Add(autoId);
}
// Return auto-id DB parameter. Callers require it to retrieve the new ID value.
return autoId;
}
private static bool IsAutoIdFieldInColumnValueList(InsertStatement insert)
{
foreach (InsertExpression colValPair in insert.ColumnsAndValues)
{
if (colValPair.Column.AutoIncrement)
return true;
}
return false;
}
private static void AppendBracketsWithAllFieldsExceptAutoId(InsertStatement insert, DbmsType dbms, StringBuilder output, int numberOfFieldsToRender)
{
output.Append(" (");
int fieldsProcessed = 0;
foreach (InsertExpression colValPair in insert.ColumnsAndValues)
{
// Skip auto-id field.
if (!colValPair.Column.AutoIncrement)
{
colValPair.Column.RenderColumnName(dbms, output);
fieldsProcessed++;
if (fieldsProcessed < numberOfFieldsToRender)
output.Append(", ");
}
}
output.Append(")");
}
private static void AppendInsertIntoTableName(InsertStatement insert, DbmsType dbms, StringBuilder output)
{
output.Append("INSERT INTO ");
insert.Table.RenderTableName(dbms, output);
}
private static int GetTotalNumberOfFieldsToRender(InsertStatement insert)
{
bool autoIdFieldIsInColumnValueList = IsAutoIdFieldInColumnValueList(insert);
int numberOfFieldsToRender = (autoIdFieldIsInColumnValueList) ? insert.ColumnsAndValues.Count - 1 : insert.ColumnsAndValues.Count;
return numberOfFieldsToRender;
}
/// <summary>Executes the INSERT command. Automatically generates the code that retrieves the new identity for
/// the supported databases. DBMS specific code depends on the DBMS property of the used ConnectionProvider.</summary>
/// <param name="insert">INSERT statement to execute.</param>
/// <param name="dbms">Target DBMS.</param>
/// <param name="conn">Connection-transaction context to use.</param>
/// <param name="lastExecutedCommandInfo">Output parameter: statistic for executed command.</param>
/// <param name="cmdTimeout">Timeout for the execution.</param>
/// <returns>Automatically generated ID for inserted row, or <b>null</b> if ID is not automatically generated.</returns>
public object Execute(InsertStatement insert, DbmsType dbms, IConnectionProvider conn, out CommandExecutionStatistics lastExecutedCommandInfo, int cmdTimeout = 30)
{
// Renderer and DBMS will compute next ID, insert row and retrieve ID in one trip.
StringBuilder cmdtxt = new StringBuilder();
DbParameterCollection parameters = new DbParameterCollection();
RenderInsert(insert, null, dbms, cmdtxt, parameters);
string command = cmdtxt.ToString();
lastExecutedCommandInfo = new CommandExecutionStatistics(command);
object id = null;
if (HasAutoIdField(insert.Table))
id = InsertAndSelectAutoNumber(conn, command, parameters, cmdTimeout);
else
Insert(conn, command, parameters, cmdTimeout);
lastExecutedCommandInfo.StopTime();
return id;
}
private static void AppendValuesForAllFieldsExceptAutoId(InsertStatement insert, DbmsType dbms, StringBuilder output, DbParameterCollection parameters, int numberOfFieldsToRender)
{
output.Append(" VALUES (");
int valuesProcessed = 0;
foreach (InsertExpression colValPair in insert.ColumnsAndValues)
{
// Skip auto-id field.
if (!colValPair.Column.AutoIncrement)
{
IRenderSql currValueItem = colValPair.ValueExpression;
currValueItem.Render(dbms, output, parameters);
valuesProcessed++;
if (valuesProcessed < numberOfFieldsToRender)
output.Append(", ");
}
}
output.Append(")");
}
/// <summary>Executes the INSERT command. Automatically generates the code that retrieves the new identity for
/// the supported databases. DBMS specific code depends on the DBMS property of the used ConnectionProvider.</summary>
/// <param name="insert">INSERT statement to execute.</param>
/// <param name="dbms">Target DBMS.</param>
/// <param name="conn">Connection-transaction context to use.</param>
/// <param name="lastExecutedCommandInfo">Output parameter: statistic for executed command.</param>
/// <param name="cmdTimeout">Timeout for this procedure</param>
/// <returns>Automatically generated ID for inserted row, or <b>null</b> if ID is not automatically generated.</returns>
public object Execute(InsertStatement insert, DbmsType dbms, IConnectionProvider conn, out CommandExecutionStatistics lastExecutedCommandInfo, int cmdTimeout = 30)
{
// Renderer and DBMS will compute next ID, insert row and retrieve ID in one trip.
StringBuilder cmdtxt = new StringBuilder();
DbParameterCollection parameters = new DbParameterCollection();
DbParameter newId = RenderInsert(insert, null, dbms, cmdtxt, parameters);
string command = cmdtxt.ToString();
lastExecutedCommandInfo = new CommandExecutionStatistics(command);
DbUtil.ExecuteNonQuery(conn, command, parameters, CommandType.Text, cmdTimeout);
lastExecutedCommandInfo.StopTime();
object id = null;
if (newId != null)
id = Convert.ToInt32(newId.Value, CultureInfo.InvariantCulture);
return id;
}
/// <summary>Executes the INSERT command. Automatically generates the code that retrieves the new identity for
/// the supported databases. DBMS specific code depends on the DBMS property of the used ConnectionProvider.</summary>
/// <param name="insert">INSERT statement to execute.</param>
/// <param name="dbms">Target DBMS.</param>
/// <param name="conn">Connection-transaction context to use.</param>
/// <param name="lastExecutedCommandInfo">Output parameter: statistic for executed command.</param>
/// <param name="cmdTimeout">Timeout for the execution.</param>
/// <returns>Automatically generated ID for inserted row, or <b>null</b> if ID is not automatically generated.</returns>
public object Execute(InsertStatement insert, DbmsType dbms, IConnectionProvider conn, out CommandExecutionStatistics lastExecutedCommandInfo, int cmdTimeout = 30)
{
// Renderer and DBMS will compute next ID, insert row and retrieve ID in one trip.
StringBuilder cmdtxt = new StringBuilder();
DbParameterCollection parameters = new DbParameterCollection();
RenderInsert(insert, null, dbms, cmdtxt, parameters);
string command = cmdtxt.ToString();
object id;
lastExecutedCommandInfo = new CommandExecutionStatistics(command);
if (HasAutoIdField(insert.Table))
{
DataTable autoIncrementData = DbUtil.ExecuteQuery(conn, command, parameters, CommandType.Text, null, cmdTimeout);
id = autoIncrementData.Rows[0][0];
if (id == DBNull.Value)
id = null;
}
else
{
DbUtil.ExecuteNonQuery(conn, command, parameters, CommandType.Text, cmdTimeout);
id = null;
}
lastExecutedCommandInfo.StopTime();
return id;
}