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; } } }
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); }
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); }
private static void AppendParameters(SqliteParameterCollection collection, object[] parameters) { if (parameters == null || parameters.Length < 1) { return; } foreach (var parameter in parameters) { SqliteParameter sqliteParam = parameter as SqliteParameter; if (sqliteParam != null) { collection.Add(sqliteParam); continue; } // Perform Type guessing DbType dbType = DbType.String; switch (Convert.GetTypeCode(parameter)) { case TypeCode.Empty: case TypeCode.DBNull: collection.Add(new SqliteParameter(dbType, DBNull.Value)); continue; case TypeCode.Boolean: dbType = DbType.Boolean; break; case TypeCode.Byte: dbType = DbType.Byte; break; case TypeCode.SByte: dbType = DbType.SByte; break; case TypeCode.UInt16: dbType = DbType.UInt16; break; case TypeCode.Int16: dbType = DbType.Int16; break; case TypeCode.UInt32: dbType = DbType.UInt32; break; case TypeCode.Int32: dbType = DbType.Int32; break; case TypeCode.UInt64: dbType = DbType.UInt64; break; case TypeCode.Int64: dbType = DbType.Int64; break; case TypeCode.Single: dbType = DbType.Single; break; case TypeCode.Double: dbType = DbType.Double; break; case TypeCode.Decimal: dbType = DbType.Decimal; break; case TypeCode.DateTime: dbType = DbType.DateTime; break; } collection.Add(new SqliteParameter(dbType, parameter)); } }
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 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); }
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); }
public static SqliteParameterCollection AddWithValue( this SqliteParameterCollection parameters, string parameterName, SqliteType dbType, object value) { var parameter = new SqliteParameter(parameterName, dbType); parameter.Value = value; parameters.Add(parameter); parameter.ResetSqliteType(); return(parameters); }
/// <summary> /// 将Request里的参数转成SqlParameter[] /// </summary> /// <returns></returns> internal static void RequestParasToSqlParameters(SqliteParameterCollection oldParas) { var oldParaList = oldParas.Cast <SqliteParameter>().ToList(); var paraDictionarAll = SqlSugarTool.GetParameterDictionary(); if (paraDictionarAll != null && paraDictionarAll.Count() > 0) { foreach (KeyValuePair <string, string> it in paraDictionarAll) { var par = new SqliteParameter("@" + it.Key, it.Value); if (!oldParaList.Any(oldPara => oldPara.ParameterName == ("@" + it.Key))) { oldParas.Add(par); } } } }
void Parameterize(SqliteParameterCollection command, object idValue) { if (null != idValue) { var pkf = PrimaryKeyField; if (!command.Contains(pkf)) { var p = new SqliteParameter("@" + pkf, idValue); command.Add(p); } else { command[PrimaryKeyField].ResetDbType(); command[PrimaryKeyField].Value = idValue; } } }
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); }
public override bool Delete(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 = "DELETE FROM " + Configuration.TBL_DATASOURCE_INFO + " WHERE " + Configuration.COL_SOURCE_ID + " = @id"; SqliteParameterCollection paramList = new SqliteParameterCollection(); paramList.Add(new SqliteParameter("@id", DbType.String) { Value = source.ID }); db.ExecuteNonQuery(cmdText, paramList); } return(true); }
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(); } } }