protected virtual async Task <byte[][]> GetRawDataAsync(int fileId)
{
using (var ctx = new MsSqlDataContext(RepositoryConnectionString, new DataOptions(), CancellationToken.None))
{
var script = "SELECT [Stream] FROM Files WHERE FileId = @FileId";
var scalar = await ctx.ExecuteScalarAsync(script, cmd =>
{
cmd.Parameters.AddRange(new[]
{
ctx.CreateParameter("@FileId", SqlDbType.Int, fileId)
});
}).ConfigureAwait(false);
if (scalar == DBNull.Value)
{
return(new byte[0][]);
}
var buffer = (byte[])scalar;
if (buffer.Length == 0)
{
return(new byte[0][]);
}
return(new [] { buffer });
}
}
public void DC_MSSQL_Construction_ConnectionInfo_Named_CustomDb()
{
var connectionInfo = new ConnectionInfo
{
ConnectionName = "CustomCnStr",
InitialCatalog = InitialCatalog.Initial,
InitialCatalogName = "CustomDb"
};
// ACTION
var connectionStrings = new ConnectionStringOptions
{
Repository = "Data Source=ds;Initial Catalog=ic;Integrated Security=True",
AllConnectionStrings = new Dictionary <string, string>
{
{ "CustomCnStr", "Data Source=CustomServer;Initial Catalog=InitialCatalog1;Integrated Security=True" }
}
};
var connectionString = MsSqlDataContext.GetConnectionString(connectionInfo, connectionStrings);
// ASSERT
var expected = "Data Source=CustomServer;Initial Catalog=CustomDb;Integrated Security=True";
Assert.AreEqual(expected, connectionString);
}
public override void Execute(ExecutionContext context)
{
var connectionInfo = new ConnectionInfo
{
ConnectionName = (string)context.ResolveVariable(ConnectionName),
DataSource = (string)context.ResolveVariable(DataSource),
InitialCatalog = InitialCatalog,
InitialCatalogName = (string)context.ResolveVariable(InitialCatalogName),
UserName = (string)context.ResolveVariable(UserName),
Password = (string)context.ResolveVariable(Password)
};
var connectionString = MsSqlDataContext.GetConnectionString(connectionInfo);
var initialData = InitialData.Load(new SenseNetServicesInitialData(), null);
var dataOptions = Options.Create(DataOptions.GetLegacyConfiguration());
var connOptions = Options.Create(new ConnectionStringOptions
{
ConnectionString = connectionString
});
var installer = new MsSqlDataInstaller(connOptions, NullLoggerFactory.Instance.CreateLogger <MsSqlDataInstaller>());
var dataProvider = new MsSqlDataProvider(dataOptions, connOptions, installer);
installer.InstallInitialDataAsync(initialData, dataProvider, CancellationToken.None).GetAwaiter().GetResult();
}
public async Task UpdatePatientsAsync_WhenValidPatients_ThenSuccess()
{
var patients = new Patient
{
Id = 5,
CardNumber = "Новый номер пациента 2",
LastName = "Новый пациент 2",
FirstName = "Новый пациент 2",
MiddleName = "Новый пациент 2",
Sex = "Новый пол пациента 2",
Birthdate = DateTime.Parse("23.03.1984"),
RegistrationDate = DateTime.Parse("13.05.2005"),
Residence = "Новая улица пациента 2",
PassportNumber = "Нвоый номер паспорта 2",
PassportSeries = "Новая серия паспорта 2",
PassportUFMS = "ТП №13 отдела УФМС России 2",
PassportIssueDate = DateTime.Parse("11.11.1995"),
PassportDepCode = "Новый код паспорта 2"
};
IDataContext sut = new MsSqlDataContext();
Result <string> result = await sut.Patients.UpdatePatientAsync(patients);
Assert.IsTrue(result.HasValue);
}
private void ExecuteSql(string script, ExecutionContext context)
{
var connectionInfo = new ConnectionInfo
{
ConnectionName = null,
DataSource = (string)context.ResolveVariable(DataSource),
InitialCatalog = InitialCatalog.Initial,
InitialCatalogName = (string)context.ResolveVariable(InitialCatalogName),
UserName = (string)context.ResolveVariable(UserName),
Password = (string)context.ResolveVariable(Password)
};
var connectionString = MsSqlDataContext.GetConnectionString(connectionInfo) ?? ConnectionStrings.ConnectionString;
//TODO: [DIREF] get options from DI through constructor
using (var ctx = new MsSqlDataContext(connectionString, DataOptions.GetLegacyConfiguration(), CancellationToken.None))
{
ctx.ExecuteReaderAsync(script, async(reader, cancel) =>
{
do
{
if (reader.HasRows)
{
while (await reader.ReadAsync(cancel).ConfigureAwait(false))
{
// empty code block, created only for checking the connection
}
}
} while (reader.NextResult());
return(Task.FromResult(0));
}).GetAwaiter().GetResult();
}
}
public void DC_MSSQL_Construction_ConnectionString()
{
var connectionString = "ConnectionString1";
var dataContext = new MsSqlDataContext(connectionString, CancellationToken.None);
Assert.AreEqual(connectionString, dataContext.ConnectionString);
}
public override void Execute(ExecutionContext context)
{
var count = 0;
using (var ctx = new MsSqlDataContext(CancellationToken.None))
{
ctx.ExecuteReaderAsync(Script, async(reader, cancel) =>
{
do
{
if (!reader.HasRows)
{
continue;
}
using (new SystemAccount())
{
while (await reader.ReadAsync(cancel).ConfigureAwait(false))
{
Operate(reader.GetInt32(0));
count++;
}
}
} while (reader.NextResult());
return(Task.FromResult(0));
}).GetAwaiter().GetResult();
}
Logger.LogMessage(count < 1
? "No content was found with aspect reference field."
: $"Aspect references were updated on {count} content.");
}
public async Task EnumerateDataAsync(string dataType, DateTime startTime, DateTime endTimeExclusive,
Action <IStatisticalDataRecord> aggregatorCallback,
CancellationToken cancellation)
{
using (var ctx = new MsSqlDataContext(ConnectionString, DataOptions, CancellationToken.None))
{
await ctx.ExecuteReaderAsync(EnumerateDataScript, cmd =>
{
cmd.Parameters.AddRange(new[]
{
ctx.CreateParameter("@DataType", DbType.String, dataType),
ctx.CreateParameter("@StartTime", DbType.DateTime2, startTime),
ctx.CreateParameter("@EndTimeExclusive", DbType.DateTime2, endTimeExclusive),
});
}, async (reader, cancel) =>
{
while (await reader.ReadAsync(cancel))
{
cancel.ThrowIfCancellationRequested();
var item = GetStatisticalDataRecordFromReader(reader);
aggregatorCallback(item);
}
return(true);
}).ConfigureAwait(false);
}
}
public async Task <DateTime?[]> LoadFirstAggregationTimesByResolutionsAsync(string dataType, CancellationToken cancellation)
{
var result = new DateTime?[4];
using (var ctx = new MsSqlDataContext(ConnectionString, DataOptions, CancellationToken.None))
{
await ctx.ExecuteReaderAsync(LoadFirstAggregationTimesByResolutionsScript, cmd =>
{
cmd.Parameters.AddRange(new[]
{
ctx.CreateParameter("@DataType", DbType.String, dataType),
});
}, async (reader, cancel) => {
if (await reader.ReadAsync(cancel))
{
result[0] = reader.GetDateTimeUtcOrNull("Minute");
result[1] = reader.GetDateTimeUtcOrNull("Hour");
result[2] = reader.GetDateTimeUtcOrNull("Day");
result[3] = reader.GetDateTimeUtcOrNull("Month");
}
return(true);
}).ConfigureAwait(false);
}
return(result.ToArray());
}
internal AssignedTaskResult AssignTasks(int taskCount, int timeoutInMinutes, CancellationToken cancellationToken)
{
var result = new List <int>();
int remainingTasks = 0;
using (var ctx = new MsSqlDataContext(_connectionStrings.Repository, _dataOptions, cancellationToken))
{
ctx.ExecuteReaderAsync(SqlScripts.AssignTasks, cmd =>
{
cmd.Parameters.Add("@AssignedTaskCount", SqlDbType.Int, taskCount);
cmd.Parameters.Add("@TimeOutInMinutes", SqlDbType.Int, timeoutInMinutes);
}, async(reader, cancel) =>
{
while (await reader.ReadAsync(cancel).ConfigureAwait(false))
{
result.Add(reader.GetInt32(0));
}
await reader.NextResultAsync(cancel).ConfigureAwait(false);
await reader.ReadAsync(cancel).ConfigureAwait(false);
remainingTasks = reader.GetInt32(0);
return(Task.FromResult(0));
}).GetAwaiter().GetResult();
}
return(new AssignedTaskResult {
VersionIds = result.ToArray(), RemainingTaskCount = remainingTasks
});
}
public async Task <IEnumerable <Aggregation> > LoadAggregatedUsageAsync(string dataType, TimeResolution resolution, DateTime startTime, DateTime endTimeExclusive,
CancellationToken cancellation)
{
var aggregations = new List <Aggregation>();
using (var ctx = new MsSqlDataContext(ConnectionString, DataOptions, CancellationToken.None))
{
await ctx.ExecuteReaderAsync(LoadAggregatedUsageScript, cmd =>
{
cmd.Parameters.AddRange(new[]
{
ctx.CreateParameter("@DataType", DbType.String, dataType),
ctx.CreateParameter("@Resolution", DbType.String, resolution.ToString()),
ctx.CreateParameter("@StartTime", DbType.DateTime2, startTime),
ctx.CreateParameter("@EndTimeExclusive", DbType.DateTime2, endTimeExclusive),
});
}, async (reader, cancel) => {
while (await reader.ReadAsync(cancel))
{
aggregations.Add(GetAggregationFromReader(reader));
}
return(true);
}).ConfigureAwait(false);
}
return(aggregations);
}
public async Task WriteDataAsync(IStatisticalDataRecord data, CancellationToken cancellation)
{
using (var ctx = new MsSqlDataContext(ConnectionString, DataOptions, CancellationToken.None))
{
await ctx.ExecuteNonQueryAsync(WriteDataScript, cmd =>
{
var now = DateTime.UtcNow;
cmd.Parameters.AddRange(new[]
{
ctx.CreateParameter("@DataType", SqlDbType.NVarChar, 50, data.DataType),
ctx.CreateParameter("@WrittenTime", SqlDbType.DateTime2, now),
ctx.CreateParameter("@CreationTime", SqlDbType.DateTime2, data.CreationTime ?? now),
ctx.CreateParameter("@Duration", SqlDbType.BigInt, (object)data.Duration?.Ticks ?? DBNull.Value),
ctx.CreateParameter("@RequestLength", SqlDbType.BigInt, (object)data.RequestLength ?? DBNull.Value),
ctx.CreateParameter("@ResponseLength", SqlDbType.BigInt, (object)data.ResponseLength ?? DBNull.Value),
ctx.CreateParameter("@ResponseStatusCode", SqlDbType.Int, (object)data.ResponseStatusCode ?? DBNull.Value),
ctx.CreateParameter("@Url", SqlDbType.NVarChar, 1000, (object)data.Url ?? DBNull.Value),
ctx.CreateParameter("@TargetId", SqlDbType.Int, (object)data.TargetId ?? DBNull.Value),
ctx.CreateParameter("@ContentId", SqlDbType.Int, (object)data.ContentId ?? DBNull.Value),
ctx.CreateParameter("@EventName", SqlDbType.NVarChar, 50, (object)data.EventName ?? DBNull.Value),
ctx.CreateParameter("@ErrorMessage", SqlDbType.NVarChar, 500, (object)data.ErrorMessage ?? DBNull.Value),
ctx.CreateParameter("@GeneralData", SqlDbType.NVarChar, (object)data.GeneralData ?? DBNull.Value),
});
}).ConfigureAwait(false);
}
}
/* ===================================================================================== SQL DATA HANDLING */
private static async Task <TimestampData> GetTimestampDataForOneNodeIntegrityCheckAsync(string path, int[] excludedNodeTypeIds)
{
var checkNodeSql = "SELECT N.NodeId, V.VersionId, CONVERT(bigint, n.timestamp) NodeTimestamp, CONVERT(bigint, v.timestamp) VersionTimestamp, N.LastMajorVersionId, N.LastMinorVersionId from Versions V join Nodes N on V.NodeId = N.NodeId WHERE N.Path = '{0}' COLLATE Latin1_General_CI_AS";
if (excludedNodeTypeIds != null && excludedNodeTypeIds.Length > 0)
{
checkNodeSql += $" AND N.NodeTypeId NOT IN ({string.Join(", ", excludedNodeTypeIds)})";
}
var sql = string.Format(checkNodeSql, path);
using var ctx = new MsSqlDataContext(ConnectionStrings.ConnectionString,
DataOptions.GetLegacyConfiguration(), CancellationToken.None);
return(await ctx.ExecuteReaderAsync(sql, async (reader, cancel) =>
{
cancel.ThrowIfCancellationRequested();
TimestampData dbNode = null;
if (await reader.ReadAsync(cancel).ConfigureAwait(false))
{
dbNode = new TimestampData
{
NodeId = reader.GetSafeInt32(reader.GetOrdinal("NodeId")),
VersionId = reader.GetSafeInt32(reader.GetOrdinal("VersionId")),
NodeTimestamp = reader.GetSafeInt64(reader.GetOrdinal("NodeTimestamp")),
VersionTimestamp = reader.GetSafeInt64(reader.GetOrdinal("VersionTimestamp")),
LastMajorVersionId = reader.GetSafeInt32(reader.GetOrdinal("LastMajorVersionId")),
LastMinorVersionId = reader.GetSafeInt32(reader.GetOrdinal("LastMinorVersionId")),
};
}
return dbNode;
}).ConfigureAwait(false));
}
private void ResetDatabase(RepositoryBuilder builder)
{
using (var ctx = new MsSqlDataContext(Platform.RepositoryConnectionString, DataOptions.GetLegacyConfiguration(), CancellationToken.None))
{
ctx.ExecuteNonQueryAsync(MsSqlStatisticalDataProvider.DropScript).GetAwaiter().GetResult();
ctx.ExecuteNonQueryAsync(MsSqlStatisticalDataProvider.CreationScript).GetAwaiter().GetResult();
}
}
public async Task GetLastAddedPatientIdAsync_ReturnsPatientsId()
{
IDataContext sut = new MsSqlDataContext();
Result <int> result = await sut.Patients.GetLastAddedPatientIdAsync();
Assert.IsTrue(result.HasValue);
}
internal void FinishTask(int versionId, CancellationToken cancellationToken)
{
using (var ctx = new MsSqlDataContext(_connectionStrings.Repository, _dataOptions, cancellationToken))
ctx.ExecuteNonQueryAsync(SqlScripts.FinishTask, cmd =>
{
cmd.Parameters.Add("@VersionId", SqlDbType.Int, versionId);
}).GetAwaiter().GetResult();
}
internal static void FinishTask(int versionId, CancellationToken cancellationToken)
{
using (var ctx = new MsSqlDataContext(cancellationToken))
ctx.ExecuteNonQueryAsync(SqlScripts.FinishTask, cmd =>
{
cmd.Parameters.Add("@VersionId", SqlDbType.Int, versionId);
}).GetAwaiter().GetResult();
}
public async Task GetMedicationsAsync_ReturnsListMedications()
{
IDataContext sut = new MsSqlDataContext();
Result <List <Medications> > result = await sut.Medications.GetMedicationsAsync();
Assert.IsTrue(result.HasValue);
Assert.IsTrue(result.Value.Count > 0);
}
public async Task GetHealthGroupsAsync_ReturnsListHealthGroups()
{
IDataContext sut = new MsSqlDataContext();
Result <List <HealthGroup> > result = await sut.HealthGroups.GetHealthGroupsAsync();
Assert.IsTrue(result.HasValue);
Assert.IsTrue(result.Value.Count > 0);
}
public async Task RemoveMedicationsAsync_WhenValidMedicationsId_ThenSuccess()
{
int id = 1;
IDataContext sut = new MsSqlDataContext();
Result <string> result = await sut.Medications.RemoveMedicationsAsync(id);
Assert.IsTrue(result.HasValue);
}
private void ExecuteSql(SqlScriptReader sqlReader, ExecutionContext context)
{
var connectionInfo = new ConnectionInfo
{
ConnectionName = (string)context.ResolveVariable(ConnectionName),
DataSource = (string)context.ResolveVariable(DataSource),
InitialCatalog = InitialCatalog,
InitialCatalogName = (string)context.ResolveVariable(InitialCatalogName),
UserName = (string)context.ResolveVariable(UserName),
Password = (string)context.ResolveVariable(Password)
};
var connectionString = MsSqlDataContext.GetConnectionString(connectionInfo, context.ConnectionStrings)
?? context.ConnectionStrings.Repository;
while (sqlReader.ReadScript())
{
var script = sqlReader.Script;
var sb = new StringBuilder();
//TODO: [DIREF] get options from DI through constructor
using (var ctx = new MsSqlDataContext(connectionString, DataOptions.GetLegacyConfiguration(), CancellationToken.None))
{
ctx.ExecuteReaderAsync(script, async(reader, cancel) =>
{
do
{
if (reader.HasRows)
{
var first = true;
while (await reader.ReadAsync(cancel).ConfigureAwait(false))
{
if (first)
{
for (int i = 0; i < reader.FieldCount; i++)
{
sb.Append(reader.GetName(i)).Append("\t");
}
Logger.LogMessage(sb.ToString());
sb.Clear();
first = false;
}
for (int i = 0; i < reader.FieldCount; i++)
{
sb.Append(reader[i]).Append("\t");
}
Logger.LogMessage(sb.ToString());
sb.Clear();
}
}
} while (await reader.NextResultAsync(cancel).ConfigureAwait(false));
return(Task.FromResult(0));
}).GetAwaiter().GetResult();
}
}
Logger.LogMessage("Script is successfully executed.");
}
/* ========================================================================================= */
public static void CreateTempTask(int versionId, int rank, CancellationToken cancellationToken)
{
using (var ctx = new MsSqlDataContext(ConnectionStrings.ConnectionString, DataOptions.GetLegacyConfiguration(), cancellationToken))
ctx.ExecuteNonQueryAsync(SqlScripts.FinishTask, cmd =>
{
cmd.Parameters.Add("@VersionId", SqlDbType.Int, versionId);
cmd.Parameters.Add("@Rank", SqlDbType.Int, rank);
}).GetAwaiter().GetResult();
}
public async Task GetProceduresAsync_ReturnsListProcedures()
{
IDataContext sut = new MsSqlDataContext();
Result <List <Procedure> > result = await sut.Procedures.GetProceduresAsync();
Assert.IsTrue(result.HasValue);
Assert.IsTrue(result.Value.Count > 0);
}
public async Task GetDoctorsAsync_ReturnsListDoctors()
{
IDataContext sut = new MsSqlDataContext();
Result <List <Doctor> > result = await sut.Doctors.GetDoctorsAsync();
Assert.IsTrue(result.HasValue);
Assert.IsTrue(result.Value.Count > 0);
}
public async Task RemoveProceduresAsync_WhenValidProceduresId_ThenSuccess()
{
int id = 5;
IDataContext sut = new MsSqlDataContext();
Result <string> result = await sut.Procedures.RemoveProcedureAsync(id);
Assert.IsTrue(result.HasValue);
}
public async Task GetMedicationsOrderByAsync_WhenOrderByName_ReturnsListMedications()
{
var key = "Name";
IDataContext sut = new MsSqlDataContext();
Result <List <Medications> > result = await sut.Medications.GetMedicationsOrderByAsync(key);
Assert.IsTrue(result.HasValue);
Assert.IsTrue(result.Value.Count > 0);
}
public async Task GetPatientsByCardNumberAsync_ReturnListPatientsByCardNumber()
{
var key = "2345";
IDataContext sut = new MsSqlDataContext();
Result <List <Patient> > result = await sut.Patients.GetPatientsByCardNumberAsync(key);
Assert.IsTrue(result.HasValue);
Assert.IsTrue(result.Value.Count > 0);
}
public async Task GetProceduresOrderByAsync_WhenOrderByDescription_ReturnsListProcedures()
{
var key = "Description";
IDataContext sut = new MsSqlDataContext();
Result <List <Procedure> > result = await sut.Procedures.GetProceduresOrderByAsync(key);
Assert.IsTrue(result.HasValue);
Assert.IsTrue(result.Value.Count > 0);
}
public async Task GetDiagnosisByIdAsync_WhenNotValidId_ReturnsError()
{
var id = 1000;
IDataContext sut = new MsSqlDataContext();
Result <Diagnosis> result = await sut.Diagnoses.GetDiagnosisByIdAsync(id);
Assert.IsFalse(result.HasValue);
Assert.IsFalse(string.IsNullOrEmpty(result.Error));
}
public DateTime LoadTimeLimit(CancellationToken cancellationToken)
{
using (var ctx = new MsSqlDataContext(_connectionStrings.Repository, _dataOptions, cancellationToken))
{
var result = ctx.ExecuteScalarAsync(SqlScripts.SelectTimeLimit).GetAwaiter().GetResult();
var timeLimit = Convert.ToDateTime(result).ToUniversalTime();
Tracer.Write("UTC timelimit: " + timeLimit.ToString("yyyy-MM-dd HH:mm:ss"));
return(timeLimit);
}
}
