//
// Delete the Employee by ID.
// This method assumes that ConflictDetection is set to OverwriteValues.
public int DeleteEmployee(NorthwindEmployee employee)
{
const string sqlCmd = "DELETE FROM Employees WHERE EmployeeID = @EmployeeID";
SqlConnection conn = new SqlConnection(m_connectionString);
SqlCommand cmd = new SqlCommand(sqlCmd, conn);
cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = employee.EmployeeID;
int result = 0;
try
{
conn.Open();
result = cmd.ExecuteNonQuery();
}
catch (SqlException)
{
// Handle exception.
}
finally
{
conn.Close();
}
return(result);
}
private static NorthwindEmployee GetNorthwindEmployeeFromReader(IDataRecord reader)
{
NorthwindEmployee employee = new NorthwindEmployee();
employee.EmployeeID = reader.GetInt32(0);
employee.LastName = reader.GetString(1);
employee.FirstName = reader.GetString(2);
if (reader.GetValue(3) != DBNull.Value)
{
employee.Address = reader.GetString(3);
}
if (reader.GetValue(4) != DBNull.Value)
{
employee.City = reader.GetString(4);
}
if (reader.GetValue(5) != DBNull.Value)
{
employee.Region = reader.GetString(5);
}
if (reader.GetValue(6) != DBNull.Value)
{
employee.PostalCode = reader.GetString(6);
}
return(employee);
}
// Insert an Employee.
public int InsertEmployee(NorthwindEmployee employee)
{
if (String.IsNullOrEmpty(employee.FirstName))
{
throw new ArgumentException("FirstName cannot be null or an empty string.");
}
if (String.IsNullOrEmpty(employee.LastName))
{
throw new ArgumentException("LastName cannot be null or an empty string.");
}
if (employee.Address == null)
{
employee.Address = String.Empty;
}
if (employee.City == null)
{
employee.City = String.Empty;
}
if (employee.Region == null)
{
employee.Region = String.Empty;
}
if (employee.PostalCode == null)
{
employee.PostalCode = String.Empty;
}
SqlConnection conn = new SqlConnection(m_connectionString);
SqlCommand cmd = new SqlCommand("INSERT INTO Employees " +
" (FirstName, LastName, Address, City, Region, PostalCode) " +
" Values(@FirstName, @LastName, @Address, @City, @Region, @PostalCode); " +
"SELECT @EmployeeID = SCOPE_IDENTITY()", conn);
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 10).Value = employee.FirstName;
cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 20).Value = employee.LastName;
cmd.Parameters.Add("@Address", SqlDbType.VarChar, 60).Value = employee.Address;
cmd.Parameters.Add("@City", SqlDbType.VarChar, 15).Value = employee.City;
cmd.Parameters.Add("@Region", SqlDbType.VarChar, 15).Value = employee.Region;
cmd.Parameters.Add("@PostalCode", SqlDbType.VarChar, 10).Value = employee.PostalCode;
SqlParameter p = cmd.Parameters.Add("@EmployeeID", SqlDbType.Int);
p.Direction = ParameterDirection.Output;
int newEmployeeID = 0;
try
{
conn.Open();
cmd.ExecuteNonQuery();
newEmployeeID = (int)p.Value;
}
catch (SqlException)
{
// Handle exception.
}
finally
{
conn.Close();
}
return(newEmployeeID);
}
//
// Update the Employee by ID.
// This method assumes that ConflictDetection is set to OverwriteValues.
public int UpdateEmployee(NorthwindEmployee employee)
{
if (String.IsNullOrEmpty(employee.FirstName))
{
throw new ArgumentException("FirstName cannot be null or an empty string.");
}
if (String.IsNullOrEmpty(employee.LastName))
{
throw new ArgumentException("LastName cannot be null or an empty string.");
}
if (employee.Address == null)
{
employee.Address = String.Empty;
}
if (employee.City == null)
{
employee.City = String.Empty;
}
if (employee.Region == null)
{
employee.Region = String.Empty;
}
if (employee.PostalCode == null)
{
employee.PostalCode = String.Empty;
}
SqlConnection conn = new SqlConnection(m_connectionString);
SqlCommand cmd = new SqlCommand("UPDATE Employees " +
" SET FirstName=@FirstName, LastName=@LastName, " +
" Address=@Address, City=@City, Region=@Region, " +
" PostalCode=@PostalCode " +
" WHERE EmployeeID=@EmployeeID", conn);
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 10).Value = employee.FirstName;
cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 20).Value = employee.LastName;
cmd.Parameters.Add("@Address", SqlDbType.VarChar, 60).Value = employee.Address;
cmd.Parameters.Add("@City", SqlDbType.VarChar, 15).Value = employee.City;
cmd.Parameters.Add("@Region", SqlDbType.VarChar, 15).Value = employee.Region;
cmd.Parameters.Add("@PostalCode", SqlDbType.VarChar, 10).Value = employee.PostalCode;
cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = employee.EmployeeID;
int result = 0;
try
{
conn.Open();
result = cmd.ExecuteNonQuery();
}
catch (SqlException)
{
// Handle exception.
}
finally
{
conn.Close();
}
return(result);
}
private static NorthwindEmployee GetNorthwindEmployeeFromReader(IDataRecord reader)
{
NorthwindEmployee employee = new NorthwindEmployee();
employee.EmployeeID = reader.GetInt32(0);
employee.LastName = reader.GetString(1);
employee.FirstName = reader.GetString(2);
if (reader.GetValue(3) != DBNull.Value)
employee.Address = reader.GetString(3);
if (reader.GetValue(4) != DBNull.Value)
employee.City = reader.GetString(4);
if (reader.GetValue(5) != DBNull.Value)
employee.Region = reader.GetString(5);
if (reader.GetValue(6) != DBNull.Value)
employee.PostalCode = reader.GetString(6);
return employee;
}
//
// Update the Employee by ID.
// This method assumes that ConflictDetection is set to OverwriteValues.
public int UpdateEmployee(NorthwindEmployee employee)
{
if (String.IsNullOrEmpty(employee.FirstName))
throw new ArgumentException("FirstName cannot be null or an empty string.");
if (String.IsNullOrEmpty(employee.LastName))
throw new ArgumentException("LastName cannot be null or an empty string.");
if (employee.Address == null)
{
employee.Address = String.Empty;
}
if (employee.City == null)
{
employee.City = String.Empty;
}
if (employee.Region == null)
{
employee.Region = String.Empty;
}
if (employee.PostalCode == null)
{
employee.PostalCode = String.Empty;
}
SqlConnection conn = new SqlConnection(m_connectionString);
SqlCommand cmd = new SqlCommand("UPDATE Employees " +
" SET FirstName=@FirstName, LastName=@LastName, " +
" Address=@Address, City=@City, Region=@Region, " +
" PostalCode=@PostalCode " +
" WHERE EmployeeID=@EmployeeID", conn);
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 10).Value = employee.FirstName;
cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 20).Value = employee.LastName;
cmd.Parameters.Add("@Address", SqlDbType.VarChar, 60).Value = employee.Address;
cmd.Parameters.Add("@City", SqlDbType.VarChar, 15).Value = employee.City;
cmd.Parameters.Add("@Region", SqlDbType.VarChar, 15).Value = employee.Region;
cmd.Parameters.Add("@PostalCode", SqlDbType.VarChar, 10).Value = employee.PostalCode;
cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = employee.EmployeeID;
int result = 0;
try
{
conn.Open();
result = cmd.ExecuteNonQuery();
}
catch (SqlException)
{
// Handle exception.
}
finally
{
conn.Close();
}
return result;
}
// Insert an Employee.
public int InsertEmployee(NorthwindEmployee employee)
{
if (String.IsNullOrEmpty(employee.FirstName))
throw new ArgumentException("FirstName cannot be null or an empty string.");
if (String.IsNullOrEmpty(employee.LastName))
throw new ArgumentException("LastName cannot be null or an empty string.");
if (employee.Address == null)
{
employee.Address = String.Empty;
}
if (employee.City == null)
{
employee.City = String.Empty;
}
if (employee.Region == null)
{
employee.Region = String.Empty;
}
if (employee.PostalCode == null)
{
employee.PostalCode = String.Empty;
}
SqlConnection conn = new SqlConnection(m_connectionString);
SqlCommand cmd = new SqlCommand("INSERT INTO Employees " +
" (FirstName, LastName, Address, City, Region, PostalCode) " +
" Values(@FirstName, @LastName, @Address, @City, @Region, @PostalCode); " +
"SELECT @EmployeeID = SCOPE_IDENTITY()", conn);
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 10).Value = employee.FirstName;
cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 20).Value = employee.LastName;
cmd.Parameters.Add("@Address", SqlDbType.VarChar, 60).Value = employee.Address;
cmd.Parameters.Add("@City", SqlDbType.VarChar, 15).Value = employee.City;
cmd.Parameters.Add("@Region", SqlDbType.VarChar, 15).Value = employee.Region;
cmd.Parameters.Add("@PostalCode", SqlDbType.VarChar, 10).Value = employee.PostalCode;
SqlParameter p = cmd.Parameters.Add("@EmployeeID", SqlDbType.Int);
p.Direction = ParameterDirection.Output;
int newEmployeeID = 0;
try
{
conn.Open();
cmd.ExecuteNonQuery();
newEmployeeID = (int)p.Value;
}
catch (SqlException)
{
// Handle exception.
}
finally
{
conn.Close();
}
return newEmployeeID;
}
//
// Delete the Employee by ID.
// This method assumes that ConflictDetection is set to OverwriteValues.
public int DeleteEmployee(NorthwindEmployee employee)
{
const string sqlCmd = "DELETE FROM Employees WHERE EmployeeID = @EmployeeID";
SqlConnection conn = new SqlConnection(m_connectionString);
SqlCommand cmd = new SqlCommand(sqlCmd, conn);
cmd.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = employee.EmployeeID;
int result = 0;
try
{
conn.Open();
result = cmd.ExecuteNonQuery();
}
catch (SqlException)
{
// Handle exception.
}
finally
{
conn.Close();
}
return result;
}
