public override int ExecuteSql(string sql, QueryParameterCollection parameters = null) { lock (this) // although sqlite3 is thread-safe, we need to make sure that the last_insert_rowid is correct { var stmt = CreateCommand(sql, parameters); try { SqliteReturnCode returnCode = _sqlite3.step(stmt); if (returnCode == SqliteReturnCode.Row) { return(0); // quietly eat any rows being returned } if (returnCode != SqliteReturnCode.Done) { throw new SqliteException(returnCode, _sqlite3.extended_errcode(DbHandle), _sqlite3.errmsg(DbHandle)); } _lastRowId.Value = _sqlite3.last_insert_rowid(DbHandle); return(_sqlite3.changes(DbHandle)); } finally { _sqlite3.finalize(stmt); } } }
public override int ExecuteSql(string sql, QueryParameterCollection parameters = null) { lock (this) // although sqlite3 is thread-safe, we need to make sure that the last_insert_rowid is correct { var stopwatch = SqlLogger != null?Stopwatch.StartNew() : null; var stmt = CreateCommand(sql, parameters); try { int returnCode = SQLitePCL.raw.sqlite3_step(stmt); if (returnCode == SQLitePCL.raw.SQLITE_ROW) { return(0); // quietly eat any rows being returned } if (returnCode != SQLitePCL.raw.SQLITE_DONE) { throw new SqliteException((SqliteReturnCode)returnCode, (SqliteExtendedErrorCode)SQLitePCL.raw.sqlite3_extended_errcode(DbHandle), SQLitePCL.raw.sqlite3_errmsg(DbHandle).utf8_to_string()); } _lastRowId.Value = SQLitePCL.raw.sqlite3_last_insert_rowid(DbHandle); return(SQLitePCL.raw.sqlite3_changes(DbHandle)); } finally { SQLitePCL.raw.sqlite3_finalize(stmt); SqlLogger?.LogSql(sql, parameters?.ToDictionary(p => SqlDialect.CreateParameterExpression(p.Name), p => p.Value), stopwatch?.Elapsed ?? TimeSpan.Zero); } } }
public IEnumerable <T> SqlQueryScalars <T>(string sql, object parameters = null) where T : new() { if (DataProvider is ISqlDataProvider sqlProvider) { return(sqlProvider.SqlQueryScalar(sql, QueryParameterCollection.FromObject(parameters)).Select(scalar => scalar.Convert <T>())); } throw new NotSupportedException(); }
public T SqlQueryScalar <T>(string sql, object parameters = null) where T : new() { if (DataProvider is ISqlDataProvider sqlProvider) { return(sqlProvider.SqlQueryScalar(sql, QueryParameterCollection.FromObject(parameters)).FirstOrDefault().Convert <T>()); } throw new NotSupportedException(); }
public IEnumerable <Dictionary <string, object> > SqlQuery(string sql, object parameters = null) { if (DataProvider is ISqlDataProvider sqlProvider) { return(sqlProvider.SqlQuery(sql, QueryParameterCollection.FromObject(parameters)).Select(entity => entity.AsDictionary())); } throw new NotSupportedException(); }
public IEnumerable <T> SqlQuery <T>(string sql, object parameters = null) where T : new() { if (DataProvider is ISqlDataProvider sqlProvider) { return(sqlProvider.SqlQuery(sql, QueryParameterCollection.FromObject(parameters)).Select(entity => entity.CreateObject <T>())); } throw new NotSupportedException(); }
// Native SQL methods public int SqlNonQuery(string sql, object parameters = null) { if (DataProvider is ISqlDataProvider sqlProvider) { return(sqlProvider.SqlNonQuery(sql, QueryParameterCollection.FromObject(parameters))); } throw new NotSupportedException(); }
public override void Purge(TableSchema schema) { var tableName = SqlDialect.QuoteTable(schema.MappedName); ExecuteSql("DELETE FROM " + tableName, null); if (SqlQueryScalar("select name from sqlite_master where name='sqlite_sequence'", null).Any()) { ExecuteSql("delete from sqlite_sequence where name=@name", QueryParameterCollection.FromObject(new { name = schema.MappedName })); } }
public IEnumerable <object> QueryScalar(string sql, QueryParameterCollection parameters) { throw new NotSupportedException(); }
public IEnumerable <SerializedEntity> Query(string sql, QueryParameterCollection parameters) { throw new NotSupportedException(); }
public int ExecuteSql(string sql, QueryParameterCollection parameters) { throw new NotSupportedException(); }
public override IEnumerable <Dictionary <string, object> > ExecuteSqlReader(string sql, QueryParameterCollection parameters) { var stmt = CreateCommand(sql, parameters); try { for (;;) { var returnCode = _sqlite3.step(stmt); if (returnCode == SqliteReturnCode.Busy) { Task.Delay(100).Wait(); continue; } if (returnCode == SqliteReturnCode.Done) { break; } if (returnCode != SqliteReturnCode.Row) { throw new SqliteException(returnCode, _sqlite3.extended_errcode(DbHandle), _sqlite3.errmsg(DbHandle)); } Dictionary <string, object> record = new Dictionary <string, object>(); for (int i = 0; i < _sqlite3.column_count(stmt); i++) { string fieldName = _sqlite3.column_name(stmt, i); SqliteColumnType columnType = _sqlite3.column_type(stmt, i); switch (columnType) { case SqliteColumnType.Blob: record[fieldName] = _sqlite3.column_blob(stmt, i); break; case SqliteColumnType.Text: record[fieldName] = _sqlite3.column_text(stmt, i); break; case SqliteColumnType.Float: record[fieldName] = _sqlite3.column_double(stmt, i); break; case SqliteColumnType.Integer: record[fieldName] = _sqlite3.column_int64(stmt, i); break; case SqliteColumnType.Null: record[fieldName] = null; break; } } yield return(record); } } finally { _sqlite3.finalize(stmt); } }
private IntPtr CreateCommand(string sql, QueryParameterCollection parameters) { IntPtr stmt; SqliteReturnCode returnCode = _sqlite3.prepare_v2(DbHandle, sql, out stmt); if (returnCode != SqliteReturnCode.Ok) { throw new SqliteException(returnCode, _sqlite3.extended_errcode(DbHandle), _sqlite3.errmsg(DbHandle)); } if (parameters != null) { foreach (var parameter in parameters) { int paramNumber = _sqlite3.bind_parameter_index(stmt, SqlDialect.CreateParameterExpression(parameter.Name)); var value = parameter.Value; if (value == null) { _sqlite3.bind_null(stmt, paramNumber); } else { var parameterType = parameter.Type.Inspector(); if (parameterType.Is(TypeFlags.Boolean)) { _sqlite3.bind_int(stmt, paramNumber, value.Convert <bool>() ? 1 : 0); } else if (parameterType.Is(TypeFlags.Array | TypeFlags.Byte)) { _sqlite3.bind_blob(stmt, paramNumber, (byte[])value); } else if (parameterType.Is(TypeFlags.Integer64)) { _sqlite3.bind_int64(stmt, paramNumber, value.Convert <long>()); } else if (parameterType.Is(TypeFlags.Integer)) { _sqlite3.bind_int(stmt, paramNumber, value.Convert <int>()); } else if (parameterType.Is(TypeFlags.FloatingPoint)) { _sqlite3.bind_double(stmt, paramNumber, value.Convert <double>()); } else if (parameterType.Is(TypeFlags.String)) { _sqlite3.bind_text(stmt, paramNumber, value.Convert <string>()); } else if (parameterType.Is(TypeFlags.DateTime)) { switch (DateFormat) { case SqliteDateFormat.String: _sqlite3.bind_text(stmt, paramNumber, ((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss.fff")); break; // case SqliteDateFormat.Julian: // _sqlite3.bind_int64(stmt, paramNumber, ((DateTime)value).Ticks); // break; case SqliteDateFormat.Unix: _sqlite3.bind_int(stmt, paramNumber, (int)(((DateTime)value) - new DateTime(1970, 1, 1)).TotalSeconds); break; case SqliteDateFormat.Ticks: _sqlite3.bind_int64(stmt, paramNumber, ((DateTime)value).Ticks); break; default: throw new ArgumentOutOfRangeException(); } } else { _sqlite3.bind_text(stmt, paramNumber, value.Convert <string>()); } } } } return(stmt); }
public override IEnumerable <Dictionary <string, object> > ExecuteSqlReader(string sql, QueryParameterCollection parameters) { var stopwatch = SqlLogger != null?Stopwatch.StartNew() : null; var stmt = CreateCommand(sql, parameters); try { for (;;) { var returnCode = SQLitePCL.raw.sqlite3_step(stmt); if (returnCode == SQLitePCL.raw.SQLITE_BUSY) { Task.Delay(100).Wait(); continue; } if (returnCode == SQLitePCL.raw.SQLITE_DONE) { break; } if (returnCode != SQLitePCL.raw.SQLITE_ROW) { throw new SqliteException((SqliteReturnCode)returnCode, (SqliteExtendedErrorCode)SQLitePCL.raw.sqlite3_extended_errcode(DbHandle), SQLitePCL.raw.sqlite3_errmsg(DbHandle).utf8_to_string()); } Dictionary <string, object> record = new Dictionary <string, object>(); for (int i = 0; i < SQLitePCL.raw.sqlite3_column_count(stmt); i++) { string fieldName = SQLitePCL.raw.sqlite3_column_name(stmt, i).utf8_to_string(); var columnType = SQLitePCL.raw.sqlite3_column_type(stmt, i); switch (columnType) { case SQLitePCL.raw.SQLITE_BLOB: record[fieldName] = SQLitePCL.raw.sqlite3_column_blob(stmt, i).ToArray(); break; case SQLitePCL.raw.SQLITE_TEXT: record[fieldName] = SQLitePCL.raw.sqlite3_column_text(stmt, i).utf8_to_string(); break; case SQLitePCL.raw.SQLITE_FLOAT: record[fieldName] = SQLitePCL.raw.sqlite3_column_double(stmt, i); break; case SQLitePCL.raw.SQLITE_INTEGER: record[fieldName] = SQLitePCL.raw.sqlite3_column_int64(stmt, i); break; case SQLitePCL.raw.SQLITE_NULL: record[fieldName] = null; break; } } yield return(record); } } finally { SQLitePCL.raw.sqlite3_finalize(stmt); } SqlLogger?.LogSql(sql, parameters?.ToDictionary(p => SqlDialect.CreateParameterExpression(p.Name), p => p.Value), stopwatch?.Elapsed ?? TimeSpan.Zero); }
private SQLitePCL.sqlite3_stmt CreateCommand(string sql, QueryParameterCollection parameters) { var returnCode = SQLitePCL.raw.sqlite3_prepare_v2(DbHandle, sql, out SQLitePCL.sqlite3_stmt stmt); if (returnCode != SQLitePCL.raw.SQLITE_OK) { throw new SqliteException((SqliteReturnCode)returnCode, (SqliteExtendedErrorCode)SQLitePCL.raw.sqlite3_extended_errcode(DbHandle), SQLitePCL.raw.sqlite3_errmsg(DbHandle).utf8_to_string()); } if (parameters != null) { foreach (var parameter in parameters) { int paramNumber = SQLitePCL.raw.sqlite3_bind_parameter_index(stmt, SqlDialect.CreateParameterExpression(parameter.Name)); var value = parameter.Value; if (value == null) { SQLitePCL.raw.sqlite3_bind_null(stmt, paramNumber); } else { var parameterType = parameter.Type.Inspector(); if (parameterType.Is(TypeFlags.Boolean)) { SQLitePCL.raw.sqlite3_bind_int(stmt, paramNumber, value.Convert <bool>() ? 1 : 0); } else if (parameterType.Is(TypeFlags.Array | TypeFlags.Byte)) { SQLitePCL.raw.sqlite3_bind_blob(stmt, paramNumber, (byte[])value); } else if (parameterType.Is(TypeFlags.Integer64)) { SQLitePCL.raw.sqlite3_bind_int64(stmt, paramNumber, value.Convert <long>()); } else if (parameterType.Is(TypeFlags.Integer)) { SQLitePCL.raw.sqlite3_bind_int(stmt, paramNumber, value.Convert <int>()); } else if (parameterType.Is(TypeFlags.FloatingPoint)) { SQLitePCL.raw.sqlite3_bind_double(stmt, paramNumber, value.Convert <double>()); } else if (parameterType.Is(TypeFlags.String)) { SQLitePCL.raw.sqlite3_bind_text(stmt, paramNumber, value.Convert <string>()); } else if (parameterType.Is(TypeFlags.Guid)) { SQLitePCL.raw.sqlite3_bind_text(stmt, paramNumber, ((Guid)value).ToString("N")); } else if (parameterType.Is(TypeFlags.DateTime)) { switch (DateFormat) { case SqliteDateFormat.String: SQLitePCL.raw.sqlite3_bind_text(stmt, paramNumber, ((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss.fff")); break; case SqliteDateFormat.Unix: SQLitePCL.raw.sqlite3_bind_int(stmt, paramNumber, (int)(((DateTime)value) - new DateTime(1970, 1, 1)).TotalSeconds); break; case SqliteDateFormat.Ticks: SQLitePCL.raw.sqlite3_bind_int64(stmt, paramNumber, ((DateTime)value).Ticks); break; default: throw new ArgumentOutOfRangeException(); } } else { SQLitePCL.raw.sqlite3_bind_text(stmt, paramNumber, value.Convert <string>()); } } } } return(stmt); }
public override int ExecuteProcedure(string procName, QueryParameterCollection parameters = null) { throw new NotSupportedException("Stored procedures are not supported in Sqlite"); }