Tom's Subject Directory & City Building Fan Site

Visual Basic 2008 & Mysql Version 5

---

An Article and Tutorial:  Connecting to MySql

Part I: Introduction and Visual Studio 2008 Setup

 
This article covers connecting to a remote MySql database with a Visual Basic application. You will need;

• Visual Basic 2008 or higher. Lower versions will probably work with some modification.
• A host with a remote MySql database available to you and some knowledge of MySql (how to add database and tables).
• A remote MySql database server. Check with your web host or cPanel ^[1] as some don't allow remote connections. Also, those that do may differ in how you connect.
• ADO.NET Driver for MySQL (Connector/NET 6.2.4 recommended). This is a .NET Framework Class Library provided by MySQL. The class library is contained in the file MySql.Data.dll.
  

The following classes of Connector/NET will be used in this article:

• MySqlConnection: Represents an open connection to a MySQL Server database.
  
• MySqlCommand: Represents an SQL statement to execute against a MySQL database.
  
• MySqlDataAdapter: Represents a set of data commands and a database connection that are used to fill a data set and update a MySQL database.
  
• MySqlDataReader: Provides a means of reading a forward-only stream of rows from a MySQL database.

Introduction

There are multiple options available when working on a login system, but in this article we will build a login system similar to one you would find for a web site. Usernames and passwords will be stored in a MySQL table, and MySQL will handle authentication and privilege management. It can be assumed that registration has taken place on the web site and the application has been downloaded by the user. The application will act as a client program for connecting to a MySql server using user name and password and will require a remote MySql server ^[1]. After successful login, the program will retreive the user ID, which is the primary index for the user table.

Getting Started

This article will explain how to implement a user Client program (or login form) on some computer somewhere and login to a MySql database whose address you know and configuration you can control. A reference must be made to the MySql DLL (MySql.Data). MySQL Connector/Net is a fully-managed ADO.NET driver written in 100% pure C#. For more information about MySQL Connector/Net, see MySQL Connector/Net.

I have opened Visual Studio and given my project a name, "LoginToMySql". I placed two buttons, two Text Boxes with labels and a label for the status of the MySql connection. I named the buttons "Login" (to compare name & password from database) and "Cancel " (to close the form). The text boxes are named "txtUsername" and "txtPassword". The status label I named svrStatus, set its test properity as "Database Server Status", set AutoSize to "True" and set TextAlign properity to "MiddleCenter". There are several properties' you can change to add formatting and color to your form such as the medical staff icon I added to this form.

Building the UI (Form)

1. Create a new project. I named mine MySqlUserLogin

2. Add a MySQL library. Right-click on the project name (in this case MySqlUserLogin) and chose Add Reference.
   Select MySql.Data on .NET tab and cliclk Ok.

3. The reference library (MySQL.Data) won’t be copied to the output directory. That means when you deploy the application on another PC which doesn’t have the library installed, it’ll throw an error. To install and run on another machine, set the "Copy Local" property of the library file to True as follows;
   1. Click Show All Files icon.
   2. Expand References -> Select MySQL.Data -> Change "Copy Local" property to True.