private bool connectToServer()
{
try
{
connection = new QueryableConnection();
connection.ServerName = txtServer.Text;
connection.DatabaseName = txtSecurity.Text;
var password = new SecureString();
foreach (char x in txtPassword.Text)
{
password.AppendChar(x);
}
password.MakeReadOnly();
if (!chkWindAuth.Checked)
{
connection.SqlCredential = new SqlCredential(txtUsername.Text, password);
}
instanceService = new InstanceService();
instance = instanceService.GetInstance(connection);
return(true);
}
catch
{
return(false);
}
}
/// <summary>
///
/// </summary>
/// <param name="theSql"></param>
/// <returns></returns>
public static List <String> getValidItemList(ref string errorMessage, string theSql)
{
List <String> theList = new List <String>();
//Connecting
connection = new QueryableConnection();
connection.ServerName = thisStudy.server;
connection.DatabaseName = thisStudy.dataDatabase;
//Running Query
var x = connection.GetDataTable(theSql);
if (x.Rows.Count == 0)
{
errorMessage += "No Valid Items Found";
return(theList);
}
else
{
foreach (DataRow row in x.Rows)
{
theList.Add(row["Item"].ToString());
}
}
return(theList);
}
/// <summary>
/// Check if a valid site or not
/// </summary>
/// <param name="errorMessage"></param>
/// <param name="siteIDString"></param>
/// <returns></returns>
public static bool validInputParam(ref string errorMessage, InputParamItem inputParam, string inputItem)
{
if (String.IsNullOrEmpty(inputItem))
{
errorMessage += "A Parameter cannot be empty\n";
return(false);
}
//ADD IN CHECK FOR SITE EXISTING
//Connecting
connection = new QueryableConnection();
connection.ServerName = thisStudy.server;
connection.DatabaseName = thisStudy.dataDatabase;
String[] inputItems = inputItem.Split(',');
// string siteList = "";
// siteList = getQueryableList(siteIDString);
foreach (String currItem in inputItems)
{
string checkQuery = inputParam.sqlValidation.Replace("{CSV_ITEM}", currItem);
checkQuery = checkQuery.Replace("{DESIGNDB}", thisStudy.designDatabase);
//If the lists are populated use this, else query this
if (!string.IsNullOrWhiteSpace(inputParam.validItems))
{
List <String> checkListItems = inputParam.validItems.Split(',').ToList();
if (!checkListItems.Contains(currItem))
{
errorMessage += inputParam.paramDesc + ": " + currItem + " not found \n";
return(false);
}
}
else
{
//Only run if we have validation
if (!String.IsNullOrWhiteSpace(checkQuery))
{
//Running Query
var x = connection.GetDataTable(checkQuery);
if (x.Rows.Count == 0)
{
errorMessage += inputParam.paramDesc + ": " + currItem + " not found \n";
return(false);
}
}
}
}
return(true);
}
private void btnVerify_Click(object sender, EventArgs e)
{
//reset
fastTextOutput.Text = "";
btnGenerateScript.Enabled = false;
initialiseQuery();
if (!studiesSetUp())
{
return;
}
String displayMessage = rtbStudyVerifications.Text + "\n";
if (!updateTypeSelected())
{
return;
}
bool isValid = true;
//Check up study and initialise
if (!validStudy(ref displayMessage, txtStudyID.Text))
{
rtbStudyVerifications.Text = displayMessage;
return;
}
//Convert Update_Param object to string so we can replace all the placeholders
//We will turn the string back into object after
XmlSerializer xsSubmit = new System.Xml.Serialization.XmlSerializer(typeof(Update_param));
string paramXml = "";
using (var sww = new System.IO.StringWriter())
{
using (XmlWriter writer = XmlWriter.Create(sww))
{
xsSubmit.Serialize(writer, updateParam);
paramXml = sww.ToString(); // Your XML
}
}
//Read in the list of queries
string queriesXml = scriptXmlText;
foreach (var x in updateParam.inputParamList)
{
paramXml = paramXml.Replace('{' + x.paramName + '}', x.value);
queriesXml = queriesXml.Replace('{' + x.paramName + '}', x.value);
}
paramXml = paramXml.Replace("{DESIGNDB}", thisStudy.designDatabase);
queriesXml = queriesXml.Replace("{DESIGNDB}", thisStudy.designDatabase);
//Convert paramXml to dcrParams object
TextReader txtReader = new StringReader(paramXml);
//This creates xml based on the QueryList object
XmlSerializer serializer = new XmlSerializer(typeof(Update_param));
updateParam = (Update_param)serializer.Deserialize(txtReader);
txtReader.Close();
if (updateParam.paramList.Count == 0)
{
rtbStudyVerifications.Text += "\n\nReturned Settings: N/A";
}
else
{
//Connecting
connection = new QueryableConnection();
connection.ServerName = thisStudy.server;
connection.DatabaseName = thisStudy.dataDatabase;
String paramSql = "";
foreach (ParamItem x in updateParam.paramList)
{
//If we need to check for a table existing first
if (!String.IsNullOrEmpty(x.requiredTables))
{
String tableList = getQueryableList(x.requiredTables);
string reqTablesSql = "Select * from Information_schema.tables where Table_Name in (" + tableList + ")";
var output = connection.GetDataTable(reqTablesSql);
if (output.Rows.Count == 0)
{
displayMessage += "Not all of the following tables are found: " + x.requiredTables + "\n";
continue;
}
}
//If we can ... set up the query
if (String.IsNullOrEmpty(paramSql))
{
paramSql = x.query;
}
else
{
paramSql = x.query + " UNION " + paramSql;
}
}
if (paramSql.ToUpper().Contains("INSERT ") || paramSql.ToUpper().Contains("UPDATE ") || paramSql.ToUpper().Contains("DELETE "))
{
MessageBox.Show("Params.xml contains either 'INSERT ', 'UPDATE ' or 'DELETE '. This isnt allowed.");
return;
}
try
{
//Get all the parameters
var paramOutput = connection.GetDataTable(paramSql);
if (displayMessage != "")
{
displayMessage += "\n";
}
displayMessage += "Returned Settings\n==============";
foreach (DataRow row in paramOutput.Rows)
{
string param = row["Param"].ToString();
string desc = row["Desc"].ToString();
string val = row["Val"].ToString();
displayMessage += "\n" + desc + ": " + val;
// var siteID = row["SiteID"].ToString();
queriesXml = queriesXml.Replace('{' + param.ToUpper() + '}', val);
}
rtbStudyVerifications.Text = displayMessage;
}
catch
{
rtbStudyVerifications.Text = "Error runnning parameters sql: \n" + paramSql;
return;
}
}
TextReader queryReader = new StringReader(queriesXml);
//This creates xml based on the QueryList object
XmlSerializer queriesSerializer = new XmlSerializer(typeof(DCR_Script));
DCR_Script dcrUpdates;
dcrUpdates = (DCR_Script)queriesSerializer.Deserialize(queryReader);
updateQueryObj = dcrUpdates.updateQueries.First();
queryReader.Close();
if (isValid)
{
btnGenerateScript.Enabled = true;
}
}
private string setUpQuery(string inputString, string inputList, StudyDetail thisStudy, QueryableConnection connection)
{
if (this.updateType == "DELETE_SITE")
{
string siteIDSQL = @"DECLARE @SiteIDs VARCHAR(MAX)
SELECT @SiteIDs = COALESCE(@SiteIDs + ',','')+isnull(Convert(varchar(max),SIteID),'N/A')
FROM Sites WHERE CentreID in (" + inputList + @")
SELECT @SiteIDs as SiteIDList";
var dataTable = connection.GetDataTable(siteIDSQL);
string siteIDList = dataTable.Rows[0]["SiteIDList"].ToString();
inputString = inputString.Replace("{CENTREID_CSV}", inputList);
inputString = inputString.Replace("{SITEID_CSV}", siteIDList);
}
if (this.updateType == "DELETE_PATIENT")
{
string patIdSql = @"DECLARE @PatientIDs VARCHAR(MAX)
SELECT @PatientIDs = COALESCE(@PatientIDs + ',','')+isnull(Convert(varchar(max),PatientID),'N/A')
FROM Patients WHERE PatientCode in (" + inputList + @")
SELECT @PatientIDs as PatientIDList";
var dataTable = connection.GetDataTable(patIdSql);
string patIDList = dataTable.Rows[0]["PatientIDList"].ToString();
inputString = inputString.Replace("{PATIENTCODE_CSV}", inputList);
inputString = inputString.Replace("{PATIENTID_CSV}", patIDList);
}
inputString = inputString.Replace("{DESIGNDB}", thisStudy.designDatabase);
return(inputString);
}
public void setUpQueryForStudy(string siteList, StudyDetail thisStudy, QueryableConnection connection)
{
this.updateSql = setUpQuery(this.updateSql, siteList, thisStudy, connection);
this.inclusionSql = setUpQuery(this.inclusionSql, siteList, thisStudy, connection);
}