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