public Employee GetEmployeeByID(int id)
{
var con = ConfigurationManager.ConnectionStrings["TestDB"].ToString();
Employee matchingShipper = new Employee();
matchingShipper.EmployeeID = id;
using (SqlConnection myConnection = new SqlConnection(con))
{
string oString = "select * from Employees a where a.EmployeeID = @EmployeeID";
SqlCommand oCmd = new SqlCommand(oString, myConnection);
oCmd.Parameters.AddWithValue("@EmployeeID", id);
myConnection.Open();
using (SqlDataReader oReader = oCmd.ExecuteReader())
{
while (oReader.Read())
{
matchingShipper.FirstName = oReader["FirstName"].ToString();
matchingShipper.LastName = oReader["LastName"].ToString();
}
myConnection.Close();
}
}
return matchingShipper;
}
public bool SaveEmployee(Employee employee)
{
try
{
using (var conn = new SqlConnection(connectionString))
{
var cmd = new SqlCommand("UPDATE [dbo].[Employees] SET [LastName] = @LastName, [FirstName] = @FirstName, [Title] = @Title, [TitleOfCourtesy] = @TitleOfCourtesy, [BirthDate] = @BirthDate, [HireDate] = @HireDate, [Address] = @Address, [City] = @City, [Region] = @Region, [PostalCode] = @PostalCode, [Country] = @Country, [HomePhone] = @HomePhone, [Extension] = @Extension, [Notes] = @Notes, [ReportsTo] = @ReportsTo WHERE [EmployeeID] = @Id", conn);
cmd.Parameters.AddWithValue("@LastName", employee.LastName);
cmd.Parameters.AddWithValue("@FirstName", employee.FirstName);
cmd.Parameters.AddWithValue("@Title", employee.Title);
cmd.Parameters.AddWithValue("@TitleOfCourtesy", employee.TitleOfCourtesy);
cmd.Parameters.AddWithValue("@BirthDate", employee.BirthDate);
cmd.Parameters.AddWithValue("@HireDate", employee.HireDate);
cmd.Parameters.AddWithValue("@Address", employee.Address);
cmd.Parameters.AddWithValue("@City", employee.City);
cmd.Parameters.AddWithValue("@Region", employee.Region);
cmd.Parameters.AddWithValue("@PostalCode", employee.PostalCode);
cmd.Parameters.AddWithValue("@Country", employee.Country);
cmd.Parameters.AddWithValue("@HomePhone", employee.HomePhone);
cmd.Parameters.AddWithValue("@Extension", employee.Extension);
cmd.Parameters.AddWithValue("@Notes", employee.Notes);
cmd.Parameters.AddWithValue("@ReportsTo", employee.ReportsTo);
cmd.Parameters.AddWithValue("@Id", employee.Id);
conn.Open();
return cmd.ExecuteNonQuery() == 1;
}
}
catch (Exception ex)
{
throw new FaultException(ex.Message);
}
}
protected void btnGetEmployee_Click(object sender, EventArgs e)
{
EmployeeService.EmployeeServiceClient client = new EmployeeService.EmployeeServiceClient();
EmployeeService.Employee employee = client.GetEmployee(Convert.ToInt32(txtID.Text));
if (employee.Type == EmployeeService.EmployeeType.FullTimeEmployee)
{
txtAnnualSalary.Text = ((EmployeeService.FullTimeEmployee)employee).AnnualSalary.ToString();
trAnnualSalary.Visible = true;
trHourlyPay.Visible = false;
trHoursWorked.Visible = false;
}
else
{
txtHourlyPay.Text = ((EmployeeService.PartTimeEmployee)employee).HourlyPay.ToString();
txtHoursWorked.Text = ((EmployeeService.PartTimeEmployee)employee).HoursWorked.ToString();
trAnnualSalary.Visible = false;
trHourlyPay.Visible = true;
trHoursWorked.Visible = true;
}
txtName.Text = employee.Name;
txtGender.Text = employee.Gender;
txtDateOfBirth.Text = employee.DateOfBirth.ToShortDateString();
ddlEmployeeType.SelectedValue = ((int)employee.Type).ToString();
lblMessage.Text = "Employee retrieved";
}
public Employee GetEmployee(int id)
{
var employee = new Employee();
var connString = ConfigurationManager.ConnectionStrings["NORTHWND"].ConnectionString;
using (var connection = new SqlConnection(connString))
{
var getEmployeeCommand = new SqlCommand("Select [EmployeeID],[LastName],[FirstName],[Address],[Notes] from Employees Where EmployeeID=@id", connection);
getEmployeeCommand.Parameters.AddWithValue("@id", id);
connection.Open();
using (var reader = getEmployeeCommand.ExecuteReader())
{
while (reader.Read())
{
if (employee.EmployeeId != 0)
{
employee.EmployeeId = int.Parse(reader["EmployeeID"].ToString());
employee.FirstName = reader["FirstName"].ToString();
employee.LastName = reader["LastName"].ToString();
employee.Address = reader["Address"].ToString();
employee.Notes = reader["Notes"].ToString();
}
else
{
throw new FaultException("Employee not found in database");
}
}
}
}
return employee;
}
public Employee GetEmployee(int Id = 0)
{
Employee employee = new Employee();
string cs = ConfigurationManager.ConnectionStrings["DDBC"].ConnectionString;
using (SqlConnection conn = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("spGetEmployee", conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter parameterId = new SqlParameter();
parameterId.ParameterName = "@Id";
parameterId.SqlValue = Id;
cmd.Parameters.Add(parameterId);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
while(reader.Read())
{
employee.Id = Convert.ToInt32(reader["Id"]);
employee.Name = reader["Name"].ToString();
employee.DateOfBirth = Convert.ToDateTime(reader["DateOfBirth"]);
}
}
return employee;
}
public void SaveEmployee(Employee employee)
{
string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("spSaveEmployee", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter { ParameterName = "@Id", Value = employee.Id });
cmd.Parameters.Add(new SqlParameter { ParameterName = "@Name", Value = employee.Name });
cmd.Parameters.Add(new SqlParameter { ParameterName = "@Gender", Value = employee.Gender });
cmd.Parameters.Add(new SqlParameter { ParameterName = "@DateOfBirth", Value = employee.DateOfBirth });
con.Open();
cmd.ExecuteNonQuery();
}
}
public EmployeeInfo(Employee employee)
{
Id = employee.Id;
Name = employee.Name;
DOB = employee.DateOfBirth;
Type = employee.Type;
if (Type == EmployeeType.FullTimeEmployee)
{
AnnualSalary = ((FullTimeEmployee)employee).AnnualSalary;
}
else
{
HourlyPay = ((PartTimeEmployee)employee).HourlyPay;
HoursWorked = ((PartTimeEmployee)employee).HoursWorked;
}
}
public EmployeeInfo(Employee employee)
{
this.Id = employee.Id;
this.Name = employee.Name;
this.Gender = employee.Gender;
this.DOB = employee.DateOfBirth;
if(this.Type==EmployeeType.FullTimeEmployee)
{
this.AnnualSalary = ((FullTimeEmployee)employee).AnnualSalary;
}
else
{
this.HourlyPay = ((PartTimeEmployee)employee).Hourlypay;
this.HoursWorked = ((PartTimeEmployee)employee).HoursWorked;
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
EmployeeService.EmployeeServiceClient client = new EmployeeService.EmployeeServiceClient();
EmployeeService.Employee employee = null;
string message = string.Empty;
if (((EmployeeService.EmployeeType)Convert.ToInt32(ddlEmployeeType.SelectedValue)) == EmployeeService.EmployeeType.FullTimeEmployee)
{
employee = new EmployeeService.FullTimeEmployee
{
ID = Convert.ToInt32(txtID.Text),
Name = txtName.Text,
Gender = txtGender.Text,
DateOfBirth = Convert.ToDateTime(txtDateOfBirth.Text),
Type = EmployeeService.EmployeeType.FullTimeEmployee,
AnnualSalary = Convert.ToInt32(txtAnnualSalary.Text),
};
client.SaveEmployee(employee);
lblMessage.Text = "Employee saved";
}
else if (((EmployeeService.EmployeeType)Convert.ToInt32(ddlEmployeeType.SelectedValue)) == EmployeeService.EmployeeType.PartTimeEmployee)
{
employee = new EmployeeService.PartTimeEmployee
{
ID = Convert.ToInt32(txtID.Text),
Name = txtName.Text,
Gender = txtGender.Text,
DateOfBirth = Convert.ToDateTime(txtDateOfBirth.Text),
Type = EmployeeService.EmployeeType.PartTimeEmployee,
HourlyPay = Convert.ToInt32(txtHourlyPay.Text),
HoursWorked = Convert.ToInt32(txtHoursWorked.Text),
};
client.SaveEmployee(employee);
lblMessage.Text = "Employee saved";
}
else
{
lblMessage.Text = "Please select Employee Type";
}
}
public Employee GetEmployee(int id)
{
try
{
var employee = new Employee { Id = id };
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
var cmd = new SqlCommand("SELECT [LastName], [FirstName], [Title], [TitleOfCourtesy], [BirthDate], [HireDate], [Address], [City], [Region], [PostalCode], [Country], [HomePhone], [Extension], [Notes], [ReportsTo] FROM [dbo].[Employees] WHERE [EmployeeId] = @Id", conn);
cmd.Parameters.AddWithValue("@Id", id);
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
employee.BirthDate = DateTime.Parse(reader["BirthDate"].ToString());
employee.Address = reader["Address"].ToString();
employee.City = reader["City"].ToString();
employee.Country = reader["Country"].ToString();
employee.Extension = reader["Extension"].ToString();
employee.FirstName = reader["FirstName"].ToString();
employee.HireDate = DateTime.Parse(reader["HireDate"].ToString());
employee.HomePhone = reader["HomePhone"].ToString();
employee.LastName = reader["LastName"].ToString();
employee.Notes = reader["Notes"].ToString();
employee.PostalCode = reader["PostalCode"].ToString();
employee.Region = reader["Region"].ToString();
employee.Title = reader["Title"].ToString();
employee.TitleOfCourtesy = reader["TitleOfCourtesy"].ToString();
employee.ReportsTo = int.Parse(reader["ReportsTo"].ToString());
}
}
}
return employee;
}
catch (Exception ex)
{
throw new FaultException(ex.Message);
}
}
public void SetEmployee(Employee employee)
{
// 1- Parepare the connection
string cs = ConfigurationManager.ConnectionStrings["DDBC"].ConnectionString;
using(SqlConnection conn = new SqlConnection(cs))
{
// 2- Setup the command type
SqlCommand cmd = new SqlCommand("spSaveEmployee", conn);
cmd.CommandType = CommandType.StoredProcedure;
// 3- Add the parameters
SqlParameter parameterId = new SqlParameter()
{
ParameterName = "@Id",
Value = employee.Id
};
SqlParameter parameterName = new SqlParameter()
{
ParameterName = "@Name",
Value = employee.Name
};
SqlParameter parameterDateOfBirth = new SqlParameter()
{
ParameterName = "@DateOfBirth",
Value = employee.DateOfBirth.ToShortDateString()
};
cmd.Parameters.Add(parameterId);
cmd.Parameters.Add(parameterName);
cmd.Parameters.Add(parameterDateOfBirth);
// 4- Open and Execute
conn.Open();
cmd.ExecuteNonQuery();
}
}
public void SaveEmployee(Employee employee)
{
string cs = ConfigurationManager.ConnectionStrings["cdkConnectionString"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("spSaveEmployee", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter parameterId = new SqlParameter
{
ParameterName = "@Id",
Value = employee.Id
};
cmd.Parameters.Add(parameterId);
SqlParameter parameterName = new SqlParameter
{
ParameterName = "@Name",
Value = employee.Name
};
cmd.Parameters.Add(parameterName);
SqlParameter parameterGender = new SqlParameter
{
ParameterName = "@Gender",
Value = employee.Gender
};
cmd.Parameters.Add(parameterGender);
SqlParameter parameterDateOfBirth = new SqlParameter
{
ParameterName = "@DateOfBirth",
Value = employee.DateOfBirth
};
cmd.Parameters.Add(parameterDateOfBirth);
SqlParameter parameterEmployeeType = new SqlParameter
{
ParameterName = "@EmployeeType",
Value = employee.Type
};
cmd.Parameters.Add(parameterEmployeeType);
if (employee.GetType() == typeof(FullTimeEmployee))
{
SqlParameter parameterAnnualSalary = new SqlParameter
{
ParameterName = "@AnnualSalary",
Value = ((FullTimeEmployee)employee).AnnualSalary
};
cmd.Parameters.Add(parameterAnnualSalary);
}
else
{
SqlParameter parameterHourlyPay = new SqlParameter
{
ParameterName = "@HourlyPay",
Value = ((PartTimeEmployee)employee).Hourlypay
};
cmd.Parameters.Add(parameterHourlyPay);
SqlParameter parameterHoursWorked = new SqlParameter
{
ParameterName = "@HoursWorked",
Value = ((PartTimeEmployee)employee).HoursWorked
};
cmd.Parameters.Add(parameterHoursWorked);
}
con.Open();
cmd.ExecuteNonQuery();
}
}
public void SaveEmployee(Employee emp)
{
if (emp == null) throw new FaultException("Employee cannot be null");
var maxId = Employees.Max(x => x.Id);
emp.Id = maxId + 1;
Employees.Add(emp);
}
public void SetEmployee(Employee employee)
{
// 1- Parepare the connection
string cs = ConfigurationManager.ConnectionStrings["DDBC"].ConnectionString;
using(SqlConnection conn = new SqlConnection(cs))
{
// 2- Setup the command type
SqlCommand cmd = new SqlCommand("spSaveEmployee", conn);
cmd.CommandType = CommandType.StoredProcedure;
// 3- Add the parameters
SqlParameter parameterId = new SqlParameter()
{
ParameterName = "@Id",
Value = employee.Id
};
SqlParameter parameterName = new SqlParameter()
{
ParameterName = "@Name",
Value = employee.Name
};
SqlParameter parameterDateOfBirth = new SqlParameter()
{
ParameterName = "@DateOfBirth",
Value = employee.DateOfBirth.ToShortDateString()
};
SqlParameter parameterEmployeeType = new SqlParameter()
{
ParameterName = "@EmployeeType",
Value = employee.Type
};
cmd.Parameters.Add(parameterId);
cmd.Parameters.Add(parameterName);
cmd.Parameters.Add(parameterDateOfBirth);
cmd.Parameters.Add(parameterEmployeeType);
if (employee.GetType() == typeof(FullTimeEmployee))
{
SqlParameter parameterAnnualSalary = new SqlParameter()
{
ParameterName = "@AnnualSalary",
Value = ((FullTimeEmployee)employee).AnnualSalary
};
cmd.Parameters.Add(parameterAnnualSalary);
}
else
{
SqlParameter parameterHourlyPay = new SqlParameter()
{
ParameterName = "@HourlyPay",
Value = ((PartTimeEmployee)employee).HourlyPay
};
SqlParameter parameterHoursWorked = new SqlParameter()
{
ParameterName = "@HoursWorked",
Value = ((PartTimeEmployee)employee).HoursWorked
};
cmd.Parameters.Add(parameterHourlyPay);
cmd.Parameters.Add(parameterHoursWorked);
}
// 4- Open and Execute
conn.Open();
cmd.ExecuteNonQuery();
}
}
public void SaveEmployee(Employee employee)
{
string cs = ConfigurationManager.ConnectionStrings["subhasishConnectionString"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("spSaveEmployee", con);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
SqlParameter parameterId = new SqlParameter()
{
ParameterName = "@Id",
Value = employee.Id
};
cmd.Parameters.Add(parameterId);
SqlParameter parameterName = new SqlParameter()
{
ParameterName = "@Name",
Value = employee.Name
};
cmd.Parameters.Add(parameterName);
SqlParameter parameterGender = new SqlParameter()
{
ParameterName = "@Gender",
Value = employee.Gender
};
cmd.Parameters.Add(parameterGender);
SqlParameter parameterDateOfBirth = new SqlParameter()
{
ParameterName = "@DateOfBirth",
Value = employee.DateOfBirth
};
cmd.Parameters.Add(parameterDateOfBirth);
SqlParameter parameterEmployeeType = new SqlParameter()
{
ParameterName = "@EmployeeType",
Value = employee.Type
};
cmd.Parameters.Add(parameterEmployeeType);
if (employee.GetType() == typeof(FullTimeEmployee))
{
SqlParameter parameterAnnualSalary = new SqlParameter()
{
ParameterName = "@AnnualSalary",
Value = ((FullTimeEmployee)employee).AnnualSalary
};
cmd.Parameters.Add(parameterAnnualSalary);
}
else
{
SqlParameter parameterHourlyPay = new SqlParameter()
{
ParameterName = "@HourlyPay",
Value = ((PartTimeEmployee)employee).HourlyPay
};
cmd.Parameters.Add(parameterHourlyPay);
SqlParameter parameterHoursWorked = new SqlParameter()
{
ParameterName = "@HoursWorked",
Value = ((PartTimeEmployee)employee).HoursWorked
};
cmd.Parameters.Add(parameterHoursWorked);
}
con.Open();
cmd.ExecuteNonQuery();
}
}
public void AddEmployee(Employee emp)
{
emplist.Add(emp);
}
