1Using#region Using
  2
  3using System;
  4using System.Collections.Generic;
  5using System.Collections.Specialized;
  6using System.Configuration;
  7using System.Data;
  8using System.Data.SqlClient;
  9using System.Data.SqlTypes;
 10using System.Text;
 11using System.Globalization;
 12using LiveBlog.Core;
 13
 14#endregion
 15
 16namespace LiveBlog.Core.Providers
 17...{
 18    /**//// <summary>
 19    /// Microsoft SQL Server Implementation of BlogProvider
 20    /// </summary>
 21    public class MSSQLBlogProvider : BlogProvider, IDisposable
 22    ...{
 23        private string connStringName;
 24        private SqlConnection providerConn;
 25
 26        Posts#region Posts
 27        /**//// <summary>
 28        /// Retrieves a post based on the specified Id.
 29        /// </summary>
 30        public override Post SelectPost( Guid id )
 31        ...{
 32            bool connClose = OpenConnection();
 33
 34            Post post = new Post();
 35            string sqlQuery = "SELECT PostID, Title, Description, PostContent, DateCreated, " +
 36                                "DateModified, Author, IsPublished, IsCommentEnabled, Raters, Rating, Slug " +
 37                                "FROM be_Posts " +
 38                                "WHERE PostID = @id";
 39            SqlCommand cmd = new SqlCommand(sqlQuery, providerConn);
 40            cmd.Parameters.Add(new SqlParameter("@id", id.ToString()));
 41            SqlDataReader rdr = cmd.ExecuteReader();
 42            rdr.Read();
 43
 44            post.Id = rdr.GetGuid(0);
 45            post.Title = rdr.GetString(1);
 46            post.Content = rdr.GetString(3);
 47            if (!rdr.IsDBNull(2))
 48                post.Description = rdr.GetString(2);
 49            if (!rdr.IsDBNull(4))
 50                post.DateCreated = rdr.GetDateTime(4);
 51            if (!rdr.IsDBNull(5))
 52                post.DateModified = rdr.GetDateTime(5);
 53            if (!rdr.IsDBNull(6))
 54                post.Author = rdr.GetString(6);
 55            if (!rdr.IsDBNull(7))
 56                post.IsPublished = rdr.GetBoolean(7);
 57            if (!rdr.IsDBNull(8))
 58                post.IsCommentsEnabled = rdr.GetBoolean(8);
 59            if (!rdr.IsDBNull(9))
 60                post.Raters = rdr.GetInt32(9);
 61            if (!rdr.IsDBNull(10))
 62                post.Rating = rdr.GetFloat(10);
 63            if (!rdr.IsDBNull(11))
 64                post.Slug = rdr.GetString(11);
 65            else
 66                post.Slug = "";
 67
 68            rdr.Close();
 69
 70            // Tags
 71            sqlQuery = "SELECT Tag " +
 72                        "FROM be_PostTag " +
 73                        "WHERE PostID = @id";
 74            cmd.CommandText = sqlQuery;
 75            rdr = cmd.ExecuteReader();
 76
 77            while (rdr.Read())
 78            ...{
 79                if (!rdr.IsDBNull(0))
 80                    post.Tags.Add(rdr.GetString(0));
 81            }
 82
 83            rdr.Close();
 84            post.Tags.MarkOld();
 85
 86            // Categories
 87            sqlQuery = "SELECT CategoryID " +
 88                        "FROM be_PostCategory " +
 89                        "WHERE PostID = @id";
 90            cmd.CommandText = sqlQuery;
 91            rdr = cmd.ExecuteReader();
 92
 93            while (rdr.Read())
 94            ...{
 95                Guid key = rdr.GetGuid(0);
 96                if (Category.GetCategory(key) != null)
 97                    post.Categories.Add(Category.GetCategory(key));
 98            }
 99
100            rdr.Close();
101
102            // Comments
103            sqlQuery = "SELECT PostCommentID, CommentDate, Author, Email, Website, Comment, Country, Ip, IsApproved " +
104                        "FROM be_PostComment " +
105                        "WHERE PostID = @id";
106            cmd.CommandText = sqlQuery;
107            rdr = cmd.ExecuteReader();
108
109            while (rdr.Read())
110            ...{
111                Comment comment = new Comment();
112                comment.Id = rdr.GetGuid(0);
113                comment.IsApproved = true;
114                comment.Author = rdr.GetString(2);
115                if (!rdr.IsDBNull(4))
116                ...{
117                    Uri website;
118                    if (Uri.TryCreate(rdr.GetString(4), UriKind.Absolute, out website))
119                        comment.Website = website;
120                }
121                comment.Email = rdr.GetString(3);
122                comment.Content = rdr.GetString(5);
123                comment.DateCreated = rdr.GetDateTime(1);
124                comment.Parent = post;
125
126                if (!rdr.IsDBNull(6))
127                    comment.Country = rdr.GetString(6);
128                if (!rdr.IsDBNull(7))
129                    comment.IP = rdr.GetString(7);
130                if (!rdr.IsDBNull(8))
131                    comment.IsApproved = rdr.GetBoolean(8);
132                else
133                    comment.IsApproved = true;
134
135                post.Comments.Add(comment);
136            }
137
138            post.Comments.Sort();
139
140            rdr.Close();
141
142            // Email Notification
143            sqlQuery = "SELECT NotifyAddress " +
144                        "FROM be_PostNotify " +
145                        "WHERE PostID = @id";
146            cmd.CommandText = sqlQuery;
147            rdr = cmd.ExecuteReader();
148
149            while (rdr.Read())
150            ...{
151                if (!rdr.IsDBNull(0))
152                    post.NotificationEmails.Add(rdr.GetString(0));
153            }
154
155            rdr.Close();
156
157            if (connClose)
158                providerConn.Close();
159
160            return post;
161        }
162
163        /**//// <summary>
164        /// Inserts a new Post to the data store.
165        /// </summary>
166        public override void InsertPost( Post post )
167        ...{
168            OpenConnection();
169
170            string sqlQuery = "INSERT INTO " +
171                                "be_Posts (PostID, Title, Description, PostContent, DateCreated, " +
172                                "DateModified, Author, IsPublished, IsCommentEnabled, Raters, Rating, Slug)" +
173                                "VALUES (@id, @title, @desc, @content, @created, @modified, " +
174                                "@author, @published, @commentEnabled, @raters, @rating, @slug)";
175            SqlCommand cmd = new SqlCommand(sqlQuery, providerConn);
176            cmd.Parameters.Add(new SqlParameter("@id", post.Id.ToString()));
177            cmd.Parameters.Add(new SqlParameter("@title", post.Title));
178            if (post.Description == null)
179                cmd.Parameters.Add(new SqlParameter("@desc", ""));
180            else
181                cmd.Parameters.Add(new SqlParameter("@desc", post.Description));
182            cmd.Parameters.Add(new SqlParameter("@content", post.Content));
183            cmd.Parameters.Add(new SqlParameter("@created", new SqlDateTime(post.DateCreated.AddHours(-BlogSettings.Instance.Timezone))));
184            if (post.DateModified == new DateTime())
185                cmd.Parameters.Add(new SqlParameter("@modified", new SqlDateTime()));
186            else
187                cmd.Parameters.Add(new SqlParameter("@modified", new SqlDateTime(post.DateModified.AddHours(-BlogSettings.Instance.Timezone))));
188            if (post.Author == null)
189                cmd.Parameters.Add(new SqlParameter("@author", ""));
190            else
191                cmd.Parameters.Add(new SqlParameter("@author", post.Author));
192            cmd.Parameters.Add(new SqlParameter("@published", post.IsPublished));
193            cmd.Parameters.Add(new SqlParameter("@commentEnabled", post.IsCommentsEnabled));
194            cmd.Parameters.Add(new SqlParameter("@raters", post.Raters.ToString(CultureInfo.InvariantCulture)));
195            cmd.Parameters.Add(new SqlParameter("@rating", post.Rating.ToString(System.Globalization.CultureInfo.InvariantCulture)));
196            if (post.Slug == null)
197                cmd.Parameters.Add(new SqlParameter("@slug", ""));
198            else
199                cmd.Parameters.Add(new SqlParameter("@slug", post.Slug));
200
201            cmd.ExecuteNonQuery();
202
203            // Tags
204            UpdateTags(post);
205
206            // Categories
207            UpdateCategories(post);
208
209            // Comments
210            UpdateComments(post);
211
212            // Email Notification
213            UpdateNotify(post);
214
215            providerConn.Close();
216        }
217
218        /**//// <summary>
219        /// Updates a Post.
220        /// </summary>
221        public override void UpdatePost( Post post )
222        ...{
223            OpenConnection();
224
225            string sqlQuery = "UPDATE be_Posts " +
226                                "SET Title = @title, Description = @desc, PostContent = @content, " +
227                                "DateCreated = @created, DateModified = @modified, Author = @Author, " +
228                                "IsPublished = @published, IsCommentEnabled = @commentEnabled, " +
229                                "Raters = @raters, Rating = @rating, Slug = @slug " +
230                                "WHERE PostID = @id";
231            SqlCommand cmd = new SqlCommand(sqlQuery, providerConn);
232            cmd.Parameters.Add(new SqlParameter("@title", post.Title));
233            if (post.Description == null)
234                cmd.Parameters.Add(new SqlParameter("@desc", ""));
235            else
236                cmd.Parameters.Add(new SqlParameter("@desc", post.Description));
237            cmd.Parameters.Add(new SqlParameter("@content", post.Content));
238            cmd.Parameters.Add(new SqlParameter("@created", new SqlDateTime(post.DateCreated.AddHours(-BlogSettings.Instance.Timezone))));
239            if (post.DateModified == new DateTime())
240                cmd.Parameters.Add(new SqlParameter("@modified", new SqlDateTime()));
241            else
242                cmd.Parameters.Add(new SqlParameter("@modified", new SqlDateTime(post.DateModified.AddHours(-BlogSettings.Instance.Timezone))));
243            if (post.Author == null)
244                cmd.Parameters.Add(new SqlParameter("@author", ""));
245            else
246                cmd.Parameters.Add(new SqlParameter("@author", post.Author));
247            cmd.Parameters.Add(new SqlParameter("@published", post.IsPublished));
248            cmd.Parameters.Add(new SqlParameter("@commentEnabled", post.IsCommentsEnabled));
249            cmd.Parameters.Add(new SqlParameter("@id", post.Id.ToString()));
250            cmd.Parameters.Add(new SqlParameter("@raters", post.Raters.ToString(CultureInfo.InvariantCulture)));
251            cmd.Parameters.Add(new SqlParameter("@rating", post.Rating.ToString(CultureInfo.InvariantCulture)));
252            if (post.Slug == null)
253                cmd.Parameters.Add(new SqlParameter("@slug", ""));
254            else
255                cmd.Parameters.Add(new SqlParameter("@slug", post.Slug));
256
257            cmd.ExecuteNonQuery();
258
259            // Tags
260            UpdateTags(post);
261
262            // Categories
263            UpdateCategories(post);
264
265            // Comments
266            UpdateComments(post);
267
268            // Email Notification
269            UpdateNotify(post);
270
271            providerConn.Close();
272
273        }
274
275        /**//// <summary>
276        /// Deletes a post from the data store.
277        /// </summary>
278        public override void DeletePost( Post post )
279        ...{
280            OpenConnection();
281
282            string sqlQuery = "DELETE FROM be_PostTag WHERE PostID = @id;" +
283                                "DELETE FROM be_PostCategory WHERE PostID = @id;" +
284                                "DELETE FROM be_PostNotify WHERE PostID = @id;" +
285                                "DELETE FROM be_PostComment WHERE PostID = @id;" +
286                                "DELETE FROM be_Posts WHERE PostID = @id;";
287            SqlCommand cmd = new SqlCommand(sqlQuery, providerConn);
288            cmd.Parameters.Add(new SqlParameter("@id", post.Id.ToString()));
289
290            cmd.ExecuteNonQuery();
291
292            providerConn.Close();
293        }
294
295        /**//// <summary>
296        /// Retrieves all posts from the data store
297        /// </summary>
298        /// <returns>List of Posts</returns>
299        public override List<Post> FillPosts()
300        ...{
301            List<Post> posts = new List<Post>();
302
303            OpenConnection();
304
305            string sqlQuery = "SELECT PostID FROM be_Posts ";
306            SqlDataAdapter sa = new SqlDataAdapter(sqlQuery, providerConn);
307            DataTable dtPosts = new DataTable();
308            dtPosts.Locale = CultureInfo.InvariantCulture;
309            sa.Fill(dtPosts);
310
311            foreach (DataRow dr in dtPosts.Rows)
312            ...{
313                posts.Add(Post.Load(new Guid(dr[0].ToString())));
314            }
315
316            providerConn.Close();
317
318            posts.Sort();
319            return posts;
320        }
321
322        private void UpdateTags( Post post )
323        ...{
324            SqlCommand cmd = new SqlCommand();
325            cmd.Connection = providerConn;
326            cmd.CommandText = "DELETE FROM be_PostTag WHERE PostID = @id";
327            cmd.Parameters.Clear();
328            cmd.Parameters.Add(new SqlParameter("@id", post.Id.ToString()));
329            cmd.ExecuteNonQuery();
330
331            foreach (string tag in post.Tags)
332            ...{
333                cmd.CommandText = "INSERT INTO be_PostTag (PostID, Tag) VALUES (@id, @tag)";
334                cmd.Parameters.Clear();
335                cmd.Parameters.Add(new SqlParameter("@id", post.Id.ToString()));
336                cmd.Parameters.Add(new SqlParameter("@tag", tag));
337                cmd.ExecuteNonQuery();
338            }
339        }
340
341        private void UpdateCategories( Post post )
342