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