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
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.
|
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
Let's step through this example:
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#
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#
Let us step through this example:
Example 17-10 shows the logic in Example 17-9 implemented in VB.NET. Example 17-10. Using a MySqlDataReader in VB.NET
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
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:
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#
Let's step through this example:
Example 17-13 shows this logic in VB.NET . Example 17-13. Populating a DataSet from a SELECT statement in VB.NET
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
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
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#
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#
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:
Example 17-18 implements the same transaction logic in VB.NET . Example 17-18. Transaction handling in VB.NET
|