private static bool ImportToDatabase(IEnumerable <DbRow> dbRows, SubjectImportInfo subjRandInfo)
{
String strConn = ConfigurationManager.ConnectionStrings["Halfpint"].ToString();
using (var conn = new SqlConnection(strConn))
{
conn.Open();
using (SqlTransaction trn = conn.BeginTransaction())
{
var row = 1;
foreach (var dbRow in dbRows)
{
var cmd = new SqlCommand
{
Transaction = trn,
Connection = conn,
CommandText = "AddDexcomRow",
CommandType = CommandType.StoredProcedure
};
var param = new SqlParameter("@studyId", subjRandInfo.StudyId);
cmd.Parameters.Add(param);
param = new SqlParameter("@subjectId", subjRandInfo.SubjectId);
cmd.Parameters.Add(param);
param = new SqlParameter("@siteId", subjRandInfo.SiteId);
cmd.Parameters.Add(param);
foreach (var col in dbRow.ColNameVals)
{
param = string.IsNullOrEmpty(col.Value)
? new SqlParameter("@" + col.Name, DBNull.Value)
: new SqlParameter("@" + col.Name, col.Value);
cmd.Parameters.Add(param);
}
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
trn.Rollback();
var sMsg = "dexcom import error subject: " + subjRandInfo.SubjectId + ", row: " + row;
sMsg += ex.Message;
Logger.Error(sMsg, ex);
return(false);
}
row++;
}
trn.Commit();
}
return(true);
}
}
private static bool IsValidDateRange(List<DbRow> dbRows, CgmFileInfo cgmFileInfo, SubjectImportInfo subjectImportInfo, out string message)
{
var firstCgmGlucoseDate = GetFirstCgmGlucoseDate(dbRows);
if (firstCgmGlucoseDate == null)
{
message = "Invalid date range: Could not get the first glucose date from file";
return false;
}
var lastCgmGlucoseDate = GetLastCgmGlucoseDate(dbRows);
if (lastCgmGlucoseDate == null)
{
message = "Invalid date range: Could not get the last glucose date from file";
return false;
}
//get checks first and last entries for subject
GetFirstLastChecksSensorDates(cgmFileInfo, subjectImportInfo.StudyId);
if ((cgmFileInfo.FirstChecksSensorDateTime == null || cgmFileInfo.LastChecksSensorDateTime == null))
{
message = "Invalid date range: Could not get checks first and last glucose entry dates:";
return false;
}
if ((cgmFileInfo.FirstChecksSensorDateTime.Value.Date.CompareTo(firstCgmGlucoseDate.Value.Date) > 0))
{
message = "Invalid date range: The first sensor date(" + firstCgmGlucoseDate.Value.Date.ToShortDateString() +
") is earlier than the first checks date(" +
cgmFileInfo.FirstChecksSensorDateTime.Value.ToShortDateString() +
")";
return false;
}
if ((cgmFileInfo.LastChecksSensorDateTime.Value.Date.CompareTo(lastCgmGlucoseDate.Value.Date) < 0))
{
message = "Invalid date range: The last sensor date(" + lastCgmGlucoseDate.Value.Date.ToShortDateString() +
") is later than the last checks date(" +
cgmFileInfo.LastChecksSensorDateTime.Value.ToShortDateString() +
")";
return false;
}
message = "Valid date range:" + cgmFileInfo.SubjectId;
return true;
}
private static bool ImportToDatabase(IEnumerable<DbRow> dbRows, SubjectImportInfo subjRandInfo)
{
String strConn = ConfigurationManager.ConnectionStrings["Halfpint"].ToString();
using (var conn = new SqlConnection(strConn))
{
conn.Open();
using (SqlTransaction trn = conn.BeginTransaction())
{
var row = 1;
foreach (var dbRow in dbRows)
{
var cmd = new SqlCommand
{
Transaction = trn,
Connection = conn,
CommandText = "AddDexcomRow",
CommandType = CommandType.StoredProcedure
};
var param = new SqlParameter("@studyId", subjRandInfo.StudyId);
cmd.Parameters.Add(param);
param = new SqlParameter("@subjectId", subjRandInfo.SubjectId);
cmd.Parameters.Add(param);
param = new SqlParameter("@siteId", subjRandInfo.SiteId);
cmd.Parameters.Add(param);
foreach (var col in dbRow.ColNameVals)
{
param = string.IsNullOrEmpty(col.Value)
? new SqlParameter("@" + col.Name, DBNull.Value)
: new SqlParameter("@" + col.Name, col.Value);
cmd.Parameters.Add(param);
}
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
trn.Rollback();
var sMsg = "dexcom import error subject: " + subjRandInfo.SubjectId + ", row: " + row;
sMsg += ex.Message;
Logger.Error(sMsg, ex);
return false;
}
row++;
}
trn.Commit();
}
return true;
}
}
private static List<SubjectImportInfo> GetRandimizedStudies(int site)
{
var list = new List<SubjectImportInfo>();
String strConn = ConfigurationManager.ConnectionStrings["Halfpint"].ToString();
SqlDataReader rdr = null;
using (var conn = new SqlConnection(strConn))
{
try
{
var cmd = new SqlCommand("", conn) { CommandType = CommandType.StoredProcedure, CommandText = "GetRandomizedStudiesForImportForSite" };
var param = new SqlParameter("@siteID", site);
cmd.Parameters.Add(param);
conn.Open();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
var ci = new SubjectImportInfo { SiteId = site };
var pos = rdr.GetOrdinal("ID");
ci.RandomizeId = rdr.GetInt32(pos);
pos = rdr.GetOrdinal("SubjectId");
ci.SubjectId = rdr.GetString(pos).Trim();
pos = rdr.GetOrdinal("StudyId");
ci.StudyId = rdr.GetInt32(pos);
pos = rdr.GetOrdinal("Arm");
ci.Arm = rdr.GetString(pos);
pos = rdr.GetOrdinal("IsCgmImported");
ci.IsCgmImported = !rdr.IsDBNull(pos) && rdr.GetBoolean(pos);
pos = rdr.GetOrdinal("ChecksLastRowImported");
ci.LastRowImported = !rdr.IsDBNull(pos) ? rdr.GetInt32(pos) : 0;
pos = rdr.GetOrdinal("DateCompleted");
if (!rdr.IsDBNull(pos))
{
ci.DateCompleted = rdr.GetDateTime(pos);
ci.SubjectCompleted = true;
}
pos = rdr.GetOrdinal("DateRandomized");
if (!rdr.IsDBNull(pos))
ci.DateRandomized = rdr.GetDateTime(pos);
pos = rdr.GetOrdinal("SiteName");
ci.SiteName = rdr.GetString(pos);
list.Add(ci);
}
rdr.Close();
}
catch (Exception ex)
{
Logger.Error(ex);
}
finally
{
if (rdr != null)
rdr.Close();
}
}
return list;
}
private static List <SubjectImportInfo> GetRandimizedStudies(int site)
{
var list = new List <SubjectImportInfo>();
String strConn = ConfigurationManager.ConnectionStrings["Halfpint"].ToString();
SqlDataReader rdr = null;
using (var conn = new SqlConnection(strConn))
{
try
{
var cmd = new SqlCommand("", conn)
{
CommandType = CommandType.StoredProcedure, CommandText = "GetRandomizedStudiesForImportForSite"
};
var param = new SqlParameter("@siteID", site);
cmd.Parameters.Add(param);
conn.Open();
rdr = cmd.ExecuteReader();
while (rdr.Read())
{
var ci = new SubjectImportInfo {
SiteId = site
};
var pos = rdr.GetOrdinal("ID");
ci.RandomizeId = rdr.GetInt32(pos);
pos = rdr.GetOrdinal("SubjectId");
ci.SubjectId = rdr.GetString(pos).Trim();
pos = rdr.GetOrdinal("StudyId");
ci.StudyId = rdr.GetInt32(pos);
pos = rdr.GetOrdinal("Arm");
ci.Arm = rdr.GetString(pos);
pos = rdr.GetOrdinal("IsCgmImported");
ci.IsCgmImported = !rdr.IsDBNull(pos) && rdr.GetBoolean(pos);
pos = rdr.GetOrdinal("ChecksLastRowImported");
ci.LastRowImported = !rdr.IsDBNull(pos) ? rdr.GetInt32(pos) : 0;
pos = rdr.GetOrdinal("DateCompleted");
if (!rdr.IsDBNull(pos))
{
ci.DateCompleted = rdr.GetDateTime(pos);
ci.SubjectCompleted = true;
}
pos = rdr.GetOrdinal("DateRandomized");
if (!rdr.IsDBNull(pos))
{
ci.DateRandomized = rdr.GetDateTime(pos);
}
pos = rdr.GetOrdinal("SiteName");
ci.SiteName = rdr.GetString(pos);
list.Add(ci);
}
rdr.Close();
}
catch (Exception ex)
{
Logger.Error(ex);
}
finally
{
if (rdr != null)
{
rdr.Close();
}
}
}
return(list);
}
private static bool IsValidDateRange(List <DbRow> dbRows, CgmFileInfo cgmFileInfo, SubjectImportInfo subjectImportInfo, out string message)
{
var firstCgmGlucoseDate = GetFirstCgmGlucoseDate(dbRows);
if (firstCgmGlucoseDate == null)
{
message = "Invalid date range: Could not get the first glucose date from file";
return(false);
}
var lastCgmGlucoseDate = GetLastCgmGlucoseDate(dbRows);
if (lastCgmGlucoseDate == null)
{
message = "Invalid date range: Could not get the last glucose date from file";
return(false);
}
//get checks first and last entries for subject
GetFirstLastChecksSensorDates(cgmFileInfo, subjectImportInfo.StudyId);
if ((cgmFileInfo.FirstChecksSensorDateTime == null || cgmFileInfo.LastChecksSensorDateTime == null))
{
message = "Invalid date range: Could not get checks first and last glucose entry dates:";
return(false);
}
if ((cgmFileInfo.FirstChecksSensorDateTime.Value.Date.CompareTo(firstCgmGlucoseDate.Value.Date) > 0))
{
message = "Invalid date range: The first sensor date(" + firstCgmGlucoseDate.Value.Date.ToShortDateString() +
") is earlier than the first checks date(" +
cgmFileInfo.FirstChecksSensorDateTime.Value.ToShortDateString() +
")";
return(false);
}
if ((cgmFileInfo.LastChecksSensorDateTime.Value.Date.CompareTo(lastCgmGlucoseDate.Value.Date) < 0))
{
message = "Invalid date range: The last sensor date(" + lastCgmGlucoseDate.Value.Date.ToShortDateString() +
") is later than the last checks date(" +
cgmFileInfo.LastChecksSensorDateTime.Value.ToShortDateString() +
")";
return(false);
}
message = "Valid date range:" + cgmFileInfo.SubjectId;
return(true);
}
