Contoh program vb net dengan database mysql

Apr 4, 2012 - NET with Examples in Visual Basic. Press.VB.NET.Step.By.Step.[].rar MYSQL/. Using UML Data Modeling.pdf R.Jennings - Expert One-on-One Visual Basic 2005 Database Programming. Contoh Program Java NetBeans untuk Tugas Akhir dan Skripsi.pdf Desain Program. Under construction Pengetahuan Dasar Koneksi VB.Net dan Database MYSQL untuk memahami bagaimana C# atau dihubungkan dengan data base anda bisa baca artikel ttg ADO.Net Karena Net Framework tdk menyediakan data provider untuk database MYSQL maka untuk menghubungkan VB.Net dengan Database MYSQL kita membutuhkan mysql connector untuk aplikasi.NET (C# atau VB.Net.

This is a Visual Basic tutorial for the MySQL database. It covers the basics of MySQL programming with Visual Basic. In this tutorial, we use the Connector/Net driver. This driver is based on the ADO.NET specification.The examples were created and tested on Ubuntu Linux. There is a similarMySQL C# tutorial, MySQL Perl tutorial and SQLite Visual Basic tutorial on ZetCode.

If you need to refresh your knowledge of the Visual Basic language, there is a full Visual Basic tutorial on ZetCode.

About MySQL database

MySQL is a leading open source database management system. It is a multi user, multithreaded database management system. MySQL is especially popular on the web. It is one of the parts of the very popular LAMPplatform consisting of Linux, Apache, MySQL, and PHP. Currently MySQL is owned by Oracle.MySQL database is available on most important OS platforms. It runs on BSD Unix, Linux, Windows, or Mac OS. Wikipedia and YouTube use MySQL. These sites manage millions of queries each day. MySQL comes in two versions: MySQL server system and MySQLembedded system.

Before we start

We need to install several packages to execute the examples in this tutorial:libmysql6.1-cil, mysql-server, mysql-client. We need to install Visual Basic compiler from the Mono project. Either from a package or from sources.

The libmysql6.1-cil is the MySQL database connector for CLI. It iswritten in C# and is available for all CLI languages. C#, Visual Basic, Boo and others.

From the technical point of view, we need a DLL. On my system (Ubuntu Lucid Lynx), itwas located under the above path. We need to know the path to the DLL library.To compile our examples.

If you don't already have MySQL installed, we must install it.

This command installs the MySQL server and various other packages. While installing the package, we are prompted to entera password for the MySQL root account.

Next, we are going to create a new database user and a new database. We use the mysql client.

We check if the MySQL server is running. If not, we needto start the server. On Ubuntu Linux, this can be donewith the service mysql start command.

We use the mysql monitor client application to connect to the server. We connect to the database using the root account. We show all availabledatabases with the SHOW DATABASES statement.

We create a new testdb database. We will use this database throughout the tutorial.

We create a new database user. We grant all privileges to this userfor all tables of the testdb database.


ADO.NET is an important part of the .NET framework. It is a specificationthat unifies access to relational databases, XML files and other application data.A MySQL Connector/Net is an implementation of the ADO.NET specificationfor the MySQL database. It is a driver written in C# language and is available forall .NET languages.

The Connection, Command, DataReader, DataSet, and DataProvider are the core elementsof the .NET data provider model. The Connection creates a connection to a specific data source. The Command object executes an SQL statementagainst a data source. The DataReader reads streams of data from a data source. The DataSet object is used for offline work with a massof data. It is a disconnected data representation that can hold data from a variety of different sources. Both DataReader and DataSet are used to work with data; they are used under different circumstances. If we only need to read the results of a query, the DataReader is the better choice. If we need more extensive processing of data, or we want to bind a Winforms control to a database table, the DataSet is preferred.

MySQL version

If the following program runs OK, then we have everythinginstalled OK. We check the version of the MySQL server.

We connect to the database and get some info about the MySQL server.

We import the elements of the MySQL data provider.

This is the connection string. It is used by the data providerto establish a connection to the database. We specify the databasename, host, user name and password.

A MySQLConnection object is created. This object is used toopen a connection to a database.

This line opens the connection.

Here we print the version of MySQL using the ServerVersionproperty of the connection object.

In case of an exception, we print the error message to theconsole.

We compile our example. A path to the MySQL connector DLLis provided.

This is the output of the program on my system.

A more complex program follows.

We check for the version of the MySQL database. This time usingan SQL query.

This is the SQL SELECT statement. It returns the versionof the database. The VERSION() is a built-in MySQL function.

The MySqlCommand is an object, which isused to execute a query on the database. The parameters are theSQL statement and the connection object.

There are queries which return only a scalar value. In ourcase, we want a simple string specifying the version of thedatabase. The ExecuteScalar() is used in such situations. We avoid the overhead of using morecomplex objects.

Same result as in the previous example.

Creating and populating tables

Next we are going to create database tables and fill themwith data. These tables will be used throughout this tutorial.

We have a books.sql file. It creates two database tables: Authors, and Books. The tables are of InnoDB type. InnoDB databases support foreign key constraints and transactions. We place a foreign key constraint on the AuthorId column of the Books table. We fill the tables with initial data.

We use the source command to execute the books.sql script.

In the following example, we are going to insert a new authorinto the Authors table.

We add a new author to the Authors table. We use a parameterized command.

Here we create a prepared statement. When we write prepared statements, we use placeholders instead of directly writing the values into the statements. Prepared statements are faster and guard against SQL injection attacks. The @Name is a placeholder, which is going to be filled later.

A value is bound to the placeholder.

The prepared statement is executed. We use the ExecuteNonQuery()method of the MySQLCommand object when we don't expect any data to be returned. This is when we create databases or execute INSERT, UPDATE, DELETE statements.

We have a new author inserted into the table.

Retrieving data with MySqlDataReader

The MySqlDataReader is an object usedto retrieve data from the database. It provides fast, forward-only, read-only access to query results. It is the most efficient wayto retrieve data from tables.

We get all authors from the Authors table and print them to the console.

To create a MySQLDataReader, we must call the ExecuteReader() method of the MySqlCommand object.

The Read() method advances the data reader to the next record.It returns true if there are more rows; otherwise false. We can retrieve the value using the array index notation, or use a specific method to access column values in their native data types. The latter is more efficient.

Always call the Close() method when done reading.

This is the output of the example.

Column headers

Next we will show, how to print column headers with the datafrom the database table.

In this program, we select authors from the Authors tableand their books from the Books table.

This is the SQL statement which joins authors with theirbooks.

We create a MySqlDataReader object.

We get the names of the columns with the GetName() method of the reader. The PadLeft() method returns a new string of a specified length in which the beginning of the current stringis padded with spaces. We use this method to align strings properly.

We print the data that was returned by the SQL statementto the terminal.

Ouput of the program.

DataSet & MySqlDataAdapter

A DataSet is a copy of the data andthe relations among the data from the database tables. It is createdin memory and used when extensive processing on data is needed or when we bind data tables to a Winforms control. When the processing isdone, the changes are written to the data source. A MySqlDataAdapter is an intermediary between the DataSet and the data source. It populates a DataSet and resolves updates with the data source.

We print the authors from the Authors table. This time, we usethe MySqlDataAdapter and DataSetobjects.

A MySqlDataAdapter object is created. It takes an SQL statement and a connection as parameters.

We create and fill the DataSet.

We get the table called Authors. We have given a DataSet only one table, but it can contain multiple tables.

We write the data to an XML file.

We display the contents of the Authors table to theterminal. To traverse the data, we utilize the rows andcolumns of the DataTable object.

In the next example, we are going to bind a table to a Winforms DataGrid control.

In this example, we bind a Authors table to a WinformsDataGrid control.

These two namespaces are for the GUI.

Inside the InitUI() method, we build the user interface. In the InitData() method, we connect to the database, retrieve the data into the DataSet and bind it to the DataGrid control.

The DataGrid control is created.

We will display the data from the Authors table in the DataGrid control.

We bind the DataSource property of the DataGrid control to the chosen table.

To compile the example, we must include two DLLs. The DLL for the Winforms and the DLLfor the MySQL connector.

Transaction support

A transaction is an atomic unit of database operations against the data in one or more databases. The effects of all the SQL statements in a transaction can be either all committed to the database or all rolled back.

The MySQL database has different types of storage engines. The most common are the MyISAM and the InnoDB engines. The MyISAM is the default one. There is a trade-off between data security and database speed. The MyISAM tables are faster to process and they do not support transactions. On the other hand, the InnoDB tables are more safe against the data loss. They support transactions. They are slower to process.

In this program, we want to change the name of the authoron the first row of the Authors table. We must also change thebooks associated with this author. A good example where a transaction is necessary. If we change the author and do notchange the author's books, the data is corrupted.

The MySqlTransaction is an objectfor working with transactions.

We begin a transaction.

The third SQL statement has an error. There is no Titl column in thetable.

If there is no exception, the transaction is committed.

In case of an exception, the transaction is rolled back. No changes are committed to the database.

An exception was thrown. The transaction was rolled back andno changes took place.

However, without a transaction, the data is not safe.

We have the same example. This time, without the transaction support.

An exception is thrown again. Leo Tolstoydid not write Martin Eden. The data is corrupted.

This was the MySQL Visual Basic tutorial, with MySQL Connector. You might be also interested in MySQL C API tutorial,MySQL Python tutorial or MySQL PHP tutorial.