public static Employee SearchRecord(int empId)
{
Employee emp = new Employee();
// 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 EmpId = " + empId;
SqlDataReader sqlReader = cmd.ExecuteReader();
if (sqlReader.Read())
{
emp.EmployeeId = Convert.ToInt32(sqlReader["EmpId"]);
emp.FirstName = sqlReader["FirstName"].ToString();
emp.LastName = sqlReader["LastName"].ToString();
emp.JobTitle = sqlReader["JobTitle"].ToString();
}
else
{
emp = null;
}
return(emp);
}
public static void DeleteRecord(int empId)
{
SqlConnection conndb = UtilityDB.ConnectDB();
try
{
string sqlDelete = "DELETE from Employees " +
" WHERE EmpId = " + empId;
string sqlDeleteUser = "******" +
" WHERE EmpId = " + empId;
SqlCommand cmd = new SqlCommand(sqlDelete, conndb);
SqlCommand cmdu = new SqlCommand(sqlDeleteUser, conndb);
cmdu.ExecuteNonQuery();
cmd.ExecuteNonQuery();
MessageBox.Show("Record deleted Successfully.");
}
catch (FormatException)
{
throw;
}
finally
{
conndb.Close();
}
}
public static int GetEmployeeId()
{
int nextId = 1112;
// connect and open the database
SqlConnection connDB = UtilityDB.ConnectDB();
// create and customize the SqlCommand object
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT max(EmpId) 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 List <Employee> GetEmployeeList()
{
List <Employee> listEmp = new List <Employee>();
// 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();
Employee emp;
while (sqlReader.Read())
{
emp = new Employee();
emp.EmployeeId = Convert.ToInt32(sqlReader["EmpId"]);
emp.FirstName = sqlReader["FirstName"].ToString();
emp.LastName = sqlReader["LastName"].ToString();
emp.JobTitle = sqlReader["JobTitle"].ToString();
listEmp.Add(emp);
}
// close database
connDB.Close();
return(listEmp);
}
public static List <User> GetUserList()
{
List <User> listUser = new List <User>();
// 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 Users";
SqlDataReader sqlReader = cmd.ExecuteReader();
User use;
while (sqlReader.Read())
{
use = new User();
use.EmpId = Convert.ToInt32(sqlReader["EmpId"]);
use.Password = sqlReader["Password"].ToString();
listUser.Add(use);
}
// close database
connDB.Close();
return(listUser);
}
public static DataSet InitializeDataSet()
{
dsHithecDB = new DataSet("HitechDS");
dtClients = new DataTable("Clients");
dtClients.Columns.Add("CId", typeof(Int32));
dtClients.Columns.Add("CName", typeof(string));
dtClients.Columns.Add("Street", typeof(string));
dtClients.Columns.Add("City", typeof(string));
dtClients.Columns.Add("PostalCode", typeof(string));
dtClients.Columns.Add("Phone", typeof(string));
dtClients.Columns.Add("FaxNumber", typeof(string));
dtClients.Columns.Add("Credit", typeof(float));
dtClients.Columns.Add("CEmail", typeof(string));
dtClients.PrimaryKey = new DataColumn[] { dtClients.Columns["CId"] };
dsHithecDB.Tables.Add(dtClients);
da = new SqlDataAdapter("SELECT * FROM Clients", UtilityDB.ConnectDB());
SqlCommandBuilder sqlBuilder = new SqlCommandBuilder(da);
da.Fill(dsHithecDB.Tables["Clients"]);
return(dsHithecDB);
}
public static void SaveRecordU(User use)
{
// Connect and open database
SqlConnection connDB = UtilityDB.ConnectDB();
// Perform insert statement
//create an object of type SqlCommand
SqlCommand cmdu = new SqlCommand();
cmdu.Connection = connDB;
string sqlInsertu = "INSERT INTO Users " +
"(EmpId,Password)" +
" VALUES (@EmpId,@Password)";
cmdu.Parameters.AddWithValue("@EmpId", use.EmpId);
cmdu.Parameters.AddWithValue("@Password", use.Password);
cmdu.CommandText = sqlInsertu;
cmdu.ExecuteNonQuery();
connDB.Close();
}
public static void UpdateRecord(Employee 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 EmpId = " + emp.EmployeeId + "," +
" FirstName = '" + emp.FirstName + "'," +
" LastName = '" + emp.LastName + "'," +
" JobTitle = '" + emp.JobTitle +
"'" + " WHERE EmpId =" + emp.EmployeeId;
sqlCmd.ExecuteNonQuery();
//Close the database
connDb.Close();
MessageBox.Show("Employee record has been updated successully..", "Confirmation");
}
public static void SaveRecord(Employee 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 " +
"(EmpId,FirstName,LastName,JobTitle)" +
" VALUES (@EmpId,@FirstName,@LastName,@JobTitle)";
cmd.Parameters.AddWithValue("@EmpId", emp.EmployeeId);
cmd.Parameters.AddWithValue("@FirstName", emp.FirstName);
cmd.Parameters.AddWithValue("@LastName", emp.LastName);
cmd.Parameters.AddWithValue("@JobTitle", emp.JobTitle);
cmd.CommandText = sqlInsert;
cmd.ExecuteNonQuery();
connDB.Close();
MessageBox.Show("Employee record has been saved successfully", "Confirmation");
}
