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