public int CompareTo(InlineDbCommandCondition other)
{
if (other == null)
{
return(1);
}
var otherLikeCondition = other as LikeCondition;
if (otherLikeCondition == null)
{
return(DataAccessMethods.CompareCommandConditionTypes(this, other));
}
var behaviorResult = EwlStatics.Compare(behavior, otherLikeCondition.behavior);
if (behaviorResult != 0)
{
return(behaviorResult);
}
var columnNameResult = EwlStatics.Compare(columnName, otherLikeCondition.columnName, comparer: StringComparer.InvariantCulture);
return(columnNameResult != 0
? columnNameResult
: EwlStatics.Compare(searchTerm, otherLikeCondition.searchTerm, comparer: StringComparer.InvariantCulture));
}
void Database.ExportToFile(string filePath)
{
Directory.CreateDirectory(dataPumpFolderPath);
try {
executeMethodWithDbExceptionHandling(
delegate {
try {
// We pass an enter keystroke as input in an attempt to kill the program if it gets stuck on a username prompt because of a bad logon string.
EwlStatics.RunProgram(
"expdp",
getLogonString() + " DIRECTORY=" + dataPumpOracleDirectoryName + " DUMPFILE=\"\"\"" + getDumpFileName() + "\"\"\" NOLOGFILE=y VERSION=12.1",
Environment.NewLine,
true);
}
catch (Exception e) {
throwUserCorrectableExceptionIfNecessary(e);
throw DataAccessMethods.CreateDbConnectionException(info, "exporting (to file)", e);
}
});
IoMethods.ExecuteWithTempFolder(
folderPath => {
IoMethods.CopyFile(getDumpFilePath(), EwlStatics.CombinePaths(folderPath, databaseFileDumpFileName));
File.WriteAllText(EwlStatics.CombinePaths(folderPath, databaseFileSchemaNameFileName), info.UserAndSchema);
ZipOps.ZipFolderAsFile(folderPath, filePath);
});
}
finally {
IoMethods.DeleteFile(getDumpFilePath());
}
}
void Database.DeleteAndReCreateFromFile(string filePath, bool keepDbInStandbyMode)
{
using (var sw = new StringWriter()) {
sw.WriteLine("DROP DATABASE IF EXISTS {0};".FormatWith(info.Database));
sw.WriteLine("CREATE DATABASE {0};".FormatWith(info.Database));
sw.WriteLine("use {0}".FormatWith(info.Database));
sw.Write(File.ReadAllText(filePath));
sw.WriteLine("quit");
executeMethodWithDbExceptionHandling(
delegate {
try {
EwlStatics.RunProgram(
EwlStatics.CombinePaths(binFolderPath, "mysql"),
getHostAndAuthenticationArguments() + " --disable-reconnect --batch --disable-auto-rehash",
sw.ToString(),
true);
}
catch (Exception e) {
if (e.Message.Contains("ERROR") && e.Message.Contains("at line"))
{
throw new UserCorrectableException("Failed to create database from file. Please try the operation again after obtaining a new database file.", e);
}
throw DataAccessMethods.CreateDbConnectionException(info, "re-creating (from file)", e);
}
});
}
}
public void TestAddStudent()
{
DataAccessMethods dam = new DataAccessMethods();
int result = dam.AddStudent("TestStudent");
// AddStudent returns 1 if it was successful
Assert.AreEqual(1, result);
}
public void TestGetStudent()
{
DataAccessMethods dam = new DataAccessMethods();
// Geet the Students with an Id = 1
Student student = dam.GetStudent(1);
Assert.AreEqual("TestStudent", student.Name);
}
public void TestGetStudentList()
{
DataAccessMethods dam = new DataAccessMethods();
// Gets a list of Student objects
List<Student> result = dam.GetStudentList();
// Our list should contain at least one Student
Assert.IsTrue(result.Count > 0);
}
public static void SetTarefas(Task tarefa, SqlTransaction tran = null)
{
DataAccessMethods.ExecuteSP("Tarefas.SetTarefas", tran,
DataAccessMethods.CreateSqlParameter("@TaskID", tarefa.TaskID, SqlDbType.Int),
DataAccessMethods.CreateSqlParameter("@DesTitulo", tarefa.DesTitulo, SqlDbType.VarChar, 40),
DataAccessMethods.CreateSqlParameter("@DesDescricao", tarefa.DesDescricao, SqlDbType.VarChar, 100),
DataAccessMethods.CreateSqlParameter("@IndStatus", tarefa.IndStatus, SqlDbType.VarChar, 10),
DataAccessMethods.CreateSqlParameter("@DatCriacao", tarefa.DatCriacao, SqlDbType.Date),
DataAccessMethods.CreateSqlParameter("@DatUltAlteracao", tarefa.DatUltAlteracao, SqlDbType.Date),
DataAccessMethods.CreateSqlParameter("@DatConclusao", tarefa.DatConclusao, SqlDbType.Date));
}
public void InsertCollection(IList <T> list, string sql, string parmeterName, string typeName)
{
SqlParameter parm = new SqlParameter
{
SqlDbType = SqlDbType.Structured,
ParameterName = parmeterName,
TypeName = typeName,
Value = DataAccessMethods.ConvertToDataTable(list)
};
_dbContext.Database.ExecuteSqlInterpolated($"{sql} {parm}");
}
void IDatabase.ExecuteSqlScriptInTransaction(string script)
{
executeMethodWithDbExceptionHandling(
() => {
try {
runDatabaseScript(info.ConnectionString, script);
}
catch (Exception e) {
throw DataAccessMethods.CreateDbConnectionException(info, "updating logic in", e);
}
});
}
public int CompareTo(InlineDbCommandCondition other)
{
if (other == null)
{
return(1);
}
var otherEqualityCondition = other as EqualityCondition;
if (otherEqualityCondition == null)
{
return(DataAccessMethods.CompareCommandConditionTypes(this, other));
}
return(Comparer <InlineDbCommandColumnValue> .Default.Compare(columnValue, otherEqualityCondition.columnValue));
}
public int CompareTo(InlineDbCommandCondition other)
{
if (other == null)
{
return(1);
}
var otherInequalityCondition = other as InequalityCondition;
if (otherInequalityCondition == null)
{
return(DataAccessMethods.CompareCommandConditionTypes(this, other));
}
var opResult = Comparer <Operator> .Default.Compare(op, otherInequalityCondition.op);
return(opResult != 0 ? opResult : Comparer <InlineDbCommandColumnValue> .Default.Compare(columnValue, otherInequalityCondition.columnValue));
}
void Database.ExportToFile(string filePath)
{
executeMethodWithDbExceptionHandling(
delegate {
try {
// The --hex-blob option prevents certain BLOBs from causing errors during database re-creation.
EwlStatics.RunProgram(
EwlStatics.CombinePaths(binFolderPath, "mysqldump"),
getHostAndAuthenticationArguments() + " --single-transaction --hex-blob --result-file=\"{0}\" ".FormatWith(filePath) + info.Database,
"",
true);
}
catch (Exception e) {
throw DataAccessMethods.CreateDbConnectionException(info, "exporting (to file)", e);
}
});
}
public int CompareTo(InlineDbCommandCondition other)
{
if (other == null)
{
return(1);
}
var otherInCondition = other as InCondition;
if (otherInCondition == null)
{
return(DataAccessMethods.CompareCommandConditionTypes(this, other));
}
var columnNameResult = StringComparer.InvariantCulture.Compare(columnName, otherInCondition.columnName);
return(columnNameResult != 0 ? columnNameResult : StringComparer.InvariantCulture.Compare(subQuery, otherInCondition.subQuery));
}
void Database.ExecuteSqlScriptInTransaction(string script)
{
executeMethodWithDbExceptionHandling(
delegate {
try {
EwlStatics.RunProgram(
"sqlcmd",
(info.Server != null ? "-S " + info.Server + " " : "") + "-d " + info.Database + " -e -b",
"BEGIN TRAN" + Environment.NewLine + "GO" + Environment.NewLine + script + "COMMIT TRAN" + Environment.NewLine + "GO" + Environment.NewLine + "EXIT" +
Environment.NewLine,
true);
}
catch (Exception e) {
throw DataAccessMethods.CreateDbConnectionException(info, "updating logic in", e);
}
});
}
public void Delete(int taskID)
{
SqlTransaction tran = null;
try
{
tran = DataAccessMethods.GetSqlTransaction(IsolationLevel.ReadCommitted);
DataAccess.DataAccess.DelTarefas(taskID, tran);
tran.CommitAndCloseConnection();
}
catch (SqlException ex)
{
if (tran != null)
{
tran.RollbackAndCloseConnection();
}
}
}
public string Post([FromBody] Task tarefa)
{
DateTime dataAtual = DateTime.Now;
if (string.IsNullOrEmpty(tarefa.DesTitulo))
{
return("Título da tarefa deve ser informado!");
}
if (string.IsNullOrEmpty(tarefa.DesDescricao))
{
return("Descrição da tarefa deve ser informada!");
}
tarefa.DatUltAlteracao = dataAtual;
if (tarefa.TaskID == 0)
{
tarefa.DatCriacao = dataAtual;
}
if (tarefa.TaskID > 0 && tarefa.IndStatus == "C")
{
tarefa.DatConclusao = dataAtual;
}
SqlTransaction tran = null;
try
{
tran = DataAccessMethods.GetSqlTransaction(IsolationLevel.ReadCommitted);
DataAccess.DataAccess.SetTarefas(tarefa, tran);
tran.CommitAndCloseConnection();
}
catch (SqlException ex)
{
if (tran != null)
{
tran.RollbackAndCloseConnection();
}
return(ex.Message);
}
return("ok");
}
void Database.ExecuteSqlScriptInTransaction(string script)
{
using (var sw = new StringWriter()) {
// Carriage returns seem to be significant here.
sw.WriteLine("WHENEVER SQLERROR EXIT SQL.SQLCODE;");
sw.Write(script);
sw.WriteLine("EXIT SUCCESS COMMIT;");
executeMethodWithDbExceptionHandling(
delegate {
try {
// -L option stops it from prompting on failed logon.
EwlStatics.RunProgram("sqlplus", "-L " + getLogonString(), sw.ToString(), true);
}
catch (Exception e) {
throw DataAccessMethods.CreateDbConnectionException(info, "updating logic in", e);
}
});
}
}
void IDatabase.ExecuteSqlScriptInTransaction(string script)
{
using (var sw = new StringWriter()) {
sw.WriteLine("START TRANSACTION;");
sw.Write(script);
sw.WriteLine("COMMIT;");
sw.WriteLine("quit");
executeMethodWithDbExceptionHandling(
() => {
try {
Utility.RunProgram(
Utility.CombinePaths(binFolderPath, "mysql"),
$"--host=localhost --user=root --password=password {connectionString.Database} --disable-reconnect --batch --disable-auto-rehash",
sw.ToString(),
true);
}
catch (Exception e) {
throw DataAccessMethods.CreateDbConnectionException(info, "updating logic in", e);
}
});
}
}
void Database.ExecuteSqlScriptInTransaction(string script)
{
using (var sw = new StringWriter()) {
sw.WriteLine("START TRANSACTION;");
sw.Write(script);
sw.WriteLine("COMMIT;");
sw.WriteLine("quit");
executeMethodWithDbExceptionHandling(
delegate {
try {
EwlStatics.RunProgram(
EwlStatics.CombinePaths(binFolderPath, "mysql"),
getHostAndAuthenticationArguments() + " " + info.Database + " --disable-reconnect --batch --disable-auto-rehash",
sw.ToString(),
true);
}
catch (Exception e) {
throw DataAccessMethods.CreateDbConnectionException(info, "updating logic in", e);
}
});
}
}
public static DataTable GetTarefas(int taskID)
{
return(DataAccessMethods.GetDataTableFromSP("Tarefas.GetTarefas",
DataAccessMethods.CreateSqlParameter("@taskID", taskID, SqlDbType.Int)));
}
public static void DelTarefas(int taskID, SqlTransaction tran = null)
{
DataAccessMethods.ExecuteSP("Tarefas.DelTarefas",
DataAccessMethods.CreateSqlParameter("@taskID", taskID, SqlDbType.Int));
}
void Database.DeleteAndReCreateFromFile(string filePath)
{
executeDbMethodWithSpecifiedDatabaseInfo(
new OracleInfo(
(info as DatabaseInfo).SecondaryDatabaseName,
info.DataSource,
"sys",
ConfigurationLogic.OracleSysPassword,
info.SupportsConnectionPooling,
info.SupportsLinguisticIndexes),
cn => {
executeLongRunningCommand(cn, "CREATE OR REPLACE DIRECTORY " + dataPumpOracleDirectoryName + " AS '" + dataPumpFolderPath + "'");
deleteAndReCreateUser(cn);
});
try {
IoMethods.ExecuteWithTempFolder(
tempFolderPath => {
var folderPath = EwlStatics.CombinePaths(tempFolderPath, "Database File");
ZipOps.UnZipFileAsFolder(filePath, folderPath);
try {
IoMethods.CopyFile(EwlStatics.CombinePaths(folderPath, databaseFileDumpFileName), getDumpFilePath());
executeMethodWithDbExceptionHandling(
delegate {
try {
EwlStatics.RunProgram(
"impdp",
getLogonString() + " DIRECTORY=" + dataPumpOracleDirectoryName + " DUMPFILE=\"\"\"" + getDumpFileName() + "\"\"\" NOLOGFILE=y REMAP_SCHEMA=" +
File.ReadAllText(EwlStatics.CombinePaths(folderPath, databaseFileSchemaNameFileName)) + ":" + info.UserAndSchema,
"",
true);
}
catch (Exception e) {
throwUserCorrectableExceptionIfNecessary(e);
if (e is FileNotFoundException)
{
throw new UserCorrectableException("The schema name file was not found, probably because of a corrupt database file in the data package.", e);
}
// Secondary databases such as RLE cause procedure compilation errors when imported, and since we have no way of
// distinguishing these from legitimate import problems, we have no choice but to ignore all exceptions.
if ((info as DatabaseInfo).SecondaryDatabaseName.Length == 0)
{
throw DataAccessMethods.CreateDbConnectionException(info, "re-creating (from file)", e);
}
}
});
}
finally {
IoMethods.DeleteFile(getDumpFilePath());
}
});
}
catch {
// We don't want to leave a partial user/schema on the machine since it may confuse future ISU operations.
executeDbMethodWithSpecifiedDatabaseInfo(
new OracleInfo(
(info as DatabaseInfo).SecondaryDatabaseName,
info.DataSource,
"sys",
ConfigurationLogic.OracleSysPassword,
info.SupportsConnectionPooling,
info.SupportsLinguisticIndexes),
deleteUser);
throw;
}
}
void Database.DeleteAndReCreateFromFile(string filePath)
{
using (var sw = new StringWriter()) {
sw.WriteLine("DROP DATABASE IF EXISTS {0};".FormatWith(info.Database));
sw.WriteLine("CREATE DATABASE {0};".FormatWith(info.Database));
if (filePath.Any())
{
sw.WriteLine("use {0}".FormatWith(info.Database));
sw.WriteLine("source {0}".FormatWith(filePath));
}
sw.WriteLine("quit");
executeMethodWithDbExceptionHandling(
delegate {
try {
TewlContrib.ProcessTools.RunProgram(
EwlStatics.CombinePaths(binFolderPath, "mysql"),
getHostAndAuthenticationArguments() + " --disable-reconnect --batch --disable-auto-rehash",
sw.ToString(),
true);
}
catch (Exception e) {
if (filePath.Any() && e.Message.Contains("ERROR") && e.Message.Contains("at line"))
{
throw new UserCorrectableException(
"Failed to create database from file. Please try the operation again after obtaining a new database file.",
e);
}
throw DataAccessMethods.CreateDbConnectionException(info, "re-creating (from file)", e);
}
});
}
if (!filePath.Any())
{
ExecuteDbMethod(
cn => {
var globalIntsCreate = cn.DatabaseInfo.CreateCommand();
globalIntsCreate.CommandText = @"CREATE TABLE global_ints(
ParameterName VARCHAR( 50 )
PRIMARY KEY,
ParameterValue INT
NOT NULL
)";
cn.ExecuteNonQueryCommand(globalIntsCreate);
var lineMarkerInsert = new InlineInsert("global_ints");
lineMarkerInsert.AddColumnModifications(new InlineDbCommandColumnValue("ParameterName", new DbParameterValue("LineMarker")).ToCollection());
lineMarkerInsert.AddColumnModifications(new InlineDbCommandColumnValue("ParameterValue", new DbParameterValue(0)).ToCollection());
lineMarkerInsert.Execute(cn);
var mainSequenceCreate = cn.DatabaseInfo.CreateCommand();
mainSequenceCreate.CommandText = @"CREATE TABLE main_sequence(
MainSequenceId INT
AUTO_INCREMENT
PRIMARY KEY
)";
cn.ExecuteNonQueryCommand(mainSequenceCreate);
});
}
}
