public static List <Discussion> FetchDiscussion(string Jobno, int OffSetMinutes)
{
String connstring = Connection.GetConnectionString();
List <Discussion> D = new List <Discussion>();
using (SqlConnection dbCon = new SqlConnection(connstring))
{
dbCon.Open();
using (SqlCommand dbCom = new SqlCommand(StoredProcedure.USP_FETCHDISSCUSSIONTEXT, dbCon))
{
dbCom.CommandType = CommandType.StoredProcedure;
dbCom.Parameters.AddWithValue("@JOBNO", Jobno);
using (SqlDataReader wizReader = dbCom.ExecuteReader())
{
while (wizReader.Read())
{
Discussion Chat = new Discussion();
Chat.ID = (Int32)wizReader["ID"];
Chat.ChatID = (Int32)wizReader["CHATID"];
Chat.UserName = (String)wizReader["USERNAME"];
Chat.JobNo = (Int32)wizReader["JOBNO"];
Chat.Messages = (String)wizReader["MESSAGES"];
Chat.DisplayName = Convert.ToString(wizReader["DisplayName"]);
Chat.ImgPath = Convert.ToString(wizReader["IMGPATH"]);
Chat.MessageDateTime = Convert.ToDateTime(wizReader["MSGDATETIME"]).AddMinutes(-OffSetMinutes);//- beacuse offset return utc-localtime
Chat.MyMsg = false;
if (HttpContext.Current.User.Identity != null)
{
if (Chat.UserName == System.Web.HttpContext.Current.User.Identity.Name.ToString())
{
Chat.MyMsg = true;
}
}
if (string.IsNullOrEmpty(Chat.ImgPath))
{
Chat.ImgPath = "/images/CandidateImages/NoImage.png";
}
else
{
Chat.ImgPath = "/images/CandidateImages/" + Chat.ImgPath;
}
D.Add(Chat);
}
}
}
}
return(D);
}
public bool AddNewDiscussion(Discussion course)
{
try
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@Name", course.name),
new SqlParameter("@courseID", course.courseID),
new SqlParameter("@post", course.post),
new SqlParameter("@email", course.email),
};
return(SqlDBHelper.ExecuteNonQuery("AddNewDiscussion", CommandType.StoredProcedure, parameters));
}
catch
{
throw;
}
}
public static List <Discussion> DiscussionAdmin()
{
String connstring = Connection.GetConnectionString();
List <Discussion> DA = new List <Discussion>();
//String Sql_GetDiscussionAdmin = String.Format("SELECT ID,CHATID,USERNAME,JOBNO,MESSAGES,MSGDATETIME FROM DISCUSSION ORDER BY MSGDATETIME DESC");
String Sql_GetDiscussionAdmin = String.Format("SELECT DISCUSSION.ID, CHATID,DISCUSSION.USERNAME,JOBNO,MESSAGES,MSGDATETIME,users.PERSONID,ISNULL(NAME,'anonymous') DisplayName,IMGPATH FROM DISCUSSION left join Users on Discussion.USERNAME=Users.USERNAME left join person on Person.PERSONID=Users.PERSONID ORDER BY MSGDATETIME DESC");
using (SqlConnection dbCon = new SqlConnection(connstring))
{
dbCon.Open();
using (SqlCommand dbCom = new SqlCommand(Sql_GetDiscussionAdmin, dbCon))
{
dbCom.CommandType = CommandType.Text;
using (SqlDataReader wizReader = dbCom.ExecuteReader())
{
while (wizReader.Read())
{
Discussion Chat = new Discussion();
Chat.ID = Convert.ToInt32(wizReader["ID"]);
Chat.ChatID = Convert.ToInt32(wizReader["CHATID"]);
Chat.UserName = Convert.ToString(wizReader["USERNAME"]);
Chat.DisplayName = Convert.ToString(wizReader["DISPLAYNAME"]);
Chat.JobNo = Convert.ToInt32(wizReader["JOBNO"]);
Chat.Messages = Convert.ToString(wizReader["MESSAGES"]);
Chat.MessageDateTime = Convert.ToDateTime(wizReader["MSGDATETIME"]);
Chat.ImgPath = Convert.ToString(wizReader["IMGPATH"]);
DA.Add(Chat);
if (String.IsNullOrEmpty(Chat.ImgPath))
{
Chat.ImgPath = "NoImage.png";
}
}
}
}
}
return(DA);
}
public List <Discussion> GetDiscussion(int NId)
{
SqlParameter[] parameters = new SqlParameter[]
{
new SqlParameter("@courseID", NId)
};
List <Discussion> listDiscussion = null;
//Lets get the list of all employees in a datataable
using (DataTable table1 = SqlDBHelper.ExecuteParamerizedSelectCommand("GetDiscussion", CommandType.StoredProcedure, parameters))
{
//check if any record exist or not
if (table1.Rows.Count > 0)
{
//Lets go ahead and create the list of employees
listDiscussion = new List <Discussion>();
//Now lets populate the employee details into the list of employees
foreach (DataRow row in table1.Rows)
{
Discussion course = new Discussion();
course.name = row["Name"].ToString();
course.courseID = Convert.ToInt32(row["courseID"]);
course.post = row["post"].ToString();
course.email = row["email"].ToString();
listDiscussion.Add(course);
}
}
}
return(listDiscussion);
}