Question How do I use the database?

[TF4 Programming]

Imports Microsoft.SqlServer

Public Class Form1
    'How do I use the SQL database?
End Class

I'm wondering what the class name in the SQL database DLL is. Can anyone help me?

 

[Kayot]

You need the connector found here for net. The way you add it to your project is by right clicking the main icon in the solution explorer, going to Refrences, clicking add, and selecting the library by browsing for a COM object. I suggest putting the file into a sub folder in the project. I call mine lib for library. After you've added it, find it in the list with the check boxes (Under the add button), find the ones with MySQL next to them and check them.

To use it in your project you have to add the following to the head of what ever form your using it on or made a modual that makes it public like so.

Module modCommon
    Public MySQLConnection As MySqlConnection
    Public MySQLReader As MySqlDataReader
End Module

To use it, you have to activate the reader or writter. I use a few subs, you'll have to take them apart to understand them.

    Public Sub SQLCommand(ByVal Value As String)
        Dim SQLCommand As New MySqlCommand(Value, MySQLConnection)
        SQLCommand.ExecuteNonQuery()
    End Sub

    Public Sub SQLReader(ByVal Value As String)
        MySQLReader = Nothing
        Dim SQLReader As New MySqlCommand(Value, MySQLConnection)
        MySQLReader = SQLReader.ExecuteReader()
    End Sub

If you plan to run the program for a long time, you'll have to set it up to close a connection after using a command, and to open one when starting a command. I've only just recently started doing that in PHP, so I don't have a VB example. For reading data, remeber to close the statement.

        SQLReader("SELECT * FROM tradeskill_recipe Where name like '%" & Replace(txtNameSearch.Text, "'", "''") & "%' Order by name;")
        While MySQLReader.Read()
                'Stuff is read here
        End While
        If Not MySQLReader Is Nothing Then MySQLReader.Close()

That last line closes the reader. Failing to do so will through up an error about already having an open connection.

To start a connection you need something like the following.

    Public Sub DatabaseConnect(ByVal ServerAdd As String, ByVal DBUserID As String, ByVal DBPassword As String, ByVal DBName As String, ByVal DBPort As String)
        If Not MySQLConnection Is Nothing Then If MySQLConnection.State = ConnectionState.Open Then MySQLConnection.Close()
        Try
            MySQLConnection = New MySqlConnection(String.Format("server={0};user id={1}; password={2}; database={3}; port={4};pooling=false", ServerAdd, DBUserID, DBPassword, DBName, DBPort))
            MySQLConnection.Open()
        Catch ex As MySqlException
            MsgBox("Error connecting to the server: " + ex.Message)
            End
        End Try
    End Sub

Again, break that down and try to understand it. This is all over welming at first, but with a ton of experimentaion you'll understand it. A tip that helped me is this little sub,

    Public Function ColumnReturn(ByVal Text As String) As String
        If Not MySQLReader.IsDBNull(MySQLReader.GetOrdinal(Text)) Then Return MySQLReader.GetString(MySQLReader.GetOrdinal(Text)) Else Return Nothing
    End Function

It allows a safe way to read column data based on the columns name rather than it's id.