/// <summary>
/// Execute statement
/// </summary>
/// </param>
/// <returns>LastResult</returns>
public int ExecuteStep( )
{
// Execute the statement
int LastResult = CSSQLite.sqlite3_step(vm);
return(LastResult);
}
/// <summary>
/// Creates new instance of SQLiteVdbe class by compiling a statement
/// </summary>
/// <param name="query"></param>
/// <returns>Vdbe</returns>
public SQLiteVdbe(SQLiteDatabase db, String query)
{
vm = null;
// prepare and compile
CSSQLite.sqlite3_prepare_v2(db.Connection(), query, query.Length, ref vm, 0);
}
// private function for creating Column Names
// Return number of colums read
private int ReadColumnNames(Vdbe vm, DataTable table)
{
String columnName = "";
int columnType = 0;
// returns number of columns returned by statement
int columnCount = CSSQLite.sqlite3_column_count(vm);
object[] columnValues = new object[columnCount];
try
{
// reads columns one by one
for (int i = 0; i < columnCount; i++)
{
columnName = CSSQLite.sqlite3_column_name(vm, i);
columnType = CSSQLite.sqlite3_column_type(vm, i);
switch (columnType)
{
case CSSQLite.SQLITE_INTEGER:
{
// adds new integer column to table
table.Columns.Add(columnName, Type.GetType("System.Int64"));
break;
}
case CSSQLite.SQLITE_FLOAT:
{
table.Columns.Add(columnName, Type.GetType("System.Double"));
break;
}
case CSSQLite.SQLITE_TEXT:
{
table.Columns.Add(columnName, Type.GetType("System.String"));
break;
}
case CSSQLite.SQLITE_BLOB:
{
table.Columns.Add(columnName, Type.GetType("System.byte[]"));
break;
}
default:
{
table.Columns.Add(columnName, Type.GetType("System.String"));
break;
}
}
}
}
catch
{
return(0);
}
return(table.Columns.Count);
}
// private function for reading rows and creating table and columns
private int ReadNextRow(Vdbe vm, DataTable table)
{
int columnCount = table.Columns.Count;
if (columnCount == 0)
{
if ((columnCount = ReadColumnNames(vm, table)) == 0)
{
return(CSSQLite.SQLITE_ERROR);
}
}
int resultType;
if ((resultType = CSSQLite.sqlite3_step(vm)) == CSSQLite.SQLITE_ROW)
{
object[] columnValues = new object[columnCount];
for (int i = 0; i < columnCount; i++)
{
int columnType = CSSQLite.sqlite3_column_type(vm, i);
switch (columnType)
{
case CSSQLite.SQLITE_INTEGER:
{
columnValues[i] = CSSQLite.sqlite3_column_int64(vm, i);
break;
}
case CSSQLite.SQLITE_FLOAT:
{
columnValues[i] = CSSQLite.sqlite3_column_double(vm, i);
break;
}
case CSSQLite.SQLITE_TEXT:
{
columnValues[i] = CSSQLite.sqlite3_column_text(vm, i);
break;
}
case CSSQLite.SQLITE_BLOB:
{
byte[] data = CSSQLite.sqlite3_column_blob(vm, i);
columnValues[i] = Convert.ToBase64String(data);
break;
}
default:
{
columnValues[i] = "";
break;
}
}
}
table.Rows.Add(columnValues);
}
return(resultType);
}
/// <summary>
/// Closes opened database.
/// </summary>
public void CloseDatabase()
{
// closes the database if there is one opened
if (db != null)
{
CSSQLite.sqlite3_close(db);
}
}
/// <summary>
/// Opens database.
/// </summary>
/// <param name="DatabaseName">Name of database file</param>
public void OpenDatabase(String DatabaseName)
{
// opens database
if (CSSQLite.sqlite3_open(DatabaseName, ref db) != CSSQLite.SQLITE_OK)
{
// if there is some error, database pointer is set to 0 and exception is throws
db = null;
throw new Exception("Error with opening database " + DatabaseName + "!");
}
}
/// <summary>
/// Executes query that does not return anything (e.g. UPDATE, INSERT, DELETE).
/// </summary>
/// <param name="query"></param>
public void ExecuteNonQuery(String query)
{
// calles SQLite function that executes non-query
CSSQLite.sqlite3_exec(db, query, 0, 0, 0);
// if there is error, excetion is thrown
if (db.errCode != CSSQLite.SQLITE_OK)
{
throw new Exception("Error with executing non-query: \"" + query + "\"!\n" + CSSQLite.sqlite3_errmsg(db));
}
}
/// <summary>
/// BindText
/// </summary>
/// <param name="index"></param>
/// <param name="bLong"></param>
/// <returns>LastResult</returns>
public int BindText(int index, string bText)
{
if ((LastResult = CSSQLite.sqlite3_bind_text(vm, index, bText, -1, null)) == CSSQLite.SQLITE_OK)
{
LastError = "";
}
else
{
LastError = "Error " + LastError + "binding Text [" + bText + "]";
}
return(LastResult);
}
/// <summary>
/// <summary>
/// BindLong
/// </summary>
/// <param name="index"></param>
/// <param name="bLong"></param>
/// <returns>LastResult</returns>
public int BindLong(int index, long bLong)
{
if ((LastResult = CSSQLite.sqlite3_bind_int64(vm, index, bLong)) == CSSQLite.SQLITE_OK)
{
LastError = "";
}
else
{
LastError = "Error " + LastError + "binding Long [" + bLong + "]";
}
return(LastResult);
}
/// <summary>
/// <summary>
/// BindInteger
/// </summary>
/// <param name="index"></param>
/// <param name="bInteger"></param>
/// <returns>LastResult</returns>
public int BindInteger(int index, int bInteger)
{
if ((LastResult = CSSQLite.sqlite3_bind_int(vm, index, bInteger)) == CSSQLite.SQLITE_OK)
{
LastError = "";
}
else
{
LastError = "Error " + LastError + "binding Integer [" + bInteger + "]";
}
return(LastResult);
}
// private function for reading rows and creating table and columns
private int ReadNextRow(Vdbe vm, DataTable table)
{
int columnCount = table.Columns.Count;
if (columnCount == 0)
{
if ((columnCount = ReadColumnNames(vm, table)) == 0)
{
return(CSSQLite.SQLITE_ERROR);
}
}
int resultType;
if ((resultType = CSSQLite.sqlite3_step(vm)) == CSSQLite.SQLITE_ROW)
{
object[] columnValues = new object[columnCount];
for (int i = 0; i < columnCount; i++)
{
int columnType = CSSQLite.sqlite3_column_type(vm, i);
switch (columnType)
{
case CSSQLite.SQLITE_INTEGER:
{
columnValues[i] = CSSQLite.sqlite3_column_int(vm, i);
break;
}
case CSSQLite.SQLITE_FLOAT:
{
columnValues[i] = CSSQLite.sqlite3_column_double(vm, i);
break;
}
case CSSQLite.SQLITE_TEXT:
{
columnValues[i] = CSSQLite.sqlite3_column_text(vm, i);
break;
}
case CSSQLite.SQLITE_BLOB:
{
// Something goes wrong between adding this as a column value and converting to a row value.
byte[] encBlob = CSSQLite.sqlite3_column_blob(vm, i);
string base64 = Convert.ToBase64String(encBlob);
//byte[] decPass = ProtectedData.Unprotect(encBlob, null, DataProtectionScope.CurrentUser);
//string password = Encoding.ASCII.GetString(decPass);
//columnValues[i] = password;
columnValues[i] = base64;
break;
}
default:
{
columnValues[i] = "";
break;
}
}
}
table.Rows.Add(columnValues);
}
return(resultType);
}
/// <summary>
/// Closes statement
/// </summary>
/// </param>
/// <returns>LastResult</returns>
public void Close()
{
CSSQLite.sqlite3_finalize(ref vm);
}
/// <summary>
/// Reset statement
/// </summary>
/// </param>
/// </returns>
public void Reset()
{
// Reset the statment so it's ready to use again
CSSQLite.sqlite3_reset(vm);
}
/// <summary>
/// Returns Result column as Text
/// </summary>
/// </param>
/// <returns>Result column</returns>
public string Result_Text(int index)
{
return(CSSQLite.sqlite3_column_text(vm, index));
}
/// <summary>
/// Returns Result column as Long
/// </summary>
/// </param>
/// <returns>Result column</returns>
public long Result_Long(int index)
{
return(CSSQLite.sqlite3_column_int64(vm, index));
}