Ejemplo n.º 1
0
        /// <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;
        }
Ejemplo n.º 2
0
        /// <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;
        }
Ejemplo n.º 3
0
        private static bool IsAutoIdFieldInColumnValueList(InsertStatement insert)
        {
            foreach (InsertExpression colValPair in insert.ColumnsAndValues)
            {
                if (colValPair.Column.AutoIncrement)
                    return true;
            }

            return false;
        }
Ejemplo n.º 4
0
 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(")");
 }
Ejemplo n.º 5
0
 private static void AppendInsertIntoTableName(InsertStatement insert, DbmsType dbms, StringBuilder output)
 {
     output.Append("INSERT INTO ");
     insert.Table.RenderTableName(dbms, output);
 }
Ejemplo n.º 6
0
 private static int GetTotalNumberOfFieldsToRender(InsertStatement insert)
 {
     bool autoIdFieldIsInColumnValueList = IsAutoIdFieldInColumnValueList(insert);
     int numberOfFieldsToRender = (autoIdFieldIsInColumnValueList) ? insert.ColumnsAndValues.Count - 1 : insert.ColumnsAndValues.Count;
     return numberOfFieldsToRender;
 }
Ejemplo n.º 7
0
        /// <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;
        }
Ejemplo n.º 8
0
        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(")");
        }
Ejemplo n.º 9
0
        /// <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;
        }
Ejemplo n.º 10
0
        /// <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;
        }