Use C # to connect to the MySQL database

Recently, this project requires the MySQL database to be used in the background, but the foreground must be in the form of C # and ASP. NET. In the past, I learned a set of things from Microsoft, such as SQL Server and LINQ. net is very easy to match, but it is good. By the way, I also learned how MySQL interacts with C #. I 've been searching for it online for a long time and got several ideas:

Use
Nhib.pdf-Recommendation
Mysqlhelper

Later, it was found that nhib.pdf was actually quite complicated. It was better to write code directly as before. I posted a post on csdn, got a few replies, read several posts, and finally connected the data.

Very good Method Introduction: http://www.cnblogs.com/youuuu/archive/2011/06/16/2082730.html

The following is a summary of the mysqlhelper method, which is similar to the article in the above link:

1. Download: mysql.data.rar, reference mysql. Data. dll in the project

2. Add a public class in the project: mysqlhelper. CS, which can be placed in the app_code folder.

3. Compile mysqlhelper. cs. The Code is available in the link, but some functions are not needed. Below are my own:

Using system; using system. data; using system. configuration; using system. LINQ; using system. web; using system. web. security; using system. web. ui; using system. web. UI. htmlcontrols; using system. web. UI. webcontrols; using system. web. UI. webcontrols. webparts; using system. XML. LINQ; using MySQL. data; using MySQL. data. mysqlclient; // <summary> // Summary of mysqlhelper /// </Summary> public abstract class mysqlhelper {// database connection Public static string conn = "database = 'xxxxxx'; Data Source = 'xxxxx'; user id = 'xxxxx'; Password = 'xxxxx'; charset = 'utf8 '; pooling = true "; // XXX indicates the modification. /// <summary> // the database for the given connection executes an SQL command with the hypothetical parameter (no dataset is returned) /// </Summary> /// <Param name = "connectionstring"> A valid connection string </param> /// <Param name = "primitive type"> command type (stored procedure, text, etc.) </param> /// <Param name = "plain text"> stored procedure name or SQL command statement </param> /// <Param name = "commandparameter S "> set of parameters used for command execution </param> // <returns> Number of lines affected by command execution </returns> Public static int executenonquery (string connectionstring, commandtype limit type, string plain text, Params mysqlparameter [] commandparameters) {mysqlcommand cmd = new mysqlcommand (); Using (mysqlconnection conn = new mysqlconnection (connectionstring) {preparecommand (CMD, Conn, null, struct type, optional text, commandparameters); int val = cmd. executen Onquery (); cmd. parameters. clear (); Return Val ;}/// <summary> // use an existing database connection to execute an SQL command (no dataset is returned) /// </Summary> /// <Param name = "connection"> an existing database connection </param> /// <Param name = "primitive type"> command type (stored procedure, text, etc) </param> /// <Param name = "plain text"> stored procedure name or SQL command statement </param> /// <Param name = "commandparameters"> used to execute the command set of parameters </param> /// <returns> Number of lines affected by command execution </returns> Public static int executenonquery (mysqlconnectio N connection, commandtype parameter type, string parameter text, Params mysqlparameter [] commandparameters) {mysqlcommand cmd = new mysqlcommand (); preparecommand (CMD, connection, null, parameter type, parameter text, commandparameters ); int val = cmd. executenonquery (); cmd. parameters. clear (); Return Val ;}/// <summary> /// execute an SQL command using an existing SQL transaction (no dataset is returned) /// </Summary> /// <remarks> // example: // int result = executenonquery (Conn String, commandtype. storedprocedure, "publishorders", new mysqlparameter ("@ prodid", 24 )); /// </remarks> /// <Param name = "trans"> an existing transaction </param> /// <Param name = "Param type"> command type (stored Procedure, text, etc) </param> /// <Param name = "plain text"> stored procedure name or SQL command statement </param> /// <Param name = "commandparameters"> used to execute the command set of parameters </param> /// <returns> Number of lines affected by command execution </returns> Public static int executenonquery (mysqltransaction trans, comm Andtype parameter type, string parameter text, Params mysqlparameter [] commandparameters) {mysqlcommand cmd = new mysqlcommand (); preparecommand (CMD, trans. connection, trans, argument type, plain text, commandparameters); int val = cmd. executenonquery (); cmd. parameters. clear (); Return val ;} /// <summary> /// use the executed database to connect to execute an SQL command to return the dataset /// </Summary> /// <remarks> /// example: /// mysqldatareader r = executereader (connstring, Commandtype. storedprocedure, "publishorders", new mysqlparameter ("@ prodid", 24 )); /// </remarks> /// <Param name = "connectionstring"> A valid connection string </param> /// <Param name = "connector type"> command type (stored procedure, text, etc) </param> /// <Param name = "plain text"> stored procedure name or SQL command statement </param> /// <Param name = "commandparameters"> used to execute the command set of parameters </param> /// <returns> reader containing results </returns> Public static mysqldatareader executereader (string connec Tionstring, commandtype primitive type, string plain text, Params mysqlparameter [] commandparameters) {// create a mysqlcommand object mysqlcommand cmd = new mysqlcommand (); // create a mysqlconnection object mysqlconnection conn = new mysqlconnection (connectionstring); // here we use a try/catch structure to execute SQL text commands/stored procedures, if this method produces an exception, we need to close the connection because no reader exists, // so commandbehaviour. closeconnection will not execute try {// call the preparecommand method, set the mysqlcommand object Parameter preparecommand (CMD, Conn, null, struct type, plain text, commandparameters); // call mysqlcommand's executereader method mysqldatareader reader = cmd. executereader (commandbehavior. closeconnection); // clear the CMD parameter. parameters. clear (); Return reader;} catch {// closes the connection and throws an exception Conn. close (); throw ;}} /// <summary> /// return dataset /// </Summary> /// <Param name = "connectionstring"> A valid connection string </param> // /<Param name = "polictyp E "> command type (stored procedure, text, and so on) </param> /// <Param name = "plain text"> stored procedure name or SQL command statement </param> /// <Param name = "commandparameters"> used to execute the command parameter set </param> /// <returns> </returns> Public static dataset getdataset (string connectionstring, commandtype parameter type, string plain text, Params mysqlparameter [] commandparameters) {// create a mysqlcommand object mysqlcommand cmd = new mysqlcommand (); // create a mysqlconnection object mysqlconnection Conn = new mysqlconnection (connectionstring); // here we use a try/catch structure to execute SQL text commands/stored procedures. If this method produces an exception, we need to close the connection, because no reader exists, try {// call the preparecommand method and set the parameter preparecommand (CMD, Conn, null, primitive type, plain text, commandparameters) for the mysqlcommand object ); // call the executereader method of mysqlcommand mysqldataadapter adapter = new mysqldataadapter (); adapter. selectcommand = cmd; dataset DS = new dataset (); adapter. fill (DS );/ /Clear the CMD parameter. parameters. clear (); Conn. close (); Return DS ;}catch (exception e) {Throw e ;}} /// <summary> /// execute a command with the specified database connection string and return the first column of a dataset /// </Summary> /// <remarks> // /example: /// object OBJ = executescalar (connstring, commandtype. storedprocedure, "publishorders", new mysqlparameter ("@ prodid", 24 )); /// </remarks> /// <Param name = "connectionstring"> A valid connection string </param> /// <Param name = "connector type"> command type (Stored procedure, text, etc) </param> /// <Param name = "plain text"> stored procedure name or SQL command statement </param> /// <Param name = "commandparameters"> used to execute the command set of parameters </param> /// <returns> Use convert. to {type} converts the type to the desired </returns> Public static object executescalar (string connectionstring, commandtype primitive type, string plain text, Params mysqlparameter [] commandparameters) {mysqlcommand cmd = new mysqlcommand (); Using (mysqlconnection connection = ne W mysqlconnection (connectionstring) {preparecommand (CMD, connection, null, struct type, plain text, commandparameters); object val = cmd. executescalar (); cmd. parameters. clear (); Return Val ;}} /// <summary> /// run a command with the specified database connection and return the first column of a dataset /// </Summary> /// <remarks> /// for example: /// object OBJ = executescalar (connstring, commandtype. storedprocedure, "publishorders", new mysqlparameter ("@ prodid", 24 ));/// </Remarks> /// <Param name = "connection"> an existing database connection </param> /// <Param name = "Param type"> command type (Stored Procedure, text, etc) </param> /// <Param name = "plain text"> stored procedure name or SQL command statement </param> /// <Param name = "commandparameters"> used to execute the command set of parameters </param> /// <returns> Use convert. to {type} converts the type to the desired </returns> Public static object executescalar (mysqlconnection connection, commandtype limit type, string plain text, Params mysqlparameter [] comma Ndparameters) {mysqlcommand cmd = new mysqlcommand (); preparecommand (CMD, connection, null, struct type, plain text, commandparameters); object val = cmd. executescalar (); cmd. parameters. clear (); Return val ;} /// <summary> /// prepare to execute a command /// </Summary> /// <Param name = "cmd"> SQL command </param> /// <Param name = "conn"> oledb connection </param> // <Param name = "trans"> oledb transaction </param> /// <Param name = "concurrent type "> command type, such as stored procedure or text </Param> /// <Param name = "plain text"> command text, for example: select * from products </param> // <Param name = "parms"> parameters for executing commands </param> Private Static void preparecommand (mysqlcommand cmd, mysqlconnection Conn, mysqltransaction trans, commandtype primitive type, string plain text, mysqlparameter [] partition parms) {If (Conn. state! = Connectionstate. Open) Conn. open (); cmd. Connection = conn; cmd. commandtext = plain text; If (trans! = NULL) cmd. Transaction = trans; cmd. commandtype = partition type; If (partition parms! = NULL) {foreach (mysqlparameter parm in parallel parms) cmd. Parameters. Add (parm );}}}

4. Add code during use

The last gridview on the page

Dgv. datasource = mysqlhelper. getdataset (mysqlhelper. conn, commandtype. text, "select * from XXXX", null ). tables [0]. defaultview; // XXX is the system table dgv. databind ();

5. Check in the browser, provided that there is data in the database

So even if C # is successfully connected to MySQL, this is the first step.