public void InsertMetric(int loadId, Metrics metric, string valueAsString, double?valueAsNumber, DateTime?valueAsDateTime)
{
const string query =
"INSERT INTO Result (LoadId,MetricId,Value_As_String,Value_As_Number,Value_As_DateTime) VALUES (@loadId, @metricId, @valueAsString, @valueAsNumber, @valueAsDateTime)";
using (var connection = SqlConnectionHelper.OpenMSSQLConnection(connectionString))
using (var cmd = new SqlCommand(query, connection)
{
CommandTimeout = 0
})
{
cmd.Parameters.Add("@loadId", SqlDbType.Int);
cmd.Parameters["@loadId"].Value = loadId;
cmd.Parameters.Add("@metricId", SqlDbType.Int);
cmd.Parameters["@metricId"].Value = (int)metric;
cmd.Parameters.Add("@valueAsString", SqlDbType.VarChar);
cmd.Parameters["@valueAsString"].Value = valueAsString;
cmd.Parameters.Add("@valueAsNumber", SqlDbType.Float);
cmd.Parameters["@valueAsNumber"].Value = valueAsNumber.HasValue ? valueAsNumber : (object)DBNull.Value;
cmd.Parameters.Add("@valueAsDateTime", SqlDbType.DateTime);
cmd.Parameters["@valueAsDateTime"].Value = valueAsDateTime.HasValue ? valueAsDateTime : (object)DBNull.Value;
cmd.ExecuteNonQuery();
}
}
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.OpenMSSQLConnection(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 UpdateMetric(int resultId, string valueAsString, double?valueAsNumber, DateTime?valueAsDateTime)
{
const string query =
"UPDATE Result SET Value_As_String = @valueAsString, Value_As_Number = @valueAsNumber, Value_As_DateTime = @valueAsDateTime WHERE ResultId = @resultId";
using (var connection = SqlConnectionHelper.OpenMSSQLConnection(connectionString))
using (var cmd = new SqlCommand(query, connection)
{
CommandTimeout = 0
})
{
cmd.Parameters.Add("@resultId", SqlDbType.Int);
cmd.Parameters["@resultId"].Value = resultId;
cmd.Parameters.Add("@valueAsString", SqlDbType.VarChar);
cmd.Parameters["@valueAsString"].Value = valueAsString;
cmd.Parameters.Add("@valueAsNumber", SqlDbType.Float);
cmd.Parameters["@valueAsNumber"].Value = valueAsNumber.HasValue ? valueAsNumber : (object)DBNull.Value;
cmd.Parameters.Add("@valueAsDateTime", SqlDbType.DateTime);
cmd.Parameters["@valueAsDateTime"].Value = valueAsDateTime.HasValue ? valueAsDateTime : (object)DBNull.Value;
cmd.ExecuteNonQuery();
}
}
public IEnumerable <IDataReader> Load(int buildingId)
{
const string query = "SELECT [BuildingId] " +
",[SourceConnectionString] " +
",[DestinationConnectionString] " +
",[VocabularyConnectionString] " +
",[Vendor] " +
",[BatchSize] " +
"FROM [dbo].[BuildingSettings] where BuildingId = {0}";
using (var connection = SqlConnectionHelper.OpenMSSQLConnection(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);
}
}
}
}
}
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;
return(!CreateBuilderDB());
}
var splashScreen = new SplashScreen("Resources/splash.png");
try
{
splashScreen.Show(false);
using (
var connection =
SqlConnectionHelper.OpenMSSQLConnection(ConfigurationManager.ConnectionStrings["Builder"].ConnectionString)
)
{
}
splashScreen.Close(TimeSpan.FromSeconds(0));
}
catch (Exception e)
{
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(!CreateBuilderDB());
}
return(false);
}
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.OpenMSSQLConnection(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 AllChunksComplete(int buildingId)
{
using (var connection = SqlConnectionHelper.OpenMSSQLConnection(connectionString))
using (var transaction = connection.BeginTransaction())
{
var query =
string.Format(
"SELECT TOP 1 Id FROM Chunk WHERE BuildingId = {0} AND Ended 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 ResetChunks(int buildingId)
{
var query = string.Format("UPDATE [dbo].[Chunk] SET [Started] = NULL, [Ended] = NULL, [QueriesExecuted] = NULL, [Loaded] = NULL, [Built] = NULL WHERE [BuildingId] = {0}", buildingId);
using (var connection = SqlConnectionHelper.OpenMSSQLConnection(connectionString))
using (var cmd = new SqlCommand(query, connection)
{
CommandTimeout = 0
})
{
cmd.ExecuteNonQuery();
}
query = string.Format("DELETE FROM [dbo].[AvailableOnS3] WHERE [BuildingId] = {0}", buildingId);
using (var connection = SqlConnectionHelper.OpenMSSQLConnection(connectionString))
using (var cmd = new SqlCommand(query, connection)
{
CommandTimeout = 0
})
{
cmd.ExecuteNonQuery();
}
//query = string.Format("DELETE FROM SubChunk where ChunkId in (SELECT Id FROM Chunk WHERE BuildingId = {0})", buildingId);
//using (var connection = SqlConnectionHelper.OpenMSSQLConnection(connectionString))
//using (var cmd = new SqlCommand(query, connection) { CommandTimeout = 0 })
//{
// cmd.ExecuteNonQuery();
//}
}
private void ButtonOk_OnClick(object sender, RoutedEventArgs e)
{
try
{
var sqlConnectionStringBuilder = new SqlConnectionStringBuilder(builderConnection.Text);
var builderDb = sqlConnectionStringBuilder["Database"];
sqlConnectionStringBuilder["Database"] = "master";
using (var connection = SqlConnectionHelper.OpenMSSQLConnection(sqlConnectionStringBuilder.ConnectionString))
{
using (var command = new SqlCommand("create database " + builderDb, connection))
{
command.ExecuteNonQuery();
}
using (var command = new SqlCommand(string.Format("ALTER DATABASE {0} SET RECOVERY Simple", builderDb), connection))
{
command.ExecuteNonQuery();
}
}
using (var connection = SqlConnectionHelper.OpenMSSQLConnection(builderConnection.Text))
{
foreach (var subQuery in buildingmanager.Resources.Resources.BuilderDbScript.Split(new[] { "\r\nGO" }, StringSplitOptions.None))
{
if (string.IsNullOrEmpty(subQuery))
{
continue;
}
using (var command = new SqlCommand(subQuery, connection))
{
command.ExecuteNonQuery();
}
}
}
var config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
if (config.ConnectionStrings.ConnectionStrings["Builder"] == null)
{
config.ConnectionStrings.ConnectionStrings.Add(new ConnectionStringSettings("Builder", builderConnection.Text));
}
else
{
config.ConnectionStrings.ConnectionStrings["Builder"].ConnectionString = builderConnection.Text;
}
config.Save(ConfigurationSaveMode.Modified);
MessageBox.Show("Database created successfully", "Done", MessageBoxButton.OK, MessageBoxImage.None);
DialogResult = true;
this.Close();
}
catch (Exception ex)
{
MessageBox.Show(Logger.CreateExceptionString(ex), "Error", MessageBoxButton.OK, MessageBoxImage.Exclamation);
DialogResult = false;
this.Close();
}
}
public void SetFieldToNowDate(int buildingId, string fieldName)
{
using (var conn = SqlConnectionHelper.OpenMSSQLConnection(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 Reset()
{
using (var connection = SqlConnectionHelper.OpenMSSQLConnection(connectionString))
{
const string query = "delete from [dbo].[BuildingSettings]";
using (var cmd = new SqlCommand(query, connection))
{
cmd.ExecuteScalar();
}
}
}
public void ChunkBuilt(int chunkId)
{
var query = string.Format("UPDATE Chunk SET Built = '{1}' WHERE Id = {0}", chunkId, DateTime.Now);
using (var connection = SqlConnectionHelper.OpenMSSQLConnection(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.OpenMSSQLConnection(connectionString))
{
using (var cmd = new SqlCommand(query, connection))
{
cmd.CommandTimeout = 30000;
cmd.ExecuteScalar();
}
}
}
public void MarkUncompletedChunks(int buildingId, int builderId)
{
var query = string.Format("UPDATE Chunk SET Failed = '{2}' WHERE BuildingId = {0} AND BuilderId = {1} AND Started is not NULL and Ended is NULL", buildingId, builderId, DateTime.Now);
using (var connection = SqlConnectionHelper.OpenMSSQLConnection(connectionString))
using (var cmd = new SqlCommand(query, connection)
{
CommandTimeout = 0
})
{
cmd.ExecuteNonQuery();
}
}
public void UncompleteChunk(int chunkId)
{
var query = string.Format("UPDATE Chunk SET Ended = NULL WHERE Id = {0}", chunkId);
using (var connection = SqlConnectionHelper.OpenMSSQLConnection(connectionString))
using (var cmd = new SqlCommand(query, connection)
{
CommandTimeout = 0
})
{
cmd.ExecuteNonQuery();
}
}
public void ResetNotFinishedChunks(int buildingId)
{
var query = string.Format("UPDATE [dbo].[Chunk] SET [Started] = NULL WHERE [Ended] is NULL and [BuildingId] = {0}", buildingId);
using (var connection = SqlConnectionHelper.OpenMSSQLConnection(connectionString))
using (var cmd = new SqlCommand(query, connection)
{
CommandTimeout = 0
})
{
cmd.ExecuteNonQuery();
}
}
public void UpdateState(int builderId, BuilderState state)
{
const string query = "UPDATE [Builder] SET [StateId] = {1} WHERE Id = {0}";
using (var connection = SqlConnectionHelper.OpenMSSQLConnection(connectionString))
{
using (var cmd = new SqlCommand(string.Format(query, builderId, (int)state), connection))
{
cmd.CommandTimeout = 30000;
cmd.ExecuteNonQuery();
}
}
}
public void AddPreparedChunk(int buildingId, int setIndex, int chunkId)
{
var sql = string.Format("INSERT INTO AvailableOnS3 (BuildingId, SetIndex, ChunkId, Saved) VALUES ({0}, {1}, {2}, 0)", buildingId, setIndex, chunkId);
using (var connection = SqlConnectionHelper.OpenMSSQLConnection(connectionString))
using (var c = new SqlCommand(sql, connection)
{
CommandTimeout = 0
})
{
c.ExecuteScalar();
}
}
public void Remove(int builderId)
{
var query = "DELETE FROM [Builder] WHERE Id = " + builderId;
using (var connection = SqlConnectionHelper.OpenMSSQLConnection(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.OpenMSSQLConnection(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.OpenMSSQLConnection(connectionString))
{
using (var cmd = new SqlCommand(string.Format(query, buildingId), connection))
{
cmd.CommandTimeout = 30000;
cmd.ExecuteScalar();
}
}
}
public int CreateSettings(string machineName, string folder, int maxDegreeOfParallelism, string version)
{
var query = "INSERT INTO [Builder] ([Dsn],[Folder],[BatchSize], [MaxDegreeOfParallelism], [Version]) VALUES ('{0}','{1}',{2},{3},'{4}');Select Scope_Identity();";
query = string.Format(query, machineName, folder, 0, maxDegreeOfParallelism, version);
using (var connection = SqlConnectionHelper.OpenMSSQLConnection(connectionString))
{
using (var cmd = new SqlCommand(query, connection))
{
cmd.CommandTimeout = 30000;
return(Convert.ToInt32(cmd.ExecuteScalar()));
}
}
}
public void SetSaved(int buildingId, int setIndex)
{
var sql = string.Format("UPDATE AvailableOnS3 SET Saved = 1 WHERE BuildingId = {0} and SetIndex = {1}",
buildingId, setIndex);
using (var connection = SqlConnectionHelper.OpenMSSQLConnection(connectionString))
using (var c = new SqlCommand(sql, connection)
{
CommandTimeout = 0
})
{
c.ExecuteScalar();
}
}
public override IEnumerable <string> GetAllTables()
{
const string query = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'";
using (var conn = SqlConnectionHelper.OpenMSSQLConnection(Settings.Current.Building.SourceConnectionString))
using (var c = new SqlCommand(query, conn))
using (var reader = c.ExecuteReader())
{
while (reader.Read())
{
yield return(reader.GetString(0));
}
}
}
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} and l.[Type] = 'Error'";
using (var connection = SqlConnectionHelper.OpenMSSQLConnection(connectionString))
{
using (var cmd = new SqlCommand(string.Format(query, buildingId), connection))
{
cmd.ExecuteNonQuery();
}
}
}
public void Recreate(int buildingId)
{
const string query = "DELETE FROM KeyOffset WHERE BuildingId = {0} " +
"INSERT INTO KeyOffset (BuildingId, VisitOccurrence, PayerPlanPeriod, DrugExposure, ProcedureOccurrence, DeviceExposure, ConditionOccurrence, Measurement, Observation, ObservationPeriod, VisitCost, ProcedureCost, DeviceCost, DrugEra, ConditionEra, Note) " +
"VALUES ({0}, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)";
using (var connection = SqlConnectionHelper.OpenMSSQLConnection(connectionString))
{
using (var cmd = new SqlCommand(string.Format(query, buildingId), connection))
{
cmd.CommandTimeout = 30000;
cmd.ExecuteNonQuery();
}
}
}
public override IEnumerable <string> GetAllColumns(string tableName)
{
var query = string.Format("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '{0}'",
tableName);
using (var conn = SqlConnectionHelper.OpenMSSQLConnection(Settings.Current.Building.SourceConnectionString))
using (var c = new SqlCommand(query, conn))
using (var reader = c.ExecuteReader())
{
while (reader.Read())
{
yield return(reader.GetString(0));
}
}
}
public override ISaver Create(string connectionString)
{
var odbcConnection = new OdbcConnectionStringBuilder(connectionString);
var sqlConnection = new SqlConnectionStringBuilder();
sqlConnection["Data Source"] = odbcConnection["server"];
sqlConnection["Initial Catalog"] = odbcConnection["database"];
sqlConnection["User Id"] = odbcConnection["uid"];
sqlConnection["Password"] = odbcConnection["pwd"];
connection = SqlConnectionHelper.OpenMSSQLConnection(sqlConnection.ConnectionString);
transaction = connection.BeginTransaction();
bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, transaction);
return(this);
}
public int GetChunksWithRunningQueriesCount(int buildingId)
{
var query =
string.Format(
"SELECT Count(*) FROM Chunk WHERE BuildingId = {0} AND Started is not null AND QueriesExecuted is NULL AND Failed is NULL",
buildingId);
using (var connection = SqlConnectionHelper.OpenMSSQLConnection(connectionString))
using (var cmd = new SqlCommand(query, connection)
{
CommandTimeout = 0
})
{
return(Convert.ToInt32(cmd.ExecuteScalar()));
}
}
public int GetChunksCount(int buildingId, int setIndex)
{
var query =
string.Format(
"SELECT Count(*) FROM AvailableOnS3 WHERE BuildingId = {0} AND SetIndex = {1}",
buildingId, setIndex);
using (var connection = SqlConnectionHelper.OpenMSSQLConnection(connectionString))
using (var cmd = new SqlCommand(query, connection)
{
CommandTimeout = 0
})
{
return(Convert.ToInt32(cmd.ExecuteScalar()));
}
}
