//validate the user credentials and status should be Enable
public int LoginUserAuthentication(DAUsers dausr)
{
SqlCommand cmd = new SqlCommand("select count(*) from Login where Username=@Username and Password=@Password and Status=@Status", con);
con.Open();
cmd.Parameters.AddWithValue("@Username", dausr.Username);
cmd.Parameters.AddWithValue("@Password", dausr.Password);
cmd.Parameters.AddWithValue("@Status", "Enable");
int i = (int)cmd.ExecuteScalar();
con.Close();
return i;
}
public int UpdateUserDetails(DAUsers dausrs)
{
SqlCommand cmd = new SqlCommand("update Login set Name='" + dausrs.Name + "',Gender='" + dausrs.Gender + "',DateOfBirth='" + dausrs.DateOfBirth + "',Address='" + dausrs.Address + "',zipCode='" + dausrs.ZipCode + "',Phno='" + dausrs.Phno + "',email='" + dausrs.Email + "' where Username='******'", con);
con.Open();
int i = cmd.ExecuteNonQuery();
con.Close();
return i;
}
//if three user inputs are matched with users table and password is sent to user registerd email address.
private void SendPasswordtoEmail(DAUsers usrs)
{
string email = usrs.GetEmailId(usrs);
string HostAdd = ConfigurationManager.AppSettings["Host"].ToString(); //host name is gmail or yahoo or outlook
string FromMailId = ConfigurationManager.AppSettings["FromMail "].ToString(); //sender email addresss
string Password = ConfigurationManager.AppSettings["Password"].ToString(); //sender email password
MailMessage mail = new MailMessage();
mail.From = new MailAddress(FromMailId);
mail.Subject = " Your Login password in .Net Community Project"; //subject of email
mail.Body = "Hello " + usrs.Username + " <br/> Your Login password: "******" <br /> Click here go to Login Page <br />" + "http://localhost:1033/Login.aspx";
mail.IsBodyHtml = true;
mail.To.Add(new MailAddress(email));
SmtpClient smtp = new SmtpClient();
smtp.Host = HostAdd;
smtp.EnableSsl = true;
NetworkCredential networkcred = new NetworkCredential();
networkcred.UserName = mail.From.Address;
networkcred.Password = Password;
smtp.UseDefaultCredentials = true;
smtp.Credentials = networkcred;
smtp.Port = 587;
try
{
smtp.Send(mail);
lblmsg.Text = "your password is send to Registerd email";
lblmsg.ForeColor = Color.Green;
ClearControlsData(Page.Controls);
}
catch (Exception ex)
{
lblmsg.Text = "your password is not sent to registerd email address";
lblmsg.ForeColor = Color.Red;
ClearControlsData(Page.Controls);
}
}
//insert the user details in Login Table
public int insertUserDetails(DAUsers dausrs)
{
SqlCommand cmd = new SqlCommand("insert into Login(LoginId,Name,Phno,email,Username,Password,SecQuestion,Answer) values(@LoginId,@Name,@Phno,@email,@Username,@Password,@SecQuestion,@Answer)", con);
cmd.Parameters.AddWithValue("@LoginId", (int)dausrs.LoginId);
cmd.Parameters.AddWithValue("@Name", dausrs.Name);
cmd.Parameters.AddWithValue("@Phno", dausrs.Phno);
cmd.Parameters.AddWithValue("@email", dausrs.Email);
cmd.Parameters.AddWithValue("@Username", dausrs.Username);
cmd.Parameters.AddWithValue("@Password", dausrs.Password);
cmd.Parameters.AddWithValue("@SecQuestion", dausrs.SecQuestion);
cmd.Parameters.AddWithValue("@Answer", dausrs.Answer);
con.Open();
int i = cmd.ExecuteNonQuery();
con.Close();
return i;
}
//check UserName is exist or not and check secquestion and answer exits or not
public int checkUser(DAUsers dausr)
{
con.Open();
SqlCommand cmd = new SqlCommand("select count(*) from Login where Username=@Username and SecQuestion=@SecQuestion and Answer=@Answer ", con);
cmd.Parameters.AddWithValue("@Username", dausr.Username);
cmd.Parameters.AddWithValue("@SecQuestion", dausr.SecQuestion);
cmd.Parameters.AddWithValue("@Answer", dausr.Answer);
int countUserCheck = (int)cmd.ExecuteScalar();
con.Close();
return countUserCheck;
}
public int CheckedUserEmail(DAUsers dausr)
{
con.Open();
SqlCommand cmd = new SqlCommand("select count(*) from Login where email=@email", con);
cmd.Parameters.Add("@email",SqlDbType.NVarChar).Value=dausr.Email;
int StatusUserCheckEmail = (int)cmd.ExecuteScalar();
con.Close();
return StatusUserCheckEmail;
}
public int CheckedUserName(DAUsers dausr)
{
con.Open();
SqlCommand cmd = new SqlCommand("select count(*) from Login where Username=@Username", con);
cmd.Parameters.AddWithValue("@Username", dausr.Username);
int StatusUserCheckEmail = (int)cmd.ExecuteScalar();
con.Close();
return StatusUserCheckEmail;
}
public byte[] GetImage(DAUsers dausr)
{
SqlDataAdapter da = new SqlDataAdapter("select Photo from Login where Username='******'", con);
DataTable dt = new DataTable();
da.Fill(dt);
bool isAvailable = false;
if (dt.Rows[0]["Photo"]==DBNull.Value)
{
isAvailable = false;
}
else
{
isAvailable = true;
}
if(isAvailable)
{
photo = (byte[])dt.Rows[0]["Photo"];
}
return photo;
}
public int CheckedPhno(DAUsers dausr)
{
con.Open();
SqlCommand cmd = new SqlCommand("select count(*) from Login where Phno=@Phno", con);
cmd.Parameters.AddWithValue("@Phno", dausr.Phno);
int StatusUserCheckPhno= (int)cmd.ExecuteScalar();
con.Close();
return StatusUserCheckPhno;
}
public int UploadImage(DAUsers dausr)
{
con.Open();
SqlCommand cmd = new SqlCommand("update Login set Photo=@Photo where Username=@UserName", con);
cmd.Parameters.Add("@Photo",SqlDbType.Image).Value=dausr.photo;
cmd.Parameters.AddWithValue("@UserName",dausr.Username);
int counnt=cmd.ExecuteNonQuery();
con.Close();
return counnt;
}
public DataTable GetUserDetails(DAUsers dausr)
{
SqlDataAdapter da = new SqlDataAdapter("select Name,Gender,DateOfBirth,Address,zipCode,Phno,email,Username from Login where Username='******'", con);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
//retrive the imageapath from users table
public int GetLoginId(DAUsers dausr)
{
con.Open();
SqlCommand cmd = new SqlCommand("Select LoginId from Login where Username=@Username", con);
cmd.Parameters.AddWithValue("@Username", dausr.Username);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
LoginId = Convert.ToInt32(dr[0]);
con.Close();
return LoginId;
}
//Retrive email address from users table
public string GetEmailId(DAUsers dausr)
{
string EmailId = "";
con.Open();
SqlCommand cmd = new SqlCommand("Select email from Login where Username=@Username", con);
cmd.Parameters.AddWithValue("@Username", dausr.Username);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
EmailId = dr[0].ToString();
con.Close();
return EmailId;
}
//Retrive the password from table based on username,security question and answer.
public string GetPassword(DAUsers dausr)
{
SqlCommand cmd = new SqlCommand("Select Password from Login where Username=@Username and SecQuestion=@SecQuestion and Answer=@Answer", con);
con.Open();
cmd.Parameters.AddWithValue("@Username", dausr.Username);
cmd.Parameters.AddWithValue("SecQuestion", dausr.SecQuestion);
cmd.Parameters.AddWithValue("@Answer", Answer);
SqlDataReader dr = cmd.ExecuteReader();
string password = "";
if (dr.Read())
password = dr[0].ToString();
con.Close();
return password;
}
