Connect MYSQL database with VB.Net in code
This is about how to connect MYSQL database with VB.Net in code and display the records from Mysqldatabase table in a DataGridview on the form.All you need is VB.net , Mysql Server and mysql-connector-net.
ADO.NET
ADO.NET is designed for a disconnected architecture. In ADO.Net the applications connected to the database to retrieve data and store in Memory and then disconnect from the database.
The Data in the Memory can be manipulated and if database needs to be updated with the changes made in memory, a new connection is to be established and then the database is updated.
The disconnected architecture allows your application to scale up means the database will perform just as well supporting hundreds of users as it does to a single user.
ADO.Net Namsespaces:- The core of ADO.Net classes exists in the System.Data namespace which in turn contains many child namespaces like System.Data.SQLclient, System.Data.Mysqlclient, System.Data.OLEDB etc.
All these child namespaces are known as Data Providers in ADO.Net.
Since we are going to fetch records from Mysql database and display them in a Datagridview on the form, so we shall use the following classes of ADO.Net.
1. Mysqlconnection
2. MysqlDataadapter
3. Dataset
4. Mysqlcommand
5. CommandText
MySQLconnection: - is used to establish and maintain a connection to the data source (MySQL server in this case).
DataSet: - It is a cache of Data retrieved from the Database.
DataAdapter: - It acts as a bridge between DataSet and DataSource. It connects DataSet and DataSource by using Fill Method to load data from the DataSource into the Dataset. If the changes made by the user in the Dataset needs to be send back to the DataSource we can use update Method of DataAdapter.
SQLCommand: - It is executed against a Data Store. This command can be INSERT, DELETE, UPDATE, SQL STRING OR STORED PROCEDURES.
CommandText: - This specifies the SQL string or Stored Procedure to be executed.
Now the Code to Display the records from Mysql Database Table to DataGridview is as under:
Before you start writing code you need to add a reference od Mysql Data to your project by Clicking on Project->Properties->References->Add->MySQl.Data
Imports MySql.Data.MySqlClient
Public Class Form1
Dim con As MySqlConnection = New MySqlConnection("server=localhost;database=students;user id=root;password=root")
Dim DA As MySqlDataAdapter = New MySqlDataAdapter
Dim DS As DataSet = New DataSet
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
DA.SelectCommand = New MySqlCommand
DA.SelectCommand.Connection = con
DA.SelectCommand.CommandText = "select* from users"
con.Open()
DA.Fill(DS, "users")
DataGridView1.DataSource = DS
DataGridView1.DataMember = "users"
End Sub
End Class
Now press F5 and see the result.
I will publish another article on Binding fields to controls on form and also how to add, edit,save records very soon.
Keep coding
Regards