/// <summary> /// Executes the query built. /// </summary> public void Execute() { if (mode == Mode.Execute) { throw new InvalidOperationException("Can not use all functions in Execute mode."); } if (mode == Mode.Update) { DtronixTransaction transaction = null; try { // Start a transaction if one does not already exist. if (context.TransactionStarted == false) { transaction = context.BeginTransaction(); } for (int i = 0; i < sql_models.Length; i++) { sql_where = null; Where(sql_models[i]); BuildSql(sql_models[i]); // Execute the update command. command.ExecuteNonQuery(); if (context.Debug.HasFlag(Context.DebugLevel.Updates)) { Console.Out.WriteLine("Update: \r\n" + command.CommandText); } } if (transaction != null) { transaction.Commit(); } } finally { if (transaction != null) { transaction.Dispose(); } } } else { BuildSql(null); command.ExecuteNonQuery(); } if (_auto_close_command) { command.Dispose(); } }
/// <summary> /// Inserts multiple rows into the database. /// </summary> /// <remarks> /// This method by default wraps all inserts into a transaction. /// If one of the inserts fails, then all of the inserts are rolled back. /// </remarks> /// <param name="models">Rows to insert.</param> public long[] Insert(T[] models) { if (mode == Mode.Execute) { throw new InvalidOperationException("Can not use all functions in Execute mode."); } if (models == null || models.Length == 0) { throw new ArgumentException("Model array is empty."); } if (mode != Mode.Insert) { throw new InvalidOperationException("Can not insert when statement is not in INSERT mode."); } var columns = models[0].GetColumns(); StringBuilder sb_sql = new StringBuilder(); sb_sql.Append("INSERT INTO ").Append(table_name).Append(" ("); // Add all the column names. foreach (var column in columns) { sb_sql.Append("").Append(column).Append(", "); } // Remove the last ", " from the query. sb_sql.Remove(sb_sql.Length - 2, 2); // Add the values. sb_sql.Append(") VALUES ("); for (int i = 0; i < columns.Length; i++) { sb_sql.Append("@v").Append(i).Append(", "); } // Remove the last ", " from the query. sb_sql.Remove(sb_sql.Length - 2, 2); sb_sql.Append(");"); long[] new_row_ids = null; if (context.LastInsertIdQuery != null) { sb_sql.Append(context.LastInsertIdQuery); new_row_ids = new long[models.Length]; } DtronixTransaction transaction = null; try { // Start a transaction if one does not already exist for fast bulk inserts. if (context.TransactionStarted == false) { transaction = context.BeginTransaction(); } command.CommandText = sb_sql.ToString(); // Create the parameters for bulk inserts. for (int i = 0; i < columns.Length; i++) { var parameter = command.CreateParameter(); parameter.ParameterName = "@v" + i; command.Parameters.Add(parameter); } // Loop through watch of the provided models. for (int i = 0; i < models.Length; i++) { var values = models[i].GetAllValues(); for (int x = 0; x < values.Length; x++) { command.Parameters[x].Value = PrepareParameterValue(values[x]); } if (context.LastInsertIdQuery != null) { object new_row = command.ExecuteScalar(); if (new_row == null) { throw new Exception("Unable to insert row"); } else { new_row_ids[i] = Convert.ToInt64(new_row); } } else { if (command.ExecuteNonQuery() != 1) { throw new Exception("Unable to insert row"); } } if (context.Debug.HasFlag(Context.DebugLevel.Inserts)) { Console.Out.WriteLine("Insert: \r\n" + command.CommandText); } } // Commit all inserts. if (transaction != null) { transaction.Commit(); } } catch (Exception e) { // If we encountered an error, rollback the transaction. if (transaction != null) { transaction.Rollback(); } throw; } finally { if (transaction != null) { transaction.Dispose(); } } if (context.Debug.HasFlag(Context.DebugLevel.Inserts)) { Console.Out.WriteLine("Insert new Row IDs: \r\n" + string.Join(", ", new_row_ids)); } return(new_row_ids); }