Binding data to a DataGridView

I have a bit of code which loads data from a stored procedure in MS SQL Server and then loads the data into a DataGridView, which works fine. What I want is for the code that connects / loads the data to sit in my Database class and then everything associated with the DataGridView to be stored in my Form but I am having problems passing the contents of the BindingSource over to the Form from the Database class.

Form1 code:

Public Class Form1

    Dim myDatabaseObj As New Class1()
    Dim bindingSource1 As New BindingSource()
    Dim connectString As New SqlConnection
    Dim objDataAdapter As New SqlDataAdapter
    Dim table As New DataTable()
    Dim tabletest As New DataTable()

    Private Sub loadCompanyList()
        Try
            Me.dgv_CompanyList.DataSource = Me.bindingSource1
            getCompanyList()

        Catch ex As NullReferenceException
        End Try
    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        loadCompanyList()
    End Sub

End Class

When I try place the getCompanyList() in a class and then create a new object that references the Form() it does not seem to return any value from the table to the MyForm.BindingSource1.Datasource meaning my DataGridView displays no data.

Database class code:

.....
Private Sub getCompanyList()
    Try

        Dim myForm as new Form()

        connect_Transaction_Database()
        objDataAdapter.SelectCommand = New SqlCommand()
        objDataAdapter.SelectCommand.Connection = connectString
        objDataAdapter.SelectCommand.CommandText = "sp_GetCompanyList"
        objDataAdapter.SelectCommand.CommandType = CommandType.Stored开发者_如何学PythonProcedure

        Dim commandBuilder As New SqlCommandBuilder(Me.objDataAdapter)

        Dim table As New DataTable()
        table.Locale = System.Globalization.CultureInfo.InvariantCulture
        Me.objDataAdapter.Fill(table)
        **MyForm.bindingSource1.DataSource = table**

    Catch ex As DataException
        MsgBox(ex.Message)
    Catch ex As NullReferenceException
        MsgBox(ex.Message)
    End Try

    disconnect_Transaction_Database()

End Sub

---

^{This question has just been bumped by the Community user so I thought I would provide an answer which may help others in the future.}

Firstly, I would consider implementing Using:

Managed resources are disposed of by the .NET Framework garbage collector (GC) without any extra coding on your part. You do not need a Using block for managed resources. However, you can still use a Using block to force the disposal of a managed resource instead of waiting for the garbage collector.

Secondly, you don't need to use a SqlDataAdapter. Instead you can load a DataTable using the SqlCommand class and return that. I would also contain the SqlConnection in the method rather than having it opened and closed in a different one.

Your code would look something like this:

Form1 code:

Public Class Form1

    Private Sub loadCompanyList()
        Dim myDatabase As New Database
        Me.dgv_CompanyList.DataSource = myDatabase.getCompanyList()
    End Sub

End Class

Database code:

Public Class Database

    Public Function getCompanyList() As DataTable

        Dim dt As New DataTable

        Using con As New SqlConnection(connectionString),
              cmd As New SqlCommand("sp_GetCompanyList", con) With {.CommandType = CommandType.StoredProcedure}

            con.Open()

            dt.Load(cmd.ExecuteReader())

        End Using

        Return dt

    End Function

End Class

---

You want getCompanyList to be a function, which returns a DataTable. Then, forget the BindingSource (if the DataGridView is read-only) and set the DataSource property to the function:

Me.dgv_CompanyList.DataSource = getCompanyList

---

I would suggest you make the GetCompanyList method into a Function that returns the DataTable filled by the SqlDataAdapter. There's no real reason this Sub should have a dependency on the form. Instead a method in the form could call this to get the DataTable and then perform the binding.

Public Function GetCompanyList() As DataTable
...
...
   Dim table As New DataTable()
   table.Locale = System.Globalization.CultureInfo.InvariantCulture
   Me.objDataAdapter.Fill(table)
   Return table
...
...