public static int insertDebtLoan(DebtLoan debtLoan)
{
int status = 0;
double amount = debtLoan.Amount;
String payer = debtLoan.Person;
String description = debtLoan.Description;
String id = debtLoan.Id;
String[] dateArray = debtLoan.Date.ToString().Split(' ');
String date = dateArray[0];
bool isDebt = debtLoan.Debt;
int cond = 0;
if (isDebt)
{
cond = 1;
}
String accID = debtLoan.AccID;
try
{
using (var connection = new SQLitePCL.SQLiteConnection("Storage.db"))
{
using (var statement = connection.Prepare(@"INSERT INTO DebtLoan (Amount, Person,
Description, ID, Date, Debt, Acc_ID)
VALUES(?,?,?,?,?,?,?);"))
{
statement.Bind(1, amount.ToString());
statement.Bind(2, payer);
statement.Bind(3, description);
statement.Bind(4, id);
statement.Bind(5, date.ToString());
statement.Bind(6, cond);
statement.Bind(7, accID);
SQLiteResult s = statement.Step();
statement.Reset();
statement.ClearBindings();
if ((s.ToString().Equals("DONE")))
{
Debug.WriteLine("Step done");
status = 1;
}
else
{
Debug.WriteLine("Step failed");
status = 0;
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
return(status);
}
private static void ProvisionDatabase(SQLiteConnection sqConn = null)
{
if (sqConn == null)
{
sqConn = new SQLitePCL.SQLiteConnection(DBNAME);
}
using (var cmdCreateTable = sqConn.Prepare
(
"CREATE TABLE Farmer(" +
" Id INT, " +
" Firstname TEXT, " +
" Lastname TEXT, " +
" Speciality TEXT, " +
" HasAnimals BOOLEAN, " +
" HasWineyards BOOLEAN, " +
" HasWholeGrainFields BOOLEAN, " +
" Country TEXT, " +
" NeedsSync BOOLEAN);"
))
{
var result = cmdCreateTable.Step();
if (result != SQLiteResult.DONE)
{
throw new Exception(string.Format("Unable to create database, error returned: {0}!", result.ToString()));
}
}
}
public void SqliteInitializationTest()
{
string dbPath = Path.Combine(PCLStorage.FileSystem.Current.LocalStorage.Path, DB_FILE_NAME);
using (SQLiteLocalStorage storage = new SQLiteLocalStorage())
{ }
using (SQLiteConnection connection = new SQLiteConnection(dbPath))
{
var query = "SELECT name FROM sqlite_master WHERE type='table'";
var tableName = new List<string>();
using (var sqliteStatement = connection.Prepare(query))
{
while(sqliteStatement.Step() == SQLiteResult.ROW)
{
tableName.Add(sqliteStatement.GetText(0));
}
}
Assert.IsTrue(tableName.Count == 2);
Assert.IsTrue(tableName.Contains("datasets"));
Assert.IsTrue(tableName.Contains("records"));
}
}
private void EnableForeignKeys(SQLiteConnection connection)
{
using (var statement = connection.Prepare(@"PRAGMA foreign_keys = ON;"))
{
statement.Step();
}
}
public static void LoadDatabase(SQLiteConnection db)
{
string sql = @"CREATE TABLE IF NOT EXISTS
Customer (Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Name VARCHAR( 140 ),
City VARCHAR( 140 ),
Contact VARCHAR( 140 )
);";
using (var statement = db.Prepare(sql))
{
statement.Step();
}
sql = @"CREATE TABLE IF NOT EXISTS
Project (Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
CustomerId INTEGER,
Name VARCHAR( 140 ),
Description VARCHAR( 140 ),
DueDate DATETIME,
FOREIGN KEY(CustomerId) REFERENCES Customer(Id) ON DELETE CASCADE
)";
using (var statement = db.Prepare(sql))
{
statement.Step();
}
// Turn on Foreign Key constraints
sql = @"PRAGMA foreign_keys = ON";
using (var statement = db.Prepare(sql))
{
statement.Step();
}
}
public override void EndTransaction()
{
if (Connection.State != ConnectionState.Open)
{
throw new InvalidOperationException("Database is not open.");
}
if (Interlocked.Decrement(ref transactionCount) > 0)
{
return;
}
if (currentTransaction == null)
{
if (shouldCommit)
{
throw new InvalidOperationException("Transaction missing.");
}
return;
}
if (shouldCommit)
{
currentTransaction.Commit();
shouldCommit = false;
}
else
{
currentTransaction.Rollback();
}
currentTransaction.Dispose();
currentTransaction = null;
}
public static void insertData(string param1, string param2, string param3)
{
try
{
using (var connection = new SQLiteConnection("Storage.db"))
{
using (var statement = connection.Prepare(@"INSERT INTO Student (ID,NAME,CGPA)
VALUES(?, ?,?);"))
{
statement.Bind(1, param1);
statement.Bind(2, param2);
statement.Bind(3, param3);
// Inserts data.
statement.Step();
statement.Reset();
statement.ClearBindings();
}
}
}
catch(Exception ex)
{
Debug.WriteLine("Exception\n"+ex.ToString());
}
}
public static ObservableCollection<Student> getValues()
{
ObservableCollection<Student> list = new ObservableCollection<Student>();
using (var connection = new SQLiteConnection("Storage.db"))
{
using (var statement = connection.Prepare(@"SELECT * FROM Student;"))
{
while (statement.Step() == SQLiteResult.ROW)
{
list.Add(new Student()
{
Id = (string)statement[0],
Name = (string)statement[1],
Cgpa = statement[2].ToString()
});
Debug.WriteLine(statement[0]+" ---"+statement[1]+" ---"+statement[2]);
}
}
}
return list;
}
public static ObservableCollection <SmallTransactions> getSmallTransactionValues()
{
ObservableCollection <SmallTransactions> list = new ObservableCollection <SmallTransactions>();
using (var connection = new SQLitePCL.SQLiteConnection("Storage.db"))
{
using (var statement = connection.Prepare(@"SELECT * FROM SmallTransactions;"))
{
while (statement.Step() == SQLiteResult.ROW)
{
list.Add(new SmallTransactions()
{
Amount = double.Parse(statement[0].ToString()),
Description = (String)statement[1],
Type = Convert.ToChar(statement[2].ToString()),
Id = (String)statement[3],
Transaction_id = (String)statement[4],
Date = (String)(statement[5]),
AccID = (String)statement[6]
});
}
}
}
return(list);
}
public static ObservableCollection<Downloads> getDownloads()
{
string path = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "User.db");
ObservableCollection<Downloads> list = new ObservableCollection<Downloads>();
using (var connection = new SQLiteConnection(path))
{
using (var statement = connection.Prepare(@"SELECT * FROM DownloadList;"))
{
while (statement.Step() == SQLiteResult.ROW)
{
list.Add(new Downloads()
{
FileName = (string)statement[0],
Path = (string)statement[1],
Date = (string)statement[2],
Size = (string)statement[3]
});
Debug.WriteLine(statement[0] + " ---" + statement[1] + " ---" + statement[2]);
}
}
}
return list;
}
public static ObservableCollection <DebtLoan> getDebtLoanValues()
{
ObservableCollection <DebtLoan> list = new ObservableCollection <DebtLoan>();
using (var connection = new SQLitePCL.SQLiteConnection("Storage.db"))
{
using (var statement = connection.Prepare(@"SELECT * FROM DebtLoan;"))
{
while (statement.Step() == SQLiteResult.ROW)
{
int value = Convert.ToInt16(statement[5]);
bool isDebt = false;
if (value == 1)
{
isDebt = true;
}
list.Add(new DebtLoan()
{
Amount = double.Parse(statement[0].ToString()),
Person = (String)statement[1],
Description = (String)statement[2],
Id = (String)statement[3],
Date = (String)(statement[4]),
Debt = isDebt,
AccID = (String)statement[6]
});
}
}
}
return(list);
}
public static void AddDownload(string filename,string path, string date, string size)
{
string path1 = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "User.db");
try
{
using (var connection = new SQLiteConnection(path1))
{
using (var statement = connection.Prepare(@"INSERT INTO DownloadList (FILENAME,PATH,DATE,SIZE)
VALUES(?,?,?,?);"))
{
statement.Bind(1, filename);
statement.Bind(2, path);
statement.Bind(3, date);
statement.Bind(4, size);
// Inserts data.
statement.Step();
statement.Reset();
statement.ClearBindings();
Debug.WriteLine("Download Added");
}
}
}
catch (Exception ex)
{
Debug.WriteLine("Exception\n" + ex.ToString());
}
}
public static int deleteSmallTrans(String id)
{
int status = 0;
using (var connection = new SQLitePCL.SQLiteConnection("Storage.db"))
{
using (var statement = connection.Prepare(@"DELETE FROM SmallTransactions WHERE TransactionID=?;"))
{
statement.Bind(1, id);
SQLiteResult s = statement.Step();
if ((s.ToString().Equals("DONE")))
{
Debug.WriteLine("Step done");
status = 1;
}
else
{
Debug.WriteLine("Step failed");
status = 0;
}
}
}
return(status);
}
public static String[] findSpecificID(String id)
{
String[] idArray = new String[4];
using (var connection = new SQLitePCL.SQLiteConnection("Storage.db"))
{
using (var statement = connection.Prepare(@"SELECT * FROM IDTracking WHERE IEID=?;"))
{
statement.Bind(1, id);
while (statement.Step() == SQLiteResult.ROW)
{
if (statement[0] != null)
{
idArray[0] = statement[0].ToString();
}
if (statement[1] != null)
{
idArray[1] = statement[1].ToString();
}
if (statement[2] != null)
{
idArray[2] = statement[2].ToString();
}
if (statement[3] != null)
{
idArray[3] = statement[3].ToString();
}
}
statement.Reset();
statement.ClearBindings();
}
}
return(idArray);
}
//Receive info
public static ObservableCollection <IncExp> getIncomeExpenseValues()
{
ObservableCollection <IncExp> list = new ObservableCollection <IncExp>();
using (var connection = new SQLitePCL.SQLiteConnection("Storage.db"))
{
using (var statement = connection.Prepare(@"SELECT * FROM IncExp;"))
{
while (statement.Step() == SQLiteResult.ROW)
{
int value = Convert.ToInt16(statement[7]);
bool isIncome = false;
if (value == 1)
{
isIncome = true;
}
list.Add(new IncExp()
{
Name = (String)statement[0],
Amount = double.Parse(statement[1].ToString()),
Person = (String)statement[2],
Category = (String)statement[3],
Description = (String)statement[4],
Id = (String)statement[5],
Date = (String)statement[6],
Income = isIncome,
AccID = (String)statement[8]
});
}
}
}
return(list);
}
public override void BeginTransaction(IsolationLevel isolationLevel)
{
// NOTE.ZJG: Seems like we should really be using TO SAVEPOINT
// but this is how Android SqliteDatabase does it,
// so I'm matching that for now.
Interlocked.Increment(ref transactionCount);
currentTransaction = Connection.BeginTransaction(isolationLevel);
}
private DBHelper(string sqliteDb)
{
SoupNameToTableNamesMap = new Dictionary<string, string>();
SoupNameToIndexSpecsMap = new Dictionary<string, IndexSpec[]>();
DatabasePath = sqliteDb;
_sqlConnection = (SQLiteConnection) Activator.CreateInstance(_sqliteConnectionType, sqliteDb);
}
public PlanetaDao(SQLiteConnection con)
{
this.con = con;
string sql = "CREATE TABLE IF NOT EXISTS planeta (id INTEGER PRIMARY KEY AUTOINCREMENT, nombre TEXT, gravedad FLOAT)";
using (var statement =con.Prepare(sql)) {
statement.Step();
}
}
public WordListDB()
{
connection_ = new SQLiteConnection(DB_NAME);
using (var statement = connection_.Prepare(SQL_CREATE_TABLE))
{
statement.Step();
}
}
public FacturaDao(SQLiteConnection con)
{
this.con = con;
string sql = "CREATE TABLE IF NOT EXISTS factura (id INTEGER PRIMARY KEY AUTOINCREMENT, nombre TEXT, vence DATETIME, alarma DATETIME, valor INTEGER, estado TEXT)";
using (var statement = con.Prepare(sql))
{
statement.Step();
}
}
public MainPage()
{
this.InitializeComponent();
con = new SQLiteConnection(Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "FacturasBD.sqlite"));
factDao = new FacturaDao(con);
facturas = App.Current.Resources["facturas"] as Facturas;
rootFrame = Window.Current.Content as Frame;
}
public WordBookDB()
{
mutex_ = new object();
connection_ = new SQLiteConnection(SQL_CREATE_TABLE);
using (var statement = connection_.Prepare(SQL_CREATE_TABLE))
{
statement.Step();
}
}
/// <summary>
/// Initializes a new instance of <see cref="MobileServiceSQLiteStore"/>
/// </summary>
/// <param name="fileName">Name of the local SQLite database file.</param>
public MobileServiceSQLiteStore(string fileName)
{
if (fileName == null)
{
throw new ArgumentNullException("fileName");
}
this.connection = new SQLiteConnection(fileName);
}
public AddFacturaPage()
{
this.InitializeComponent();
con = new SQLiteConnection(Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "FacturasBD.sqlite"));
factDao = new FacturaDao(con);
rootFrame = Window.Current.Content as Frame;
SystemNavigationManager.GetForCurrentView().AppViewBackButtonVisibility = AppViewBackButtonVisibility.Visible;
SystemNavigationManager.GetForCurrentView().BackRequested += AddFacturaPage_BackRequested;
}
/// <summary>
/// Closes the connection (if open).
/// </summary>
public void Close()
{
if (Connection.State == ConnectionState.Open)
{
Connection.Close();
Connection = null;
_isOpen = false;
}
}
/// <summary>
/// Invoked when the application is launched normally by the end user. Other entry points
/// will be used such as when the application is launched to open a specific file.
/// </summary>
/// <param name="e">Details about the launch request and process.</param>
protected override async void OnLaunched(LaunchActivatedEventArgs e)
{
conn = new SQLiteConnection("bazadanych-sqlite.db");
CreateDatabase.LoadDatabase(conn);
// Windows 10 Mobile StatusBar Configuration
if (Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons"))
{
var statusBar = Windows.UI.ViewManagement.StatusBar.GetForCurrentView();
//statusBar.BackgroundColor = Windows.UI.Colors.Black;
//statusBar.ForegroundColor = Windows.UI.Colors.White;
//statusBar.BackgroundOpacity = 1;
await statusBar.HideAsync();
}
//await CreateDatabase.ResetDataAsync(conn);
// Do not repeat app initialization when the Window already has content,
// just ensure that the window is active
if (Window.Current.Content == null)
{
// Create a Frame to act as the navigation context and navigate to the first page
_rootFrame = new Frame();
_rootFrame.NavigationFailed += OnNavigationFailed;
_rootFrame.Navigated += OnNavigated;
if (e.PreviousExecutionState == ApplicationExecutionState.Terminated)
{
//TODO: Load state from previously suspended application
}
// Place the frame in the current Window
Window.Current.Content = new Shell(_rootFrame);
// Register a handler for BackRequested events and set the
// visibility of the Back button
SystemNavigationManager.GetForCurrentView().BackRequested += OnBackRequested;
SystemNavigationManager.GetForCurrentView().AppViewBackButtonVisibility =
_rootFrame.CanGoBack ?
AppViewBackButtonVisibility.Visible :
AppViewBackButtonVisibility.Collapsed;
}
if (_rootFrame.Content == null)
{
// When the navigation stack isn't restored navigate to the first page,
// configuring the new page by passing required information as a navigation
// parameter
_rootFrame.Navigate(typeof(Views.MainPage), e.Arguments);
}
// Ensure the current window is active
Window.Current.Activate();
}
//Delete table
public static void dropIncomeExpenseTable()
{
using (var connection = new SQLitePCL.SQLiteConnection("Storage.db"))
{
using (var statement = connection.Prepare(@"DROP TABLE IncExp;"))
{
statement.Step();
}
}
}
public BeginTransactionSqliteOperation(SQLiteConnection conn)
{
var result = (SQLite3.Result)raw.sqlite3_prepare_v2(conn.Handle, "BEGIN TRANSACTION", out beginOp);
Connection = conn;
if (result != SQLite3.Result.OK)
{
throw new SQLiteException(result, "Couldn't prepare statement");
}
inner = beginOp;
}
public static int insertSmallTransactions(SmallTransactions sTrans)
{
int status = 0;
double amount = sTrans.Amount;
String description = sTrans.Description;
char type = sTrans.Type;
String id = sTrans.Id;
String trans_id = sTrans.Transaction_id;
String[] dateArray = sTrans.Date.ToString().Split(' ');
String date = dateArray[0];
String accID = sTrans.AccID;
try
{
using (var connection = new SQLitePCL.SQLiteConnection("Storage.db"))
{
using (var statement = connection.Prepare(@"INSERT INTO SmallTransactions (Amount, Description, Type,
ID, TransactionID, Date, Acc_ID)
VALUES(?,?,?,?,?,?,?);"))
{
statement.Bind(1, amount.ToString());
statement.Bind(2, description);
statement.Bind(3, type.ToString());
statement.Bind(4, id);
statement.Bind(5, trans_id);
statement.Bind(6, date);
statement.Bind(7, accID);
SQLiteResult s = statement.Step();
statement.Reset();
statement.ClearBindings();
if ((s.ToString().Equals("DONE")))
{
Debug.WriteLine("Step done");
status = 1;
}
else
{
Debug.WriteLine("Step failed");
status = 0;
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
return(status);
}
public static int insertIDs(String incexpID, String otherID)
{
int status = 0;
int number = 0;
String[] array = otherID.Split(' ');
if (array[0].Equals("sa"))
{
number = 3;
}
else if (array[0].Equals("dl"))
{
number = 2;
}
else if (array[0].Equals("st"))
{
number = 4;
}
try
{
using (var connection = new SQLitePCL.SQLiteConnection("Storage.db"))
{
using (var statement = connection.Prepare(@"INSERT INTO IDTracking (IEID, DLID, SAID, STID)VALUES(?,?,?,?);"))
{
statement.Bind(1, incexpID);
statement.Bind(number, otherID);
SQLiteResult s = statement.Step();
statement.Reset();
statement.ClearBindings();
if ((s.ToString().Equals("DONE")))
{
Debug.WriteLine("ID insert step done");
status = 1;
}
else
{
Debug.WriteLine("ID insert step failed");
status = 0;
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
return(status);
}
public void Init()
{
using (var connection = new SQLiteConnection(DatabaseFilename))
{
EnableForeignKeys(connection);
using (var statement = connection.Prepare(@"CREATE TABLE IF NOT EXISTS SAVED_STREAM_ITEM (ID TEXT PRIMARY KEY NOT NULL,
TITLE TEXT,
PUBLISHED TEXT,
WEBURI TEXT,
SHORT_CONTENT TEXT,
CONTENT TEXT,
IMAGE_FOLDER TEXT);"))
{
statement.Step();
}
using (var statement = connection.Prepare(@"CREATE TABLE IF NOT EXISTS TAG_ACTION (ID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
ITEM_ID TEXT,
TAG TEXT,
ACTION_KIND INTEGER);"))
{
statement.Step();
}
using (var statement = connection.Prepare(@"CREATE TABLE IF NOT EXISTS SUB_ITEM (ID TEXT PRIMARY KEY NOT NULL,
SORT_ID TEXT, TITLE TEXT, UNREAD_COUNT INTEGER, URL TEXT, HTML_URL TEXT, ICON_URL TEXT, FIRST_ITEM_MSEC INTEGER);"))
{
statement.Step();
}
using (var statement = connection.Prepare(@"CREATE TABLE IF NOT EXISTS SUB_CAT (ID TEXT PRIMARY KEY NOT NULL,
SORT_ID TEXT, TITLE TEXT, UNREAD_COUNT INTEGER);"))
{
statement.Step();
}
using (var statement = connection.Prepare(@"CREATE TABLE IF NOT EXISTS SUB_CAT_SUB_ITEM (CAT_ID TEXT, ITEM_ID TEXT);"))
{
statement.Step();
}
using (var statement = connection.Prepare(@"CREATE TABLE IF NOT EXISTS STREAM_COLLECTION (STREAM_ID TEXT PRIMARY KEY NOT NULL, CONTINUATION TEXT, SHOW_NEWEST_FIRST INTEGER, STREAM_TIMESTAMP INTEGER, FAULT INTEGER);"))
{
statement.Step();
}
using (var statement = connection.Prepare(@"CREATE TABLE IF NOT EXISTS STREAM_ITEM (ID TEXT PRIMARY KEY NOT NULL, STREAM_ID TEXT REFERENCES STREAM_COLLECTION(STREAM_ID) ON DELETE CASCADE, PUBLISHED TEXT, TITLE TEXT, WEB_URI TEXT, CONTENT TEXT, UNREAD INTEGER, NEED_SET_READ_EXPLICITLY INTEGER, IS_SELECTED INTEGER, STARRED INTEGER, SAVED INTEGER);"))
{
statement.Step();
}
}
}
public BulkInsertSqliteOperation(SQLiteConnection conn)
{
var result = (SQLite3.Result)raw.sqlite3_prepare_v2(conn.Handle, "INSERT OR REPLACE INTO CacheElement VALUES (?,?,?,?,?)", out insertOp);
Connection = conn;
if (result != SQLite3.Result.OK)
{
throw new SQLiteException(result, "Couldn't prepare statement");
}
inner = insertOp;
}
public static void ExecuteNonQuery(string dbName, string sql)
{
using (var connection = new SQLiteConnection(dbName))
{
using (var statement = connection.Prepare(sql))
{
if (statement.Step() != SQLiteResult.DONE)
{
throw new InvalidOperationException();
}
}
}
}
public static void createTable()
{
using (var connection = new SQLiteConnection("Storage.db"))
{
using (var statement = connection.Prepare(@"CREATE TABLE IF NOT EXISTS Student (
ID NVARCHAR(10),
NAME NVARCHAR(50),
CGPA NVARCHAR(10));"))
{
statement.Step();
}
}
}
public static long CountRows(string dbName, string tableName)
{
long count;
using (var connection = new SQLiteConnection(dbName))
{
using (var statement = connection.Prepare("SELECT COUNT(1) from " + tableName))
{
statement.Step();
count = (long)statement[0];
}
}
return count;
}
private void LoadDatabase()
{
conn = new SQLiteConnection("record_list.db");
string sql_create = @"CREATE TABLE IF NOT EXISTS
Record (Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
Name VARCHAR(200),
FinishTime INTEGER,
Date VARCHAR(200))";
using (var statement = conn.Prepare(sql_create))
{
statement.Step();
}
}
public static void LoadDatabase(SQLiteConnection db)
{
string sql = @"CREATE TABLE IF NOT EXISTS
UserLocation (Id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
DateCreated VARCHAR( 140 ),
Lat VARCHAR( 140 ),
Lon VARCHAR( 140 ),
Att VARCHAR( 140 )
);";
using (var statement = db.Prepare(sql))
{
statement.Step();
}
}
public static List<SimpleGeoData> GetAllLocation(SQLiteConnection db)
{
var ret = new List<SimpleGeoData>();
using (var stmt = db.Prepare("SELECT Id, DateCreated, Lat, Long, Att FROM UserLocation"))
{
while(stmt.Step()== SQLiteResult.ROW)
{
var item = CreateSimpleGeo(stmt);
ret.Add(item);
}
}
return ret;
}
public static void createIDTrackingTable()
{
using (var connection = new SQLitePCL.SQLiteConnection("Storage.db"))
{
using (var statement = connection.Prepare(@"CREATE TABLE IF NOT EXISTS IDTracking (
IEID NVARCHAR(10),
DLID NVARCHAR(10),
SAID NVARCHAR(10),
STID NVARCHAR(10));"))
{
statement.Step();
}
}
}
public static int updateSaving(Savings saving)
{
int status = 0;
String name = saving.Name;
double goal = saving.Goal;
double initial = saving.Initial;
String id = saving.Id;
String accID = saving.AccID;
try
{
using (var connection = new SQLitePCL.SQLiteConnection("Storage.db"))
{
using (var statement = connection.Prepare(@"UPDATE Savings SET Name=?, Goal=?, Initial=?,
ID=?, Acc_ID=?
WHERE ID=?;"))
{
statement.Bind(1, name);
statement.Bind(2, goal.ToString());
statement.Bind(3, initial.ToString());
statement.Bind(4, id);
statement.Bind(5, accID);
statement.Bind(6, id);
SQLiteResult s = statement.Step();
statement.Reset();
statement.ClearBindings();
if ((s.ToString().Equals("DONE")))
{
Debug.WriteLine("Update done");
status = 1;
}
else
{
Debug.WriteLine("Update failed");
status = 0;
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
return(status);
}
private void SetupDatabase()
{
#if __IOS__
SQLitePCL.CurrentPlatform.Init();
#endif
string dbPath = Path.Combine(PCLStorage.FileSystem.Current.LocalStorage.Path, DB_FILE_NAME);
connection = new SQLiteConnection(dbPath);
string createDatasetTable = "CREATE TABLE IF NOT EXISTS " + TABLE_DATASETS + "("
+ DatasetColumns.IDENTITY_ID + " TEXT NOT NULL,"
+ DatasetColumns.DATASET_NAME + " TEXT NOT NULL,"
+ DatasetColumns.CREATION_TIMESTAMP + " TEXT DEFAULT '0',"
+ DatasetColumns.LAST_MODIFIED_TIMESTAMP + " TEXT DEFAULT '0',"
+ DatasetColumns.LAST_MODIFIED_BY + " TEXT,"
+ DatasetColumns.STORAGE_SIZE_BYTES + " INTEGER DEFAULT 0,"
+ DatasetColumns.RECORD_COUNT + " INTEGER DEFAULT 0,"
+ DatasetColumns.LAST_SYNC_COUNT + " INTEGER NOT NULL DEFAULT 0,"
+ DatasetColumns.LAST_SYNC_TIMESTAMP + " INTEGER DEFAULT '0',"
+ DatasetColumns.LAST_SYNC_RESULT + " TEXT,"
+ "UNIQUE (" + DatasetColumns.IDENTITY_ID + ", "
+ DatasetColumns.DATASET_NAME + ")"
+ ")";
using (var sqliteStatement = connection.Prepare(createDatasetTable))
{
sqliteStatement.Step();
}
string createRecordsTable = "CREATE TABLE IF NOT EXISTS " + TABLE_RECORDS + "("
+ RecordColumns.IDENTITY_ID + " TEXT NOT NULL,"
+ RecordColumns.DATASET_NAME + " TEXT NOT NULL,"
+ RecordColumns.KEY + " TEXT NOT NULL,"
+ RecordColumns.VALUE + " TEXT,"
+ RecordColumns.SYNC_COUNT + " INTEGER NOT NULL DEFAULT 0,"
+ RecordColumns.LAST_MODIFIED_TIMESTAMP + " TEXT DEFAULT '0',"
+ RecordColumns.LAST_MODIFIED_BY + " TEXT,"
+ RecordColumns.DEVICE_LAST_MODIFIED_TIMESTAMP + " TEXT DEFAULT '0',"
+ RecordColumns.MODIFIED + " INTEGER NOT NULL DEFAULT 1,"
+ "UNIQUE (" + RecordColumns.IDENTITY_ID + ", " + RecordColumns.DATASET_NAME
+ ", " + RecordColumns.KEY + ")"
+ ")";
using (var sqliteStatement = connection.Prepare(createRecordsTable))
{
sqliteStatement.Step();
}
}
public override bool Open (string path)
{
var result = true;
try {
shouldCommit = false;
connection = new SQLiteConnection (path);
CreateFunctions ();
} catch (Exception ex) {
Log.E(Tag, string.Format("Error opening the Sqlite connection using connection String: {0}", path), ex);
result = false;
}
return result;
}
public static void createSavingsTable()
{
using (var connection = new SQLitePCL.SQLiteConnection("Storage.db"))
{
using (var statement = connection.Prepare(@"CREATE TABLE IF NOT EXISTS Savings (
Name NVARCHAR(50),
Goal DOUBLE(20),
Initial DOUBLE(20),
ID NVARCHAR(10),
Acc_ID NVARCHAR(10));"))
{
statement.Step();
}
}
}
public static int insertSavings(Savings savings)
{
int status = 0;
String name = savings.Name;
double goal = savings.Goal;
double initial = savings.Initial;
String id = savings.Id;
String accID = savings.AccID;
try
{
using (var connection = new SQLitePCL.SQLiteConnection("Storage.db"))
{
using (var statement = connection.Prepare(@"INSERT INTO Savings (Name, Goal, Initial,
ID, Acc_ID)
VALUES(?,?,?,?,?);"))
{
statement.Bind(1, name);
statement.Bind(2, goal.ToString());
statement.Bind(3, initial.ToString());
statement.Bind(4, id);
statement.Bind(5, accID);
SQLiteResult s = statement.Step();
statement.Reset();
statement.ClearBindings();
if ((s.ToString().Equals("DONE")))
{
Debug.WriteLine("Step done");
status = 1;
}
else
{
Debug.WriteLine("Step failed");
status = 0;
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
return(status);
}
public App()
{
Microsoft.ApplicationInsights.WindowsAppInitializer.InitializeAsync(
Microsoft.ApplicationInsights.WindowsCollectors.Metadata |
Microsoft.ApplicationInsights.WindowsCollectors.Session);
this.InitializeComponent();
this.Suspending += OnSuspending;
login = false;
conn = new SQLiteConnection("sqlitetodo.db");
string sql = @"CREATE TABLE IF NOT EXISTS User (Username VARCHAR(20) PRIMARY KEY,Password VARCHAR(20),Root INTERGER(1))";
using (var statement = conn.Prepare(sql))
{
statement.Step();
}
}
public bool Open (String path)
{
var result = true;
try {
shouldCommit = false;
Connection = new SQLiteConnection(path);// (connectionString.ToString ());
Connection.CreateFunction("JSON", 2, new Function(CouchbaseSqliteJsonUnicodeCollationFunction.Compare), true);
Connection.CreateFunction("JSON_ASCII", 2, new Function(CouchbaseSqliteJsonAsciiCollationFunction.Compare), true);
Connection.CreateFunction("JSON_RAW", 2, new Function(CouchbaseSqliteJsonRawCollationFunction.Compare), true);
Connection.CreateFunction("REVID", 2, new Function(CouchbaseSqliteRevIdCollationFunction.Compare), true);
} catch (Exception ex) {
Log.E(Tag, "Error opening the Sqlite connection using connection String: {0}".Fmt(path), ex);
result = false;
}
return result;
}
public static String getLastID(String table)
{
String id = "";
String text = "ID";
if (table.Equals("SmallTransactions"))
{
text = "TransactionID";
}
using (var connection = new SQLitePCL.SQLiteConnection("Storage.db"))
{
using (var statement = connection.Prepare(@"SELECT * FROM " + table + " ORDER BY " + text + " DESC LIMIT 1;"))
{
if (table.Equals("IncExp"))
{
while (statement.Step() == SQLiteResult.ROW)
{
id = statement[5].ToString();
}
}
else if (table.Equals("Savings"))
{
while (statement.Step() == SQLiteResult.ROW)
{
id = statement[3].ToString();
}
}
else if (table.Equals("DebtLoan"))
{
while (statement.Step() == SQLiteResult.ROW)
{
id = statement[3].ToString();
}
}
else if (table.Equals("SmallTransactions"))
{
while (statement.Step() == SQLiteResult.ROW)
{
id = statement[4].ToString();
}
}
}
}
return(id);
}
public Task<IDisposable> Open()
{
Debug.WriteLine("Opening database: {0}", this.dbFile);
db = new SQLiteConnection(dbFile);
#if DEBUG
this.databaseThreadId = Environment.CurrentManagedThreadId;
#endif
return Task.FromResult<IDisposable>(new Disposable(() =>
{
if (db != null)
{
db.Dispose();
db = null;
}
}));
}
public static void createSmallTransactionsTable()
{
using (var connection = new SQLitePCL.SQLiteConnection("Storage.db"))
{
using (var statement = connection.Prepare(@"CREATE TABLE IF NOT EXISTS SmallTransactions (
Amount DOUBLE(20),
Description NVARCHAR(200),
Type NVARCHAR(10),
ID NVARCHAR(10),
TransactionID NVARCHAR(10),
Date NVARCHAR(20),
Acc_ID NVARCHAR(10));"))
{
statement.Step();
}
}
}
public static int deleteIDTracking(String id)
{
String[] array = id.Split(' ');
String idType;
if (array[0].Equals("ie"))
{
idType = "IEID";
}
else if (array[0].Equals("sa"))
{
idType = "SAID";
}
else if (array[0].Equals("dl"))
{
idType = "DLID";
}
else
{
idType = "STID";
}
int status = 0;
using (var connection = new SQLitePCL.SQLiteConnection("Storage.db"))
{
using (var statement = connection.Prepare(@"DELETE FROM IDTracking WHERE " + idType + "=?;"))
{
statement.Bind(1, id);
SQLiteResult s = statement.Step();
if ((s.ToString().Equals("DONE")))
{
Debug.WriteLine("Step done");
status = 1;
}
else
{
Debug.WriteLine("Step failed");
status = 0;
}
}
}
return(status);
}
public static void createDebtLoanTable()
{
using (var connection = new SQLitePCL.SQLiteConnection("Storage.db"))
{
using (var statement = connection.Prepare(@"CREATE TABLE IF NOT EXISTS DebtLoan (
Amount DOUBLE(20),
Person NVARCHAR(60),
Description NVARCHAR(200),
ID NVARCHAR(10),
Date NVARCHAR(20),
Debt TINYINT(1),
Acc_ID NVARCHAR(10));"))
{
statement.Step();
}
}
}
//Create Tables
public static void createIncomeExpenseTable()
{
using (var connection = new SQLitePCL.SQLiteConnection("Storage.db"))
{
using (var statement = connection.Prepare(@"CREATE TABLE IF NOT EXISTS IncExp (
Name NVARCHAR(50),
Amount DOUBLE(20),
Person NVARCHAR(60),
Category NVARCHAR(60),
Description NVARCHAR(200),
ID NVARCHAR(10),
Date NVARCHAR(20),
Income TINYINT(1),
Acc_ID NVARCHAR(10));"))
{
statement.Step();
}
}
}
public static ObservableCollection <String> getAllIEIDs(String id)
{
ObservableCollection <String> list = new ObservableCollection <String>();
using (var connection = new SQLitePCL.SQLiteConnection("Storage.db"))
{
using (var statement = connection.Prepare(@"SELECT * FROM IDTracking WHERE SAID=?;"))
{
statement.Bind(1, id);
while (statement.Step() == SQLiteResult.ROW)
{
list.Add(statement[0].ToString());
}
statement.Reset();
statement.ClearBindings();
}
}
return(list);
}
public static String findIEID(String otherId, String idType)
{
String id = null;
using (var connection = new SQLitePCL.SQLiteConnection("Storage.db"))
{
using (var statement = connection.Prepare(@"SELECT * FROM IDTracking WHERE " + idType + "=?;"))
{
statement.Bind(1, otherId);
while (statement.Step() == SQLiteResult.ROW)
{
if (statement[0] != null)
{
id = statement[0].ToString();
}
}
statement.Reset();
statement.ClearBindings();
}
}
return(id);
}
public static int insertMoreIDs(String incexpID, String saID, String stID)
{
int status = 0;
try
{
using (var connection = new SQLitePCL.SQLiteConnection("Storage.db"))
{
using (var statement = connection.Prepare(@"INSERT INTO IDTracking (IEID, DLID, SAID, STID)VALUES(?,?,?,?);"))
{
statement.Bind(1, incexpID);
statement.Bind(3, saID);
statement.Bind(4, stID);
SQLiteResult s = statement.Step();
statement.Reset();
statement.ClearBindings();
if ((s.ToString().Equals("DONE")))
{
Debug.WriteLine("ID insert step done");
status = 1;
}
else
{
Debug.WriteLine("ID insert step failed");
status = 0;
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
return(status);
}
public static ObservableCollection <Savings> getSavingsValues()
{
ObservableCollection <Savings> list = new ObservableCollection <Savings>();
using (var connection = new SQLitePCL.SQLiteConnection("Storage.db"))
{
using (var statement = connection.Prepare(@"SELECT * FROM Savings;"))
{
while (statement.Step() == SQLiteResult.ROW)
{
list.Add(new Savings()
{
Name = (String)statement[0],
Goal = double.Parse(statement[1].ToString()),
Initial = double.Parse(statement[2].ToString()),
Id = (String)statement[3],
AccID = (String)statement[4]
});
}
}
}
return(list);
}
//Insert info
public static int insertIncome(IncExp incExp)
{
int status = 0;
String name = incExp.Name;
double amount = incExp.Amount;
String payer = incExp.Person;
String category = incExp.Category;
String description = incExp.Description;
String id = incExp.Id;
String[] dateArray = incExp.Date.ToString().Split(' ');
String date = dateArray[0];
bool isIncome = incExp.Income;
int cond = 0;
if (isIncome)
{
cond = 1;
}
String accID = incExp.AccID;
try
{
using (var connection = new SQLitePCL.SQLiteConnection("Storage.db"))
{
using (var statement = connection.Prepare(@"INSERT INTO IncExp (Name, Amount, Person,
Category, Description, ID, Date, Income, Acc_ID)
VALUES(?,?,?,?,?,?,?,?,?);"))
{
statement.Bind(1, name);
statement.Bind(2, amount.ToString());
statement.Bind(3, payer);
statement.Bind(4, category);
statement.Bind(5, description);
statement.Bind(6, id);
statement.Bind(7, date.ToString());
statement.Bind(8, cond);
statement.Bind(9, accID);
SQLiteResult s = statement.Step();
statement.Reset();
statement.ClearBindings();
if ((s.ToString().Equals("DONE")))
{
Debug.WriteLine("Step done");
status = 1;
}
else
{
Debug.WriteLine("Step failed");
status = 0;
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
return(status);
}
public BaseDAO(SQLitePCL.SQLiteConnection conn)
{
this.conn = conn;
this.CheckCreateTable();
this.CheckAlterTable();
}
//Update info
public static int updateIncome(IncExp incExp)
{
int status = 0;
String name = incExp.Name;
double amount = incExp.Amount;
String payer = incExp.Person;
String category = incExp.Category;
String description = incExp.Description;
String id = incExp.Id;
String date = incExp.Date;
bool isIncome = incExp.Income;
int cond = 0;
if (isIncome)
{
cond = 1;
}
String accID = incExp.AccID;
try
{
using (var connection = new SQLitePCL.SQLiteConnection("Storage.db"))
{
using (var statement = connection.Prepare(@"UPDATE IncExp SET Name=?, Amount=?, Person=?,
Category=?, Description=?, ID=?, Date=?, Income=?, Acc_ID=?
WHERE ID=?;"))
{
statement.Bind(1, name);
statement.Bind(2, amount.ToString());
statement.Bind(3, payer);
statement.Bind(4, category);
statement.Bind(5, description);
statement.Bind(6, id);
statement.Bind(7, date.ToString());
statement.Bind(8, cond);
statement.Bind(9, accID);
statement.Bind(10, id);
SQLiteResult s = statement.Step();
statement.Reset();
statement.ClearBindings();
if ((s.ToString().Equals("DONE")))
{
Debug.WriteLine("Update done");
status = 1;
}
else
{
Debug.WriteLine("Update failed");
status = 0;
}
}
}
}
catch (Exception ex)
{
Debug.WriteLine(ex.Message);
}
return(status);
}
