Clsadmin Class

'======================Created By dinesh for process related to admin and its functions Dated on 13-3-08====================
Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports Microsoft.VisualBasic
Imports System.Net.Mail

Public Class ClsAdmin
Private MyConnection As New SqlConnection
Private MyCommand As SqlCommand
Private MyReader As SqlDataReader
'this function is used to open connection.
Public Sub OpenConnection()
If MyConnection.State = ConnectionState.Open Then
MyConnection.Close()
End If
MyConnection.ConnectionString = ConfigurationManager.AppSettings("connectstring")
MyConnection.Open()
MyCommand = New SqlCommand()
MyCommand.Connection = MyConnection
End Sub
'this function is used to relese connection from memory.
Public Sub DisposeConnection()
If MyConnection IsNot Nothing Then
MyConnection.Close()
MyConnection.Dispose()
MyConnection = Nothing
End If
End Sub
'this login function is made by dinesh on 13-Mar-2008
'This function varify the client login.
'Used variables are username and password for passing the client login info.
'This function return the string value after varified the user login.
Function AdmAuthentication(ByVal username As String, ByVal password As String) As String
Dim nID As Integer
Dim admName As String
Try
OpenConnection()
MyCommand = New SqlCommand("procAdminloginSelect", MyConnection)
MyCommand.CommandType = Data.CommandType.StoredProcedure
MyCommand.Parameters.Add(New SqlParameter("@vadminname", Data.SqlDbType.VarChar)).Value = username
MyCommand.Parameters.Add(New SqlParameter("@vadminpassword", Data.SqlDbType.VarChar)).Value = password
MyReader = MyCommand.ExecuteReader()
If MyReader.Read() = True Then
admName = MyReader("vadminname")
nID = 1
Else
nID = -1
End If
MyReader.Close()
Return nID & "," & admName
Catch ex As Exception
Return -1
End Try
End Function
'this forget password function is made by dinesh on 13-Mar-2008
'This function varify the client password.
'This function return the integer value after varified the user login.
Function Admcheckdata(ByVal AdminId As String) As String
Dim strArr As String
Try
OpenConnection()
MyCommand = New SqlCommand("ProcAdminloginByUserIdSelect", MyConnection)
MyCommand.Parameters.Add(New SqlParameter("@adm_userid", Data.SqlDbType.VarChar, 50))
MyCommand.Parameters("@adm_userid").Value = (AdminId.Replace("'", "''")).Trim()
MyCommand.CommandType = Data.CommandType.StoredProcedure
MyReader = MyCommand.ExecuteReader()
If (MyReader.Read()) Then
strArr = MyReader("uname")
Else
strArr = Nothing
End If
Catch ex As Exception
End Try
Return strArr
End Function
'this SecretQuestion function is made by dinesh on 13-Mar-2008
'This function varify the Secretd Question.
'This function return the string value after varified the Secretd Question.
Function selectQustion() As String
Dim SecretQuestion As String
Try
OpenConnection()
MyCommand = New SqlCommand("ProcAdminloginSelectAll", MyConnection)
MyCommand.CommandType = Data.CommandType.StoredProcedure
MyReader = MyCommand.ExecuteReader()
MyReader.Read()
Dim qid As Integer
qid = CInt(MyReader("nquestionID"))
OpenConnection()
MyCommand = New SqlCommand("procSecretQuestionsByidSelect", MyConnection)
MyCommand.Parameters.Add(New SqlParameter("@Id", SqlDbType.Int, 4)).Value = qid
MyCommand.CommandType = Data.CommandType.StoredProcedure
MyReader = MyCommand.ExecuteReader
If MyReader.Read() Then
SecretQuestion = MyReader("vSecretQuestion")
End If
Catch ex As Exception
End Try
Return SecretQuestion
End Function
'this sendMail function is made by dinesh on 13-Mar-2008
'This function return the string value after varified the sendMail.
Function sendMail(ByVal SecretWord As String, ByVal MapPath As String)
Dim msg
Try
OpenConnection()
MyCommand = New SqlCommand("procAdminloginByqustionIdSelect", MyConnection)
MyCommand.Parameters.Add(New SqlParameter("@admans", SqlDbType.VarChar, 50)).Value = SecretWord
MyCommand.CommandType = Data.CommandType.StoredProcedure
MyReader = MyCommand.ExecuteReader
If (MyReader.Read()) Then
Dim strpassword = MyReader("vadminpassword")
Dim stremail = MyReader("vadmemail")
Dim struid = MyReader("vadminname")
Dim adminname = MyReader("uname")
'=====================EMAIL======================================================================
Dim filename = MapPath & "\admin_email_temp.htm"
Dim MailMsg As String = ""
Dim MailSubject As String = "Your Password at standard."
Dim fromEmail As String = "Admin@computerpundit.COM"
Dim oRead As System.IO.StreamReader
oRead = System.IO.File.OpenText(filename)
While oRead.Peek <> -1
MailMsg = MailMsg & oRead.ReadLine()
End While
oRead.Close()
MailMsg = Replace(MailMsg, "Name1", adminname)
MailMsg = Replace(MailMsg, "userid", struid)
MailMsg = Replace(MailMsg, "pwd", strpassword)
If SendEmailWithOutTemplate(fromEmail, stremail, MailSubject, MailMsg) = 1 Then
msg = "Your User Name and Password has been sent to your email."
End If
'==============================================================================================
Else
msg = "Invalid Answer."
End If
Return msg
Catch ex As Exception
End Try
End Function
'this AdminUpdateMysettings function is made by dinesh on 13-Mar-2008
'This function return the string value after varified the updation.
Function AdminUpdateMysettings(ByVal txtfirstname, ByVal txtlastname, ByVal txtemail, ByVal txtuid, ByVal txtcpwd, ByVal ddlqustion, ByVal txtans) As String
Dim msg
Try
OpenConnection()
MyCommand = New SqlCommand("procAdminloginUpdate", MyConnection)
MyCommand.CommandType = Data.CommandType.StoredProcedure
MyCommand.Parameters.Add("@vadmfname", SqlDbType.VarChar, 50).Value = txtfirstname
MyCommand.Parameters.Add("@vadmlname", SqlDbType.VarChar, 50).Value = txtlastname
MyCommand.Parameters.Add("@vadmemail", SqlDbType.VarChar, 75).Value = txtemail
MyCommand.Parameters.Add("@vadminname", SqlDbType.VarChar, 75).Value = txtuid
MyCommand.Parameters.Add("@vadminpassword", SqlDbType.VarChar, 20).Value = txtcpwd
MyCommand.Parameters.Add("@nquestionID", SqlDbType.Int, 9).Value = ddlqustion
MyCommand.Parameters.Add("@vadmans", SqlDbType.VarChar, 50).Value = txtans
MyReader = MyCommand.ExecuteReader
msg = "Your account has been updated successfully."
Catch ex As Exception
End Try
Return msg
End Function
Function SendEmailWithOutTemplate(ByVal fromemail As String, ByVal toemail As String, ByVal subject As String, ByVal msg As String) As Integer
Try
Dim mm As New System.Net.Mail.MailMessage(fromemail, toemail)
Dim smtp As New System.Net.Mail.SmtpClient()
mm.Subject = subject
mm.Body = msg
mm.IsBodyHtml = True
smtp.Host = "mail.epsilonium.com"
smtp.Send(mm)
Return 1
Catch ex As Exception
Return 0
End Try
End Function
'this function is used to get data by datareader.
Public Function GetDataReader(ByVal SqlQuery As String) As SqlDataReader
OpenConnection()
MyCommand.CommandType = CommandType.StoredProcedure
MyCommand.CommandText = SqlQuery
MyReader = MyCommand.ExecuteReader()
Return MyReader
End Function
Public Function ExecuteStoredProcreturnvalue(ByVal strProcName As String, ByVal arProcParams() As System.Data.SqlClient.SqlParameter) As Integer
OpenConnection()
Dim i As Integer
Dim param As System.Data.SqlClient.SqlParameter
Try
MyCommand.CommandText = strProcName
MyCommand.CommandType = CommandType.StoredProcedure
MyCommand.Parameters.Clear()
For Each param In arProcParams
MyCommand.Parameters.Add(param)
Next
MyCommand.Parameters.Add("@retval", SqlDbType.Int)
MyCommand.Parameters("@retval").Direction = ParameterDirection.ReturnValue
MyCommand.ExecuteNonQuery()
ExecuteStoredProcreturnvalue = MyCommand.Parameters("@retval").Value ' IIf(i >= 0, i, -1)
Catch ex As System.Exception
'ErrorTrack(ex.Message)
End Try
End Function
'this function is used to get data by datareader.
Public Function GetDataset(ByVal SqlQuery As String) As DataSet
OpenConnection()
Dim da As SqlDataAdapter
Dim ds As New DataSet
MyCommand.CommandType = CommandType.StoredProcedure
MyCommand.CommandText = SqlQuery
Try
da = New SqlDataAdapter(MyCommand)
da.Fill(ds, "Table1")
Catch ex As Exception
ex.ToString()
End Try
Return ds
End Function
'For Edit Case..................
Public Function SelectAll(ByVal query As String, ByVal id As Integer) As DataSet
OpenConnection()
MyCommand = New SqlCommand(query, MyConnection)
MyCommand.CommandTimeout = 450
MyCommand.CommandType = Data.CommandType.StoredProcedure
MyCommand.Parameters.Add("@userid", SqlDbType.Int).Value = id
Dim ds As New DataSet
Dim da As New SqlDataAdapter
da.SelectCommand = MyCommand
da.Fill(ds, "tblcat")
Return ds
End Function
Public Function UserAuthentication(ByVal Username As String, ByVal Password As String) As String
Dim uname As String
OpenConnection()
MyCommand = New SqlCommand("procUserDetailByEmailidSelect", MyConnection)
MyCommand.CommandType = CommandType.StoredProcedure
MyCommand.Parameters.Add("@Username", SqlDbType.VarChar).Value = Username
MyCommand.Parameters.Add("@Password", SqlDbType.VarChar).Value = Password
Try
MyReader = MyCommand.ExecuteReader
If MyReader.Read() Then
uname = MyReader("vFirstName")
End If
MyReader.Close()
Return uname
Catch ex As Exception
End Try

End Function
End Class