Public MySQL Connection

[TGrahmann]

Hi, I'm fairly new to VB.net, but I'm curious if it's possible to create a MySQL Connection and make it Public/Global to where multiple Subs/source files could use it. Any solutions would be greatly appreciated!
Thanks,
Ty

 

[jebenson]

Yes it is. Right-click the project in Solution Explorer, then select Add->Module. Give the module a name and click Add. You can the create the connection object there, as Public, and it will be visible to the entire project. When referencing global variables in modules, I like to include the module name (e.g., Module1.Connection1), even though the module name is not required. It just keeps things clear, that the connection is defined in a module.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[JRB-Bldr]

This is just one way of doing this. There are a number of ways to approach what you want.
Generally I create a new VB.Net App_code module containing a Class named:

Public Class DataClass​

And within that class I have 3 separate Functions to use anywhere in my code;
1) GetDataSet(ByVal SQLQueryStr as String) ' <-- Returns Records from Database
2) ExecuteNonQuery(ByVal SQLQueryStr as String) ' <-- Runs Updates/Deletes/Etc. not requiring Record(s) Return
3) GetDataReader(ByVal SQLQueryStr As String) ' <-- Used as part of Gridview ObjectDataSource SelectMethod

Each of those contains the appropriate Connect String, but in addition each one is used for different types of SQL Operations against a 'backend' database.

The Functions are then called anywhere within the application
Example:

' --- Return a set of Records ---
 Dim ds As DataSet
 Dim dt As DataTable
 ds = DataClass.GetDataSet(<SQL Query String>)
 dt = ds.Tables(0)
 If dt.Rows.Count >= 1 Then
   < do something with the record(s) >
 Else
   ' --- No Records Returned ---
 Endif

That way not only the Connection string is 'seen' as public, but also all the other code necessary to perform the desired operation - which is often used multiple times within a given application.

Good Luck,
JRB-Bldr

 

[TGrahmann]

Thank you both so much for your great help!
-Ty

 

[TGrahmann]

Hey guys, I tried that module method. I get an error when compiling. I'm going to post my code any maybe someone can tell me where I'm going wrong? Thanks in advance!

**the variable 'Mnemonic' in this case is defined by a form prior to referencing this module

Imports MySql.Data.MySqlClient

Public Module CLETSConnect

    Dim StatColor As Color
    Dim ConnStr As String

    Public Sub ConnCLET()
        Dim cletconn As MySqlConnection
        cletconn = New MySqlConnection()

        cletconn.ConnectionString = "server=localhost;user id=root;password=rootpw;database=" & Mnemonic

        Try
            cletconn.Open()
            StatColor = Color.Green

            ConnStr = "CLETS Connected"
            cletconn.Close()
        Catch errors As MySqlException
            StatColor = Color.Red

            ConnStr = "CLETS NOT CONNECTED"


        End Try


    End Sub
End Module

 

[JRB-Bldr]

The following is an example of one of the Connections that I talk about above.
Note that within the calling code, I pass the SQL Query String.

Public Class DataClass
    Public Shared Function GetDataReader(ByVal sql As String) As Data.OleDb.OleDbDataReader
        Dim cDataPath As String = ""
        Dim Conn As New System.Data.OleDb.OleDbConnection
        Dim Cmd As New System.Data.OleDb.OleDbCommand
        Dim reader As System.Data.OleDb.OleDbDataReader = Nothing
        GetDataReader = Nothing

        Try
            cDataPath = [u]< UNC and/or fully pathed String defining where to find data >[/u]
            ' --- Use cDataPath in the following Connection String ---
            Conn.ConnectionString = [u]< Your specific MySQL Connection String >[/u]

            Cmd.Connection = Conn
            Cmd.CommandText = sql  ' --- Passed as Parameter to this Class's Function --
            Cmd.Connection.Open()

            GetDataReader = Cmd.ExecuteReader

        Catch ex As Exception
            ' --- Close/Clear Open Objects ---
            GetDataReader = Nothing
            Conn.Close()
            Conn.Dispose()
            HttpContext.Current.Session("sql") = Nothing
            ' --- Write out Error to Log File ---
            ' --- Examine  [u]ex.Message[/u]   to understand error specifics ---
            LogError("GetDataReader:" & ex.Message & vbCrLf & ex.Source & ex.StackTrace & vbCrLf & " DataPath: " & cDataPath & vbCrLf & " SQL:" & sql)

        End Try
        HttpContext.Current.Session("sql") = Nothing
    End Function

End Class

Once the

GetDataSet(<SQL Query String>)

is executed and no error encountered, it brings back a Data Table ( dt ) which may have Rows in it ((assuming that the SQL Query found something) or not.

Also you indicate

the variable 'Mnemonic' in this case is defined by a form prior to referencing this module

but have you confirmed that it is truly being 'seen' by this Function so as to result in an accurate MySQL Connection String?

Creating a MySQL Connector
MySQL Connection Strings

Good Luck,
JRB-Bldr