/// <summary> /// <list type="bullet"> /// <item>Initialises User Interface</item> /// <item>Loads and initialises a new SQLite connection and maintains it.</item> /// <item>use default URI if connect string string is empty.</item> /// </list> /// </summary> /// <param name="connect">connect string</param> override public void Initialise(string connect) { // default to something sensible if (connect == "") connect = "URI=file:userprofiles.db,version=3"; SqliteConnection conn = new SqliteConnection(connect); // This sucks, but It doesn't seem to work with the dataset Syncing :P g_conn = conn; g_conn.Open(); Assembly assem = GetType().Assembly; Migration m = new Migration(g_conn, assem, "UserStore"); m.Update(); ds = new DataSet(); da = new SqliteDataAdapter(new SqliteCommand(userSelect, conn)); dua = new SqliteDataAdapter(new SqliteCommand(userAgentSelect, conn)); daf = new SqliteDataAdapter(new SqliteCommand(userFriendsSelect, conn)); daa = new SqliteDataAdapter(new SqliteCommand(AvatarAppearanceSelect, conn)); //if (daa == null) m_log.Info("[SQLiteUserData]: daa = null"); lock (ds) { ds.Tables.Add(createUsersTable()); ds.Tables.Add(createUserAgentsTable()); ds.Tables.Add(createUserFriendsTable()); ds.Tables.Add(createAvatarAppearanceTable()); setupUserCommands(da, conn); da.Fill(ds.Tables["users"]); setupAgentCommands(dua, conn); dua.Fill(ds.Tables["useragents"]); setupUserFriendsCommands(daf, conn); daf.Fill(ds.Tables["userfriends"]); setupAvatarAppearanceCommands(daa, conn); daa.Fill(ds.Tables["avatarappearance"]); } return; }
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."); }
private DataTable ExecuteDataTable(string sql) { using (SqliteConnection conn = new SqliteConnection(connectionString)) { sql = sql + " --dataProfilerIgnore"; SqliteCommand cmd = new SqliteCommand(sql, conn); cmd.CommandTimeout = 1200; conn.Open(); SqliteDataAdapter da = new SqliteDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); return ds.Tables[0]; } }
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(); }
// Temporary attribute while this is experimental /*********************************************************************** * * Public Interface Functions * **********************************************************************/ /// <summary> /// See IRegionDataStore /// <list type="bullet"> /// <item>Initialises RegionData Interface</item> /// <item>Loads and initialises a new SQLite connection and maintains it.</item> /// </list> /// </summary> /// <param name="connectionString">the connection string</param> public void Initialise(string connectionString) { m_connectionString = connectionString; ds = new DataSet(); m_log.Info("[REGION DB]: Sqlite - connecting: " + connectionString); m_conn = new SqliteConnection(m_connectionString); m_conn.Open(); SqliteCommand primSelectCmd = new SqliteCommand(primSelect, m_conn); primDa = new SqliteDataAdapter(primSelectCmd); // SqliteCommandBuilder primCb = new SqliteCommandBuilder(primDa); SqliteCommand shapeSelectCmd = new SqliteCommand(shapeSelect, m_conn); shapeDa = new SqliteDataAdapter(shapeSelectCmd); // SqliteCommandBuilder shapeCb = new SqliteCommandBuilder(shapeDa); SqliteCommand itemsSelectCmd = new SqliteCommand(itemsSelect, m_conn); itemsDa = new SqliteDataAdapter(itemsSelectCmd); SqliteCommand terrainSelectCmd = new SqliteCommand(terrainSelect, m_conn); terrainDa = new SqliteDataAdapter(terrainSelectCmd); SqliteCommand landSelectCmd = new SqliteCommand(landSelect, m_conn); landDa = new SqliteDataAdapter(landSelectCmd); SqliteCommand landAccessListSelectCmd = new SqliteCommand(landAccessListSelect, m_conn); landAccessListDa = new SqliteDataAdapter(landAccessListSelectCmd); SqliteCommand regionSettingsSelectCmd = new SqliteCommand(regionSettingsSelect, m_conn); regionSettingsDa = new SqliteDataAdapter(regionSettingsSelectCmd); // This actually does the roll forward assembly stuff Assembly assem = GetType().Assembly; Migration m = new Migration(m_conn, assem, "RegionStore"); m.Update(); lock (ds) { ds.Tables.Add(createPrimTable()); setupPrimCommands(primDa, m_conn); primDa.Fill(ds.Tables["prims"]); ds.Tables.Add(createShapeTable()); setupShapeCommands(shapeDa, m_conn); ds.Tables.Add(createItemsTable()); setupItemsCommands(itemsDa, m_conn); itemsDa.Fill(ds.Tables["primitems"]); ds.Tables.Add(createTerrainTable()); setupTerrainCommands(terrainDa, m_conn); ds.Tables.Add(createLandTable()); setupLandCommands(landDa, m_conn); ds.Tables.Add(createLandAccessListTable()); setupLandAccessCommands(landAccessListDa, m_conn); ds.Tables.Add(createRegionSettingsTable()); setupRegionSettingsCommands(regionSettingsDa, m_conn); // WORKAROUND: This is a work around for sqlite on // windows, which gets really unhappy with blob columns // that have no sample data in them. At some point we // need to actually find a proper way to handle this. try { shapeDa.Fill(ds.Tables["primshapes"]); } catch (Exception) { m_log.Info("[REGION DB]: Caught fill error on primshapes table"); } try { terrainDa.Fill(ds.Tables["terrain"]); } catch (Exception) { m_log.Info("[REGION DB]: Caught fill error on terrain table"); } try { landDa.Fill(ds.Tables["land"]); } catch (Exception) { m_log.Info("[REGION DB]: Caught fill error on land table"); } try { landAccessListDa.Fill(ds.Tables["landaccesslist"]); } catch (Exception) { m_log.Info("[REGION DB]: Caught fill error on landaccesslist table"); } try { regionSettingsDa.Fill(ds.Tables["regionsettings"]); } catch (Exception) { m_log.Info("[REGION DB]: Caught fill error on regionsettings table"); } return; } }
/// <summary> /// <list type="bullet"> /// <item>Initialises Inventory interface</item> /// <item>Loads and initialises a new SQLite connection and maintains it.</item> /// <item>use default URI if connect string string is empty.</item> /// </list> /// </summary> /// <param name="dbconnect">connect string</param> public void Initialise(string dbconnect) { if (!m_Initialized) { m_Initialized = true; if (dbconnect == string.Empty) { dbconnect = "URI=file:inventoryStore.db,version=3"; } m_log.Info("[INVENTORY DB]: Sqlite - connecting: " + dbconnect); conn = new SqliteConnection(dbconnect); conn.Open(); Assembly assem = GetType().Assembly; Migration m = new Migration(conn, assem, "InventoryStore"); m.Update(); SqliteCommand itemsSelectCmd = new SqliteCommand(invItemsSelect, conn); invItemsDa = new SqliteDataAdapter(itemsSelectCmd); // SqliteCommandBuilder primCb = new SqliteCommandBuilder(primDa); SqliteCommand foldersSelectCmd = new SqliteCommand(invFoldersSelect, conn); invFoldersDa = new SqliteDataAdapter(foldersSelectCmd); ds = new DataSet(); ds.Tables.Add(createInventoryFoldersTable()); invFoldersDa.Fill(ds.Tables["inventoryfolders"]); setupFoldersCommands(invFoldersDa, conn); m_log.Info("[INVENTORY DB]: Populated Inventory Folders Definitions"); ds.Tables.Add(createInventoryItemsTable()); invItemsDa.Fill(ds.Tables["inventoryitems"]); setupItemsCommands(invItemsDa, conn); m_log.Info("[INVENTORY DB]: Populated Inventory Items Definitions"); ds.AcceptChanges(); } }