Helper class for working with DbConnections.
This class is meant to reduce the amount of repeated code in database provider classes by pulling all the common actions into one spot. This should remove the many repetitive null checks on connections/parameters. This class handles the creation of DbConnection, setting its connection string, and opening the connection if possible. Usage is simple: using(var helper = new ConnectionHelper(provider)) { if (helper.HasConnection) { // do stuff } } Note: This class throws a NullReferenceException if its Provider.CreateParameter() method returns a null object. All of the methods in the DbBlogProvider class require parameterized queries, and previously each parameter created was being checked for null before proceeding. It's better to fail fast in this instance, to help creaters of custom implementations figure out what's wrong.
Наследование: IDisposable
 /// <summary>
 /// Adds blog parameters to command.
 /// </summary>
 /// <param name="blog">
 /// The blog.
 /// </param>
 /// <param name="conn">
 /// The connection.
 /// </param>
 /// <param name="cmd">
 /// The command.
 /// </param>
 private void AddBlogParametersToCommand(
     Blog blog, DbConnectionHelper conn, DbCommand cmd)
 {
     var parms = cmd.Parameters;
     parms.Add(conn.CreateParameter(FormatParamName("BlogId"), blog.Id.ToString()));
     parms.Add(conn.CreateParameter(FormatParamName("BlogName"), blog.Name ?? string.Empty));
     parms.Add(conn.CreateParameter(FormatParamName("Hostname"), blog.Hostname ?? string.Empty));
     parms.Add(conn.CreateParameter(FormatParamName("IsAnyTextBeforeHostnameAccepted"), blog.IsAnyTextBeforeHostnameAccepted));
     parms.Add(conn.CreateParameter(FormatParamName("StorageContainerName"), blog.StorageContainerName));
     parms.Add(conn.CreateParameter(FormatParamName("VirtualPath"), blog.VirtualPath ?? string.Empty));
     parms.Add(conn.CreateParameter(FormatParamName("IsPrimary"), blog.IsPrimary));
     parms.Add(conn.CreateParameter(FormatParamName("IsActive"), blog.IsActive));
     parms.Add(conn.CreateParameter(FormatParamName("IsSiteAggregation"), blog.IsSiteAggregation));
 }
        /// <summary>
        /// The add referrers parameters to command.
        /// </summary>
        /// <param name="referrer">
        /// The referrer.
        /// </param>
        /// <param name="conn">
        /// The connection.
        /// </param>
        /// <param name="cmd">
        /// The command.
        /// </param>
        private void AddReferrersParametersToCommand(Referrer referrer, DbConnectionHelper conn, DbCommand cmd)
        {
            var parms = cmd.Parameters;

            parms.Add(conn.CreateParameter("BlogId", Blog.CurrentInstance.Id.ToString()));
            parms.Add(conn.CreateParameter("ReferrerId", referrer.Id.ToString()));
            parms.Add(conn.CreateParameter(FormatParamName("ReferralDay"), referrer.Day));
            parms.Add(conn.CreateParameter(FormatParamName("ReferrerUrl"), (referrer.ReferrerUrl != null ? (object)referrer.ReferrerUrl.ToString() : DBNull.Value)));
            parms.Add(conn.CreateParameter(FormatParamName("ReferralCount"), referrer.Count));
            parms.Add(conn.CreateParameter(FormatParamName("Url"), (referrer.Url != null ? (object)referrer.Url.ToString() : DBNull.Value)));
            parms.Add(conn.CreateParameter(FormatParamName("IsSpam"), referrer.PossibleSpam));
        }
        /// <summary>
        /// The add blog roll parameters to command.
        /// </summary>
        /// <param name="blogRollItem">
        /// The blog roll item.
        /// </param>
        /// <param name="conn">
        /// The connection.
        /// </param>
        /// <param name="cmd">
        /// The command.
        /// </param>
        private void AddBlogRollParametersToCommand(
            BlogRollItem blogRollItem, DbConnectionHelper conn, DbCommand cmd)
        {

            var parms = cmd.Parameters;
            parms.Add(conn.CreateParameter(FormatParamName("BlogId"), Blog.CurrentInstance.Id.ToString()));
            parms.Add(conn.CreateParameter(FormatParamName("BlogRollId"), blogRollItem.Id.ToString()));
            parms.Add(conn.CreateParameter(FormatParamName("Title"), blogRollItem.Title));
            parms.Add(conn.CreateParameter(FormatParamName("Description"), blogRollItem.Description));
            parms.Add(conn.CreateParameter(FormatParamName("BlogUrl"), (blogRollItem.BlogUrl != null ? (object)blogRollItem.BlogUrl.ToString() : DBNull.Value)));
            parms.Add(conn.CreateParameter(FormatParamName("FeedUrl"), (blogRollItem.FeedUrl != null ? (object)blogRollItem.FeedUrl.ToString() : DBNull.Value)));
            parms.Add(conn.CreateParameter(FormatParamName("Xfn"), blogRollItem.Xfn));
            parms.Add(conn.CreateParameter(FormatParamName("SortIndex"), blogRollItem.SortIndex));
        }
        /// <summary>
        /// The update tags.
        /// </summary>
        /// <param name="post">
        /// The post to update.
        /// </param>
        /// <param name="conn">
        /// The connection
        /// </param>
        private void UpdateTags(Post post, DbConnectionHelper conn)
        {
            var sqlQuery = string.Format("DELETE FROM {0}PostTag WHERE BlogID = {1}blogid AND PostID = {1}id", this.tablePrefix, this.parmPrefix);
            using (var cmd = conn.CreateTextCommand(sqlQuery))
            {
                cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));
                cmd.Parameters.Add(conn.CreateParameter(FormatParamName("id"), post.Id.ToString()));
                cmd.ExecuteNonQuery();

                foreach (var tag in post.Tags)
                {
                    cmd.CommandText = string.Format("INSERT INTO {0}PostTag (BlogID, PostID, Tag) VALUES ({1}blogid, {1}id, {1}tag)", this.tablePrefix, this.parmPrefix);
                    cmd.Parameters.Clear();

                    cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));
                    cmd.Parameters.Add(conn.CreateParameter(FormatParamName("id"), post.Id.ToString()));
                    cmd.Parameters.Add(conn.CreateParameter(FormatParamName("tag"), tag));

                    cmd.ExecuteNonQuery();
                }
            }
        }
        /// <summary>
        /// The update notify.
        /// </summary>
        /// <param name="post">
        /// The post to update.
        /// </param>
        /// <param name="conn">
        /// The connection.
        /// </param>
        private void UpdateNotify(Post post, DbConnectionHelper conn)
        {
            var sqlQuery = string.Format("DELETE FROM {0}PostNotify WHERE BlogID = {1}blogid AND PostID = {1}id", this.tablePrefix, this.parmPrefix);
            using (var cmd = conn.CreateTextCommand(sqlQuery))
            {
                var parms = cmd.Parameters;

                parms.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));
                parms.Add(conn.CreateParameter(FormatParamName("id"), post.Id.ToString()));

                cmd.ExecuteNonQuery();

                foreach (var email in post.NotificationEmails)
                {
                    cmd.CommandText = string.Format("INSERT INTO {0}PostNotify (BlogID, PostID, NotifyAddress) VALUES ({1}blogid, {1}id, {1}notify)", this.tablePrefix, this.parmPrefix);
                    parms.Clear();

                    parms.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));
                    parms.Add(conn.CreateParameter(FormatParamName("id"), post.Id.ToString()));
                    parms.Add(conn.CreateParameter(FormatParamName("notify"), email));

                    cmd.ExecuteNonQuery();
                }
            }
        }
        /// <summary>
        /// The update comments.
        /// </summary>
        /// <param name="post">
        /// The post to update.
        /// </param>
        /// <param name="conn">
        /// The connection.
        /// </param>
        private void UpdateComments(Post post, DbConnectionHelper conn)
        {
            var sqlQuery = string.Format("DELETE FROM {0}PostComment WHERE BlogID = {1}blogid AND PostID = {1}id", this.tablePrefix, this.parmPrefix);
            using (var cmd = conn.CreateTextCommand(sqlQuery))
            {

                var parms = cmd.Parameters;

                cmd.Parameters.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));
                parms.Add(conn.CreateParameter(FormatParamName("id"), post.Id.ToString()));

                cmd.ExecuteNonQuery();

                foreach (var comment in post.AllComments)
                {
                    sqlQuery = string.Format("INSERT INTO {0}PostComment (BlogID, PostCommentID, ParentCommentID, PostID, CommentDate, Author, Email, Website, Comment, Country, Ip, IsApproved, ModeratedBy, Avatar, IsSpam, IsDeleted) VALUES ({1}blogid, {1}postcommentid, {1}parentid, {1}id, {1}date, {1}author, {1}email, {1}website, {1}comment, {1}country, {1}ip, {1}isapproved, {1}moderatedby, {1}avatar, {1}isspam, {1}isdeleted)", this.tablePrefix, this.parmPrefix);

                    cmd.CommandText = sqlQuery;
                    parms.Clear();

                    parms.Add(conn.CreateParameter(FormatParamName("blogid"), Blog.CurrentInstance.Id.ToString()));
                    parms.Add(conn.CreateParameter(FormatParamName("postcommentid"), comment.Id.ToString()));
                    parms.Add(conn.CreateParameter(FormatParamName("parentid"), comment.ParentId.ToString()));
                    parms.Add(conn.CreateParameter(FormatParamName("id"), post.Id.ToString()));
                    parms.Add(conn.CreateParameter(FormatParamName("date"), comment.DateCreated.AddHours(-BlogSettings.Instance.Timezone)));
                    parms.Add(conn.CreateParameter(FormatParamName("author"), comment.Author));
                    parms.Add(conn.CreateParameter(FormatParamName("email"), comment.Email));
                    parms.Add(conn.CreateParameter(FormatParamName("website"), (comment.Website == null ? string.Empty : comment.Website.ToString())));
                    parms.Add(conn.CreateParameter(FormatParamName("comment"), comment.Content));
                    parms.Add(conn.CreateParameter(FormatParamName("country"), (comment.Country ?? string.Empty)));
                    parms.Add(conn.CreateParameter(FormatParamName("ip"), (comment.IP ?? string.Empty)));
                    parms.Add(conn.CreateParameter(FormatParamName("isapproved"), comment.IsApproved));
                    parms.Add(conn.CreateParameter(FormatParamName("moderatedby"), (comment.ModeratedBy ?? string.Empty)));
                    parms.Add(conn.CreateParameter(FormatParamName("avatar"), (comment.Avatar ?? string.Empty)));
                    parms.Add(conn.CreateParameter(FormatParamName("isspam"), comment.IsSpam));
                    parms.Add(conn.CreateParameter(FormatParamName("isdeleted"), comment.IsDeleted));

                    cmd.ExecuteNonQuery();
                }
            }
        }
        /// <summary>
        /// The update categories.
        /// </summary>
        /// <param name="post">
        /// The post to update.
        /// </param>
        /// <param name="conn">
        /// The connection.
        /// </param>
        private void UpdateCategories(Post post, DbConnectionHelper conn)
        {
            var sqlQuery = string.Format("DELETE FROM {0}PostCategory WHERE PostID = {1}id", this.tablePrefix, this.parmPrefix);
            using (var cmd = conn.CreateTextCommand(sqlQuery))
            {
                cmd.Parameters.Add(conn.CreateParameter(FormatParamName("id"), post.Id.ToString()));

                cmd.ExecuteNonQuery();

                foreach (var cat in post.Categories)
                {
                    cmd.CommandText = string.Format("INSERT INTO {0}PostCategory (PostID, CategoryID) VALUES ({1}id, {1}cat)", this.tablePrefix, this.parmPrefix);
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add(conn.CreateParameter(FormatParamName("id"), post.Id.ToString()));
                    cmd.Parameters.Add(conn.CreateParameter(FormatParamName("cat"), cat.Id.ToString()));

                    cmd.ExecuteNonQuery();
                }
            }
        }