// 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(); } } }