public static int Login(User user)
{
int jobId = 0;
if (connDB.State == ConnectionState.Closed)
{
connDB = UtilityDB.ConnectDB();
cmd = new SqlCommand();
}
cmd.Connection = connDB;
cmd.CommandText = string.Format("select jobId from [Employee] where username='******' and password='******'", user.Username, user.Password);
SqlDataReader reader = cmd.ExecuteReader();
int count = 0;
while (reader.Read())
{
count++;
}
reader.Close();
if (count > 0)
{
jobId = (Int32)cmd.ExecuteScalar();
}
else
{
jobId = 0;
}
cmd.Dispose();
connDB.Close();
return(jobId);
}
public static bool SaveUser(User user)
{
bool result = true;
try
{
if (connDB.State == ConnectionState.Closed)
{
connDB = UtilityDB.ConnectDB();
cmd = new SqlCommand();
}
cmd.Connection = connDB;
cmd.CommandText = string.Format("insert into Employee values('{0}', '{1}',{2},'{3}','{4}')",
user.FirstName, user.LastName, user.JobId, user.Username, user.Password);
cmd.ExecuteNonQuery();
connDB.Close();
}
catch (Exception)
{
result = false;
// throw;
}
return(result);
}
public static bool UpdateCustomer(Customer cust)
{
bool result = true;
try
{
if (connDB.State == ConnectionState.Closed)
{
connDB = UtilityDB.ConnectDB();
cmd = new SqlCommand();
}
cmd.Connection = connDB;
cmd.CommandText = string.Format("UPDATE Customer SET Street ='{0}', City ='{1}', " +
"PostalCode ='{2}' , PhoneNumber ='{3}', FaxNumber ='{4}', CreditLimit ={5}" +
" WHERE CustomerId='{5}'", cust.Street, cust.City, cust.PostalCode, cust.PhoneNumber, cust.FaxNumber, cust.CreditLimit, cust.CustomerId);
cmd.ExecuteNonQuery();
connDB.Close();
}
catch (Exception)
{
result = false;
throw;
}
return(result);
}
public static bool SaveCustomer(Customer cust)
{
bool result = true;
try
{
if (connDB.State == ConnectionState.Closed)
{
connDB = UtilityDB.ConnectDB();
cmd = new SqlCommand();
}
cmd.Connection = connDB;
cmd.CommandText = string.Format("insert into Customers values( '{0}', '{1}','{2}','{3}','{4}','{5}', '{6}')",
cust.Name, cust.Street, cust.City, cust.PostalCode, cust.PhoneNumber, cust.FaxNumber, cust.CreditLimit);
cmd.ExecuteNonQuery();
connDB.Close();
}
catch (Exception)
{
result = false;
// throw;
}
return(result);
}
public static bool UpdateUser(User user)
{
bool res = true;
try
{
if (connDB.State == ConnectionState.Closed)
{
connDB = UtilityDB.ConnectDB();
cmd = new SqlCommand();
}
cmd.Connection = connDB;
cmd.CommandText = string.Format("update [User] set username='******', " +
"password='******', FirstName='{2}', LastName='{3}', role_id='{4}' where UserId='{5}'",
user.Username, user.Password, user.FirstName, user.LastName, user.RoleId, user.UserId);
cmd.ExecuteNonQuery();
connDB.Close();
}
catch (Exception)
{
res = false;
throw;
}
return(res);
}
public static bool updateCustomer(Customer customer)
{
bool res = true;
try
{
if (connDB.State == ConnectionState.Closed)
{
connDB = UtilityDB.ConnectDB();
cmd = new SqlCommand();
}
cmd.Connection = connDB;
cmd.CommandText = string.Format("update Customer set FirstName='{0}'," +
" LastName='{1}', City='{2}', ZipCode='{3}', PhoneNumber='{4}', " +
"FaxNumber='{5}', CreditLimit='{6}' where Customer_Id='{7}'",
customer.FirstName, customer.LastName, customer.City,
customer.ZipCode, customer.PhoneNumber, customer.FaxNumber
, customer.CreditLimit, customer.CustomerId);
cmd.ExecuteNonQuery();
connDB.Close();
}
catch (Exception)
{
res = false;
throw;
}
return(res);
}
public static int SearchUser(string FirstName)
{
if (connDB.State == System.Data.ConnectionState.Closed)
{
connDB = UtilityDB.ConnectDB();
cmd = new SqlCommand();
}
cmd.Connection = connDB;
cmd.CommandText = string.Format("SELECT EmployeeId FROM Employee WHERE FirstName='{0}'", FirstName);
int UserId = Convert.ToInt32(cmd.ExecuteScalar());
connDB.Close();
return(UserId);
}
public static string SearchCustomer(Customer customer)
{
if (connDB.State == System.Data.ConnectionState.Closed)
{
connDB = UtilityDB.ConnectDB();
cmd = new SqlCommand();
}
cmd.Connection = connDB;
cmd.CommandText = string.Format("SELECT Name FROM [Customer] WHERE CustomerId='{0}'", customer.CustomerId);
string Name = (String)cmd.ExecuteScalar();
connDB.Close();
return(Name);
}
public static DataTable ListAllUsers()
{
if (connDB.State == ConnectionState.Closed)
{
connDB = UtilityDB.ConnectDB();
cmd = new SqlCommand();
}
cmd.Connection = connDB;
cmd.CommandText = "select * from Employee";
SqlDataReader reader = cmd.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(reader);
reader.Close();
cmd.Dispose();
connDB.Close();
return(dt);
}
public static string SearchUser(User user)
{
if (connDB.State == System.Data.ConnectionState.Closed)
{
connDB = UtilityDB.ConnectDB();
cmd = new SqlCommand();
}
//cmd.Connection = connDB;
//cmd.CommandText = string.Format("SELECT [User] FROM Employee WHERE UserId='{0}'", UserId);
//string FirstName = (String)cmd.ExecuteScalar();
//connDB.Close();
//return FirstName;
cmd.Connection = connDB;
cmd.CommandText = string.Format("SELECT FirstName FROM [User] WHERE UserId='{0}'", user.UserId);
string FirstName = (String)cmd.ExecuteScalar();
connDB.Close();
return(FirstName);
}
public static bool UpdateUser(User user)
{
bool result = true;
try
{
if (connDB.State == ConnectionState.Closed)
{
connDB = UtilityDB.ConnectDB();
cmd = new SqlCommand();
}
cmd.Connection = connDB;
cmd.CommandText = string.Format("UPDATE Employee SET FirstName ='{0}', LastName ='{1}', JobId ={2} , UserName ='******', Password ='******' WHERE EmployeeId={5}", user.FirstName, user.LastName, user.JobId, user.Username, user.Password, user.EmployeeId);
cmd.ExecuteNonQuery();
connDB.Close();
}
catch (Exception)
{
result = false;
//throw;
}
return(result);
}
public static bool DeleteUsers(int EmployeeId)
{
bool result = true;
try
{
if (connDB.State == ConnectionState.Closed)
{
connDB = UtilityDB.ConnectDB();
cmd = new SqlCommand();
}
cmd.Connection = connDB;
cmd.CommandText = string.Format("delete from Employee where EmployeeId ='{0}'", EmployeeId);
cmd.ExecuteNonQuery();
connDB.Close();
}
catch (Exception)
{
result = false;
// throw;
}
return(result);
}