Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Configuration
Imports System.Web.Mail
Namespace VB2TheMax.WebLogger.Business
Public Class Blog
Private m_Connection As OleDbConnection
Private m_brChar As String = Convert.ToChar(13) & Convert.ToChar(10)
Public Sub New()
m_Connection = New OleDbConnection(ConfigurationManager.AppSettings("Blog_ConnString"))
End Sub
Private Sub ExecuteCommand(ByVal cmd As OleDbCommand)
cmd.Connection = m_Connection
Try
m_Connection.Open()
cmd.ExecuteNonQuery()
Finally
m_Connection.Close()
End Try
End Sub
' Get the messages/comments for the specified interval
Public Function GetData(ByVal fromDate As Date, ByVal toDate As Date) As DataSet
Dim ds As New DataSet()
' fill the dataset's Messages table with the messages posted in the specified interval
Dim da As New OleDbDataAdapter("SELECT * FROM Blog_Messages WHERE AddedDate BETWEEN ? AND ? ORDER BY AddedDate DESC", m_Connection)
da.SelectCommand.Parameters.Add("FromDate", OleDbType.Date).Value = fromDate
da.SelectCommand.Parameters.Add("ToDate", OleDbType.Date).Value = toDate.AddDays(1)
m_Connection.Open()
da.Fill(ds, "Messages")
' fill the dataset's Comments table with the comments for the loaded messages
If ds.Tables("Messages").Rows.Count > 0 Then
Dim inFilter As String = ""
' get a comma-delimited list of the IDs of the retrieved messages
Dim dr As DataRow
For Each dr In ds.Tables("Messages").Rows
If inFilter = "" Then
inFilter = dr("MessageID")
Else
inFilter &= ", " & dr("MessageID")
End If
Next
' build the SELECT for the Comments table
da.SelectCommand.CommandText = "SELECT * FROM Blog_Comments WHERE MessageID IN (" & _
inFilter & ") ORDER BY AddedDate DESC"
Else
da.SelectCommand.CommandText = "SELECT * FROM Blog_Comments WHERE MessageID = -1"
End If
da.Fill(ds, "Comments")
m_Connection.Close()
' if the Comments table is empty, add a fake comment with ID = -1
' this is necessary to have the calculated column added next working fine
If ds.Tables("Comments").Rows.Count = 0 Then
Dim dr As DataRow = ds.Tables("Comments").NewRow()
dr("CommentID") = -1
dr("Author") = "none"
dr("Email") = "none"
dr("Comment") = "none"
dr("AddedDate") = Date.Today
ds.Tables("Comments").Rows.Add(dr)
dr.AcceptChanges()
End If
' create a parent/child relationship between the two tables, against the MessageID column
ds.Relations.Add(New DataRelation("MsgComments", _
ds.Tables("Messages").Columns("MessageID"), _
ds.Tables("Comments").Columns("MessageID")))
' add a new calculated column to the Messages table, with the number of child comments
ds.Tables("Messages").Columns.Add("CommentsCount", _
GetType(Integer), "Count(Child(MsgComments).CommentID)")
Return ds
End Function
' Insert a new message
Public Function InsertMessage(ByVal title As String, ByVal message As String)
Dim cmd As New OleDbCommand("INSERT INTO Blog_Messages (Title, Message) VALUES (?, ?)")
cmd.Parameters.Add("Title", OleDbType.VarChar).Value = title
cmd.Parameters.Add("Message", OleDbType.LongVarChar).Value = message.Replace(m_brChar, "<br>")
ExecuteCommand(cmd)
End Function
' Insert a new comment
Public Function InsertComment(ByVal messageID As Integer, ByVal author As String, ByVal email As String, ByVal comment As String)
Dim cmd As New OleDbCommand("INSERT INTO Blog_Comments (MessageID, Author, Email, Comment) VALUES (?, ?, ?, ?)")
cmd.Parameters.Add("MessageID", OleDbType.Integer).Value = messageID
cmd.Parameters.Add("Author", OleDbType.VarChar).Value = HttpUtility.HtmlEncode(author)
cmd.Parameters.Add("Email", OleDbType.VarChar).Value = HttpUtility.HtmlEncode(email)
cmd.Parameters.Add("Comment", OleDbType.LongVarChar).Value = HttpUtility.HtmlEncode(comment).Replace(m_brChar, "<br>")
ExecuteCommand(cmd)
' send the notification e-mail, if the setting is on
Dim sendNotifications As String = ConfigurationSettings.AppSettings("Blog_SendNotifications")
If Not sendNotifications Is Nothing AndAlso sendNotifications = "1" Then
Dim msgTitle, msgDate As String
' read a few details of the parent message
cmd.CommandText = "SELECT Title, AddedDate FROM Blog_Messages WHERE MessageID = " & messageID
m_Connection.Open()
Dim reader As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
If reader.Read Then
msgTitle = reader("Title")
msgDate = CType(reader("AddedDate"), Date).ToString("hh:mm tt on dddd, MMMM dd")
reader.Close()
End If
' build the msg's content
Dim msg As String = String.Format( _
"{0} (email: {1}) has just posted a comment the message ""{2}"" " & _
"of your BLOG that you posted at {3}. Here's the comment:{4}{4}{5}", _
author, email, msgTitle, msgDate, Environment.NewLine, comment)
' send the mail
System.Web.Mail.SmtpMail.Send("WebLogger", _
ConfigurationSettings.AppSettings("Blog_AdminEmail"), _
"New comment notification", msg)
End If
End Function
' Update a message
Public Function UpdateMessage(ByVal messageID As Integer, ByVal title As String, ByVal message As String)
Dim cmd As New OleDbCommand("UPDATE Blog_Messages SET Title = ?, Message = ? WHERE MessageID = ?")
cmd.Parameters.Add("Title", OleDbType.VarChar).Value = title
cmd.Parameters.Add("Message", OleDbType.LongVarChar).Value = message.Replace(m_brChar, "<br>")
cmd.Parameters.Add("MessageID", OleDbType.Integer).Value = messageID
ExecuteCommand(cmd)
End Function
' Update a comment
Public Function UpdateComment(ByVal commentID As Integer, ByVal author As String, ByVal email As String, ByVal comment As String)
Dim cmd As New OleDbCommand("UPDATE Blog_Comments SET Author = ?, Email = ?, Comment = ? WHERE CommentID = ?")
cmd.Parameters.Add("Author", OleDbType.VarChar).Value = author
cmd.Parameters.Add("Email", OleDbType.VarChar).Value = email
cmd.Parameters.Add("Comment", OleDbType.LongVarChar).Value = comment.Replace(m_brChar, "<br>")
cmd.Parameters.Add("CommentID", OleDbType.Integer).Value = commentID
ExecuteCommand(cmd)
End Function
' Delete a message
Public Function DeleteMessage(ByVal messageID As Integer)
Dim cmd As New OleDbCommand("DELETE FROM Blog_Messages WHERE MessageID = " & messageID)
ExecuteCommand(cmd)
End Function
' Delete a comment
Public Function DeleteComment(ByVal commentID As Integer)
Dim cmd As New OleDbCommand("DELETE FROM Blog_Comments WHERE CommentID = " & commentID)
ExecuteCommand(cmd)
End Function
' Return the data for a single message
Public Sub GetMessageData(ByVal messageID As Integer, ByRef title As String, ByRef message As String)
Dim cmd As New OleDbCommand("SELECT * FROM Blog_Messages WHERE MessageID = " & messageID, m_Connection)
Try
m_Connection.Open()
Dim reader As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
If reader.Read Then
title = reader("Title")
message = reader("Message")
reader.Close()
End If
Finally
m_Connection.Close()
End Try
End Sub
' Return the data for a single comment
Public Sub GetCommentData(ByVal commentID As Integer, ByRef author As String, ByRef email As String, ByRef comment As String)
Dim cmd As New OleDbCommand("SELECT * FROM Blog_Comments WHERE CommentID = " & commentID, m_Connection)
Try
m_Connection.Open()
Dim reader As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection)
If reader.Read Then
author = reader("Author")
email = reader("Email")
comment = reader("Comment")
reader.Close()
End If
Finally
m_Connection.Close()
End Try
End Sub
End Class
End Namespace
Reply With Quote
ataList Runat="server" Width="500px" ItemStyle-BackColor="whitesmoke" AlternatingItemStyle-BackColor="white"
Originally Posted by Ferris
