calling function in class file using vb.net

Question

My database access methods are in class file. This file is has a separate project name: Usr_mysqladapter. In another website project I import the class file as a dll and call the function in the class file by creating an object.

The class file contains the following function:

Public Function readfunctioid(ByVal query As String) As DataTable
    Try
        connecttodb()
        mysqlcmd = New MySqlCommand(query, con)
        da = New MySqlDataAdapter(mysqlcmd)
        dt = New DataTable
        da.Fill(dt)
    Catch ex As Exception
        Throw ex
    Finally
        disconnect()
    End Try
    Return dt
End Function

The website projects imports the dll as follows:

  Imports Usr_mysqladapter

  Dim obj As Usr_mysqladapter.Class1 = New Usr_mysqladapter.Class1

My button click calls the function like this:

dt = obj.readfunctioid("select fld_functionid  from tbl_users_function_permission")

Unfortunately, I am getting error. How do I access the function in the class file from my website project?

Answer 1

I started this in the comments to the question, but let me show you the start of a greatly improved data access class:

Public Class SqlDataHelper

    Private Property ConnectionString() As String
         Get
            'return the connection string, perhaps read/cached from a config file
            'I'll often require this as an argument to the class constructor as well
         End Get
         'May not need a setter, depending on how you build this
    End Property

    Private Function GetConnection() As SqlConnection
         GetConnection = New SqlConnection(ConnectionString)
         GetConnection.Open()
    End Function

    Private Function GetDataTable(ByVal query As String, ByVal ParamArray args() As SqlParameter) As DataTable
       Dim ds As New DataTable
       Dim cmd As New SqlCommand(query, GetConnection())     
       If args IsNot Nothing Then
           For Each p As SqlParameter In args
               cmd.Parameters.Add(p)
           Next p
       End If

       Dim da As New SqlDataAdapter(cmd)
       da.Fill(dt)
       Return dt

    End Function

    Private Function UpdateData(ByVal command As String, ByVal ParamArray args() As SqlParameter) As Integer
       Using cn As SqlConnection = GetConnection(), _
             cmd As New SqlCommand(command, cn) 

           If args IsNot Nothing Then
               For Each p As SqlParameter In args
                   cmd.Parameters.Add(p)
               Next p
           End If

           Return cmd.ExecuteNonQuery()
    End Function

    Private Function GetSingleValue(ByVal query As String, ByVal ParamArray args() As SqlParameter) As Object
       Using cn As SqlConnection = GetConnection(), _
             cmd As New SqlCommand(query, cn) 

           If args IsNot Nothing Then
               For Each p As SqlParameter In args
                   cmd.Parameters.Add(p)
               Next p
           End If

           Return cmd.ExecuteScalar()
    End Function

    Public Function ReadFunctionID() As DataTable  
        Return GetDataTable("SELECT fld_functionid FROM tbl_users_function_permission", Nothing)
    End Function

    Public Function ReadFunctionIDByUser(ByVal UserID As Integer) As DataTable
         Return GetDataTable("SELECT fld_functionid FROM tbl_users_function_permission WHERE UserID = @UserID", _
                    New SqlParameter("@UserID", UserID))
    End Function
End Class

Let's take a closer look at some of the improvements in this code. First of all, notice what is Public vs what is Private. At no point do you ever want to accept an ad hoc sql string from the presentation tier. That doesn't mean you can't use ad hoc sql, just that you want to keep your data and presentation sections separate.

Take a look at how the final two functions in the class handle parameter data. The fact that you need to use an explicit Nothing when not using any parameters is a feature of this code. It's a constant reminder that you should be using query parameters. If you don't know what sql injection is, and how to prevent it, you shouldn't be writing database code!

Hopefully this is helpful to you.

Answer 2

In your website project simply do like.....

Dim MyCustomSqlAdapter As = New MyCustomClass()

This is when your class is CALLED MyCustomClass And the INSTANCE is called MyCustomSqlAdapter

Then in button do like....

dt = MyCustomSqlAdapter.readfunctioid("select fld_functionid  from tbl_users_function_permission")