/// <summary>
        /// Add Folder metadata into datatabse
        /// Add is atomic action
        /// </summary>
        /// <param name="folders"></param>
        /// <returns></returns>
        public bool Add(IList<FolderMetadataItem> folders)
        {
            var db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME),false);
            using (SqliteConnection con = db.NewSQLiteConnection())
            {
                if (con == null)
                    throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME)));

                var trasaction = (SqliteTransaction)con.BeginTransaction();
                try
                {
                    string cmdText = string.Format("INSERT INTO {0}( {1},{2},{3})VALUES (@source_id , @relative_path, @isEmpty)", Configuration.TLB_FOLDERMETADATA, Configuration.COL_SOURCE_ID, Configuration.COL_FOLDER_RELATIVE_PATH, Configuration.COL_IS_FOLDER_EMPTY);
                    foreach (FolderMetadataItem item in folders)
                    {
                        var paramList = new SqliteParameterCollection
                                            {
                                                new SqliteParameter("@source_id", DbType.String) {Value = item.SourceId},
                                                new SqliteParameter("@relative_path", DbType.String)
                                                    {Value = item.RelativePath},
                                                new SqliteParameter("@isEmpty", DbType.Int32){Value = item.IsEmpty}
                                            };
                        db.ExecuteNonQuery(cmdText, paramList);
                    }
                    trasaction.Commit();
                    return true;
                }
                catch (Exception)
                {
                    trasaction.Rollback();
                    return false;
                }
            }
        }
        private static bool InsertCreateAction(CreateAction createAction, SqliteConnection con)
        {
            using (SqliteCommand cmd = con.CreateCommand ())
            {
                cmd.CommandText = "INSERT INTO " + Configuration.TBL_ACTION +
                                 " ( " + Configuration.COL_CHANGE_IN + "," +
                                 Configuration.COL_ACTION_TYPE + "," +
                                 Configuration.COL_NEW_RELATIVE_PATH + "," +
                                 Configuration.COL_NEW_HASH + ") VALUES (@changeIn, @action, @newPath, @newHash)";

                SqliteParameterCollection paramList = new SqliteParameterCollection();
                cmd.Parameters.Add( new SqliteParameter("@changeIn", System.Data.DbType.String) { Value = createAction.SourceID });
                cmd.Parameters.Add(new SqliteParameter("@action", System.Data.DbType.Int32) { Value = createAction.ChangeType });
                cmd.Parameters.Add(new SqliteParameter("@newPath", System.Data.DbType.String) { Value = createAction.RelativeFilePath });
                cmd.Parameters.Add(new SqliteParameter("@newHash", System.Data.DbType.String) { Value = createAction.FileHash });

                cmd.ExecuteNonQuery();
                return true;
            }
        }
        public override IList<SyncAction> Load(string sourceID, SourceOption option)
        {
            string opt = (option == SourceOption.SOURCE_ID_NOT_EQUALS) ? " <> " : " = ";
            IList<SyncAction> actions = new List<SyncAction>();

            SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME),false);
            using (SqliteConnection con = db.NewSQLiteConnection ())
            {
                if (con == null)
                    throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME)));

                string cmdText = "SELECT * FROM " + Configuration.TBL_ACTION +
                                 " WHERE " + Configuration.COL_CHANGE_IN + opt + " @sourceId";

                SqliteParameterCollection paramList = new SqliteParameterCollection();
                paramList.Add(new SqliteParameter("@sourceId", System.Data.DbType.String) { Value = sourceID });

                db.ExecuteReader(cmdText, paramList, reader =>
                    {
                        ChangeType actionType = (ChangeType)reader[Configuration.COL_ACTION_TYPE];

                        if (actionType == ChangeType.DELETED)
                        {
                            DeleteAction delAction = new DeleteAction(
                                    (int)reader[Configuration.COL_ACTION_ID],
                                    (string)reader[Configuration.COL_CHANGE_IN],
                                    (string)reader[Configuration.COL_OLD_RELATIVE_PATH], (string)reader[Configuration.COL_OLD_HASH]);
                            actions.Add(delAction);
                        }
                        else if (actionType == ChangeType.NEWLY_CREATED)
                        {
                            CreateAction createAction = new CreateAction(
                                (int)reader[Configuration.COL_ACTION_ID],
                                (string)reader[Configuration.COL_CHANGE_IN],
                                (string)reader[Configuration.COL_NEW_RELATIVE_PATH], (string)reader[Configuration.COL_NEW_HASH]);
                            actions.Add(createAction);
                        }
                        else if (actionType == ChangeType.RENAMED)
                        {
                            RenameAction renameAction = new RenameAction(
                                (int)reader[Configuration.COL_ACTION_ID],
                                (string)reader[Configuration.COL_CHANGE_IN],
                                (string)reader[Configuration.COL_NEW_RELATIVE_PATH],
                                (string)reader[Configuration.COL_OLD_RELATIVE_PATH],
                                (string)reader[Configuration.COL_OLD_HASH]);
                            actions.Add(renameAction);
                        }
                    }
                );

            }
            return actions;
        }
        public override bool Delete(string sourceID, SourceOption option)
        {
            string opt = (option == SourceOption.SOURCE_ID_NOT_EQUALS ) ? " <> " : " = ";

            SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME),false);
            using (SqliteConnection con = db.NewSQLiteConnection ())
            {
                if (con == null)
                    throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME)));

                string cmdText = "DELETE FROM " + Configuration.TBL_ACTION +
                                 " WHERE " + Configuration.COL_CHANGE_IN + " " + opt + " @id";

                SqliteParameterCollection paramList = new SqliteParameterCollection();
                paramList.Add(new SqliteParameter("@id", System.Data.DbType.Int32) { Value = sourceID });

                db.ExecuteNonQuery(cmdText, paramList);
            }

            return true;
        }
        public override bool Delete(IList<SyncAction> actions)
        {
            SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME),false);
            using (SqliteConnection con = db.NewSQLiteConnection ())
            {
                if (con == null)
                    throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME)));

                string cmdText = "DELETE FROM " + Configuration.TBL_ACTION +
                                 " WHERE " + Configuration.COL_ACTION_ID + " = @id";

                SqliteParameterCollection paramList = new SqliteParameterCollection();

                foreach (SyncAction action in actions)
                {
                    paramList.Clear();
                    paramList.Add(new SqliteParameter("@id", DbType.Int32) { Value = action.ActionId });
                    db.ExecuteNonQuery(cmdText, paramList);
                }
            }
            return true;
        }
        public bool Delete(IList<FolderMetadataItem> items)
        {
            // All deletions are atomic
            const string cmdText = "DELETE FROM " + Configuration.TLB_FOLDERMETADATA +
                                   " WHERE " + Configuration.COL_SOURCE_ID + " = @sourceId AND " +
                                   Configuration.COL_RELATIVE_PATH + " = @path";

            var dbAccess = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME),false);

            using (SqliteConnection con = dbAccess.NewSQLiteConnection())
            {
                if (con == null)
                    throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME)));

                var transaction = (SqliteTransaction)con.BeginTransaction();
                try
                {
                    foreach (FolderMetadataItem item in items)
                    {
                        var paramList = new SqliteParameterCollection
                                            {
                                                new SqliteParameter("@sourceId", DbType.String) {Value = item.SourceId},
                                                new SqliteParameter("@path", DbType.String) {Value = item.RelativePath}
                                            };
                        dbAccess.ExecuteNonQuery(cmdText, paramList);
                    }
                    transaction.Commit();
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    return false;
                }
            }
            return true;
        }
        public bool SyncJobExists(string jobName, string id)
        {
            SQLiteAccess db = new SQLiteAccess(Path.Combine (this.StoragePath, Configuration.DATABASE_NAME ),false);
            using (SqliteConnection con = db.NewSQLiteConnection ())
            {
                if (con == null)
                    throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME)));

                // TODO: Change sql to SELECT COUNT(*)?
                string cmdText = "SELECT * FROM " + SYNCJOB_TABLE + " WHERE "
                                 + COL_SYNCJOB_NAME + " = @profileName AND " + COL_SYNCJOB_ID + " <> @id";

                SqliteParameterCollection paramList = new SqliteParameterCollection();
                paramList.Add(new SqliteParameter("@profileName", System.Data.DbType.String) { Value = jobName });
                paramList.Add(new SqliteParameter("@id", System.Data.DbType.String) { Value =  id});

                bool found = false;
                db.ExecuteReader(cmdText, paramList, reader =>
                    { found = true; return; }
                );

                return found;
            }
        }
        public override bool Delete(SyncJob job)
        {
            SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, DATABASE_NAME),false);
            using (SqliteConnection con = db.NewSQLiteConnection ())
            {
                if (con == null)
                    throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, DATABASE_NAME)));

                SqliteParameterCollection paramList = new SqliteParameterCollection();

                string cmdText = "DELETE FROM " + SyncSource.DATASOURCE_INFO_TABLE +
                                 " WHERE " + SyncSource.SOURCE_ID + " = @sid;";

                paramList.Add(new SqliteParameter("@sid", System.Data.DbType.String) { Value = job.SyncSource.ID });

                cmdText += "DELETE FROM " + SYNCJOB_TABLE +
                           " WHERE " + COL_SYNCJOB_ID + " = @pid";

                paramList.Add(new SqliteParameter("@pid", System.Data.DbType.String) { Value = job.ID });

                db.ExecuteNonQuery(cmdText, paramList);
            }

            return true;
        }
Exemple #9
0
 /// <summary>
 /// Executes the query and returns the first column of the first row in the result set returned by the query.
 /// All other columns and rows are ignored.
 /// </summary>
 /// <param name="cmdText">SQL query string.</param>
 /// /// <param name="paramsList">Parameters list.</param>
 /// <returns>The first column of the first row in the result set.</returns>
 public string ExecuteScalar(string cmdText, SqliteParameterCollection paramsList)
 {
     string retStr = null;
     using (SqliteCommand cmd = GetCommand(cmdText, paramsList))
     {
         retStr = cmd.ExecuteScalar().ToString();
     }
     return retStr;
 }
Exemple #10
0
 /// <summary>
 /// Executes data reader of command object with specified SQL query
 /// </summary>
 /// <param name="cmdText">SQL query text.</param>
 /// <param name="paramsList">Parameters list.</param>
 /// <param name="callback">Callback method while moving through the records.</param>
 /// <returns></returns>
 public void ExecuteReader(string cmdText, SqliteParameterCollection paramsList, DataReaderCallbackDelegate callback)
 {
     using (SqliteCommand cmd = GetCommand(cmdText, paramsList))
     {
         using (SqliteDataReader reader = cmd.ExecuteReader())
         {
             while (reader.Read())
             {
                 // Need to check if callback is null?
                 if (callback != null) callback(reader);
             }
         }
     }
 }
Exemple #11
0
        /// <summary>
        /// Executes a SQL statement against the SQLite connection
        /// and returns the number of rows affected.
        /// </summary>
        /// <param name="cmdText">SQL query string.</param>
        /// <param name="paramsList">Parameters list.</param>
        /// <param name="atomic">Whether query is executed as a transaction.</param>
        /// <returns>
        /// For UPDATE, INSERT, and DELETE statements, the return value is the
        /// number of rows affected by the command.
        /// For all other types of statements, the return value is -1.
        /// </returns>
        public int ExecuteNonQuery(string cmdText, SqliteParameterCollection paramsList)
        {
            int affectedRows = 0;

            using (SqliteCommand cmd = GetCommand(cmdText, paramsList))
            {
                affectedRows = cmd.ExecuteNonQuery();
            }
            return affectedRows;
        }
        public override bool Update(IList<FileMetaDataItem> items)
        {
            SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME),false);
            using (SqliteConnection con = db.NewSQLiteConnection())
            {
                if (con == null)
                    throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME)));

                SqliteTransaction trasaction = (SqliteTransaction)con.BeginTransaction();
                try
                {
                    foreach (FileMetaDataItem item in items)
                    {
                        string cmdText = "UPDATE " + Configuration.TBL_METADATA +
                                         " SET " + Configuration.COL_HASH_CODE + " = @hash, " +
                                         Configuration.COL_LAST_MODIFIED_TIME + " = @lmf" +
                                         " WHERE " + Configuration.COL_RELATIVE_PATH + " = @rel AND " +
                                         Configuration.COL_SOURCE_ID + " = @sourceId";

                        SqliteParameterCollection paramList = new SqliteParameterCollection();
                        paramList.Add(new SqliteParameter("@hash", DbType.String) { Value = item.HashCode });
                        paramList.Add(new SqliteParameter("@lmf", DbType.DateTime) { Value = item.LastModifiedTime });
                        paramList.Add(new SqliteParameter("@rel", DbType.String) { Value = item.RelativePath });
                        paramList.Add(new SqliteParameter("@sourceId", DbType.String) { Value = item.SourceId });

                        db.ExecuteNonQuery(cmdText, false);
                    }
                    trasaction.Commit();
                }
                catch (Exception)
                {
                    trasaction.Rollback();
                    return false;
                }
            }

            return true;
        }
        public override FolderMetadata LoadFolderMetadata(string currId, SourceOption option)
        {
            string opt = (option == SourceOption.SOURCE_ID_NOT_EQUALS) ? " <> " : " = ";
            var folderMetadata = new FolderMetadata(currId, this.RootPath);

            var db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME),false);
            using (SqliteConnection con = db.NewSQLiteConnection())
            {
                if (con == null)
                    throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME)));

                string cmdText = "SELECT * FROM " + Configuration.TLB_FOLDERMETADATA +
                    " WHERE " + Configuration.COL_SOURCE_ID + opt + " @sourceId";
                var paramList = new SqliteParameterCollection
                                    {new SqliteParameter("@sourceId", DbType.String) {Value = currId}};
                db.ExecuteReader(cmdText, paramList, reader => folderMetadata.FolderMetadataItems.Add(
                                                                   new FolderMetadataItem(
                                                                       (string)reader[Configuration.COL_SOURCE_ID],
                                                                       (string)reader[Configuration.COL_FOLDER_RELATIVE_PATH],
                                                                       (int)reader[Configuration.COL_IS_FOLDER_EMPTY] )));
            }
            return folderMetadata;
        }
        public override FileMetaData LoadFileMetadata(string currId, SourceOption option)
        {
            string opt = (option == SourceOption.SOURCE_ID_NOT_EQUALS) ? " <> " : " = ";
            var mData = new FileMetaData(currId, RootPath);

            var db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME),false);
            using (SqliteConnection con = db.NewSQLiteConnection())
            {
                if (con == null)
                    throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME)));

                string cmdText = string.Format("SELECT * FROM {0} WHERE {1}{2} @sourceId", Configuration.TBL_METADATA, Configuration.COL_SOURCE_ID, opt);

                var paramList = new SqliteParameterCollection
                                                          {
                                                              new SqliteParameter("@sourceId", DbType.String)
                                                                  {Value = currId}
                                                          };

                db.ExecuteReader(cmdText, paramList, reader => mData.MetaDataItems.Add(new FileMetaDataItem(
                                                                                           (string) reader[Configuration.COL_SOURCE_ID],
                                                                                           (string) reader[Configuration.COL_RELATIVE_PATH],
                                                                                           (string) reader[Configuration.COL_HASH_CODE],
                                                                                           (DateTime) reader[Configuration.COL_LAST_MODIFIED_TIME],
                                                                                           Convert.ToUInt32(reader[Configuration.COL_NTFS_ID1]),
                                                                                           Convert.ToUInt32(reader[Configuration.COL_NTFS_ID2]))));
            }
            return mData;
        }
        private bool InsertRenameAction(RenameAction renameAction)
        {
            SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME),false);
            using (SqliteConnection con = db.NewSQLiteConnection ())
            {
                if (con == null)
                    throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME)));

                string cmdText = "INSERT INTO " + Configuration.TBL_ACTION +
                                " ( " + Configuration.COL_CHANGE_IN + "," +
                                Configuration.COL_ACTION_TYPE + "," +
                                Configuration.COL_OLD_RELATIVE_PATH + "," +
                                Configuration.COL_NEW_RELATIVE_PATH + "," +
                                Configuration.COL_OLD_HASH + ") VALUES (@changeIn, @action, @oldPath, @newPath, @oldHash)";

                SqliteParameterCollection paramList = new SqliteParameterCollection();
                paramList.Add(new SqliteParameter("@changeIn", DbType.String) { Value = renameAction.SourceID });
                paramList.Add(new SqliteParameter("@action", DbType.Int32) { Value = renameAction.ChangeType });
                paramList.Add(new SqliteParameter("@oldPath", DbType.String) { Value = renameAction.PreviousRelativeFilePath });
                paramList.Add(new SqliteParameter("@newPath", DbType.String) { Value = renameAction.RelativeFilePath });
                paramList.Add(new SqliteParameter("@oldHash", DbType.String) { Value = renameAction.FileHash });

                db.ExecuteNonQuery(cmdText, paramList);
            }

            return true;
        }
Exemple #16
0
        /// <summary>
        /// Executes the query and returns the first column of the first row in the result set returned by the query.
        /// All other columns and rows are ignored.
        /// </summary>
        /// <param name="cmdText">SQL query string</param>
        /// <returns>The first column of the first row in the result set.</returns>
        /// <summary>
        /// Gets Command object with specified command text of current Sqlite connection
        /// </summary>
        private SqliteCommand GetCommand(string cmdText, SqliteParameterCollection paramsList)
        {
            SqliteCommand cmd = conn.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = cmdText;

            // Add parameters is necessary
            if (paramsList != null)
            {
                cmd.Parameters.Clear();
                foreach(SqliteParameter p in paramsList)
                {
                    cmd.Parameters.Add(p);
                }
            }

            return cmd;
        }
        public override bool Add(SyncJob job)
        {
            if (this.SyncJobExists(job.Name, job.ID))
                throw new SyncJobNameExistException(String.Format(m_ResourceManager.GetString("err_syncjobCreated"), job.Name));

            SQLiteAccess dbAccess1 = new SQLiteAccess(Path.Combine (this.StoragePath, Configuration.DATABASE_NAME),false);
            SQLiteAccess dbAccess2 = new SQLiteAccess(Path.Combine(job.IntermediaryStorage.Path, Configuration.DATABASE_NAME),false);
            SqliteConnection con1 = dbAccess1.NewSQLiteConnection();
            SqliteConnection con2 = dbAccess2.NewSQLiteConnection();

            if (con1 == null)
                throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, DATABASE_NAME)));

            if (con2 == null)
                throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(job.IntermediaryStorage.Path, Configuration.DATABASE_NAME)));

            SqliteTransaction transaction1 = (SqliteTransaction)con1.BeginTransaction();
            SqliteTransaction transaction2 = (SqliteTransaction)con2.BeginTransaction();

            try
            {
                string insertJobText = "INSERT INTO " + SYNCJOB_TABLE +
                                 " (" + COL_SYNCJOB_ID + ", " + COL_SYNCJOB_NAME +
                                 " ," + COL_METADATA_SOURCE_LOCATION + ", " + COL_SYNC_SOURCE_ID +
                                 ") VALUES (@id, @name, @meta, @source)";

                SqliteParameterCollection paramList = new SqliteParameterCollection();
                paramList.Add(new SqliteParameter("@id", System.Data.DbType.String) { Value = job.ID });
                paramList.Add(new SqliteParameter("@name", System.Data.DbType.String) { Value = job.Name });
                paramList.Add(new SqliteParameter("@meta", System.Data.DbType.String) { Value = job.IntermediaryStorage.Path });
                paramList.Add(new SqliteParameter("@source", System.Data.DbType.String) { Value = job.SyncSource.ID });

                dbAccess1.ExecuteNonQuery(insertJobText, paramList);

                SQLiteSyncSourceProvider.Add(job.SyncSource, con1);

                SQLiteSyncSourceProvider provider = (SQLiteSyncSourceProvider)SyncClient.GetSyncSourceProvider(job.IntermediaryStorage.Path);
                if (provider.GetSyncSourceCount() == 2)
                    throw new SyncSourcesNumberExceededException(m_ResourceManager.GetString("err_onlyTwoSyncSourceFolders"));
                SQLiteSyncSourceProvider.Add(job.SyncSource, con2);

                transaction1.Commit();
                transaction2.Commit();
                return true;
            }
            catch (Exception)
            {
                transaction1.Rollback();
                transaction2.Rollback();
                throw;
            }
            finally
            {
                if (con1 != null) con1.Dispose();
                if (con2 != null) con2.Dispose();
            }
        }
        /// <summary>
        /// Update details of a sync source
        /// </summary>
        /// <param name="source"></param>
        /// <returns></returns>
        public override bool Update(SyncSource source)
        {
            SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME),false);
            using (SqliteConnection con = db.NewSQLiteConnection ())
            {
                if (con == null)
                    throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME)));
                string cmdText = "UPDATE " + Configuration.TBL_DATASOURCE_INFO +
                        " SET " + Configuration.COL_SOURCE_ABSOLUTE_PATH + " = @path WHERE "
                        + Configuration.COL_SOURCE_ID + " = @id";

                SqliteParameterCollection paramList = new SqliteParameterCollection
                                                          {
                                                              new SqliteParameter("@id", DbType.String)
                                                                  {Value = source.ID},
                                                              new SqliteParameter("@path", DbType.String)
                                                                  {Value = source.Path}
                                                          };

                db.ExecuteNonQuery(cmdText, false);
            }
            return true;
        }
        public override SyncJob Load(string jobName)
        {
            SyncJob p = null;

            SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, DATABASE_NAME),false);
            using (SqliteConnection con = db.NewSQLiteConnection())
            {
                if (con == null)
                    throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME)));
                string cmdText = "SELECT * FROM " + SYNCJOB_TABLE +
                                 " p, " + DATASOURCE_INFO_TABLE +
                                 " d WHERE p" + "." + COL_SYNC_SOURCE_ID + " = d" + "." + COL_SOURCE_ID +
                                 " AND " + COL_SYNCJOB_NAME + " = @pname";

                SqliteParameterCollection paramList = new SqliteParameterCollection();
                paramList.Add(new SqliteParameter("@pname", System.Data.DbType.String) { Value = jobName });

                db.ExecuteReader(cmdText, paramList, reader =>
                {
                    // TODO: constructor of Profile takes in more arguments to remove dependency on IntermediaryStorage and SyncSource class.
                    SyncSource source = new SyncSource((string)reader[COL_SYNC_SOURCE_ID], (string)reader[COL_SOURCE_ABS_PATH]);
                    IntermediaryStorage mdSource = new IntermediaryStorage((string)reader[COL_METADATA_SOURCE_LOCATION]);

                    p = new SyncJob((string)reader[COL_SYNCJOB_ID], (string)reader[COL_SYNCJOB_NAME], source, mdSource);

                    return;
                }
                );

            }
            return p;
        }
        /// <summary>
        /// Add sync source to database
        /// no transaction supports
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public override bool Add(SyncSource s)
        {
            if (GetSyncSourceCount() == 2)
                throw new SyncSourcesNumberExceededException(m_ResourceManager.GetString("err_onlyTwoSyncSourceFolders"));

            string insertText =  "INSERT INTO " + Configuration.TBL_DATASOURCE_INFO +
                                 "(" + Configuration.COL_SOURCE_ID + "," + Configuration.COL_SOURCE_ABSOLUTE_PATH +
                                 ") VALUES (@id, @path)";

            SqliteParameterCollection paramList = new SqliteParameterCollection
                                                      {
                                                          new SqliteParameter("@id", DbType.String) {Value = s.ID},
                                                          new SqliteParameter("@path", DbType.String) {Value = s.Path}
                                                      };

            SQLiteAccess dbAccess = new SQLiteAccess(Path.Combine (this.StoragePath, Configuration.DATABASE_NAME),false);
            using (SqliteConnection con = dbAccess.NewSQLiteConnection())
            {
                if (con == null)
                    throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME)));

                dbAccess.ExecuteNonQuery(insertText, paramList);
            }
            return true;
        }
        public override bool Update(SyncJob job)
        {
            if (this.SyncJobExists(job.Name, job.ID))
                throw new SyncJobNameExistException(String.Format(m_ResourceManager.GetString("err_syncjobCreated"), job.Name));

            SQLiteSyncSourceProvider provider = (SQLiteSyncSourceProvider)SyncClient.GetSyncSourceProvider(job.IntermediaryStorage.Path);
            if (provider.GetSyncSourceCount() > 2)
                throw new SyncSourcesNumberExceededException(m_ResourceManager.GetString("err_onlyTwoSyncSourceFolders"));

            // Update a profile requires update 2 tables at the same time,
            // If one update on a table fails, the total update action must fail too.
            string updateProfileText  = "UPDATE " + SYNCJOB_TABLE +
                                        " SET " + COL_METADATA_SOURCE_LOCATION + " = @mdSource, " +
                                        COL_SYNCJOB_NAME + " = @name WHERE " + COL_SYNCJOB_ID + " = @id;";

            SqliteParameterCollection paramList = new SqliteParameterCollection();
            // Add parameters for 1st Update statement
            paramList.Add(new SqliteParameter("@mdSource", System.Data.DbType.String) { Value = job.IntermediaryStorage.Path });
            paramList.Add(new SqliteParameter("@name", System.Data.DbType.String) { Value = job.Name });
            paramList.Add(new SqliteParameter("@id", System.Data.DbType.String) { Value = job.ID });

            SQLiteAccess db = new SQLiteAccess(Path.Combine(this.StoragePath, DATABASE_NAME),false);
            using (SqliteConnection con = db.NewSQLiteConnection ())
            {
                if (con == null)
                    throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, DATABASE_NAME)));

                SqliteTransaction transaction = (SqliteTransaction)con.BeginTransaction();
                try
                {
                    SQLiteSyncSourceProvider.Update(job.SyncSource,con );
                    db.ExecuteNonQuery(updateProfileText, paramList);
                    transaction.Commit();
                    return true;
                }
                catch (Exception)
                {
                    transaction.Rollback();
                    throw;
                }
            }
        }
        /// <summary>
        /// Add a list of file metadata item into database
        /// Actom action 
        /// </summary>
        /// <param name="mData"></param>
        /// <returns></returns>
        public override bool Add(IList<FileMetaDataItem> mData)
        {
            var db = new SQLiteAccess(Path.Combine(this.StoragePath, Configuration.DATABASE_NAME),false);
            using (SqliteConnection con = db.NewSQLiteConnection())
            {
                if (con == null)
                    throw new DatabaseException(String.Format(m_ResourceManager.GetString("err_somethingNotFound"), Path.Combine(this.StoragePath, Configuration.DATABASE_NAME)));

                var trasaction = (SqliteTransaction)con.BeginTransaction();
                try
                {
                    const string cmdText = "INSERT INTO " + Configuration.TBL_METADATA +
                                           "( " + Configuration.COL_SOURCE_ID + "," + Configuration.COL_RELATIVE_PATH + "," +
                                           Configuration.COL_HASH_CODE + "," +
                                           Configuration.COL_LAST_MODIFIED_TIME + "," +
                                           Configuration.COL_NTFS_ID1 + "," +
                                           Configuration.COL_NTFS_ID2 + ")" +
                                           "VALUES (@source_id , @relative_path, @hash_code, @last_modified_time, @ntfs_id1, @ntfs_id2) ";
                    foreach (FileMetaDataItem item in mData)
                    {
                        var paramList = new SqliteParameterCollection
                                            {
                                                new SqliteParameter("@source_id", DbType.String) {Value = item.SourceId},
                                                new SqliteParameter("@relative_path", DbType.String)
                                                    {Value = item.RelativePath},
                                                new SqliteParameter("@hash_code", DbType.String) {Value = item.HashCode},
                                                new SqliteParameter("@last_modified_time", DbType.DateTime)
                                                    {Value = item.LastModifiedTime},
                                                new SqliteParameter("@ntfs_id1", DbType.Int32) {Value = item.NTFS_ID1},
                                                new SqliteParameter("@ntfs_id2", DbType.Int32) {Value = item.NTFS_ID2}
                                            };

                        db.ExecuteNonQuery(cmdText, paramList);
                    }
                    trasaction.Commit();
                }
                catch (Exception)
                {
                    trasaction.Rollback();
                    return false;
                }
            }
            return true;
        }