.NET [TUT] Connect and read data from a MySQL Database [CODE] - Printable Version

[.NET] [TUT] Connect and read data from a MySQL Database [CODE] - Printable Version

+- Hack Forums (https://hackforums.net)
+-- Forum: Tech (https://hackforums.net/forumdisplay.php?fid=444)
+--- Forum: Coding (https://hackforums.net/forumdisplay.php?fid=151)
+---- Forum: Software Development (https://hackforums.net/forumdisplay.php?fid=118)
+---- Thread: [.NET] [TUT] Connect and read data from a MySQL Database [CODE] (/showthread.php?tid=1382110)

[TUT] Connect and read data from a MySQL Database [CODE] - +Xenon - Jun 15, 2011

Ok guys recently I got incredibly into VB.NET plus MySQL coding. Hence the new username (it would just be MySQL but some skid already had that one :/)

So I have decided to make a thread about how to connect to and read data from a MySQL database. I will go in depth with the code and explain what each part means. Also, I just pulled an all-nighter, its 6:00 in the morning, so if some stuff is unclear, you know why ;)

INTRO

What is MySQL?

MySQL is the most widely used and extremely accessible database. It is used by nearly every website and is absolutely free. Heard of SQL injection? Get over it thats not what this thread is about. MySQL simply stores your data in an orderly way so that you can go back and retrieve it later. Its uses (on HF) are things such as websites, forums, booters, licensing systems, and much more.

How would you benefit from using a MySQL database in VB.NET?

Well first of all, it gives you a much higher level of security. I have already seen crypters and other paid programs use a form of MySQL as login systems. That is its most practical use here is seems, so we will focus on the login system.

How to Connect to a MySQL Database in Visual Basic 2010

PreRequisites:
-MySQL Data Connector (Download HERE)
-Visual Basic 2010 (or 2008 i don't think it matters)

So create a new project in VB. You can do with this information whatever you wish, if you are worthy of .NET coding, you will be able to figure many things out. ;)

Ok so after you have your project (you can make a login, whatever. I have 2 textboxes and a button. The textboxes are txtuser and txtpwd), you will obviously need a MySQL server (user db4free.net if you don't have a website, otherwise create one on your site and enable remote MySQL)
Now reference the .dll file provided

Code 
Imports MySQL.Data

Imports MySQL.Data.MySQLClient

Now you are ready to connect. This will be your "connection string". The connection string is like your front door, when you need to use it, open it. If you don't need it, close it.

Code 
Dim conn As New MySQLConnection 'Sets the variable conn to be a new mysql connection

conn.ConnectionString = "server=db4free.net(or your website); username=sqlusername; password=password; database=sqldatabase" ' this will connect you to your sql database

'Now we open the connection (since we need it), but we will check for failures

Try

     conn.Open

     MsgBox("Connection Successful")

Catch sqlerror As MySQLException

     MsgBox("Error Connecting to Database")

End Try

Basically what that does is set your credentials for your SQL server, then tries to open the connection. If the connection is successful, it displays a message box saying so. If not, it displays a message box saying it was unsuccessful.

Now, assuming you are using the login like i have, we can connect to the server, and send the username and password that is entered on the form to the server to get a reply.

Code 
'PUT YOUR CONNECTION STRING HERE, BUT DON'T OPEN THE CONNECTION'

Dim sqlAdapter As New MySQLAdapter 'This is the base for an interactive connection

Dim query = "SELECT * FROM users WHERE username='"+txtuser.text+"' AND pwd='"+txtpwd.text+"'"   'This will compare the user data from the input with the user data from the server by selecting any fields that contain the username AND password entered

Dim sqlcmd As New MySQLCommand 'Setting up the command that will use the query we just set up

sqlcmd.Connection = conn 'Tells what connection we are gonna use

sqlcmd.CommandText = query 'Tells what the command will execute

'Now we will send the query

sqlAdapter.SelectCommand = sqlcmd 'Says what command the adapter will use

Dim sqldata As MySQLDataReader 'We are setting up the data reader to get and send the info

sqldata.ExecuteReader 'Sends the query and reads the data

'Now we will see if the user existed, thus allowing login or not

If sqldata.HasRows = 0 Then

     MsgBox("Error Logging In!") 'If no rows contained the data specified, login is denied

Else

MsgBox("Login Successful!") 'The server contained matching data, so you may log in!

End If

Due to the varying usefulness of this thread, you may post any questions you may have. DO NOT hesitate to make a suggestion, I WILL add it! :D

Thank You for reading, I hope you learned a bit about MySQL and VB.NET today!

* - Cody8295 - Jun 15, 2011

How about some credits? http://www.vbmysql.com/articles/vbnet-mysql-tutorials/the-vbnet-mysql-tutorial-part-3

Also, use & to join strings, not +.

* - Apple - Jun 15, 2011

I've learned the hard way never to do this for applications you're releasing to others. Just a heads up

* - +Xenon - Jun 15, 2011

(Jun 15, 2011 03:29 AM)Cody8295 Wrote: How about some credits? http://www.vbmysql.com/articles/vbnet-mysql-tutorials/the-vbnet-mysql-tutorial-part-3

Also, use & to join strings, not +.

Although the code is not entirely mine, I do not actually recall visiting that site? I did get the connection string from a website but I have modified lots of different things (you will see that when I update the post with more features) to fit different functions.

Thanks for the heads up though

* - Redeemz - Jun 15, 2011

i need to know how to do this without a dll or maybe a built in one.

* - +Xenon - Jun 15, 2011

(Jun 15, 2011 11:49 AM)dwart420 Wrote: i need to know how to do this without a dll or maybe a built in one.

There is no way not do this without a .dll...