public List<DataObjectSessionsOverview> GetAllSessionsOverview(string sortData) { SqlConnection conn = new SqlConnection(connectionString); conn.Open(); List<DataObjectSessionsOverview> DataTemplateODSList = new List<DataObjectSessionsOverview>(); SqlDataReader reader = null; string sqlSelectString = "SELECT UserFirstName,UserLastName,Number,Capacity,SessionId,SessionTimesId,StartTime,title FROM [dbo].[SessionsOverview] "; SqlCommand cmd = new SqlCommand(sqlSelectString, conn); reader = cmd.ExecuteReader(); try { while (reader.Read()) { string userfirstname = reader.IsDBNull(0) ? "" : reader.GetString(0); string userlastname = reader.IsDBNull(1) ? "" : reader.GetString(1); string number = reader.IsDBNull(2) ? "" : reader.GetString(2); int capacity = reader.IsDBNull(3) ? 0 : reader.GetInt32(3); int sessionid = reader.IsDBNull(4) ? 0 : reader.GetInt32(4); int sessiontimesid = reader.IsDBNull(5) ? 0 : reader.GetInt32(5); DateTime starttime = reader.IsDBNull(6) ? DateTime.Now : reader.GetDateTime(6); string title = reader.IsDBNull(7) ? "" : reader.GetString(7); DataObjectSessionsOverview td = new DataObjectSessionsOverview(userfirstname, userlastname, number, capacity, sessionid, sessiontimesid, starttime, title); DataTemplateODSList.Add(td); } } finally { if (reader != null) reader.Close(); } conn.Close(); if (sortData == null) { sortData = "Title"; } if (sortData.Length == 0) { sortData = "Title"; } string sortDataBase = sortData; string descString = " DESC"; if (sortData.EndsWith(descString)) { sortDataBase = sortData.Substring(0, sortData.Length - descString.Length); } Comparison<DataObjectSessionsOverview> comparison = null; switch (sortDataBase) { case "Userfirstname": comparison = delegate(DataObjectSessionsOverview lhs, DataObjectSessionsOverview rhs) { return lhs.Userfirstname.CompareTo(rhs.Userfirstname); }; break; case "Userlastname": comparison = new Comparison<DataObjectSessionsOverview>( delegate(DataObjectSessionsOverview lhs, DataObjectSessionsOverview rhs) { return lhs.Userlastname.CompareTo(rhs.Userlastname); } ); break; case "Number": comparison = new Comparison<DataObjectSessionsOverview>( delegate(DataObjectSessionsOverview lhs, DataObjectSessionsOverview rhs) { return lhs.Number.CompareTo(rhs.Number); } ); break; case "Capacity": comparison = new Comparison<DataObjectSessionsOverview>( delegate(DataObjectSessionsOverview lhs, DataObjectSessionsOverview rhs) { return lhs.Capacity.CompareTo(rhs.Capacity); } ); break; case "Sessionid": comparison = new Comparison<DataObjectSessionsOverview>( delegate(DataObjectSessionsOverview lhs, DataObjectSessionsOverview rhs) { return lhs.Sessionid.CompareTo(rhs.Sessionid); } ); break; case "Sessiontimesid": comparison = new Comparison<DataObjectSessionsOverview>( delegate(DataObjectSessionsOverview lhs, DataObjectSessionsOverview rhs) { return lhs.Sessiontimesid.CompareTo(rhs.Sessiontimesid); } ); break; case "Starttime": comparison = new Comparison<DataObjectSessionsOverview>( delegate(DataObjectSessionsOverview lhs, DataObjectSessionsOverview rhs) { return lhs.Starttime.CompareTo(rhs.Starttime); } ); break; case "Title": comparison = new Comparison<DataObjectSessionsOverview>( delegate(DataObjectSessionsOverview lhs, DataObjectSessionsOverview rhs) { return lhs.Title.CompareTo(rhs.Title); } ); break; } if (comparison != null) { DataTemplateODSList.Sort(comparison); if (sortData.ToLower().EndsWith("desc")) { DataTemplateODSList.Reverse(); } } return DataTemplateODSList; }
public List<DataObjectSessionsOverview> GetBySessionTimesId(int sessionTimesId,int codeCampYearId) { int currentCodeCampYearId = Utils.GetCurrentCodeCampYear(); List<DataObjectSessionsOverview> DataTemplateODSList = null; string cacheName = String.Format("{0}-{1}-{2}", CodeCampSV.Utils.CacheBySessionTimesId, sessionTimesId, currentCodeCampYearId); if (HttpContext.Current.Cache[cacheName] == null) { SqlConnection conn = new SqlConnection(connectionString); conn.Open(); DataTemplateODSList = new List<DataObjectSessionsOverview>(); SqlDataReader reader = null; string sqlSelectString = @"SELECT UserFirstName, UserLastName, Number, Capacity, SessionId, SessionTimesId, StartTime, title FROM SessionsOverview WHERE SessionTimesId = @SessionTimesId AND CodeCampYearId = @CodeCampYearId ORDER BY title"; SqlCommand cmd = new SqlCommand(sqlSelectString, conn); cmd.Parameters.Add("@CodeCampYearId", SqlDbType.Int).Value = codeCampYearId; cmd.Parameters.Add("@SessionTimesId", SqlDbType.Int).Value = sessionTimesId; reader = cmd.ExecuteReader(); try { while (reader.Read()) { string userfirstname = reader.IsDBNull(0) ? "" : reader.GetString(0); string userlastname = reader.IsDBNull(1) ? "" : reader.GetString(1); string number = reader.IsDBNull(2) ? "" : reader.GetString(2); int capacity = reader.IsDBNull(3) ? 0 : reader.GetInt32(3); int sessionid = reader.IsDBNull(4) ? 0 : reader.GetInt32(4); int sessiontimesid = reader.IsDBNull(5) ? 0 : reader.GetInt32(5); DateTime starttime = reader.IsDBNull(6) ? DateTime.Now : reader.GetDateTime(6); string title = reader.IsDBNull(7) ? "" : reader.GetString(7); DataObjectSessionsOverview td = new DataObjectSessionsOverview(userfirstname, userlastname, number, capacity, sessionid, sessiontimesid, starttime, title); DataTemplateODSList.Add(td); } } finally { if (reader != null) reader.Close(); } conn.Close(); HttpContext.Current.Cache.Insert(cacheName, DataTemplateODSList, null, DateTime.Now.Add(new TimeSpan(0, 0, Utils.RetrieveSecondsForSessionCacheTimeout())), TimeSpan.Zero); } else { DataTemplateODSList = (List<DataObjectSessionsOverview>)HttpContext.Current.Cache[cacheName]; } return DataTemplateODSList; }