public static BaseReferenceRaw[] GetList(long[] types)
{
using (DbManagerProxy manager = DbManagerFactory.Factory.Create(EidssUserContext.Instance))
{
var list = new List<BaseReferenceRaw>();
foreach (long type in types)
{
if (type == 19000019) // Diagnosis
{
list.AddRange(
manager.SetCommand(@"
select idfsBaseReference, idfsReferenceType, intHACode, strDefault
from trtBaseReference
inner join trtDiagnosis
on trtDiagnosis.idfsDiagnosis = trtBaseReference.idfsBaseReference
where trtBaseReference.intRowStatus = 0 and idfsReferenceType = @idfsReferenceType
and trtDiagnosis.idfsUsingType = 10020001"
, manager.Parameter("@idfsReferenceType", type))
.ExecuteList<BaseReferenceRaw>());
}
else
{
list.AddRange(
manager.SetCommand(@"
select idfsBaseReference, idfsReferenceType, intHACode, strDefault
from trtBaseReference
where intRowStatus = 0 and idfsReferenceType = @idfsReferenceType"
, manager.Parameter("@idfsReferenceType", type))
.ExecuteList<BaseReferenceRaw>());
}
}
return list.ToArray();
}
}
public static GisBaseReferenceTranslationRaw[] GetAll(string[] langs)
{
using (DbManagerProxy manager = DbManagerFactory.Factory.Create(EidssUserContext.Instance))
{
var list = new List<GisBaseReferenceTranslationRaw>();
foreach (string lang in langs)
{
list.AddRange(manager.SetCommand(@"
select b.idfsGISBaseReference as idfsBaseReference, o.strTextString as strTranslation, @lang as strLanguage
from gisBaseReference b
inner join gisCountry c on c.idfsCountry = b.idfsGISBaseReference
left join dbo.gisStringNameTranslation as o
on b.idfsGISBaseReference = o.idfsGISBaseReference and o.idfsLanguage = dbo.fnGetLanguageCode(@lang)
where b.intRowStatus = 0 and c.idfsCountry = @idfsCountry"
, manager.Parameter("@idfsCountry", EidssSiteContext.Instance.CountryID), manager.Parameter("@lang", lang))
.ExecuteList<GisBaseReferenceTranslationRaw>());
list.AddRange(manager.SetCommand(@"
select b.idfsGISBaseReference as idfsBaseReference, o.strTextString as strTranslation, @lang as strLanguage
from gisBaseReference b
inner join gisRegion c on c.idfsRegion = b.idfsGISBaseReference
left join dbo.gisStringNameTranslation as o
on b.idfsGISBaseReference = o.idfsGISBaseReference and o.idfsLanguage = dbo.fnGetLanguageCode(@lang)
where b.intRowStatus = 0 and c.idfsCountry = @idfsCountry"
, manager.Parameter("@idfsCountry", EidssSiteContext.Instance.CountryID), manager.Parameter("@lang", lang))
.ExecuteList<GisBaseReferenceTranslationRaw>());
list.AddRange(manager.SetCommand(@"
select b.idfsGISBaseReference as idfsBaseReference, o.strTextString as strTranslation, @lang as strLanguage
from gisBaseReference b
inner join gisRayon d on d.idfsRayon = b.idfsGISBaseReference
inner join gisRegion c on c.idfsRegion = d.idfsRegion
left join dbo.gisStringNameTranslation as o
on b.idfsGISBaseReference = o.idfsGISBaseReference and o.idfsLanguage = dbo.fnGetLanguageCode(@lang)
where b.intRowStatus = 0 and c.idfsCountry = @idfsCountry"
, manager.Parameter("@idfsCountry", EidssSiteContext.Instance.CountryID), manager.Parameter("@lang", lang))
.ExecuteList<GisBaseReferenceTranslationRaw>());
list.AddRange(manager.SetCommand(@"
select b.idfsGISBaseReference as idfsBaseReference, o.strTextString as strTranslation, @lang as strLanguage
from gisBaseReference b
inner join gisSettlement e on e.idfsSettlement = b.idfsGISBaseReference
inner join gisRayon d on d.idfsRayon = e.idfsRayon
inner join gisRegion c on c.idfsRegion = d.idfsRegion
left join dbo.gisStringNameTranslation as o
on b.idfsGISBaseReference = o.idfsGISBaseReference and o.idfsLanguage = dbo.fnGetLanguageCode(@lang)
where b.intRowStatus = 0 and c.idfsCountry = @idfsCountry"
, manager.Parameter("@idfsCountry", EidssSiteContext.Instance.CountryID), manager.Parameter("@lang", lang))
.ExecuteList<GisBaseReferenceTranslationRaw>());
}
return list.ToArray();
}
}
public static GisBaseReferenceRaw[] GetAll()
{
using (DbManagerProxy manager = DbManagerFactory.Factory.Create(EidssUserContext.Instance))
{
var list = new List<GisBaseReferenceRaw>();
list.AddRange(manager.SetCommand(@"
select b.idfsGISBaseReference as idfsBaseReference, b.idfsGISReferenceType as idfsReferenceType,
c.idfsCountry as idfsCountry, 0 as idfsRegion, 0 as idfsRayon, b.strDefault as strDefault
from gisBaseReference b
inner join gisCountry c on c.idfsCountry = b.idfsGISBaseReference
where b.intRowStatus = 0 and c.idfsCountry = @idfsCountry"
, manager.Parameter("@idfsCountry", EidssSiteContext.Instance.CountryID))
.ExecuteList<GisBaseReferenceRaw>());
list.AddRange(manager.SetCommand(@"
select b.idfsGISBaseReference as idfsBaseReference, b.idfsGISReferenceType as idfsReferenceType,
c.idfsCountry as idfsCountry, c.idfsRegion as idfsRegion, 0 as idfsRayon, b.strDefault as strDefault
from gisBaseReference b
inner join gisRegion c on c.idfsRegion = b.idfsGISBaseReference
where b.intRowStatus = 0 and c.idfsCountry = @idfsCountry"
, manager.Parameter("@idfsCountry", EidssSiteContext.Instance.CountryID))
.ExecuteList<GisBaseReferenceRaw>());
list.AddRange(manager.SetCommand(@"
select b.idfsGISBaseReference as idfsBaseReference, b.idfsGISReferenceType as idfsReferenceType,
c.idfsCountry as idfsCountry, d.idfsRegion as idfsRegion, d.idfsRayon as idfsRayon, b.strDefault as strDefault
from gisBaseReference b
inner join gisRayon d on d.idfsRayon = b.idfsGISBaseReference
inner join gisRegion c on c.idfsRegion = d.idfsRegion
where b.intRowStatus = 0 and c.idfsCountry = @idfsCountry"
, manager.Parameter("@idfsCountry", EidssSiteContext.Instance.CountryID))
.ExecuteList<GisBaseReferenceRaw>());
list.AddRange(manager.SetCommand(@"
select b.idfsGISBaseReference as idfsBaseReference, b.idfsGISReferenceType as idfsReferenceType,
c.idfsCountry as idfsCountry, c.idfsRegion as idfsRegion, d.idfsRayon as idfsRayon, b.strDefault as strDefault
from gisBaseReference b
inner join gisSettlement e on e.idfsSettlement = b.idfsGISBaseReference
inner join gisRayon d on d.idfsRayon = e.idfsRayon
inner join gisRegion c on c.idfsRegion = d.idfsRegion
where b.intRowStatus = 0 and c.idfsCountry = @idfsCountry"
, manager.Parameter("@idfsCountry", EidssSiteContext.Instance.CountryID))
.ExecuteList<GisBaseReferenceRaw>());
return list.ToArray();
}
}
public static T GetInnerQueryResult <T>(DbManagerProxy manager, string queryString, string lang, Func <DbManager, T> commandExecutor)
{
Utils.CheckNotNull(manager, "manager");
Utils.CheckNotNull(lang, "lang");
Utils.CheckNotNullOrEmpty(queryString, "queryString");
Utils.CheckNotNull(commandExecutor, "commandExecutor");
int oldTimeout = manager.CommandTimeout;
try
{
manager.CommandTimeout = m_CommandTimeout;
manager.BeginTransaction(IsolationLevel.ReadUncommitted);
DbManager command = manager.SetCommand(queryString,
manager.Parameter("LangID", lang));
T result = commandExecutor(command);
manager.CommitTransaction();
return(result);
}
catch (Exception)
{
manager.RollbackTransaction();
throw;
}
finally
{
manager.CommandTimeout = oldTimeout;
}
}
public void AvrSearchObjectCheckTest()
{
using (DbManagerProxy manager = DbManagerFactory.Factory.Create())
{
try
{
manager.BeginTransaction();
manager.CommandTimeout = 120;
string scriptPath =
PathToTestFolder.Get(TestFolders.Db) + @"..\..\Sources\EIDSS\eidss.db\Scripts\AVR Check script\Search objects check script.sql";
if (!File.Exists(scriptPath))
{
scriptPath =
PathToTestFolder.Get(TestFolders.Db) + @"..\..\eidss.db\Scripts\AVR Check script\Search objects check script.sql";
}
IEnumerable <string> scripts = ScriptLoader.LoadScript(scriptPath);
foreach (string script in scripts)
{
if (String.IsNullOrWhiteSpace(script))
{
continue;
}
manager.SetCommand(script.Trim()).ExecuteNonQuery();
}
}
finally
{
manager.RollbackTransaction();
}
}
}
public static int GetQueryFieldsCount(long queryId)
{
using (DbManagerProxy manager = DbManagerFactory.Factory.Create())
{
DbManager command = manager.SetCommand(
@" select sum(t.fieldCount) from
(
select
case
when sf.idfsGISReferenceType is not null then 2 else 1
end as fieldCount
from tasQuerySearchField qsf
inner join tasQuerySearchObject qso
on qsf.idfQuerySearchObject = qso.idfQuerySearchObject
inner join tasSearchField sf
on sf.idfsSearchField = qsf.idfsSearchField
where qso.idflQuery = @idflQuery
) as t",
manager.Parameter("idflQuery", queryId)
);
var count = (int)command.ExecuteScalar();
return(count);
}
}
public static string GetQueryFunctionName(DbManagerProxy manager, long queryId)
{
Utils.CheckNotNull(manager, "manager");
DbManager command = manager.SetCommand(@"SELECT [strFunctionName] FROM [dbo].[tasQuery] where [idflQuery] = @idflQuery",
manager.Parameter("idflQuery", queryId));
var functionName = command.ExecuteScalar <string>();
return(functionName);
}
public static void DropAndCreateArchiveQuery(DbManagerProxy manager, DbManagerProxy archiveManager, long queryId)
{
Utils.CheckNotNull(manager, "manager");
Utils.CheckNotNull(archiveManager, "archiveManager");
if (manager.Connection.Database == archiveManager.Connection.Database)
{
return;
}
const string dropFunctionFormat =
@"IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[{0}] ";
const string dropViewFormat =
@"IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[{0}]'))
DROP VIEW [dbo].[{0}]";
List <string> queryFunctionNames = GetSubQueryFunctionName(manager, queryId);
queryFunctionNames.Add(GetQueryFunctionName(manager, queryId));
foreach (string queryFunctionName in queryFunctionNames)
{
if (!queryFunctionName.StartsWith("fn"))
{
throw new AvrDbException(string.Format("Query function {0} should starts with 'fn'", queryFunctionName));
}
string queryViewName = "vw" + queryFunctionName.Remove(0, 2);
string dropFunctionText = string.Format(dropFunctionFormat, queryFunctionName);
string dropViewText = string.Format(dropViewFormat, queryViewName);
string createFunctionText = GetQueryFunctionText(manager, queryFunctionName);
string createViewText = GetQueryFunctionText(manager, queryViewName);
archiveManager.SetCommand(dropViewText).ExecuteNonQuery();
archiveManager.SetCommand(createViewText).ExecuteNonQuery();
archiveManager.SetCommand(dropFunctionText).ExecuteNonQuery();
if (!string.IsNullOrEmpty(createFunctionText))
{
archiveManager.SetCommand(createFunctionText).ExecuteNonQuery();
}
}
}
private static int GetLayoutCount(long publishedId)
{
using (DbManagerProxy manager = DbManagerFactory.Factory.Create())
{
DbManager command = manager.SetCommand(
@" select COUNT(*) from tasglLayout where idfsLayout = @idfsLayout",
manager.Parameter("idfsLayout", publishedId));
var result = (int)command.ExecuteScalar();
return(result);
}
}
public void create_extenders_Test()
{
DbManagerFactory.SetRemoteFactory();
using (DbManagerProxy manager = DbManagerFactory.Factory.Create())
{
var result = manager
.SetCommand("select @par1 - @par2",
manager.InputParameter("par1", 5),
manager.InputParameter("par2", 2)
)
.ExecuteScalar <int>();
}
}
private static void UpdateDateCacheRequest(long query)
{
using (var avrTran = new AvrDbTransaction())
{
DbManagerProxy manager = avrTran.Manager;
DbManager command = manager.SetCommand(
@"update dbo.QueryCache set datQueryCacheRequest = '2010-01-01' where idfQuery = @idflQuery",
manager.Parameter("idflQuery", query)
);
command.ExecuteNonQuery();
avrTran.CommitTransaction();
}
}
public AVRFacadeStub(long queryCacheId, int multiplier = 1)
{
QueryTableModel tableModel;
using (DbManagerProxy manager = DbManagerFactory.Factory.Create())
{
using (DbManager command = manager.SetCommand(@"select * from dbo.AVR_HumanCaseReport"))
{
tableModel = BinarySerializer.SerializeFromCommand(command, 123, "en", false, 10);
}
}
m_ZippedBody = tableModel.BodyPackets.Select(BinaryCompressor.Zip).ToList();
m_ZippedHeader = new QueryTableHeaderDTO(BinaryCompressor.Zip(tableModel.Header), queryCacheId, m_ZippedBody.Count * multiplier);
}
private static void UpdateQueryRefreshDate(long?id, int days)
{
using (var avrTran = new AvrDbTransaction())
{
DbManagerProxy manager = avrTran.Manager;
DbManager command = manager.SetCommand(
@" update QueryCache
set datQueryRefresh = DATEADD(day, @intDays, GETDATE())
where [idfQueryCache] = @idflQueryCache",
manager.Parameter("idflQueryCache", id),
manager.Parameter("intDays", days)
);
command.ExecuteNonQuery();
avrTran.CommitTransaction();
}
}
private static object GetDateUserQueryCacheRequest(long?id)
{
object datUserQueryCacheRequest;
using (var avrTran = new AvrDbTransaction())
{
DbManagerProxy manager = avrTran.Manager;
DbManager command = manager.SetCommand(
@" select datUserQueryCacheRequest from QueryCache
where [idfQueryCache] = @idflQueryCache",
manager.Parameter("idflQueryCache", id)
);
datUserQueryCacheRequest = command.ExecuteScalar();
}
return(datUserQueryCacheRequest);
}
public void GetInnerExceptionDescriptionTest()
{
try
{
DbManagerFactory.SetSqlFactory("Persist Security Info=true;Initial Catalog=xxx;Data Source=xxx;");
using (DbManagerProxy manager = DbManagerFactory.Factory.Create())
{
manager.SetCommand("select 1;");
manager.ExecuteNonQuery();
}
}
catch (Exception ex)
{
string description = SqlExceptionHandler.GetExceptionDescription(ex);
Assert.AreNotEqual(string.Empty, description);
}
}
public static void CheckDbConnection()
{
try
{
string dbName;
using (DbManagerProxy dbManager = DbManagerFactory.Factory.Create(ModelUserContext.Instance))
{
dbName = dbManager.Connection.Database;
dbManager.SetCommand(@"select 1").ExecuteNonQuery();
}
m_Trace.Trace(TraceTitle, "Database '{0}' connection checked.", dbName);
}
catch (Exception ex)
{
m_Trace.TraceError(ex);
}
}
public override void DeleteContextData()
{
using (DbManagerProxy manager = DbManagerFactory.Factory.Create(this))
{
try
{
manager.SetCommand("dbo.spDeleteContextData").ExecuteNonQuery();
}
catch (Exception e)
{
if (e is DataException)
{
throw DbModelException.Create(null, e as DataException);
}
throw;
}
}
}
public void MissedValuesLookupTests()
{
using (DbManagerProxy manager = DbManagerFactory.Factory.Create())
{
DbManager command = manager.SetCommand(
@"select
sf.idfsSearchField,
br.strDefault,
sf.strSearchFieldAlias,
sf.strLookupTable,
sf.strLookupAttribute
from tasSearchField sf
inner join trtBaseReference br
on sf.idfsSearchFieldType = br.idfsBaseReference
and br.idfsReferenceType = 19000081
and sf.blnAllowMissedReferenceValues = 1
and sf.idfsSearchFieldType <> 10081002"
);
DataTable searchFields = command.ExecuteDataTable();
Assert.AreEqual(0, searchFields.Select("strLookupTable is NULL").Length, "Not all search fields have strLookupTable");
Assert.AreEqual(0, searchFields.Select("strLookupAttribute is NULL").Length, "Not all search fields have strLookupAttribute");
foreach (DataRow row in searchFields.Rows)
{
object tableNameObj = row["strLookupTable"];
object columnNameObj = row["strLookupAttribute"];
if (tableNameObj != DBNull.Value && columnNameObj != DBNull.Value)
{
string tableName = tableNameObj.ToString();
string columnName = columnNameObj.ToString();
DataView lookup = LookupCache.Get(tableName);
Assert.IsNotNull(lookup, string.Format("Could not load lookup '{0}'", tableName));
//if (!lookup.Table.Columns.Contains(columnName) && columnName == "strRegionExtendedName")
//{
// columnName = "strExtendedRegionName";
//}
Assert.IsTrue(lookup.Table.Columns.Contains(columnName),
string.Format("Could not find attribute '{0}' in lookup '{1}'", columnName, tableName));
}
}
}
}
private string CreateGeoLocationString()
{
if (IsNull)
{
return("");
}
if (bNeedCreateGeoLocationString || Utils.IsEmpty(strAddressStringTranslate))
{
try
{
using (DbManagerProxy manager = DbManagerFactory.Factory.Create(ModelUserContext.Instance))
{
strAddressStringTranslate = manager.SetCommand(@"select dbo.fnCreateGeoLocationString(@LangID, @GeoLocationType, @Country, @Region, @Rayon, @Latitude, @Longitude, @PostCode, @SettlementType, @Settlement, @Street, @House, @Building, @Apartment, @Alignment, @Distance, @blnForeignAddress, @strForeignAddress)",
manager.Parameter("@LangID", ModelUserContext.CurrentLanguage),
manager.Parameter("@GeoLocationType", idfsGeoLocationType),
manager.Parameter("@Country", Country == null ? "" : Country.strCountryName),
manager.Parameter("@Region", Region == null ? "" : Region.strRegionName),
manager.Parameter("@Rayon", Rayon == null ? "" : Rayon.strRayonName),
manager.Parameter("@Latitude", dblLatitude == null ? "0" : dblLatitude.Value.ToString("0.00000")),
manager.Parameter("@Longitude", dblLongitude == null ? "0" : dblLongitude.Value.ToString("0.00000")),
manager.Parameter("@PostCode", strPostCode ?? ""),
manager.Parameter("@SettlementType", Settlement == null ? "" : Settlement.strSettlementType),
manager.Parameter("@Settlement", Settlement == null ? "" : Settlement.strSettlementName),
manager.Parameter("@Street", strStreetName ?? ""),
manager.Parameter("@House", strHouse ?? ""),
manager.Parameter("@Building", strBuilding ?? ""),
manager.Parameter("@Apartment", strApartment ?? ""),
manager.Parameter("@Alignment", dblAlignment ?? 0),
manager.Parameter("@Distance", dblDistance ?? 0),
manager.Parameter("@blnForeignAddress", idfsGeoLocationType == (long)GeoLocationTypeEnum.Address),
manager.Parameter("@strForeignAddress", strForeignAddress ?? "")
).ExecuteScalar <string>();
}
}
catch
{
strAddressStringTranslate = "";
}
bNeedCreateGeoLocationString = false;
}
return(strAddressStringTranslate);
}
private List <long> GetDenyPermissionOnDiagnosis(object userId)
{
using (DbManagerProxy manager = DbManagerFactory.Factory.Create(ModelUserContext.Instance))
{
try
{
DataTable table =
manager.SetCommand(
"select idfsDiagnosis from dbo.fnGetPermissionOnDiagnosis(@ObjectOperation, @Employee) where intPermission = 1",
manager.Parameter("@ObjectOperation", Convert.ToInt64(ObjectOperation.Read)),
manager.Parameter("@Employee", userId)
).ExecuteDataTable();
return((from DataRow row in table.Rows
select(long) row["idfsDiagnosis"]).ToList());
}
catch (DataException e)
{
throw DbModelException.Create(null, e);
}
}
}
public static BaseReferenceTranslationRaw[] GetList(long[] types, string[] langs)
{
using (DbManagerProxy manager = DbManagerFactory.Factory.Create(EidssUserContext.Instance))
{
var list = new List<BaseReferenceTranslationRaw>();
foreach (string lang in langs)
{
foreach (long type in types)
{
list.AddRange(manager.SetCommand(@"
select br.idfsBaseReference, bt.strTextString as strTranslation, @lang as strLanguage
from trtBaseReference br
inner join trtStringNameTranslation bt on bt.idfsBaseReference = br.idfsBaseReference
and bt.idfsLanguage = dbo.fnGetLanguageCode(@lang)
where br.intRowStatus = 0 and br.idfsReferenceType = @idfsReferenceType
"
, manager.Parameter("@idfsReferenceType", type), manager.Parameter("@lang", lang))
.ExecuteList<BaseReferenceTranslationRaw>());
}
}
return list.ToArray();
}
}
public static IList <string> GetLanguages()
{
var result = new List <string>();
using (DbManagerProxy manager = DbManagerFactory.Factory.Create())
{
DbManager commandLookup = manager.SetSpCommand("spAsLanguageSelectLookup",
manager.Parameter("strLanguage", ModelUserContext.CurrentLanguage));
DataTable supportedLanguagesTable = commandLookup.ExecuteDataTable();
foreach (string lang in Localizer.AllSupportedLanguages.Keys)
{
DbManager command = manager.SetCommand("select [dbo].[fnGetLanguageCode](@languageCode)",
manager.Parameter("languageCode", lang));
var langId = command.ExecuteScalar <long>();
if (supportedLanguagesTable.Select("idfsReference = " + langId).Length > 0)
{
result.Add(lang);
}
}
}
return(result);
}
public void TestGetUserQueryRefershDateHeader()
{
QueryTableModel table = GetTestTableModel();
AvrDbHelper.SaveQueryCache(table);
var queryId = table.QueryId;
long?id = AvrDbHelper.GetQueryCacheId(queryId, "en", false);
Assert.IsTrue(id.HasValue);
using (var avrTran = new AvrDbTransaction())
{
DbManagerProxy manager = avrTran.Manager;
DbManager command = manager.SetCommand(
@" update QueryCache
set datUserQueryCacheRequest = NULL
where [idfQuery] = @idflQuery",
manager.Parameter("idflQuery", queryId)
);
command.ExecuteNonQuery();
avrTran.CommitTransaction();
}
var date = AvrDbHelper.GetsQueryCacheUserRequestDate(queryId);
Assert.IsFalse(date.HasValue);
AvrDbHelper.GetQueryCacheHeader(id.Value, true, false);
date = AvrDbHelper.GetsQueryCacheUserRequestDate(queryId);
Assert.IsFalse(date.HasValue);
AvrDbHelper.GetQueryCacheHeader(id.Value, false, false);
date = AvrDbHelper.GetsQueryCacheUserRequestDate(queryId);
Assert.IsTrue(date.HasValue);
}
public void TestExistingTable()
{
using (var avrTran = new AvrDbTransaction())
{
DbManagerProxy manager = avrTran.Manager;
DbManager command = manager.SetCommand(
@" DELETE FROM [dbo].[QueryCachePacket]
where [idfQueryCache] = @idflQueryCache
and ([idfQueryCachePacket] = @idflQueryCachePacket1 or [idfQueryCachePacket] = @idflQueryCachePacket2)
DELETE FROM [dbo].[QueryCache]
where [idfQueryCache] = @idflQueryCache
and [strLanguage] = @strLanguage",
manager.Parameter("idflQueryCache", 1),
manager.Parameter("strLanguage", "en"),
manager.Parameter("idflQueryCachePacket1", 1),
manager.Parameter("idflQueryCachePacket2", 2)
);
command.ExecuteNonQuery();
avrTran.CommitTransaction();
}
Assert.IsNull(AvrDbHelper.GetQueryCacheId(2, "en", false));
Assert.AreEqual(0, AvrDbHelper.GetQueryCacheHeader(1, false, false).BinaryHeader.BinaryBody.Length);
Assert.AreEqual(0, AvrDbHelper.GetQueryCachePacket(1, 0).BinaryBody.Length);
var header = new byte[10000];
for (int i = 0; i < header.Length; i++)
{
header[i] = (byte)i;
}
var packet = new byte[20000];
for (int i = 0; i < packet.Length; i++)
{
packet[i] = (byte)(i + 10);
}
using (var avrTran = new AvrDbTransaction())
{
DbManagerProxy manager = avrTran.Manager;
DbManager command = manager.SetCommand(
@"
SET IDENTITY_INSERT [dbo].[QueryCachePacket] OFF
SET IDENTITY_INSERT [dbo].[QueryCache] ON
INSERT INTO [dbo].[QueryCache]
([idfQueryCache],[idfQuery],[strLanguage],[blbQuerySchema],[intQueryColumnCount],[datQueryRefresh],[datQueryCacheRequest],[blnUseArchivedData],[blnActualQueryCache])
VALUES (@idflQueryCache, @idflQuery, @strLanguage, @binHeaderCache, @intHeaderRowCount, GETDATE(), GETDATE(), 1, 0)
SET IDENTITY_INSERT [dbo].[QueryCache] OFF
SET IDENTITY_INSERT [dbo].[QueryCachePacket] ON
INSERT INTO [dbo].[QueryCachePacket]
([idfQueryCachePacket],[idfQueryCache],[intQueryCachePacketNumber],[blbQueryCachePacket], [intTableRowCount],[blnArchivedData])
VALUES(@idflQueryCachePacket1, @idflQueryCache, 0, @binPacketCache, @intPacketRowCount, 0)
INSERT INTO [dbo].[QueryCachePacket]
([idfQueryCachePacket],[idfQueryCache],[intQueryCachePacketNumber],[blbQueryCachePacket], [intTableRowCount],[blnArchivedData])
VALUES(@idflQueryCachePacket2, @idflQueryCache, 1, @binPacketCache, @intPacketRowCount, 0)
SET IDENTITY_INSERT [dbo].[QueryCachePacket] OFF",
manager.Parameter("idflQueryCache", 1),
manager.Parameter("idflQuery", 2),
manager.Parameter("strLanguage", "en"),
manager.Parameter("idflQueryCachePacket1", 1),
manager.Parameter("idflQueryCachePacket2", 2),
manager.Parameter("binHeaderCache", header),
manager.Parameter("binPacketCache", packet),
manager.Parameter("intHeaderRowCount", 10),
manager.Parameter("intPacketRowCount", 100)
);
command.ExecuteNonQuery();
avrTran.CommitTransaction();
}
Assert.AreEqual(1, AvrDbHelper.GetQueryCacheId(2, "en", false, 7, true));
Assert.AreEqual(1, AvrDbHelper.GetQueryCacheId(new QueryCacheKey(2, "en", true), 7, true));
QueryTableHeaderDTO resultHeader = AvrDbHelper.GetQueryCacheHeader(1, false, false);
Assert.AreEqual(10, resultHeader.BinaryHeader.RowCount);
Assert.AreEqual(2, resultHeader.PacketCount);
Assert.AreEqual(1, resultHeader.QueryCacheId);
AssertAreArrayEqual(new ChunkByteArray(header), resultHeader.BinaryHeader.BinaryBody);
Assert.AreEqual(20000, AvrDbHelper.GetQueryCachePacket(1, 0).BinaryBody.Length);
Assert.AreEqual(20000, AvrDbHelper.GetQueryCachePacket(1, 1).BinaryBody.Length);
Assert.AreEqual(0, AvrDbHelper.GetQueryCachePacket(1, 2).BinaryBody.Length);
QueryTablePacketDTO resultPacket = AvrDbHelper.GetQueryCachePacket(1, 0);
Assert.AreEqual(100, resultPacket.RowCount);
AssertAreArrayEqual(new ChunkByteArray(packet), resultPacket.BinaryBody);
DateTime dateTime = AvrDbHelper.GetQueryRefreshDateTime(1, "en");
Assert.IsTrue(DateTime.Now.Subtract(dateTime).Seconds < 2);
AvrDbHelper.InvalidateQueryCache(2, "en");
Assert.IsNull(AvrDbHelper.GetQueryCacheId(2, "en", false));
Assert.IsNotNull(AvrDbHelper.GetQueryCacheId(2, "en", false, 7, true));
Assert.AreNotEqual(0, AvrDbHelper.GetQueryCacheHeader(1, false, false).BinaryHeader.BinaryBody.Length);
Assert.AreNotEqual(0, AvrDbHelper.GetQueryCachePacket(1, 0).BinaryBody.Length);
AvrDbHelper.DeleteQueryCache(2, "en", true);
Assert.IsNull(AvrDbHelper.GetQueryCacheId(2, "en", false));
Assert.IsNotNull(AvrDbHelper.GetQueryCacheId(2, "en", false, 7, true));
}
public void TestArchiveExistingTable()
{
using (var avrTran = new AvrDbTransaction())
{
DbManagerProxy manager = avrTran.Manager;
DbManager command = manager.SetCommand(
@" DELETE FROM [dbo].[QueryCachePacket]
where [idfQueryCache] = @idflQueryCache
and ([idfQueryCachePacket] = @idflQueryCachePacket1 or [idfQueryCachePacket] = @idflQueryCachePacket2)
DELETE FROM [dbo].[QueryCache]
where [idfQueryCache] = @idflQueryCache
and [strLanguage] = @strLanguage",
manager.Parameter("idflQueryCache", 1),
manager.Parameter("strLanguage", "en"),
manager.Parameter("idflQueryCachePacket1", 1),
manager.Parameter("idflQueryCachePacket2", 2)
);
command.ExecuteNonQuery();
avrTran.CommitTransaction();
}
Assert.IsNull(AvrDbHelper.GetQueryCacheId(2, "en", false));
Assert.AreEqual(0, AvrDbHelper.GetQueryCacheHeader(1, false, false).BinaryHeader.BinaryBody.Length);
Assert.AreEqual(0, AvrDbHelper.GetQueryCachePacket(1, 0).BinaryBody.Length);
var header = new byte[10000];
var packet = new byte[20000];
using (var avrTran = new AvrDbTransaction())
{
DbManagerProxy manager = avrTran.Manager;
DbManager command = manager.SetCommand(
@"
SET IDENTITY_INSERT [dbo].[QueryCachePacket] OFF
SET IDENTITY_INSERT [dbo].[QueryCache] ON
INSERT INTO [dbo].[QueryCache]
([idfQueryCache],[idfQuery],[strLanguage],[blbQuerySchema],[intQueryColumnCount],[datQueryRefresh],[datQueryCacheRequest],[blnUseArchivedData],[blnActualQueryCache])
VALUES (@idflQueryCache, @idflQuery, @strLanguage, @binHeaderCache, @intHeaderRowCount, GETDATE(), GETDATE(), 1, 1)
SET IDENTITY_INSERT [dbo].[QueryCache] OFF
SET IDENTITY_INSERT [dbo].[QueryCachePacket] ON
INSERT INTO [dbo].[QueryCachePacket]
([idfQueryCachePacket],[idfQueryCache],[intQueryCachePacketNumber],[blbQueryCachePacket], [intTableRowCount],[blnArchivedData])
VALUES(@idflQueryCachePacket1, @idflQueryCache, 0, @binPacketCache, @intPacketRowCount, 0)
INSERT INTO [dbo].[QueryCachePacket]
([idfQueryCachePacket],[idfQueryCache],[intQueryCachePacketNumber],[blbQueryCachePacket], [intTableRowCount],[blnArchivedData])
VALUES(@idflQueryCachePacket2, @idflQueryCache, 1, @binPacketCache, @intPacketRowCount, 1)
SET IDENTITY_INSERT [dbo].[QueryCachePacket] OFF",
manager.Parameter("idflQueryCache", 1),
manager.Parameter("idflQuery", 2),
manager.Parameter("strLanguage", "en"),
manager.Parameter("idflQueryCachePacket1", 1),
manager.Parameter("idflQueryCachePacket2", 2),
manager.Parameter("binHeaderCache", header),
manager.Parameter("binPacketCache", packet),
manager.Parameter("intHeaderRowCount", 10),
manager.Parameter("intPacketRowCount", 100)
);
command.ExecuteNonQuery();
avrTran.CommitTransaction();
}
Assert.AreEqual(1, AvrDbHelper.GetQueryCacheId(2, "en", false));
QueryTableHeaderDTO resultHeader = AvrDbHelper.GetQueryCacheHeader(1, false, false);
Assert.AreEqual(10, resultHeader.BinaryHeader.RowCount);
Assert.AreEqual(1, resultHeader.PacketCount);
Assert.AreEqual(1, resultHeader.QueryCacheId);
Assert.AreEqual(false, resultHeader.BinaryHeader.IsArchive);
Assert.AreEqual(1, AvrDbHelper.GetQueryCacheId(2, "en", true));
resultHeader = AvrDbHelper.GetQueryCacheHeader(1, false, true);
Assert.AreEqual(10, resultHeader.BinaryHeader.RowCount);
Assert.AreEqual(2, resultHeader.PacketCount);
Assert.AreEqual(1, resultHeader.QueryCacheId);
Assert.AreEqual(true, resultHeader.BinaryHeader.IsArchive);
QueryTablePacketDTO resultPacket1 = AvrDbHelper.GetQueryCachePacket(1, 0);
Assert.AreEqual(20000, resultPacket1.BinaryBody.Length);
Assert.AreEqual(false, resultPacket1.IsArchive);
QueryTablePacketDTO resultPacket2 = AvrDbHelper.GetQueryCachePacket(1, 1);
Assert.AreEqual(20000, resultPacket2.BinaryBody.Length);
Assert.AreEqual(true, resultPacket2.IsArchive);
}
public long GetScalar(DbManagerProxy manager, T t, params object[] pars)
{
return(manager.SetCommand("select dbo.fnSiteID()").ExecuteScalar <long>());
}