public static List <Employees> GetEmployeeList()
{
List <Employees> listEmp = new List <Employees>();
// Connect and open database
SqlConnection connDB = UtilityDB.ConnectDB();
// Perform insert statement
//create an object of type SqlCommand
SqlCommand cmd = new SqlCommand();
cmd.Connection = connDB;
cmd.CommandText = "SELECT * FROM Employees";
SqlDataReader sqlReader = cmd.ExecuteReader();
Employees emp;
while (sqlReader.Read())
{
emp = new Employees();
emp.EmployeeId = Convert.ToInt32(sqlReader["EmployeeId"]);
emp.FirstName = sqlReader["FirstName"].ToString();
emp.LastName = sqlReader["LastName"].ToString();
emp.JobTitle = sqlReader["JobTitle"].ToString();
emp.Email = sqlReader["Email"].ToString();
listEmp.Add(emp);
}
// close database
connDB.Close();
return(listEmp);
}
public static Employees SearchRecordId(int empId)
{
Employees emp = new Employees();
// Connect Database
SqlConnection connDB = UtilityDB.ConnectDB();
// Create SQl command
SqlCommand cmd = new SqlCommand();
cmd.Connection = connDB;
// Create the Select Statement
cmd.CommandText = "SELECT * FROM Employees" +
" WHERE EmployeeId = " + empId;//+
//" OR FirstName = " + empId;
SqlDataReader sqlReader = cmd.ExecuteReader();
if (sqlReader.Read())
{
emp.EmployeeId = Convert.ToInt32(sqlReader["EmployeeId"]);
emp.FirstName = sqlReader["FirstName"].ToString();
emp.LastName = sqlReader["LastName"].ToString();
emp.JobTitle = sqlReader["JobTitle"].ToString();
emp.Email = sqlReader["Email"].ToString();
}
else
{
emp = null;
}
return(emp);
}
public static int GetEmployeeId()
{
int nextId = 1001;
// connect and open the database
SqlConnection connDB = UtilityDB.ConnectDB();
// create and customize the SqlCommand object
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT max(EmployeeId) as TempId " +
" FROM Employees";
cmd.Connection = connDB;
SqlDataReader sqlReader = cmd.ExecuteReader();
if (sqlReader.Read())
{
if (!sqlReader.IsDBNull(0))
{
nextId = Convert.ToInt32(sqlReader["TempId"]) + 1;
}
}
// close the database
connDB.Close();
//return the nextId
return(nextId);
}
public static void UpdateRecord(Employees emp)
{
//Connect and open the database : SqlConnection
SqlConnection connDb = UtilityDB.ConnectDB();
// Perform the Update operation : SqlCommand
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = connDb;
sqlCmd.CommandText = "Update Employees " +
"SET EmployeeId = @EmployeeId, " +
" FirstName = @FirstName, " +
" LastName = @LastName, " +
" JobTitle = @JobTitle, " +
" Email = @Email " +
" WHERE EmployeeId = @EmployeeId";
sqlCmd.Parameters.AddWithValue("@EmployeeId", emp.EmployeeId);
sqlCmd.Parameters.AddWithValue("@FirstName", emp.FirstName);
sqlCmd.Parameters.AddWithValue("@LastName", emp.LastName);
sqlCmd.Parameters.AddWithValue("@JobTitle", emp.JobTitle);
sqlCmd.Parameters.AddWithValue("@Email", emp.Email);
sqlCmd.ExecuteNonQuery();
//Close the database
connDb.Close();
MessageBox.Show("Employee record has been updated successully..", "Confirmation");
}
//public static List<Customers> ReadAllClients()
//{
// List<Customers> xxClients = new List<Customers>();
// if (File.Exists(filePath))
// {
// StreamReader sr = new StreamReader(filePath);
// string line = sr.ReadLine();
// while (line != null)
// {
// string[] index = line.Split(',');
// Customers xx = new Customers();
// xx.ClientName = index[0];
// xx.ClientAddress = index[1];
// xx.ClientCity = index[2];
// xx.PostalCode = index[3];
// xx.ClientPhone = index[4];
// xx.ClientCredit = Convert.ToInt32(index[5]);
// xxClients.Add(xx);
// line = sr.ReadLine();
// }
// sr.Close();
// }
// if (xxClients.Count() <= 0)
// MessageBox.Show("Employee not found!", "Error");
// return xxClients;
//}
//public static void UpdateClients(Customers cli)
//{
// StreamReader sr = new StreamReader(filePath);
// StreamWriter sw = new StreamWriter(filePath2);
// string line = sr.ReadLine();
// while (line != null)
// {
// string[] field = line.Split(',');
// if (cli.ClientName != field[0])
// {
// sw.WriteLine(field[0] + "," + field[1] + "," + field[2] + "," + field[3] + "," + field[4] + "," + field[5]);
// }
// line = sr.ReadLine();
// }
// sw.WriteLine(cli.ClientName + "," + cli.ClientAddress + "," + cli.ClientCity + "," + cli.PostalCode + "," + cli.ClientPhone + "," + cli.ClientCredit);
// sw.Close();
// sr.Close();
// File.Delete(filePath);
// File.Move(filePath2, filePath);
// MessageBox.Show("Update successfully!");
//}
public static List <Customers> GetCustomerList()
{
List <Customers> listCu = new List <Customers>();
// Connect and open database
SqlConnection connDB = UtilityDB.ConnectDB();
// Perform insert statement
//create an object of type SqlCommand
SqlCommand cmd = new SqlCommand();
cmd.Connection = connDB;
cmd.CommandText = "SELECT * FROM Customers";
SqlDataReader sqlReader = cmd.ExecuteReader();
Customers cu;
while (sqlReader.Read())
{
cu = new Customers();
cu.CustomerId = Convert.ToInt32(sqlReader["CustomerId"]);
cu.CustomerName = sqlReader["CustomerName"].ToString();
cu.Street = sqlReader["Street"].ToString();
cu.City = sqlReader["City"].ToString();
cu.PostalCode = sqlReader["PostalCode"].ToString();
cu.PhoneNumber = sqlReader["PhoneNumber"].ToString();
cu.FaxNumber = sqlReader["FaxNumber"].ToString();
cu.CreditLimit = float.Parse(sqlReader["CreditLimit"].ToString());
listCu.Add(cu);
}
// close database
connDB.Close();
return(listCu);
}
public static void SavePass(Userr au)
{
// Connect and open database
SqlConnection connDB = UtilityDB.ConnectDB();
// Perform insert statement
//create an object of type SqlCommand
SqlCommand cmd = new SqlCommand();
cmd.Connection = connDB;
string sqlInsert = "INSERT INTO Users " +
"(UserName,Password)" +
" VALUES (@UserName,@Password)";
cmd.Parameters.AddWithValue("@UserName", au.UserName);
cmd.Parameters.AddWithValue("@Password", au.Password);
cmd.CommandText = sqlInsert;
cmd.ExecuteNonQuery();
// close database
connDB.Close();
MessageBox.Show("Employee record has been saved successfully", "Confirmation");
}
public static bool IsValidUser(Userr myUser)
{
using (SqlConnection connectionDb = UtilityDB.ConnectDB())
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = connectionDb;
string sqlSelect = "SELECT UserName,Password FROM Users " +
"WHERE UserName = @UserName AND Password = @Password";
cmd.CommandText = sqlSelect;
cmd.Parameters.AddWithValue("@UserName", myUser.UserName);
cmd.Parameters.AddWithValue("@Password", myUser.Password);
SqlDataReader sqlReader = cmd.ExecuteReader();
if (sqlReader.Read())
{
return(true);
}
else
{
return(false);
}
}
}
public static void DeleteRecord(int empId)
{
SqlConnection conndb = UtilityDB.ConnectDB();
try
{
string sqlDelete = "DELETE from Employees " +
" WHERE EmployeeId = " + empId;
SqlCommand cmd = new SqlCommand(sqlDelete, conndb);
cmd.ExecuteNonQuery();
MessageBox.Show("Record deleted Successfully.");
}
catch (FormatException)
{
throw;
}
finally
{
conndb.Close();
}
}
public static void SaveRecord(Employees emp)
{
// Connect and open database
SqlConnection connDB = UtilityDB.ConnectDB();
// Perform insert statement
//create an object of type SqlCommand
SqlCommand cmd = new SqlCommand();
cmd.Connection = connDB;
string sqlInsert = "INSERT INTO Employees " +
"(EmployeeId,FirstName,LastName,JobTitle,Email)" +
" VALUES (@EmployeeId,@FirstName,@LastName,@JobTitle,@Email)";
cmd.Parameters.AddWithValue("@EmployeeId", emp.EmployeeId);
cmd.Parameters.AddWithValue("@FirstName", emp.FirstName);
cmd.Parameters.AddWithValue("@LastName", emp.LastName);
cmd.Parameters.AddWithValue("@JobTitle", emp.JobTitle);
cmd.Parameters.AddWithValue("@Email", emp.Email);
cmd.CommandText = sqlInsert;
cmd.ExecuteNonQuery();
// close database
connDB.Close();
MessageBox.Show("Employee record has been saved successfully", "Confirmation");
}