public User GetWithReviews(int userId)
        {
            using (IDbConnection connection = new SqlConnection(ConnectionString))
            using (IDbCommand command = connection.CreateCommand())
            {
                connection.Open();

                command.CommandText =
                    "select [User].Id as UserId, [User].Username, [User].Password, [User].LastLoginDate, " +
                    "Review.Id as ReviewId, Review.DateTime, Review.Title, Review.Body, Review.Rating " +
                    "from [User] left outer join Review on [User].Id = Review.UserId " +
                    "where [User].Id = @userId";

                var userIdParameter = command.CreateParameter();
                userIdParameter.DbType = DbType.Int32;
                userIdParameter.ParameterName = "userId";
                userIdParameter.Value = userId;

                command.Parameters.Add(userIdParameter);

                using (var reader = command.ExecuteReader())
                {
                    User user = null;

                    while (reader.Read())
                    {
                        if (user == null)
                        {
                            user = ReadUser(reader);
                        }

                        var review = new Review
                            {
                                Id = (int)reader["ReviewId"],
                                Title = (string)reader["Title"],
                                Body = (string)reader["Body"],
                                DateTime = (DateTime)reader["DateTime"],
                                Rating = (int)reader["Rating"]
                            };

                        user.AddReview(review);
                    }

                    return user;
                }
            }
        }
예제 #2
0
 public virtual void AddReview(Review review)
 {
     _reviews.Add(review);
 }
        private void InsertReview(IDbConnection connection, User user, Review review)
        {
            using (IDbCommand command = connection.CreateCommand())
            {
                command.CommandText =
                    "insert into [Review] (UserId, Title, Body, Rating, DateTime) " +
                    "values (@userId, @title, @body, @rating, @dateTime);" +
                    "select SCOPE_IDENTITY()";

                var title = command.CreateParameter();
                title.DbType = DbType.String;
                title.ParameterName = "title";
                title.Value = review.Title;

                var body = command.CreateParameter();
                body.DbType = DbType.String;
                body.ParameterName = "body";
                body.Value = review.Body;

                var dateTime = command.CreateParameter();
                dateTime.DbType = DbType.DateTime;
                dateTime.ParameterName = "dateTime";
                dateTime.Value = review.DateTime;

                var rating = command.CreateParameter();
                rating.DbType = DbType.Int32;
                rating.ParameterName = "rating";
                rating.Value = review.Rating;

                var userId = command.CreateParameter();
                userId.DbType = DbType.Int32;
                userId.ParameterName = "userId";
                userId.Value = user.Id;

                command.Parameters.Add(title);
                command.Parameters.Add(body);
                command.Parameters.Add(dateTime);
                command.Parameters.Add(rating);
                command.Parameters.Add(userId);

                var id = Convert.ToInt32(command.ExecuteScalar());
                review.Id = id;
            }
        }