Imports System.Data.SqlClient
Imports System.Web.SessionState

#Region "Developer Credits"
'=======================================================================================
'AUTHOR                  : David Silverlight
'
'CLAIM TO FAME      : Head Geek at XMLPitstop.com and CoFounder of NonProfitWays.com
'
'EMAIL                      : HeadGeek@XMLPitstop.com
'
'SHAMELESS PLUG  :Yes I am a consultant who is always looking for cool projects.
'
'PHONE                    : (561) 212-5707  Coconut Grove, Florida
'
'COPYWRIGHT		: This demo is not copywrighted so feel free to use it however it may help you.  Credit is always appreciated,
'                                  but not required.  I created it simply for the learning experience. If you have any suggestions on areas that 
'                                  can be improved upon, please feel free to email me.  All credit for enhancements will be given to 
'                                  those who suggest them.  Enjoy.
'=======================================================================================
#End Region

Public Class Chat_BLL

        'Read the Connection string frm the Web.Config File
        Private strCN As String = ConfigurationSettings.AppSettings("cnChat")

#Region "Chat Data access code"

        Function get_ChatDescription(ByVal intChatID As Integer) As String

                Return SqlHelper.ExecuteScalar(strCN, CommandType.StoredProcedure, "sp_getChatDescription", New SqlParameter("@ChatID", intChatID))

        End Function

        Function AddUserToChat(ByVal intChatID As Integer, ByVal intUserID As Integer, ByVal strSessionID As String) As String
                Dim strSQL As String = "sp_insUserPerChat"

                'Retrieve the parameters from the cache or from internal mechanism using SqlCommandBuilder
                Dim aParams() As SqlParameter = SqlHelperParameterCache.GetSpParameterSet(strCN, strSQL)

                'Set the parameter values
                aParams(0).Value = intChatID
                aParams(1).Value = intUserID
                aParams(2).Value = strSessionID

                'Save the parameters so that they will be cached in a hash table for subsequent stored procedure calls
                SqlHelperParameterCache.CacheParameterSet(strCN, strSQL, aParams)

                'Execute the stored procedure to Insert the Chat User Record
                Return SqlHelper.ExecuteNonQuery(strCN, CommandType.StoredProcedure, strSQL, aParams)

        End Function


        Function RemoveUserFromChat(ByVal intChatID As Integer, ByVal intUserID As Integer) As String
                Dim strSQL As String = "sp_delUserPerChat"

                'Retrieve the parameters from the cache or from internal mechanism using SqlCommandBuilder
                Dim aParams() As SqlParameter = SqlHelperParameterCache.GetSpParameterSet(strCN, strSQL)

                'Set the parameter values
                aParams(0).Value = intChatID
                aParams(1).Value = intUserID

                'Save the parameters so that they will be cached in a hash table for subsequent stored procedure calls
                SqlHelperParameterCache.CacheParameterSet(strCN, strSQL, aParams)

                'Execute the stored procedure to Remove the Chat User Record
                Return SqlHelper.ExecuteNonQuery(strCN, CommandType.StoredProcedure, strSQL, aParams)

        End Function

        Function AddChatMessage(ByVal intChatID As Integer, ByVal intUserID As Integer, ByVal strMessage As String)

                Dim strSQL As String = "sp_insChatMessage"

                'Retrieve the parameters from the cache or from internal mechanism using SqlCommandBuilder
                Dim aParams() As SqlParameter = SqlHelperParameterCache.GetSpParameterSet(strCN, strSQL)

                'Set the parameter values
                aParams(0).Value = intChatID
                aParams(1).Value = intUserID
                aParams(2).Value = strMessage

                'Save the parameters so that they will be cached in a hash table for subsequent stored procedure calls
                SqlHelperParameterCache.CacheParameterSet(strCN, strSQL, aParams)

                'Execute the stored procedure to Remove the Chat User Record
                Return SqlHelper.ExecuteNonQuery(strCN, CommandType.StoredProcedure, strSQL, aParams)


        End Function

        Function get_ChatUsers(ByVal intChatID As Integer) As DataSet
                Try

                        'Return All Chat Users for the given Chat
                        Return SqlHelper.ExecuteDataset(strCN, "sp_getUserPerChat", intChatID)

                Catch ex As Exception
                        ' throw an exception so that it can be handled by the front end
                        Throw ex
                End Try
        End Function

        Function get_LatestMessages(ByVal intChatID As Integer, ByVal intMessageID As Integer) As DataSet

                Dim strSQL As String = "sp_getNewChatMessage"

                'Retrieve the parameters from the cache or from internal mechanism using SqlCommandBuilder
                Dim aParams() As SqlParameter = SqlHelperParameterCache.GetSpParameterSet(strCN, strSQL)

                'Set the parameter values
                aParams(0).Value = intChatID
                aParams(1).Value = intMessageID


                'Save the parameters so that they will be cached in a hash table for subsequent stored procedure calls
                SqlHelperParameterCache.CacheParameterSet(strCN, strSQL, aParams)

                'Execute the stored procedure to return the latest Chat Messages
                Return SqlHelper.ExecuteDataset(strCN, CommandType.StoredProcedure, strSQL, aParams)

        End Function


        Function AddNewChat(ByVal strChatDescription As String) As String

                'Execute the stored procedure to Insert the Chat Session and return the new Identity value for that chat
                Return SqlHelper.ExecuteScalar(strCN, CommandType.StoredProcedure, "sp_insChatSession", New SqlParameter("@chatDescription", strChatDescription))

        End Function

        Function LoginChatUser(ByVal strUserID As String, ByVal strPassWord As String) As SqlDataReader



                Dim strSQL As String = "sp_getChatUserByLogin"

                'Retrieve the parameters from the cache or from internal mechanism using SqlCommandBuilder
                Dim aParams() As SqlParameter = SqlHelperParameterCache.GetSpParameterSet(strCN, strSQL)


                'Set the parameter values
                aParams(0).Value = strUserID
                aParams(1).Value = strPassWord

                'Save the parameters so that they will be cached in a hash table for subsequent stored procedure calls
                SqlHelperParameterCache.CacheParameterSet(strCN, strSQL, aParams)

                'Execute the stored procedure to return the ChatUserRecord
                Return SqlHelper.ExecuteReader(strCN, CommandType.StoredProcedure, strSQL, aParams)

        End Function

        Function AddChatUser(ByVal strUserID As String, ByVal strPassWord As String, ByVal strFirstName As String, ByVal strLastName As String, ByVal strScreenName As String) As Integer



                Dim strSQL As String = "sp_insChatUser"

                'Retrieve the parameters from the cache or from internal mechanism using SqlCommandBuilder
                Dim aParams() As SqlParameter = SqlHelperParameterCache.GetSpParameterSet(strCN, strSQL)


                'Set the parameter values
                aParams(0).Value = strUserID
                aParams(1).Value = strPassWord
                aParams(2).Value = strFirstName
                aParams(3).Value = strLastName
                aParams(4).Value = strScreenName

                'Save the parameters so that they will be cached in a hash table for subsequent stored procedure calls
                SqlHelperParameterCache.CacheParameterSet(strCN, strSQL, aParams)

                'Execute the stored procedure to Insert the Chat Session and return the ID of the user
                Return SqlHelper.ExecuteScalar(strCN, CommandType.StoredProcedure, strSQL, aParams)

        End Function



        Function GetChatSessions(ByVal strSortField As String) As DataSet

                Dim strSQL As String = "Select * from ChatSessions"
                Dim strSortOrder As String = ""
                If Trim(strSortField) <> "" Then
                        strSQL &= " order by " & strSortField
                Else
                        strSQL &= " order by ChatSessionID desc"
                End If

                Return SqlHelper.ExecuteDataset(strCN, CommandType.Text, strSQL)

        End Function


        Function get_UserInfo(ByVal intUserID As Integer) As SqlDataReader

                'Return User information for the given UserID
                Return SqlHelper.ExecuteReader(strCN, "sp_getChatUser", intUserID)

        End Function



        Function DeleteSession(ByVal strSessionID As String)

                Return SqlHelper.ExecuteNonQuery(strCN, "sp_del_SessionUsers", strSessionID)

        End Function



#End Region




End Class
