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