Preparing to use MySQL and to develop Web applications

Source: Internet
Author: User
Tags exception handling first string odbc mysql in odbc connection mediawiki mysql database visual studio|mysql|web recently prepared to use for development, read three article:

1 MySQL and cooperate more powerful;

2 choose MySQL or SQL Server;

3 The use of MySQL and. Net2.0.

MySQL is more powerful with

Due to competitive prices and ease of use, MySQL is gradually upgrading its market share. The open source community has developed to extend the scope of MySQL usage. NET Framework (. NET Framework) is a database connector that can be used in the. Let's learn how to do it in. NET applications, using MySQL.

MySQL is becoming a practical database scheme when choosing the database platform. To prove this, many companies have opted for MySQL as their database platform, such as Google, the Associated Press, and NASA, the United States National Aeronautics and Space Agency (associated). While low prices are often used as a major advantage for an open-source source, they will not be reassuring to store very useful information in a database product with a price advantage for large organizations like Google. MySQL's real strength far exceeds his price advantage, and he offers a wealth of additional tools from the open source community and commercialization.

Data consolidation with. Net

The MySQL community has developed a MySQL data interface that provides basic functionality for connecting data sources and program code. On the Windows platform, like the following MySQL connector:

* MySQL Connector/net 1.0 (previously known as is an open source. Net Data interface designed for MySQL. It was developed entirely in C # and we can find it on the website. (Note: In the example of this article, we use the MySQL connector/net 1.0 data interface to connect to the database, which can be easily installed with Windows Installer, with code instances and documents included.) )

* mysqldirect. NET data Provider: A commercial data interface developed by Core Lab. His price is determined by the type of license purchased, but we can download its trial version.

If you use Mono, you can find the download of the PHP connector on the Mono website. If you run mono on a Windows platform, the connectors you download include the installer. If not, then download the appropriate connector based on the type of operating system you are using.

Using the MySQL Data interface

Once you have installed the MySQL data interface, you must introduce it into your code to use it. You can use the MySql.Data.MySqlClient namespace to connect to the MYSQL server. In C #, you can use a using statement to introduce the MySQL data interface:

Using MySql.Data.MySqlClient;

Alternatively, you can introduce the MySQL data interface in a Web Form (Web Form) by using Import directives:

Alternatively, you can write the full path of a class in your code while using the namespace, but that would waste more bytes than importing more characters using the import directive. Once we have specified a name space, we can interact with the MySQL database for data. MySql.Data.MySqlClient This namespace provides a number of classes for handling MySQL data. Here is a sample of these classes:

* Mysqlconnection: Manage the connection with MySQL server/database;

* Mysqldataadapter: A set of commands and connections for populating dataset objects and updating MySQL databases;

* Mysqldatareader: Allows you to read data from a MySQL database. It is a one-way flow of data;

* Mysqlcommand: Provides the function of sending instructions to the database server;

* Mysqlexception: Provides exception handling when a problem occurs.

We will use some of these classes to interact with our sample database.

Connecting to the MySQL database

The first step in using the MySQL database is to establish a connection through the Mysqlconnection class and the database. With a connection string, Mysqlconnection will be instantiated as an example. The connection string will tell the code where to find the MySQL server and some other options.

A connection string tells the code to use the specified username and password to connect to a MySQL server named Mysqltestserver and enter the TechRepublic database. I set up an anonymous login on my test machine (which has a very large security vulnerability, so it is not recommended that you do so on the production server), so the following connection string will be used in the example:

"Server=localhost; Database=sitepoint; "
When a connection string is specified, the Mysqlconnection object's Open method is invoked and the connection is opened. Once the connection is established, you can send commands to the MySQL database or get the data from the database.

Asp. Combination of net and MySQL

Let's take a closer look at the combination of the Mysqlconnection class and other classes to generate a list of databases on a MySQL server. Table B lists a Web form that is written using C #. It establishes a connection, then gives the server an instruction (show DATABASES), and then displays the results through the Mysqlreader object.

Using the Mysqlcommand object to send the show DATABASES command to the MySQL server and enter the command directly into the MySQL administration tool The result is the same. The only difference is that we have to use another object in our code to get the result set. The Mysqldatareader object is instantiated when it obtains the result (through the ExecuteReader method of the Mysqlcommand class). The GetString method of the Mysqldatareader object is used to display the data in the result set through label control. Pointer 0 of the GetString method specifies the first column of data that displays the current row of the result set (in the while loop).

Mono Tips
If you use an open source mono development platform, the code in the example needs only a small change to run normally. MySQL's data interface is ByteFX.Data.MySqlClient this space name, not the MySql.Data.MySqlClient space name on Windows. In fact, the MySQL data interface was originally developed by Bytefx Company, but was acquired by MySQL company. So if you use mono, you have to declare space names like this:

Using ByteFX.Data.MySqlClient;


The combination of MySQL and. NET provides a powerful development platform. MySQL has strong technical support in the open source community. NET is also accepted by the open source community through Mono. Such a combination provides a highly flexible development platform in Windows, and other languages such as UNIX or Linux.


mono and encoding are MySQL's. Net Data Provider, with Mono and Microsoft.NET Version (Microsoft version seems to be called connector/.net).
These two days are trying to use mono and to access the MySQL database.
but found that Chinese strings are stored in the database, all become question marks.
I thought it was a problem with the disposition or setup of my code, and after Trace Mono and's source code, I found it was not.
I'm starting to suspect that MySQL is the problem, and I'm going to ask Google the great God first.
Google God told me that MySQL does not support Unicode until 4.0, and the database preset encoding is latin1.
OK, so why use MediaWiki or some blogs to save Unicode strings??
and a little Trace of the MediaWiki source code, there is no surprise, there is no special treatment, is simply insert, update just.

Finally I looked back at source code, finally found the problem. is encoded according to MySQL's encoding before dropping packets to MySQL.
So no matter what, I in the client how to turn the code, to the final will be converted into latin1, and then deposited. So, the possible solution is as follows:
1. Upgrade MySQL to 4.0 after the version.
2. Before the loss of the database, the first string into byte[], if it is byte[], will not be transcoding, and directly deposited. But then, when the data is taken out, it needs to be done again, and the byte[] is reversed to string.
Solution 2 is quite troublesome, and then if you want to change the database, you will be hit again.
It seems that solution 1 is kingly. But, lazy to upgrade said ... =_=


Choose MySQL or SQL Server

For program developers, the two most popular background databases are MySQL and SQL Server. The basic similarity between the two is the data storage and the query system. You can use SQL to access the data for both databases, because they all support ansi-sql. Also, both of these database systems support binary keywords and key indexes, which greatly speeds up the query. At the same time, both provide various formats that support XML.

Apart from the obvious difference in the price of software, what is the obvious difference between the two products? How do you choose between the two? Let's look at the major differences between the two products, including release fees, performance, and their security.

The fundamental difference is the basic principles they follow.
The basic principles they follow are the main differences: openness vs conservatism. The narrow, conservative storage engine of the SQL Server is extensible with the MySQL server, and the open storage engine is very different. While you can use the SQL Server's Sybase engine, MySQL can provide more options such as MyISAM, Heap, InnoDB, and Berkeley DB. MySQL does not fully support unfamiliar keywords, so it is less relevant than SQL Server database. At the same time, MySQL also lacks some of the functionality of the storage program, such as the MyISAM engine to support switching capabilities.

Issue fee: MySQL is not all free, but very cheap
When it comes to the cost of distribution, the two products use two very different decisions. For SQL Servers, the most common way to get a free development fee is to purchase Microsoft Office or Visual Studio costs. However, if you want to use the commercial product development, you must also purchase the SQL Server Standard Edition. Schools or non-profit enterprises may not consider this additional cost.

Performance: Advanced MySQL
MySQL is pretty good for performance purely because it contains a default desktop format, MyISAM. The MyISAM database is very compatible with disk and does not consume too much CPU and memory. MySQL can run on the Windows system without conflict, and it is better to run on UNIX or similar UNIX systems. You can also use a 64-bit processor to get some extra performance. Because MySQL uses 64-bit integer processing in many cases internally. Yahoo! Commercial website uses MySQL as the background database.

When it comes to software performance, SQL Server stability is much stronger than its competitors. However, these characteristics also have to pay a price. For example, additional complex operations, disk storage, memory loss, and so on must be added. If your hardware and software do not fully support SQL Server, I suggest you better choose other DBMS databases, because you will get better results.

Both databases can be in the. NET or Java-EE operation is normal, the same, can use RAID.

Security features

MySQL has a binary log that changes data. Because it is binary, this log can quickly replicate data from the host to the client. Even if the server crashes, the twos log will remain intact and the copied portions will not be corrupted.

In SQL Server, you can also log queries about SQL, but this is a high price to pay.

Both of these products have their own complete security mechanisms. As long as you follow these security mechanisms, there will be no problem with the general program. Both use the default IP port, but sometimes unfortunately, these IPs are also hacked into by some hackers. Of course, you can also set up these IP ports yourself.

recoverability: Advanced SQL Server
Recoverability is also a feature of MySQL, which is mainly manifested in the MyISAM configuration. This approach has its inherent imperfections, and if you accidentally damage the database, the results may result in all data loss. However, the SQL Server is a very stable key. The SQL Server is able to monitor data exchange points at all times and to preserve the process of database corruption.

Decide your choice as needed
For both of these databases, I might disappoint you if I had to say which one is better. In my opinion, any database that helps you with your work is a good database, no database is absolutely outstanding, and no database is absolutely bad. What I want to tell you is that you should start with your own needs, that is, what kind of task do you want to accomplish? Instead of simply starting with the function of the software.

If you want to build a. NET Server system, this system can access data from many different platforms, participate in database management, then you can choose SQL Server. If you want to build a Third-party site, this site can read data from some clients, then MySQL will be the best choice.


MySQL and. Net2.0 used together

MySQL is now the latest version of 5.x. First contact It was in a sophomore, with PHP, then it was like the 4.x version.

MYSQL5 add a lot of new features to start support: stored procedures, triggers, views, information architecture views, etc. ...

MySQL in the installation as always more complex, often is a failure of the hint, there is no other reason why.

This is an article that compares MySQL and SQL Server,

MySQL Chinese website There is very little information, mostly some installation help.
To check the data or to the MySQL website

MySQL now has a variety of connection tools available ( ,. Net can be used under the CONNECTOR/ODBC and Connector/net.

ODBC connection may be slightly inefficient, preferably with net direct connection
This article describes the various connection methods

1:ODBC Connection
Now the version is 3.51, after installation, you can do this:
String Constr = "DRIVER = {MySQL ODBC 3.51 DRIVER}; SERVER = localhost; DATABASE =test; UID = root; password=; ";

String constr = "Driver={mysql ODBC 3.51 DRIVER}; Server=localhost;database=test; User=root; password=;option=3; ";
String Constr = "Provider = MySQL ODBC 3.51 Driver; SERVER = localhost; DATABASE =test; UID = root; password=; ";

OleDbConnection connection = new OleDbConnection (CONSTR);
Connection. Open ();
catch (Exception ex)
MessageBox.Show (ex. message);
2:net Connection:
MySQL Connector Net 1.0.7: There are net1.0;net.1;net2.0;mono1.0 four versions of Connector. Free
Corelab.mysql 3.5: This is a commercial version of the trial period of 30 days.

The code below is an example of using MySQL Connector Net. Note: The prefix of his parameter is "? "Rather than" @ ". This question is quite special. The prefix of the parameter inside the Corelab is "@".
String connstr = String.Format ("Server={0};user id={1}"; PASSWORD={2}; DATABASE={3}; pooling=false;port=3308 "," localhost "," root "," "", "Test");
Mysqlconnection myconn = new Mysqlconnection (CONNSTR);
MyConn.Open ();
Mysqlcommand cmd = Myconn.createcommand ();

Cmd. Parameters.Add ("? DocName ", Mysqldbtype.varchar, 50);
Cmd. Parameters[0]. Value = "Test by code";
Cmd. Parameters[0]. SourceColumn = "DocName";
Cmd.commandtext = "Update t_docs set docname=?" DocName where docid=4 ";
Cmd. ExecuteNonQuery ();
This is an example of setting provider when using an ORM
CustomProvider Mysqlprovider = new CustomProvider ("", "MySql.Data.MySqlClient.MySqlConnection", " MySql.Data.MySqlClient.MySqlDataAdapter ");
Mysqlprovider.startdelimiter = "";//default is "/"
Mysqlprovider.enddelimiter = "";//default is "/"
Mysqlprovider.parameterprefix = "?"; /Set parameter prefix
Mysqlprovider.selectpagequery = "Select * LIMIT {0} OFFSET {1}";//Set Paging algorithm
Mysqlprovider.identityquery = "Select last_insert_id ()";/set to get the function that just inserted the record ID

3:ole Connection:
There is no official support at the moment.

In vs2005, you can refer directly to the for. net2.0 version of the DLL. As for the commercial version, it's going to take some work, a license file (drag a conection component onto the form to automatically create the license)

attached, the connection string can be queried here , it's full.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.