/// <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>
/// 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>
/// Creates the data source with the specified filename (call this for file data sources).
/// </summary>
/// <param name="filename">The filename.</param>
/// <returns></returns>
public IMovieDataSourcePlugin Create(string filename)
{
if (!File.Exists(filename))
{
SqlCE.CreateNewDB(filename, true);
}
return(new SqlCeMovieDataSource(filename));
}
/// <summary>
/// Gets the row.
/// </summary>
/// <param name="table">The table.</param>
/// <param name="id">The id.</param>
/// <returns></returns>
internal Dictionary <string, object> GetRow(Table table, int id)
{
if (CACHE_ENABLED && RowCache.ContainsKey(table) && RowCache[table].ContainsKey(id))
{
return(RowCache[table][id]);
}
while (commandsQueue.Count > 0)
{
Thread.Sleep(10);
}
SqlCeDataReader reader;
if (CACHE_ENABLED && !RowCache.ContainsKey(table))
{
SqlCeCommand cmd = DbConnection.CreateCommand();
cmd.CommandText = string.Format("SELECT * FROM {0}", table);
reader = SqlCE.ExecuteReader(cmd);
}
else
{
SqlCeCommand cmd = DbConnection.CreateCommand();
cmd.CommandText = string.Format("SELECT * FROM {0} WHERE id=@id", table);
cmd.Parameters.Add("id", id);
reader = SqlCE.ExecuteReader(cmd);
}
while (reader.Read())
{
Dictionary <string, object> columns = new Dictionary <string, object>();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i), reader[i]);
}
if (!CACHE_ENABLED)
{
return(columns);
}
if (!RowCache.ContainsKey(table))
{
RowCache[table] = new Dictionary <int, Dictionary <string, object> >();
}
RowCache[table][Convert.ToInt32(columns["id"])] = columns;
}
reader.Close();
return(RowCache[table][id]);
}
/// <summary>
/// Loads the users.
/// </summary>
private void LoadUsers()
{
SqlCeCommand cmd = DbConnection.CreateCommand();
cmd.CommandText = "SELECT id FROM UserProfiles";
SqlCeDataReader reader = SqlCE.ExecuteReader(cmd);
List <int> ids = new List <int>();
while (reader.Read())
{
ids.Add(Convert.ToInt32(reader["id"]));
}
reader.Close();
ids.ForEach(i => Users.Add(new SqlCeUserProfile(i, this)));
}
/// <summary>
/// Commands the executer.
/// </summary>
private void CommandExecuter()
{
while (true)
{
if (commandsQueue.Count <= 0)
{
Thread.CurrentThread.IsBackground = true;
}
while (commandsQueue.Count <= 0)
{
Thread.Sleep(10);
}
Thread.CurrentThread.IsBackground = false;
SqlCE.ExecuteNonQuery(commandsQueue.Dequeue());
}
}
/// <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));
}
}
/// <summary>
/// Initializes a new instance of the <see cref="SqlCeMovieDataSource"/> class.
/// </summary>
/// <param name="filename">The filename.</param>
public SqlCeMovieDataSource(string filename)
{
RealFilename = string.Empty;
bool exclusive = true;
if (Path.IsPathRooted(filename))
{
try
{
DriveInfo driveInfo = new DriveInfo(filename[0].ToString());
exclusive = (driveInfo.DriveType == DriveType.Fixed || driveInfo.DriveType == DriveType.Removable) ? false : true;
}
catch { }
}
if (exclusive)
{
RealFilename = filename;
string tempFilename = Path.Combine(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), "MovieCollection"), Path.GetFileName(filename));
if (!Directory.Exists(Path.GetDirectoryName(tempFilename)))
{
Directory.CreateDirectory(Path.GetDirectoryName(tempFilename));
}
File.Copy(filename, tempFilename);
File.Move(filename, filename + ".open");
filename = tempFilename;
}
Filename = filename;
DbConnection = new SqlCeConnection(SqlCE.GetConnectionString(filename));
DbConnection.Open();
Movies = new ObservableCollection <IMovie>();
LoadList();
Movies.CollectionChanged += new System.Collections.Specialized.NotifyCollectionChangedEventHandler(Movies_CollectionChanged);
Users = new ObservableCollection <IUserProfile>();
LoadUsers();
Users.CollectionChanged += new NotifyCollectionChangedEventHandler(Users_CollectionChanged);
}
/// <summary>
/// Executes the command async.
/// </summary>
/// <param name="command">The command.</param>
private void ExecuteCommandAsync(SqlCeCommand command)
{
if (CACHE_ENABLED)
{
if (asyncThread == null)
{
asyncThread = new Thread(new ThreadStart(CommandExecuter));
asyncThread.Priority = ThreadPriority.Lowest;
asyncThread.Name = "SqlCE Command Execution Thread";
asyncThread.CurrentCulture = Thread.CurrentThread.CurrentCulture;
asyncThread.CurrentUICulture = Thread.CurrentThread.CurrentUICulture;
asyncThread.Start();
}
commandsQueue.Enqueue(command);
asyncThread.IsBackground = false;
}
else
{
SqlCE.ExecuteNonQuery(command);
}
}
/// <summary>
/// Gets the references of table 2 in the specified row of table 1.
/// </summary>
/// <param name="tabel1">The tabel1.</param>
/// <param name="table2">The table2.</param>
/// <param name="table1Id">The table1 id.</param>
/// <returns></returns>
internal List <int> GetReferences(Table tabel1, Table table2, int table1Id)
{
if (CACHE_ENABLED)
{
if (!ReferencesCache.ContainsKey(tabel1) || !ReferencesCache[tabel1].ContainsKey(table2))
{
if (!ReferencesCache.ContainsKey(tabel1))
{
ReferencesCache[tabel1] = new Dictionary <Table, List <KeyValuePair <int, int> > >();
}
if (!ReferencesCache[tabel1].ContainsKey(table2))
{
ReferencesCache[tabel1][table2] = new List <KeyValuePair <int, int> >();
}
SqlCeCommand cmd = DbConnection.CreateCommand();
cmd.CommandText = string.Format("SELECT {0}_id, {1}_id FROM {2}",
tabel1.ToString().ToLower(), table2.ToString().ToLower(), tabel1 + "_" + table2);
while (commandsQueue.Count > 0)
{
Thread.Sleep(10);
}
SqlCeDataReader reader = SqlCE.ExecuteReader(cmd);
while (reader.Read())
{
ReferencesCache[tabel1][table2].Add(new KeyValuePair <int, int>(Convert.ToInt32(reader[0]), Convert.ToInt32(reader[1])));
}
reader.Close();
}
var refs = from reference in ReferencesCache[tabel1][table2]
where reference.Key == table1Id
select reference.Value;
return(refs.ToList());
}
else
{
SqlCeCommand cmd = DbConnection.CreateCommand();
cmd.CommandText = string.Format("SELECT {1}_id FROM {2} WHERE {0}_id=@id",
tabel1.ToString().ToLower(), table2.ToString().ToLower(), tabel1 + "_" + table2);
cmd.Parameters.Add("id", table1Id);
while (commandsQueue.Count > 0)
{
Thread.Sleep(10);
}
SqlCeDataReader reader = SqlCE.ExecuteReader(cmd);
List <int> refs = new List <int>();
while (reader.Read())
{
refs.Add(Convert.ToInt32(reader[0]));
}
reader.Close();
return(refs);
}
}
