Programming Against the .NET Connector
It's time. I'm throwing a grid view control below so I can output data from the new MySQL database; these are the character sets supported by the MySQL instance.
| DESCRIPTION | Max. Byte Length |
| UTF-32 Unicode | 4 |
| UTF-8 Unicode | 4 |
| UTF-16 Unicode | 4 |
| EUC-JP Japanese | 3 |
| UTF-8 Unicode | 3 |
| UJIS for Windows Japanese | 3 |
| EUC-KR Korean | 2 |
| GB2312 Simplified Chinese | 2 |
| Big5 Traditional Chinese | 2 |
| Shift-JIS Japanese | 2 |
| GBK Simplified Chinese | 2 |
| SJIS for Windows Japanese | 2 |
| UCS-2 Unicode | 2 |
| DOS Kamenicky Czech-Slovak | 1 |
| Mac West European | 1 |
| ISO 8859-8 Hebrew | 1 |
| ISO 8859-13 Baltic | 1 |
| Windows Cyrillic | 1 |
| Windows Arabic | 1 |
| Windows Central European | 1 |
| DOS West European | 1 |
| ISO 8859-9 Turkish | 1 |
| KOI8-R Relcom Russian | 1 |
| GEOSTD8 Georgian | 1 |
| ISO 8859-2 Central European | 1 |
| DOS Russian | 1 |
| US ASCII | 1 |
| Mac Central European | 1 |
| DOS Central European | 1 |
| TIS620 Thai | 1 |
| KOI8-U Ukrainian | 1 |
| ISO 8859-7 Greek | 1 |
| DEC West European | 1 |
| Windows Baltic | 1 |
| HP West European | 1 |
| Binary pseudo charset | 1 |
| ARMSCII-8 Armenian | 1 |
| cp1252 West European | 1 |
| 7bit Swedish | 1 |
The XHTML output is virtually identical to what's above and the gridview is literally...
<asp:GridView ID="htmlTableOfMysqlCharacterSetData" runat="server">
</asp:GridView>
so the only thing interesting is the C# programming in the code behind...
using System.Web.Configuration;
using MySql.Data.MySqlClient;
public partial class Database08MySqlDataRetrieval : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
.
.
.
}
}
This is pretty basic. I neglected the default namespaces, but I am showing you that I've added a couple required namespaces. System.Web.Configuration is needed to retrieve the connection string to the MySQL database; we've stored it in the Web.config file and an object that lives in that namespace, WebConfigurationManager, is how we'll get it out. Also, we need to access the MySQL classes built into the .NET Connector, which is the entire reason we were trying to install that in the first place; they're stored in MySql.Data.MySqlClient so we're importing that namespace as well.
There's also a standard ASP.NET class declaration for the page. public means anyone (including the server which needs it) can access it. partial means it's incomplete, and still has to be put together with the .aspx page, and class defines this as a class. Finally, the class is named Database08MySqlDataRetrieval, and the colon, :, indicates that it's implementing the base class System.Web.UI.Page which means we don't have to write a lot of basic webpage functionality, we get it just by including ": System.Web.UI.Page". The only thing we need to do is run one method to execute our database instructions when the page loads, ie the Page_Load event handler. The event handler is protected so that it can only be used in this code or in the .aspx page, and void means it's code that executes instructions but doesn't return any output object. Beyond that, we can get down to the real database stuff that happens inside.
// Create a connection to the new MySQL database
string mysqlConnectionString = WebConfigurationManager.ConnectionStrings["MysqlInstanceConnectionString"].ConnectionString;
MySqlConnection connectionToTheMysqlDatabase = new MySqlConnection(mysqlConnectionString);
//Create a command object to pass a SQL statement to the MySQL database
string sqlStatementForTheMysqlDatabase = "SELECT C.`DESCRIPTION`, C.`MAXLEN` AS 'Max. Byte Length' FROM CHARACTER_SETS C ORDER BY C.`MAXLEN` DESC";
MySqlCommand commandForTheMysqlDatabase = new MySqlCommand(sqlStatementForTheMysqlDatabase, connectionToTheMysqlDatabase);
DataTable dataForMysqlDatabaseCharacterSets = new DataTable();
The first thing we have to do is prepare the pieces necessary to connect to the database. I've created a string object, mysqlConnectionString, to store the connection string and then assigned it the value from the Web.config file. The connection string is named "MysqlInstanceConnectionString". Next I take advantage of that MySql.Data.MySqlClient namespace to create some MySQL objects. First, I create an instance of a MySqlConnection using the new connection string. I also need a SQL statement to tell the database what information I want. In this case, I'm selecting the DESCRIPTION and the MAXLEN fields from the C table. You'll note I'm using an AS keyword to change the cryptic name of MAXLEN into 'Max. Byte Length'. I'm selecting the information from the CHARACTER_SETS table; what's happening next was specified by one of my GUI tools, the Query Browser, so I'm not touching it. However, I believe what's happening is that an implicit AS is being used to rename the table from "CHARACTER_SETS" to "C" making the rest of the statement much shorter, ie C.'DESCRIPTION' instead of CHARACTER_SETS.'DESCRIPTION'. It would be better practice, however, to include the AS. I'm ordering the data by the MAXLEN field and putting the results in descending order (from largest to smallest). With the SQL stored in a variable, sqlStatementForTheMysqlDatabase, I can go back to MySql.Data.MySqlClient and create a MySqlCommand that uses the SQL statement and the connection object. That's all I need to query the database. In order to have some place to put the information it returns, I still need to create a DataTable which I name dataForMysqlDatabaseCharacterSets. Now I'm ready to hit the database.
try
{
commandForTheMysqlDatabase.Connection.Open();
dataForMysqlDatabaseCharacterSets.Load(commandForTheMysqlDatabase.ExecuteReader());
}
finally
{
commandForTheMysqlDatabase.Connection.Close();
}
htmlTableOfMysqlCharacterSetData.DataSource = dataForMysqlDatabaseCharacterSets.DefaultView;
htmlTableOfMysqlCharacterSetData.DataBind();
I wrap the call to the database in a try block for protection, and then open the connection specified by the MySqlCommand. Then I call the ExecuteReader method on the command which actually returns the data from the database. These results are loaded into the DataTable for storage. A second block, finally closes the connection so that even if something goes terribly wrong while trying to get the data, the connection will be closed. Next, I call the id of the GridView and set it's DataSource to the default view of the DataTable. This tells the GridView what data it should be working with, but I still need to call the DataBind() method on the GridView which actually inserts the data.
The results appear above; the data from the MySQL database is published as requested onscreen. This is not a hard coded test; the MySQL database now works on both my dev box and the production server, this is live information from the database. With some assistance from my webhost, I have a MySQL database up on this account in just a couple hours. Aaaaaaaah... so much easier than having to do it all myself on the dev box. Once that was taken care of, I reworked the connection string in the production server's Web.config file. When I settled in to see what happened, everything works first time. Brilliant! I love it! I'm also able to connect to the database remotely using MySQL's GUI tools, so I have access directly to the database as well as through the .NET application's webpages. That means I can work with the database at will. We have an enterprise-class database up live!