温馨提示:代码在线浏览功能只能做为源码浏览参考,如果想更进一步了解该代码请下载:LiveBlog v1.0测试版源码
当前文件:
LiveBlog/LiveBlog.Core/Providers/OleDbBlogProvider.cs,打开代码结构图
LiveBlog/LiveBlog.Core/Providers/OleDbBlogProvider.cs,打开代码结构图1Using#region Using 2
3
using System; 4
using System.Collections.Generic; 5
using System.Collections.Specialized; 6
using System.Configuration; 7
using System.Data; 8
using System.Data.OleDb; 9
using System.Text; 10
using System.Globalization; 11
using LiveBlog.Core; 12
13
#endregion 14
15
namespace 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<




