C operates the MySQL database using open source driver connections

Front 1 piece https: / / www ofstack. com article / 61219 htm spoke with MySQL C # official driver how to get to the connection operation MySQL driver, is MySQL JDBC drive has two 1 sample, for the NET MySQL driver also has two. Here is the.NET connection to the second driver of MySQL, which comes from the open source driver of Sourceforge.

First of all, to http: / / sourceforge net/projects/mysqldrivercs/download, when he wrote this article to download version is MySQLDriverCS n - EasyQueryTools - 4.0.1 - DotNet2. 0. exe. Installation, with source code and examples, only 3 dynamic libraries libmySQL-4.0. dll, ES31en. dll, MySQLDriverCS. dll, just look at the dynamic library seems not as powerful as the official version of the driver. Introduce MySQLDriverCS.dll from the installation directory into your C# project and you're ready to go. Keep an eye on the help\SDK\ Documentation.chm help document in the installation directory.

It also revolves around the following questions:

Basic usage, such as concatenation string writing, basic query operations, which classes are probably used
Handling of exceptions
Handling of things
Parameterized query support

This time, I will cover the above four aspects in an example. The following piece of code is a bit confusing. Please read it when you use it, and then you can get what you need from each one:

using System;

using System.IO;

 

using MySQLDriverCS; // This is the namespace that was introduced 

using System.Data.Common;

 

namespace cc.unmi

{

    public class Program

    {

        public static void Main(string[] args)

        {

            //MySQLDriverCS  There are the  MySQLConnectionStringBuilder  and  MySQLConnectionString  To construct the connection string 

            // In fact, it's a little confusing, maybe I used to use concatenation strings 

            MySQLConnectionString conStr = new MySQLConnectionString("localhost", "unmi_db", "unmi", "xxxxxx");

 

            // from  MySQLConnectionStringBuilder  There seem to be fewer properties to support. What about connection pooling 

            //string connStr = "Data Source=unmi_db;Password=xxxxxx;User ID=unmi;Location=localhost;Port=3306;Extended Properties=";

 

            MySQLConnection conn = new MySQLConnection(conStr.AsString); // or  new MySQLConnection(connStr);

 

            conn.Open();

 

            // Parameters with  @,  Also supports  ?  The way of ,  It should also not be recommended  ?  In the form of 

            //string sql = "update wp_options set option_value='http://unmi.cc' where option_id=?id and <a href="mailto:option_name=@name">option_name=@name</a>";

            string sql = "select option_name from wp_options where option_id=?id and <a href="mailto:option_name=@name">option_name=@name</a>";

 

            MySQLCommand com = new MySQLCommand(sql, conn);

 

            MySQLParameter paraId = new MySQLParameter("?id", 1);

            MySQLParameter paraName = new MySQLParameter("@name", "siteurl");

 

            // It's actually adding in  MySQLParameterCollection  In the 

            // Looks like  AddWithValue()  There are different music and the same work, such as can be written  com.Parameters.Add(new MySQLParameter("@id",1);

            com.Parameters.Add(paraId);

            com.Parameters.Add(paraName);

 

            // If it's a query 

            //MySQLDataReader dr = com.ExecuteReaderEx();// perform  ExecuteReaderEx()  The one that comes back is  MySQLDataReader

 

            DbTransaction trans = conn.BeginTransaction(); // Open the things 

 

            try

            {

                //com.ExecuteNonQuery();

                // Use things when there are more statements to execute 

                Console.WriteLine(com.ExecuteScalar().ToString());

                trans.Commit();

            }

            catch (MySQLException ex)

            {

                Console.WriteLine(ex.Message);

                trans.Rollback();

            }

            finally

            {

                conn.Close();

            }

 

            Console.ReadKey();

        }

    }

}

Even though the driver file name is es56EN2.0, it should only run as long as it is.Net 2.0 or above. I ran it with.Net 3.5 on 32-bit XP, Visual Studio 2008, no problem.

Note 1, it doesn't work on my computer at home. The prompt is:

{"Could not load file or assembly 'MySQLDriverCS, Version=3.0.1735.36021, Culture=neutral, PublicKeyToken=172f94dfb0faf263' or one of its dependencies. An attempt was made to load a program with an incorrect format."}

Again, my damned 64-bit XP, which can only support 32-bit applications, and the official version of the driver performance is not the same. The official VERSION of MSI cannot be installed on my 64-bit system, but the.ES70en in the package is available for 64-bit use. This version of the driver can be installed, but when used to tell me that I can't, obviously is to dump me. There are various Winows operating systems with 32-bit notes on the download driver's web page.

Finally, a brief note on the differences with the official version of the driver, the connection string is not as rich as the official version of the driver, and do not know how to support the connection pool. When setting parameters with parameterized queries, it is slightly less convenient than the AddWithValue() method available in the official version, although it is more convenient than the ADO.Net method of the tag. Then there's the fact that it can't run at all on the 64-bit side of the server, which can't be avoided.

What makes this open source driver unique, however, is that the MySQLInsertCommand, MySQLSelectCommand, MySQLUpdateCommand and MySQLDeleteCommand classes make it easy to manipulate data without having to explicitly write SQL statements, much like the corresponding functions in WordPress's $wpdb - : $wpdb- > insert (), $wpdb - > query (), $wpdb - > update () and $wpdb - > delete ().

There are several features of this driver that should not have been left behind. Take a brief look at the operations of these functions:

 new MySQLInsertCommand(

                dbCon,// The connection 

                new object[,] { // Field names and corresponding values 

                    {"SettingID",100},

                    {"SettingValue","https://www.ofstack.com"}

                },

                "Settings" // The name of the table 

            ); //new  After execution, available  bSuccess  with  Query  Attributes are successful or not and the corresponding  SQL

 

            // this  MySQLSelectCommand  It's a bit of a pain in the ass  SQL  A good time 

            DataTable dt = new MySQLSelectCommand(dbCon, // The connection 

                new string[] { "SettingID", "SettingValue" },// A list of fields to query 

                new string[] { "Settings" }, //  You can query multiple tables 

                new object[,] { { "SettingID", "=", 100 } },// conditions 

                null,

                null // We could have some in the back  limit, distinct  And so on instruction and parameter 

            ).Table; // To obtain  DataTable

 

            // This is a convenient way to update 

            new MySQLUpdateCommand(dbCon,

                new object[,] { { "SettingValue", "https://www.ofstack.com" } },

                "Settings",

                new object[,] { { "SettingID", "=", 100 } },

                null

            ); //new  After execution, available  bSuccess  with  Query  Attributes are successful or not and the corresponding  SQL

 

            // Parameters and  MySQLUpdateCommand  After the 4 same 

            new MySQLDeleteCommand(dbCon, "Trash", null, null);