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