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