Пример #1
0
        public TypeAWriting TypeAWritingFindByID(int authorID, int bookID)
        {
            TypeAWriting rv = null;

            try
            {
                EnsureConnected();
                using (IDbCommand command = _connection.CreateCommand())
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "TypeAWritingFindByID";
                    IDbDataParameter p = command.CreateParameter();
                    p.DbType        = DbType.Int32;
                    p.Direction     = ParameterDirection.Input;
                    p.ParameterName = "@authorID";
                    p.Value         = authorID;
                    command.Parameters.Add(p);

                    p               = command.CreateParameter();
                    p.DbType        = DbType.Int32;
                    p.Direction     = ParameterDirection.Input;
                    p.ParameterName = "@bookID";
                    p.Value         = bookID;
                    command.Parameters.Add(p);

                    using (IDataReader reader = command.ExecuteReader())
                    {
                        int count            = 0;
                        TypeAWritingMapper m = new TypeAWritingMapper(reader);
                        while (reader.Read())
                        {
                            rv = m.ToWriting(reader);
                            count++;
                        }
                        if (count > 1)
                        {
                            throw new Exception($"Multiple reccords found with id: author: {authorID}  book:{bookID}");
                        }
                    }
                }
                // the integer ids for book and author are now loaded from SQL
                // this procedure now loads the book and author data from
                // SQL
                LoadWritingItems(rv);
            }
            catch (Exception ex) when(Logger.Log(ex, "DAL"))
            {
            }
            return(rv);
        }
Пример #2
0
        public List <TypeAWriting> TypeAWritingsGetAll(int Skip = 0, int Take = 0)
        {
            List <TypeAWriting> rv = new List <TypeAWriting>();

            try
            {
                EnsureConnected();
                using (IDbCommand command = _connection.CreateCommand())
                {
                    // configure the command object
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "TypeAWritingsGetAll";

                    IDbDataParameter p = command.CreateParameter();
                    p.ParameterName = "@Skip";
                    p.DbType        = DbType.Int32;
                    p.Direction     = ParameterDirection.Input;
                    p.Value         = Skip;
                    command.Parameters.Add(p);
                    // since p has been added to the parameters collection
                    // it is safe to reuse the same variable
                    // for a new parameter
                    p = command.CreateParameter();
                    p.ParameterName = "@Take";
                    p.DbType        = DbType.Int32;
                    p.Direction     = ParameterDirection.Input;
                    p.Value         = Take;
                    command.Parameters.Add(p);
                    // load the data from the database
                    using (IDataReader reader = command.ExecuteReader())
                    {
                        // the mapper is constructed, and this is where the shape
                        // is validated to insure it is correct
                        // if the database structure changes,
                        // an exception will be thrown
                        // the less efficient Get Ordinal methods are only
                        // invoked one time per command
                        // this less efficient (but required) code
                        // is outside the loop
                        TypeAWritingMapper mapper = new TypeAWritingMapper(reader);

                        while (reader.Read())
                        {
                            TypeAWriting a = mapper.ToWriting(reader);
                            // the mapper uses the much more efficient getXXX
                            // methods internally to avoid boxing and
                            // string manipulation.  this more efficient code is
                            // inside the loop
                            if (a != null)
                            // if the mapper returns null for some reason it will
                            // be ignored
                            {
                                rv.Add(a);
                            }
                        }
                    }
                }
                foreach (TypeAWriting writing in rv)
                {
                    // the integer ids for book and author are already loaded from SQL
                    // this procedure now loads the book and author data from
                    // SQL
                    // this part of this method can be optimized
                    // it currently is making several round trips to the server

                    // optimization could be achieved by recreating the
                    // stored procedure to return multiple record sets
                    // and refactor this routine to read all the record sets

                    // I envision that the stored procedure would return all
                    // the junction items in the first set, then all the
                    // matching authors in the second set, then all the
                    // matching books.

                    // processing would involve calling reader.read until
                    // it fails
                    // then call reader.nextresult to get the next result set
                    // (the authors)  then call reader.nextresult to get
                    // the books

                    // the advantage of this is that it could be done in a single round trip to the server

                    // i leave this refactoring as an exercise for a future
                    // developer

                    LoadWritingItems(writing);
                }
            }
            catch (Exception ex) when(Logger.Log(ex, "DAL"))
            {
            }
            return(rv);
        }