public static bool DatabaseObjectExists(this SqliteConnection sql, string table) { RowsRet rows = null; rows = sql.ExecuteCommand( "SELECT COUNT(*) FROM sqlite_master WHERE name=?", new object[] { table }); int m_startRow = 1; if (rows == null || rows.Rows.Count < ((m_startRow == 0) ? 2 : 1)) { return(false); } else { int count = int.Parse(rows.Rows[(m_startRow == 0) ? 1 : 0].Cols[0]); if (count >= 1) { return(true); } else { return(false); } } }
public static List <int> findIds(string table, string commandText, List <string> parameters) { List <int> ids = new List <int>(); try { OpenDB(); #if MONO var cm = detailsDB.CreateCommand(); cm.CommandText = commandText; foreach (String s in parameters) { var p = cm.CreateParameter(); p.Value = s; cm.Parameters.Add(p); } var r = cm.ExecuteReader(); while (r.Read()) { object obj = r.GetValue(r.GetOrdinal("id")); if (obj != null) { ids.Add(int.Parse((String)obj)); } } r.Close(); #else RowsRet ret = null; Object [] ob = new Object[parameters.Count]; for (int i = 0; i < parameters.Count; i++) { ob[i] = parameters[i]; } ret = sqlDetailsDB.ExecuteCommand(commandText, ob); for (int i = 0; i < ret.Rows.Count; i++) { Row row = ret.Rows[i]; ids.Add(int.Parse(row["id"])); } #endif } catch (Exception ex) { System.Diagnostics.Debug.WriteLine(ex.ToString()); } return(ids); }
private void UpdateNextIndexForTableFromDB(string tableName) { string statement = "SELECT MAX(ID) FROM " + tableName + ";"; RowsRet ret = sql.ExecuteCommand(statement); if (ret.Rows.Count == 0) { nextIndexForTable[tableName] = 1; } else { nextIndexForTable[tableName] = ret.Rows[0].IntValue("ID") + 1; } }
public List <object> LoadSimpleValues(DBTableDesc table, int owner) { string field = table.Fields[0].Name; Type type = table.Type.GetGenericArguments()[0]; RowsRet ret = sql.ExecuteCommand("SELECT " + field + " FROM " + table.Name + " WHERE OwnerID = ?;", new object[] { owner }); List <object> list = new List <object>(); foreach (Row row in ret.Rows) { list.Add(ParseObjectForType(row.Cols[0], type)); } return(list); }
public void CreateDeleteTableItemStatements(DBTableDesc basetable, int index, List <String> statementList, List <object[]> paramList, bool ignoreCurrent) { object[] idParam = new object[] { index }; if (!ignoreCurrent) { statementList.Add("DELETE FROM " + basetable.Name + " WHERE ID = ?;"); paramList.Add(idParam); } foreach (DBFieldDesc field in basetable.SubtableFields) { DBTableDesc table = field.Subtable; RowsRet ret = sql.ExecuteCommand("SELECT ID FROM " + table.Name + " WHERE OwnerID = ?", idParam); foreach (Row row in ret.Rows) { CreateDeleteTableItemStatements(table, row.IntValue("ID"), statementList, paramList); } } }
private void UpdateNextIndexForTableFromDB(string tableName) { string statement = "SELECT MAX(ID) FROM " + tableName + ";"; RowsRet ret = sql.ExecuteCommand(statement); if (ret.Rows.Count == 0) { nextIndexForTable[tableName] = 1; } else { var row = ret.Rows[0]; String text = row.Cols[0]; int index; int.TryParse(text, out index); nextIndexForTable[tableName] = index + 1; } }
private bool TableMatchesDescription(SQL_Lite sql, DBTableDesc table) #endif { RowsRet ret = sql.ExecuteCommand("PRAGMA table_info(" + table.Name + ");"); Dictionary <string, DBFieldDesc> fieldInfo = new Dictionary <string, DBFieldDesc>(); foreach (var field in table.Fields) { fieldInfo.Add(field.Name, field); } bool tableMatches = true; foreach (Row row in ret.Rows) { string name = row["name"]; if (fieldInfo.ContainsKey(row["name"])) { fieldInfo.Remove(row["name"]); } else { if (name != "ID" && name != "OwnerID") { tableMatches = false; break; } } } if (fieldInfo.Count > 0) { tableMatches = false; } return(tableMatches); }
private static void CopyTable(SqliteConnection sql, SqliteConnection sql2, DBTableDesc table) #endif { #if ANDROID Android.Util.Log.Error("DBLoader", "CopyTable"); #endif String str = CreateTableStatementForDesc(table); sql2.ExecuteCommand(str); #if ANDROID Android.Util.Log.Error("DBLoader", "Created"); #endif if (sql.DatabaseObjectExists(table.Name)) { RowsRet data = sql.ExecuteCommand("Select * from " + table.Name); #if ANDROID Android.Util.Log.Error("DBLoader", "Select from table.name"); #endif List <string> validOldFields = new List <string>(); List <DBFieldDesc> invalidNewFields = new List <DBFieldDesc>(); foreach (DBFieldDesc desc in table.Fields) { if (data.HasColumn(desc.Name)) { validOldFields.Add(desc.Name); } else { invalidNewFields.Add(desc); } } StringBuilder commandBuilder = new StringBuilder(); commandBuilder.Append("Insert into " + table.Name + " (ID"); int count = validOldFields.Count + 1;; if (!table.Primary) { commandBuilder.Append(", OwnerID"); count++; } StringBuilder fieldBuilder = new StringBuilder(); StringBuilder valueBuilder = new StringBuilder(); foreach (string strField in validOldFields) { fieldBuilder.Append(", " + strField); } for (int i = 1; i < count; i++) { valueBuilder.Append(", ?"); } foreach (DBFieldDesc desc in invalidNewFields) { if (desc.Nullable == false) { if (desc.Type == "INTEGER") { fieldBuilder.Append(", " + desc.Name); valueBuilder.Append(", 0"); } } } fieldBuilder.Append(") VALUES ( ?"); valueBuilder.Append(");"); commandBuilder.Append(fieldBuilder); commandBuilder.Append(valueBuilder); string command = commandBuilder.ToString(); foreach (Row row in data.Rows) { List <object> values = new List <object>(); values.Add(row["ID"]); if (!table.Primary) { values.Add(row["OwnerID"]); } foreach (string strField in validOldFields) { values.Add(row[strField]); } object[] objParams = values.ToArray(); sql2.ExecuteCommand(command, objParams); #if ANDROID Android.Util.Log.Error("DBLoader", "row command"); #endif } } }
public DBLoader(String filename) { Type type = typeof(T); #if ANDROID string path = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData); #else string path = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData); #endif path = Path.Combine(path, "Combat Manager"); if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } try { string fullfilename = Path.Combine(path, filename); #if !MONO sql = new SQL_Lite(); sql.SkipHeaderRow = true; sql.Open(fullfilename); string backtext = Assembly.GetExecutingAssembly().GetName().Version.ToString(); string backname = fullfilename + backtext + ".db"; #else #if ANDROID Android.Util.Log.Error("DBLoader", "fullfilename " + fullfilename); #endif sql = new SqliteConnection("DbLinqProvider=Sqlite;Data Source=" + fullfilename); sql.Open(); #endif #if !MONO //make a backup if (File.Exists(fullfilename) && !(File.Exists(backname))) { try { File.Copy(fullfilename, backname); } catch (IOException ex) { System.Diagnostics.Debug.WriteLine(ex.ToString()); } } #endif bool needsCopy = false; #if !MONO try { sql.ExecuteCommand("SELECT name FROM sqlite_master"); } catch (SQL_Lite_Exception ex) { System.Diagnostics.Debug.WriteLine(ex.ToString()); sql.Dispose(); sql = null; string errorname = fullfilename + ".error.db"; if (File.Exists(errorname)) { File.Delete(errorname); } File.Move(fullfilename, errorname); sql = new SQL_Lite(); sql.SkipHeaderRow = true; sql.Open(fullfilename); } #endif List <DBTableDesc> tables = GetTablesForType(type); foreach (DBTableDesc desc in tables) { RowsRet ret = sql.ExecuteCommand("SELECT name FROM sqlite_master WHERE type='table' AND name=?", new object[] { desc.Name }); if (ret.Rows.Count == 0) { String str = CreateTableStatementForDesc(desc); sql.ExecuteCommand(str); } else { if (!TableMatchesDescription(sql, desc)) { needsCopy = true; break; } } } if (needsCopy) { #if ANDROID Android.Util.Log.Error("DBLoader", "DBL needs copy"); #endif string newfile = fullfilename + ".tmp"; if (File.Exists(newfile)) { #if ANDROID Android.Util.Log.Error("DBLoader", "DBL new file exists"); #endif File.Delete(newfile); #if ANDROID Android.Util.Log.Error("DBLoader", "DBL new file delete"); #endif } #if !MONO SQL_Lite sql2 = new SQL_Lite(); sql2.SkipHeaderRow = true; sql2.Open(newfile); #else LogError("DBLoader", "NewFile " + newfile); SqliteConnection sql2 = new SqliteConnection("DbLinqProvider=Sqlite;Data Source=" + newfile); sql2.Open(); #endif foreach (DBTableDesc table in tables) { CopyTable(sql, sql2, table); } sql.Dispose(); sql2.Dispose(); File.Delete(fullfilename); File.Move(newfile, fullfilename); #if !MONO sql = new SQL_Lite(); sql.SkipHeaderRow = true; sql.Open(fullfilename); #else sql = new SqliteConnection("DbLinqProvider=Sqlite;Data Source=" + fullfilename); sql.Open(); #endif } LoadTableNextIndexes(tables); LoadDBItems(); } #if !MONO catch (SQL_Lite_Exception ex) { System.Diagnostics.Debug.WriteLine(ex); } #else finally { } #endif }
public Row(RowsRet ResultSet) { this._ResultSet = ResultSet; }
private static void CopyTable(SqliteConnection sql, SqliteConnection sql2, DBTableDesc table) #endif { String str = CreateTableStatementForDesc(table); sql2.ExecuteCommand(str); if (sql.DatabaseObjectExists(table.Name)) { RowsRet data = sql.ExecuteCommand("Select * from " + table.Name); List <string> validOldFields = new List <string>(); foreach (DBFieldDesc desc in table.Fields) { if (data.HasColumn(desc.Name)) { validOldFields.Add(desc.Name); } } StringBuilder commandBuilder = new StringBuilder(); commandBuilder.Append("Insert into " + table.Name + " (ID"); int count = validOldFields.Count + 1;; if (!table.Primary) { commandBuilder.Append(", OwnerID"); count++; } foreach (string strField in validOldFields) { commandBuilder.Append(", " + strField); } commandBuilder.Append(") VALUES ( ?"); for (int i = 1; i < count; i++) { commandBuilder.Append(", ?"); } commandBuilder.Append(");"); string command = commandBuilder.ToString(); foreach (Row row in data.Rows) { List <object> values = new List <object>(); values.Add(row["ID"]); if (!table.Primary) { values.Add(row["OwnerID"]); } foreach (string strField in validOldFields) { values.Add(row[strField]); } object[] objParams = values.ToArray(); sql2.ExecuteCommand(command, objParams); } } }
public static Dictionary <string, string> LoadDetails(string ID, string table, List <string> fields) { var dict = new Dictionary <string, string>(); string selectFields = ""; bool first = true; foreach (string s in fields) { if (!first) { selectFields += ", "; } first = false; selectFields += s; } string commandText = "Select " + selectFields + " from " + table + " where ID=?"; try { OpenDB(); #if MONO var cm = detailsDB.CreateCommand(); cm.CommandText = commandText; var p = cm.CreateParameter(); p.Value = ID; cm.Parameters.Add(p); var r = cm.ExecuteReader(); r.Read(); foreach (string s in fields) { object obj = r.GetValue(r.GetOrdinal(s)); if (obj != null) { dict[s] = obj.ToString(); } else { dict[s] = null; } } r.Close(); #else RowsRet ret = null; ret = sqlDetailsDB.ExecuteCommand(commandText, new object[] { ID }); if (ret == null || ret.Count() == 0) { return(dict); } foreach (string s in fields) { dict[s] = ret.Rows[0][s]; } #endif } catch (Exception ex) { System.Diagnostics.Debug.WriteLine(ex.ToString()); } return(dict); }