/// <summary> /// Creates a new connection to the icontent.cache file. /// </summary> /// <param name="filename">The file to connect to.</param> public IGADatabaseConnector(String filename) { try { sqlite = new SqliteConnection("URI=file:" + filename + ",version=3"); sqlite.Open(); SqliteCommand query = new SqliteCommand("SELECT [appId] FROM [contentlist] LIMIT 1", sqlite); Object result = query.ExecuteScalar(); sqlite.Close(); if (result == null) { this._appID = 0; } else { this._appID = (int)result; } if (this._appID > 0) { this._appSupported = Common.AppInfos.ContainsKey(this._appID); } else { this._appSupported = false; } if (this._appSupported) { _appInfo = Common.AppInfos[this._appID]; } } catch (Exception) { throw new DatabaseConnectionFailureException(); } }
public new object ExecuteScalar(string sql, string connectionString) { using (SqliteConnection conn = new SqliteConnection(connectionString)) { SqliteCommand cmd = new SqliteCommand(sql, conn); cmd.CommandTimeout = 1200; conn.Open(); object retVal = cmd.ExecuteScalar(); conn.Close(); return retVal; } }
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(); }
/// <summary> /// Creates a new entry in the cache file. /// </summary> /// <param name="entry">The entry to create.</param> /// <returns>The new entry's contentId.</returns> public uint NewEntry(ContentEntry entry) { uint active = 1; if (!entry.Active) { active = 0; } uint activate = (uint)((TimeSpan)entry.Activate.Subtract(new DateTime(1970, 1, 1, 0, 0, 0, 0))).TotalSeconds; uint expiry = 0; if (entry.Expires) { expiry = (uint)((TimeSpan)entry.Expiry.Subtract(new DateTime(1970, 1, 1, 0, 0, 0, 0))).TotalSeconds; } String p = entry.GetPropsAsString(); SqliteCommand query = new SqliteCommand(@"INSERT INTO [content] ([active], [activate], [expire], [dayparts], [contentType], [descriptor], [size], [viewcount], [viewlimit], [displayafter], [props], [data]) VALUES (@active, @activate, @expire, @dayparts, @contentType, @descriptor, @size, @viewcount, @viewlimit, @displayafter, @props, @data); SELECT last_insert_rowid() AS contentId;", sqlite); query.Parameters.Add(new SqliteParameter("@active", (int)active)); query.Parameters.Add(new SqliteParameter("@activate", (int)activate)); query.Parameters.Add(new SqliteParameter("@expire", (int)expiry)); query.Parameters.Add(new SqliteParameter("@dayparts", (int)entry.DayParts)); query.Parameters.Add(new SqliteParameter("@contentType", (int)entry.contentType.contentType)); query.Parameters.Add(new SqliteParameter("@descriptor", entry.Descriptor)); query.Parameters.Add(new SqliteParameter("@size", (int)entry.Size)); query.Parameters.Add(new SqliteParameter("@viewcount", (int)entry.ViewCount)); query.Parameters.Add(new SqliteParameter("@viewlimit", (int)entry.ViewLimit)); query.Parameters.Add(new SqliteParameter("@displayafter", (int)entry.DisplayAfter)); query.Parameters.Add(new SqliteParameter("@props", p)); query.Parameters.Add(new SqliteParameter("@data", entry.Data)); // get the contentId back sqlite.Open(); Object cidR = query.ExecuteScalar(); uint cid = (uint)((long)cidR); // shove cid into props p = "contentId=" + cid.ToString() + "&" + p; // reset connection sqlite.Close(); sqlite.Open(); // shove in updated props query = new SqliteCommand(@"UPDATE [content] SET [props]=@props WHERE [contentId]=@cid", sqlite); query.Parameters.Add(new SqliteParameter("@props", p)); query.Parameters.Add(new SqliteParameter("@cid", (int)cid)); if (query.ExecuteNonQuery() == 0) { sqlite.Close(); throw new DatabaseUpdateFailureException(); } // reset connection sqlite.Close(); sqlite.Open(); // shove in corresponding contentlist entry for record query = new SqliteCommand(@"INSERT INTO [contentlist] ([appId], [contentType], [contentId], [userInfo], [zoneId]) VALUES (@appid, @contentType, @cid, @userinfo, @zoneid)", sqlite); query.Parameters.Add(new SqliteParameter("@appid", this._appID)); query.Parameters.Add(new SqliteParameter("@contentType", (int)entry.contentType.contentType)); query.Parameters.Add(new SqliteParameter("@cid", (int)cid)); query.Parameters.Add(new SqliteParameter("@zoneid", entry.Properties["zoneId"])); query.Parameters.Add(new SqliteParameter("@userinfo", entry.Properties["userInfo"])); if (query.ExecuteNonQuery() == 0) { sqlite.Close(); throw new DatabaseUpdateFailureException(); } sqlite.Close(); return cid; }
/// <summary> /// Exports a single image from the database. /// </summary> /// <param name="contentId">The contentId to export.</param> /// <returns>A byte[] containing the raw image or video data, or null if there was an error or there is no data.</returns> public byte[] ExportImage(uint contentId) { SqliteCommand cmd = new SqliteCommand(@"SELECT [data] FROM [content] WHERE [contentId] = @cid LIMIT 1", sqlite); cmd.Parameters.Add(new SqliteParameter("@cid", (int)contentId)); sqlite.Open(); byte[] idata = null; try { idata = (byte[])cmd.ExecuteScalar(); } catch (Exception) { } // pass sqlite.Close(); return idata; }
public void ScalarReturn() { // This should return the 1 line that got inserted in CreateTable() Test SqliteCommand cmd = new SqliteCommand("SELECT COUNT(*) FROM t1 WHERE t LIKE '%äöüß'",_conn); using(_conn) { _conn.Open(); Assert.AreEqual(1, Convert.ToInt32(cmd.ExecuteScalar())); } }
private object ExecuteScalar(string sql, IDbConnection conn, IDbTransaction tran) { sql = sql + " --dataProfilerIgnore"; SqliteCommand cmd = new SqliteCommand(sql, (SqliteConnection)conn, tran); cmd.CommandTimeout = CommandTimeout; return cmd.ExecuteScalar(); }
private object ExecuteScalar(string sql) { using (SqliteConnection conn = new SqliteConnection(connectionString)) { object retVal = null; try { sql = sql + " --dataProfilerIgnore"; SqliteCommand cmd = new SqliteCommand(sql, conn); cmd.CommandTimeout = CommandTimeout; conn.Open(); retVal = cmd.ExecuteScalar(); } finally { if (conn.State != ConnectionState.Closed) { conn.Close(); } } return retVal; } }