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