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