public async Task <IActionResult> Get()
{
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader reader = null;
String queryString = "";
String strErrMsg = "";
HttpStatusCode errorCode = HttpStatusCode.OK;
List <DBVersionViewModel> listVM = new List <DBVersionViewModel>();
try
{
using (conn = new SqlConnection(Startup.DBConnectionString))
{
await conn.OpenAsync();
queryString = @"SELECT [VersionID]
,[ReleasedDate]
,[AppliedDate]
FROM [dbo].[t_dbversion]
ORDER BY [VersionID] DESC";
cmd = new SqlCommand(queryString, conn);
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
DBVersionViewModel vm = new DBVersionViewModel();
vm.VersionID = reader.GetInt32(0);
vm.ReleasedDate = reader.GetDateTime(1);
vm.AppliedDate = reader.GetDateTime(2);
listVM.Add(vm);
}
}
}
}
catch (Exception exp)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(exp.Message);
#endif
strErrMsg = exp.Message;
if (errorCode == HttpStatusCode.OK)
{
errorCode = HttpStatusCode.InternalServerError;
}
}
finally
{
if (reader != null)
{
reader.Dispose();
reader = null;
}
if (cmd != null)
{
cmd.Dispose();
cmd = null;
}
if (conn != null)
{
conn.Close();
conn.Dispose();
}
}
if (errorCode != HttpStatusCode.OK)
{
switch (errorCode)
{
case HttpStatusCode.Unauthorized:
return(Unauthorized());
case HttpStatusCode.NotFound:
return(NotFound());
case HttpStatusCode.BadRequest:
return(BadRequest(strErrMsg));
default:
return(StatusCode(500, strErrMsg));
}
}
return(new JsonResult(listVM));
}
public async Task <IActionResult> Post()
{
SqlConnection conn = null;
SqlCommand cmd = null;
SqlDataReader reader = null;
SqlTransaction tran = null;
String queryString = "";
String strErrMsg = "";
HttpStatusCode errorCode = HttpStatusCode.OK;
DBVersionViewModel vmCurrent = new DBVersionViewModel();
try
{
using (conn = new SqlConnection(Startup.DBConnectionString))
{
await conn.OpenAsync();
queryString = @"SELECT TOP (1) [VersionID]
,[ReleasedDate]
,[AppliedDate]
FROM [dbo].[t_dbversion]
ORDER BY [VersionID] DESC";
cmd = new SqlCommand(queryString, conn);
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
vmCurrent.VersionID = reader.GetInt32(0);
vmCurrent.ReleasedDate = reader.GetDateTime(1);
vmCurrent.AppliedDate = reader.GetDateTime(2);
// Only 1 row
break;
}
}
else
{
vmCurrent.VersionID = 0;
}
reader.Close();
reader = null;
cmd.Dispose();
cmd = null;
if (vmCurrent.VersionID < DBVersionCheckController.CurrentVersion)
{
var nver = vmCurrent.VersionID + 1;
while (nver <= DBVersionCheckController.CurrentVersion)
{
tran = conn.BeginTransaction();
// Update the DB version
await updateDBVersion(conn, tran, nver ++);
tran.Commit();
}
}
else if (vmCurrent.VersionID > CurrentVersion)
{
strErrMsg = "Contact system administrator";
throw new Exception(strErrMsg);
}
}
}
catch (Exception exp)
{
#if DEBUG
System.Diagnostics.Debug.WriteLine(exp.Message);
#endif
strErrMsg = exp.Message;
if (errorCode == HttpStatusCode.OK)
{
errorCode = HttpStatusCode.InternalServerError;
}
if (tran != null)
{
tran.Rollback();
}
}
finally
{
if (tran != null)
{
tran.Dispose();
tran = null;
}
if (reader != null)
{
reader.Dispose();
reader = null;
}
if (cmd != null)
{
cmd.Dispose();
cmd = null;
}
if (conn != null)
{
conn.Dispose();
conn = null;
}
}
if (errorCode != HttpStatusCode.OK)
{
switch (errorCode)
{
case HttpStatusCode.Unauthorized:
return(Unauthorized());
case HttpStatusCode.NotFound:
return(NotFound());
case HttpStatusCode.BadRequest:
return(BadRequest(strErrMsg));
default:
return(StatusCode(500, strErrMsg));
}
}
return(Ok());
}
