public static void SendRegistrationMail(UserLogin confirmObj, string pass)
{
var fromaddr = "*****@*****.**";
var toaddr = confirmObj.EMail;
const string fromPassword = "******";
string subject = "Confirmation of your apartment registration for energy dashboard";
string body = "Congratulations! We have confirmed your apartment registration for our energy dashboard." + "\n";
body += "Username: "******"\n";
body += "Password: "******"\n";
body += "Login @ http://energy.iiitd.edu.in";
//body += "Email: " + "*****@*****.**" + "\n";
//body += "Subject: " + "Confirmation of your apartment registration for energy dashboard";
var smtp = new System.Net.Mail.SmtpClient();
{
smtp.Host = "smtp.gmail.com";
smtp.Port = 587;
smtp.EnableSsl = true;
smtp.DeliveryMethod = System.Net.Mail.SmtpDeliveryMethod.Network;
smtp.Credentials = new NetworkCredential(fromaddr, fromPassword);
smtp.Timeout = 20000;
}
smtp.Send(fromaddr, toaddr, subject, body);
}
public static bool UpdateProfile(UserLogin newPwdUser)
{
try
{
using (DbConnection conn = provider.CreateConnection())
{
conn.ConnectionString = connString;
conn.Open();
using (DbCommand cmd = conn.CreateCommand())
{
string sqlQuery;
sqlQuery = "UPDATE login_Info " +
"SET FullName = @fullnames, mobile=@mobiles, email=@emails WHERE userID = @userId";
if (parmPrefix != "@")
{
sqlQuery = sqlQuery.Replace("@", parmPrefix);
}
cmd.CommandText = sqlQuery;
cmd.CommandType = CommandType.Text;
DbParameter sUserId = provider.CreateParameter();
sUserId.ParameterName = parmPrefix + "userId";
sUserId.Value = newPwdUser.UserId;
cmd.Parameters.Add(sUserId);
DbParameter sname = provider.CreateParameter();
sname.ParameterName = parmPrefix + "fullnames";
sname.Value = newPwdUser.FullName;
cmd.Parameters.Add(sname);
DbParameter sMobile = provider.CreateParameter();
sMobile.ParameterName = parmPrefix + "mobiles";
sMobile.Value = newPwdUser.Mobile;
cmd.Parameters.Add(sMobile);
DbParameter sEmail = provider.CreateParameter();
sEmail.ParameterName = parmPrefix + "emails";
sEmail.Value = newPwdUser.EMail;
cmd.Parameters.Add(sEmail);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
conn.Close();
}
return true;
}
catch (Exception exp)
{
return false;
}
}
public static UserLogin SeeUserInfo(string UserName)
{
UserLogin userDetail = new UserLogin();
try
{
using (DbConnection conn = provider.CreateConnection())
{
conn.ConnectionString = connString;
using (DbCommand cmd = conn.CreateCommand())
{
string sqlQuery = "SELECT UserID,UserName,FullName,apartment,mobile,building" +
" FROM Login_Info WHERE UserName = @UserName";
if (parmPrefix != "@")
{
sqlQuery = sqlQuery.Replace("@", parmPrefix);
}
cmd.CommandText = sqlQuery;
cmd.CommandType = CommandType.Text;
DbParameter dpID = provider.CreateParameter();
dpID.ParameterName = parmPrefix + "UserName";
dpID.Value = UserName;
cmd.Parameters.Add(dpID);
conn.Open();
using (DbDataReader rdr = cmd.ExecuteReader())
{
if (rdr.HasRows)
{
while (rdr.Read())
{
userDetail = new UserLogin();
if (!rdr.IsDBNull(0))
{
userDetail.UserId = rdr.GetGuid(0);
}
if (!rdr.IsDBNull(1))
{
userDetail.UserName = rdr.GetString(1);
}
if (!rdr.IsDBNull(2))
{
userDetail.FullName = rdr.GetString(2);
}
if (!rdr.IsDBNull(3))
{
userDetail.Apartment = rdr.GetString(3);
}
if (!rdr.IsDBNull(4))
{
userDetail.Mobile = rdr.GetString(4);
}
if (!rdr.IsDBNull(5))
{
userDetail.Building = rdr.GetString(5);
}
}
}
else
{
return null;
}
}
}
conn.Close();
}
}
catch (Exception exp)
{
return null;
}
return userDetail;
}
public static bool ResetUsername(UserLogin newPwdUser)
{
try
{
using (DbConnection conn = provider.CreateConnection())
{
conn.ConnectionString = connString;
conn.Open();
using (DbCommand cmd = conn.CreateCommand())
{
string sqlQuery;
sqlQuery = "UPDATE login_Info " +
"SET UserName = @usrname WHERE userID = @userId";
if (parmPrefix != "@")
{
sqlQuery = sqlQuery.Replace("@", parmPrefix);
}
cmd.CommandText = sqlQuery;
cmd.CommandType = CommandType.Text;
DbParameter sUserId = provider.CreateParameter();
sUserId.ParameterName = parmPrefix + "userId";
sUserId.Value = newPwdUser.UserId;
cmd.Parameters.Add(sUserId);
DbParameter sUsername = provider.CreateParameter();
sUsername.ParameterName = parmPrefix + "usrname";
sUsername.Value = newPwdUser.UserName;
cmd.Parameters.Add(sUsername);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
conn.Close();
}
return true;
}
catch (Exception exp)
{
return false;
}
}
public static List<UserLogin> ListOfAllUsers()
{
List<UserLogin> AllUsers = new List<UserLogin>();
try
{
using (DbConnection conn = provider.CreateConnection())
{
conn.ConnectionString = connString;
using (DbCommand cmd = conn.CreateCommand())
{
string sqlQuery = "SELECT UserID,UserName,Password,FullName,Apartment,Building" +
" FROM Login_Info";
if (parmPrefix != "@")
{
sqlQuery = sqlQuery.Replace("@", parmPrefix);
}
cmd.CommandText = sqlQuery;
cmd.CommandType = CommandType.Text;
conn.Open();
using (DbDataReader rdr = cmd.ExecuteReader())
{
if (rdr.HasRows)
{
while (rdr.Read())
{
UserLogin userDetail = new UserLogin();
if (!rdr.IsDBNull(0))
{
userDetail.UserId = rdr.GetGuid(0);
}
if (!rdr.IsDBNull(1))
{
userDetail.UserName = rdr.GetString(1);
}
if (!rdr.IsDBNull(2))
{
userDetail.Password = rdr.GetString(2);
}
if (!rdr.IsDBNull(3))
{
userDetail.FullName = rdr.GetString(3);
}
if (!rdr.IsDBNull(4))
{
userDetail.Apartment = rdr.GetString(4);
}
if (!rdr.IsDBNull(5))
{
userDetail.Building = rdr.GetString(5);
}
AllUsers.Add(userDetail);
}
}
else
{
return null;
}
}
conn.Close();
return AllUsers;
}
}
}
catch (Exception exp)
{
return null;
}
}
public static bool InsertUser(UserLogin insertUser)
{
try
{
using (DbConnection conn = provider.CreateConnection())
{
conn.ConnectionString = connString;
conn.Open();
using (DbCommand cmd = conn.CreateCommand())
{
string sqlQuery;
sqlQuery = "INSERT INTO Login_Info" +
"(UserID,UserName,Password,FullName,mobile,apartment, building, email) " +
"VALUES(@id,@userName,@password,@fullName,@mobile,@apartment,@building, @email)";
if (parmPrefix != "@")
{
sqlQuery = sqlQuery.Replace("@", parmPrefix);
}
cmd.CommandText = sqlQuery;
cmd.CommandType = CommandType.Text;
DbParameter sNewId = provider.CreateParameter();
sNewId.ParameterName = parmPrefix + "id";
sNewId.Value = insertUser.UserId;
cmd.Parameters.Add(sNewId);
DbParameter sUserName = provider.CreateParameter();
sUserName.ParameterName = parmPrefix + "userName";
sUserName.Value = insertUser.UserName;
cmd.Parameters.Add(sUserName);
DbParameter sPassword = provider.CreateParameter();
sPassword.ParameterName = parmPrefix + "password";
sPassword.Value = insertUser.Password;
cmd.Parameters.Add(sPassword);
DbParameter sFullName = provider.CreateParameter();
sFullName.ParameterName = parmPrefix + "fullName";
sFullName.Value = insertUser.FullName;
cmd.Parameters.Add(sFullName);
DbParameter sMobile = provider.CreateParameter();
sMobile.ParameterName = parmPrefix + "mobile";
sMobile.Value = insertUser.Mobile;
cmd.Parameters.Add(sMobile);
DbParameter sAddress = provider.CreateParameter();
sAddress.ParameterName = parmPrefix + "apartment";
sAddress.Value = insertUser.Apartment;
cmd.Parameters.Add(sAddress);
DbParameter sBuilding = provider.CreateParameter();
sBuilding.ParameterName = parmPrefix + "building";
sBuilding.Value = insertUser.Building;
cmd.Parameters.Add(sBuilding);
DbParameter sEmail = provider.CreateParameter();
sEmail.ParameterName = parmPrefix + "email";
sEmail.Value = insertUser.EMail;
cmd.Parameters.Add(sEmail);
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
conn.Close();
}
return true;
}
catch (Exception exp)
{
return false;
}
}