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); }