/// <summary>
/// execute a query</summary>
/// <returns>
/// return a JArray which contains all objects </returns>
/// <param name="query"> contains the query to execute</param>
/// <param name="param"> contains the parameters needed to execute the query</param>
public JArray execute(String query, object[] param)
{
JArray array = new JArray();
if (query != null && query.Length > 0)
{
stmt = SQLite3.Prepare2(this.Handle, query);
if (stmt != null)
{
if (param != null && param.Length > 0)
{
for (int i = 1; i <= param.Length; i++)
{
bindValue(param.GetValue(i - 1), i);
}
}
while (SQLite3.Step(stmt) == SQLite3.Result.Row)
{
int count = SQLite3.ColumnCount(stmt);
JObject obj = new JObject();
for (int i = 0; i < count; i++)
{
string name = SQLite3.ColumnName(stmt, i);
SQLite3.ColType colType = SQLite3.ColumnType(stmt, i);
switch (colType)
{
case SQLite3.ColType.Blob:
byte[] bytes = SQLite3.ColumnByteArray(stmt, i);
obj.Add(name, bytes);
break;
case SQLite3.ColType.Integer:
int intValue = SQLite3.ColumnInt(stmt, i);
obj.Add(name, intValue);
break;
case SQLite3.ColType.Float:
double doubleValue = SQLite3.ColumnDouble(stmt, i);
obj.Add(name, doubleValue);
break;
case SQLite3.ColType.Text:
string text = SQLite3.ColumnString(stmt, i);
obj.Add(name, text);
break;
case SQLite3.ColType.Null:
default:
obj.Add(name, null);
break;
}
}
array.Add(obj);
}
}
SQLite3.Finalize(stmt);
}
return(array);
}
public static string[] GetColunasSQLite(this SQLitePCL.sqlite3_stmt stQuery)
{
var qtdColunas = SQLite3.ColumnCount(stQuery);
var colunas = new string[qtdColunas];
for (int i = 0; i < qtdColunas; i++)
{
colunas[i] = SQLite3.ColumnName(stQuery, i);
}
return(colunas);
}
private void GetColumns(SQLitePCL.sqlite3_stmt stmt, out string[] names, out Type[] types)
{
int columnCount = SQLite3.ColumnCount(stmt);
types = new Type[columnCount];
names = new string[columnCount];
for (int i = 0; i < columnCount; i++)
{
names[i] = CheckName(names, i, SQLite3.ColumnName(stmt, i));
types[i] = SQLiteColumnTypeToType(SQLite3.ColumnType(stmt, i));
}
}
private List <object[]> RunSql(string sqlString, bool includeColumnNamesAsFirstRow)
{
var lstRes = new List <object[]>();
SQLitePCL.sqlite3_stmt stQuery = null;
try
{
stQuery = SQLite3.Prepare2(App.Database.DatabaseNotAsync.Handle, sqlString);
var colLenght = SQLite3.ColumnCount(stQuery);
if (includeColumnNamesAsFirstRow)
{
var obj = new object[colLenght];
lstRes.Add(obj);
for (int i = 0; i < colLenght; i++)
{
obj[i] = SQLite3.ColumnName(stQuery, i);
}
}
while (SQLite3.Step(stQuery) == SQLite3.Result.Row)
{
var obj = new object[colLenght];
lstRes.Add(obj);
for (int i = 0; i < colLenght; i++)
{
var colType = SQLite3.ColumnType(stQuery, i);
switch (colType)
{
case SQLite3.ColType.Blob:
obj[i] = SQLite3.ColumnBlob(stQuery, i);
break;
case SQLite3.ColType.Float:
obj[i] = SQLite3.ColumnDouble(stQuery, i);
break;
case SQLite3.ColType.Integer:
obj[i] = SQLite3.ColumnInt(stQuery, i);
break;
case SQLite3.ColType.Null:
obj[i] = null;
break;
case SQLite3.ColType.Text:
obj[i] = SQLite3.ColumnString(stQuery, i);
break;
}
}
}
return(lstRes);
}
catch (Exception)
{
return(null);
}
finally
{
if (stQuery != null)
{
SQLite3.Finalize(stQuery);
}
}
}
public IEnumerator <IDictionary <String, Object> > ExecuteSelectQuery(DatabaseDescriptor databaseDescriptor, EntityDescriptor entityDescriptor, String query)
{
#if XAMARIN
SQLitePCL.sqlite3_stmt statement = SQLite3.Prepare2(sqliteDatabase.Handle, query);
#elif WINDOWS
List <SQLiteQueryRow> statement = null;
#endif
try
{
#if XAMARIN
statement = SQLite3.Prepare2(sqliteDatabase.Handle, query);
#elif WINDOWS
statement = sqliteDatabase.Query2(query);
#endif
}
catch (System.Exception exception)
{
if (sqliteDatabase.IsInTransaction)
{
sqliteDatabase.Rollback();
}
Log.Log.Error(typeof(Database).FullName, "ExecuteSelectQuery", "Exception caught while executing the select query, QUERY: " + query);
throw new DatabaseException(typeof(Database).FullName, "ExecuteSelectQuery", exception.Message);
}
List <Dictionary <String, Object> > tuples = new List <Dictionary <String, Object> >();
#if XAMARIN
SQLite3.Result result;
while ((result = SQLite3.Step(statement)) == SQLite3.Result.Row)
{
IDictionary <String, Object> tuple = new Dictionary <String, Object>();
String stmtResult = result.ToString();
//string[] names = SQLite3.ColType.GetNames ();
//string[] values = SQLite3.ColType.GetValues ();
int columnsCount = SQLite3.ColumnCount(statement);
for (int i = 0; i < columnsCount; i++)
{
String columnName = SQLite3.ColumnName(statement, i);
SQLite3.ColType columnType = SQLite3.ColumnType(statement, i);
Object columnValue = SQLite3.ColumnText(statement, i);
bool isString = false;
bool isLong = false;
bool isFloat = false;
bool isBlob = false;
if (columnType == SQLite3.ColType.Text)
{
isString = true;
}
else if (columnType == SQLite3.ColType.Integer)
{
isLong = true;
}
else if (columnType == SQLite3.ColType.Float)
{
isFloat = true;
}
else if (columnType == SQLite3.ColType.Blob)
{
isBlob = true;
}
else if (columnType == SQLite3.ColType.Null)
{
}
if (isString)
{
if (entityDescriptor != null)
{
EntityDescriptor.Attribute attribute = entityDescriptor.GetAttributeBasedOnColumnName(columnName);
if (attribute != null)
{
if (attribute.GetType().Equals(IDataTypeHandler.CSHARP_STRING_DATA_TYPE, StringComparison.OrdinalIgnoreCase))
{
tuple.Add(columnName, (String)columnValue);
}
else if (attribute.GetType().Equals(IDataTypeHandler.CSHARP_BOOLEAN_PRIMITIVE_DATA_TYPE, StringComparison.OrdinalIgnoreCase))
{
tuple.Add(columnName, columnValue.Equals(Boolean.TrueString.ToString()) ? true : false);
}
else if (attribute.GetType().Equals(IDataTypeHandler.CSHARP_BOOLEAN_DATA_TYPE, StringComparison.OrdinalIgnoreCase))
{
tuple.Add(columnName, columnValue.Equals(Boolean.TrueString.ToString()) ? Boolean.TrueString : Boolean.FalseString);
}
else if (attribute.GetType().Equals(IDataTypeHandler.JAVASCRIPT_STRING_DATA_TYPE, StringComparison.OrdinalIgnoreCase))
{
tuple.Add(columnName, (String)columnValue);
}
}
else
{
tuple.Add(columnName, columnValue);
}
}
else
{
tuple.Add(columnName, columnValue);
}
}
else if (isLong)
{
if (entityDescriptor != null)
{
EntityDescriptor.Attribute attribute = entityDescriptor.GetAttributeBasedOnColumnName(columnName);
if (attribute != null)
{
if (attribute.GetType().Equals(IDataTypeHandler.JAVASCRIPT_NUMBER_DATA_TYPE, StringComparison.OrdinalIgnoreCase))
{
tuple.Add(columnName, columnValue);
}
}
else
{
tuple.Add(columnName, columnValue);
}
}
else
{
tuple.Add(columnName, columnValue);
}
}
else if (isFloat)
{
if (entityDescriptor != null)
{
EntityDescriptor.Attribute attribute = entityDescriptor.GetAttributeBasedOnColumnName(columnName);
if (attribute != null)
{
if (attribute.GetType().Equals(IDataTypeHandler.JAVASCRIPT_NUMBER_DATA_TYPE, StringComparison.OrdinalIgnoreCase))
{
tuple.Add(columnName, columnValue);
}
}
else
{
tuple.Add(columnName, columnValue);
}
}
else
{
tuple.Add(columnName, columnValue);
}
tuple.Add(columnName, columnValue);
}
else if (isBlob)
{
tuple.Add(columnName, columnValue);
}
}
tuples.Add((Dictionary <String, Object>)tuple);
}
SQLite3.Finalize(statement);
return(tuples.GetEnumerator());
#elif WINDOWS
foreach (SQLiteQueryRow cursor in statement)
{
IDictionary <String, Object> tuple = new Dictionary <String, Object>();
List <SQLiteQueryColumn> columns = cursor.column;
if (columns == null || columns.Count <= 0)
{
continue;
}
foreach (SQLiteQueryColumn column in columns)
{
String columnName = column.Key;
Object columnValue = column.Value;
bool isString = false;
bool isLong = false;
bool isFloat = false;
bool isBlob = false;
if (columnValue != null)
{
if (columnValue.GetType().FullName.Equals(typeof(String).FullName, StringComparison.OrdinalIgnoreCase))
{
isString = true;
}
else if (columnValue.GetType().FullName.Equals(typeof(long).FullName, StringComparison.OrdinalIgnoreCase))
{
isLong = true;
}
else if (columnValue.GetType().FullName.Equals(typeof(float).FullName, StringComparison.OrdinalIgnoreCase))
{
isFloat = true;
}
else if (columnValue.GetType().FullName.Equals(typeof(byte).FullName, StringComparison.OrdinalIgnoreCase))
{
isBlob = true;
}
}
else
{
isString = true;
}
if (isString)
{
if (entityDescriptor != null)
{
EntityDescriptor.Attribute attribute = entityDescriptor.GetAttributeBasedOnColumnName(columnName);
if (attribute != null)
{
if (attribute.GetType().Equals(IDataTypeHandler.CSHARP_STRING_DATA_TYPE, StringComparison.OrdinalIgnoreCase))
{
tuple.Add(columnName, (String)columnValue);
}
else if (attribute.GetType().Equals(IDataTypeHandler.CSHARP_BOOLEAN_PRIMITIVE_DATA_TYPE, StringComparison.OrdinalIgnoreCase))
{
tuple.Add(columnName, columnValue.Equals(Boolean.TrueString.ToString()) ? true : false);
}
else if (attribute.GetType().Equals(IDataTypeHandler.CSHARP_BOOLEAN_DATA_TYPE, StringComparison.OrdinalIgnoreCase))
{
tuple.Add(columnName, columnValue.Equals(Boolean.TrueString.ToString()) ? Boolean.TrueString : Boolean.FalseString);
}
else if (attribute.GetType().Equals(IDataTypeHandler.JAVASCRIPT_STRING_DATA_TYPE, StringComparison.OrdinalIgnoreCase))
{
tuple.Add(columnName, (String)columnValue);
}
}
else
{
tuple.Add(columnName, columnValue);
}
}
else
{
tuple.Add(columnName, columnValue);
}
}
else if (isLong)
{
if (entityDescriptor != null)
{
EntityDescriptor.Attribute attribute = entityDescriptor.GetAttributeBasedOnColumnName(columnName);
if (attribute != null)
{
if (attribute.GetType().Equals(IDataTypeHandler.JAVASCRIPT_NUMBER_DATA_TYPE, StringComparison.OrdinalIgnoreCase))
{
tuple.Add(columnName, columnValue);
}
}
else
{
tuple.Add(columnName, columnValue);
}
}
else
{
tuple.Add(columnName, columnValue);
}
}
else if (isFloat)
{
if (entityDescriptor != null)
{
EntityDescriptor.Attribute attribute = entityDescriptor.GetAttributeBasedOnColumnName(columnName);
if (attribute != null)
{
if (attribute.GetType().Equals(IDataTypeHandler.JAVASCRIPT_NUMBER_DATA_TYPE, StringComparison.OrdinalIgnoreCase))
{
tuple.Add(columnName, columnValue);
}
}
else
{
tuple.Add(columnName, columnValue);
}
}
else
{
tuple.Add(columnName, columnValue);
}
tuple.Add(columnName, columnValue);
}
else if (isBlob)
{
tuple.Add(columnName, columnValue);
}
}
tuples.Add((Dictionary <String, Object>)tuple);
}
return(tuples.GetEnumerator());
#endif
}