public static QueryTableHeaderDTO GetQueryCacheHeader(long queryCacheId, bool isSchedulerCall, bool isArchive)
{
using (var avrTran = new AvrDbTransaction())
{
DbManagerProxy manager = avrTran.Manager;
DbManager command = manager.SetSpCommand("spAsQueryCacheGetHeader",
manager.Parameter("idfQueryCache", queryCacheId),
manager.Parameter("blnSchedulerCall", isSchedulerCall),
manager.Parameter("blnUseArchivedData", isArchive));
QueryTablePacketDTO headerPacket = new QueryTablePacketDTO {
IsArchive = isArchive
};
int packetCount = 0;
lock (m_DbSyncLock)
{
using (IDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
packetCount = (int)reader["intPacketCount"];
headerPacket.RowCount = (int)reader["intQueryColumnCount"];
var binaryBody = (byte[])reader["blbQuerySchema"];
headerPacket.BinaryBody = new ChunkByteArray(binaryBody);
}
}
avrTran.CommitTransaction();
}
return(new QueryTableHeaderDTO(headerPacket, queryCacheId, packetCount));
}
}
public void DeserializeHeaderTest()
{
List <BaseColumnModel> resultHeader;
using (var manager = new DbManager(new SqlDataProvider(), Config.GetSetting("TestConnectionString")))
{
using (IDbCommand command = manager.Connection.CreateCommand())
{
command.CommandText = @"select * from dbo.AVR_HumanCaseReport";
using (IDataReader reader = command.ExecuteReader())
{
List <BaseColumnModel> columnModels = BinarySerializer.GetSchemaColumnModels(reader.GetSchemaTable());
BinarySerializer.SerializeBodyPacket(reader, columnModels, false);
QueryTablePacketDTO header = BinarySerializer.SerializeHeader(columnModels, false);
resultHeader = BinarySerializer.DeserializeHeader(header);
}
}
}
Assert.IsTrue(resultHeader.Exists(c => c.Name == "sflHC_PatientDOB"));
Assert.IsTrue(resultHeader.Exists(c => c.Name == "sflHC_PatientSex_ID"));
Assert.IsTrue(resultHeader.Exists(c => c.Name == "sflHC_LabDiagBasis"));
Assert.IsTrue(resultHeader.Exists(c => c.Name == "sflHC_CS__10034010__9253110000000"));
Assert.AreEqual(typeof(DateTime), resultHeader.Find(c => c.Name == "sflHC_PatientDOB").FinalType);
Assert.AreEqual(typeof(long), resultHeader.Find(c => c.Name == "sflHC_PatientSex_ID").FinalType);
Assert.AreEqual(typeof(string), resultHeader.Find(c => c.Name == "sflHC_LabDiagBasis").FinalType);
Assert.AreEqual(typeof(DateTime), resultHeader.Find(c => c.Name == "sflHC_CS__10034010__9253110000000").FinalType);
}
public void SerializeIntegrationArchiveTest()
{
QueryTableModel tableModel;
using (var manager = new DbManager(new SqlDataProvider(), Config.GetSetting("TestConnectionString")))
{
using (DbManager command = manager.SetCommand(@"select * from dbo.AVR_HumanCaseReport"))
{
tableModel = BinarySerializer.SerializeFromCommand(command, 123, "en", true, 10);
}
}
Assert.AreEqual(54, tableModel.Header.RowCount);
Assert.AreEqual(true, tableModel.UseArchivedData);
Assert.AreEqual(true, tableModel.Header.IsArchive);
QueryTablePacketDTO zippedHeader = BinaryCompressor.Zip(tableModel.Header);
Assert.AreEqual(true, zippedHeader.IsArchive);
List <QueryTablePacketDTO> zippedBody = tableModel.BodyPackets.Select(BinaryCompressor.Zip).ToList();
Assert.IsFalse(zippedBody.Any(p => !p.IsArchive));
QueryTablePacketDTO unzipedHeader = BinaryCompressor.Unzip(zippedHeader);
Assert.AreEqual(true, unzipedHeader.IsArchive);
List <QueryTablePacketDTO> unzippedBody = zippedBody.Select(BinaryCompressor.Unzip).ToList();
Assert.IsFalse(unzippedBody.Any(p => !p.IsArchive));
}
private static void TestInternalSaveTable(QueryTableModel source)
{
QueryTableModel actualTable = GetTestTableModel();
AvrDbHelper.InvalidateQueryCache(actualTable.QueryId, "en");
Assert.IsFalse(AvrDbHelper.GetQueryCacheId(source.QueryId, "en", false).HasValue);
AvrDbHelper.SaveQueryCache(source);
// manager.CommitTransaction();
long?id = AvrDbHelper.GetQueryCacheId(source.QueryId, "en", false);
Assert.IsTrue(id.HasValue);
QueryTableHeaderDTO resultHeader = AvrDbHelper.GetQueryCacheHeader(id.Value, false, false);
Assert.IsNotNull(resultHeader);
Assert.IsNotNull(resultHeader.BinaryHeader);
Assert.AreEqual(id.Value, resultHeader.QueryCacheId);
Assert.AreEqual(source.BodyPackets.Count, resultHeader.PacketCount);
Assert.AreEqual(source.Header.RowCount, resultHeader.BinaryHeader.RowCount);
AssertAreArrayEqual(source.Header.BinaryBody, resultHeader.BinaryHeader.BinaryBody);
for (int i = 0; i < resultHeader.PacketCount; i++)
{
QueryTablePacketDTO resultPacket = AvrDbHelper.GetQueryCachePacket(id.Value, i);
Assert.IsNotNull(resultPacket);
Assert.IsNotNull(resultPacket.BinaryBody);
QueryTablePacketDTO sourcePacket = source.BodyPackets[i];
Assert.IsNotNull(sourcePacket);
Assert.IsNotNull(sourcePacket.BinaryBody);
Assert.AreEqual(sourcePacket.RowCount, resultPacket.RowCount);
AssertAreArrayEqual(sourcePacket.BinaryBody, resultPacket.BinaryBody);
}
}
public QueryTablePacketDTO GetCachedQueryTablePacket(long queryCasheId, int packetNumber, int totalPacketCount)
{
try
{
if (queryCasheId < 0)
{
return(new QueryTablePacketDTO());
}
TraceMethodCall(queryCasheId, packetNumber);
QueryTablePacketDTO packet = AvrDbHelper.GetQueryCachePacket(queryCasheId, packetNumber);
m_Trace.Trace(m_TraceTitle, "Packet {0} of {1} for Query Cashe {2} received", packetNumber + 1, totalPacketCount,
queryCasheId);
return(packet);
}
catch (Exception ex)
{
m_Trace.TraceMethodException(ex, Utils.GetCurrentMethodName(), m_TraceTitle, queryCasheId, packetNumber);
string format = EidssMessages.Get("msgCouldNotGetQueryCachePacket",
"Could not get packet of query cashe table. QueryCasheID={0}, Packet No={1}");
string msg = String.Format(format, queryCasheId, packetNumber + 1);
throw new AvrDataException(msg, ex);
}
}
public static QueryTableModel SerializeFromCommand
(DbManager command, long queryId, string lang, bool isArchive, int maxPacketRows = 0)
{
if (maxPacketRows <= 0)
{
maxPacketRows = MaxPacketRows;
}
using (IDataReader reader = command.ExecuteReader())
{
if (reader == null)
{
throw new CustomSerializationException(string.Format("Could not get DataReader from command {0}",
command.Command.CommandText));
}
List <BaseColumnModel> columnModels = GetSchemaColumnModels(reader.GetSchemaTable());
var result = new QueryTableModel(queryId, lang)
{
UseArchivedData = isArchive
};
QueryTablePacketDTO packet = SerializeBodyPacket(reader, columnModels, isArchive, maxPacketRows);
while (packet.RowCount != 0)
{
result.BodyPackets.Add(packet);
packet = SerializeBodyPacket(reader, columnModels, isArchive, maxPacketRows);
}
// note : header should be serialized afer body because of possible columnModels change inside SerializeBodyPacket
result.Header = SerializeHeader(columnModels, isArchive);
return(result);
}
}
internal static QueryTablePacketDTO SerializeHeader(List <BaseColumnModel> columnModelList, bool isArchive)
{
BaseTablePacketDTO baseHeader = SerializeHeader(columnModelList);
QueryTablePacketDTO header = QueryTablePacketDTO.FromBaseTablePacketDTO(baseHeader, isArchive);
return(header);
}
internal static QueryTablePacketDTO SerializeBodyPacket
(IDataReader reader, List <BaseColumnModel> columnModels, bool isArchive, int maxPacketRows = 0)
{
BaseTablePacketDTO basePacket = SerializeBodyPacket(reader, columnModels, maxPacketRows);
QueryTablePacketDTO packet = QueryTablePacketDTO.FromBaseTablePacketDTO(basePacket, isArchive);
return(packet);
}
public static QueryTablePacketDTO Zip(QueryTablePacketDTO sourcePacket)
{
var result = new QueryTablePacketDTO
{
RowCount = sourcePacket.RowCount,
BinaryBody = Zip(sourcePacket.BinaryBody),
IsArchive = sourcePacket.IsArchive
};
return(result);
}
public static StreamTablePacketDTO UnzipStream
(QueryTablePacketDTO sourcePacket, byte[] inputBuffer = null, byte[] outputBuffer = null)
{
var stream = UnzipStream(sourcePacket.BinaryBody, ref inputBuffer, ref outputBuffer);
var result = new StreamTablePacketDTO
{
RowCount = sourcePacket.RowCount,
Stream = stream,
InputBuffer = inputBuffer,
OutputBuffer = outputBuffer,
IsArchive = sourcePacket.IsArchive
};
return(result);
}
public void TestGetNonExistingTable()
{
Assert.IsNull(AvrDbHelper.GetQueryCacheId(1234, "en", false));
Assert.IsNull(AvrDbHelper.GetQueryCacheId(new QueryCacheKey(1234, "en", false)));
QueryTableHeaderDTO header = AvrDbHelper.GetQueryCacheHeader(1234, false, false);
QueryTablePacketDTO packet = AvrDbHelper.GetQueryCachePacket(2310, 0);
Assert.IsNotNull(header);
Assert.IsNotNull(header.BinaryHeader);
Assert.AreEqual(0, header.PacketCount);
Assert.AreEqual(1234, header.QueryCacheId);
Assert.AreEqual(0, header.BinaryHeader.RowCount);
Assert.AreEqual(0, header.BinaryHeader.BinaryBody.Length);
Assert.AreEqual(0, packet.RowCount);
Assert.AreEqual(0, packet.BinaryBody.Length);
}
public void PacketCompressTest()
{
var source = new QueryTablePacketDTO
{
RowCount = 1,
BinaryBody = new ChunkByteArray(new byte[] { 1, 2, 3 }),
IsArchive = true
};
QueryTablePacketDTO zip = BinaryCompressor.Zip(source);
QueryTablePacketDTO result = BinaryCompressor.Unzip(zip);
Assert.AreEqual(source.RowCount, result.RowCount);
Assert.AreEqual(source.BinaryBody.Length, result.BinaryBody.Length);
Assert.AreEqual(source.IsArchive, result.IsArchive);
for (int i = 0; i < source.BinaryBody.Length; i++)
{
Assert.AreEqual(source.BinaryBody[i], result.BinaryBody[i]);
}
}
public static QueryTablePacketDTO GetQueryCachePacket(long queryCasheId, int packetNumber)
{
using (var avrTran = new AvrDbTransaction())
{
DbManager command = avrTran.Manager.SetSpCommand("spAsQueryCacheGetPacket",
avrTran.Manager.Parameter("idfQueryCache", queryCasheId),
avrTran.Manager.Parameter("intQueryCachePacketNumber", packetNumber));
var result = new QueryTablePacketDTO();
using (IDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
result.RowCount = (int)reader["intTableRowCount"];
result.BinaryBody = new ChunkByteArray((byte[])reader["blbQueryCachePacket"]);
result.IsArchive = (bool)reader["blnArchivedData"];
}
}
avrTran.CommitTransaction();
return(result);
}
}
public void SerializeBodyTest()
{
List <BaseColumnModel> columnModels;
IList <QueryTablePacketDTO> bodyPackets = new List <QueryTablePacketDTO>();
using (var manager = new DbManager(new SqlDataProvider(), Config.GetSetting("TestConnectionString")))
{
using (IDbCommand command = manager.Connection.CreateCommand())
{
command.CommandText = @"select * from dbo.AVR_HumanCaseReport";
using (IDataReader reader = command.ExecuteReader())
{
columnModels = BinarySerializer.GetSchemaColumnModels(reader.GetSchemaTable());
BinarySerializer.SerializeHeader(columnModels, false);
QueryTablePacketDTO packet = BinarySerializer.SerializeBodyPacket(reader, columnModels, false, 10);
while (packet.RowCount != 0)
{
bodyPackets.Add(packet);
packet = BinarySerializer.SerializeBodyPacket(reader, columnModels, false, 10);
}
}
}
}
Assert.AreEqual(4, bodyPackets.Count);
Assert.AreEqual(10, bodyPackets[0].RowCount);
Assert.AreEqual(10, bodyPackets[1].RowCount);
Assert.AreEqual(10, bodyPackets[2].RowCount);
Assert.AreEqual(1, bodyPackets[3].RowCount);
var processed = new object[columnModels.Count];
using (Stream stream = new MemoryStream())
{
stream.Write(bodyPackets[3].BinaryBody, 0, bodyPackets[3].BinaryBody.Length);
stream.Flush();
stream.Seek(0, SeekOrigin.Begin);
using (var reader = new BinaryReader(stream))
{
for (int j = 0; j < columnModels.Count; j++)
{
bool hasValue = reader.ReadBoolean();
if (hasValue)
{
Type type = columnModels[j].FinalType;
if (type == typeof(String))
{
processed[j] = reader.ReadString();
}
else if (type == typeof(Int64))
{
processed[j] = reader.ReadInt64();
}
else if (type == typeof(Int32))
{
processed[j] = reader.ReadInt32();
}
else if (type == typeof(Int16))
{
processed[j] = reader.ReadInt16();
}
else if (type == typeof(DateTime))
{
processed[j] = new DateTime(reader.ReadInt64());
}
else if (type == typeof(Double))
{
processed[j] = reader.ReadDouble();
}
else if (type == typeof(Decimal))
{
processed[j] = reader.ReadDecimal();
}
else if (type == typeof(Single))
{
processed[j] = reader.ReadSingle();
}
else if (type == typeof(Boolean))
{
processed[j] = reader.ReadBoolean();
}
else if (type == typeof(Byte))
{
processed[j] = reader.ReadByte();
}
}
else
{
processed[j] = DBNull.Value;
}
}
}
stream.Close();
}
Assert.AreEqual(new DateTime(1990, 02, 01), processed[0]);
Assert.AreEqual(23, processed[1]);
Assert.AreEqual(DBNull.Value, processed[3]);
Assert.AreEqual("xxx", processed[4]);
Assert.AreEqual("Male", processed[6]);
Assert.AreEqual(DBNull.Value, processed[7]);
Assert.AreEqual(DateTime.Now.Year, ((DateTime)processed[50]).Year);
Assert.AreEqual(DateTime.Now.Year, processed[51]);
Assert.AreEqual(25460000000m, processed[52]);
Assert.AreEqual("Yes", processed[53]);
}
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 DeserializeBodyTest()
{
List <BaseColumnModel> columnModels;
IList <QueryTablePacketDTO> bodyPackets = new List <QueryTablePacketDTO>();
using (var manager = new DbManager(new SqlDataProvider(), Config.GetSetting("TestConnectionString")))
{
using (IDbCommand command = manager.Connection.CreateCommand())
{
command.CommandText = @"select * from dbo.AVR_HumanCaseReport";
using (IDataReader reader = command.ExecuteReader())
{
columnModels = BinarySerializer.GetSchemaColumnModels(reader.GetSchemaTable());
BinarySerializer.SerializeHeader(columnModels, false);
QueryTablePacketDTO packet = BinarySerializer.SerializeBodyPacket(reader, columnModels, false, 10);
while (packet.RowCount != 0)
{
bodyPackets.Add(packet);
packet = BinarySerializer.SerializeBodyPacket(reader, columnModels, false, 10);
}
}
}
}
Assert.AreEqual(4, bodyPackets.Count);
Type[] types = columnModels.Select(c => c.FinalType).ToArray();
AvrDataTable array = new AvrDataTable(new DataTable());
for (int i = 0; i < 4; i++)
{
BinarySerializer.DeserializeBodyPacket(bodyPackets[i], types, array);
}
array.AcceptChanges();
Assert.AreEqual(31, array.Count);
Assert.AreEqual(54, array[0].Count);
AvrDataRowEx row3 = (AvrDataRowEx)array[3];
Assert.AreEqual(typeof(string), row3[53].GetType());
Assert.AreEqual("2", row3[53].ToString());
AvrDataRowEx row5 = (AvrDataRowEx)array[5];
Assert.AreEqual(typeof(DateTime), row5[50].GetType());
Assert.AreEqual(2010, ((DateTime)row5[50]).Year);
AvrDataRowEx row8 = (AvrDataRowEx)array[8];
Assert.AreEqual(typeof(decimal), row8[52].GetType());
Assert.AreEqual(0m, row8[52]);
AvrDataRowEx row = (AvrDataRowEx)array[30];
Assert.AreEqual(new DateTime(1990, 02, 01), row[0]);
Assert.AreEqual(23, row[1]);
Assert.AreEqual(DBNull.Value, row[3]);
Assert.AreEqual("xxx", row[4]);
Assert.AreEqual("Male", row[6]);
Assert.AreEqual(DBNull.Value, row[7]);
Assert.AreEqual(DateTime.Now.Year, ((DateTime)row[50]).Year);
Assert.AreEqual(DateTime.Now.Year, row[51]);
Assert.AreEqual(25460000000m, row[52]);
Assert.AreEqual("Yes", row[53]);
}
public void SerializeIntegrationTest()
{
QueryTableModel tableModel;
using (var manager = new DbManager(new SqlDataProvider(), Config.GetSetting("TestConnectionString")))
{
using (DbManager command = manager.SetCommand(@"select * from dbo.AVR_HumanCaseReport"))
{
tableModel = BinarySerializer.SerializeFromCommand(command, 123, "en", false, 10);
}
}
Assert.AreEqual(54, tableModel.Header.RowCount);
Assert.AreEqual(false, tableModel.UseArchivedData);
Assert.AreEqual(false, tableModel.Header.IsArchive);
QueryTablePacketDTO zippedHeader = BinaryCompressor.Zip(tableModel.Header);
Assert.AreEqual(false, zippedHeader.IsArchive);
List <QueryTablePacketDTO> zippedBody = tableModel.BodyPackets.Select(BinaryCompressor.Zip).ToList();
Assert.IsFalse(zippedBody.Any(p => p.IsArchive));
QueryTablePacketDTO unzipedHeader = BinaryCompressor.Unzip(zippedHeader);
Assert.AreEqual(false, unzipedHeader.IsArchive);
List <QueryTablePacketDTO> unzippedBody = zippedBody.Select(BinaryCompressor.Unzip).ToList();
Assert.IsFalse(unzippedBody.Any(p => p.IsArchive));
List <BaseColumnModel> resultHeader = BinarySerializer.DeserializeHeader(unzipedHeader);
Assert.IsTrue(resultHeader.Exists(c => c.Name == "sflHC_PatientDOB"));
Assert.IsTrue(resultHeader.Exists(c => c.Name == "sflHC_PatientSex_ID"));
Assert.IsTrue(resultHeader.Exists(c => c.Name == "sflHC_LabDiagBasis"));
Assert.AreEqual(typeof(DateTime), resultHeader.Find(c => c.Name == "sflHC_PatientDOB").FinalType);
Assert.AreEqual(typeof(long), resultHeader.Find(c => c.Name == "sflHC_PatientSex_ID").FinalType);
Assert.AreEqual(typeof(string), resultHeader.Find(c => c.Name == "sflHC_LabDiagBasis").FinalType);
Type[] types = resultHeader.Select(c => c.FinalType).ToArray();
AvrDataTable resultBody = new AvrDataTable(new DataTable());
foreach (var packetDTO in unzippedBody)
{
BinarySerializer.DeserializeBodyPacket(packetDTO, types, resultBody);
}
Assert.AreEqual(31, resultBody.Count);
Assert.AreEqual(54, resultBody[0].Count);
AvrDataRowEx row = (AvrDataRowEx)resultBody[30];
Assert.AreEqual(new DateTime(1990, 02, 01), row[0]);
Assert.AreEqual(23, row[1]);
Assert.AreEqual(DBNull.Value, row[3]);
Assert.AreEqual("xxx", row[4]);
Assert.AreEqual("Male", row[6]);
Assert.AreEqual(DBNull.Value, row[7]);
Assert.AreEqual(DateTime.Now.Year, ((DateTime)row[50]).Year);
Assert.AreEqual(DateTime.Now.Year, row[51]);
Assert.AreEqual(25460000000m, row[52]);
Assert.AreEqual("Yes", row[53]);
}
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);
}