public List <T> ExecuteQuery <T>(string sql) where T : class
{
UserEngine.SessionTimeOut();
IEnumerable <T> result = Standard.ExecuteQuery <T>(sql);
return(result.ToList <T>());
}
private void btnFind_Click(object sender, RoutedEventArgs e)
{
if (tbSearchString.Text == "")
{
btnRefresh_Click(sender, e);
return;
}
else
{
string SqlCommand;
if (TableType.Name[TableType.Name.Length - 1] == 'y')
{
SqlCommand = "select * from " + TableType.Name.Substring(0, TableType.Name.Length - 1) + "ies where ";
}
else
{
SqlCommand = "select * from " + TableType.Name + "s where ";
}
string[] SearchValues = tbSearchString.Text.Split();
foreach (string Str in SearchValues)
{
try
{
int I = Convert.ToInt32(Str);
foreach (var F in Fields)
{
if (F.Value.PropertyType == typeof(int))
{
SqlCommand += F.Value.Name + " = " + I.ToString() + " or ";
}
}
}
catch { }
try
{
double I = Convert.ToDouble(Str);
foreach (var F in Fields)
{
if (F.Value.PropertyType == typeof(double))
{
SqlCommand += F.Value.Name + " = " + I.ToString() + " or ";
}
}
}
catch { }
foreach (var F in Fields)
{
if (F.Value.PropertyType == typeof(string))
{
SqlCommand += F.Value.Name + " like '%" + Str + "%' or ";
}
}
}
SqlCommand = SqlCommand.Substring(0, SqlCommand.Length - 4);
DatabaseContext = new System.Data.Linq.DataContext(ConnectionString);
DataGrid.DataContext = DatabaseContext.ExecuteQuery(TableType, SqlCommand);
}
}
public static IEnumerable <T> GetMatchingObjects <T>(System.Data.Linq.DataContext dataContext, IEnumerable <WhereCondition> elements, string tableName, string[] columnNames)
#if Mono
where T : class, new()
#endif
{
string sql = string.Format("SELECT {0} FROM {1} WHERE {2}",
string.Join(",", columnNames),
tableName,
elements.ToWhereString()
);
var parameterValues = elements.AsQueryable().SelectMany(elem => elem.Values).ToArray();
return(dataContext.ExecuteQuery <T>(sql, parameterValues));
}
public static IEnumerable <T> GetMatchingObjects <T>(System.Data.Linq.DataContext dataContext, IEnumerable <WhereCondition> elements, string tableName, bool distinct, string[] columnNames, int startIndex, int maxCount, string sort)
#if Mono
where T : class, new()
#endif
{
string tempName = Strings.NewRandomString(10);
string columnNamesStr = string.Join(",", columnNames);
string sql = string.Format(""
+ " WITH t{0} AS "
+ " ("
+ " SELECT TOP {1} *,"
+ " ROW_NUMBER() OVER (ORDER BY {2}) AS RowNumber{3} "
+ " FROM "
+ " ("
+ " SELECT {4} {5}"
+ " FROM {6}"
+ " WHERE {7}"
+ " ) AS s{8}"
+ " ) "
+ " SELECT {9} "
+ " FROM t{10} "
+ " WHERE RowNumber{11} BETWEEN {12} AND {13}",
tempName,
startIndex + maxCount,
sort,
tempName,
distinct ? "DISTINCT" : "",
columnNamesStr,
tableName,
elements.ToWhereString(),
tempName,
columnNamesStr,
tempName,
tempName,
startIndex + 1,
startIndex + maxCount
);
var parameterValues = elements.AsQueryable().SelectMany(elem => elem.Values).ToArray();
return(dataContext.ExecuteQuery <T>(sql, parameterValues));
}
/// <summary>
/// Execute an SQL query directly to the database.
/// </summary>
/// <typeparam name="TEntity">The type to examine.</typeparam>
/// <param name="dataContext">The data context to the database.</param>
/// <param name="sqlQuery">The sql command to execute to the database.</param>
/// <param name="values">The parameter values for the command, can be null.</param>
/// <returns>The enumerable collection type.</returns>
/// <exception cref="System.ArgumentNullException"></exception>
public static IEnumerable <TEntity> ExecuteQuery <TEntity>(
System.Data.Linq.DataContext dataContext, string sqlQuery, params object[] values)
where TEntity : class, new()
{
// Check to see if the critical parameters
// have been set, throw exception on each.
if (sqlQuery == null)
{
throw new ArgumentNullException("sqlQuery");
}
// Execute the query.
IEnumerable <TEntity> query =
dataContext.ExecuteQuery <TEntity>(sqlQuery, values);
// Return the enumerable result
// of the specified type.
return(query);
}
private void MWL_Server_QueryReceived(object sender, QueryReceivedArgs e)
{
#region "Tracing all calls to Server"
Log.Trace("MWL_Server_QueryReceived with QueryRoot: " + e.Root.ToString());
#endregion
#region "Abort if not an MWL query"
if (e.Root != QueryRoot.ModalityWorklist)
{
e.Errors.Add(Keyword.ErrorComment, "Invalid Query Root : None");
e.Status = StatusCodes.InvalidQueryRoot;
return;
}
#endregion
DicomDataSetCollection NullSequence = new DicomDataSetCollection();
DicomDataSet requestFromAssociation;
DicomDataSet requestedProcedureStep = null;
IEnumerable <ExamsScheduledTable> results = new List <ExamsScheduledTable>();
int status = 0;
System.Data.SqlClient.SqlConnection db = null;
try
{
#region "Open PowerPath db"
try
{
db = new System.Data.SqlClient.SqlConnection(powerpathloginconfig.ConnectionString);
db.Open();
}
catch (SqlException se)
{
Log.Error("Sql Error opening Database -- " + se.Message);
status = StatusCodes.GeneralError; // Error, unable to process!
return;
}
#endregion
using (var DBContext = new System.Data.Linq.DataContext(db))
{
// Get the Imcoming Query Request
requestFromAssociation = e.RequestAssociation.Request;
#region "Build SQL query based on SCU request"
// the "where 1=1" makes the syntax of adding further conditions simpler, as all are then " AND x=y"
//TODO: refactor hard-coded table into Powerpath configuration item
string tableWithExamsScheduled = @"vwsu_scheduled_speciman_xrays";
if (this.dicomserverconfig.ExamScheduledTable.Length > 4)
{
tableWithExamsScheduled = dicomserverconfig.ExamScheduledTable;
}
string sql = "SELECT * from " + tableWithExamsScheduled + " Where 1=1";
SqlQueryUtils.AddCondition(ref sql, requestFromAssociation[Keyword.PatientID], "PatientID");
SqlQueryUtils.AddNameCondition(ref sql, requestFromAssociation[Keyword.PatientName], "surname", "Forename");
if (requestFromAssociation[Keyword.ScheduledProcedureStepSequence].ExistsWithValue)
{
DicomDataSetCollection requestedProcedureSteps;
//TODO: in what circumstances would there be more than one requested step?
requestedProcedureSteps = (DicomDataSetCollection)requestFromAssociation[Keyword.ScheduledProcedureStepSequence].Value;
requestedProcedureStep = requestedProcedureSteps[0];
// Required Matching keys
SqlQueryUtils.AddCondition(ref sql, requestedProcedureStep[Keyword.ScheduledStationAETitle], "ScheduledAET");
// always blank from PowerPath //SqlQueryUtils.AddCondition(ref sql, rq1[Keyword.PerformingPhysicianName], "PerformingPhysician");
SqlQueryUtils.AddCondition(ref sql, requestedProcedureStep[Keyword.Modality], "Modality");
// AddDateCondition looks for a leading or trailing -hyphen-
// if only date is specified, then using standard matching
//but if both are specified, then MWL defines a combined match
//note: for PowerPath, there is no Scheduled Time that makes any sense
if (requestedProcedureStep[Keyword.ScheduledProcedureStepStartDate].ExistsWithValue && requestedProcedureStep[Keyword.ScheduledProcedureStepStartTime].ExistsWithValue) // if both Date and Time are specified
{
SqlQueryUtils.AddDateTimeCondition(ref sql, requestedProcedureStep[Keyword.ScheduledProcedureStepStartDate], requestedProcedureStep[Keyword.ScheduledProcedureStepStartTime], "ExamDateAndTime");
}
else if (requestedProcedureStep[Keyword.ScheduledProcedureStepStartDate].ExistsWithValue) // if Date is specified
{
SqlQueryUtils.AddDateCondition(ref sql, requestedProcedureStep[Keyword.ScheduledProcedureStepStartDate], "ExamDateAndTime");
}
// Optional (but commonly used) matching keys.
SqlQueryUtils.AddCondition(ref sql, requestedProcedureStep[Keyword.ScheduledProcedureStepLocation], "ExamRoom");
SqlQueryUtils.AddCondition(ref sql, requestedProcedureStep[Keyword.ScheduledProcedureStepDescription], "ExamDescription");
}
sql = sql + " Order by Surname, Forename";
Log.Debug("SQL query statement:" + Environment.NewLine + sql);
#endregion
#region "Execute Sql Query"
try
{
results = DBContext.ExecuteQuery <ExamsScheduledTable>(sql);
}
catch (Exception ex)
{
Log.Error("Error executing SQL: '" + sql + "'. " + ex.Message);
status = StatusCodes.GeneralError;
return;
}
#endregion
#region "Parse results"
DicomDataSet rr1;
DicomDataSet rr;
DicomDataSetCollection rrs;
foreach (ExamsScheduledTable result in results)
{
rr1 = new DicomDataSet();
rr = new DicomDataSet();
rrs = new DicomDataSetCollection {
rr1
};
if (requestedProcedureStep != null)
{
rr.Add(Keyword.ScheduledProcedureStepSequence, rrs);
}
// add results to "main" dataset
AddResultItem(rr, requestFromAssociation, Keyword.AccessionNumber, result.AccessionNumber); // T2
AddResultItem(rr, requestFromAssociation, Keyword.InstitutionName, result.HospitalName);
AddResultItem(rr, requestFromAssociation, Keyword.ReferringPhysicianName, result.ReferringPhysician); // T2
AddResultItem(rr, requestFromAssociation, Keyword.PatientName, result.Surname + "^" + result.Forename + "^^" + result.Title); //T1
AddResultItem(rr, requestFromAssociation, Keyword.PatientID, result.PatientID); // T1
AddResultItem(rr, requestFromAssociation, Keyword.PatientBirthDate, result.DateOfBirth); // T2
AddResultItem(rr, requestFromAssociation, Keyword.PatientSex, result.Sex); //T2
AddResultItem(rr, requestFromAssociation, Keyword.StudyInstanceUID, result.StudyUID); // T1
AddResultItem(rr, requestFromAssociation, Keyword.RequestingPhysician, result.ReferringPhysician); //T2
AddResultItem(rr, requestFromAssociation, Keyword.RequestedProcedureDescription, result.ExamDescription); //T1C
AddResultItem(rr, requestFromAssociation, Keyword.RequestedProcedureID, result.ProcedureID); // T1
// Scheduled Procedure Step sequence T1
// add results to procedure step dataset
// Return if requested
if (requestedProcedureStep != null)
{
AddResultItem(rr1, requestedProcedureStep, Keyword.ScheduledStationAETitle, result.ScheduledAET); // T1
AddResultItem(rr1, requestedProcedureStep, Keyword.ScheduledProcedureStepStartDate, result.ExamDateAndTime); //T1
AddResultItem(rr1, requestedProcedureStep, Keyword.ScheduledProcedureStepStartTime, result.ExamDateAndTime); //T1
AddResultItem(rr1, requestedProcedureStep, Keyword.Modality, result.Modality); // T1
AddResultItem(rr1, requestedProcedureStep, Keyword.ScheduledPerformingPhysicianName, result.PerformingPhysician); //T2
AddResultItem(rr1, requestedProcedureStep, Keyword.ScheduledProcedureStepDescription, result.ExamDescription); // T1C
AddResultItem(rr1, requestedProcedureStep, Keyword.ScheduledProcedureStepID, result.ProcedureStepID); // T1
AddResultItem(rr1, requestedProcedureStep, Keyword.ScheduledStationName, result.ExamRoom); //T2
AddResultItem(rr1, requestedProcedureStep, Keyword.ScheduledProcedureStepLocation, result.ExamRoom); //T2
}
// Put blanks in for unsupported fields which are type 2 (i.e. must have a value even if NULL)
// In a real server, you may wish to support some or all of these, but they are not commonly supported
AddResultItem(rr, requestFromAssociation, Keyword.ReferencedStudySequence, NullSequence);
AddResultItem(rr, requestFromAssociation, Keyword.Priority, "");
AddResultItem(rr, requestFromAssociation, Keyword.PatientTransportArrangements, "");
AddResultItem(rr, requestFromAssociation, Keyword.AdmissionID, "");
AddResultItem(rr, requestFromAssociation, Keyword.CurrentPatientLocation, "");
AddResultItem(rr, requestFromAssociation, Keyword.ReferencedPatientSequence, NullSequence);
AddResultItem(rr, requestFromAssociation, Keyword.PatientWeight, "");
AddResultItem(rr, requestFromAssociation, Keyword.ConfidentialityConstraintOnPatientDataDescription, "");
Log.Debug("Sending response ..");
// Send Reponse Back
e.SendResponse(rr, StatusCodes.Pending);
Log.Info("PowerPath Database Disconnected Normally");
}
#endregion
}
}
catch (Exception ex)
{
Log.Error("Error processing MWL request -- " + ex.Message);
status = StatusCodes.GeneralError; // Error, unable to process!
}
finally
{
if (null != db)
{
try
{
db.Close();
}
catch { /* ignore an SqlException here */ }
db.Dispose();
}
e.Status = status;
}
}
public static DateTime GetDateServer(System.Data.Linq.DataContext db)
{
return(db.ExecuteQuery <DateTime>("select getdate()").FirstOrDefault());
}
public void RemakeWindow(Dictionary <string, PropertyInfo> Fields)
{
int i = 0;
int MaxLabelWidth = 0;
ValueItems = new Dictionary <string, Control>(Fields.Count);
foreach (KeyValuePair <string, PropertyInfo> Pair in Fields)
{
if (Pair.Key.Length > MaxLabelWidth)
{
MaxLabelWidth = Pair.Key.Length;
}
}
MaxLabelWidth *= 8;
wrapPanelMain.Width = MarginLeft + MaxLabelWidth + MarginRight + MarginLeft + ValueItemWidth + MarginRight;
wrapPanelMain.Height = (MarginTop + MarginBottom + ValueItemHeight) * (Fields.Count + 1);
windowMain.Width = wrapPanelMain.Width + 22;
windowMain.Height = wrapPanelMain.Height + 39;
foreach (KeyValuePair <string, PropertyInfo> Pair in Fields)
{
Label L = new Label();
L.Content = Pair.Key;
L.Width = MaxLabelWidth;
L.Height = ValueItemHeight;
L.Margin = new Thickness(MarginLeft, MarginTop, MarginRight, MarginBottom);
L.HorizontalAlignment = System.Windows.HorizontalAlignment.Left;
L.VerticalAlignment = System.Windows.VerticalAlignment.Top;
L.Name = "lbl" + Pair.Value.Name;
Control C = CreateValueItem(Pair.Value.GetType());
C.Width = ValueItemWidth;
C.Height = ValueItemHeight;
C.Margin = new Thickness(MarginLeft, MarginTop, MarginRight, MarginBottom);
C.HorizontalAlignment = System.Windows.HorizontalAlignment.Stretch;
C.VerticalAlignment = System.Windows.VerticalAlignment.Top;
C.Name = "item" + Pair.Value.Name;
if (Creating && Pair.Value.Name == "ID")
{
(C as TextBox).Text = ((int)DatabaseContext.ExecuteQuery <int>("select max(ID) from " + TableType.Name + "s").First() + 1).ToString();
}
ValueItems.Add(C.Name, C);
wrapPanelMain.Children.Add(L);
wrapPanelMain.Children.Add(C);
}
ButtonOk = new Button();
ButtonOk.Content = "ОК";
ButtonOk.Width = MaxLabelWidth;
ButtonOk.Height = ValueItemHeight;
ButtonOk.Margin = new Thickness(MarginLeft, MarginTop, MarginRight, MarginBottom);
ButtonOk.HorizontalAlignment = System.Windows.HorizontalAlignment.Left;
ButtonOk.VerticalAlignment = System.Windows.VerticalAlignment.Top;
ButtonOk.Name = "btnOk";
ButtonOk.Click += new RoutedEventHandler(ButtonOk_Click);
ButtonCancel = new Button();
ButtonCancel.Content = "Отмена";
ButtonCancel.Width = ValueItemWidth;
ButtonCancel.Height = ValueItemHeight;
ButtonCancel.Margin = new Thickness(MarginLeft, MarginTop, MarginRight, MarginBottom);
ButtonCancel.HorizontalAlignment = System.Windows.HorizontalAlignment.Left;
ButtonCancel.VerticalAlignment = System.Windows.VerticalAlignment.Top;
ButtonCancel.Name = "btnCancel";
ButtonCancel.Click += new RoutedEventHandler(ButtonCancel_Click);
wrapPanelMain.Children.Add(ButtonOk);
wrapPanelMain.Children.Add(ButtonCancel);
}