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