/// <summary> /// Deletes a specific resource ID based on ResourceId, ResourceSet and Culture. /// If an empty culture is passed the entire group is removed (ie. all locales). /// </summary> /// <param name="resourceId">Resource Id to delete</param> /// <param name="cultureName">language ID - if empty all languages are deleted</param>e /// <param name="resourceSet">The resource set to remove</param> /// <returns></returns> public bool DeleteResource(string resourceId, string cultureName = null, string resourceSet = null) { int Result = 0; if (cultureName == null) cultureName = string.Empty; if (resourceSet == null) resourceSet = string.Empty; using (SqlDataAccess Data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString)) { if (!string.IsNullOrEmpty(cultureName)) // Delete the specific entry only Result = Data.ExecuteNonQuery("delete from " + DbResourceConfiguration.Current.ResourceTableName + " where ResourceId=@ResourceId and LocaleId=@LocaleId and ResourceSet=@ResourceSet", Data.CreateParameter("@ResourceId", resourceId), Data.CreateParameter("@LocaleId", cultureName), Data.CreateParameter("@ResourceSet", resourceSet)); else // If we're deleting the invariant entry - delete ALL of the languages for this key Result = Data.ExecuteNonQuery("delete from " + DbResourceConfiguration.Current.ResourceTableName + " where ResourceId=@ResourceId and ResourceSet=@ResourceSet", Data.CreateParameter("@ResourceId", resourceId), Data.CreateParameter("@ResourceSet", resourceSet)); if (Result == -1) { ErrorMessage = Data.ErrorMessage; return false; } } return true; }
public void ExecuteNonQueryTest() { using (var data = new SqlDataAccess(STR_ConnectionString)) { var count = data.ExecuteNonQuery("update Customers set Updated=@1 where id=@0", 1, DateTime.Now); Assert.IsTrue(count > -1, data.ErrorMessage); Assert.IsTrue(count > 0, "No record found to update"); Assert.IsTrue(count == 1, "Invalid number of records updated."); } }
/// <summary> /// Adds a resource to the Localization Table /// </summary> /// <param name="resourceId"></param> /// <param name="value"></param> /// <param name="cultureName"></param> /// <param name="resourceSet"></param> /// <param name="Type"></param> /// <param name="Filename"></param> /// <param name="valueIsFileName">if true the Value property is a filename to import</param> public int AddResource(string resourceId, object value, string cultureName, string resourceSet, string comment, bool valueIsFileName) { string Type = string.Empty; if (cultureName == null) cultureName = string.Empty; if (string.IsNullOrEmpty(resourceId)) { ErrorMessage = "No ResourceId specified. Can't add resource"; return -1; } SqlDataAccess Data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString); if (Transaction != null) Data.Transaction = Transaction; if (value != null && !(value is string)) { Type = value.GetType().AssemblyQualifiedName; try { LosFormatter output = new LosFormatter(); StringWriter writer = new StringWriter(); output.Serialize(writer, value); value = writer.ToString(); } catch (Exception ex) { ErrorMessage = ex.Message; return -1; } } else Type = string.Empty; byte[] BinFile = null; string TextFile = null; string FileName = string.Empty; if (valueIsFileName) { FileInfoFormat FileData = null; try { FileData = GetFileInfo(value as string); } catch (Exception ex) { ErrorMessage = ex.Message; return -1; } Type = "FileResource"; value = FileData.ValueString; FileName = FileData.FileName; if (FileData.FileFormatType == FileFormatTypes.Text) TextFile = FileData.TextContent; else BinFile = FileData.BinContent; } if (value == null) value = string.Empty; DbParameter BinFileParm = Data.CreateParameter("@BinFile", BinFile, DbType.Binary); DbParameter TextFileParm = Data.CreateParameter("@TextFile", TextFile); string Sql = "insert into " + DbResourceConfiguration.Current.ResourceTableName + " (ResourceId,Value,LocaleId,Type,Resourceset,BinFile,TextFile,Filename,Comment) Values (@ResourceID,@Value,@LocaleId,@Type,@ResourceSet,@BinFile,@TextFile,@FileName,@Comment)"; if (Data.ExecuteNonQuery(Sql, Data.CreateParameter("@ResourceId", resourceId), Data.CreateParameter("@Value", value), Data.CreateParameter("@LocaleId", cultureName), Data.CreateParameter("@Type", Type), Data.CreateParameter("@ResourceSet", resourceSet), BinFileParm, TextFileParm, Data.CreateParameter("@FileName", FileName), Data.CreateParameter("@Comment", comment)) == -1) { ErrorMessage = Data.ErrorMessage; return -1; } return 1; }
/// <summary> /// Updates an existing resource in the Localization table /// </summary> /// <param name="ResourceId"></param> /// <param name="Value"></param> /// <param name="CultureName"></param> /// <param name="ResourceSet"></param> /// <param name="Type"></param> public int UpdateResource(string ResourceId, object Value, string CultureName, string ResourceSet, string Comment, bool ValueIsFileName) { string Type = string.Empty; if (CultureName == null) CultureName = string.Empty; SqlDataAccess Data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString); if (Transaction != null) Data.Transaction = Transaction; if (Value != null && !(Value is string)) { Type = Value.GetType().AssemblyQualifiedName; try { LosFormatter output = new LosFormatter(); StringWriter writer = new StringWriter(); output.Serialize(writer, Value); Value = writer.ToString(); } catch (Exception ex) { ErrorMessage = ex.Message; return -1; } } else { Type = string.Empty; if (Value == null) Value = string.Empty; } byte[] BinFile = null; string TextFile = null; string FileName = string.Empty; if (ValueIsFileName) { FileInfoFormat FileData = null; try { FileData = GetFileInfo(Value as string); } catch (Exception ex) { ErrorMessage = ex.Message; return -1; } Type = "FileResource"; Value = FileData.ValueString; FileName = FileData.FileName; if (FileData.FileFormatType == FileFormatTypes.Text) TextFile = FileData.TextContent; else BinFile = FileData.BinContent; } if (Value == null) Value = string.Empty; // Set up Binfile and TextFile parameters which are set only for // file values - otherwise they'll pass as Null values. DbParameter BinFileParm = Data.CreateParameter("@BinFile", BinFile, DbType.Binary); DbParameter TextFileParm = Data.CreateParameter("@TextFile", TextFile); int Result = 0; string Sql = "update " + DbResourceConfiguration.Current.ResourceTableName + " set Value=@Value, Type=@Type, BinFile=@BinFile,TextFile=@TextFile,FileName=@FileName, Comment=@Comment " + "where LocaleId=@LocaleId AND ResourceSet=@ResourceSet and ResourceId=@ResourceId"; Result = Data.ExecuteNonQuery(Sql, Data.CreateParameter("@ResourceId", ResourceId), Data.CreateParameter("@Value", Value), Data.CreateParameter("@Type", Type), Data.CreateParameter("@LocaleId", CultureName), Data.CreateParameter("@ResourceSet", ResourceSet), BinFileParm, TextFileParm, Data.CreateParameter("@FileName", FileName), Data.CreateParameter("@Comment", Comment) ); if (Result == -1) { ErrorMessage = Data.ErrorMessage; return -1; } return Result; }
/// <summary> /// Create a backup of the localization database. /// /// Note the table used is the one specified in the DbResourceConfiguration.Current.ResourceTableName /// </summary> /// <param name="BackupTableName">Table of the backup table. Null creates a _Backup table.</param> /// <returns></returns> public bool CreateBackupTable(string BackupTableName) { if (BackupTableName == null) BackupTableName = DbResourceConfiguration.Current.ResourceTableName + "_Backup"; var data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString); data.ExecuteNonQuery("drop table " + BackupTableName); if (data.ExecuteNonQuery("select * into " + BackupTableName + " from " + DbResourceConfiguration.Current.ResourceTableName) < 0) { ErrorMessage = data.ErrorMessage; return false; } return true; }
/// <summary> /// Restores the localization table from a backup table by first wiping out /// </summary> /// <param name="backupTableName"></param> /// <returns></returns> public bool RestoreBackupTable(string backupTableName) { if (backupTableName == null) backupTableName = DbResourceConfiguration.Current.ResourceTableName + "_Backup"; using (var data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString)) { data.BeginTransaction(); if (data.ExecuteNonQuery("delete from " + DbResourceConfiguration.Current.ResourceTableName) < 0) { data.RollbackTransaction(); ErrorMessage = data.ErrorMessage; return false; } string sql = @"insert into {0} (ResourceId,Value,LocaleId,ResourceSet,Type,BinFile,TextFile,FileName,Comment) select ResourceId,Value,LocaleId,ResourceSet,Type,BinFile,TextFile,FileName,Comment from {1}"; sql = string.Format(sql, DbResourceConfiguration.Current.ResourceTableName, backupTableName); if (data.ExecuteNonQuery(sql) < 0) { data.RollbackTransaction(); ErrorMessage = data.ErrorMessage; return false; } data.CommitTransaction(); } return true; }
/// <summary> /// Persists resources to the database - first wipes out all resources, then writes them back in /// from the ResourceSet /// </summary> /// <param name="ResourceList"></param> /// <param name="CultureName"></param> /// <param name="BaseName"></param> public bool GenerateResources(IDictionary ResourceList, string CultureName, string BaseName, bool DeleteAllResourceFirst) { if (ResourceList == null) throw new InvalidOperationException("No Resources"); if (CultureName == null) CultureName = string.Empty; using (SqlDataAccess Data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString)) { if (!Data.BeginTransaction()) return false; // Set transaction to be shared by other methods Transaction = Data.Transaction; try { // First delete all resources for this resource set if (DeleteAllResourceFirst) { int Result = Data.ExecuteNonQuery("delete " + DbResourceConfiguration.Current.ResourceTableName + " where LocaleId=@LocaleId and ResourceSet=@ResourceSet", Data.CreateParameter("@LocaleId", CultureName), Data.CreateParameter("@ResourceSet", BaseName)); if (Result == -1) { Data.RollbackTransaction(); return false; } } // Now add them all back in one by one foreach (DictionaryEntry Entry in ResourceList) { if (Entry.Value != null) { int Result = 0; if (DeleteAllResourceFirst) Result = AddResource(Entry.Key.ToString(), Entry.Value, CultureName, BaseName, null); else Result = UpdateOrAdd(Entry.Key.ToString(), Entry.Value, CultureName, BaseName, null); if (Result == -1) { Data.RollbackTransaction(); return false; } } } } catch { Data.RollbackTransaction(); return false; } Data.CommitTransaction(); } // Clear out the resources ResourceList = null; return true; }
/// <summary> /// Renames a resource set. Useful if you need to move a local page resource set /// to a new page. ResourceSet naming for local resources is application relative page path: /// /// test.aspx /// subdir/test.aspx /// /// Global resources have a simple name /// </summary> /// <param name="OldResourceSet">Name of the existing resource set</param> /// <param name="NewResourceSet">Name to set the resourceset name to</param> /// <returns></returns> public bool RenameResourceSet(string OldResourceSet, string NewResourceSet) { using (SqlDataAccess Data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString)) { int Result = Data.ExecuteNonQuery("update " + DbResourceConfiguration.Current.ResourceTableName + " set ResourceSet=@NewResourceSet where ResourceSet=@OldResourceSet", Data.CreateParameter("@NewResourceSet", NewResourceSet), Data.CreateParameter("@OldResourceSet", OldResourceSet)); if (Result == -1) { ErrorMessage = Data.ErrorMessage; return false; } if (Result == 0) { SetError(Resources.Resources.No_matching_Recordset_found_); return false; } } return true; }
/// <summary> /// Deletes an entire resource set from the database. Careful with this function! /// </summary> /// <param name="ResourceSet"></param> /// <returns></returns> public bool DeleteResourceSet(string ResourceSet) { if (string.IsNullOrEmpty(ResourceSet)) return false; int Result = -1; using (SqlDataAccess Data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString)) { Result = Data.ExecuteNonQuery("delete from " + DbResourceConfiguration.Current.ResourceTableName + " where ResourceSet=@ResourceSet", Data.CreateParameter("@ResourceSet", ResourceSet)); if (Result < 0) { ErrorMessage = Data.ErrorMessage; return false; } if (Result > 0) return true; } return false; }
/// <summary> /// Renames all property keys for a given property prefix. So this routine updates /// lblName.Text, lblName.ToolTip to lblName2.Text, lblName2.ToolTip if the property /// is changed from lblName to lblName2. /// </summary> /// <param name="Property"></param> /// <param name="NewProperty"></param> /// <param name="ResourceSet"></param> /// <returns></returns> public bool RenameResourceProperty(string Property, string NewProperty, string ResourceSet) { using (SqlDataAccess Data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString)) { Property += "."; NewProperty += "."; string PropertyQuery = Property + "%"; int Result = Data.ExecuteNonQuery("update " + DbResourceConfiguration.Current.ResourceTableName + " set ResourceId=replace(resourceid,@Property,@NewProperty) where ResourceSet=@ResourceSet and ResourceId like @PropertyQuery", Data.CreateParameter("@Property", Property), Data.CreateParameter("@NewProperty", NewProperty), Data.CreateParameter("@ResourceSet", ResourceSet), Data.CreateParameter("@PropertyQuery", PropertyQuery)); if (Result == -1) { ErrorMessage = Data.ErrorMessage; return false; } } return true; }
/// <summary> /// Renames a given resource in a resource set. Note all languages will be renamed /// </summary> /// <param name="ResourceId"></param> /// <param name="NewResourceId"></param> /// <param name="ResourceSet"></param> /// <returns></returns> public bool RenameResource(string ResourceId, string NewResourceId, string ResourceSet) { SqlDataAccess Data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString); int Result = Data.ExecuteNonQuery("update " + DbResourceConfiguration.Current.ResourceTableName + " set ResourceId=@NewResourceId where ResourceId=@ResourceId AND ResourceSet=@ResourceSet", Data.CreateParameter("@ResourceId", ResourceId), Data.CreateParameter("@NewResourceId", NewResourceId), Data.CreateParameter("@ResourceSet", ResourceSet)); if (Result == -1) { ErrorMessage = Data.ErrorMessage; return false; } if (Result == 0) { ErrorMessage = "Invalid ResourceId"; return false; } return true; }