/// <summary>
/// Gets all the Resourecs and ResourceIds for a given resource set and Locale
///
/// returns a table "TResource" ResourceId, Value fields
/// </summary>
/// <param name="resourceSet"></param>
/// <param name="cultureName"></param>
/// <returns></returns>
public DataTable GetAllResourcesForCulture(string resourceSet, string cultureName)
{
if (cultureName == null)
cultureName = string.Empty;
using (var data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString))
{
return data.ExecuteTable("TResources",
"select ResourceId, Value from " + DbResourceConfiguration.Current.ResourceTableName + " where ResourceSet=@ResourceSet and LocaleId=@LocaleId",
data.CreateParameter("@ResourceSet", resourceSet),
data.CreateParameter("@LocaleId", cultureName));
}
}
public void NewParametersTableTest()
{
var data = new SqlDataAccess(STR_ConnectionString);
// warmup
data.ExecuteScalar("select top1 id from ApplicationLog");
//var cmd = data.CreateCommand("select * from ApplicationLog where entered > @0 and entered > @1",CommandType.Text, DateTime.Now.AddYears(-10), DateTime.Now.AddYears(-));
//var table = data.ExecuteTable("TLogs", cmd);
var swatch = Stopwatch.StartNew();
var table = data.ExecuteTable("TLogs",
"select * from ApplicationLog where entered > @0 and entered < @1 order by Entered",
DateTime.Now.AddYears(-115), DateTime.Now.AddYears(-1));
Assert.IsNotNull(table, data.ErrorMessage);
Console.WriteLine(table.Rows.Count);
foreach (DataRow row in table.Rows)
{
Console.WriteLine(((DateTime) row["Entered"]));
}
swatch.Stop();
Console.WriteLine(swatch.ElapsedMilliseconds + "ms");
}
/// <summary>
/// Returns all available resource sets
/// </summary>
/// <returns></returns>
public DataTable GetAllResourceSets(ResourceListingTypes Type)
{
SqlDataAccess Data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString);
DataTable dt = null;
if (Type == ResourceListingTypes.AllResources)
dt = Data.ExecuteTable("TResourcesets", "select ResourceSet as ResourceSet from " + DbResourceConfiguration.Current.ResourceTableName + " group by ResourceSet");
else if (Type == ResourceListingTypes.LocalResourcesOnly)
dt = Data.ExecuteTable("TResourcesets", "select ResourceSet as ResourceSet from " + DbResourceConfiguration.Current.ResourceTableName + " where resourceset like @ResourceSet group by ResourceSet",
Data.CreateParameter("@ResourceSet", "%.%"));
else if (Type == ResourceListingTypes.GlobalResourcesOnly)
dt = Data.ExecuteTable("TResourcesets", "select ResourceSet as ResourceSet from " + DbResourceConfiguration.Current.ResourceTableName + " where resourceset not like @ResourceSet group by ResourceSet",
Data.CreateParameter("@ResourceSet", "%.%"));
if (dt == null)
ErrorMessage = Data.ErrorMessage;
return dt;
}
/// <summary>
/// Gets all the locales for a specific resource set.
///
/// Returns a table named TLocaleIds (LocaleId field)
/// </summary>
/// <param name="ResourceSet"></param>
/// <returns></returns>
public DataTable GetAllLocaleIds(string resourceSet)
{
if (resourceSet == null)
resourceSet = string.Empty;
using (SqlDataAccess data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString))
{
return data.ExecuteTable("TLocaleIds", "select LocaleId,'' as Language from " + DbResourceConfiguration.Current.ResourceTableName +
" where ResourceSet=@ResourceSet group by LocaleId",
data.CreateParameter("@ResourceSet", resourceSet));
}
}
/// <summary>
/// Returns all available resource ids for a given resource set in all languages.
///
/// Returns a DataTable called TResoureIds with ResourecId and HasValue fields
/// HasValue returns whether there are any entries in any culture for this
/// resourceId
/// </summary>
/// <param name="resourceSet"></param>
/// <returns></returns>
public DataTable GetAllResourceIds(string resourceSet)
{
using (var data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString))
{
string sql =
@"select resourceId,CAST( MAX(
case
WHEN len( CAST(Value as varchar(max))) > 0 THEN 1
ELSE 0
end ) as Bit) as HasValue
from " + DbResourceConfiguration.Current.ResourceTableName +
@" where ResourceSet=@ResourceSet
group by ResourceId";
var dt = data.ExecuteTable("TResourceIds", sql,
data.CreateParameter("@ResourceSet", resourceSet));
if (dt == null)
{
SetError(data.ErrorMessage);
return null;
}
return dt;
}
}
/// <summary>
/// Returns a data table of all the resources for all locales. The result is in a
/// table called TResources that contains all fields of the table. The table is
/// ordered by LocaleId.
///
/// This version returns ALL resources
///
/// Fields:
/// ResourceId,Value,LocaleId,ResourceSet,Type
/// </summary>
/// <returns></returns>
public DataTable GetAllResources()
{
DataTable dt;
using (SqlDataAccess data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString))
{
string sql = "select ResourceId,Value,LocaleId,ResourceSet,Type,TextFile,BinFile,FileName,Comment from " +
DbResourceConfiguration.Current.ResourceTableName +
" ORDER by ResourceSet,LocaleId";
dt = data.ExecuteTable("TResources", sql, data.CreateParameter("@ResourceSet", "%.%"));
if (dt == null)
{
SetError(data.ErrorMessage);
return null;
}
}
return dt;
}
/// <summary>
/// Returns a data table of all the resources for all locales. The result is in a
/// table called TResources that contains all fields of the table. The table is
/// ordered by LocaleId.
///
/// This version returns either local or global resources in a Web app
///
/// Fields:
/// ResourceId,Value,LocaleId,ResourceSet,Type
/// </summary>
/// <param name="ResourceSet"></param>
/// <returns></returns>
public DataTable GetAllResources(bool LocalResources)
{
SqlDataAccess Data = new SqlDataAccess(DbResourceConfiguration.Current.ConnectionString);
string Sql = string.Empty;
Sql = "select ResourceId,Value,LocaleId,ResourceSet,Type,TextFile,BinFile,FileName,Comment from " + DbResourceConfiguration.Current.ResourceTableName +
" where ResourceSet " +
(!LocalResources ? "not" : string.Empty) + " like @ResourceSet ORDER by ResourceSet,LocaleId";
DataTable dt = Data.ExecuteTable("TResources",
Sql,
Data.CreateParameter("@ResourceSet", "%.%"));
if (dt == null)
{
ErrorMessage = Data.ErrorMessage;
return null;
}
return dt;
}
public void CheckDataBase()
{
SqlDataAccess db = new SqlDataAccess(STR_ConnectionString);
var tb = db.ExecuteTable("localizations","select * from localizations");
Console.WriteLine(tb.Rows.Count);
}
public void DataBase()
{
SqlDataAccess db = new SqlDataAccess("DevSamples");
var tb = db.ExecuteTable("localizations","select * from localizations");
Console.WriteLine(tb.Rows.Count);
}
