public List<DataObjectTags> GetTagsBySession(int sessionId, int maxReturn) { List<DataObjectTags> DataTemplateODSList = null; string cacheName = String.Format("{0}-{1}", Utils.CacheTagsGetBySession, sessionId); if (HttpContext.Current.Cache[cacheName] == null) { var conn = new SqlConnection(connectionString); conn.Open(); DataTemplateODSList = new List<DataObjectTags>(); //SqlDataReader reader = null; const string sqlSelectString = @" SELECT SessionTags.tagid, Tags.TagName FROM SessionTags INNER JOIN Tags ON (SessionTags.tagid = Tags.id) WHERE SessionTags.sessionId = @sessionId ORDER BY Tags.TagName "; var cmd = new SqlCommand(sqlSelectString, conn); cmd.Parameters.Add("@sessionId", SqlDbType.Int).Value = sessionId; SqlDataReader reader = cmd.ExecuteReader(); try { int cnt = 0; while (reader.Read() && cnt < maxReturn) { cnt++; int tagId = reader.IsDBNull(0) ? 0 : reader.GetInt32(0); string tagName = reader.IsDBNull(1) ? "" : reader.GetString(1); var dotags = new DataObjectTags(tagId, tagName); DataTemplateODSList.Add(dotags); } } finally { if (reader != null) reader.Close(); } conn.Close(); conn.Dispose(); HttpContext.Current.Cache.Insert(cacheName, DataTemplateODSList, null, DateTime.Now.Add(new TimeSpan(0, 0, Utils.RetrieveSecondsForSessionCacheTimeout())), TimeSpan.Zero); } else { DataTemplateODSList = (List<DataObjectTags>) HttpContext.Current.Cache[cacheName]; } return DataTemplateODSList; }
public List<DataObjectTags> GetByPrimaryKeyTags(string sortData, int searchid) { var conn = new SqlConnection(connectionString); conn.Open(); var DataTemplateODSList = new List<DataObjectTags>(); SqlDataReader reader = null; const string sqlSelectString = "SELECT TagName,TagDescription,id FROM [dbo].[Tags] WHERE id = @searchid "; var cmd = new SqlCommand(sqlSelectString, conn); cmd.Parameters.Add("@searchid", SqlDbType.Int, 4).Value = searchid; ; reader = cmd.ExecuteReader(); try { while (reader.Read()) { string tagname = reader.IsDBNull(0) ? "" : reader.GetString(0); string tagdescription = reader.IsDBNull(1) ? "" : reader.GetString(1); int sessionid = 0; Boolean sessionassigned = false; int id = reader.IsDBNull(2) ? 0 : reader.GetInt32(2); var td = new DataObjectTags(tagname, tagdescription, sessionid, sessionassigned, id); DataTemplateODSList.Add(td); } } finally { if (reader != null) reader.Close(); } conn.Close(); if (sortData == null) { sortData = "Id"; } if (sortData.Length == 0) { sortData = "Id"; } string sortDataBase = sortData; string descString = " DESC"; if (sortData.EndsWith(descString)) { sortDataBase = sortData.Substring(0, sortData.Length - descString.Length); } Comparison<DataObjectTags> comparison = null; switch (sortDataBase) { case "Tagname": comparison = delegate(DataObjectTags lhs, DataObjectTags rhs) { return lhs.Tagname.ToLower().Trim().CompareTo(rhs.Tagname.ToLower().Trim()); }; break; case "Tagdescription": comparison = delegate(DataObjectTags lhs, DataObjectTags rhs) { return lhs.Tagdescription.CompareTo(rhs.Tagdescription); }; break; case "Sessionassigned": comparison = delegate(DataObjectTags lhs, DataObjectTags rhs) { return lhs.Sessionassigned.CompareTo(rhs.Sessionassigned); }; break; case "Id": comparison = delegate(DataObjectTags lhs, DataObjectTags rhs) { return lhs.Id.CompareTo(rhs.Id); }; break; } if (comparison != null) { DataTemplateODSList.Sort(comparison); if (sortData.ToLower().EndsWith("desc")) { DataTemplateODSList.Reverse(); } } return DataTemplateODSList; }
public List<DataObjectTags> GetAllBySession(string sortData, int sessionid,bool useCache) { string cacheName = string.Format("{0}-{1}", Utils.CacheTagNameBySession, sessionid); List<DataObjectTags> DataTemplateODSList = (List<DataObjectTags>)HttpContext.Current.Cache[cacheName]; if (DataTemplateODSList == null || !useCache) // force thru if not wanting cache { using (var conn = new SqlConnection(connectionString)) { DataTemplateODSList = new List<DataObjectTags>(); conn.Open(); List<int> listOfTags = new List<int>(); // make this a two stepper. first, get the assigned values, and stuff // in a List, then get the full list and populate on return string sqlSelectString = "SELECT tagid from SessionTags WHERE sessionid=@sessionid "; using (SqlCommand cmda = new SqlCommand(sqlSelectString, conn)) { cmda.Parameters.Add("@sessionid", SqlDbType.Int).Value = sessionid; using (SqlDataReader readera = cmda.ExecuteReader()) { try { while (readera.Read()) { listOfTags.Add(readera.GetInt32(0)); } } finally { if (readera != null) readera.Close(); } } } sqlSelectString = "SELECT TagName,TagDescription,id FROM [dbo].[Tags] ORDER BY UPPER(LTRIM(TagName)) "; using (SqlCommand cmd = new SqlCommand(sqlSelectString, conn)) { using (SqlDataReader reader = cmd.ExecuteReader()) { try { while (reader.Read()) { string tagname = reader.IsDBNull(0) ? "" : reader.GetString(0); string tagdescription = reader.IsDBNull(1) ? "" : reader.GetString(1); int id = reader.IsDBNull(2) ? 0 : reader.GetInt32(2); Boolean sessionassigned = false; if (listOfTags.Contains(id)) { var td = new DataObjectTags(tagname, tagdescription, sessionid, sessionassigned, id); DataTemplateODSList.Add(td); } } } catch (Exception ex) { throw new ApplicationException("Error InsufficientMemoryException TagsODS: " + ex); } } } } if (useCache) { HttpContext.Current.Cache.Insert(cacheName, DataTemplateODSList, null, DateTime.Now.Add(new TimeSpan(0, 0, Utils. RetrieveSecondsForSessionCacheTimeout ())), TimeSpan.Zero); } } else { DataTemplateODSList = (List<DataObjectTags>)HttpContext.Current.Cache[cacheName]; } return DataTemplateODSList; }
public List<DataObjectTags> GetAllTags(string sortData, int searchsessionid) { var conn = new SqlConnection(connectionString); conn.Open(); var DataTemplateODSList = new List<DataObjectTags>(); SqlDataReader reader = null; const string sqlSelectString = "SELECT TagName,TagDescription,id FROM [dbo].[Tags] ORDER BY UPPER(LTRIM(TagName))"; var cmd = new SqlCommand(sqlSelectString, conn); reader = cmd.ExecuteReader(); try { while (reader.Read()) { string tagname = reader.IsDBNull(0) ? "" : reader.GetString(0); string tagdescription = reader.IsDBNull(1) ? "" : reader.GetString(1); int sessionid = 0; Boolean sessionassigned = false; int id = reader.IsDBNull(2) ? 0 : reader.GetInt32(2); var td = new DataObjectTags(tagname, tagdescription, sessionid, sessionassigned, id); DataTemplateODSList.Add(td); } } finally { if (reader != null) reader.Close(); } conn.Close(); //// do a quick case insensative sort //Comparison<DataObjectTags> comparison = // delegate(DataObjectTags lhs, DataObjectTags rhs) { return lhs.Tagname.ToLower().CompareTo(rhs.Tagname.ToLower()); }; //DataTemplateODSList.Sort(comparison); return DataTemplateODSList; }