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