public static DataTable ExecuteSQLSelect(SqlCommand sqlComm, string connectionString)
{
SqlConnection sqlConn = new SqlConnection(connectionString);
try
{
sqlConn.Open();
}
catch (SqlException sqlE)
{
//The source argument must be less than 250 characters or an exception will be thrown when writing to the CancerGov event log
CancerGovError.LogError("DatabaseFunctions.ExecuteSQLSelect(SqlCommand, string)", "ConnectionString: " + connectionString + "; CommandText: " + sqlComm.CommandText.Substring(0, 100), ErrorType.DbUnavailable, sqlE);
}
sqlComm.Connection = sqlConn;
DataTable dbTable = new DataTable();
SqlDataAdapter dbAdapter = new SqlDataAdapter(sqlComm);
try
{
dbAdapter.Fill(dbTable);
}
catch (SqlException sqlE)
{
//The source argument must be less than 250 characters or an exception will be thrown when writing to the CancerGov event log
CancerGovError.LogError("DatabaseFunctions.ExecuteSQLSelect(SqlCommand, string)", "ConnectionString: " + connectionString + "; CommandText: " + sqlComm.CommandText.Substring(0, 100), ErrorType.DbUnavailable, sqlE);
}
sqlConn.Close();
sqlConn.Dispose();
dbAdapter.Dispose();
return(dbTable);
}
public static DataTable AZListLettersWithData(string language)
{
// create our null object
DataTable dt = null;
SqlParameter outputParam = new SqlParameter("@totalresult", SqlDbType.Int);
outputParam.Direction = ParameterDirection.Output;
// create our parameter array
SqlParameter[] parms = { new SqlParameter("@Language", SqlDbType.VarChar) };
// Set the values on the parameters
parms[0].Value = language;
try
{
// Query the database and get the results
dt = SqlHelper.ExecuteDatatable(
ConfigurationManager.ConnectionStrings["CDRDbConnectionString"].ConnectionString,
CommandType.StoredProcedure,
"usp_getGlossaryFirstLetter",
parms);
}
catch (Exception ex)
{
CancerGovError.LogError("TermDictionaryQuery", 2, ex);
throw ex;
}
// return the DataTable
return(dt);
}
/// <summary>
/// Method used to query database for English language results. This will return the
/// data in XML format. There is no way to create an additional WebGet so that we
/// can return the same data in JSON format.
/// </summary>
/// <param name="language">The language needed to do the lookup</param>
/// <param name="criteria">The partial text used to query the database</param>
/// <param name="maxRows">The maximum number of rows that the database will return. a value of zero will return the entire set</param>
/// <param name="contains">Indicator on whether the text is to be search from the beginning of the text or anywhere in the string</param>
/// <returns>Returns the search results</returns>
private AutoSuggestSearchServiceCollection Search(string language, string criteria, int maxRows, bool contains)
{
// create the collection variable
AutoSuggestSearchServiceCollection sc = new AutoSuggestSearchServiceCollection();
try
{
// language passed to an enum
DisplayLanguage displayLanguage =
(DisplayLanguage)Enum.Parse(typeof(DisplayLanguage), language);
// Call the database query
AutoSuggestSearchCollection dc =
AutoSuggestSearchManager.Search(language, criteria, maxRows, contains);
// Use Linq to extract the data from the business layer and create
// the service data objects
// TermID is 0 always , that value is not part of the result received from the call to
// Stroed procedure.But can be used in the future for other purposes.
var collection = dc.ConvertAll(entry => new AutoSuggestSearchServiceItem(
entry.TermID,
entry.TermName,
string.Empty
));
sc.AddRange(collection);
}
catch (Exception ex)
{
// Log the error that occured
CancerGovError.LogError("AutoSuggestSearch", 2, ex);
}
return(sc);
}
public static int ExecuteSQLNonQuery(string commandText, string connectionString)
{
int recsAffected = 0;
SqlConnection sqlConn = new SqlConnection(connectionString);
try
{
sqlConn.Open();
}
catch (SqlException sqlE)
{
//The source argument must be less than 250 characters or an exception will be thrown when writing to the CancerGov event log
CancerGovError.LogError("DatabaseFunctions.ExecuteSQLNonQuery(string, string)", "ConnectionString: " + connectionString + "; CommandText: " + commandText.Substring(0, 100), ErrorType.DbUnavailable, sqlE);
}
SqlCommand sqlCommand = new SqlCommand(commandText, sqlConn);
try
{
recsAffected = sqlCommand.ExecuteNonQuery();
}
catch (SqlException sqlE)
{
//The source argument must be less than 250 characters or an exception will be thrown when writing to the CancerGov event log
CancerGovError.LogError("DatabaseFunctions.ExecuteSQLNonQuery(string, string)", "ConnectionString: " + connectionString + "; CommandText: " + commandText.Substring(0, 100), ErrorType.DbUnavailable, sqlE);
recsAffected = -1;
}
sqlConn.Close();
sqlConn.Dispose();
sqlCommand.Dispose();
return(recsAffected);
}
public static string ExecuteSQLScalar(string commandText, string connectionString)
{
string result = "";
SqlConnection sqlConn = new SqlConnection(connectionString);
try
{
sqlConn.Open();
}
catch (SqlException sqlE)
{
//The source argument must be less than 250 characters or an exception will be thrown when writing to the CancerGov event log
CancerGovError.LogError("DatabaseFunctions.ExecuteSQLScalar(string, string)", "ConnectionString: " + connectionString + "; CommandText: " + commandText.Substring(0, 100), ErrorType.DbUnavailable, sqlE);
}
SqlCommand sqlComm = new SqlCommand(commandText, sqlConn);
try
{
result = sqlComm.ExecuteScalar().ToString();
}
catch (SqlException sqlE)
{
//The source argument must be less than 250 characters or an exception will be thrown when writing to the CancerGov event log
CancerGovError.LogError("DatabaseFunctions.ExecuteSQLScalar(string, string)", "ConnectionString: " + connectionString + "; CommandText: " + commandText.Substring(0, 100), ErrorType.InvalidArgument, sqlE);
}
sqlConn.Close();
sqlConn.Dispose();
sqlComm.Dispose();
return(result);
}
public DataSet GetSearchFormMasterData()
{
DataSet dbSet = new DataSet();
SqlDataAdapter dbAdapter = null;
try
{
dbAdapter = new SqlDataAdapter("Select ShortName + ' - ' + ISNULL(FullName, ShortName) AS Name, ShortName AS StateAbbr From PoliticalSubUnit WHERE ShortName IS NOT NULL AND CountryName = 'U.S.A.' AND ShortName <> 'AS' Order By ShortName", ConfigurationManager.ConnectionStrings["CDRDbConnectionString"].ConnectionString);
dbAdapter.Fill(dbSet, "States");
dbAdapter.SelectCommand.CommandText = "Select FamilyCancerSyndrome, FamilyCancerSyndrome + ';' + CONVERT(varchar,FamilyCancerSyndromeListID) AS Value From GenProfFamilyCancerSyndromeList Order By FamilyCancerSyndrome";
dbAdapter.Fill(dbSet, "CancerFamily");
dbAdapter.SelectCommand.CommandText = "Select CancerTypeSite, CancerTypeSite + ';' + CONVERT(varchar,CancerTypeSiteID) As [Type] From GenProfCancerTypeSite Order By CancerTypeSite";
dbAdapter.Fill(dbSet, "CancerType");
dbAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
dbAdapter.SelectCommand.CommandText = "usp_GetGenProfCountry";
dbAdapter.Fill(dbSet, "Country");
}
catch (SqlException sqlE)
{
CancerGovError.LogError("GeneticProfessional GetSearchFormMasterData method: Error Getting the Master data for the Search Form: ", "", this.ToString(), ErrorType.DbUnavailable, sqlE);
}
finally
{
if (dbAdapter != null)
{
dbAdapter.Dispose();
}
}
return(dbSet);
}
public string GetXML()
{
string docXML = "";
DataTable dbTable = new DataTable();
SqlDataAdapter dbAdapter = null;
try {
//11-23-2004 BryanP: SCR1002 Changed the database object fetching from adhoc to stored proc.
dbAdapter = new SqlDataAdapter("usp_GetCDRDocumentXml", ConfigurationManager.ConnectionStrings["CDRDbConnectionString"].ConnectionString);
dbAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
dbAdapter.SelectCommand.Parameters.Add(new SqlParameter("@DocumentId", documentId));
dbAdapter.Fill(dbTable);
}
catch (SqlException sqlE) {
CancerGovError.LogError("Genetic Professional DAC", this.ToString(), ErrorType.DbUnavailable, sqlE);
} finally {
if (dbAdapter != null)
{
dbAdapter.Dispose();
}
}
if (dbTable.Rows.Count > 0)
{
docXML = dbTable.Rows[0]["XML"].ToString().Trim();
}
if (dbTable != null)
{
dbTable.Dispose();
}
return(docXML);
}
public static Hashtable GetViewObjectProperty(string viewObjectId)
{
Hashtable properties = new Hashtable();
DataTable dbTable = new DataTable();
try
{
SqlDataAdapter dbAdapter = new SqlDataAdapter("usp_GetViewObjectProperties @ViewObjectID='" + viewObjectId + "'", ConfigurationManager.ConnectionStrings["DbConnectionString"].ConnectionString);
dbAdapter.Fill(dbTable);
}
catch (SqlException sqlE)
{
CancerGovError.LogError("CancerGov.Common.Functions.GetViewObjectProperty", ErrorType.DbUnavailable, sqlE);
}
foreach (DataRow dbRow in dbTable.Rows)
{
properties.Add(dbRow["PropertyName"].ToString().Trim().ToLower(), Functions.IfNull(dbRow["PropertyValue"], ""));
}
dbTable.Clear();
dbTable.Dispose();
return(properties);
}
/// <summary>
/// Method gets view object property value from cancergov database
/// </summary>
/// <param name="viewobjectId">NCIViewId owning property</param>
/// <param name="property">Name of property to get</param>
/// <returns>Value of view object property</returns>
public static string GetViewObjectProperty(string viewObjectId, string property)
{
string propertyValue = "";
DataTable dbTable = new DataTable();
SqlDataAdapter dbAdapter = new SqlDataAdapter("usp_GetViewObjectProperties @ViewObjectID='" + viewObjectId + "', @PropertyName='" + property + "'", ConfigurationManager.ConnectionStrings["DbConnectionString"].ConnectionString);
try
{
dbAdapter.Fill(dbTable);
}
catch (SqlException)
{
CancerGovError.LogError("GetViewObjectProperty: viewObjectId=\"" + viewObjectId + "\"", "CancerGov.Common.Utility.Functions.GetViewObjectProperty", ErrorType.DbUnavailable, ErrorTypeDesc.DbUnavailable);
}
if (dbTable.Rows.Count > 0)
{
if (HasValue(dbTable.Rows[0]["PropertyValue"].ToString().Trim()))
{
propertyValue = dbTable.Rows[0]["PropertyValue"].ToString().Trim();
}
}
dbTable.Clear();
return(propertyValue);
}
/// <summary>
/// Method used to query database for English language results. This will return the
/// data in XML format. There is no way to create an additional WebGet so that we
/// can return the same data in JSON format.
/// </summary>
/// <param name="criteria">The partial text used to query the database</param>
/// <param name="maxRows">The maximum number of rows that the database will return. a value of zero will return the entire set</param>
/// <param name="contains">Indicator on whether the text is to be search from the beginning of the text or anywhere in the string</param>
/// <returns>Returns the search results</returns>
private DrugDictionaryServiceCollection Search(string criteria, int maxRows, bool contains)
{
// create the collection variable
DrugDictionaryServiceCollection sc = new DrugDictionaryServiceCollection();
try
{
// Call the database query
DrugDictionaryCollection dc =
DrugDictionaryManager.SearchNameOnly(criteria, maxRows, contains);
// Use Linq to extract the data from the business layer and create
// the service data objects
var collection = dc.ConvertAll(entry => new DrugDictionaryServiceItem(
entry.TermID,
entry.PreferredName,
string.Empty
));
sc.AddRange(collection);
}
catch (Exception ex)
{
// Log the error that occured
CancerGovError.LogError("DrugDictionary", 2, ex);
}
return(sc);
}
public static void LogUserInput(string eventSrc, string destUrl, string clientIP, string inputValue, string sessionID)
{
try
{
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DbConnectionString"].ConnectionString))
{
using (SqlCommand cmd = new SqlCommand("usp_LogClick", conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@EventSrc", SqlDbType.VarChar));
cmd.Parameters.Add(new SqlParameter("@ClickValue", SqlDbType.VarChar));
cmd.Parameters.Add(new SqlParameter("@ClickItem", SqlDbType.VarChar));
cmd.Parameters.Add(new SqlParameter("@ClientIP", SqlDbType.VarChar));
cmd.Parameters.Add(new SqlParameter("@UserSessionID", SqlDbType.VarChar));
cmd.Parameters["@EventSrc"].Value = eventSrc;
cmd.Parameters["@ClickValue"].Value = destUrl;
cmd.Parameters["@ClickItem"].Value = inputValue;
cmd.Parameters["@ClientIP"].Value = eventSrc;
cmd.Parameters["@UserSessionID"].Value = sessionID;
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close();
}
}
}
catch (Exception e) {
CancerGovError.LogError("", "Functions.LogUserInput(string inputValue)", ErrorType.DbUnavailable, e);
}
}
/// <summary>
/// A private method that is used by both GetTermDictionary by id and Name.
/// </summary>
/// <param name="byType">The value that specifies the type of search to be performed.</param>
/// <param name="language">This is English or Spanish</param>
/// <param name="termId">The CDRID of the term</param>
/// <param name="termName">The term name</param>
/// <param name="audience">This Patient or HealthProfessional</param>
/// <returns>TermDictionaryServiceItem instance</returns>
private TermDictionaryServiceItem getTermDictionary(TermDefinitionByType byType, string language, int termId, string termName, string audience)
{
TermDictionaryServiceItem termDicSvcItem = null;
try
{
// language passed to an enum calling this also validates the language value passed is meaningful DisplayLanguage
DisplayLanguage displayLanguage =
(DisplayLanguage)Enum.Parse(typeof(DisplayLanguage), language);
// Call the database query
TermDictionaryDataItem termDicDataItem = null;
if (byType == TermDefinitionByType.ById)
{
termDicDataItem = TermDictionaryManager.GetDefinitionByTermID(language, termId.ToString(), audience, 1);
}
else
{
termDicDataItem = TermDictionaryManager.GetDefinitionByTermName(displayLanguage, termName, audience, 1);
}
if (termDicDataItem != null)
{
termDicSvcItem = createTermDictionarySvcItem(termDicDataItem);
}
}
catch (Exception ex)
{
// Log the error that occured
CancerGovError.LogError("TermDictionary", 2, ex);
}
return(termDicSvcItem);
}
/// <summary>
/// Method used to query database for English language results. This will return the
/// data in XML format. There is no way to create an additional WebGet so that we
/// can return the same data in JSON format.
/// </summary>
/// <param name="language">The language needed to do the lookup</param>
/// <param name="criteria">The partial text used to query the database</param>
/// <param name="maxRows">The maximum number of rows that the database will return. a value of zero will return the entire set</param>
/// <param name="contains">Indicator on whether the text is to be search from the beginning of the text or anywhere in the string</param>
/// <param name="dictionary">Which Term dicitonary to search - Cancer.gov or Genetics</param>
/// <param name="audience">Definition audience - Patient or Health professional</param>
/// <returns>Returns the search results</returns>
private TermDictionaryServiceCollection Search(string language, string criteria, int maxRows, bool contains, string dictionary, string audience)
{
// create the collection variable
TermDictionaryServiceCollection sc = new TermDictionaryServiceCollection();
try
{
// language passed to an enum
DisplayLanguage displayLanguage =
(DisplayLanguage)Enum.Parse(typeof(DisplayLanguage), language);
// Call the database query
TermDictionaryCollection dc =
TermDictionaryManager.Search(language, criteria, maxRows, contains, dictionary, audience);
// Use Linq to extract the data from the business layer and create
// the service data objects
var collection = dc.ConvertAll(entry => new TermDictionaryServiceItem(
entry.GlossaryTermID,
entry.TermName,
string.Empty
));
sc.AddRange(collection);
}
catch (Exception ex)
{
// Log the error that occured
CancerGovError.LogError("TermDictionary", 2, ex);
}
return(sc);
}
/// <summary>
/// This method is a public call
/// It calls the database query and uses the preferred name to get the previous
/// and next term names.
/// </summary>
/// <param name="di"></param>
/// <param name="nRows"></param>
public static void GetTermNeighbors(DrugDictionaryDataItem di, int nRows)
{
try
{
// Call the database layer and get data
DataTable dt = DrugDictionaryQuery.GetTermNeighbors(
di.PreferredName, nRows);
// use Linq to move information from the dataTable
// into the DrugDictionaryDataItem
di.PreviousNeighbors.AddRange(
from entry in dt.AsEnumerable()
where entry["isPrevious"].ToString() == "Y"
select GetEntryFromDR(entry)
);
// use Linq to move information from the dataTable
// into the DrugDictionaryDataItem
di.NextNeighbors.AddRange(
from entry in dt.AsEnumerable()
where entry["isPrevious"].ToString() == "N"
select GetEntryFromDR(entry)
);
}
catch (Exception ex)
{
CancerGovError.LogError("DrugDictionatyManager", 2, ex);
throw ex;
}
}
/// <summary>
/// Method determines if a particular view property is set for a view
/// </summary>
/// <param name="viewId">NCIViewId</param>
/// <param name="property">Valid ViewProperty.PropertyName value</param>
/// <returns></returns>
public static bool IsPropertySet(string viewId, string property)
{
bool isSet = false;
if (Functions.IsGuid(viewId))
{
DataTable dbTable = new DataTable();
SqlDataAdapter dbAdapter = new SqlDataAdapter("usp_GetViewProperties @ViewID='" + viewId + "', @PropertyName='" + property + "'", ConfigurationManager.ConnectionStrings["DbConnectionString"].ConnectionString);
try {
dbAdapter.Fill(dbTable);
}
catch (SqlException) {
CancerGovError.LogError("IsPropertySet: viewId=\"" + viewId + "\"", "CancerGov.Common.Utility.Functions.IsPropertySet", ErrorType.DbUnavailable, ErrorTypeDesc.DbUnavailable);
}
if (dbTable.Rows.Count > 0)
{
if (HasValue(dbTable.Rows[0]["PropertyValue"].ToString()) && dbTable.Rows[0]["PropertyValue"].ToString().Trim().ToUpper() == "YES")
{
isSet = true;
}
}
dbTable.Clear();
}
return(isSet);
}
/// <summary>
/// Connects to the database, and executes the stored proc with the required parameters. The
/// resulting content is the guid associated with the cached print content.
/// </summary>
/// <param name="content"></param>
/// <param name="searchParams"></param>
/// <param name="isLive"></param>
/// <returns>A guid.</returns>
public static Guid SavePrintResult(string content, IEnumerable <String> trialIDs, CTSSearchParams searchParams, bool isLive)
{
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DbConnectionString"].ConnectionString))
{
Guid printResultGuid = Guid.Empty;
SqlParameter[] parameters =
{
new SqlParameter("@printid", SqlDbType.UniqueIdentifier),
new SqlParameter("@content", SqlDbType.NVarChar, content.Length),
new SqlParameter("@searchparams", SqlDbType.NVarChar),
new SqlParameter("@isLive", SqlDbType.Bit),
new SqlParameter("@trialids", SqlDbType.Structured)
};
parameters[0].Direction = ParameterDirection.Output;
parameters[1].Value = content;
parameters[2].Value = new JavaScriptSerializer().Serialize(searchParams);
parameters[3].Value = isLive;
parameters[4].Value = CreatePrintIdDataTable(trialIDs);
try
{
SqlHelper.ExecuteNonQuery(conn, CommandType.StoredProcedure, "dbo.ct_insertPrintresultcache", parameters);
printResultGuid = (Guid)parameters[0].Value;
}
catch (SqlException ex)
{
CancerGovError.LogError("Unable to save data. Search Params: " + searchParams + "isLive: " + isLive, 2, ex);
}
return(printResultGuid);
}
}
/// <summary>
/// This methods filters the information passed to it in order to refine the query
/// that will be called in the database layer.
/// </summary>
/// <param name="language">enumeration indicating language</param>
/// <param name="criteria">The partial text used to query the database</param>
/// <param name="maxRows">The maximum number of rows that the database will return. a value of zero will return the entire set</param>
/// <param name="contains">indicator as to whether the text is to be searched starting from the beginning or anywhere
/// in the string</param>
/// <param name="pageNumber">The page for which the records should be returned.The records returned
/// will always equal maxRows in every page.</param>
/// <returns>Returns the search results</returns>
public static TermDictionaryCollection GetTermDictionaryList(string language, string criteria, bool contains, int maxRows, int pageNumber, ref int totalRecordCount)
{
TermDictionaryCollection dc = new TermDictionaryCollection();
totalRecordCount = 0;
// Find out how we should search for the string
if (Strings.Clean(criteria) != null)
{
// replace any '[' with '[[]'
//criteria = criteria.Replace("[", "[[]");
// put the '%' at the end to indicate that the search starts
// with the criteria passed.
criteria += "%";
// put the '%' at the beginning to indicate that the search
// data contains the criteria passed
if (contains)
{
criteria = "%" + criteria;
}
// Find out the field we need to get to build our list
string fieldName = "TermName";
if (language == "Spanish")
{
fieldName = language.ToString() + fieldName;
}
try
{
// Call the database layer and get data
DataTable dt = TermDictionaryQuery.GetTermDictionaryList(
language.ToString(),
criteria,
maxRows,
pageNumber,
ref totalRecordCount);
// use Linq to move information from the dataTable
// into the TermDictionaryCollection
dc.AddRange(
from entry in dt.AsEnumerable()
select GetEntryFromDR(entry)
);
}
catch (Exception ex)
{
CancerGovError.LogError("TermDictionatyManager", 2, ex);
throw ex;
}
}
return(dc);
}
public static DataTable GetTermNeighbors(string language, string termName, int nMatches)
{
// create our null object
DataTable dt = null;
// create our parameter array
SqlParameter[] parms =
{
new SqlParameter("@name", SqlDbType.VarChar),
new SqlParameter("@num", SqlDbType.Int),
new SqlParameter("@language", SqlDbType.VarChar)
};
// Set the values on the parameters
parms[0].Value = termName;
parms[1].Value = nMatches;
parms[2].Value = language;
try
{
// Query the database and get the results
dt = SqlHelper.ExecuteDatatable(
ConfigurationManager.ConnectionStrings["CDRDbConnectionString"].ConnectionString,
CommandType.StoredProcedure,
"usp_GetGlossaryTermNeighbors",
parms);
// Add the columns we don't have to make this work at the manager level
dt.Columns.Add(new DataColumn("MediaHTML", System.Type.GetType("System.String")));
dt.Columns.Add(new DataColumn("MediaCaption", System.Type.GetType("System.String")));
dt.Columns.Add(new DataColumn("MediaID", System.Type.GetType("System.String")));
dt.Columns.Add(new DataColumn("AudioMediaHTML", System.Type.GetType("System.String")));
dt.Columns.Add(new DataColumn("RelatedInformationHtml", System.Type.GetType("System.String")));
//if (dt.Rows.Count > 0)
//{
//if (language == "Spanish")
//{
// dt.Columns.Add(new DataColumn("TermName", System.Type.GetType("System.String")));
//}
//else
//{
dt.Columns.Add(new DataColumn("OLTermName", System.Type.GetType("System.String")));
//}
//}
}
catch (Exception ex)
{
CancerGovError.LogError("TermDictionaryQuery", 2, ex);
throw ex;
}
// return the DataTable
return(dt);
}
/// <summary>
/// Returns a TermDictionaryServiceList which contains a collection of Term Dictionary
/// items and the total number of records.
/// </summary>
/// <param name="language"></param>
/// <param name="criteria"></param>
/// <param name="contains"></param>
/// <param name="maxRows">Maxrows is treated as recordPerPage or the topN records.
/// Used for records per page when pagenumber is 0 or greater than 0.
/// If pagenumber is -1 number records returned is specified by maxRows.
/// If maxRows=0 and pageNumber=-1 all records are returned.</param>
/// <param name="pageNumber">Specifies the pagenumber for which the records should be returned.
/// If pagenumber is -1 number records returned is specified by maxRows.
/// If maxRows=0 and pageNumber=-1 all records are returned.</param>
/// <returns></returns>
private TermDictionaryServiceList getTermDictionaryList(string language, string criteria, bool contains, int maxRows, int pageNumber)
{
TermDictionaryServiceList sc = new TermDictionaryServiceList();
sc.TermDictionaryServiceCollection = new TermDictionaryServiceCollection();
sc.TotalRecordCount = 0;
try
{
int totalRecordCount = 0;
// No criteria specified
if (string.IsNullOrEmpty(criteria))
{
return(sc);
}
// if maxrows is 0 and pagenumber is > 0 then return empty.
if ((maxRows == 0 && pageNumber >= 0) || (maxRows < 0))
{
return(sc);
}
if (pageNumber == 0)
{
pageNumber = 1;
}
// language passed to an enum
DisplayLanguage displayLanguage =
(DisplayLanguage)Enum.Parse(typeof(DisplayLanguage), language);
// Call the database query
TermDictionaryCollection dc =
TermDictionaryManager.GetTermDictionaryList(language, criteria, contains, maxRows, pageNumber, ref totalRecordCount);
// Use Linq to extract the data from the business layer and create
// the service data objects
sc.TermDictionaryServiceCollection.AddRange(
from entry in dc
select createTermDictionarySvcItem(entry)
);
sc.TotalRecordCount = totalRecordCount;
}
catch (Exception ex)
{
// Log the error that occured
CancerGovError.LogError("TermDictionary", 2, ex);
}
return(sc);
}
private static DataTable GetDefinition(string language, string termName, string termID, string audience)
{
// create our null object
DataTable dt = null;
// create our parameter array
SqlParameter[] parms =
{
new SqlParameter("@Term", SqlDbType.VarChar),
new SqlParameter("@ID", SqlDbType.VarChar),
new SqlParameter("@Audience", SqlDbType.VarChar),
new SqlParameter("@language", SqlDbType.VarChar)
};
// Set the values on the parameters
parms[0].Value = termName;
parms[1].Value = termID;
parms[2].Value = audience;
parms[3].Value = language;
try
{
// Query the database and get the results
dt = SqlHelper.ExecuteDatatable(
ConfigurationManager.ConnectionStrings["CDRDbConnectionString"].ConnectionString,
CommandType.StoredProcedure,
"usp_GetGlossaryDefinition",
parms);
// Let's add the SpanishTermName so we don't break in the manager
//if (dt.Rows.Count > 0)
//{
//if (language == "Spanish")
//{
// dt.Columns.Add(new DataColumn("TermName", System.Type.GetType("System.String")));
//}
//else
//{
dt.Columns.Add(new DataColumn("OLTermName", System.Type.GetType("System.String")));
//}
//}
}
catch (Exception ex)
{
CancerGovError.LogError("TermDictionaryQuery", 2, ex);
throw ex;
}
// return the DataTable
return(dt);
}
public static DataTable GetTermDictionaryList(string language, string criteria, int rows, int pageNumber, ref int totalRecordCount)
{
// create our null object
DataTable dt = null;
totalRecordCount = 0;
SqlParameter outputParam = new SqlParameter("@totalresult", SqlDbType.Int);
outputParam.Direction = ParameterDirection.Output;
// create our parameter array
SqlParameter[] parms =
{
new SqlParameter("@Criteria", SqlDbType.VarChar),
new SqlParameter("@Language", SqlDbType.VarChar),
new SqlParameter("@topN", SqlDbType.Int),
new SqlParameter("@pagenumber", SqlDbType.Int),
new SqlParameter("@recordsPerPage", SqlDbType.Int),
outputParam
};
// Set the values on the parameters
parms[0].Value = criteria;
parms[1].Value = language;
parms[2].Value = rows;
parms[3].Value = pageNumber;
parms[4].Value = rows;
try
{
// Query the database and get the results
dt = SqlHelper.ExecuteDatatable(
ConfigurationManager.ConnectionStrings["CDRDbConnectionString"].ConnectionString,
CommandType.StoredProcedure,
"usp_GetGlossary",
parms);
totalRecordCount = (int)parms[5].Value;
}
catch (Exception ex)
{
CancerGovError.LogError("TermDictionaryQuery", 2, ex);
throw ex;
}
// return the DataTable
return(dt);
}
/// <summary>
/// This methods filters the information passed to it in order to refine the query
/// that will be called in the database layer.
/// </summary>
/// <param name="criteria">The partial text used to query the database</param>
/// <param name="maxRows">The maximum number of rows that the database will return. a value of zero will return the entire set</param>
/// <param name="contains">indicator as to whether the text is to be searched starting from the beginning or anywhere
/// in the string</param>
/// <returns>Returns the search results</returns>
public static DrugDictionaryCollection SearchNameOnly(string criteria, int maxRows, bool contains)
{
DrugDictionaryCollection dc = new DrugDictionaryCollection();
// Find out how we should search for the string
if (Strings.Clean(criteria) != null)
{
// put the '%' at the end to indicate that the search starts
// with the criteria passed.
criteria += "%";
// put the '%' at the beginning to indicate that the search
// data contains the criteria passed
if (contains)
{
criteria = "%" + criteria;
}
// The stored procedure needs to change to reflect a 0 number of
// rows to mean the entire set.
if (maxRows <= 0)
{
maxRows = 9999;
}
try
{
// Call the database layer and get data
DataTable dt = DrugDictionaryQuery.SearchNameOnly(criteria, maxRows);
// use Linq to move information from the dataTable
// into the DrugDictionaryCollection
dc.AddRange(
from entry in dt.AsEnumerable()
select GetEntryFromDR(entry)
);
}
catch (Exception ex)
{
CancerGovError.LogError("DrugDictionatyManager", 2, ex);
throw ex;
}
}
// return the collection
return(dc);
}
public static DataTable Search(string criteria, int rows, int curPage, char otherNames, out int matchCount)
{
// create our null object
DataTable dt = null;
// create our parameter array
SqlParameter[] parms =
{
new SqlParameter("@Match", SqlDbType.NVarChar),
new SqlParameter("@PageSize", SqlDbType.Int),
new SqlParameter("@CurPage", SqlDbType.Int),
new SqlParameter("@OtherNamesP", SqlDbType.Char),
new SqlParameter("@NumMatches", SqlDbType.Int)
};
// Set the values on the parameters
parms[0].Value = criteria;
parms[1].Value = rows;
parms[2].Value = curPage;
parms[3].Value = otherNames;
parms[4].Direction = ParameterDirection.Output;
try
{
// Query the database and get the results
dt = SqlHelper.ExecuteDatatable(
ConfigurationManager.ConnectionStrings["CDRDbConnectionString"].ConnectionString,
CommandType.StoredProcedure,
"usp_TermDD_GetMatchingTerms",
parms);
// Add the column so that the manager doesn't get upset
dt.Columns.Add(new DataColumn("PrettyURL", System.Type.GetType("System.String")));
// Get the total number of matches we found. This value may be different than
// the actual number of rows returned
matchCount = (int)parms[4].Value;
}
catch (Exception ex)
{
CancerGovError.LogError("DrugDictionaryQuery", 2, ex);
throw ex;
}
// return the DataTable
return(dt);
}
/// <summary>
/// This methods filters the information passed to it in order to refine the query
/// that will be called in the database layer.
/// </summary>
/// <param name="language">enumeration indicating language</param>
/// <param name="criteria">The partial text used to query the database</param>
/// <param name="maxRows">The maximum number of rows that the database will return. a value of zero will return the entire set</param>
/// <param name="contains">indicator as to whether the text is to be searched starting from the beginning or anywhere
/// in the string</param>
/// <returns>Returns the search results</returns>
public static AutoSuggestSearchCollection Search(string language, string criteria, int maxRows, bool contains)
{
AutoSuggestSearchCollection dc = new AutoSuggestSearchCollection();
// Find out how we should search for the string
if (Strings.Clean(criteria) != null)
{
// input criteria cleaup
// 1. replace any ',' or ';' with space
// 2. Trim whitespace.
criteria = criteria.Trim();
criteria = criteria.Replace(",", " ");
criteria = criteria.Replace(";", " ");
// Find out the field we need to get to build our list
string fieldName = "TermName";
if (language == "Spanish")
{
fieldName = language.ToString() + fieldName;
}
try
{
// Call the database layer and get data
DataTable dt = AutoSuggestSearchQuery.Search(
language.ToString(),
criteria,
maxRows);
// use Linq to move information from the dataTable
// into the AutoSuggestSearchCollection
dc.AddRange(
from entry in dt.AsEnumerable()
select GetEntryFromDR(entry)
);
}
catch (Exception ex)
{
CancerGovError.LogError("TermDictionatyManager", 2, ex);
throw ex;
}
}
return(dc);
}
/// <summary>
/// This method calls the database layer for a single item and returns
/// the drugDictionaryDataItem
/// </summary>
/// <param name="termID"></param>
/// <returns></returns>
public static DrugDictionaryDataItem GetDefinitionByTermID(int termID)
{
DrugDictionaryDataItem di = null;
try
{
// Call the database layer and get data
DataSet ds =
DrugDictionaryQuery.GetDefinitionByTermID(termID);
// Only do something if we have data
if (ds.Tables.Count > 0)
{
// Get the entry record
if (ds.Tables[0].Rows.Count == 1)
{
DataRow dr = ds.Tables[0].Rows[0];
dr["TermID"] = termID;
// build the data item
di = GetEntryFromDR(ds.Tables[0].Rows[0]);
// Get the display names
if (ds.Tables.Count > 1)
{
GetDisplayNames(di, ds.Tables[1]);
}
}
else if (ds.Tables[0].Rows.Count > 0)
{
throw new Exception("GetDefinitionByTermID returned more than 1 record.");
}
}
}
catch (Exception ex)
{
CancerGovError.LogError("DrugDictionaryManager", 2, ex);
throw ex;
}
return(di);
}
/// <summary>
/// Method will return a single TermDictionaryItem with its associated
/// TermDictionaryNeighbors
/// </summary>
/// <param name="language"></param>
/// <param name="termName"></param>
/// <param name="nMatches"></param>
/// <returns></returns>
public static TermDictionaryDataItem GetDefinitionByTermID(string language, string termID, string audience, int nNeighborMatches)
{
TermDictionaryDataItem di = null;
// default the audience if there was none supplied
if (string.IsNullOrEmpty(audience))
{
audience = "Patient";
}
try
{
// Call the database layer and get data
DataTable dt =
TermDictionaryQuery.GetDefinitionByTermID(
language.ToString(),
termID,
audience);
// Get the entry record
if (dt.Rows.Count == 1)
{
// build the data item
di = GetEntryFromDR(dt.Rows[0]);
// Get the neighbors
GetTermNeighbors(di, language, nNeighborMatches);
}
else if (dt.Rows.Count > 0)
{
throw new Exception("GetDefinitionByTermID returned more than 1 record.");
}
}
catch (Exception ex)
{
CancerGovError.LogError("TermDictionaryManager", 2, ex);
throw ex;
}
return(di);
}
/// <summary>
/// This method is a public call for use by the GetDefinitionByTermID method
/// It takes the information from the second table in the call to usp_GetTermDefinition
/// This builds the DisplayName dictionary list
/// </summary>
/// <param name="di"></param>
/// <param name="dt"></param>
private static void GetDisplayNames(DrugDictionaryDataItem di, DataTable dt)
{
try
{
// Initialize our vars
string lastDisplayName = string.Empty;
List <string> listOfNames = null;
// We will not use Linq since this is a more complex list
foreach (DataRow dr in dt.Rows)
{
if (!lastDisplayName.Equals(dr["displayName"].ToString()))
{
// Save anything that we might have
if (!lastDisplayName.Equals(string.Empty))
{
di.DisplayNames.Add(lastDisplayName, listOfNames);
}
// Get a new list of names
lastDisplayName = dr["displayName"].ToString();
listOfNames = new List <string>();
}
// Add the name to the list
listOfNames.Add(dr["otherName"].ToString());
}
// See if we need to add anything to the dictionary
if (!lastDisplayName.Equals(string.Empty))
{
di.DisplayNames.Add(lastDisplayName, listOfNames);
}
}
catch (Exception ex)
{
CancerGovError.LogError("DrugDictionatyManager", 2, ex);
throw ex;
}
}
public static DataTable Search(string language, string criteria, int rows)
{
// create our null object
DataTable dt = null;
// create our parameter array
SqlParameter[] parms =
{
new SqlParameter("@t", SqlDbType.VarChar)
};
// Set the values on the parameters
parms[0].Value = criteria;
string storedProcName = "autosuggest_en_search";
if (language.ToLower() == "spanish")
{
storedProcName = "autosuggest_es_search";
}
try
{
// Query the database and get the results
dt = SqlHelper.ExecuteDatatable(
ConfigurationManager.ConnectionStrings["DbConnectionString"].ConnectionString,
CommandType.StoredProcedure,
storedProcName,
parms);
}
catch (Exception ex)
{
CancerGovError.LogError("AutoSuggestSearchQuery", 2, ex);
throw ex;
}
// return the DataTable
return(dt);
}
public static DataSet GetDefinitionByTermID(int termID)
{
// create our null object
DataSet ds = null;
// create our parameter array
SqlParameter[] parms =
{
new SqlParameter("@TermID", SqlDbType.Int)
};
// Set the values on the parameters
parms[0].Value = termID;
try
{
// Query the database and get the results
ds = SqlHelper.ExecuteDataset(
ConfigurationManager.ConnectionStrings["CDRDbConnectionString"].ConnectionString,
CommandType.StoredProcedure,
"usp_TermDD_GetTermDefinition",
parms);
// Add the columns so that the manager doesn't get upset
if (ds.Tables.Count > 0)
{
ds.Tables[0].Columns.Add(new DataColumn("TermID", System.Type.GetType("System.Int32")));
ds.Tables[0].Columns.Add(new DataColumn("OtherName", System.Type.GetType("System.Int32")));
}
}
catch (Exception ex)
{
CancerGovError.LogError("DrugDictionaryQuery", 2, ex);
throw ex;
}
// return the DataTable
return(ds);
}
public static DataTable SearchNameOnly(string criteria, int rows)
{
// create our null object
DataTable dt = null;
// create our parameter array
SqlParameter[] parms =
{
new SqlParameter("@criteria", SqlDbType.NVarChar),
new SqlParameter("@TopN", SqlDbType.Int),
};
// Set the values on the parameters
parms[0].Value = criteria;
parms[1].Value = rows;
try
{
// Query the database and get the results
dt = SqlHelper.ExecuteDatatable(
ConfigurationManager.ConnectionStrings["CDRDbConnectionString"].ConnectionString,
CommandType.StoredProcedure,
"usp_getDrugTermName",
parms);
// Add the column so that the manager doesn't get upset
dt.Columns.Add(new DataColumn("otherName", System.Type.GetType("System.String")));
dt.Columns.Add(new DataColumn("definitionHTML", System.Type.GetType("System.String")));
dt.Columns.Add(new DataColumn("PrettyURL", System.Type.GetType("System.String")));
}
catch (Exception ex)
{
CancerGovError.LogError("DrugDictionaryQuery", 2, ex);
throw ex;
}
// return the DataTable
return(dt);
}
