• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 474
  • Last Modified:

VB .NET connection to database

Pretty much all my pages interact with a mysql database, my connection is fine and working perfectly, however Im finding that there is a lot of repetitve code everytime I need to connect, I thinking more of creating an object to do this, as I am more a php programmer, Im not quite sure how to do this in .NET at the same time, I have in my menu items a setup which calls a windows form, I would like to be able to define the database connection credentials here (db Server address, db Name, db user, db password), so that they can be changed if needed and be unique to each user im guessing to store them in a text file or something you may suggest better.

Thanks
0
prowebinteractiveinc
Asked:
prowebinteractiveinc
  • 6
  • 3
1 Solution
 
Matthew KellyCommented:
As far as the repetitive connection code, best thing to do is make a Database class that you pass the SQL text and an enumeration as to which database to connect to and let the class do all the connection handling, etc.

As far as defining credentials that can be changed, you can save and retrieve values from the app.config: http://www.codeproject.com/Articles/14744/Read-Write-App-Config-File-with-NET-2-0

For security, more information on encrypting the strings here: http://msdn.microsoft.com/en-us/library/89211k9b(v=vs.80).aspx
0
 
prowebinteractiveincAuthor Commented:
I am not asking for you to build my whole program, but maybe someone can guide me on the class for connecting to the mysql database. I have tried to start it but im having issues... lol

This is what I have:

Imports MySql.Data.MySqlClient
Public Class databaseConnection

    Dim MysqlConn As MySqlConnection
    MysqlConn = New MySqlConnection()
    Dim dr As MySqlDataReader
    Dim cmd As New MySqlCommand


        MysqlConn.ConnectionString = "server=dbserver.com;" _
            & "user id=vbnet;" _
            & "password=dbpassword;" _
            & "database=admin"

        Try
            MysqlConn.Open()

            cmd.CommandText = "SELECT m.merchantId, m.companyName, p.firstName, p.lastName FROM profiles As p LEFT JOIN merchants AS m ON p.merchantId=m.merchantId WHERE p.username = ?UserName AND p.password = ?Password"
            cmd.Parameters.Add(New MySqlParameter("?MID", txtMID.Text))
            cmd.Parameters.Add(New MySqlParameter("?UserName", txtUserName.Text))
            cmd.Parameters.Add(New MySqlParameter("?Password", txtPassword.Text))
            cmd.Connection = MysqlConn
            dr = cmd.ExecuteReader

            If dr.HasRows() Then
                dr.Read()

            Else


            End If

            MysqlConn.Close()
        Catch myerror As MySqlException
            MessageBox.Show("Cannot connect to database: " & myerror.Message)
        Finally
            MysqlConn.Dispose()

        End Try
End Class

Open in new window

0
 
prowebinteractiveincAuthor Commented:
Also Im not quite sure what lines 19-21 are neccessary in here, please help me out.

Thanks in advance
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
Matthew KellyCommented:
Lines 19-21 are a parameter query: http://visualbasic.about.com/od/learnvbnet/a/begdbapp9.htm

You can make this way more object oriented, but something like below. For example for our database classes we have DataReader return a "DataSet" object which we can then use as a DataSet in the code. You could also have it return a DataView, etc. Below code would assume your code would just be using the MySqlDataReader to parse our the returns.

If you have multiple databases, you can pass an enumeration to OpenConnection/CloseConnection, etc.

How generic/object oriented you make it depends on what you need it to do.

Your code would look like this:
Dim db As New databaseConnection()
db.OpenConnection()
Dim cmd As New MySqlCommand
cmd.CommandText = "SELECT m.merchantId, m.companyName, p.firstName, p.lastName FROM profiles As p LEFT JOIN merchants AS m ON p.merchantId=m.merchantId WHERE p.username = ?UserName AND p.password = ?Password"
            cmd.Parameters.Add(New MySqlParameter("?MID", txtMID.Text))
            cmd.Parameters.Add(New MySqlParameter("?UserName", txtUserName.Text))
            cmd.Parameters.Add(New MySqlParameter("?Password", txtPassword.Text))

Dim dr MySqlDataReader = DataReader(cmd)

'Use values from dr
If dr.HasRows Then

Else

End If

db.CloseConnection()

Open in new window


The database class would be something like this
Imports MySql.Data.MySqlClient
Public Class databaseConnection

    Dim MysqlConn As MySqlConnection
    MysqlConn = New MySqlConnection()
    
    Dim cmd 

   Public Sub CloseConnections()
      MysqlConn.Close()
  End Sub

   Public Sub OpenConnection()

        MysqlConn.ConnectionString = "server=dbserver.com;" _
            & "user id=vbnet;" _
            & "password=dbpassword;" _
            & "database=admin"

        Catch myerror As MySqlException
            MessageBox.Show("Cannot connect to database: " & myerror.Message)

        End Try

     End Sub

     'Returns  a MySqlDataReader object, used for SELECT statements
     Public Function DataReader(ByVal cmd As MySqlCommand) As MySqlDataReader
            cmd.Connection = MysqlConn
            Return cmd.ExecuteReader
     End Sub

     ' Returns number of rows affected, used for UPDATE, DELETE, and INSERT statements
     Public Function ExecuteNonQuery(ByVal cmd As MySqlCommand) As Integer
            cmd.Connection = MysqlConn
            Return cmd.ExecuteNonQuery()
     End Sub
End Class

Open in new window

0
 
Matthew KellyCommented:
And of course, as linked above, you would change the OpenConnection to use appSettings for the connection string.
0
 
prowebinteractiveincAuthor Commented:
matthewstevenkelly

I tried your code and I get an error. I tried to figure it but can't

InvalidOperationException was unhandled
return cmd.ExecuteReader
your line 30 just above

I also took your line 5 and placed it on line 14

Thanks
0
 
prowebinteractiveincAuthor Commented:
just in case Im adding the code calling the db connection

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim db As New databaseConnection()
        db.OpenConnection()

        Dim cmd As New MySqlCommand
        cmd.CommandText = "SELECT companyName FROM merchants WHERE merchantId= " & Form1.mMerchantId


        Dim dr As MySqlDataReader = db.DataReader(cmd)

        'Use values from dr
        If dr.HasRows Then
            MessageBox.Show(dr(0))
        Else

        End If

        db.CloseConnection()
    End Sub

Open in new window

0
 
prowebinteractiveincAuthor Commented:
I did try and catch, and Im getting that the connection must be valid and open ?

    'Returns  a MySqlDataReader object, used for SELECT statements
    Public Function DataReader(ByVal cmd As MySqlCommand) As MySqlDataReader
        Try
            cmd.Connection = MysqlConn
            Return cmd.ExecuteReader
        Catch myerror As Exception
            MessageBox.Show(myerror.Message)
        End Try
    End Function

Open in new window

0
 
prowebinteractiveincAuthor Commented:
all that was missing was MysqlConn.Open()  duh !

Thanks for your help !
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now