[RESOLVED] How can i do this...

I've got a big database driven project....15 forms 50+ input boxes and im wondering how to do it effieciently



I've got code to connect to the mysql databse but i dont want to put it inside each form over and over for every form

How can i do it so that i only have to clal one method to connect to the db or leave the db connected constantly from the start of the app. here is the code that i got to put in every form -_-

Code:

---

  Dim conn = New MySqlConnection()

        conn.ConnectionString = "server=localhost;" & "user id=root;" & "password=;" & "database=project"

        Try
            conn.Open()
            MessageBox.Show("worked")

        Catch myerror As MySqlException

            MessageBox.Show("Error Connecting to Database: " & myerror.Message)
        Finally
            'conn.Dispose()
        End Try

        Dim myAdpter As New MySqlDataAdapter
        Dim myCommand As New MySqlCommand
        Dim sqlquery As String
        sqlquery = "insert into Users values (14,'moe','moe70','123456',1);"
        myCommand.Connection = conn
        myCommand.CommandText = sqlquery
        myAdpter.SelectCommand = myCommand
        Dim myData As MySqlDataReader
        myData = myCommand.ExecuteReader()
        If myData.HasRows = 0 Then
            TextBox2.Text = "no results"
        Else
            TextBox2.Text = myData.ToString


        End If

---

For ADO.Net the recommedation is to open the connection as late as posible and close as early as possible. To do that I create a class for database access then call that class when needed. The class will connect query the db , close the connection and pass the result back to the calling location.

As Gary says. You might have a method named GetDataTable that accepts a String containing a SQL query or a sproc name. Internally, the method will create a Connection and a Command, open the Connection, execute the Command to create a DataReader, load the DataReader into a DataTable and return it. Because you would use Using blocks to create the data access objects, they will all be disposed automatically. E.g.

vb.net Code:

Public Class DataAccessManager
 
    Public Function GetDataTable(ByVal query As String) As DataTable
        Using connection As New MySqlConnection(Me._connectionString)
            Using command As New MySqlCommand(query, connection)
                connection.Open()
 
                Using reader As MySqlDataReader = command.ExecuteReader()
                    Dim table As New DataTable
 
                    reader.Load(table)
 
                    Return table
                End Using
            End Using
        End Using
    End Funtion
 
End Class

Note, for action statements like INSERT you'd need to be able to pass the query parameter values to the method too.

BTW, why are you using a DataReader with an INSERT statement? As the name suggests, a DataReader reads data. An INSERT statement doesn't produce any data to read. DataReaders are for queries, i.e. SELECT statements. ExecuteNonQuery is for action statements.

Thank you both. this is abit complicated. Luckily I found out how to use the dataset, database amanger etc in vb.