C # Connect, access MySQL database

First, the Preparation tool

Visual Stuido (this example uses Visual Studio 2010)

MySql.Data.dll

Mysql_installer_community_v5.6.21.1_setup.1415604646.msi (online search to download or request from me, contact information in http://xunwn.com/1010100)

Second, new dbutility universal access MySQL class library

1. Create a new Dbutility class library in the solution as a common SQL statement storage location for MySQL access



2. Introduction of MySql.Data.dll



3. Configure the Web. config MySQL database connection string

Open the Web. config configuration file under the solution root and add the MySQL connection string to the connectionstrings node

  <connectionStrings>    <add name= "dbconnectionstring" connectionstring= "Data source=127.0.0.1;database= The name of your database; User id= username; password= password "providername=" MySql.Data.MySqlClient "/>  </connectionStrings>

4, the new SQLHelper Universal MySQL Access class, the definition of this class is as follows:

<summary>///sqlhelper Summary description///</summary> public abstract class SQLHelper {//database connection character  String (Note: "dbconnectionstring" here must be consistent with the connectionstrings node value in the Web. config file) public static readonly string connectionString = system.configuration.configurationmanager.connectionstrings["Dbconnectionstring"].        ToString ();        Hash table for cache parameters private static Hashtable Parmcache = hashtable.synchronized (New Hashtable ()); <summary>////The database for a given connection executes an SQL command (without returning a dataset) using the assumed parameters///</summary>//<param name= "Cmdt ype "> Command type (stored procedure, text, etc.) </param>//<param name=" cmdtext "> stored procedure name or SQL command statement </param>///        Lt;param name= "Commandparameters" > The set of parameters used to execute the command </param>//<returns> The number of rows affected by the execution of the command </returns> public static int ExecuteNonQuery (string cmdtext, commandtype cmdtype = CommandType.Text, params mysqlparameter[] command Parameters) {Mysqlcommand cmd = new MYsqlcommand ();  using (mysqlconnection conn = new Mysqlconnection (connectionString)) {PrepareCommand (CMD, conn,                NULL, Cmdtype, Cmdtext, commandparameters); int val = cmd.                ExecuteNonQuery (); Cmd.                Parameters.clear ();            return Val; }}////<summary> execute a SQL command that returns a dataset with the database connection executed///</summary>//<remar Ks>///For example://Mysqldatareader r = ExecuteReader (connstring, CommandType.StoredProcedure, "Publishorder        S ", New Mysqlparameter (" @prodid ", 24)); </remarks>//<param name= "cmdtype" > Command type (stored procedure, text, etc.) </param>//<param name= "cm        Dtext "> Stored procedure name or SQL command statement </param>//<param name=" Commandparameters "> Collection of parameters used to execute the command </param>  <returns> Reader with results </returns> public static Mysqldatareader ExecuteReader (string cmdtext, CommandType Cmdtype = Commandtype.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 the SQL text command/stored procedure, because if this method produces an exception we want to close the connection because there is no reader present,//So commandbehaviour.closeconnection will not execute try {//Call PrepareCommand method, set parameter PrepareCommand (cmd for Mysqlcommand object)                , conn, NULL, Cmdtype, Cmdtext, commandparameters); Call Mysqlcommand's ExecuteReader method mysqldatareader reader = cmd.                ExecuteReader (commandbehavior.closeconnection); Clears the parameter cmd.                Parameters.clear ();            return reader; } catch {//close connection, throw exception Conn.                Close ();            Throw }}///<summary>//Return dataset//</summary>//<param name= "cmdtype" > Command type (stored procedure, text, etc.) </param>//<param name= "Cmdtext" > Stored Process name or SQL command statement </param>//<param name= "Commandparameters" > set of parameters used to execute the command </param>///<retur ns></returns> public static DataSet GetDataSet (String cmdtext, commandtype cmdtype = CommandType.Text, par AMS mysqlparameter[] commandparameters) {//Create a Mysqlcommand object Mysqlcommand cmd = new Mysqlco            Mmand ();            Create a Mysqlconnection object Mysqlconnection conn = new mysqlconnection (connectionString); Here we use a try/catch structure to execute the SQL text command/stored procedure, because if this method produces an exception we want to close the connection because no reader exists, try {//Call PREPA                Recommand method, set parameters to Mysqlcommand object PrepareCommand (CMD, conn, null, Cmdtype, Cmdtext, commandparameters);                Call Mysqlcommand's ExecuteReader method Mysqldataadapter adapter = new Mysqldataadapter (); Adapter.SelectCommand = cmd;                DataSet ds = new DataSet (); Adapter.                Fill (DS); Clears the parameter cmd.                Parameters.clear (); Conn.                Close ();            return DS;            } catch (Exception e) {throw e; }}/////<summary> executes a command with the specified database connection string and returns the first column of a DataSet///</summary>//&lt ;remarks>////For example://Object obj = ExecuteScalar (connstring, CommandType.StoredProcedure, "Publishorders"        , New Mysqlparameter ("@prodid", 24)); </remarks>//<param name= "cmdtype" > Command type (stored procedure, text, etc.) </param>//<param name= "cm        Dtext "> Stored procedure name or SQL command statement </param>//<param name=" Commandparameters "> Collection of parameters used to execute the command </param> <returns> use Convert.to{type} to convert the type to the desired </returns> public static object ExecuteScalar (String Cmdtext, CommandType cmdtype = CommandType.Text,params mysqlparameter[] commandparameters) {mysqlcommand cmd = new Mysqlcommand ();  using (mysqlconnection connection = new Mysqlconnection (connectionString)) {PrepareCommand (cmd,                Connection, NULL, Cmdtype, Cmdtext, commandparameters); Object val = cmd.                ExecuteScalar (); Cmd.                Parameters.clear ();            return Val; }}///<summary> Add the parameter collection to the cache///</summary>//<param name= "Cacheke Y "> variables added to the cache </param>//<param name=" Commandparameters "> A collection of SQL parameters to be added to the cache </param> Publi c static void Cacheparameters (string cacheKey, params mysqlparameter[] commandparameters) {PARMCACHE[CA        Chekey] = commandparameters; }///<summary>//Retrieve the cache parameter collection///</summary>//<param name= "CacheKey" > used to retrieve the reference Number of keywords </param>////<returns> Cached parameter collection </returns> public static mysqlparameter[] Getcachedparameters (string cacheKey) {mysqlparameter[            ] Cachedparms = (mysqlparameter[]) Parmcache[cachekey];            if (cachedparms = = null) return null;            mysqlparameter[] clonedparms = new Mysqlparameter[cachedparms.length]; for (int i = 0, j = cachedparms.length; I < J; i++) clonedparms[i] = (mysqlparameter) ((icloneable) Cached Parms[i]).            Clone ();        return clonedparms; }///<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= "cmdtype" > Command type such as stored procedure or text </param>//<param name= "cmdtext" > Command text, for example: S Elect * from products</param>///<param Name= "cmdparms" > Parameters to execute command </param> private static VOID PrepareCommand (mysqlcommand cmd, mysqlconnection conn, mysqltransaction Trans, CommandType Cmdtype, string cmdText, M Ysqlparameter[] cmdparms) {if (conn. state! = ConnectionState.Open) Conn.            Open (); Cmd.            Connection = conn;            Cmd.commandtext = Cmdtext; if (trans! = null) cmd.            Transaction = trans;            Cmd.commandtype = Cmdtype;                    if (cmdparms! = null) {foreach (mysqlparameter parameter in cmdparms) { if (parameter. Direction = = Parameterdirection.inputoutput | | Parameter. Direction = = ParameterDirection.Input) && (parameter. Value = = null) {parameter.                    Value = DBNull.Value; } cmd.                Parameters.Add (parameter); }            }        }    }

Third, access to the MySQL database (additions and deletions to check)

Hint: SQL statements, whether Oracle, MySQL, SQL Server, and so on, and so on, and so on, are insert, delete, update, select operation, so familiar with a database operation statement, it is also relatively easy to learn other database statements

Using system.collections.generic;using system.text;using mysql.data.mysqlclient;using System.Data;using DBUtility;

　　

1, insert operation (insert)

Example code:

        public int Add (int userId, string name)        {            StringBuilder sb = new StringBuilder ();            Sb. Append ("INSERT into T_photo (Name,userid)");            Sb. Append ("VALUES (? Name,? UserID) ");            mysqlparameter[] Parameters = {                                              new Mysqlparameter ("? Name ", mysqldbtype.string),                                             new Mysqlparameter ("? UserID ", Mysqldbtype.int32)                                         };            Parameters[0]. Value = name;            PARAMETERS[1]. Value = userId;            Return Sqlhelper.executenonquery (sb.) ToString (), CommandType.Text, parameters);        }

　　

2. Delete operation

Example code:

        public int Delete (long id, int userId)        {            StringBuilder sb = new StringBuilder ();            Sb. Append ("DELETE from T_photo WHERE ID =? id and UserID =?") UserID ");            mysqlparameter[] Parameters = {                                              new Mysqlparameter ("? ID", Mysqldbtype.int64),                                             new Mysqlparameter ("? UserID ", Mysqldbtype.int32)                                         };            Parameters[0]. Value = ID;            PARAMETERS[1]. Value = userId;            Return Sqlhelper.executenonquery (sb.) ToString (), CommandType.Text, parameters);        }

　　

3. Modification operation (UPDATE)

Example code:

        public int editname (long id, int userId, string name)        {            StringBuilder sb = new StringBuilder ();            Sb. Append ("UPDATE t_photo SET Name =?") Name WHERE ID =? id and UserID =? UserID ");            mysqlparameter[] Parameters = {                                              new Mysqlparameter ("? ID", Mysqldbtype.int64),                                             new Mysqlparameter ("? UserID ", Mysqldbtype.int32),                                             new Mysqlparameter ("? Name ", mysqldbtype.string)                                         };            Parameters[0]. Value = ID;            PARAMETERS[1]. Value = userId;            PARAMETERS[2]. Value = name;            Return Sqlhelper.executenonquery (sb.) ToString (), CommandType.Text, parameters);        }

　　

4. Query operation (SELECT)

Example code:

        Public Mysqldatareader Getlistbyordercode (string ordercode)        {            StringBuilder sb = new StringBuilder ();            Sb. Append ("Select Id,ordercount,subtotal,productid,");            Sb. Append ("from T_orderdetail  ");            Filter conditions            sb. Append ("WHERE Ordercode =?") Ordercode ");            Sort            sb. Append ("ORDER by ID DESC");            mysqlparameter[] Parameters = {                                              new Mysqlparameter ("? Ordercode ", mysqldbtype.string)                                         };            Parameters[0]. Value = Ordercode;            Return Sqlhelper.executereader (sb.) ToString (), CommandType.Text, parameters);        }

　　

5. Call the stored procedure

Example code:

        public int backorder (long id)        {            StringBuilder sb = new StringBuilder ();            Sb. Append ("backorder");//stored Procedure name            mysqlparameter[] Parameters = {                                              new Mysqlparameter ("? OrderId ", Mysqldbtype.int64)//orderid must be consistent with the stored procedure parameter name, data type                                         };            Parameters[0]. Value = ID;            Return Mycustomsqlhelper.executenonquery (sb.) ToString (), commandtype.storedprocedure, parameters);        }

　　

Today to share here, are their own accumulation of experience to share, if there are questions please contact me, thank you, http://xunwn.com/1010100 will share more to everyone

more shares:http://xunwn.com/photo/1010100

C # Connect, access MySQL database