public static int CheckLoginDB(Login login)
{
using (SqlConnection conn = UtilityDB.ConnectDB())
{
try
{
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = conn;
sqlCommand.CommandText = String.Format("Select Count(*) from users where UserName = '******' and Password = '******'",
login.UserName, login.Password);
int count = Convert.ToInt32(sqlCommand.ExecuteScalar());
//todo Return First Column and First Row other columns and rows are ignored.
if (count != 0)
{
sqlCommand.CommandText = String.Format("Select RoleID from users where UserName = '******' and Password = '******'",
login.UserName, login.Password);
int roleID = Convert.ToInt32(sqlCommand.ExecuteScalar());
return(roleID);
}
return(-1);
}
catch (Exception)
{
return(-1);
}
}
}
public static DataTable ExecuteReaderQuery(string queryText)
{
DataTable dataTable = new DataTable();
if (string.IsNullOrEmpty(queryText))
{
return(dataTable);
}
try
{
using (SqlConnection conn = UtilityDB.ConnectDB())
{
//SqlCommand sqlCommand = new SqlCommand();
//sqlCommand.Connection = conn;
//sqlCommand.CommandText = queryText;
SqlCommand sqlCommand = new SqlCommand
{
Connection = conn,
CommandText = queryText
};
SqlDataReader dataReader = sqlCommand.ExecuteReader();
dataTable.Load(dataReader);
return(dataTable);
}
}
catch (Exception)
{
return(dataTable);
}
}
private static bool SqlNonQuery(string queryText)
{
try
{
using (SqlConnection conn = UtilityDB.ConnectDB())
{
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = conn;
sqlCommand.CommandText = queryText;
sqlCommand.ExecuteNonQuery();
return(true);
}
}
catch (Exception ex)
{
return(false);
}
}
public static int CheckUser(User user)
{
if (connDB.State == ConnectionState.Closed)
{
connDB = UtilityDB.ConnectDB();
cmd = new SqlCommand();
}
cmd.Connection = connDB;
cmd.CommandText = string.Format("select * from Users where UserName='******' and Password='******'", user.Username, user.Oldpassword);
SqlDataReader reader = cmd.ExecuteReader(); //looks like a table
int count = 0;
while (reader.Read())
{
count++;
}
reader.Close();
cmd.Dispose();
connDB.Close();
return(count);
}
public static bool UpdatePassword(User user)
{
try
{
if (connDB.State == ConnectionState.Closed)
{
connDB = UtilityDB.ConnectDB();
cmd = new SqlCommand();
}
cmd.Connection = connDB;
cmd.CommandText = string.Format("update users set Password='******' where UserName='******'", user.Newpassword, user.Username);
cmd.ExecuteNonQuery();
connDB.Close();
}
catch (Exception)
{
return(false);
// throw;
}
return(true);
}
