// Listing 9-3. Updating and Inserting Data
public void UpdateEngineerData(EngineerRecord Engineer)
{
using (SqlConnection cnn = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = cnn.CreateCommand())
{
cmd.CommandText =
@"UPDATE Engineers
SET [Surname] = @Surname,
[Firstname] = @Firstname,
[DateOfBirth] = @DateOfBirth,
[SecurityVetted] = @SecurityVetted
WHERE Id=@Id";
cmd.Parameters.AddWithValue("Surname", Engineer.Surname);
cmd.Parameters.AddWithValue("Firstname", Engineer.Firstname);
cmd.Parameters.AddWithValue("DateOfBirth", Engineer.DateOfBirth);
cmd.Parameters.AddWithValue("SecurityVetted", Engineer.SecurityVetted);
cmd.Parameters.AddWithValue("Id", Engineer.Id);
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
}
}
}
public IQueryable <EngineerRecord> GetEngineerData()
{
_EngineerRecordList.Clear();
using (SqlConnection cnn = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = cnn.CreateCommand())
{
cmd.CommandText =
@"SELECT Id , Surname , Firstname , DateOfBirth , SecurityVetted , IssueCount
FROM dbo.Engineers eng JOIN ( SELECT Engineer_Issue, COUNT(Engineer_Issue) IssueCount
FROM dbo.Issues GROUP BY Engineer_Issue) AS iss ON eng.Id = iss.Engineer_Issue";
cnn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
EngineerRecord Engineer = new EngineerRecord();
Engineer.Id = (int)dr["Id"];
Engineer.Surname = dr["Surname"].ToString();
Engineer.Firstname = dr["Firstname"].ToString();
Engineer.DateOfBirth = (DateTime)dr["DateOfBirth"];
Engineer.SecurityVetted = (bool)dr["SecurityVetted"];
Engineer.IssueCount = (int)dr["IssueCount"];
_EngineerRecordList.Add(Engineer);
}
}
cnn.Close();
}
}
return(_EngineerRecordList.AsQueryable());
}
//Listing 9-5. Deleting Records
public void DeleteEngineerData(EngineerRecord Engineer)
{
using (SqlConnection cnn = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = cnn.CreateCommand())
{
cmd.CommandText =
"DeleteEngineer";
cmd.Parameters.AddWithValue("@ID", Engineer.Id);
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cnn.Open();
cmd.ExecuteNonQuery();
cnn.Close();
}
}
}
public void InsertEngineerData(EngineerRecord Engineer)
{
using (SqlConnection cnn = new SqlConnection(_connectionString))
{
using (SqlCommand cmd = cnn.CreateCommand())
{
cmd.CommandText =
@"INSERT INTO Engineers (Surname, Firstname, DateOfBirth, SecurityVetted)
VALUES (@Surname, @Firstname, @DateOfBirth, @SecurityVetted);
SELECT @@Identity ";
cmd.Parameters.AddWithValue("Surname", Engineer.Surname);
cmd.Parameters.AddWithValue("Firstname", Engineer.Firstname);
cmd.Parameters.AddWithValue("DateOfBirth", Engineer.DateOfBirth);
cmd.Parameters.AddWithValue("SecurityVetted", Engineer.SecurityVetted);
cnn.Open();
Engineer.Id = (int)cmd.ExecuteScalar();
cnn.Close();
}
}
}