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); }