public static HQUserItem GetUsersByLogin(string username)
{
HQUserItem item = new HQUserItem();
SqlConnection connection = new SqlConnection(ConnectionString);
connection.Open();
string selectQuery = "SELECT login,first_name,last_name,email,dob,mobile,gender,lang FROM users WHERE login=@username";
SqlCommand cmd = new SqlCommand(selectQuery, connection);
cmd.Parameters.AddWithValue("@username", username);
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
item.Username = reader["login"].ToString();
item.FirstName = reader["first_name"].ToString();
item.LastName = reader["last_name"].ToString();
item.Phone = reader["mobile"].ToString();
item.Email = reader["email"].ToString();
if (!reader.IsDBNull(reader.GetOrdinal("gender")))
{
item.Gender = Convert.ToInt32(reader["gender"]);
}
if (!reader.IsDBNull(reader.GetOrdinal("dob")))
{
item.Birthday = Convert.ToDateTime(reader["dob"]);
}
item.PreferedLanguage = reader["lang"].ToString().ToLower();
}
}
connection.Close();
return(item);
}
public static bool SetData(HQUserItem item)
{
try
{
SqlConnection connection = new SqlConnection(ConnectionString);
connection.Open();
string updateQuery = "UPDATE users SET first_name=@first_name, last_name=@last_name, email=@email, dob=@dob, mobile=@mobile, gender=@gender, lang=@lang where login=@login";
SqlCommand cmd = new SqlCommand(updateQuery, connection);
if (item.Username == "")
{
cmd.Parameters.AddWithValue("@login", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@login", item.Username);
}
if (item.FirstName == "")
{
cmd.Parameters.AddWithValue("@first_name", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@first_name", item.FirstName);
}
if (item.LastName == "")
{
cmd.Parameters.AddWithValue("@last_name", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@last_name", item.LastName);
}
if (item.Email == "")
{
cmd.Parameters.AddWithValue("@email", DBNull.Value);
}
else
{
if (CheckEmail(item.Email, item.Username))
{
}
else if (Regex.Match(item.Email, @"^(?("")("".+?(?<!\\)""@)|(([0-9a-z]((\.(?!\.))|[-!#\$%&'\*\+/=\?\^`\{\}\|~\w])*)(?<=[0-9a-z])@))" +
@"(?(\[)(\[(\d{1,3}\.){3}\d{1,3}\])|(([0-9a-z][-\w]*[0-9a-z]*\.)+[a-z0-9][\-a-z0-9]{0,22}[a-z0-9]))$").Success)
{
cmd.Parameters.AddWithValue("@email", item.Email);
}
}
if (item.Birthday == null)
{
cmd.Parameters.AddWithValue("@dob", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@dob", item.Birthday);
}
if (item.Phone == "")
{
cmd.Parameters.AddWithValue("@mobile", DBNull.Value);
}
else if (Regex.Match(item.Phone, @"^([0-9]{9,15})$").Success)
{
cmd.Parameters.AddWithValue("@mobile", item.Phone);
}
if (item.Gender == 0)
{
cmd.Parameters.AddWithValue("@gender", DBNull.Value);
}
else if (item.Gender != 0)
{
cmd.Parameters.AddWithValue("@gender", item.Gender);
}
if (item.PreferedLanguage == "")
{
cmd.Parameters.AddWithValue("@lang", DBNull.Value);
}
else
{
cmd.Parameters.AddWithValue("@lang", item.PreferedLanguage);
}
cmd.ExecuteNonQuery();
connection.Close();
return(true);
}
catch
{
return(false);
}
}