private Boolean PostDBLog(string connStringAdmin, DatabaseLogModel databaseLogModel)
{
try
{
using (SqlConnection con = new SqlConnection(connStringAdmin))
{
using (SqlCommand cmd = new SqlCommand("SP_SysADM_Insert_DBLog", con))
{
//Get Current Loggedin user via session
RbacUser currentUser = HttpContext.Session.Get <RbacUser>("currentuser");
databaseLogModel.CreatedBy = currentUser.EmployeeId;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@FileName", SqlDbType.VarChar).Value = databaseLogModel.FileName;
cmd.Parameters.Add("@FolderPath", SqlDbType.VarChar).Value = databaseLogModel.FolderPath;
cmd.Parameters.Add("@DatabaseName", SqlDbType.VarChar).Value = databaseLogModel.DatabaseName;
cmd.Parameters.Add("@DatabaseVersion", SqlDbType.VarChar).Value = databaseLogModel.DatabaseVersion;
cmd.Parameters.Add("@IsDBRestorable", SqlDbType.Bit).Value = databaseLogModel.IsDBRestorable;
cmd.Parameters.Add("@Action", SqlDbType.VarChar).Value = databaseLogModel.Action;
cmd.Parameters.Add("@ActionType", SqlDbType.VarChar).Value = databaseLogModel.ActionType;
cmd.Parameters.Add("@Status", SqlDbType.VarChar).Value = databaseLogModel.Status;
cmd.Parameters.Add("@MessageDetail", SqlDbType.VarChar).Value = databaseLogModel.MessageDetail;
cmd.Parameters.Add("@Remarks", SqlDbType.VarChar).Value = databaseLogModel.Remarks;
cmd.Parameters.Add("@CreatedBy", SqlDbType.Int).Value = databaseLogModel.CreatedBy;
cmd.Parameters.Add("@IsActive", SqlDbType.Bit).Value = databaseLogModel.IsActive;
con.Open();
cmd.ExecuteNonQuery();
}
}
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
public IActionResult AddRereshRequest([FromBody] NewRequestModel newRequest)
{
var newRequestId = Guid.NewGuid().ToString();
var user = WindowsIdentity.GetCurrent().Name;
var dbs = new List <DatabaseLogModel>();
for (var i = 0; i < newRequest.DatabaseNames.Length; i++)
{
var db = new DatabaseLogModel()
{
RefreshRequstId = newRequestId,
DatabaseName = newRequest.DatabaseNames[i]
};
dbs.Add(db);
}
var request = new RefreshRequestModel()
{
Id = newRequestId,
Environment = newRequest.Environment,
Status = "Initiated",
Requestor = user,
ScheduleDate = DateTime.Now,
ScheduledBy = user,
Databases = dbs
};
_context.RefreshRequests.Add(request);
_context.SaveChanges();
return(Ok(request));
}
private Boolean RestoreDatabase(string connStringAdmin, DatabaseLogModel dbBackupLogDataFromClient)
{
try
{
using (SqlConnection conAdmin = new SqlConnection(connStringAdmin))
{
conAdmin.Open();
using (SqlConnection conDanpheEMRLive = new SqlConnection(connString))
{
string backupFilePath = dbBackupLogDataFromClient.FolderPath + dbBackupLogDataFromClient.FileName;
conDanpheEMRLive.Open();
//set db single user mode
string sqlSetSingleUserModeQuery = string.Format("ALTER DATABASE [" + dbBackupLogDataFromClient.DatabaseName + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE");
SqlCommand sqlCmdSingleUserMode = new SqlCommand(sqlSetSingleUserModeQuery, conDanpheEMRLive);
sqlCmdSingleUserMode.ExecuteNonQuery();
//Restore Database
string sqlRestoreDBQuery = "USE MASTER RESTORE DATABASE [" + dbBackupLogDataFromClient.DatabaseName + "] FROM DISK='" + backupFilePath + "'WITH REPLACE;";
SqlCommand sqlCmdRestoreDB = new SqlCommand(sqlRestoreDBQuery, conDanpheEMRLive);
sqlCmdRestoreDB.ExecuteNonQuery();
//set db multi user mode
string sqlSetMultiUserModeQuery = string.Format("ALTER DATABASE [" + dbBackupLogDataFromClient.DatabaseName + "] SET MULTI_USER");
SqlCommand sqlCmdMultiUserMode = new SqlCommand(sqlSetMultiUserModeQuery, conDanpheEMRLive);
sqlCmdMultiUserMode.ExecuteNonQuery();
//updating client dbLog info for insert as restore database type
dbBackupLogDataFromClient.IsDBRestorable = false;
dbBackupLogDataFromClient.Action = "restore";
dbBackupLogDataFromClient.ActionType = "manual";
dbBackupLogDataFromClient.Status = "success";
dbBackupLogDataFromClient.MessageDetail = "Database restore successfully";
dbBackupLogDataFromClient.IsActive = false;
//Insert restore successfully log
Boolean dbLogResult = PostDBLog(connStringAdmin, dbBackupLogDataFromClient);
return(dbLogResult == true ? true : false);
}
}
}
catch (Exception ex)
{
throw ex;
}
}
public string Post()
{
DanpheHTTPResponse <object> responseData = new DanpheHTTPResponse <object>();
responseData.Status = "OK";
//string ipDataString = Request.Form.Keys.First<string>();
try
{
string reqType = this.ReadQueryStringData("reqType");
string ExportType = this.ReadQueryStringData("ExportType");
SystemAdminDbContext systemAdmindbContext = new SystemAdminDbContext(connStringAdmin);
#region Database Backup functionality
if (reqType == "databaseBackup")
{
//Transaction Begin
using (var dbContextTransaction = systemAdmindbContext.Database.BeginTransaction())
{
try
{
//Check Backup Folder is present or not in local system
if (CheckBackupFolderPath())
{
int todayDBBackup = CheckTodaysBackup();
SystemAdminDbContext systemAdminDbContext = new SystemAdminDbContext(connStringAdmin);
int todaysDBBackupFrequency = Convert.ToInt32((from parameters in systemAdminDbContext.AdminParameters
where parameters.ParameterName == "DaillyDBBackupLimit"
select parameters.ParameterValue
).SingleOrDefault());
if (todayDBBackup >= todaysDBBackupFrequency)
{
responseData.Status = "Failed";
responseData.ErrorMessage = "Today You have already taken " + todaysDBBackupFrequency + "DB backup";
}
else
{
//Backup Database with local directory
if (BackupDatabase(connStringAdmin))
{
if (DeleteOldBackupFiles(connStringAdmin))
{
responseData.Status = "OK";
responseData.Results = 1;
dbContextTransaction.Commit();
}
else
{
responseData.Status = "Failed";
responseData.ErrorMessage = "Backup Files deleting Error";
dbContextTransaction.Rollback();
}
}
else
{
responseData.Status = "Failed";
responseData.ErrorMessage = "Database Backup failed, Please try again";
}
}
}
else
{
responseData.Status = "Failed";
responseData.ErrorMessage = "Please create Directory(folder) first for Backup.";
}
//Commit Transaction
//dbContextTransaction.Commit();
}
catch (Exception ex)
{
//Rollback all transaction if exception occured
dbContextTransaction.Rollback();
throw ex;
}
}
}
#endregion
#region Database Restore Functionality
else if (reqType == "databaseRestore")
{
string dataString = this.ReadPostData();
DatabaseLogModel dbBackupLogDataFromClient = DanpheJSONConvert.DeserializeObject <DatabaseLogModel>(dataString);
string backupDBFilePath = dbBackupLogDataFromClient.FolderPath + dbBackupLogDataFromClient.FileName;
string backupDBFileVersion = dbBackupLogDataFromClient.DatabaseVersion;
//Transaction Begin
using (var dbContextTransaction = systemAdmindbContext.Database.BeginTransaction())
{
try
{
//Boolean x = System.IO.File.Exists("");
//Check backup file is exist or not for restore
if (System.IO.File.Exists(backupDBFilePath))
{
//check Backup dbfile and current database version compatibility
if (CheckDBVersionForRestore(backupDBFileVersion))
{
//first take backup of Database then restore
BackupDatabase(connStringAdmin);
//Restore database
if (RestoreDatabase(connStringAdmin, dbBackupLogDataFromClient))
{
responseData.Status = "OK";
responseData.Results = 1;
dbContextTransaction.Commit();
}
else
{
responseData.Status = "Failed";
responseData.ErrorMessage = "Database restore failed, Please try again";
dbContextTransaction.Rollback();
}
}
else
{
responseData.Status = "Failed";
responseData.ErrorMessage = "Version is not compatible for Restore.";
}
}
else
{
responseData.Status = "Failed";
responseData.ErrorMessage = "There is no backup file for restore, Please Try again.";
}
}
catch (Exception ex)
{
//Rollback all transaction if exception occured
dbContextTransaction.Rollback();
throw ex;
}
}
}
#endregion
#region Database Export as CSV/XML/pdf files
else if (reqType != null && reqType == "exportDBToCSVOrXMLOrPDF")
{
try
{
if (!String.IsNullOrEmpty(ExportType))
{
CoreDbContext coreDBContext = new CoreDbContext(connString);
//get local export file path for export files and send to user also
string ExportedFilePath = (from parameter in coreDBContext.Parameters
where parameter.ParameterName == "DBExportCSVXMLDirPath"
select parameter.ParameterValue
).SingleOrDefault();
//Call function to complete Export database functionality
if (ExportDatabaseToCSVOrXMLOrPDF(connString, ExportType, ExportedFilePath))
{
responseData.Status = "OK";
responseData.Results = ExportedFilePath;
}
else
{
responseData.Status = "Failed";
responseData.ErrorMessage = "Failed to Export Database as " + ExportType;
}
}
else
{
responseData.Status = "Failed";
responseData.ErrorMessage = "Please select export file type as " + ExportType;
}
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
else
{
responseData.Status = "Failed";
responseData.ErrorMessage = "request type is incorrect.";
}
}
catch (Exception ex)
{
responseData.Status = "Failed";
responseData.ErrorMessage = ex.Message + " exception details:" + ex.ToString();
}
return(DanpheJSONConvert.SerializeObject(responseData, true));
}
public void IntegrationTest()
{
var connection = TestSession.GetConnection();
connection.Open();
#region good insertion and select by id test
DatabaseLogModel inserted = new DatabaseLogModel();
inserted.PostTime = TestSession.Random.RandomDateTime();
inserted.DatabaseUser = TestSession.Random.RandomString(128);
inserted.Event = TestSession.Random.RandomString(128);
inserted.Schema = TestSession.Random.RandomString(128);
inserted.Object = TestSession.Random.RandomString(128);
inserted.TSQL = TestSession.Random.RandomString(-1);
var xml = new XmlDocument();
xml.LoadXml(@"<EVENT_INSTANCE><EventType>CREATE_TABLE</EventType><PostTime>2017-10-27T14:33:01.373</PostTime><SPID>56</SPID><ServerName>BARBKESS24\MSSQL2017RTM</ServerName><LoginName>REDMOND\barbkess</LoginName><UserName>dbo</UserName><DatabaseName>AdventureWorks2017</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>ErrorLog</ObjectName><ObjectType>TABLE</ObjectType><TSQLCommand><SetOptions ANSI_NULLS=""ON"" ANSI_NULL_DEFAULT=""ON"" ANSI_PADDING=""ON"" QUOTED_IDENTIFIER=""ON"" ENCRYPTED=""FALSE"" /><CommandText>CREATE TABLE [dbo].[ErrorLog](
[ErrorLogID][int] IDENTITY(1, 1) NOT NULL,
[ErrorTime][datetime] NOT NULL CONSTRAINT[DF_ErrorLog_ErrorTime] DEFAULT(GETDATE()),
[UserName][sysname] NOT NULL,
[ErrorNumber][int] NOT NULL,
[ErrorSeverity][int] NULL,
[ErrorState][int] NULL,
[ErrorProcedure][nvarchar](126) NULL,
[ErrorLine][int] NULL,
[ErrorMessage][nvarchar](4000) NOT NULL
) ON[PRIMARY] </CommandText></TSQLCommand></EVENT_INSTANCE>");
inserted.XmlEvent = xml;
_tested.Insert(connection, new[] { inserted });
var selectedAfterInsertion = _tested.GetByPrimaryKey(connection, new DatabaseLogModelPrimaryKey()
{
DatabaseLogID = inserted.DatabaseLogID,
});
CollectionAssert.IsNotEmpty(selectedAfterInsertion);
var selectedAfterInsert = selectedAfterInsertion.Single();
Assert.AreEqual(inserted.DatabaseLogID, selectedAfterInsert.DatabaseLogID);
Assert.AreEqual(inserted.PostTime, selectedAfterInsert.PostTime);
Assert.AreEqual(inserted.DatabaseUser, selectedAfterInsert.DatabaseUser);
Assert.AreEqual(inserted.Event, selectedAfterInsert.Event);
Assert.AreEqual(inserted.Schema, selectedAfterInsert.Schema);
Assert.AreEqual(inserted.Object, selectedAfterInsert.Object);
Assert.AreEqual(inserted.TSQL, selectedAfterInsert.TSQL);
Assert.AreEqual(inserted.XmlEvent.ToString(), selectedAfterInsert.XmlEvent.ToString());
#endregion
#region update and select by id test
inserted.PostTime = TestSession.Random.RandomDateTime();
inserted.DatabaseUser = TestSession.Random.RandomString(128);
inserted.Event = TestSession.Random.RandomString(128);
inserted.Schema = TestSession.Random.RandomString(128);
inserted.Object = TestSession.Random.RandomString(128);
inserted.TSQL = TestSession.Random.RandomString(-1);
var updatedXml = new XmlDocument();
updatedXml.LoadXml(@"<EVENT_INSTANCE><EventType>DROP_TABLE</EventType><PostTime>2017-10-27T14:33:01.373</PostTime><SPID>56</SPID><ServerName>BARBKESS24\MSSQL2017RTM</ServerName><LoginName>REDMOND\barbkess</LoginName><UserName>dbo</UserName><DatabaseName>AdventureWorks2017</DatabaseName><SchemaName>dbo</SchemaName><ObjectName>ErrorLog</ObjectName><ObjectType>TABLE</ObjectType><TSQLCommand><SetOptions ANSI_NULLS=""ON"" ANSI_NULL_DEFAULT=""ON"" ANSI_PADDING=""ON"" QUOTED_IDENTIFIER=""ON"" ENCRYPTED=""FALSE"" /><CommandText>CREATE TABLE [dbo].[ErrorLog](
[ErrorLogID][int] IDENTITY(1, 1) NOT NULL,
[ErrorTime][datetime] NOT NULL CONSTRAINT[DF_ErrorLog_ErrorTime] DEFAULT(GETDATE()),
[UserName][sysname] NOT NULL,
[ErrorNumber][int] NOT NULL,
[ErrorSeverity][int] NULL,
[ErrorState][int] NULL,
[ErrorProcedure][nvarchar](126) NULL,
[ErrorLine][int] NULL,
[ErrorMessage][nvarchar](4000) NOT NULL
) ON[PRIMARY] </CommandText></TSQLCommand></EVENT_INSTANCE>");
inserted.XmlEvent = updatedXml;
_tested.Update(connection, new[] { inserted });
var selectedAfterUpdateAddresss = _tested.GetByPrimaryKey(connection, new DatabaseLogModelPrimaryKey()
{
DatabaseLogID = inserted.DatabaseLogID,
});
CollectionAssert.IsNotEmpty(selectedAfterUpdateAddresss);
var selectedAfterUpdate = selectedAfterUpdateAddresss.Single();
Assert.AreEqual(inserted.DatabaseLogID, selectedAfterUpdate.DatabaseLogID);
Assert.AreEqual(inserted.PostTime, selectedAfterUpdate.PostTime);
Assert.AreEqual(inserted.DatabaseUser, selectedAfterUpdate.DatabaseUser);
Assert.AreEqual(inserted.Event, selectedAfterUpdate.Event);
Assert.AreEqual(inserted.Schema, selectedAfterUpdate.Schema);
Assert.AreEqual(inserted.Object, selectedAfterUpdate.Object);
Assert.AreEqual(inserted.TSQL, selectedAfterUpdate.TSQL);
Assert.AreEqual(inserted.XmlEvent.ToString(), selectedAfterUpdate.XmlEvent.ToString());
#endregion
#region delete test
_tested.Delete(connection, new[] { inserted });
var selectedAfterDeleteAddresss = _tested.GetByPrimaryKey(connection, new DatabaseLogModelPrimaryKey()
{
DatabaseLogID = inserted.DatabaseLogID,
});
CollectionAssert.IsEmpty(selectedAfterDeleteAddresss);
#endregion
connection.Close();
}
