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 bool setUpParams()
{
String searchString = "Scripts/" + cmbUpdateType.Text + ".xml";
try
{
//Read in the xml
System.IO.StreamReader objReader;
objReader = new System.IO.StreamReader(searchString);
scriptXmlText = objReader.ReadToEnd();
objReader.Close();
}
catch
{
MessageBox.Show("Error reading: " + searchString);
return(false);
}
//Convert scriptXmlText to dcrParams object
TextReader txtReader = new StringReader(scriptXmlText);
//This creates xml based on the QueryList object
XmlSerializer serializer = new XmlSerializer(typeof(DCR_Script));
//Sets up the entire script
dcrScript = (DCR_Script)serializer.Deserialize(txtReader);
txtReader.Close();
//Set up the updateParam to be refreshed
updateParam = dcrScript.updateParamList.First();
string displayMessage = "";
//Check up study and initialise
if (validStudy(ref displayMessage, txtStudyID.Text))
{
if (updateParam.inputParamList.Count == 0)
{
rtbStudyVerifications.Text = "Parameters: N/A";
btnVerify.Enabled = true;
}
else
{
FormGetParams frmGetParams = new FormGetParams();
if (frmGetParams.ShowDialog() == DialogResult.OK)
{
string paramText = "Parameters:\n=========";
foreach (var x in updateParam.inputParamList)
{
paramText += "\n" + x.paramDesc + ": " + x.value;
}
rtbStudyVerifications.Text = paramText;
btnVerify.Enabled = true;
}
}
}
else
{
MessageBox.Show(displayMessage);
}
return(true);
}
