Application Data Functions i.e. Data Manipulation Functions in .NET to SQL Server Database.

Hi,

Application Data Functions i.e. Data Manipulation Functions in .NET to SQL Server Database.

Below is class file AppDataFunctions.vb for Application data (DML) functions i.e. Fetch, Insert and Delete in .NET

AppDataFunctions.vb

Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Security.Cryptography
Imports System.Data
Imports System
Imports System.Configuration
Imports System.Text
Imports System.Net 'For Sending Mail  
Imports System.Net.Mail 'For Sending Mail

Public Class AppDataFunctions

    Private Con As SqlConnection

    Private strConStr As String = "Data Source=serverName;Initial Catalog=DBName;User ID=UserID;Password=Password"

    '######### Open Connection
    Public Function OpenConnection() As SqlConnection
        If Con Is Nothing Then
            Try
                Con = New SqlConnection(strConStr)
                Con.Open()
            Catch ex As Exception
                MsgBox(ex.GetBaseException)
            End Try
        End If
        Return Con
    End Function

    '######### Close Connection
    Public Function CloseConnection() As Object
        If Not Con Is Nothing Then
            Con.Close()
            Con = Nothing
        End If
        Return Nothing
    End Function


    '######### Get Record Value
    Public Function GetSingleEntity(ByVal SqlQuery As String, ByRef Entity As Object) As ErrorObject
        Dim bReturn As ErrorObject = New ErrorObject()
        Dim cmd As New SqlCommand
        Try
            cmd.CommandType = Data.CommandType.Text
            cmd.CommandText = SqlQuery
            cmd.CommandTimeout = 0
            cmd.Connection = OpenConnection()
            Entity = cmd.ExecuteScalar()
            cmd.Dispose()
            bReturn.ErrorType = ErrorObject.ErrorTypes.success
        Catch ex As Exception
            bReturn.ErrorType = ErrorObject.ErrorTypes.err
            bReturn.Description = ex.Message
        Finally
            CloseConnection()
        End Try
        Return (bReturn)
    End Function

    '######### Check Record Exists or Not
    Public Function CheckEntityExists(ByVal SqlQuery As String) As ErrorObject
        Dim bReturn As ErrorObject = New ErrorObject()
        Dim cmd As New SqlCommand
        Dim dr As SqlDataReader
        Try
            cmd.CommandType = Data.CommandType.Text
            cmd.CommandText = SqlQuery
            cmd.CommandTimeout = 0
            cmd.Connection = OpenConnection()
            dr = cmd.ExecuteReader
            If dr.Read() Then
                bReturn.ErrorType = ErrorObject.ErrorTypes.success
            Else
                bReturn.ErrorType = ErrorObject.ErrorTypes.err
                bReturn.ErrorMessage = "Record does not exist."
            End If
            dr.Close()
            cmd.Dispose()
        Catch ex As Exception
            bReturn.ErrorType = ErrorObject.ErrorTypes.err
            bReturn.Description = ex.Message
            bReturn.ErrorMessage = "Error reading record."
        Finally
            CloseConnection()
        End Try
        Return (bReturn)
    End Function

############# Fetch Record
    Public Function FetchRecord(ByVal SqlQuery As String, ByRef ds As Data.DataSet, ByRef RecordCount As Long) As ErrorObject
        Dim bReturn As ErrorObject = New ErrorObject()
        Dim da As SqlDataAdapter
        Dim cmd As New SqlCommand
        Try
            cmd.CommandType = Data.CommandType.Text
            cmd.CommandText = SqlQuery
            cmd.CommandTimeout = 0
            cmd.Connection = OpenConnection()
            da = New SqlDataAdapter(cmd)
            da.Fill(ds)
            RecordCount = ds.Tables(0).Rows.Count()
            da.Dispose()
            cmd.Dispose()
            bReturn.ErrorType = ErrorObject.ErrorTypes.success
        Catch ex As Exception
            bReturn.ErrorType = ErrorObject.ErrorTypes.err
            bReturn.Description = ex.Message
        Finally
            CloseConnection()
        End Try
        Return (bReturn)
    End Function

'########### Get a single record
    Public Function ExecuteDataSet(ByVal cmd As SqlCommand, ByVal ProcedureName As String) As DataSet
        Dim bReturn As ErrorObject = New ErrorObject()
        Dim da As SqlDataAdapter = Nothing
        Dim dsDataSet As New DataSet
        Try
            cmd.CommandType = Data.CommandType.StoredProcedure
            cmd.CommandText = ProcedureName
            cmd.Connection = OpenConnection()
            da = New SqlDataAdapter(cmd)
            da.Fill(dsDataSet)
            Return dsDataSet
            bReturn.ErrorType = ErrorObject.ErrorTypes.success
        Catch ex As Exception
            Throw ex
        Finally
            da.Dispose()
            cmd.Dispose()
            CloseConnection()
        End Try
    End Function

'########## Fetch record using Stored Procedure
    Public Function pFetchRecord(ByVal cmd As SqlCommand, ByVal ProcedureName As String, ByRef ds As Data.DataSet, ByRef RecordCount As Long, Optional ExceptionHandle As Boolean = False) As ErrorObject
        Dim bReturn As ErrorObject = New ErrorObject()
        Dim da As SqlDataAdapter
        Try
            cmd.CommandType = Data.CommandType.StoredProcedure
            cmd.CommandText = ProcedureName
            cmd.Connection = OpenConnection()
            cmd.CommandTimeout = 0
            da = New SqlDataAdapter(cmd)
            da.Fill(ds)
            RecordCount = ds.Tables(0).Rows.Count()
            da.Dispose()
            bReturn.ErrorType = ErrorObject.ErrorTypes.success
        Catch ex As Exception
            If ExceptionHandle = False Then
                bReturn.ErrorType = ErrorObject.ErrorTypes.err
                bReturn.Description = ex.Message
            Else
                Throw ex
            End If
        Finally
            CloseConnection()
        End Try
        Return (bReturn)
    End Function

'############ Get Single record by Stored Procedure
    Public Function ExcecuteScalar(ByVal cmd As SqlCommand, ByVal ProcedureName As String) As Object
        Try
            cmd.CommandType = Data.CommandType.StoredProcedure
            cmd.CommandTimeout = 0
            cmd.CommandText = ProcedureName
            cmd.Connection = OpenConnection()
            Dim obj As Object = cmd.ExecuteScalar()
            Try
                If Not IsDBNull(obj) Then
                    If obj <> Nothing Then
                        Return obj.ToString()
                    Else
                        Return Nothing
                    End If
                Else
                    Return Nothing
                End If
            Catch
                Return Nothing
            End Try

        Catch ex As Exception
            Throw ex
        Finally
            CloseConnection()
        End Try
        Return Nothing
    End Function

'########## Insert Record
    Public Function InsertRecord(ByVal SqlQuery As String, ByRef RecordsAffected As Long) As ErrorObject
        Dim bReturn As ErrorObject = New ErrorObject()
        Dim cmd As New SqlCommand
        Try
            cmd.CommandType = Data.CommandType.Text
            cmd.CommandText = SqlQuery
            cmd.CommandTimeout = 0
            cmd.Connection = OpenConnection()
            RecordsAffected = cmd.ExecuteNonQuery
            cmd.Dispose()
            bReturn.ErrorType = ErrorObject.ErrorTypes.success
        Catch ex As Exception
            bReturn.ErrorType = ErrorObject.ErrorTypes.err
            bReturn.Description = ex.Message
        Finally
            CloseConnection()
        End Try
        Return (bReturn)
    End Function


'######### Save Record
    Public Function SaveRecord(ByVal cmd As SqlCommand, ByVal ProcedureName As String, ByRef RecordsAffected As Long, Optional ExceptionHandle As Boolean = False) As ErrorObject
        Dim bReturn As ErrorObject = New ErrorObject()
        Try
            cmd.CommandType = Data.CommandType.StoredProcedure
            cmd.CommandText = ProcedureName
            cmd.CommandTimeout = 0
            cmd.Connection = OpenConnection()
            cmd.CommandTimeout = 0
            RecordsAffected = cmd.ExecuteNonQuery()

            'therapistID = int.Parse(cmdGetRecords.Parameters["@TherapistID"].Value.ToString());
            bReturn.ErrorType = ErrorObject.ErrorTypes.success
        Catch ex As Exception
            If ExceptionHandle = False Then
                bReturn.ErrorType = ErrorObject.ErrorTypes.err
                bReturn.Description = ex.Message
            Else
                Throw ex
            End If
        Finally
            CloseConnection()
        End Try
        Return (bReturn)
    End Function

'########## Update Record
    Public Function UpdateRecord(ByVal SqlQuery As String, ByRef RecordsAffected As Long) As ErrorObject
        Dim bReturn As ErrorObject = New ErrorObject()
        Dim cmd As New SqlCommand
        Try
            cmd.CommandType = Data.CommandType.Text
            cmd.CommandText = SqlQuery
            cmd.CommandTimeout = 0
            cmd.Connection = OpenConnection()
            RecordsAffected = cmd.ExecuteNonQuery()
            cmd.Dispose()
            bReturn.ErrorType = ErrorObject.ErrorTypes.success
        Catch ex As Exception
            bReturn.ErrorType = ErrorObject.ErrorTypes.err
            bReturn.Description = ex.Message
        Finally
            CloseConnection()
        End Try
        Return bReturn
    End Function


'########### Delete Record
    Public Function DeleteRecord(ByVal SqlQuery As String, ByRef RecordsAffected As Long) As ErrorObject
        Dim bReturn As ErrorObject = New ErrorObject()
        Dim cmd As New SqlCommand
        Try
            cmd.CommandType = Data.CommandType.Text
            cmd.CommandText = SqlQuery
            cmd.CommandTimeout = 0
            cmd.Connection = OpenConnection()
            RecordsAffected = cmd.ExecuteNonQuery()
            cmd.Dispose()
            bReturn.ErrorType = ErrorObject.ErrorTypes.success
        Catch ex As Exception
            bReturn.ErrorType = ErrorObject.ErrorTypes.err
            bReturn.Description = ex.Message
        Finally
            CloseConnection()
        End Try
        Return bReturn
    End Function

'######### Delete Record via Stored Procedure
    Public Function pDeleteRecord(ByVal cmd As SqlCommand, ByVal procedureName As String, ByRef RecordsAffected As Long) As ErrorObject
        Dim bReturn As ErrorObject = New ErrorObject()

        Try
            cmd.CommandType = Data.CommandType.StoredProcedure
            cmd.CommandText = procedureName
            cmd.CommandTimeout = 0
            cmd.Connection = OpenConnection()
            RecordsAffected = cmd.ExecuteNonQuery()
            cmd.Dispose()
            bReturn.ErrorType = ErrorObject.ErrorTypes.success
        Catch ex As Exception
            bReturn.ErrorType = ErrorObject.ErrorTypes.err
            bReturn.Description = ex.Message
        Finally
            CloseConnection()
        End Try
        Return bReturn
    End Function

    '###### Calling Reporting Services
    Public Function ReportServices(ByVal blnParamTlbr As Boolean, ByVal strReportName As String, ByVal ParamArray strParameters() As String) As String
        Dim strUrl As String
        Dim intCount As Integer

        strUrl = ConfigurationManager.AppSettings("ReportServer") + "?/ApplicationName_Reports/" + strReportName

        For intCount = 0 To strParameters.Length - 1 Step 2
            strParameters(intCount) = strParameters(intCount).Replace("&", "and")
            strParameters(intCount + 1) = strParameters(intCount + 1).Replace("&", "and")
            strUrl = strUrl + "&" + strParameters(intCount) + "=" + strParameters(intCount + 1)
        Next

        If blnParamTlbr Then
            strUrl = strUrl + "&rc:Toolbar=True&rc:Parameters=true&rs:Command=Render"
        Else
            strUrl = strUrl + "&rc:Toolbar=True&rc:Parameters=False&rs:Command=Render"
        End If
        Return strUrl
    End Function

'########## Popup Script
    'Public Function GetPopupScript(ByVal url As String) As String
    '    On Error Resume Next
    '    Dim strWindowSpecs = "width=1024,height=740,left=0,top=0,directories=no,menubar=no,toolbar=No,status=no,scrollbars=Yes,resizable=no,dependent=no,location=no,addressbar=no;"
    '    Return "var w = window.open(""" & url & """, null, """ & strWindowSpecs & """);"
    'End Function

    Public Function GetPopupScript(ByVal STRurl As String) As String
        'On Error Resume Next
        Return "{var wOpen; var sOptions; " & _
        " sOptions = 'status=no,menubar=no,scrollbars=no,resizable=no,toolbar=no,titlebar=no,location=no,directories=no';" & _
        " sOptions = sOptions + ',width=' + (screen.availWidth-15).toString();" & _
        " sOptions = sOptions + ',height=' + (screen.availHeight-58).toString();" & _
        " sOptions = sOptions + ',screenX=0,screenY=0,left=1,top=4';" & _
        " wOpen = window.open('" & STRurl & "', '_blank', sOptions ); wOpen.focus();}"
    End Function

'########### Populate Grid View
    Public Function PopulateFRRGrid(ByRef ddlcommand As String, ByRef ds As DataSet, ByRef recordCount As Integer) As ErrorObject

        Dim bReturn As ErrorObject = New ErrorObject()
        Dim cmd As New SqlCommand
        Dim da As SqlDataAdapter = Nothing
        Try
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "getFRRData"
            cmd.Connection = OpenConnection()
            Dim param As New SqlParameter("@command", ddlcommand.ToString())
            cmd.Parameters.Add(param)
            da = New SqlDataAdapter(cmd)
            da.Fill(ds)
            recordCount = ds.Tables(0).Rows.Count()
            bReturn.ErrorType = ErrorObject.ErrorTypes.success
        Catch ex As Exception
            bReturn.ErrorType = ErrorObject.ErrorTypes.err
            bReturn.Description = ex.Message
        Finally
            da.Dispose()
            cmd.Dispose()
            CloseConnection()
        End Try
        Return (bReturn)

    End Function

'########### Populate Grid View
    Public Function PopulateGridView(ByRef spName As String, ByRef ds As DataSet, ByRef recordCount As Integer) As ErrObject
        Dim param As SqlParameter = Nothing
        Dim cmd As SqlCommand = New SqlCommand()
        Dim da As SqlDataAdapter
        Dim bReturn As ErrorObject = New ErrorObject()
        Try
            cmd.CommandType = Data.CommandType.StoredProcedure
            cmd.CommandText = spName
            param = New SqlParameter("@command", "All")
            cmd.Parameters.Add(param)
            cmd.Connection = OpenConnection()
            da = New SqlDataAdapter(cmd)
            da.Fill(ds)
            If (ds.Tables(0).Rows.Count > 0) Then
                recordCount = ds.Tables(0).Rows.Count
            Else
                recordCount = 0
            End If
            cmd.Dispose()
            bReturn.ErrorType = ErrorObject.ErrorTypes.success
        Catch ex As Exception
            bReturn.ErrorType = ErrorObject.ErrorTypes.err
            bReturn.Description = ex.Message
        Finally
            CloseConnection()
        End Try
        Return Nothing
    End Function

End Class

'####### below sample code accessing one of above function in VB.NET Form

 Public Function AttachmentCount() As Integer
        Dim objAct As New AppDataFunctions, dsAct As New DataSet, cmdAct As New SqlCommand, lngRows As Long = 0
        Dim strQuery As String = "select * from CCAttachments where CNr='" & txtComplaintNo.Text & "'"
        objAct.FetchRecord(strQuery, dsAct, lngRows)
        If lngRows > 0 Then
            Return lngRows
        Else
            Return 0
        End If
    End Function