static void Main(string[] args)
{
Console.WriteLine("If this test works, you should get:");
Console.WriteLine("Data 1: 5");
Console.WriteLine("Data 2: Mono");
Console.WriteLine("create SqliteConnection...");
SqliteConnection dbcon = new SqliteConnection();
// the connection string is a URL that points
// to a file. If the file does not exist, a
// file is created.
// "URI=file:some/path"
string connectionString =
"URI=file:SqliteTest.db";
Console.WriteLine("setting ConnectionString using: " +
connectionString);
dbcon.ConnectionString = connectionString;
Console.WriteLine("open the connection...");
dbcon.Open();
Console.WriteLine("create SqliteCommand to CREATE TABLE MONO_TEST");
SqliteCommand dbcmd = new SqliteCommand();
dbcmd.Connection = dbcon;
dbcmd.CommandText =
"CREATE TABLE MONO_TEST ( " +
"NID INT, " +
"NDESC TEXT )";
Console.WriteLine("execute command...");
dbcmd.ExecuteNonQuery();
Console.WriteLine("set and execute command to INSERT INTO MONO_TEST");
dbcmd.CommandText =
"INSERT INTO MONO_TEST " +
"(NID, NDESC )"+
"VALUES(5,'Mono')";
dbcmd.ExecuteNonQuery();
Console.WriteLine("set command to SELECT FROM MONO_TEST");
dbcmd.CommandText =
"SELECT * FROM MONO_TEST";
SqliteDataReader reader;
Console.WriteLine("execute reader...");
reader = dbcmd.ExecuteReader();
Console.WriteLine("read and display data...");
while(reader.Read()) {
Console.WriteLine("Data 1: " + reader[0].ToString());
Console.WriteLine("Data 2: " + reader[1].ToString());
}
Console.WriteLine("read and display data using DataAdapter...");
SqliteDataAdapter adapter = new SqliteDataAdapter("SELECT * FROM MONO_TEST", connectionString);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
foreach(DataTable myTable in dataset.Tables){
foreach(DataRow myRow in myTable.Rows){
foreach (DataColumn myColumn in myTable.Columns){
Console.WriteLine(myRow[myColumn]);
}
}
}
Console.WriteLine("clean up...");
dataset.Dispose();
adapter.Dispose();
reader.Close();
dbcmd.Dispose();
dbcon.Close();
Console.WriteLine("Done.");
}
static void Test(bool v3, string encoding) {
if (!v3)
Console.WriteLine("Testing Version 2" + (encoding != null ? " with " + encoding + " encoding" : ""));
else
Console.WriteLine("Testing Version 3");
System.IO.File.Delete("SqliteTest.db");
SqliteConnection dbcon = new SqliteConnection();
// the connection string is a URL that points
// to a file. If the file does not exist, a
// file is created.
// "URI=file:some/path"
string connectionString =
"URI=file:SqliteTest.db";
if (v3)
connectionString += ",Version=3";
if (encoding != null)
connectionString += ",encoding=" + encoding;
dbcon.ConnectionString = connectionString;
dbcon.Open();
SqliteCommand dbcmd = new SqliteCommand();
dbcmd.Connection = dbcon;
dbcmd.CommandText =
"CREATE TABLE MONO_TEST ( " +
"NID INT, " +
"NDESC TEXT, " +
"NTIME DATETIME); " +
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(1,'One (unicode test: \u05D0)', '2006-01-01')";
Console.WriteLine("Create & insert modified rows = 1: " + dbcmd.ExecuteNonQuery());
dbcmd.CommandText =
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(:NID,:NDESC,:NTIME)";
dbcmd.Parameters.Add( new SqliteParameter("NID", 2) );
dbcmd.Parameters.Add( new SqliteParameter(":NDESC", "Two (unicode test: \u05D1)") );
dbcmd.Parameters.Add( new SqliteParameter(":NTIME", DateTime.Now) );
Console.WriteLine("Insert modified rows with parameters = 1, 2: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());
dbcmd.CommandText =
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(3,'Three, quoted parameter test, and next is null; :NTIME', NULL)";
Console.WriteLine("Insert with null modified rows and ID = 1, 3: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());
dbcmd.CommandText =
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(4,'Four with ANSI char: ü', NULL)";
Console.WriteLine("Insert with ANSI char ü = 1, 4: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());
dbcmd.CommandText =
"INSERT INTO MONO_TEST " +
"(NID, NDESC, NTIME) " +
"VALUES(?,?,?)";
dbcmd.Parameters.Clear();
IDbDataParameter param1 = dbcmd.CreateParameter();
param1.DbType = DbType.DateTime;
param1.Value = 5;
dbcmd.Parameters.Add(param1);
IDbDataParameter param2 = dbcmd.CreateParameter();
param2.Value = "Using unnamed parameters";
dbcmd.Parameters.Add(param2);
IDbDataParameter param3 = dbcmd.CreateParameter();
param3.DbType = DbType.DateTime;
param3.Value = DateTime.Parse("2006-05-11 11:45:00");
dbcmd.Parameters.Add(param3);
Console.WriteLine("Insert with unnamed parameters = 1, 5: " + dbcmd.ExecuteNonQuery() + " , " + dbcmd.LastInsertRowID());
dbcmd.CommandText =
"SELECT * FROM MONO_TEST";
SqliteDataReader reader;
reader = dbcmd.ExecuteReader();
Console.WriteLine("read and display data...");
while(reader.Read())
for (int i = 0; i < reader.FieldCount; i++)
Console.WriteLine(" Col {0}: {1} (type: {2}, data type: {3})",
i, reader[i] == null ? "(null)" : reader[i].ToString(), reader[i] == null ? "(null)" : reader[i].GetType().FullName, reader.GetDataTypeName(i));
dbcmd.CommandText = "SELECT NDESC FROM MONO_TEST WHERE NID=2";
Console.WriteLine("read and display a scalar = 'Two': " + dbcmd.ExecuteScalar());
dbcmd.CommandText = "SELECT count(*) FROM MONO_TEST";
Console.WriteLine("read and display a non-column scalar = 3: " + dbcmd.ExecuteScalar());
Console.WriteLine("read and display data using DataAdapter/DataSet...");
SqliteDataAdapter adapter = new SqliteDataAdapter("SELECT * FROM MONO_TEST", connectionString);
DataSet dataset = new DataSet();
adapter.Fill(dataset);
foreach(DataTable myTable in dataset.Tables){
foreach(DataRow myRow in myTable.Rows){
foreach (DataColumn myColumn in myTable.Columns){
Console.WriteLine(" " + myRow[myColumn]);
}
}
}
/*Console.WriteLine("read and display data using DataAdapter/DataTable...");
DataTable dt = new DataTable();
adapter.Fill(dt);
DataView dv = new DataView(dt);
foreach (DataRowView myRow in dv) {
foreach (DataColumn myColumn in myRow.Row.Table.Columns) {
Console.WriteLine(" " + myRow[myColumn.ColumnName]);
}
}*/
try {
dbcmd.CommandText = "SELECT NDESC INVALID SYNTAX FROM MONO_TEST WHERE NID=2";
dbcmd.ExecuteNonQuery();
Console.WriteLine("Should not reach here.");
} catch (Exception e) {
Console.WriteLine("Testing a syntax error: " + e.GetType().Name + ": " + e.Message);
}
/*try {
dbcmd.CommandText = "SELECT 0/0 FROM MONO_TEST WHERE NID=2";
Console.WriteLine("Should not reach here: " + dbcmd.ExecuteScalar());
} catch (Exception e) {
Console.WriteLine("Testing an execution error: " + e.GetType().Name + ": " + e.Message);
}*/
dataset.Dispose();
adapter.Dispose();
reader.Close();
dbcmd.Dispose();
dbcon.Close();
}
protected void CloseCommand(SqliteCommand cmd)
{
cmd.Connection.Close();
cmd.Connection.Dispose();
cmd.Dispose();
}
private void DoExpire()
{
SqliteCommand cmd = new SqliteCommand("delete from tokens where validity < datetime('now', 'localtime')");
ExecuteNonQuery(cmd, m_Connection);
cmd.Dispose();
m_LastExpire = System.Environment.TickCount;
}
public bool CheckToken(UUID principalID, string token, int lifetime)
{
if (System.Environment.TickCount - m_LastExpire > 30000)
DoExpire();
SqliteCommand cmd = new SqliteCommand("update tokens set validity = datetime('now, 'localtime', '+" + lifetime.ToString() +
" minutes') where UUID = '" + principalID.ToString() + "' and token = '" + token + "' and validity > datetime('now', 'localtime')");
if (ExecuteNonQuery(cmd, m_Connection) > 0)
{
cmd.Dispose();
return true;
}
cmd.Dispose();
return false;
}
public bool SetToken(UUID principalID, string token, int lifetime)
{
if (System.Environment.TickCount - m_LastExpire > 30000)
DoExpire();
SqliteCommand cmd = new SqliteCommand("insert into tokens (UUID, token, validity) values ('" + principalID.ToString() +
"', '" + token + "', datetime('now', 'localtime', '+" + lifetime.ToString() + " minutes'))");
if (ExecuteNonQuery(cmd, m_Connection) > 0)
{
cmd.Dispose();
return true;
}
cmd.Dispose();
return false;
}
private void SetupDb()
{
SqliteCommand command = new SqliteCommand ();
command.Connection = conn;
command.CommandText =
"CREATE TABLE songs ( " +
" id INTEGER PRIMARY KEY NOT NULL, " +
" filename STRING NOT NULL, " +
" title STRING NOT NULL, " +
" artists STRING NOT NULL, " +
" performers STRING NOT NULL, " +
" album STRING NOT NULL, " +
" tracknumber INTEGER NOT NULL, " +
" year INTEGER NOT NULL, " +
" duration INTEGER NOT NULL, " +
" mtime INTEGER NOT NULL, " +
" gain FLOAT NOT NULL " +
")";
command.ExecuteNonQuery ();
command.Dispose ();
command = new SqliteCommand ();
command.Connection = conn;
command.CommandText =
"CREATE TABLE albums ( " +
" id INTEGER PRIMARY KEY NOT NULL, " +
" name STRING NOT NULL, " +
" artists STRING NOT NULL, " +
" songs STRING NOT NULL, " +
" performers STRING NOT NULL, " +
" year INTEGER NOT NULL " +
")";
command.ExecuteNonQuery ();
command.Dispose ();
}
