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