public void Add(int?builderId, int?buildingId, int?chunkId, LogMessageTypes type, string message)
{
const string query =
"INSERT INTO [Log] ([ChunkId],[BuilderId],[BuildingId],[Type],[Time],[Message]) VALUES (@chunkId, @builderId, @buildingId, @type, @time, @message)";
using (var connection = SqlConnectionHelper.OpenConnection(connectionString))
using (var cmd = new SqlCommand(query, connection)
{
CommandTimeout = 0
})
{
cmd.Parameters.Add("@chunkId", SqlDbType.Int);
cmd.Parameters["@chunkId"].Value = chunkId.HasValue ? chunkId : (object)DBNull.Value;
cmd.Parameters.Add("@builderId", SqlDbType.Int);
cmd.Parameters["@builderId"].Value = builderId.HasValue ? builderId : (object)DBNull.Value;
cmd.Parameters.Add("@buildingId", SqlDbType.Int);
cmd.Parameters["@buildingId"].Value = buildingId.HasValue ? buildingId : (object)DBNull.Value;
cmd.Parameters.Add("@type", SqlDbType.VarChar);
cmd.Parameters["@type"].Value = type.ToString();
cmd.Parameters.Add("@time", SqlDbType.DateTime);
cmd.Parameters["@time"].Value = DateTime.Now;
cmd.Parameters.Add("@message", SqlDbType.VarChar);
cmd.Parameters["@message"].Value = message;
cmd.ExecuteNonQuery();
}
}
public void Update(int buildingId, string sourceConnectionString, string destinationConnectionString, string vocabularyConnectionString, Vendors vendor)
{
using (var connection = SqlConnectionHelper.OpenConnection(connectionString))
{
const string query = "UPDATE [dbo].[BuildingSettings] " +
"SET [SourceConnectionString] = @sourceConnectionString " +
",[DestinationConnectionString] = @destinationConnectionString " +
",[VocabularyConnectionString] = @vocabularyConnectionString " +
",[Vendor] = @vendor " +
"WHERE BuildingId = @buildingId";
using (var cmd = new SqlCommand(query, connection))
{
cmd.Parameters.Add("@buildingId", SqlDbType.Int);
cmd.Parameters["@buildingId"].Value = buildingId;
cmd.Parameters.Add("@sourceConnectionString", SqlDbType.VarChar);
cmd.Parameters["@sourceConnectionString"].Value = sourceConnectionString;
cmd.Parameters.Add("@destinationConnectionString", SqlDbType.VarChar);
cmd.Parameters["@destinationConnectionString"].Value = destinationConnectionString;
cmd.Parameters.Add("@vocabularyConnectionString", SqlDbType.VarChar);
cmd.Parameters["@vocabularyConnectionString"].Value = vocabularyConnectionString;
cmd.Parameters.Add("@vendor", SqlDbType.VarChar);
cmd.Parameters["@vendor"].Value = vendor.ToString();
cmd.CommandTimeout = 30000;
cmd.ExecuteScalar();
}
}
}
public IEnumerable <string> GetErrors(int buildingId)
{
const string query =
"SELECT 'Builder: ' + b.Dsn + CHAR(13) + " +
"'Time: ' + cast([Time] as nvarchar) + CHAR(13) + " +
"'Error: ' + [Message] + CHAR(13) + CHAR(13) " +
"FROM [dbo].[Log] l " +
"join [dbo].[Builder] b on l.BuilderId = b.Id " +
"where l.[BuildingId] = {0} and [Type] = 'Error' " +
"order by time desc ";
using (var connection = SqlConnectionHelper.OpenConnection(connectionString))
{
using (var cmd = new SqlCommand(string.Format(query, buildingId), connection))
{
cmd.CommandTimeout = 30000;
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
yield return(reader.GetString(0));
}
}
}
}
}
public bool AllChunksStarted(int buildingId)
{
using (var connection = SqlConnectionHelper.OpenConnection(connectionString))
using (var transaction = connection.BeginTransaction())
{
var query =
string.Format(
"SELECT TOP 1 Id FROM Chunk WHERE BuildingId = {0} AND Started is null",
buildingId);
using (var cmd = new SqlCommand(query, connection, transaction)
{
CommandTimeout = 0
})
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
return(false);
}
}
}
}
return(true);
}
public void Load(QueryDefinition qd, EntityDefinition ed)
{
using (var conn = SqlConnectionHelper.OpenConnection(connectionString))
{
using (var c = new SqlCommand(qd.Query, conn))
{
c.CommandTimeout = 30000;
using (var reader = c.ExecuteReader())
{
while (reader.Read())
{
Concept conceptDef = null;
if (ed.Concepts != null && ed.Concepts.Any())
{
conceptDef = ed.Concepts[0];
}
var concept = (T)ed.GetConcepts(conceptDef, reader, null).ToList()[0];
var key = concept.GetKey();
if (key == null)
{
continue;
}
if (!lookup.ContainsKey(key))
{
lookup.Add(key, concept);
}
}
}
}
}
}
public void DropDatabase()
{
var sqlConnectionStringBuilder = new SqlConnectionStringBuilder(connectionString);
var destination = sqlConnectionStringBuilder["Database"];
sqlConnectionStringBuilder["Database"] = "master";
using (var connection = SqlConnectionHelper.OpenConnection(sqlConnectionStringBuilder.ConnectionString))
{
var query = string.Format("ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;", destination);
using (var command = new SqlCommand(query, connection))
{
command.CommandTimeout = 30000;
command.ExecuteNonQuery();
}
query = string.Format("DROP DATABASE [{0}]", destination);
using (var command = new SqlCommand(query, connection))
{
command.CommandTimeout = 30000;
command.ExecuteNonQuery();
}
}
}
public int Create(string sourceConnectionString, string destinationConnectionString, string vocabularyConnectionString, Vendors vendor)
{
using (var connection = SqlConnectionHelper.OpenConnection(connectionString))
{
const string query = "INSERT INTO [dbo].[BuildingSettings] " +
"([SourceConnectionString] " +
",[DestinationConnectionString] " +
",[VocabularyConnectionString] " +
",[Vendor]) " +
"VALUES " +
"(@sourceConnectionString " +
",@destinationConnectionString " +
",@vocabularyConnectionString " +
",@vendor);Select Scope_Identity();";
using (var cmd = new SqlCommand(query, connection))
{
cmd.Parameters.Add("@sourceConnectionString", SqlDbType.VarChar);
cmd.Parameters["@sourceConnectionString"].Value = sourceConnectionString;
cmd.Parameters.Add("@destinationConnectionString", SqlDbType.VarChar);
cmd.Parameters["@destinationConnectionString"].Value = destinationConnectionString;
cmd.Parameters.Add("@vocabularyConnectionString", SqlDbType.VarChar);
cmd.Parameters["@vocabularyConnectionString"].Value = vocabularyConnectionString;
cmd.Parameters.Add("@vendor", SqlDbType.VarChar);
cmd.Parameters["@vendor"].Value = vendor.ToString();
cmd.CommandTimeout = 30000;
return(Convert.ToInt32(cmd.ExecuteScalar()));
}
}
}
public void UpdateSettings(string machineName, int buildingId, string folder, int maxDegreeOfParallelism, int batchSize)
{
const string query = "UPDATE [dbo].[Builder] " +
"SET [Folder] = @folder " +
",[MaxDegreeOfParallelism] = @maxDegreeOfParallelism " +
",[BuildingId] = @buildingId " +
",[BatchSize] = @batchSize " +
"WHERE [Dsn] = @dsn";
using (var connection = SqlConnectionHelper.OpenConnection(connectionString))
{
using (var cmd = new SqlCommand(query, connection))
{
cmd.Parameters.Add("@dsn", SqlDbType.VarChar);
cmd.Parameters["@dsn"].Value = machineName;
cmd.Parameters.Add("@folder", SqlDbType.VarChar);
cmd.Parameters["@folder"].Value = folder;
cmd.Parameters.Add("@maxDegreeOfParallelism", SqlDbType.VarChar);
cmd.Parameters["@maxDegreeOfParallelism"].Value = maxDegreeOfParallelism;
cmd.Parameters.Add("@batchSize", SqlDbType.VarChar);
cmd.Parameters["@batchSize"].Value = batchSize;
cmd.Parameters.Add("@buildingId", SqlDbType.Int);
cmd.Parameters["@buildingId"].Value = buildingId;
cmd.CommandTimeout = 30000;
cmd.ExecuteScalar();
}
}
}
private static bool IsBuilderConnectionUnavailable()
{
if (ConfigurationManager.ConnectionStrings["Builder"] == null)
{
MessageBox.Show(
"Connection to Builder database was missed in configuration file." + Environment.NewLine +
"Please check app.config file.", "Configuration error", MessageBoxButton.OK, MessageBoxImage.Exclamation);
return(true);
}
var splashScreen = new SplashScreen("Resources/splash.png");
try
{
splashScreen.Show(false);
using (
var connection =
SqlConnectionHelper.OpenConnection(ConfigurationManager.ConnectionStrings["Builder"].ConnectionString)
)
{
}
splashScreen.Close(TimeSpan.FromSeconds(0));
}
catch (Exception)
{
splashScreen.Close(TimeSpan.FromSeconds(0));
MessageBox.Show(
"Connection to Builder database not available." + Environment.NewLine + "Please check app.config file.",
"Configuration error", MessageBoxButton.OK, MessageBoxImage.Exclamation);
return(true);
}
return(false);
}
public override ISaver Create()
{
connection = SqlConnectionHelper.OpenConnection(Settings.Current.Building.DestinationConnectionString);
transaction = connection.BeginTransaction();
bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, transaction);
return(this);
}
private void SaveEntityLookup(bool saveToDb)
{
if (saveToDb)
{
using (var c = SqlConnectionHelper.OpenConnection(Settings.Current.Building.DestinationConnectionString))
using (var bulkCopy = new SqlBulkCopy(c, SqlBulkCopyOptions.TableLock, null))
{
if (locationConcepts != null)
{
bulkCopy.Write(new LocationDataReader(locationConcepts.Lookup.Values.ToList()), "LOCATION");
}
if (organizationConcepts != null)
{
bulkCopy.Write(new OrganizationDataReader(organizationConcepts.Lookup.Values.ToList()), "ORGANIZATION");
}
if (careSiteConcepts != null)
{
bulkCopy.Write(new CareSiteDataReader(careSiteConcepts.Lookup.Values.ToList()), "CARE_SITE");
}
if (providerConcepts != null)
{
bulkCopy.Write(new ProviderDataReader(providerConcepts.Lookup.Values.ToList()), "PROVIDER");
}
bulkCopy.Close();
}
}
else
{
var folder = Settings.Current.ResultFolder;
if (locationConcepts != null)
{
SaveToFile(folder, new LocationDataReader(locationConcepts.Lookup.Values.ToList()), "LOCATION");
}
if (organizationConcepts != null)
{
SaveToFile(folder, new OrganizationDataReader(organizationConcepts.Lookup.Values.ToList()),
"ORGANIZATION");
}
if (careSiteConcepts != null)
{
SaveToFile(folder, new CareSiteDataReader(careSiteConcepts.Lookup.Values.ToList()), "CARE_SITE");
}
if (providerConcepts != null)
{
SaveToFile(folder, new ProviderDataReader(providerConcepts.Lookup.Values.ToList()), "PROVIDER");
}
}
}
public void DropChunkTable()
{
using (var connection = SqlConnectionHelper.OpenConnection(connectionString))
using (var cmd = new SqlCommand(DROP_TMP_CHUNK_TABLE, connection)
{
CommandTimeout = 0
})
{
cmd.ExecuteNonQuery();
}
}
public void SetFieldToNowDate(int buildingId, string fieldName)
{
using (var conn = SqlConnectionHelper.OpenConnection(connectionString))
{
var query = string.Format("UPDATE [dbo].[Building] SET {1} = '{2}' WHERE [Id] = {0}", buildingId, fieldName, DateTime.Now);
using (var command = new SqlCommand(query, conn))
{
command.ExecuteNonQuery();
}
}
}
public void TruncateLookup()
{
using (var connection = SqlConnectionHelper.OpenConnection(connectionString))
{
const string query = "truncate table CARE_SITE; truncate table LOCATION; truncate table ORGANIZATION; truncate table PROVIDER;";
using (var command = new SqlCommand(query, connection))
{
command.CommandTimeout = 0;
command.ExecuteNonQuery();
}
}
}
public void Reset()
{
using (var connection = SqlConnectionHelper.OpenConnection(connectionString))
{
const string query = "delete from [dbo].[BuildingSettings]";
using (var cmd = new SqlCommand(query, connection))
{
cmd.ExecuteScalar();
}
}
}
public void CreateTables(string query)
{
var sqlConnectionStringBuilder = new SqlConnectionStringBuilder(connectionString);
using (var connection = SqlConnectionHelper.OpenConnection(sqlConnectionStringBuilder.ConnectionString))
{
using (var command = new SqlCommand(query, connection))
{
command.ExecuteNonQuery();
}
}
}
public void DropVocabularyTables()
{
using (var connection = SqlConnectionHelper.OpenConnection(connectionString))
{
const string query = "DROP TABLE [CONCEPT]; DROP TABLE [CONCEPT_ANCESTOR]; DROP TABLE [CONCEPT_RELATIONSHIP]; DROP TABLE [CONCEPT_SYNONYM]; DROP TABLE [RELATIONSHIP]; DROP TABLE [SOURCE_TO_CONCEPT_MAP]; DROP TABLE [VOCABULARY];";
using (var command = new SqlCommand(query, connection))
{
command.CommandTimeout = 0;
command.ExecuteNonQuery();
}
}
}
public void UpdateState(int builderId, BuilderState state)
{
const string query = "UPDATE [Builder] SET [StateId] = {1} WHERE Id = {0}";
using (var connection = SqlConnectionHelper.OpenConnection(connectionString))
{
using (var cmd = new SqlCommand(string.Format(query, builderId, (int)state), connection))
{
cmd.CommandTimeout = 30000;
cmd.ExecuteNonQuery();
}
}
}
public void ResetChunks(int buildingId)
{
var query = string.Format("UPDATE [dbo].[Chunk] SET [Started] = NULL, [Ended] = NULL WHERE [BuildingId] = {0}", buildingId);
using (var connection = SqlConnectionHelper.OpenConnection(connectionString))
using (var cmd = new SqlCommand(query, connection)
{
CommandTimeout = 0
})
{
cmd.ExecuteNonQuery();
}
}
public void ClearChunks(int buildingId)
{
var query = string.Format("DELETE FROM Chunk WHERE BuildingId = {0} ", buildingId);
using (var connection = SqlConnectionHelper.OpenConnection(connectionString))
using (var cmd = new SqlCommand(query, connection)
{
CommandTimeout = 0
})
{
cmd.ExecuteNonQuery();
}
}
public void ChunkComplete(int chunkId)
{
var query = string.Format("UPDATE Chunk SET Ended = '{1}' WHERE Id = {0}", chunkId, DateTime.Now);
using (var connection = SqlConnectionHelper.OpenConnection(connectionString))
using (var cmd = new SqlCommand(query, connection)
{
CommandTimeout = 0
})
{
cmd.ExecuteNonQuery();
}
}
public void Reset(int builderId)
{
var query = "UPDATE [dbo].[Builder] SET [BuildingId] = NULL WHERE Id = " + builderId;
using (var connection = SqlConnectionHelper.OpenConnection(connectionString))
{
using (var cmd = new SqlCommand(query, connection))
{
cmd.CommandTimeout = 30000;
cmd.ExecuteScalar();
}
}
}
public void SetFieldTo(int buildingId, string fieldName, DateTime?value)
{
using (var conn = SqlConnectionHelper.OpenConnection(connectionString))
{
var query = string.Format("UPDATE [dbo].[Building] SET {1} = @time WHERE [Id] = {0}", buildingId, fieldName);
using (var command = new SqlCommand(query, conn))
{
command.Parameters.Add("@time", SqlDbType.DateTime);
command.Parameters["@time"].Value = value.HasValue ? value.Value : (object)DBNull.Value;
command.ExecuteNonQuery();
}
}
}
public void Create(int buildingId)
{
const string query = "INSERT INTO [dbo].[Building] ([Id]) VALUES ({0})";
using (var connection = SqlConnectionHelper.OpenConnection(connectionString))
{
using (var cmd = new SqlCommand(string.Format(query, buildingId), connection))
{
cmd.CommandTimeout = 30000;
cmd.ExecuteScalar();
}
}
}
public void Remove(int builderId)
{
var query = "DELETE FROM [Builder] WHERE Id = " + builderId;
using (var connection = SqlConnectionHelper.OpenConnection(connectionString))
{
using (var cmd = new SqlCommand(query, connection))
{
cmd.CommandTimeout = 30000;
cmd.ExecuteScalar();
}
}
}
public int CreateSettings(string machineName, string folder, int maxDegreeOfParallelism, int batchSize)
{
var query = "INSERT INTO [Builder] ([Dsn],[Folder],[MaxDegreeOfParallelism],[BatchSize]) VALUES ('{0}','{1}',{2},{3});Select Scope_Identity();";
query = string.Format(query, machineName, folder, maxDegreeOfParallelism, batchSize);
using (var connection = SqlConnectionHelper.OpenConnection(connectionString))
{
using (var cmd = new SqlCommand(query, connection))
{
cmd.CommandTimeout = 30000;
return(Convert.ToInt32(cmd.ExecuteScalar()));
}
}
}
public void CreateIndexes(string query)
{
using (var connection = SqlConnectionHelper.OpenConnection(connectionString))
{
foreach (var subQuery in query.Split(new[] { "\r\nGO" }, StringSplitOptions.None))
{
using (var command = new SqlCommand(subQuery, connection))
{
command.CommandTimeout = 0;
command.ExecuteNonQuery();
}
}
}
}
public void CopyVocabulary(string query, string vocabularyConnectionString)
{
var vocab = new SqlConnectionStringBuilder(vocabularyConnectionString);
using (var connection = SqlConnectionHelper.OpenConnection(connectionString))
{
query = string.Format(query, vocab.DataSource, vocab.InitialCatalog);
using (var command = new SqlCommand(query, connection))
{
command.CommandTimeout = 0;
command.ExecuteNonQuery();
}
}
}
public void Create(int buildingId)
{
const string query = "DELETE FROM KeyOffset WHERE BuildingId = {0} " +
"INSERT INTO KeyOffset (BuildingId, VisitOccurrence, PayerPlanPeriod, DrugExposure, ProcedureOccurrence) " +
"VALUES ({0}, 0, 0, 0, 0)";
using (var connection = SqlConnectionHelper.OpenConnection(connectionString))
{
using (var cmd = new SqlCommand(string.Format(query, buildingId), connection))
{
cmd.CommandTimeout = 30000;
cmd.ExecuteNonQuery();
}
}
}
public void Reset(int buildingId)
{
const string query =
"delete [dbo].[Log] FROM [dbo].[Log] l " +
"join [dbo].[Builder] b on l.BuilderId = b.Id " +
"where l.[BuildingId] = {0} ";
using (var connection = SqlConnectionHelper.OpenConnection(connectionString))
{
using (var cmd = new SqlCommand(string.Format(query, buildingId), connection))
{
cmd.ExecuteNonQuery();
}
}
}
