VB2010 ADO.NET

ADO.NET

ADO.NET is a data access technology from Microsoft .Net Framework , which provides communication between relational and non-relational systems through a common set of components.

The two key components of ADO.NET are Data Providers and DataSet . The .Net Frameworkincludes mainly three Data Providers for ADO.NET. They are the Microsoft SQL Server Data Provider, OLEDB Data Provider and ODBC Data Provider. SQL Server uses theSqlConnection object , OLEDB uses the OleDbConnection Object and ODBC usesOdbcConnection Object respectively.

The four Objects from the .Net Framework provide the functionality of Data Providers in the ADO.NET. They are Connection Object, Command Object , DataReader Object and DataAdapter Object. The Connection Object provides physical connection to the Data Source. The Command Object uses to perform SQL statement or stored procedure to be executed at the Data Source. The DataReader Object is a stream-based , forward-only, read-only retrieval of query results from the Data Source, which do not update the data. Finally the DataAdapter Object , which populate a Dataset Object with results from a Data Source .

CREATING CONNECTION

After installing mysqlconnector/net, the next thing to do is to add a MySQL library. Right-click on the project name -> Add Reference. On Add Reference, select MySQL.Data on .NET tab. By default, the reference library (MySQL.Data) won’t be copied to the output directory. That means when the application is deployed on other PC which doesn’t have the library installed, it’ll throw error. Therefore, it is a good idea to set the Copy Local property of the library file to True. Click Show All Files icon. Expand References -> Select MySQL.Data -> Change Copy Local property to True. Now it’s time to coding the application. First, is to import a namespace. Open the Code View and add this line on the top.

  

Imports MySql.Data.MySqlClient

PERFORMING SQL OPERATIONS

The first thing to do is to declare a connection string

1 2 3 4

Private connStr As String = "Database=dbName;" & _                     "Data Source=localhost;" & _                     "User Id=mysqlUser;Password=mysqlPassword;" & _                     "Connection Timeout=20"

Retrieve data from database

To retrieve data from MySQL, we will use MySqlDataReader Class from MySql.Data library. First, is to open the connection to the world database on MySQL Server. Then, executes the query command by using ExecuteReader method and assigns to MySqlDataReader object. After that, looping on MySqlDataReader object to get result. Let’s see the code below.

19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

Public Sub retriveData()         Try             Dim query As String = "SELECT * FROM tblname"             Dim connection As New MySqlConnection(connStr)             Dim cmd As New MySqlCommand(query, connection)             connection.Open()             Dim reader As MySqlDataReader             reader = cmd.ExecuteReader()             While reader.Read()                 Console.WriteLine((reader.GetString(0) & ", " & _                     reader.GetString(1)))             End While             reader.Close()             connection.Close()         Catch ex As Exception             Console.WriteLine(ex.Message)         End Try End Sub

Code Explanation:

• Line 21: Create a query variable as string.
• Line 22: Create a MySQLConnection object with the defined connection string in global as parameter.
• Line 23: Create a MySQLCommand object with previous 2 variables as parameters.
• Line 25: Open a connection to MySQL Server using the defined connection string.
• Line 27-28: Call ExecuteReader() method and assign the result to MySqlDataReader
  object.
• Line 30-33: Looping on MySqlDataReader object to get results to the console.
• Line 35-36: Close the reader and connection. It is recommended closing these objects after using every time.
• Line 38: If there is any error in the method, send to console.

UPDATE, DELETE AND INSERT RECORD ON DATABASE

Coding on INSERT, UPDATE and DELETE SQL operations are identical except only sql command that is executed. In performing these operations to database, there is no need to get records from the database. We need to use ExecuteNonQuery() Method from MySqlCommand Class. For INSERT, UPDATE and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1.
Note: You can use ExecuteNonQuery to perform any type of database operation; however any result sets returned will not be available.

dbEdit Function

This function accepts a parameter as sql command and send to execute on MySQL Server. So the function can be use for INSERT, UPDATE and DELETE operations also any operation that doesn’t need a return result sets. Also, it returns an integer value of affected rows.

42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58

Function dbEdit(ByVal query As String) As Integer         Try             Dim rowsEffected As Integer = 0             Dim connection As New MySqlConnection(connStr)             Dim cmd As New MySqlCommand(query, connection)             connection.Open()             rowsEffected = cmd.ExecuteNonQuery()             connection.Close()             Return rowsEffected         Catch ex As Exception             Console.WriteLine(ex.Message)         End Try End Function

Code Explanation:

Line 50: The code is similar to retrieve data section only it call ExecuteNonQuery() method and the return value is affected rows.