public List<DataObjectSessions> GetBySessionTitle()
{
int currentCodeCampYearId = Utils.GetCurrentCodeCampYear();
List<DataObjectSessions> DataTemplateODSList = null;
string cacheName = String.Format("{0}-{1}", Utils.CacheSessionsAllByTitle, currentCodeCampYearId);
if (HttpContext.Current.Cache[cacheName] == null)
{
var conn = new SqlConnection(connectionString);
conn.Open();
DataTemplateODSList = new List<DataObjectSessions>();
SqlDataReader reader = null;
string sqlSelectString =
@"SELECT
dbo.Sessions.SessionLevel_id,
dbo.Sessions.Username,
dbo.Sessions.title,
dbo.Sessions.description,
dbo.Sessions.approved,
dbo.Sessions.createdate,
dbo.Sessions.updatedate,
dbo.Sessions.AdminComments,
dbo.Sessions.InterentAccessRequired,
dbo.Sessions.LectureRoomsId,
dbo.Sessions.SessionTimesId,
dbo.Sessions.id
FROM
dbo.Sessions
WHERE
CodeCampYearId=@CodeCampYearId
ORDER BY
dbo.Sessions.title";
//"SELECT SessionLevel_id,Username,title,description,approved,createdate,updatedate,AdminComments,InterentAccessRequired,LectureRoomsId,SessionTimesId,id FROM [dbo].[Sessions] ";
var cmd = new SqlCommand(sqlSelectString, conn);
cmd.Parameters.Add("@CodeCampYearId", SqlDbType.Int, 4).Value = Utils.GetCurrentCodeCampYear();
reader = cmd.ExecuteReader();
try
{
while (reader.Read())
{
int sessionlevel_id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
string username = reader.IsDBNull(1) ? "" : reader.GetString(1);
string title = reader.IsDBNull(2) ? "" : reader.GetString(2);
string description = reader.IsDBNull(3) ? "" : reader.GetString(3);
bool approved = reader.IsDBNull(4) ? false : reader.GetBoolean(4);
DateTime createdate = reader.IsDBNull(5) ? DateTime.Now : reader.GetDateTime(5);
DateTime updatedate = reader.IsDBNull(6) ? DateTime.Now : reader.GetDateTime(6);
string admincomments = reader.IsDBNull(7) ? "" : reader.GetString(7);
bool interentaccessrequired = reader.IsDBNull(8) ? false : reader.GetBoolean(8);
int lectureroomsid = reader.IsDBNull(9) ? 0 : reader.GetInt32(9);
int sessiontimesid = reader.IsDBNull(10) ? 0 : reader.GetInt32(10);
int id = reader.IsDBNull(11) ? 0 : reader.GetInt32(11);
var td = new DataObjectSessions(sessionlevel_id, username, title, description, approved,
createdate, updatedate, admincomments, interentaccessrequired,
lectureroomsid, sessiontimesid, id);
DataTemplateODSList.Add(td);
}
// DataTemplateODSList is sorted by title that could have funny stuff in it so need to resort with nimas stuff
DataTemplateODSList = Utils.SortSessionsByCleanTitle(DataTemplateODSList);
}
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<DataObjectSessions>) HttpContext.Current.Cache[cacheName];
}
return DataTemplateODSList;
}
public List<DataObjectSessions> GetByTrack(int trackId)
{
List<DataObjectSessions> DataTemplateODSList = null;
int currentCodeCampYearId = Utils.GetCurrentCodeCampYear();
string cacheName = String.Format("{0}-{1}-{2}", Utils.CacheSessionsGetByTrackWithParams, trackId, currentCodeCampYearId);
if (HttpContext.Current.Cache[cacheName] == null)
{
var conn = new SqlConnection(connectionString);
conn.Open();
DataTemplateODSList = new List<DataObjectSessions>();
SqlDataReader reader = null;
const string sqlSelectString = @"SELECT dbo.Sessions.SessionLevel_id,
dbo.Sessions.Username,
dbo.Sessions.title,
dbo.Sessions.description,
dbo.Sessions.approved,
dbo.Sessions.createdate,
dbo.Sessions.updatedate,
dbo.Sessions.AdminComments,
dbo.Sessions.InterentAccessRequired,
dbo.Sessions.LectureRoomsId,
dbo.Sessions.SessionTimesId,
dbo.Sessions.id
FROM dbo.SessionTimes
INNER JOIN dbo.Sessions ON (dbo.SessionTimes.id =
dbo.Sessions.SessionTimesId)
WHERE dbo.Sessions.CodeCampYearId = @CodeCampYearId AND
dbo.Sessions.id IN (SELECT TrackSession.SessionId FROM TrackSession WHERE
TrackId = @trackid)
ORDER BY dbo.SessionTimes.StartTime
";
//"SELECT SessionLevel_id,Username,title,description,approved,createdate,updatedate,AdminComments,InterentAccessRequired,LectureRoomsId,SessionTimesId,id FROM [dbo].[Sessions] ";
var cmd = new SqlCommand(sqlSelectString, conn);
cmd.Parameters.Add("@CodeCampYearId", SqlDbType.Int, 4).Value = Utils.GetCurrentCodeCampYear();
cmd.Parameters.Add("@trackid", SqlDbType.Int).Value = trackId;
reader = cmd.ExecuteReader();
try
{
while (reader.Read())
{
int sessionlevel_id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
string username = reader.IsDBNull(1) ? "" : reader.GetString(1);
string title = reader.IsDBNull(2) ? "" : reader.GetString(2);
string description = reader.IsDBNull(3) ? "" : reader.GetString(3);
bool approved = reader.IsDBNull(4) ? false : reader.GetBoolean(4);
DateTime createdate = reader.IsDBNull(5) ? DateTime.Now : reader.GetDateTime(5);
DateTime updatedate = reader.IsDBNull(6) ? DateTime.Now : reader.GetDateTime(6);
string admincomments = reader.IsDBNull(7) ? "" : reader.GetString(7);
bool interentaccessrequired = reader.IsDBNull(8) ? false : reader.GetBoolean(8);
int lectureroomsid = reader.IsDBNull(9) ? 0 : reader.GetInt32(9);
int sessiontimesid = reader.IsDBNull(10) ? 0 : reader.GetInt32(10);
int id = reader.IsDBNull(11) ? 0 : reader.GetInt32(11);
var td = new DataObjectSessions(sessionlevel_id, username, title, description, approved,
createdate, updatedate, admincomments, interentaccessrequired,
lectureroomsid, sessiontimesid, id);
DataTemplateODSList.Add(td);
}
}
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<DataObjectSessions>)HttpContext.Current.Cache[cacheName];
}
return DataTemplateODSList;
}
public List<DataObjectSessions> GetByPrimaryKeySessions(int searchid)
{
var conn = new SqlConnection(connectionString);
conn.Open();
var DataTemplateODSList = new List<DataObjectSessions>();
SqlDataReader reader = null;
string sqlSelectString =
"SELECT SessionLevel_id,Username,title,description,approved,createdate,updatedate,AdminComments,InterentAccessRequired,LectureRoomsId,SessionTimesId,DoNotShowPrimarySpeaker,SponsorId,id FROM [dbo].[Sessions] WHERE CodeCampYearId=@CodeCampYearId AND id = @searchid ";
var cmd = new SqlCommand(sqlSelectString, conn);
cmd.Parameters.Add("@searchid", SqlDbType.Int, 4).Value = searchid;
cmd.Parameters.Add("@CodeCampYearId", SqlDbType.Int, 4).Value = Utils.GetCurrentCodeCampYear();
reader = cmd.ExecuteReader();
try
{
while (reader.Read())
{
int sessionlevel_id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
string username = reader.IsDBNull(1) ? "" : reader.GetString(1);
string title = reader.IsDBNull(2) ? "" : reader.GetString(2);
string description = reader.IsDBNull(3) ? "" : reader.GetString(3);
bool approved = reader.IsDBNull(4) ? false : reader.GetBoolean(4);
DateTime createdate = reader.IsDBNull(5) ? DateTime.Now : reader.GetDateTime(5);
DateTime updatedate = reader.IsDBNull(6) ? DateTime.Now : reader.GetDateTime(6);
string admincomments = reader.IsDBNull(7) ? "" : reader.GetString(7);
bool interentaccessrequired = reader.IsDBNull(8) ? false : reader.GetBoolean(8);
int lectureroomsid = reader.IsDBNull(9) ? 0 : reader.GetInt32(9);
int sessiontimesid = reader.IsDBNull(10) ? 0 : reader.GetInt32(10);
bool doNotShowPrimarySpeaker = reader.IsDBNull(11) ? false : reader.GetBoolean(11);
int sponsorId = reader.IsDBNull(12) ? 0 : reader.GetInt32(12);
int id = reader.IsDBNull(13) ? 0 : reader.GetInt32(13);
var td = new DataObjectSessions(sessionlevel_id, username, title, description, approved, createdate,
updatedate, admincomments, interentaccessrequired, lectureroomsid,
sessiontimesid, doNotShowPrimarySpeaker, id) {SponsorId = sponsorId};
DataTemplateODSList.Add(td);
}
}
finally
{
if (reader != null) reader.Close();
}
conn.Close();
return DataTemplateODSList;
}
public List<DataObjectSessions> GetByPKID(Guid userGuid)
{
int currentCodeCampYearId = Utils.GetCurrentCodeCampYear();
List<DataObjectSessions> DataTemplateODSList = null;
string cacheName = String.Format("{0}_{1}-{2}", Utils.CacheSessionsByUserGuid, userGuid.ToString(), currentCodeCampYearId);
object o = HttpContext.Current.Cache[cacheName];
if (o == null)
{
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
DataTemplateODSList = new List<DataObjectSessions>();
// const string sqlSelectString = @"
// SELECT SessionLevel_id,
// Username,
// title,
// description,
// approved,
// createdate,
// updatedate,
// AdminComments,
// InterentAccessRequired,
// LectureRoomsId,
// SessionTimesId,
// id
// FROM Sessions
// WHERE CodeCampYearId=@CodeCampYearId AND (Username IN (SELECT Username FROM Attendees WHERE (PKID = @PKID)))
// ORDER BY Username";
const string sqlSelectString = @"
SELECT SessionLevel_id,
Username,
title,
description,
approved,
createdate,
updatedate,
AdminComments,
InterentAccessRequired,
LectureRoomsId,
SessionTimesId,
id
FROM Sessions
WHERE CodeCampYearId = @CodeCampYearId AND
Id IN (
SELECT SessionId
FROM SessionPresenter
WHERE AttendeeId =
(
select Id
from Attendees
WHERE PKID = @PKID
)
)
ORDER BY Username";
//"SELECT SessionLevel_id,Username,title,description,approved,createdate,
//updatedate,AdminComments,InterentAccessRequired,LectureRoomsId,
//SessionTimesId,id FROM [dbo].[Sessions] WHERE id = @searchid";
using (var cmd = new SqlCommand(sqlSelectString, conn))
{
cmd.Parameters.Add("@PKID", SqlDbType.UniqueIdentifier).Value = userGuid;
cmd.Parameters.Add("@CodeCampYearId", SqlDbType.Int, 4).Value = Utils.GetCurrentCodeCampYear();
using (SqlDataReader reader = cmd.ExecuteReader())
{
try
{
while (reader.Read())
{
int sessionlevel_id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
string username = reader.IsDBNull(1) ? "" : reader.GetString(1);
string title = reader.IsDBNull(2) ? "" : reader.GetString(2);
string description = reader.IsDBNull(3) ? "" : reader.GetString(3);
bool approved = reader.IsDBNull(4) ? false : reader.GetBoolean(4);
DateTime createdate = reader.IsDBNull(5) ? DateTime.Now : reader.GetDateTime(5);
DateTime updatedate = reader.IsDBNull(6) ? DateTime.Now : reader.GetDateTime(6);
string admincomments = reader.IsDBNull(7) ? "" : reader.GetString(7);
bool interentaccessrequired = reader.IsDBNull(8) ? false : reader.GetBoolean(8);
int lectureroomsid = reader.IsDBNull(9) ? 0 : reader.GetInt32(9);
int sessiontimesid = reader.IsDBNull(10) ? 0 : reader.GetInt32(10);
int id = reader.IsDBNull(11) ? 0 : reader.GetInt32(11);
var td = new DataObjectSessions(sessionlevel_id, username, title, description, approved, createdate, updatedate, admincomments, interentaccessrequired, lectureroomsid, sessiontimesid, id);
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<DataObjectSessions>) HttpContext.Current.Cache[cacheName];
}
return DataTemplateODSList;
}
public List<DataObjectSessions> GetAllSessionsByUsername(string usernameForSessions)
{
List<DataObjectSessions> DataTemplateODSList = null;
int currentCodeCampYearId = Utils.GetCurrentCodeCampYear();
string cacheName = String.Format("{0}-{1}-{2}", Utils.CacheSessionsByUsername, usernameForSessions, currentCodeCampYearId);
if (HttpContext.Current.Cache[cacheName] == null)
{
var conn = new SqlConnection(connectionString);
conn.Open();
DataTemplateODSList = new List<DataObjectSessions>();
SqlDataReader reader = null;
string sqlSelectString =
@"
SELECT SessionLevel_id,
Username,
title,
description,
approved,
createdate,
updatedate,
AdminComments,
InterentAccessRequired,
LectureRoomsId,
SessionTimesId,
id
FROM Sessions
WHERE CodeCampYearId=@CodeCampYearId AND (Username = @Username)
Order By title";
//"SELECT SessionLevel_id,Username,title,description,approved,createdate,
//updatedate,AdminComments,InterentAccessRequired,LectureRoomsId,
//SessionTimesId,id FROM [dbo].[Sessions] WHERE id = @searchid";
var cmd = new SqlCommand(sqlSelectString, conn);
cmd.Parameters.Add("@Username", SqlDbType.VarChar).Value = usernameForSessions;
cmd.Parameters.Add("@CodeCampYearId", SqlDbType.Int, 4).Value = Utils.GetCurrentCodeCampYear();
reader = cmd.ExecuteReader();
try
{
while (reader.Read())
{
int sessionlevel_id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
string username = reader.IsDBNull(1) ? "" : reader.GetString(1);
string title = reader.IsDBNull(2) ? "" : reader.GetString(2);
string description = reader.IsDBNull(3) ? "" : reader.GetString(3);
bool approved = reader.IsDBNull(4) ? false : reader.GetBoolean(4);
DateTime createdate = reader.IsDBNull(5) ? DateTime.Now : reader.GetDateTime(5);
DateTime updatedate = reader.IsDBNull(6) ? DateTime.Now : reader.GetDateTime(6);
string admincomments = reader.IsDBNull(7) ? "" : reader.GetString(7);
bool interentaccessrequired = reader.IsDBNull(8) ? false : reader.GetBoolean(8);
int lectureroomsid = reader.IsDBNull(9) ? 0 : reader.GetInt32(9);
int sessiontimesid = reader.IsDBNull(10) ? 0 : reader.GetInt32(10);
int id = reader.IsDBNull(11) ? 0 : reader.GetInt32(11);
var td = new DataObjectSessions(sessionlevel_id, username, title, description, approved,
createdate, updatedate, admincomments, interentaccessrequired,
lectureroomsid, sessiontimesid, id);
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<DataObjectSessions>) HttpContext.Current.Cache[cacheName];
}
return DataTemplateODSList;
}
public List<DataObjectSessions> GetAllSessionsBySessionId(int sessionId)
{
var sqlConnection = new SqlConnection(connectionString);
sqlConnection.Open();
var DataTemplateODSList = new List<DataObjectSessions>();
SqlDataReader reader = null;
string sqlSelectString =
@"
SELECT Sessions.SessionLevel_id,
Sessions.Username,
Sessions.Title,
Sessions.description,
Sessions.Approved,
Sessions.Createdate,
Sessions.Updatedate,
Sessions.AdminComments,
Sessions.InterentAccessRequired,
Sessions.LectureRoomsId,
Sessions.SessionTimesId,
Sessions.id
FROM Sessions
LEFT OUTER JOIN SessionTimes ON (Sessions.SessionTimesId = SessionTimes.Id)
WHERE Sessions.Id = @id
";
// @"
//SELECT SessionLevel_id,
// Username,
// title,
// sessions.description,
// approved,
// createdate,
// updatedate,
// AdminComments,
// InterentAccessRequired,
// LectureRoomsId,
// SessionTimesId,
// sessions.id
//FROM Sessions,
// SessionTimes
//WHERE (Sessions.SessionTimesId = sessiontimes.id) AND
// (Username IN (SELECT Username FROM Sessions, dbo.SessionTimes WHERE (
// sessions.id = @id)))
//ORDER BY StartTime";
//"SELECT SessionLevel_id,Username,title,description,approved,createdate,
//updatedate,AdminComments,InterentAccessRequired,LectureRoomsId,
//SessionTimesId,id FROM [dbo].[Sessions] WHERE id = @searchid";
var cmd = new SqlCommand(sqlSelectString, sqlConnection);
cmd.Parameters.Add("@id", SqlDbType.Int).Value = sessionId;
//cmd.Parameters.Add("@CodeCampYearId", SqlDbType.Int, 4).Value = Utils.GetCurrentCodeCampYear();
reader = cmd.ExecuteReader();
try
{
while (reader.Read())
{
int sessionlevel_id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
string username = reader.IsDBNull(1) ? "" : reader.GetString(1);
string title = reader.IsDBNull(2) ? "" : reader.GetString(2);
string description = reader.IsDBNull(3) ? "" : reader.GetString(3);
bool approved = reader.IsDBNull(4) ? false : reader.GetBoolean(4);
DateTime createdate = reader.IsDBNull(5) ? DateTime.Now : reader.GetDateTime(5);
DateTime updatedate = reader.IsDBNull(6) ? DateTime.Now : reader.GetDateTime(6);
string admincomments = reader.IsDBNull(7) ? "" : reader.GetString(7);
bool interentaccessrequired = reader.IsDBNull(8) ? false : reader.GetBoolean(8);
int lectureroomsid = reader.IsDBNull(9) ? 0 : reader.GetInt32(9);
int sessiontimesid = reader.IsDBNull(10) ? 0 : reader.GetInt32(10);
int id = reader.IsDBNull(11) ? 0 : reader.GetInt32(11);
var td = new DataObjectSessions(sessionlevel_id, username, title, description, approved, createdate,
updatedate, admincomments, interentaccessrequired, lectureroomsid,
sessiontimesid, id);
DataTemplateODSList.Add(td);
}
}
finally
{
if (reader != null) reader.Close();
}
sqlConnection.Close();
return DataTemplateODSList;
}
private List<DataObjectSessions> GetAllSessions(int sessionTimesIdExclude)
{
// no cache here because this is private and called by the other public methods
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
var DataTemplateODSList = new List<DataObjectSessions>();
SqlDataReader reader = null;
string sqlSelectString = @"SELECT
dbo.Sessions.SessionLevel_id,
dbo.Sessions.Username,
dbo.Sessions.title,
dbo.Sessions.description,
dbo.Sessions.approved,
dbo.Sessions.createdate,
dbo.Sessions.updatedate,
dbo.Sessions.AdminComments,
dbo.Sessions.InterentAccessRequired,
dbo.Sessions.LectureRoomsId,
dbo.Sessions.SessionTimesId,
dbo.Sessions.id
FROM
dbo.Sessions
INNER JOIN dbo.SessionTimes ON (dbo.Sessions.SessionTimesId = dbo.SessionTimes.id)
WHERE
dbo.Sessions.CodeCampYearId=@CodeCampYearId AND dbo.Sessions.SessionTimesId <> @TimeSessionIdExclude
ORDER BY
dbo.SessionTimes.StartTime";
//"SELECT SessionLevel_id,Username,title,description,approved,createdate,updatedate,AdminComments,InterentAccessRequired,LectureRoomsId,SessionTimesId,id FROM [dbo].[Sessions] ";
var cmd = new SqlCommand(sqlSelectString, conn);
cmd.Parameters.Add("@TimeSessionIdExclude", SqlDbType.Int).Value = sessionTimesIdExclude;
cmd.Parameters.Add("@CodeCampYearId", SqlDbType.Int, 4).Value = Utils.GetCurrentCodeCampYear();
reader = cmd.ExecuteReader();
try
{
while (reader.Read())
{
int sessionlevel_id = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
string username = reader.IsDBNull(1) ? "" : reader.GetString(1);
string title = reader.IsDBNull(2) ? "" : reader.GetString(2);
string description = reader.IsDBNull(3) ? "" : reader.GetString(3);
bool approved = reader.IsDBNull(4) ? false : reader.GetBoolean(4);
DateTime createdate = reader.IsDBNull(5) ? DateTime.Now : reader.GetDateTime(5);
DateTime updatedate = reader.IsDBNull(6) ? DateTime.Now : reader.GetDateTime(6);
string admincomments = reader.IsDBNull(7) ? "" : reader.GetString(7);
bool interentaccessrequired = reader.IsDBNull(8) ? false : reader.GetBoolean(8);
int lectureroomsid = reader.IsDBNull(9) ? 0 : reader.GetInt32(9);
int sessiontimesid = reader.IsDBNull(10) ? 0 : reader.GetInt32(10);
int id = reader.IsDBNull(11) ? 0 : reader.GetInt32(11);
var td = new DataObjectSessions(sessionlevel_id, username, title, description, approved, createdate, updatedate, admincomments, interentaccessrequired, lectureroomsid, sessiontimesid, id);
DataTemplateODSList.Add(td);
}
}
finally
{
if (reader != null)
reader.Close();
}
conn.Close();
return DataTemplateODSList;
}
}