public void WhenIUpdateExistingItemDataOfAPatientUsingConsumeODMStrTransactionalWebserviceWithBelowDataAndOdmfile_(string patientIdentity, Table table)
{
List <string> RepFields = new List <string>(table.Header);
foreach (TableRow row in table.Rows)
{
List <string> RepData = new List <string>();
foreach (string col in RepFields)
{
if (col == "ODMDATA")
{
string ODMText = RequestResponseHandler.GetODMText(row[col]);
if (patientIdentity != null && patientIdentity != "")
{
SQLInfo Info = StoreSQLInfo();
string QueryToExecute = "Select subjectkey from Patient where PatientIdentity = '" + patientIdentity + "'";
Info.QueryResultTable = DatabaseUtilities.GetRecordsFromDB(QueryToExecute, Info);
DataTable data = Info.QueryResultTable;
string SubjectKey = string.Empty;
SubjectKey = data.Rows[0].ItemArray[0].ToString();
bool exist = ODMText.Contains("SubjectKeyDataValue");
ODMText = ODMText.Replace("SubjectKeyDataValue", SubjectKey);
}
RepData.Add("<![CDATA[" + ODMText + "]]>");
}
else
{
RepData.Add(row[col]);
}
}
ScenarioContext.Current["StatusCode"] = RequestResponseHandler.HandleSyncRequest(ConfigurationHelper.WebServiceEndPoint + "/" + ConfigurationHelper.DLInstanceName, "POST", "ConsumeODM1_StrTransactional", RepFields, RepData);
}
}
private void Page_Load(object sender, System.EventArgs e)
{
string xmlPath = Server.MapPath("customers.xml");
SQLGenerator sqlGenerator = new SQLGenerator();
SQLInfo sqlInfo = sqlGenerator.CreateSQLStatement(xmlPath);
if (sqlInfo.Status == 0)
{
Response.Write("<b>SQL Output:</b><p />" + sqlInfo.SQL + "<p />");
/*
* Execute the SQL statement
* SqlConnection dataConn = null;
* try {
* string connStr = ConfigurationSettings.AppSettings["dsn"];
* dataConn = new SqlConnection(connStr);
* SqlCommand cmd = new SqlCommand(sqlInfo.SQL,dataConn);
* dataConn.Open();
* cmd.ExecuteNonQuery();
* Response.Write("<b>Database updated!</b>");
* }
* catch (Exception exp) {
* Response.Write("<b>Error: </b>" + exp.Message);
* }
* finally {
* dataConn.Close();
* }
*/
}
else
{
Response.Write("<b>Error Occurred:</b><p />" + sqlInfo.StatusMessage);
}
}
public void WhenIAddAItemLevelDCFUsingDCFCreateFormLevelDCFWebserviceWithBelowData_(Table table)
{
List <string> RepFields = new List <string>(table.Header);
foreach (TableRow row in table.Rows)
{
List <string> RepData = new List <string>();
foreach (string col in RepFields)
{
if (col == "SubjectKeyData")
{
SQLInfo Info = StoreSQLInfo();
string QueryToExecute = "Select subjectkey from Patient where PatientIdentity = '" + row[col] + "'";
Info.QueryResultTable = DatabaseUtilities.GetRecordsFromDB(QueryToExecute, Info);
DataTable data = Info.QueryResultTable;
string SubjectKey = string.Empty;
SubjectKey = data.Rows[0].ItemArray[0].ToString();
RepData.Add(SubjectKey);
}
else
{
RepData.Add(row[col]);
}
}
ScenarioContext.Current["StatusCode"] = RequestResponseHandler.HandleSyncRequest(ConfigurationHelper.WebServiceEndPoint + "/" + ConfigurationHelper.DLInstanceName, "POST", "CreateItemLevelDCF", RepFields, RepData);
}
}
public static bool ExecuteUpdateQuery(string SqlQuery, SQLInfo DBInfo)
{
try
{
if (!IsDBConnectionAlive(DBInfo))
{
if (!ConnectToDB(DBInfo))
{
return(false);
}
}
SqlCommand sqlCommand = DBInfo.DBConnection.CreateCommand();
sqlCommand.Connection = DBInfo.DBConnection;
sqlCommand.CommandText = SqlQuery;
sqlCommand.ExecuteNonQuery();
CloseDBConnection(DBInfo);
return(true);
}
catch (Exception e)
{
CloseDBConnection(DBInfo);
new DebugLogGenerator().WriteException(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, e);
return(false);
}
}
public static DataTable GetRecordsFromDB(string SqlQuery, SQLInfo DBInfo)
{
try
{
DataSet dataset = new DataSet();
//SQLInfo DBInfo = new SQLInfo();
if (!IsDBConnectionAlive(DBInfo))
{
if (!ConnectToDB(DBInfo))
{
return(null);
}
}
SqlDataAdapter dataAdaptor = new SqlDataAdapter();
dataAdaptor.SelectCommand = new SqlCommand(SqlQuery, DBInfo.DBConnection);
dataAdaptor.SelectCommand.CommandType = CommandType.Text;
dataAdaptor.Fill(dataset, "table");
CloseDBConnection(DBInfo);
return(dataset.Tables["table"]);
}
catch (Exception e)
{
CloseDBConnection(DBInfo);
return(null);
//fail statement
}
}
public void WhenIDeleteStudy()
{
SQLInfo Info = DatabaseSteps.StoreSQLInfo();
if (!BP_StudyUtilities.DeleteStudy(Info))
{
ScenarioContext.Current["ActResult"] = "Failed";
}
}
public static object Extract(SQLiteDataReader reader, SQLInfo.ColDef col)
{
object data = null;
try
{
data = reader[col.ColName];
}
catch (Exception e)
{
Logger.ReportException("Error reading data for col: " + col.ColName + " type is: " + col.ColType.Name, e);
return null;
}
if (data is DBNull || data == null) return null;
var typ = data.GetType();
//Logger.ReportVerbose("Extracting: " + col.ColName + " Defined Type: "+col.ColType+"/"+col.NullableType + " Actual Type: "+typ.Name+" Value: "+data);
if (typ == typeof(string))
{
if (col.ColType == typeof(MediaType))
return Enum.Parse(typeof(MediaType), (string)data);
else
if (col.ColType == typeof(MediaBrowser.Library.Network.DownloadPolicy))
return Enum.Parse(typeof(MediaBrowser.Library.Network.DownloadPolicy), (string)data);
else
if (col.ColType == typeof(VideoFormat))
return Enum.Parse(typeof(VideoFormat), (string)data);
else
if (col.ColType == typeof(Guid))
{
return new Guid((string)data);
}
else
return data;
} else
if (typ == typeof(Int64)) {
if (col.ColType == typeof(DateTime))
return new DateTime((Int64)data);
else if (col.InternalType == typeof(int) || col.ColType == typeof(int))
return Convert.ToInt32(data);
else
return data;
} else
if (typ == typeof(Double)) {
if (col.ColType == typeof(Single) || col.InternalType == typeof(Single))
return Convert.ToSingle(data);
else
return data;
} else
{
var ms = new MemoryStream((byte[])data);
return Serializer.Deserialize<object>(ms);
//return JsonSerializer.DeserializeFromString((string)reader[col.ColName], col.ColType);
}
}
public void GivenISaveTheXMLDataIntoAnXMLFileWithNameAs_(string XMLFileName)
{
SQLInfo Info = StoreSQLInfo();
DataTable data = Info.QueryResultTable;
string FilePath = @"C:\TestXMLFiles\";
string content = data.Rows[0].ItemArray[0].ToString();
XDocument xdoc = XDocument.Parse(content);
xdoc.Save(FilePath + XMLFileName);
}
public void GivenIHaveConnectedToDatabase_()
{
SQLInfo Info = StoreSQLInfo();
if (!DatabaseUtilities.ConnectToDB(Info))
{
ScenarioContext.Current["Comments"] = ScenarioContext.Current["Comments"] + "Unable to database. \n";
ScenarioContext.Current["ActResult"] = "Failed";
}
}
public static bool CloseDBConnection(SQLInfo DBInfo)
{
//SQLInfo DBInfo = new SQLInfo();
if (IsDBConnectionAlive(DBInfo))
{
DBInfo.DBConnection.Close();
return(!IsDBConnectionAlive(DBInfo));
}
return(false);
}
/*
* public static string DBServerName { get; set; }
* public static string DBName { get; set; }
* public static string UserID { get; set; }
* public static string Password { get; set; }
*
*/
//public DBInfo;
public static void SQLData(string DBServer, string DBName, string UserName, string Password)
{
SQLInfo DBInfo = new SQLInfo();
DBInfo.DataBaseServer = DBServer;
DBInfo.DataBaseName = DBName;
DBInfo.DBUserName = UserName;
DBInfo.DBUserPassword = Password;
DBInfo.SQLConnectionString = @"Data Source=" + DBInfo.DataBaseServer + ";Initial Catalog=" + DBInfo.DataBaseName + ";User ID=" + DBInfo.DBUserName + ";Password="******"";
}
public void ThenICloseTheDatabaseConnection_()
{
SQLInfo Info = StoreSQLInfo();
if (!DatabaseUtilities.CloseDBConnection(Info))
{
ScenarioContext.Current["Comments"] = ScenarioContext.Current["Comments"] + "Unable to close database. \n";
ScenarioContext.Current["ActResult"] = "Failed";
}
}
public bool VerifyData(SQLInfo sqlInfo, String command, DataTable dataToVerify)
{
try
{
}
catch (Exception e)
{
new DebugLogGenerator().WriteException(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, e);
}
return(true);
}
public static SQLInfo StoreSQLInfo()
{
SQLInfo DBInfo = new SQLInfo();
DBInfo.DataBaseServer = ConfigurationHelper.DatabaseServerName;
DBInfo.DataBaseName = ConfigurationHelper.DatabaseName;
DBInfo.DBUserName = ConfigurationHelper.DatabaseUserName;
DBInfo.DBUserPassword = ConfigurationHelper.DatabaseUserPwd;
DBInfo.SQLConnectionString = @"Data Source=" + DBInfo.DataBaseServer + ";Initial Catalog=" + DBInfo.DataBaseName + ";User ID=" + DBInfo.DBUserName + ";Password="******"";
return(DBInfo);
//DatabaseUtilities.SQLData(ConfigurationHelper.DatabaseServerName, ConfigurationHelper.DatabaseName, ConfigurationHelper.DatabaseUserName, ConfigurationHelper.DatabaseUserPwd);
}
public static bool IsDBConnectionAlive(SQLInfo DBInfo)
{
//SQLInfo DBInfo = new SQLInfo();
SqlConnection ConnObj = DBInfo.DBConnection;
if (ConnObj == null || (ConnObj != null && (ConnObj.State == ConnectionState.Closed || ConnObj.State == ConnectionState.Broken)))
{
//fail statement
return(false);
}
return(true);
}
public static bool DeleteStudy(SQLInfo dBInfo = null)
{
try
{
BP_DataManagerPortalDropdownUtilities.SelectPortalDropdownItem("Study Administration");
BP_StudyAdministationUtilities.InvokeLinkInStudyAdministrationPage("Study Management");
BP_ActionPaletteUtilities.InvokeActionPaletteLink("Manage Versions", "StudyResetAction");
int i = 0;
bool isStudyAvailable = true;
while (i < 5 || isStudyAvailable)
{
++i;
String studyStatus = GetStudyStatus();
if (studyStatus.Equals("Uploaded", StringComparison.InvariantCultureIgnoreCase))
{
DeleteUploadedStudy();
}
else if (studyStatus.Equals("Staged", StringComparison.InvariantCultureIgnoreCase))
{
DeleteStagedStudy();
}
else if (studyStatus.Contains("Published"))
{
DeletePublishedStudy();
}
else if (studyStatus.Equals("Exception", StringComparison.InvariantCultureIgnoreCase))
{
DeleteExceptionStudy();
}
isStudyAvailable = BP_StudyVersionManagementUtilities.IsStudyAvailable();
}
string sqlQuery = @"DBCC CHECKIDENT ('LabReferenceRange', RESEED, 999)";
if (!DatabaseUtilities.ExecuteUpdateQuery(sqlQuery, dBInfo))
{
return(false);
}
return(!isStudyAvailable);
}
catch (Exception e)
{
new DebugLogGenerator().WriteException(MethodBase.GetCurrentMethod().DeclaringType.Name, MethodBase.GetCurrentMethod().Name, e);
}
return(false);
}
public void GivenIHaveConnectedToDBAndExecuteTheQuery_(string QueryToExecute)
{
SQLInfo Info = StoreSQLInfo();
if (DatabaseUtilities.ConnectToDB(Info))
{
Info.QueryResultTable = DatabaseUtilities.GetRecordsFromDB(QueryToExecute, Info);
if (Info.QueryResultTable == null)
{
ScenarioContext.Current["Comments"] = ScenarioContext.Current["Comments"] + "Unable to execute query " + QueryToExecute + " on database. \n";
ScenarioContext.Current["ActResult"] = "Failed";
}
}
else
{
ScenarioContext.Current["Comments"] = ScenarioContext.Current["Comments"] + "Unable to database. \n";
ScenarioContext.Current["ActResult"] = "Failed";
}
}
public static bool ConnectToDB(SQLInfo DBInfo)
//public static SqlConnection ConnectToDB(string DBServerName, string DBName, string UserID, string Password)
{
try
{
string ConString;
ConString = DBInfo.SQLConnectionString;
//ConString = @"Data Source=ABC;Initial Catalog=Demo;User ID=sa;Password=Datalabs123";
//ConString = @"Data Source=" + DBServerName + ";Initial Catalog=" + DBName + ";User ID=" + UserID + ";Password="******"";
SqlConnection DbCon;
DbCon = new SqlConnection(ConString);
DbCon.Open();
DBInfo.DBConnection = DbCon;
return(IsDBConnectionAlive(DBInfo));
}
catch (Exception e)
{
//fail statement
return(false);
}
}
public void ThenISaveTheFileWithNamePrefixByExecutingTheQuery_(string XMLFileName)
{
string QueryToExecute = "select XmlMessage from OdmSubscriberDetail where StudyEventKey = 8 and StatusKey = 311";
SQLInfo Info = StoreSQLInfo();
try
{
if (DatabaseUtilities.ConnectToDB(Info))
{
Thread.Sleep(5000);
Info.QueryResultTable = DatabaseUtilities.GetRecordsFromDB(QueryToExecute, Info);
if (Info.QueryResultTable == null)
{
ScenarioContext.Current["Comments"] = ScenarioContext.Current["Comments"] + "Unable to execute query " + QueryToExecute + " on database. \n";
ScenarioContext.Current["ActResult"] = "Failed";
}
else
{
DataTable data = Info.QueryResultTable;
string FilePath = FeatureContext.Current["ReportsFolderPath"].ToString();
for (int i = 0; i < data.Rows.Count - 1; i++)
{
string content = data.Rows[i].ItemArray[0].ToString();
XDocument xdoc = XDocument.Parse(content);
xdoc.Save(FilePath + XMLFileName + i + ".xml");
}
}
DatabaseUtilities.CloseDBConnection(Info);
}
else
{
ScenarioContext.Current["Comments"] = ScenarioContext.Current["Comments"] + "Unable to database. \n";
ScenarioContext.Current["ActResult"] = "Failed";
}
}
catch (Exception e)
{
string abc = Info.DataBaseName;
}
}
public void WhenIExecuteTheQuery_(string QueryToExecute)
{
SQLInfo Info = StoreSQLInfo();
Info.QueryResultTable = DatabaseUtilities.GetRecordsFromDB(QueryToExecute, Info);
if (Info.QueryResultTable == null)
{
ScenarioContext.Current["Comments"] = ScenarioContext.Current["Comments"] + "Unable to execute query " + QueryToExecute + " on database. \n";
ScenarioContext.Current["ActResult"] = "Failed";
}
DataTable dtClone = Info.QueryResultTable.Clone();
foreach (DataColumn dc in dtClone.Columns)
{
dc.DataType = typeof(string);
}
foreach (DataRow dr in Info.QueryResultTable.Rows)
{
dtClone.ImportRow(dr);
}
ScenarioContext.Current["QueryResults"] = dtClone;
}
public void ThenIVerifyTheInIsAgainstDBByExecuting(string FieldName, string TagName, string DataValue, string Query)
{
SQLInfo Info = DataBase.DatabaseSteps.StoreSQLInfo();
try
{
if (DatabaseUtilities.ConnectToDB(Info))
{
Info.QueryResultTable = DatabaseUtilities.GetRecordsFromDB(Query, Info);
if (Info.QueryResultTable == null)
{
ScenarioContext.Current["Comments"] = ScenarioContext.Current["Comments"] + "Unable to execute query " + Query + " on database. \n";
ScenarioContext.Current["ActResult"] = "Failed";
}
else
{
DataTable data = Info.QueryResultTable;
string FilePath = FeatureContext.Current["ReportsFolderPath"].ToString();
string content = data.Rows[0].ItemArray[0].ToString();
XmlDocument xDoc = (XmlDocument)ScenarioContext.Current["LoadedXML"];
if (!XMLActionUtilities.VerifyFieldDataInXMLTag(xDoc, TagName, FieldName, DataValue))
{
ScenarioContext.Current["Comments"] = ScenarioContext.Current["Comments"] + FieldName + " value in " + TagName + " is displayed as " + content + " instead of " + DataValue + ". \n";
ScenarioContext.Current["ActResult"] = "Failed";
}
}
DatabaseUtilities.CloseDBConnection(Info);
}
else
{
ScenarioContext.Current["Comments"] = ScenarioContext.Current["Comments"] + "Unable to connect to database. \n";
ScenarioContext.Current["ActResult"] = "Failed";
}
}
catch (Exception e)
{
string abc = Info.DataBaseName;
}
}
public void GivenISaveTheEventXMLDataInFileByExecutingTheQuery(string XMLFileName, string QueryToExecute)
{
SQLInfo Info = StoreSQLInfo();
try
{
if (DatabaseUtilities.ConnectToDB(Info))
{
Thread.Sleep(5000);
Info.QueryResultTable = DatabaseUtilities.GetRecordsFromDB(QueryToExecute, Info);
if (Info.QueryResultTable == null)
{
ScenarioContext.Current["Comments"] = ScenarioContext.Current["Comments"] + "Unable to execute query " + QueryToExecute + " on database. \n";
ScenarioContext.Current["ActResult"] = "Failed";
}
else
{
DataTable data = Info.QueryResultTable;
string FilePath = FeatureContext.Current["ReportsFolderPath"].ToString();
string content = data.Rows[0].ItemArray[0].ToString();
XDocument xdoc = XDocument.Parse(content);
xdoc.Save(FilePath + XMLFileName);
}
DatabaseUtilities.CloseDBConnection(Info);
}
else
{
ScenarioContext.Current["Comments"] = ScenarioContext.Current["Comments"] + "Unable to database. \n";
ScenarioContext.Current["ActResult"] = "Failed";
}
}
catch (Exception e)
{
string abc = Info.DataBaseName;
}
}
public static object Encode(SQLInfo.ColDef col, object data)
{
if (data == null) return null;
//Logger.ReportVerbose("Encoding " + col.ColName + " as " + col.ColType.Name.ToLower());
switch (col.ColType.Name.ToLower())
{
case "guid":
return data.ToString();
case "string":
return data;
case "datetime":
return ((DateTime)data).Ticks;
case "mediatype":
return ((MediaType)data).ToString();
case "videoformat":
return ((VideoFormat)data).ToString();
case "downloadpolicy":
return ((MediaBrowser.Library.Network.DownloadPolicy)data).ToString();
case "int":
case "int16":
case "int32":
case "int64":
case "long":
case "double":
case "nullable`1":
return data;
default:
var ms = new MemoryStream();
Serializer.Serialize<object>(ms,data);
ms.Seek(0,0);
return ms.ReadAllBytes();
}
}
protected BaseItem GetItem(SQLiteDataReader reader, string itemType)
{
BaseItem item = null;
try
{
item = Serializer.Instantiate<BaseItem>(itemType);
}
catch (Exception e)
{
Logger.ReportException("Error trying to create instance of type: " + itemType, e);
return null;
}
if (item != null)
{
SQLInfo itemSQL;
lock (ItemSQL)
{
if (!ItemSQL.TryGetValue(item.GetType(), out itemSQL))
{
itemSQL = new SQLInfo(item);
ItemSQL.Add(item.GetType(), itemSQL);
//make sure our schema matches
itemSQL.FixUpSchema(connection);
}
}
foreach (var col in itemSQL.AtomicColumns)
{
var data = SQLizer.Extract(reader, col);
if (data != null)
if (col.MemberType == MemberTypes.Property)
col.PropertyInfo.SetValue(item, data, null);
else
col.FieldInfo.SetValue(item, data);
}
// and our list columns
//this is an optimization - we go get all the list values for this item in one statement
var listCmd = connection.CreateCommand();
listCmd.CommandText = "select property, value from list_items where guid = @guid and property != 'ActorName' order by property, sort_order";
listCmd.AddParam("@guid", item.Id);
string currentProperty = "";
System.Collections.IList list = null;
SQLInfo.ColDef column = new SQLInfo.ColDef();
using (var listReader = listCmd.ExecuteReader())
{
while (listReader.Read())
{
string property = listReader.GetString(0);
if (property != currentProperty)
{
//new column...
if (list != null)
{
//fill in the last one
if (column.MemberType == MemberTypes.Property)
column.PropertyInfo.SetValue(item, list, null);
else
column.FieldInfo.SetValue(item, list);
}
currentProperty = property;
column = itemSQL.Columns.Find(c => c.ColName == property);
list = (System.Collections.IList)column.ColType.GetConstructor(new Type[] { }).Invoke(null);
//Logger.ReportVerbose("Added list item '" + listReader[0] + "' to " + col.ColName);
}
try
{
list.Add(SQLizer.Extract(listReader, new SQLInfo.ColDef() { ColName = "value", ColType = column.InternalType }));
}
catch (Exception e)
{
Logger.ReportException("Error adding item to list " + column.ColName + " on item " + item.Name, e);
}
}
if (list != null)
{
//fill in the last one
if (column.MemberType == MemberTypes.Property)
column.PropertyInfo.SetValue(item, list, null);
else
column.FieldInfo.SetValue(item, list);
}
}
}
else
{
Logger.ReportWarning("Ignoring invalid item " + itemType + ". Would not instantiate in current environment.");
}
return item;
}
