/// <summary> /// Searches the row. /// </summary> /// <param name="table">The table.</param> /// <param name="column">The column.</param> /// <param name="value">The value.</param> /// <returns></returns> internal int?SearchRow(Table table, string column, object value) { if (CACHE_ENABLED && RowCache.ContainsKey(table)) { var res = from r in RowCache[table] where r.Value.ContainsKey(column) && r.Value[column] == value select r.Key; if (res.ToList().Count > 0) { return(res.ToList()[0]); } } SqlCeCommand cmd = DbConnection.CreateCommand(); cmd.CommandText = string.Format("SELECT id FROM {0} WHERE {1}=@value", table, column); cmd.Parameters.Add("value", value); while (commandsQueue.Count > 0) { Thread.Sleep(10); } return(SqlCE.ExecuteScalar <int>(cmd)); }
/// <summary> /// Adds the row to the specified table. /// </summary> /// <param name="table">The table.</param> /// <param name="values">The values (AT LEAST ONE, even if it is "NULL").</param> /// <returns></returns> internal int AddRow(Table table, Dictionary <string, object> values) { SqlCeCommand cmd = DbConnection.CreateCommand(); string col = string.Empty, val = string.Empty; foreach (KeyValuePair <string, object> pair in values) { col += pair.Key + ", "; val += "@" + pair.Key + ", "; cmd.Parameters.Add(pair.Key, pair.Value); } col = col.Trim(new char[] { ',', ' ' }); val = val.Trim(new char[] { ',', ' ' }); cmd.CommandText = string.Format("INSERT INTO {0} ({1}) VALUES ({2}); SELECT @@IDENTITY", table, col, val); while (commandsQueue.Count > 0) { Thread.Sleep(10); } int id = SqlCE.ExecuteScalar <int>(cmd).Value; //if (CACHE_ENABLED) //{ //if (!RowCache.ContainsKey(table)) // RowCache[table] = new Dictionary<int, Dictionary<string, object>>(); //RowCache[table][id] = values; //} return(id); }
/// <summary> /// Gets the field. /// </summary> /// <param name="table">The table.</param> /// <param name="id1Column">The id1 column.</param> /// <param name="id1">The id1.</param> /// <param name="id2Column">The id2 column.</param> /// <param name="id2">The id2.</param> /// <param name="fieldColumn">The field column.</param> /// <returns></returns> internal object GetField(string table, string id1Column, int id1, string id2Column, int id2, string fieldColumn) { if (CACHE_ENABLED) { string key = GetFieldKey(table, id1Column, id1, id2Column, id2, fieldColumn); if (FieldCache.ContainsKey(key)) { return(FieldCache[key]); } SqlCeCommand cmd = DbConnection.CreateCommand(); cmd.CommandText = string.Format("SELECT {2}, {3}, {0} FROM {1}", fieldColumn, table, id1Column, id2Column); while (commandsQueue.Count > 0) { Thread.Sleep(10); } SqlCeDataReader reader = SqlCE.ExecuteReader(cmd); while (reader.Read()) { FieldCache[GetFieldKey(table, id1Column, Convert.ToInt32(reader[id1Column]), id2Column, Convert.ToInt32(reader[id2Column]), fieldColumn)] = reader[fieldColumn]; } return(FieldCache[key]); } else { SqlCeCommand cmd = DbConnection.CreateCommand(); cmd.CommandText = string.Format("SELECT {0} FROM {1} WHERE {2}=@id1 AND {3}=@id2", fieldColumn, table, id1Column, id2Column); cmd.Parameters.Add("id1", id1); cmd.Parameters.Add("id2", id2); while (commandsQueue.Count > 0) { Thread.Sleep(10); } return(SqlCE.ExecuteScalar(cmd)); } }