You are here: Saotn.org » Code base » MySQL Connector/ODBC C# test script

MySQL Connector/ODBC C# test script

Whenever you need to test your ASP.NET connection to your MySQL database, using ODBC, you can use the following C# test script. Currently it uses MySQL ODBC 8.0 Unicode Driver, but you can easily change the version used. As a bonus it prints some connection information and it requires an SSL connection to your MySQL database (SslMode=REQUIRED).

<%@ Page Language="C#" Trace="False" Debug="False" EnableViewState="False" %> <%@ import Namespace="System.Data.Odbc" %> <script runat="server"> private void Page_Load(object sender, System.EventArgs e) { try { //Connection string for MyODBC 8.0, pooling=true not added string MyConString = "DRIVER={MySQL ODBC 8.0 Unicode Driver};" + "Provider=MSDASQL;" + "SERVER=HOSTNAME;" + "DATABASE=DATABASENAME;" + "UID=USERNAME;" + "PASSWORD=PASSWORD;" + "SslMode=REQUIRED;"; //Connect to MySQL using MyODBC OdbcConnection MyConnection = new OdbcConnection(MyConString); MyConnection.Open(); Response.Write("<br /> !!! success, connected successfully !!!<br />"); //Display connection information Response.Write("Connection Information:<br />"); Response.Write("Connection String:" + MyConnection.ConnectionString + "<br />"); Response.Write("Connection Timeout:" + MyConnection.ConnectionTimeout + "<br />"); Response.Write("Database:" + MyConnection.Database + "<br />"); Response.Write("DataSource:" + MyConnection.DataSource + "<br />"); Response.Write("Driver:" + MyConnection.Driver + "<br />"); Response.Write("ServerVersion:" + MyConnection.ServerVersion + "<br />"); //Close all resources MyConnection.Close(); } catch (OdbcException MyOdbcException)//Catch any ODBC exception .. { for (int i=0; i < MyOdbcException.Errors.Count; i++) { Response.Write("ERROR #" + i + "<br />" + "Message: " + MyOdbcException.Errors[i].Message + "<br />" + "Native: " + MyOdbcException.Errors[i].NativeError.ToString() + "<br />" + "Source: " + MyOdbcException.Errors[i].Source + "<br />" + "SQL: " + MyOdbcException.Errors[i].SQLState + "<br />"); } } } </script>

Set SslMode to PREFERRED or DISABLED if SSL is not available on your MySQL database server.

Use NO_SSPS=1 as a connection option when server-side prepared statements are not returning any results – for example with VARCHAR() columns larger than 1024 bytes.

If you have installed Connector/ODBC side-by-side with Connector/NET, you can use this MySQL ODBC test script next to my MySql.Data.MySqlClient test script. Neat, right? :)

Leave a Reply

Your email address will not be published. Required fields are marked *