public static IEnumerable<Dictionary<string, object>> Query(this SqliteConnection connection, SqlBuilder sql, SqliteTransaction transaction = null, object sqlParams = null)
{
if (sql == null)
throw new ArgumentNullException("sql", "sql cannot be null.");
return Query(connection, sql.GetSql(), transaction, sqlParams);
}
private void updateVerseTable(IQueryContext context, SqliteTransaction transaction)
{
var sql = SqlBuilder.Update("Verses")
.Set(DbVerse.BookField + " = @Book",
DbVerse.BeginChapterField + " = @BeginChapter",
DbVerse.EndChapterField + " = @EndChapter",
DbVerse.BeginVerseField + " = @BeginVerse",
DbVerse.EndVerseField + " = @EndVerse",
DbVerse.BodyField + " = @Body",
DbVerse.TitleField + " = @Title",
DbVerse.DateField + " = @Date",
DbVerse.TranslationField + " = @Translation")
.Where(DbVerse.VerseIdField + " = @VerseId");
var sqlParams = new
{
Book = _verse.Reference.Book.Id,
BeginChapter = _verse.Reference.From.Chapter,
EndChapter = _verse.Reference.To.Chapter,
BeginVerse = _verse.Reference.From.Verse,
EndVerse = _verse.Reference.To.Verse,
Body = _verse.Body,
Title = _verse.Title,
Date = _verse.Date,
Translation = _verse.Translation.ToDatabaseEntity().Id,
VerseId = _verse.VerseId
};
context.Connection.Execute(sql, transaction, sqlParams);
}
private void updateVerseListAssocTable(IQueryContext context, SqliteTransaction transaction)
{
var sql = SqlBuilder.Update("VerseListAssoc")
.Set("ListId = @ListId")
.Where("VerseId = @VerseId");
var sqlParams = new
{
ListId = _list.Id,
VerseId = _verse.VerseId
};
context.Connection.Execute(sql, transaction, sqlParams);
}
private void associateWithList(IQueryContext context, SqliteTransaction transaction, long insertedVerseId)
{
var sql = SqlBuilder.InsertInto("VerseListAssoc")
.Columns("VerseId", "ListId")
.Values("@VerseId", "@ListId");
var sqlParam = new
{
VerseId = insertedVerseId,
ListId = _list.Id
};
context.Connection.Execute(sql, transaction, sqlParams: sqlParam);
}
protected override DbTransaction BeginDbTransaction(IsolationLevel il)
{
if (state != ConnectionState.Open)
{
throw new InvalidOperationException("Invalid operation: The connection is closed");
}
SqliteTransaction t = new SqliteTransaction();
t.SetConnection(this);
SqliteCommand cmd = (SqliteCommand)this.CreateCommand();
cmd.CommandText = "BEGIN";
cmd.ExecuteNonQuery();
return(t);
}
public SqliteTransaction BeginTransaction()
{
if (state != ConnectionState.Open)
{
throw new InvalidOperationException("Invalid operation: The connection is closed");
}
SqliteTransaction t = new SqliteTransaction();
t.Connection = this;
SqliteCommand cmd = (SqliteCommand)this.CreateCommand();
cmd.CommandText = "BEGIN";
cmd.ExecuteNonQuery();
return(t);
}
public static IEnumerable<Dictionary<string, object>> Query(this SqliteConnection connection, string sql, SqliteTransaction transaction = null, object sqlParams = null)
{
if (connection == null)
throw new ArgumentNullException("connection", "connection cannot be null.");
if (sql == null)
throw new ArgumentNullException("sql", "sql cannot be null.");
var wasOpenAtBegin = connection.State == ConnectionState.Open;
if (!wasOpenAtBegin)
connection.Open();
using (var command = connection.CreateCommand())
{
command.CommandText = sql;
if (sqlParams != null)
{
var properties = sqlParams.GetType().GetProperties();
foreach (var item in properties)
{
command.Parameters.Add(string.Format("@{0}", item.Name), item.GetValue(sqlParams, null));
}
}
if (transaction != null)
command.Transaction = transaction;
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
var fieldCount = reader.FieldCount;
var record = new Dictionary<string, object>(fieldCount);
for (int i = 0; i < fieldCount; i++)
record[reader.GetName(i)] = reader[i];
yield return record;
}
}
}
if (!wasOpenAtBegin)
connection.Close();
}
public IDbTransaction BeginTransaction()
#endif
{
if (state != ConnectionState.Open)
{
throw new InvalidOperationException("Invalid operation: The connection is closed");
}
SqliteTransaction t = new SqliteTransaction();
#if NET_2_0
t.SetConnection(this);
#else
t.Connection = this;
#endif
SqliteCommand cmd = (SqliteCommand)this.CreateCommand();
cmd.CommandText = "BEGIN";
cmd.ExecuteNonQuery();
return(t);
}
private long insertIntoVerseTable(IQueryContext context, SqliteTransaction transaction)
{
var sql = SqlBuilder.InsertInto("Verses")
.Columns(DbVerse.BookField,
DbVerse.BeginChapterField,
DbVerse.EndChapterField,
DbVerse.BeginVerseField,
DbVerse.EndVerseField,
DbVerse.BodyField,
DbVerse.TitleField,
DbVerse.DateField,
DbVerse.TranslationField)
.Values("@Book",
"@BeginChapter",
"@EndChapter",
"@BeginVerse",
"@EndVerse",
"@Body",
"@Title",
"@Date",
"@Translation");
var sqlParams = new
{
Book = _newVerse.Book.Id,
BeginChapter = _newVerse.From.Chapter,
EndChapter = _newVerse.To.Chapter,
BeginVerse = _newVerse.From.Verse,
EndVerse = _newVerse.To.Verse,
Body = _newVerse.Body,
Title = _newVerse.Title,
Date = _newVerse.Date,
Translation = _newVerse.Translation.Id
};
context.Connection.Execute(sql, transaction, sqlParams: sqlParams);
const string LastIdField = "LastId";
var verseIdQuery = SqlBuilder.Select("last_insert_rowid()").As(LastIdField);
return (long)context.Connection.Query(verseIdQuery, transaction).Single()[LastIdField];
}
public static int Execute(this SqliteConnection connection, string sql, SqliteTransaction transaction = null, object sqlParams = null)
{
if (connection == null)
throw new ArgumentNullException("connection", "connection cannot be null.");
if (sql == null)
throw new ArgumentNullException("sql", "sql cannot be null.");
var wasOpenAtBegin = connection.State == ConnectionState.Open;
if (!wasOpenAtBegin)
connection.Open();
int result;
using (var command = connection.CreateCommand())
{
command.CommandText = sql;
if (sqlParams != null)
{
var properties = sqlParams.GetType().GetProperties();
foreach (var item in properties)
{
command.Parameters.Add(string.Format("@{0}", item.Name), item.GetValue(sqlParams, null));
}
}
if (transaction != null)
command.Transaction = transaction;
result = command.ExecuteNonQuery();
}
if (!wasOpenAtBegin)
connection.Close();
return result;
}
protected override DbTransaction BeginDbTransaction (IsolationLevel il)
{
if (state != ConnectionState.Open)
throw new InvalidOperationException("Invalid operation: The connection is closed");
SqliteTransaction t = new SqliteTransaction();
t.SetConnection (this);
SqliteCommand cmd = (SqliteCommand)this.CreateCommand();
cmd.CommandText = "BEGIN";
cmd.ExecuteNonQuery();
return t;
}
public SqliteCommand(string sqlText, SqliteConnection dbConn, SqliteTransaction trans)
{
sql = sqlText;
parent_conn = dbConn;
transaction = trans;
}
public IDbTransaction BeginTransaction ()
#endif
{
if (state != ConnectionState.Open)
throw new InvalidOperationException("Invalid operation: The connection is closed");
SqliteTransaction t = new SqliteTransaction();
#if NET_2_0
t.SetConnection (this);
#else
t.Connection = this;
#endif
SqliteCommand cmd = (SqliteCommand)this.CreateCommand();
cmd.CommandText = "BEGIN";
cmd.ExecuteNonQuery();
return t;
}
public SqliteTransaction BeginTransaction()
{
if (state != ConnectionState.Open)
throw new InvalidOperationException("Invalid operation: The connection is closed");
SqliteTransaction t = new SqliteTransaction();
t.Connection = this;
SqliteCommand cmd = (SqliteCommand)this.CreateCommand();
cmd.CommandText = "BEGIN";
cmd.ExecuteNonQuery();
return t;
}
public bool BeginTransaction()
{
if (database != null && transaction == null && IsOpen)
{
transaction = database.BeginTransaction();
return transaction != null;
}
return false;
}
public SqliteCommand( string sqlText, SqliteConnection dbConn, SqliteTransaction trans )
{
sql = sqlText;
parent_conn = dbConn;
transaction = trans;
}
public CSSqliteTransaction(SqliteTransaction transaction)
{
Transaction = transaction;
}
public static int Execute(this SqliteConnection connection, SqlBuilder sql, SqliteTransaction transaction = null, object sqlParams = null)
{
return Execute(connection, sql.GetSql(), transaction, sqlParams);
}
