Review of ADO.NET Basics

Before looking at how to invoke stored programs using ADO.NET, let's review how we perform operations in ADO.NET involving simple SQL statements. These operations form the foundation of stored program interactions. If you are already familiar with using ADO.NET with MySQL, you might want to skip forward to "Using Stored Programs in ADO.NET," later in this chapter.

17.1.1. Installing the Connector/Net Driver and Configuring Your IDE

To connect to MySQL from ADO.NET, we first need to download and install the Connector/Net provider from MySQL. We can download the Connector/Net driver from the MySQL web site at http://dev.mysql.com/downloads/connector/net/.

Once we have installed the Connector/Net driver, we are ready to write .NET programs to connect to MySQL. However, we must add a reference to the Connector/Net driver in our .NET application.

To do this in Visual Studio, select Project Add Reference from the main menu, then select the Browse tab. We find the MySQL.Data.dll file on our system, usually located in a directory such as C:Program FilesMySQLMySQL Connector Net in.NET ; where "x.x.x corresponds to the version of the Connector/Net driver (currently 1.0.7) and "y.y" corresponds to the version of .NET that we are using (usually 1.1 or 2.0). Figure 17-1 shows how we can configure Visual C# Visual Studio Express Edition to use the Connector/Net driver.

Figure 17-1. Adding a reference to the Connector/Net driver in Visual C# Express

 

17.1.2. Registering the Driver and Connecting to MySQL

To use the MySQL driver in your program code, we will normally first import the MySQL.Data.MySqlClient namespace so we don't have to fully qualify every reference to Connector/Net classes. In VB.NET, this means we would include Imports MySql.Data.MySqlClient as the first line of our VB.NET module. In C#, we would include a using MySql.Data.MySqlClient; statement within the Using directives region, as shown in Figure 17-2.

Figure 17-2. Adding the "using" clause in Visual C# Express

To establish a connection to MySQL we need to create a MySQLConnection object. The Constructer method for the MySQLConnection object accepts a string that defines the server, database, and connection credentials. This string consists of a set of name-value pairs separated by semicolons. For instance, the following string defines a connection to a server on the localhost at port 3306 and connects to database prod using the account fred and the password freddy:

 Server=localhost;Port=3306;Database=prod;Username=fred;Password=freddy

Table 17-1 lists the most important keywords that you can provide for the MySQLConnection object; you can find a complete list in the Connector/Net documentation that ships with the driver.

Table 17-1. Some of the keyword values for the MySQLConnection

Keyword	Description
Host	Name of the host on which the MySQL server is located. This could be an IP address, hostname, or localhost.
Port	Port number upon which the MySQL server is listening.
Database	Name of the database for initial connection.
Username	MySQL username to use for the connection.
Password	Password for the MySQL account.

It would be unusualand probably bad practiceto hardcode the MySQLConnection details in your program. More often, you will retrieve the keywords from command-line arguments or from a login dialog box.

Once the MySQLConnection object is initialized, we can establish the connection using the open( ) method. If the connection fails, a MySQLException will be thrown, so we need to enclose this call in a try block if we don't want to throw a non-handled exception (see "Handling Errors," later in this chapter). Example 17-1 shows us connecting to MySQL from within a VB.NET program, with the connection details specified as command-line arguments.

Example 17-1. Connecting to MySQL in VB.NET

 Sub Main(ByVal CmdArgs( ) As String)

 Dim myHost As String = CmdArgs(0)
 Dim myUserId As String = CmdArgs(1)
 Dim myPassword As String = CmdArgs(2)
 Dim myDatabase As String = CmdArgs(3)

 Dim myConnectionString As String = "Database=" & myDatabase & _
 " ;Data Source=" & myHost & _
 ";User ;Password=" & myPassword


 Dim myConnection As New MySqlConnection(myConnectionString)

 Try
 myConnection.Open( )
 Console.WriteLine("Connection succeeded")
 Catch MyException As MySqlException
 Console.WriteLine("Connection error: MySQL code: " _
 & MyException.Number & " " & MyException.Message)
 End Try

Example 17-2 implements the same logic in C#.

Example 17-2. Connecting to MySQL in C#

static void Main(string[] args)
{
 String myHost=args[0];
 String myUserId=args[1];
 String myPassword=args[2];
 String myDatabase=args[3];

 String myConnectionString = "Database=" + myDatabase +
 " ;Host=" + myHost +
 ";UserName=" + myUserId + ";Password=" + myPassword;

 MySqlConnection myConnection;
 myConnection = new MySqlConnection( );
 myConnection.ConnectionString = myConnectionString;

 try {
 myConnection.Open( );
 Console.WriteLine("Connection succeded");
 }
 catch (MySqlException MyException) {
 Console.WriteLine("Connection error: MySQL code: "+MyException.Number
 +" "+ MyException.Message);
 }

17.1.3. Issuing a Non-SELECT Statement

It is fairly straightforward to execute a non-SELECT statementsuch as UPDATE, INSERT, DELETE, or SETin .NET. First, we create a new MySQLCommand object, passing it the SQL statement to be executed and the name of the active connection (these can also be specified using the properties of the MySqlCommand object at a later time).

The ExecuteNonQuery() method of the MySqlCommand executes a statement that returns no result sets. It returns the number of rows affected by the statement. Example 17-3 shows an example of this in C#.

Example 17-3. Executing a non-SELECT SQL statement in C#

 MySqlCommand NonSelect = new MySqlCommand(
 "DELETE FROM employees WHERE employee_id=2001", myConnection);
 int RowsAffected = NonSelect.ExecuteNonQuery( );

Example 17-4 shows the same logic in VB.NET.

Example 17-4. Executing a non-SELECT statement in VB.NET

Dim NonSelect As MySqlCommand
NonSelect = New MySqlCommand( _
 "DELETE FROM employees WHERE employee_id=2001", myConnection)
Dim RowsAffected As Int16
RowsAffected = NonSelect.ExecuteNonQuery( )

17.1.4. Reusing a Statement Object

We don't have to create a new statement object for every SQL statement we execute. By changing the CommandText property of the MySqlCommand object, we associate the object with a new SQL statement text, which we can submit to the database by calling the ExecuteNonQuery() method. Example 17-5 provides an example of this technique in C#.

Example 17-5. Reusing a MySqlCommand object in C#

MySqlCommand NonSelect = new MySqlCommand("set autocommit=0",myConnection);
int RowsAffected=NonSelect.ExecuteNonQuery( );

NonSelect.CommandText = "update departments "+
 "set location=location "+
 "where department_id=1";
RowsAffected = NonSelect.ExecuteNonQuery( );
Console.WriteLine(RowsAffected + " rows affected");

17.1.5. Using Parameters

A lot of the time we execute the same logical SQL statement with different values for the WHERE clause or some other variable part of the statement. It might seem simple to do this by manipulating the CommandText and "pasting it" in the variable portions. For instance, in Example 17-6 we generate a new unique SQL statement to update employees' salaries based on some values in arrays.

Example 17-6. "Paste" method of changing SQL parameters (not recommended)

For i = 1 To N
 NonSelect.CommandText = "UPDATE employees " + _
 " SET salary= " + EmployeeSal(i).ToString + _
 " WHERE employee_docText">While this method will workand is, in fact, a common techniqueit is neither efficient nor safe. In particular, this style of coding cannot take advantage of MySQL server-side prepared statements, and it is vulnerable to SQL injection (a form of attack in which SQL syntax is inserted into parameters, leading to unintended SQL syntax being executed).

A far better way of performing this kind of iterative processing is to use the Parameters collection of the MySqlCommand object. Parameters are prefixed in the SQL text with the "?" character. You then use the Parameter methods of the MySqlCommand object to define the parameters and set their values, as shown in Example 17-7.

Example 17-7. Using parameters in VB.NET

1 Dim ParameterSQL As MySqlCommand
2 Dim SQLText As String
3 SQLText = "UPDATE employees " + _
4 " SET salary= ?NewSal" + _
5 " WHERE employee_id= ?EmpID"
6 ParameterSQL = New MySqlCommand(SQLText, myConnection)
7
8 Dim EmpSal As MySqlParameter
9 EmpSal = ParameterSQL.Parameters.Add("?NewSal", MySqlDbType.Float)
10 Dim EmpId As MySqlParameter
11 EmpId = ParameterSQL.Parameters.Add("?EmpID", MySqlDbType.Int16)
12 Dim RowCount As Int16
13
14 For i = 1 To N
15 EmpSal.Value = EmployeeSal(i)
16 EmpId.Value = EmployeeID(i)
17 RowCount = ParameterSQL.ExecuteNonQuery( )
18 Console.WriteLine(RowCount.ToString)
19 Next

Let's step through this example:

Line(s)	Explanation
3	Create the text for our SQL. The parameters in the SQL (?NewSal and ?EmpID) are prefixed by ? characters to distinguish them from normal MySQL identifiers.
6	Create the MySqlCommand object and associate it with our SQL text.
89	Declare a MySqlParameter object for the NewSal parameter on line 8, and on line 9, associate it with the MySqlCommand object. The name of the parameter provided to the Add() method should match exactly the name of the parameter in your SQL text. The second argument to Add() specifies the data type of the parameter.
1011	Create a second parameter to represent the EmpID parameter.
1419	Iterate through the EmployeeSal and EmployeeID arrays, which contain new salaries for specific employees.
1516	Assign the appropriate values to the parameter objects. The values are taken from the EmployeeSal and EmployeeID arrays.
17	The ExecuteNonQuery() method executes the SQL with the parameters supplied.

 

Using parameters rather than hardcoded literals is highly recommended, especially sinceas we will see laterwe really must use parameters if we are going to invoke stored programs in .NET.

Example 17-8 shows the logic of Example 17-7 expressed in C# .NET.

Example 17-8. Using parameters in C#

String SQLText = "UPDATE employees " +
 " SET salary= ?NewSal" +
 " WHERE employee_id= ?EmpID";
MySqlCommand ParameterSQL = new MySqlCommand(SQLText,myConnection);

MySqlParameter EmpSal = ParameterSQL.Parameters.Add(
 "?NewSal", MySqlDbType.Float);
MySqlParameter EmpId = ParameterSQL.Parameters.Add(
 "?EmpID", MySqlDbType.Int16);

for(i=1;i<=N;i++)
 {
 EmpSal.Value = EmployeeSal[i];
 EmpId.Value = EmployeeID[i];
 RowCount = ParameterSQL.ExecuteNonQuery( );
 }

17.1.6. Issuing a SELECT and Using a DataReader

MySQL supports a wide variety of methods of dealing with the output from a query. In this section, we will first review what is arguably the most straightforward of these methods: the DataReader.

A DataReader allows us to fetch rows from a result set in a manner similar to the fetching of rows from a stored program cursor. To create a MySqlDataReader object, we use the ExecuteReader( ) method of the MySqlCommand object. We iterate through the MySqlDataReader using the Read( ) method, and retrieve data values using GetInt32( ), GetString( ), and other data type-specific Get methods.

Example 17-9 is an example of using a MySqlDataReader in C#.

Example 17-9. Using a MySqlDataReader in C#

1 String SelectText = "SELECT department_id, department_name FROM departments";
2 MySqlCommand SelectStatement = new MySqlCommand(SelectText, myConnection);
3 MySqlDataReader SelectReader = SelectStatement.ExecuteReader( );
4 while (SelectReader.Read( ))
5 {
6 Console.WriteLine(SelectReader.GetInt32(0) + "	" +
7 SelectReader.GetString(1));
8 }
9 SelectReader.Close( );

Let us step through this example:

Line(s)	Explanation
2	Create a MySqlCommand object for a SELECT statement.
3	Use the ExecuteReader( ) method to create a MySqlDataReader object.
4-8	Loop through the rows returned by the SELECT statement using the Read( ) method of the MySqlDataReader.
6	Use the GetInt32() and GetString() methods to retrieve the current values for the department_id and department_name columns. The argument for these methods is the numeric position of the column in the result setstarting with "0" as the first column.
9	Close the Reader. We should always do this since it releases database resources and is also a prerequisite for retrieving OUT parameters from stored procedures.

 

Example 17-10 shows the logic in Example 17-9 implemented in VB.NET.

Example 17-10. Using a MySqlDataReader in VB.NET

 Dim SelectText As String
 Dim SelectStatement As MySqlCommand
 Dim SelectReader As MySqlDataReader
 SelectText = "SELECT department_id, department_name FROM departments"
 SelectStatement = New MySqlCommand(SelectText, myConnection)
 SelectReader = SelectStatement.ExecuteReader( )
 While (SelectReader.Read( ))
 Console.WriteLine(SelectReader.GetInt32(0).ToString + _
 " " + SelectReader.GetString(1))
 End While
 SelectReader.Close( )

17.1.7. Getting DataReader Metadata

The DataReader provides methods for retrieving information about the columns that will be returned in the Reader. This information is essential if we are going to process dynamic SQLfor instance, SQL that is entered at a terminal by an end user or generated on-the-fly by some other module in our program.

The FieldCount() method returns the number of columns in the DataReader's result set. GetFieldType() and GetName() return the name and data type of a column within the result set, where GetName(0) would return the name of the first column. Example 17-11 uses these methods to retrieve the names and data types of a query from within VB.NET and displays those to the console.

Example 17-11. Accessing DataReader metadata

SelectText = "SELECT * FROM departments"
SelectStatement = New MySqlCommand(SelectText, myConnection)
SelectReader = SelectStatement.ExecuteReader( )
For i = 0 To SelectReader.FieldCount( ) - 1
 Console.WriteLine(SelectReader.GetName(i) + " " + _
 SelectReader.GetFieldType(i).ToString)
Next

17.1.8. DataSets

While DataReaders offer a convenient way to access query result sets, the ADO.NET DataSet class provides an alternative that is a little more complex, but that offers increased flexibility and functionality. In particular, because we can only ever have a single DataReader open simultaneously for a given connection, we are likely to use DataSets in most complex applications.

DataSets provide an in-memory, datasource-independent representation of data that can persist even when a connection is closed. DataSets offer a number of methods for handling data modification, including a mechanism for resynchronizing data when a closed connection is reopened.

In this section we will provide a simple example of using a DataSet to retrieve the outputs only from a simple SQL query.

A DataSet object contains a collection of tables, each of which includes a collection of columns and rows. We can access and manipulate the tables, columns, and rows in the DataSet using the DataTable, DataColumn, and DataRow objects.

A DataSet is associated with its data source through a DataAdapter object. In our case, we have to create a MySqlDataAdapator object to associate a DataSet with a MySqlCommand.

The general steps for processing a SQL query through a DataSet are as follows:

1. Create the MySqlCommand object.
2. Create a MySqlDataAdpator object and associate it with the MySQLCommand.
3. Create a DataSet object.
4. Use the MySqlDataAdapter object to populate the DataSet.
5. Use the DataTable, DataColumn, and DataRow objects to retrieve the contents of the DataSet.

Example 17-12 shows an example of populating and examining a DataSet object in C# .

Example 17-12. Populating a DataSet from a simple SQL statement in C#

1 String SqlText = "SELECT * FROM departments";
2 MySqlCommand SqlCmd = new MySqlCommand(SqlText, myConnection);
3
4 MySqlDataAdapter MyAdapter=new MySqlDataAdapter(SqlCmd);
5 DataSet MyDataSet=new DataSet( );
6 int rows = MyAdapter.Fill(MyDataSet);
7
8 DataTable MyTable=MyDataSet.Tables[0];
9
10 //Write column headings
11 foreach(DataColumn MyColumn in MyTable.Columns)
12 {
13 Console.Write(MyColumn.Caption+"	");
14 }
15 Console.WriteLine( );
16
17 //Write Column Rows
18 foreach(DataRow MyRow in MyTable.Rows)
19 {
20 foreach(DataColumn MyColumn in MyTable.Columns)
21 {
22 Console.Write(MyRow[MyColumn]+"	");
23 }
24 Console.WriteLine( );
25 }

Let's step through this example:

Line(s)	Explanation
12	Define a MySqlCommand object (SqlCmd) that will issue our query.
4	Create a new MySQLDataAdapator object and associate it with SqlCmd (our MySqlCommand object).
5	Create a new DataSet and, in line 6, we populate this data set with the output of the SELECT statement (via the MySqlDataAdapter).
8	Declare a DataTable (MyTable) that references the first table (index "0") in the DataSet MyDataSet. Remember that a DataSet can contain multiple tables, but in this case we know that we need only concern ourselves with the first and only DataTable in the DataSet.
1115	Print the names of the columns in the DataTable. We do this by iterating through the Columns collection in the DataTable and printing the Caption property for each column.
1825	Print out the data rows. We do this by iterating through the Rows collection in the DataTable. For each Row, we iterate through the Columns collection to print an individual column value. MyRow[MyColumn] represents the value of a specific column within a specific row.

 

Example 17-13 shows this logic in VB.NET .

Example 17-13. Populating a DataSet from a SELECT statement in VB.NET

 Dim TabChr As Char = Microsoft.VisualBasic.Chr(9)
 Dim SqlText As String = "SELECT * FROM departments"
 Dim SqlCmd As MySqlCommand = New MySqlCommand(SqlText, myConnection)


 Dim MyAdapter As MySqlDataAdapter = New MySqlDataAdapter(SqlCmd)

 Dim MyDataSet As DataSet = New DataSet
 Dim rows As Integer = MyAdapter.Fill(MyDataSet)

 Dim MyTable As DataTable = MyDataSet.Tables(0)

 For Each MyColumn As DataColumn In MyTable.Columns
 Console.Write(MyColumn.Caption + "" & TabChr & "")
 Next
 Console.WriteLine( )
 For Each MyRow As DataRow In MyTable.Rows
 For Each MyColumn As DataColumn In MyTable.Columns
 Console.Write(MyRow(MyColumn).ToString + "" & TabChr & "")
 Next
 Console.WriteLine( )
 Next
 End Sub

As we will see later, using a DataSet is a good technique for stored procedures, which might return multiple result sets. However, for a single result set, we can populate the DataTable directly from the MySqlDataAdaptor() method, as shown in Example 17-14.

Example 17-14. Populating a DataTable directly from a MySqlDataAdapter( ) method

 String SqlText = "SELECT * FROM departments";
 MySqlCommand SqlCmd = new MySqlCommand(SqlText, myConnection);

 MySqlDataAdapter MyAdapter = new MySqlDataAdapter(SqlCmd);
 DataTable MyTable = new DataTable( );

 MyAdapter.Fill(MyTable);

17.1.9. Handling Errors

The Connector/Net methods will throw a MySqlException exception if the database returns an error with respect to any of our ADO.NET calls. Therefore, we will usually want to enclose our ADO.NET sections in a try/catch block to ensure that we do not generate an unhandled exception condition at runtime. Example 17-15 shows a simple example of using an exception handler in VB.NET .

Example 17-15. Error handling in VB.NET

 Sub CreateDemoTables( )
 Dim MySqlText As String
 MySqlText = "CREATE TABLE DEMO" & _
 " (MyInt INT," & _
 " MyString VARCHAR(30)) "

 Dim CrDemoSQL As MySqlCommand

 Try
 CrDemoSQL = New MySqlCommand(MySqlText, myConnection)
 CrDemoSQL.ExecuteNonQuery( )
 Catch MyException As MySqlException
 Console.WriteLine("Error creating demo tables:")
 Console.WriteLine(MyException.Number.ToString & ": " & _
 MyException.Message)
 Console.WriteLine(MyException.StackTrace)
 End Try

 End Sub

In this example, the SQL statement is executed within a try block. If an error occurs, control is passed to the Catch block that creates a MySqlException object call "MyException". The Number property returns the MySQL error code; the Message property contains the MySQL error message. StackTrace generates a familiar .NET stack trace that can be useful during debugging (though not so useful for Auntie Edna or other end users).

Example 17-16 demonstrates the same exception handling in C# .

Example 17-16. Exception handling in C#

 static void CreateDemoTables( )
 {
 String MySqlText= "CREATE TABLE DEMO" +
 " (MyInt INT," +
 " MyString VARCHAR(30)) ";

 try
 {
 MySqlCommand CrDemoSQL=new MySqlCommand(MySqlText,myConnection);
 CrDemoSQL.ExecuteNonQuery( );
 }
 catch(MySqlException MyException)
 {
 Console.WriteLine("Error creating demo tables:");
 Console.WriteLine(MyException.Number +
 ": " + MyException.Message);
 Console.WriteLine(MyException.StackTrace);
 }
 }

17.1.10. Managing Transactions

You can execute the usual MySQL statements to manage your transactions in .NET programs, such as BEGIN TRANSACTION, COMMIT, and ROLLBACK. However, instead of using these statements, you may want to take advantage of the built-in transaction object to manage your transactions. Doing so may help make your code more readable and maintainable.

Connector/Net allows us to create a MySqlTransaction object that represents a transaction. Methods to the MySqlTransaction object allow us to commit and roll back our transaction, or to set the transaction isolation levels.

Example 17-17 shows an example of using these facilities in C#.

Example 17-17. Transaction management in C#

1 static void TferFunds(int FromAccount, int ToAccount, float TferAmount)
2 {
3 String TransSQL = "UPDATE account_balance " +
4 " SET balance=balance+?tfer_amount " +
5 "WHERE account_id=?account_id";
6 MySqlCommand TransCmd = new MySqlCommand(TransSQL, myConnection);
7 MySqlParameter P_tfer_amount = TransCmd.Parameters.Add("?tfer_amount",
8 MySqlDbType.Float);
9 MySqlParameter P_account_id = TransCmd.Parameters.Add("?account_id",
10 MySqlDbType.Int32);
11
12 MySqlTransaction myTransaction = myConnection.BeginTransaction( );
13 try
14 {
15 //Remove amount from from_account
16 P_tfer_amount.Value = TferAmount * -1;
17 P_account_id.Value = FromAccount;
18 TransCmd.ExecuteNonQuery( );
19 //Add amount to to_account;
20 P_tfer_amount.Value = TferAmount;
21 P_account_id.Value = ToAccount;
22 TransCmd.ExecuteNonQuery( );
23
24 myTransaction.Commit( );
25 Console.WriteLine("Transaction Succeeded");
26 }
27 catch (MySqlException TransException)
28 {
29 Console.WriteLine("Error in transaction: ");
30 Console.WriteLine(TransException.Message);
31 try
32 {
33 myTransaction.Rollback( );
34 Console.WriteLine("Transaction rollback");
35 }
36 catch (MySqlException RollbackException)
37 {
38 Console.WriteLine("Failed to rollback transaction:");
39 Console.WriteLine(RollbackException.Message);
40 }
41 }
42 }

The function is designed to transfer some money from one account to another. It is absolutely essential that both operations succeed or fail as a unit, and therefore they are enclosed within a transaction.

This is a relatively long example and ties in the use of parameters and exception handlers, so let us step through it line by line:

Line(s)	Explanation
39	Create a SQL UPDATE statement to adjust the account balance for a specific account. The statement includes parameters for the account ids and amounts, so we can reuse the statement to do both parts of the transfer and could also reuse it for subsequent transactions.
12	The BeginTransaction( ) method of the connection indicates the commencement of the transaction.
13	Declare a TRy/catch block that will handle any errors that occur within our transaction.
1522	Execute the transfer by placing the appropriate values into the account and amount parameters, and then executing the UPDATE statement twice once to reduce the balance in the "from" account and once to increase the balance in the "to" account.
24	Commit the transaction. Note that this statement would be reached only if all of the previous statements succeed. If any of the previous ADO.NET statements raised an exception, control would be assumed by the code in the catch block.
2741	This is the catch block that will be invoked if a SQL error occurs. It executes a ROLLBACK statement (line 33) to undo any parts of the transaction that may have successfully executed.
3141	We've nested another catch block without the main error handler to catch any problems that occur when we execute the rollback. This might seem a bit paranoid, but it is possible that the errors that caused the statements to fail will also cause us to fail to execute a rollback (the server may have crashed, for instance).

 

Example 17-18 implements the same transaction logic in VB.NET .

Example 17-18. Transaction handling in VB.NET

 Sub TferFunds(ByVal FromAccount As Integer, _
 ByVal ToAccount As Integer, _
 ByVal TferAmount As Single)

 Dim TransSQL As String = "UPDATE account_balance " + _
 " SET balance=balance+?tfer_amount " + _
 "WHERE account_id=?account_id"
 Dim TransCmd As MySqlCommand = New MySqlCommand(TransSQL, myConnection)
 Dim P_tfer_amount As MySqlParameter = _
 TransCmd.Parameters.Add("?tfer_amount", MySqlDbType.Float)
 Dim P_account_id As MySqlParameter = _
 TransCmd.Parameters.Add("?account_id", MySqlDbType.Int32)
 Dim myTransaction As MySqlTransaction = myConnection.BeginTransaction
 Try
 'Remove amount from FromAccount
 P_tfer_amount.Value = TferAmount * -1
 P_account_id.Value = FromAccount
 TransCmd.ExecuteNonQuery( )
 'Add amount to ToAccount
 P_tfer_amount.Value = TferAmount
 P_account_id.Value = ToAccount
 TransCmd.ExecuteNonQuery( )

 myTransaction.Commit( )
 Console.WriteLine("Transaction Succeded")

 Catch TransException As MySqlException
 Console.WriteLine("Error in transaction: ")
 Console.WriteLine(TransException.Message)
 Try
 myTransaction.Rollback( )
 Console.WriteLine("Transaction rollback")
 Catch RollbackException As MySqlException
 Console.WriteLine("Failed to rollback transaction:")
 Console.WriteLine(RollbackException.Message)
 End Try
 End Try
 End Sub