static Task <CAsyncDBHandler.SQLExeInfo>[] TestCreateTables(CSqlite sqlite) { var v = new Task <CAsyncDBHandler.SQLExeInfo> [2]; v[0] = sqlite.execute("CREATE TABLE IF NOT EXISTS COMPANY(ID INT8 PRIMARY KEY NOT NULL,name CHAR(64)NOT NULL,ADDRESS varCHAR(256)not null,Income float not null)"); v[1] = sqlite.execute("CREATE TABLE IF NOT EXISTS EMPLOYEE(EMPLOYEEID INT8 PRIMARY KEY NOT NULL unique,CompanyId INT8 not null,name NCHAR(64)NOT NULL,JoinDate DATETIME not null default(datetime('now')),IMAGE BLOB,DESCRIPTION NTEXT,Salary real,FOREIGN KEY(CompanyId)REFERENCES COMPANY(id))"); return(v); }
static Task <CAsyncDBHandler.SQLExeInfo> TestBLOBByPreparedStatement(CSqlite sqlite, List <KeyValue> ra) { //a complex SQL statement combined with two insert and query prepare statements sqlite.Prepare("insert or replace into employee(EMPLOYEEID,CompanyId,name,JoinDate,image,DESCRIPTION,Salary)values(?,?,?,?,?,?,?);select * from employee where employeeid=?"); CDBVariantArray vData = new CDBVariantArray(); using (CScopeUQueue sbBlob = new CScopeUQueue()) { //first set of data vData.Add(1); vData.Add(1); //google company id vData.Add("Ted Cruz"); vData.Add(DateTime.Now); sbBlob.Save(m_wstr); vData.Add(sbBlob.UQueue.GetBuffer()); vData.Add(m_wstr); vData.Add(254000.0); vData.Add(1); //second set of data vData.Add(2); vData.Add(1); //google company id vData.Add("Donald Trump"); vData.Add(DateTime.Now); sbBlob.UQueue.SetSize(0); sbBlob.Save(m_str); vData.Add(sbBlob.UQueue.GetBuffer()); vData.Add(m_str); vData.Add(20254000.0); vData.Add(2); //third set of data vData.Add(3); vData.Add(2); //Microsoft company id vData.Add("Hillary Clinton"); vData.Add(DateTime.Now); sbBlob.Save(m_wstr); vData.Add(sbBlob.UQueue.GetBuffer()); vData.Add(m_wstr); vData.Add(6254000.0); vData.Add(3); } //send three sets of parameterized data in one shot for processing return(sqlite.execute(vData, (handler, rowData) => { //rowset data come here int last = ra.Count - 1; KeyValue item = ra[last]; item.Value.AddRange(rowData); }, (handler) => { //rowset header meta info comes here KeyValue item = new KeyValue(handler.ColumnInfo, new CDBVariantArray()); ra.Add(item); })); }
private async void btnConnect_Click(object sender, EventArgs e) { CConnectionContext cc = new CConnectionContext(txtHost.Text, 20901, txtUser.Text, txtPassword.Text); m_spSqlite = new CSocketPool <CSqlite>(false); //set event for MySQL/Mariadb database shutdown m_spSqlite.SocketPoolEvent += new CSocketPool <CSqlite> .DOnSocketPoolEvent(m_spSqlite_SocketPoolEvent); if (!m_spSqlite.StartSocketPool(cc, 1)) { txtMessage.Text = "No connection to " + txtHost.Text; return; } CSqlite sqlite = m_spSqlite.AsyncHandlers[0]; //set event for tracking all database table update events, delete, update and insert m_spSqlite.Sockets[0].Push.OnPublish += new DOnPublish(Push_OnPublish); //create a DB session with default to sample database sakil sqlite.Open("sakila.db", null, DB_CONSTS.ENABLE_TABLE_UPDATE_MESSAGES); m_ds = new DataSet("real-time cache"); DataTable dt = null; //query all cached tables into client side for intial cache data var res = await sqlite.execute("", (h, data) => { //this callback is fired from worker thread from socket pool thread CSqlite.AppendRowDataIntoDataTable(data, dt); }, (h) => { //this callback is fired from worker thread from socket pool thread dt = CSqlite.MakeDataTable(h.ColumnInfo); string name = h.ColumnInfo[0].DBPath + "." + h.ColumnInfo[0].TablePath; dt.TableName = name; m_ds.Tables.Add(dt); }); txtMessage.Text = res.em; lstTables.Items.Clear(); foreach (DataTable table in m_ds.Tables) { lstTables.Items.Add(table.TableName); } if (m_ds.Tables.Count > 0) { lstTables.SelectedIndex = 0; } btnDisconnect.Enabled = (res.ec == 0); btnConnect.Enabled = (res.ec != 0); }
private async void btnDoSQL_Click(object sender, EventArgs e) { CSqlite sqlite = null; if (m_spSqlite != null) { sqlite = m_spSqlite.Seek(); } if (sqlite == null) { txtMessage.Text = "No connection to a SocketPro SQLite server"; return; } var res = await sqlite.execute(txtSQL.Text); txtMessage.Text = res.em; }
static Task <CAsyncDBHandler.SQLExeInfo> TestPreparedStatements(CSqlite sqlite, List <KeyValue> ra) { //a complex SQL statement combined with query and insert prepare statements sqlite.Prepare("Select datetime('now');INSERT OR REPLACE INTO COMPANY(ID,NAME,ADDRESS,Income)VALUES(?,?,?,?)"); CDBVariantArray vData = new CDBVariantArray(); vData.Add(1); vData.Add("Google Inc."); vData.Add("1600 Amphitheatre Parkway, Mountain View, CA 94043, USA"); vData.Add(66000000000.0); vData.Add(2); vData.Add("Microsoft Inc."); vData.Add("700 Bellevue Way NE- 22nd Floor, Bellevue, WA 98804, USA"); vData.Add(93600000000.0); vData.Add(3); vData.Add("Apple Inc."); vData.Add("1 Infinite Loop, Cupertino, CA 95014, USA"); vData.Add(234000000000.0); //send three sets of parameterized data in one shot for processing return(sqlite.execute(vData, (handler, rowData) => { //rowset data come here int last = ra.Count - 1; KeyValue item = ra[last]; item.Value.AddRange(rowData); }, (handler) => { //rowset header meta info comes here KeyValue item = new KeyValue(handler.ColumnInfo, new CDBVariantArray()); ra.Add(item); })); }