Пример #1
0
        // 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();
                }
            }
        }
Пример #2
0
        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());
        }
Пример #3
0
 //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();
         }
     }
 }
Пример #4
0
        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();
                }
            }
        }