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>()); }