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