示例#1
0
文件: Program.cs 项目: wggreen/SQL
        static void Main(string[] args)
        {
            Console.Clear();

            WalkerRepository walkers = new WalkerRepository();

            System.Collections.Generic.List <Walker> allWalkers = walkers.GetAllWalkers();

            Console.WriteLine("Walkers:");
            foreach (Walker walker in allWalkers)
            {
                Console.WriteLine($"{walker.WalkerName}");
            }

            Console.WriteLine();

            Walker foundWalker = walkers.GetWalkerByNeighborhoodId(3);

            Console.WriteLine("All walkers on the east side:");
            Console.WriteLine($"{foundWalker.WalkerName}");

            Console.WriteLine();

            Walker newWalker = new Walker
            {
                WalkerName     = "Holden Parker",
                NeighborhoodId = 1
            };

            walkers.AddWalker(newWalker);
            Console.WriteLine($"Added new walker: {newWalker.WalkerName}");

            Console.WriteLine();

            OwnerRepository owners = new OwnerRepository();

            System.Collections.Generic.List <Owner> allOwners = owners.GetAllOwnersWithNeighborhood();
            Console.WriteLine("All owners and their neighborhood:");
            foreach (Owner owner in allOwners)
            {
                Console.WriteLine($"{owner.DogOwnerName}: {owner.Neighborhood.NeighborhoodName}");
            }

            Console.WriteLine();

            Owner newOwner = new Owner
            {
                DogOwnerName    = "Wily Metcalf",
                DogOwnerAddress = "6256 Premier Dr",
                NeighborhoodId  = 2,
                Phone           = "615-555-5555"
            };

            owners.AddOwner(newOwner);
            Console.WriteLine($"Added new owner: {newOwner.DogOwnerName}");

            Console.WriteLine();

            Owner updatedOwner = new Owner
            {
                Id              = newOwner.Id,
                DogOwnerName    = newOwner.DogOwnerName,
                DogOwnerAddress = newOwner.DogOwnerAddress,
                NeighborhoodId  = 3,
                Phone           = newOwner.Phone
            };

            owners.UpdateOwner(updatedOwner.Id, updatedOwner);
            Console.WriteLine($"Updated {newOwner.DogOwnerName}'s NeighborhoodId from {newOwner.NeighborhoodId} to {updatedOwner.NeighborhoodId}");

            Console.WriteLine();

            Walker updatedWalker = new Walker
            {
                Id             = newWalker.Id,
                WalkerName     = newWalker.WalkerName,
                NeighborhoodId = 2
            };

            walkers.UpdateWalker(updatedWalker.Id, updatedWalker);
            Console.WriteLine($"Updated {updatedWalker.WalkerName}'s NeighborhoodId from {newWalker.NeighborhoodId} to {updatedWalker.NeighborhoodId}");
        }
示例#2
0
        /// <summary>
        ///  Returns a list of all departments in the database
        /// </summary>
        public List <Walker> GetAllWalkers()
        {
            //  We must "use" the database connection.
            //  Because a database is a shared resource (other applications may be using it too) we must
            //  be careful about how we interact with it. Specifically, we Open() connections when we need to
            //  interact with the database and we Close() them when we're finished.
            //  In C#, a "using" block ensures we correctly disconnect from a resource even if there is an error.
            //  For database connections, this means the connection will be properly closed.
            using (SqlConnection conn = Connection)
            {
                // Note, we must Open() the connection, the "using" block   doesn't do that for us.
                conn.Open();

                // We must "use" commands too.
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    // Here we setup the command with the SQL we want to execute before we execute it.
                    cmd.CommandText = "SELECT Id, WalkerName, NeighborhoodId FROM Walker";

                    // Execute the SQL in the database and get a "reader" that will give us access to the data.
                    SqlDataReader reader = cmd.ExecuteReader();

                    // A list to hold the departments we retrieve from the database.
                    List <Walker> walkers = new List <Walker>();

                    // Read() will return true if there's more data to read
                    while (reader.Read())
                    {
                        // The "ordinal" is the numeric position of the column in the query results.
                        //  For our query, "Id" has an ordinal value of 0 and "DeptName" is 1.
                        int idColumnPosition = reader.GetOrdinal("Id");

                        // We user the reader's GetXXX methods to get the value for a particular ordinal.
                        int idValue = reader.GetInt32(idColumnPosition);

                        int    walkerNameColumnPosition = reader.GetOrdinal("WalkerName");
                        string walkerNameColumnValue    = reader.GetString(walkerNameColumnPosition);

                        int neighborhoodIdColumnPosition = reader.GetOrdinal("NeighborhoodId");
                        int neighborhoodIdColumnValue    = reader.GetInt32(neighborhoodIdColumnPosition);

                        // Now let's create a new department object using the data from the database.
                        Walker walker = new Walker
                        {
                            Id             = idValue,
                            WalkerName     = walkerNameColumnValue,
                            NeighborhoodId = neighborhoodIdColumnValue
                        };

                        // ...and add that department object to our list.
                        walkers.Add(walker);
                    }

                    // We should Close() the reader. Unfortunately, a "using" block won't work here.
                    reader.Close();

                    // Return the list of departments who whomever called this method.
                    return(walkers);
                }
            }
        }