public bool UpdateUserConfirmRegistration(string login, bool isConfirmRegistration)
{
bool flag = false;
try
{
using (SqlConnection con =
new SqlConnection(WebConfigurationManager.ConnectionStrings["LoginDb"].ConnectionString))
{
SqlCommand cmd = new SqlCommand("UpdateUserConfirmRegistration", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@login", login);
cmd.Parameters.AddWithValue("@confirmRegistration", isConfirmRegistration);
con.Open();
if (cmd.ExecuteNonQuery() == 1)
{
flag = true;
}
}
}
catch (Exception e)
{
ErrorDAL.AddNewError(DateTime.Now, e.ToString(), "");
throw new Exception("Oшибка данных");
}
return(flag);
}
public void UserAuthenticationDB2(int userID, out string user)
{
user = null;
try
{
using (SqlConnection con =
new SqlConnection(WebConfigurationManager.ConnectionStrings["LoginDb"].ConnectionString))
{
SqlCommand cmd = new SqlCommand("GetUserAuth", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@userID", userID);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
user = userID + "; " +
dr["Login"].ToString() + "; " +
dr["UserName"].ToString() + "; " +
dr["Status"].ToString() + "; " +
dr["RoleName"].ToString().Trim();
}
}
}
catch (Exception e)
{
ErrorDAL.AddNewError(DateTime.Now, e.ToString(), "");
throw new Exception("Oшибка данных");
}
}
//Если есть логин или емейл в БД вернет true
public bool IsUserLoginEmail(string userLogin, string userEmail)
{
bool flag = false;
try
{
using (SqlConnection con =
new SqlConnection(WebConfigurationManager.ConnectionStrings["LoginDb"].ConnectionString))
{
SqlCommand cmd = new SqlCommand("IsUserLoginEmail", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@login", userLogin);
cmd.Parameters.AddWithValue("@email", userEmail);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if ((reader["Login"].ToString().Trim() == userLogin) || (reader["Email"].ToString().Trim() == userEmail))
{
flag = true;
}
}
}
}
catch (Exception e)
{
ErrorDAL.AddNewError(DateTime.Now, e.ToString(), "");
throw new Exception("Oшибка данных");
}
return(flag);
}
public bool AddNewUser(string login, string password, string userName, string email)
{
bool flag = false;
try
{
using (SqlConnection con =
new SqlConnection(WebConfigurationManager.ConnectionStrings["LoginDb"].ConnectionString))
{
SqlCommand cmd = new SqlCommand("AddNewUser", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@login", login);
cmd.Parameters.AddWithValue("@password", password);
cmd.Parameters.AddWithValue("@userName", userName);
cmd.Parameters.AddWithValue("@email", email);
cmd.Parameters.AddWithValue("@dataRegistration", DateTime.Now);
con.Open();
if (cmd.ExecuteNonQuery() == 1)
{
flag = true;
}
}
}
catch (Exception e)
{
ErrorDAL.AddNewError(DateTime.Now, e.ToString(), "");
throw new Exception("Oшибка записи пользовательских данных");
}
return(flag);
}
public bool AddNewMessage(int topicID, int userID, string textMess)
{
bool flag = false;
try
{
using (SqlConnection con =
new SqlConnection(WebConfigurationManager.ConnectionStrings["LoginDb"].ConnectionString))
{
SqlCommand cmd = new SqlCommand("AddNewMess", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@topID", topicID);
cmd.Parameters.AddWithValue("@userID", userID);
cmd.Parameters.AddWithValue("@addDate", DateTime.Now);
cmd.Parameters.AddWithValue("@text", textMess);
con.Open();
if (cmd.ExecuteNonQuery() == 1)
{
flag = true;
}
}
}
catch (Exception e)
{
ErrorDAL.AddNewError(DateTime.Now, e.ToString(), "");
throw new Exception("Oшибка данных");
}
return(flag);
}
//public bool GetUserName(string userlogin, out string userName)
//{
// userName = string.Empty;
// bool flag = false;
// try
// {
// using (SqlConnection con =
// new SqlConnection(WebConfigurationManager.ConnectionStrings["LoginDb"].ConnectionString))
// {
// SqlCommand cmd = new SqlCommand("GetUserName", con);
// cmd.CommandType = System.Data.CommandType.StoredProcedure;
// cmd.Parameters.AddWithValue("@usr", userlogin);
// con.Open();
// SqlDataReader dr = cmd.ExecuteReader();
// userlogin = "";
// while (dr.Read())
// {
// userName = dr["UserName"].ToString();
// flag = true;
// }
// }
// }
// catch (Exception e)
// {
// string text = " Message: " + e.Message +
// " StackTrace: " + e.StackTrace;
// ErrorDAL.AddNewError(DateTime.Now, text, "");
// throw new Exception("Oшибка данных");
// }
// return flag;
//}
/*
* /// <summary>
* /// Если существует пользователь с таким логином, вернет true
* /// </summary>
* /// <returns></returns>
* public bool IsUserLogin(string userlogin)
* {
* bool flag = false;
* try
* {
* using (SqlConnection con =
* new SqlConnection(WebConfigurationManager.ConnectionStrings["LoginDb"].ConnectionString))
* {
*
* SqlCommand cmd = new SqlCommand("IsUserLogin", con);
* cmd.CommandType = System.Data.CommandType.StoredProcedure;
* cmd.Parameters.AddWithValue("@login", userlogin);
*
* con.Open();
* SqlDataReader dr = cmd.ExecuteReader();
*
* if (dr.HasRows)
* flag = true;
* }
* }
* catch (Exception e)
* {
* string text = " Message: " + e.Message +
* " StackTrace: " + e.StackTrace;
* ErrorDAL.AddNewError(DateTime.Now, text, "");
*
* throw new Exception("Oшибка данных");
* }
*
* return flag;
* }
*/
//IsUserLogin
public string GetUserPass(string userlogin)
{
string password = string.Empty;
try
{
using (SqlConnection con =
new SqlConnection(WebConfigurationManager.ConnectionStrings["LoginDb"].ConnectionString))
{
SqlCommand cmd = new SqlCommand("GetUserPass", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@login", userlogin);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
password = reader["Password"].ToString();
}
}
}
catch (Exception e)
{
ErrorDAL.AddNewError(DateTime.Now, e.ToString(), "");
throw new Exception("Oшибка данных");
}
return(password);
}
internal static bool SendEmail(string toMail, string subject, string textMassage)
{
bool flag = false;
try
{
using (MailMessage mm = new MailMessage())
{
mm.Subject = subject;
mm.Body = textMassage;
mm.IsBodyHtml = true;
mm.From = new System.Net.Mail.MailAddress(senderEmail); //мейл отправителя
mm.To.Add(new MailAddress(toMail)); //мейл получателя
using (SmtpClient sc = new SmtpClient(smtp, port))
{
//sc.EnableSsl = true; // включение SSL
sc.DeliveryMethod = SmtpDeliveryMethod.Network;
sc.UseDefaultCredentials = false;
sc.Timeout = 3000;
sc.Credentials = new NetworkCredential(senderEmail, senderPasword);
sc.Send(mm);
flag = true;
}
}
}
catch (Exception e)
{
ErrorDAL.AddNewError(DateTime.Now, e.ToString(), "");
throw new Exception("Oшибка отправки сообщения");
}
return(flag);
}
public string GetTopicName(int topicID)
{
string topicName = "";
try
{
using (SqlConnection con =
new SqlConnection(WebConfigurationManager.ConnectionStrings["LoginDb"].ConnectionString))
{
SqlCommand cmd = new SqlCommand("GetTopicName", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@topicID", topicID);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
topicName = reader["TopicName"].ToString();
}
}
}
catch (Exception e)
{
ErrorDAL.AddNewError(DateTime.Now, e.ToString(), "");
throw new Exception("Oшибка данных");
}
return(topicName);
}
public void DeleteTopic(int topID)
{
try
{
using (SqlConnection con =
new SqlConnection(WebConfigurationManager.ConnectionStrings["LoginDb"].ConnectionString))
{
SqlCommand cmd = new SqlCommand("DeleteTopic", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@topicID", topID);
con.Open();
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
ErrorDAL.AddNewError(DateTime.Now, e.ToString(), "");
throw new Exception("Oшибка данных");
}
}
public int AddNewTopic(int userID, string text, int sectionID)
{
int resultTopicID = 0;
DateTime dtime = new DateTime();
dtime = DateTime.Now;
try
{
using (SqlConnection con =
new SqlConnection(WebConfigurationManager.ConnectionStrings["LoginDb"].ConnectionString))
{
SqlCommand cmd = new SqlCommand("AddNewTopic", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@sectionID", sectionID);
cmd.Parameters.AddWithValue("@userID", userID);
cmd.Parameters.AddWithValue("@name", text);
cmd.Parameters.AddWithValue("@addDate", dtime);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
if (reader["TopicID"] != DBNull.Value)
{
resultTopicID = Convert.ToInt32(reader["TopicID"]);
}
}
}
}
catch (Exception e)
{
ErrorDAL.AddNewError(DateTime.Now, e.ToString(), "");
throw new Exception("Oшибка данных");
}
return(resultTopicID);
}
public List <Entities.Message> GetMessagesByTopic(int topicID, DateTime startDate)
{
List <Entities.Message> listMessages = new List <Entities.Message>();
try
{
using (SqlConnection con =
new SqlConnection(WebConfigurationManager.ConnectionStrings["LoginDb"].ConnectionString))
{
SqlCommand cmd = new SqlCommand("GetMessagesbyTopAndDate", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@topicID", topicID);
cmd.Parameters.AddWithValue("@data", startDate);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Entities.Message mess = new Entities.Message(
Convert.ToInt32(reader["ID"]),
Convert.ToInt32(reader["TopicID"]),
reader["TopicName"].ToString(),
Convert.ToInt32(reader["UserID"]),
reader["UserLogin"].ToString(),
(DateTime)reader["AddDate"],
reader["Text"].ToString()
);
listMessages.Add(mess);
}
}
}
catch (Exception e)
{
ErrorDAL.AddNewError(DateTime.Now, e.ToString(), "");
throw new Exception("Oшибка данных");
}
return(listMessages);
}
public Entities.User UserAuthenticationDB(string userlogin, string userPassword)
{
Entities.User user = null;
try
{
using (SqlConnection con =
new SqlConnection(WebConfigurationManager.ConnectionStrings["LoginDb"].ConnectionString))
{
SqlCommand cmd = new SqlCommand("GetUserInfo", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@usr", userlogin);
cmd.Parameters.AddWithValue("@pwd", userPassword);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
user = new Entities.User(
Convert.ToInt32(dr["ID"]),
userlogin,
userPassword,
dr["UserName"].ToString(),
dr["Email"].ToString(),
dr["Status"].ToString(),
dr["RoleName"].ToString().Trim(),
Convert.ToBoolean(dr["ConfirmRegistration"])
);
}
}
}
catch (Exception e)
{
ErrorDAL.AddNewError(DateTime.Now, e.ToString(), "");
throw new Exception("Oшибка данных");
}
return(user);
}
public List <Entities.Topic> GetTopics(int sectionID)
{
List <Entities.Topic> listTopic = new List <Entities.Topic>();
try
{
using (SqlConnection con =
new SqlConnection(WebConfigurationManager.ConnectionStrings["LoginDb"].ConnectionString))
{
SqlCommand cmd = new SqlCommand("GetTopics", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("sectionID", sectionID);
con.Open();
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
//проверка входных данных на DBNull.Value
int?idLastMess = null;
if (reader["IDLastMess"] != DBNull.Value)
{
idLastMess = Convert.ToInt32(reader["IDLastMess"]);
}
int?userIDLastMess = null;
if (reader["IDLastMess"] != DBNull.Value)
{
userIDLastMess = Convert.ToInt32(reader["IDLastMess"]);
}
DateTime?dataLastMess = null;
if (reader["DataLastMess"] != DBNull.Value)
{
dataLastMess = (DateTime)reader["DataLastMess"];
}
Entities.Topic topic = new Entities.Topic(
Convert.ToInt32(reader["ID"]),
reader["Name"].ToString(),
Convert.ToInt32(reader["UserID"]),
reader["Login"].ToString(),
(DateTime)reader["AddDate"],
(reader["countReply"] != DBNull.Value) ? Convert.ToInt32(reader["countReply"]) : 0,
idLastMess, //(reader["IDLastMess"] != DBNull.Value)?Convert.ToInt32(reader["IDLastMess"]):null,
userIDLastMess,
(reader["UserLoginLastMess"] != DBNull.Value) ? reader["UserLoginLastMess"].ToString() : "",
dataLastMess
);
listTopic.Add(topic);
}
}
}
catch (Exception e)
{
ErrorDAL.AddNewError(DateTime.Now, e.ToString(), "");
throw new Exception("Oшибка данных");
}
return(listTopic);
}
