//public static void UpdateM(Mdata m)
public static void UpdateM(string subjID, string smsIDs, string measstatusID, string measstatusdetailID, string date, string clinician, string notes)
{
SQL_utils sql = new SQL_utils("backend");
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("smsIDs", smsIDs, "text"));
ps.Add(sql.CreateParam("measstatusID", measstatusID, "int"));
ps.Add(sql.CreateParam("measstatusdetailID", measstatusdetailID, "int"));
ps.Add(sql.CreateParam("date", date, "date"));
ps.Add(sql.CreateParam("clinician", clinician, "int"));
ps.Add(sql.CreateParam("notes", notes, "text"));
sql.StringScalar_from_ProcName("spTracking_SubjInfo_M_UPDATE", ps);
sql.Close();
}
protected void gvFiles_RowCommand(object sender, GridViewCommandEventArgs e)
{
SQL_utils sql = new SQL_utils("backend");
string param = e.CommandArgument.ToString();
if (e.CommandName == "DeleteThisFile")
{
int filepk;
bool isint = int.TryParse(param, out filepk);
if (isint)
{
string del = sql.StringScalar_from_ProcName("gev.spFiles_DELETE",
sql.CreateParam("filepk", filepk.ToString(), "int"));
}
}
lblSubmitInfo.Text = "";
ListGeneEventFiles();
}
//public static void UpdateM(Mdata m)
public static void UpdateA(string actionIDs, string actionstatusID, string respforID, string CompletedBy, string datedone,
string starttime, string endtime, string location, string notes)
{
SQL_utils sql = new SQL_utils("backend");
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("actionIDs", actionIDs, "text"));
ps.Add(sql.CreateParam("actionstatusID", actionstatusID, "int"));
ps.Add(sql.CreateParam("respforID", respforID, "int"));
ps.Add(sql.CreateParam("CompletedBy", CompletedBy, "int"));
ps.Add(sql.CreateParam("datedone", datedone, "text"));
ps.Add(sql.CreateParam("starttime", starttime, "text"));
ps.Add(sql.CreateParam("endtime", endtime, "text"));
ps.Add(sql.CreateParam("location", location, "text"));
ps.Add(sql.CreateParam("notes", notes, "text"));
sql.StringScalar_from_ProcName("spTracking_SubjInfo_A_UPDATE", ps);
sql.Close();
}
protected void gridP_DeleteCommand(object sender, GridRecordEventArgs e)
{
int pk = Convert.ToInt32(e.Record["personID"]);
if (pk > 0)
{
SQL_utils sql = new SQL_utils("backend");
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("personID", pk.ToString(), "int"));
string result = sql.StringScalar_from_ProcName("hh.spPerson_DELETE", ps);
sql.Close();
//oboutGrid_utils o = new oboutGrid_utils();
//o.oGrid_DeleteData("tblAddress", "backend", "dbo", "addressID", pk);
//RefreshGrid(gridAddress, "hh.spAddress_by_HouseholdID", Request.QueryString["hhID"]);
}
}
//public static void UpdateM(Mdata m)
public static void UpdateAddress(string mode, string HouseholdID, string addressID, string address1, string address2, string city, string state, string zip,
string country, string notes)
{
SQL_utils sql = new SQL_utils("backend");
List <SqlParameter> ps = new List <SqlParameter>();
ps.Add(sql.CreateParam("mode", mode, "text"));
ps.Add(sql.CreateParam("HouseholdID", HouseholdID, "int"));
ps.Add(sql.CreateParam("addressID", addressID, "int"));
ps.Add(sql.CreateParam("address1", address1, "text"));
ps.Add(sql.CreateParam("address2", address1, "text"));
ps.Add(sql.CreateParam("city", city, "text"));
ps.Add(sql.CreateParam("state", state, "text"));
ps.Add(sql.CreateParam("zip", zip, "text"));
ps.Add(sql.CreateParam("country", country, "text"));
ps.Add(sql.CreateParam("notes", notes, "text"));
sql.StringScalar_from_ProcName("hh.spAddress_MODIFY", ps);
sql.Close();
}
//public static void UpdateM(Mdata m)
public static void UpdateP(string mode, string HouseholdID, string personID, string last, string first, string middle, string nickname, string lastBC,
string city, string state, string sex, string dobtxt, string role, string ethnicityID, string hispanicID, string maincontact, string FapersonID, string MopersonID, string notes)
{
SQL_utils sql = new SQL_utils("backend");
List <SqlParameter> ps = new List <SqlParameter>();
int maincontact01 = (maincontact == "True") ? 1 : 0;
ps.Add(sql.CreateParam("mode", mode, "text"));
ps.Add(sql.CreateParam("HouseholdID", HouseholdID, "int"));
ps.Add(sql.CreateParam("personID", personID, "int"));
ps.Add(sql.CreateParam("last", last, "text"));
ps.Add(sql.CreateParam("first", first, "text"));
ps.Add(sql.CreateParam("middle", middle, "text"));
ps.Add(sql.CreateParam("nickname", nickname, "text"));
ps.Add(sql.CreateParam("lastBC", lastBC, "text"));
ps.Add(sql.CreateParam("city", city, "text"));
ps.Add(sql.CreateParam("state", state, "text"));
ps.Add(sql.CreateParam("sex", sex, "text"));
ps.Add(sql.CreateParam("dobtxt", dobtxt, "text"));
ps.Add(sql.CreateParam("role", role, "int"));
ps.Add(sql.CreateParam("EthnicityID", ethnicityID, "int"));
ps.Add(sql.CreateParam("HispanicID", hispanicID, "int"));
ps.Add(sql.CreateParam("MainContact", maincontact01.ToString(), "int"));
ps.Add(sql.CreateParam("FapersonID", FapersonID, "text"));
ps.Add(sql.CreateParam("MopersonID", MopersonID, "text"));
ps.Add(sql.CreateParam("notes", notes, "text"));
sql.StringScalar_from_ProcName("hh.spPerson_MODIFY", ps);
sql.Close();
}
protected void SaveDataTableToSQL(DataTable dt, int filepk)
{
dt.Columns.Add("filepk", typeof(int));
dt.Columns.Add("pk", typeof(int));
int counter = 1;
foreach (DataRow row in dt.Rows)
{
row["filepk"] = filepk;
row["pk"] = counter;
counter++;
}
DataColumn[] pkcol = new DataColumn[1];
pkcol[0] = dt.Columns["pk"];
pkcol[0].AutoIncrement = true;
dt.PrimaryKey = pkcol;
string DestinationTableName = "file" + filepk.ToString("D3");
SQL_utils sql = new SQL_utils("backend");
string createSQL = sql.GetCreateTableSql(dt, "gev", DestinationTableName, false);
string createTable = sql.StringScalar_from_SQLstring(createSQL);
//If there is a max column, truncate data to left(2000)
if (createSQL.Contains("/*TOOLONG*/"))
{
lblSubmitInfo.Text += "Warning: Some columns were truncated to 200 characters.<br/><br/>";
lblSubmitInfo.ForeColor = System.Drawing.Color.DarkRed;
foreach (DataRow row in dt.Rows)
{
foreach (DataColumn col in dt.Columns)
{
if (row[col.ColumnName].ToString().Length > 200)
{
row[col.ColumnName] = row[col.ColumnName].ToString().PadRight(200).Substring(0, 200).Trim();
}
}
}
}
// Create the SqlBulkCopy object.
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sql.SqlConnection))
{
bulkCopy.DestinationTableName = "gev." + DestinationTableName;
try
{
// Write from the source to the destination.
bulkCopy.WriteToServer(dt);
string nrows_inserted = sql.StringScalar_from_ProcName("gev.spEvents_EXTRACT",
sql.CreateParam("filepk", filepk.ToString(), "int"));
string prefix = (filepk < 10) ? "00" : (filepk < 100) ? ")" : "";
int n = sql.IntScalar_from_SQLstring("select count(*) from gev.file" + prefix + filepk.ToString());
lblSubmitInfo.Text += n.ToString() + " rows inserted.";
lblSubmitInfo.ForeColor = System.Drawing.Color.Blue;
}
catch (Exception ex)
{
lblSubmitInfo.Text += (ex.Message);
//Delete this file as it was not successfully oploaded.
string del = sql.StringScalar_from_ProcName("gev.spFiles_DELETE",
sql.CreateParam("filepk", filepk.ToString(), "int"));
}
}
ListGeneEventFiles();
}
protected void SaveSheet(int idx)
{
SQL_utils sql = new SQL_utils("backend");
GridViewRow row = gvSheets.Rows[idx - 1];
bool validSelections = true;
string[] ddl_values = new string[6];
if (row.RowType == DataControlRowType.DataRow)
{
for (int i = 0; i < 6; i++)
{
DropDownList ddl = (DropDownList)row.FindControlRecursive("ddl" + i.ToString());
ddl_values[i] = ddl.SelectedValue;
if (ddl.SelectedValue == "..select..")
{
validSelections = false;
}
}
if (!validSelections)
{
lblSubmitInfo.Text = "Select a value for each parameter. (Use **NA** if the parameter is not present in the data).";
lblSubmitInfo.ForeColor = System.Drawing.Color.Red;
}
else
{ //Proceed - no invalid selections
lblSubmitInfo.Text = "";
List <SqlParameter> p = new List <SqlParameter>();
DataTable dt = dset.Tables[idx - 1];
int filepk = 0;
filepk = sql.IntScalar_from_SQLstring("select coalesce(max(filepk) + 1,1) from gev.files");
DataColumn methodCol = new System.Data.DataColumn("method", typeof(System.String));
methodCol.DefaultValue = ddl_values[1];
dt.Columns.Add(methodCol);
p.Add(sql.CreateParam("filepk", filepk.ToString(), "int"));
p.Add(sql.CreateParam("filename", FileName, "text"));
p.Add(sql.CreateParam("sheetname", dt.TableName, "text"));
p.Add(sql.CreateParam("numrows", dt.Rows.Count.ToString(), "int"));
p.Add(sql.CreateParam("numcols", dt.Columns.Count.ToString(), "int"));
p.Add(sql.CreateParam("col_id", ddl_values[0], "text"));
p.Add(sql.CreateParam("col_method", "method", "text"));
p.Add(sql.CreateParam("col_gene", ddl_values[2], "text"));
p.Add(sql.CreateParam("col_region", ddl_values[3], "text"));
p.Add(sql.CreateParam("col_eventtype", ddl_values[4], "text"));
p.Add(sql.CreateParam("col_inheritance", ddl_values[5], "text"));
//OUTPUT Parameter not working
//p.Add(Master.SQL.CreateParam("output", "output", "int", "output"));
string p_vals =
"filename " + FileName + "<br/>" +
"sheetname " + dt.TableName + "<br/>" +
"numrows " + dt.Rows.Count.ToString() + "<br/>" +
"numcols " + dt.Columns.Count.ToString() + "<br/>" +
"col_id " + ddl_values[0] + "<br/>" +
"col_method " + ddl_values[1] + "<br/>" +
"col_gene " + ddl_values[2] + "<br/>" +
"col_region " + ddl_values[3] + "<br/>" +
"col_eventtype " + ddl_values[4] + "<br/>" +
"col_inheritance " + ddl_values[5] + "<br/>";
string sfilepk = "";
try
{
sfilepk = sql.StringScalar_from_ProcName("gev.spFiles_INSERT", p);
lblSubmitInfo.Text = "New filepk = " + filepk.ToString() + ". ";
//Hide this row
gvSheets.Rows[idx - 1].Visible = false;
int n_visible = 0;
for (int r = 0; r < gvSheets.Rows.Count; r++)
{
if (gvSheets.Rows[r].Visible == true)
{
n_visible++;
}
}
if (n_visible == 0)
{
panelSheets.Visible = false;
}
if (filepk > 0)
{
SaveDataTableToSQL(dt, filepk);
}
}
catch (Exception ex)
{
lblSubmitInfo.Text += sfilepk + "<br/>" + ex.Message;
}
}
}
}