C # Implementation operation MySQL data layer class Mysqlhelper instance

The example in this paper describes the implementation of C # MySQL data layer class Mysqlhelper. Share to everyone for your reference. Specific as follows:

Using system;using system.data;using system.configuration;using system.collections.generic;using System.Linq;using System.text;using system.xml.linq;using mysql.data;using mysql.data.mysqlclient;namespace VideoWorks.ITmanage.DAL{ Public abstract class Mysqlhelper {//database connection string public static string Conn = "database= ' device_manage ';D ata source= ' Localh OST '; User id= ' root '; password= ' 123456 '; charset= ' UTF8 ';p ooling=true;  Allow Zero datetime=true "; <summary>////The database for a given connection executes an SQL command with the assumed parameters (no data set returned)///</summary>//<param name= "connectionString" > A A valid connection string </param>//<param name= "cmdtype" > Command type (stored procedure, text, etc.) </param>//<param name= "Cmdtext" & gt; Stored procedure name or SQL command statement </param>//<param name= "Commandparameters" > Collection 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 connectionString, CommandType cmdtype, String cmdtext, params mysqlparameter[] commandparameters) {Mysqlcommand cmd =New Mysqlcommand (); using (mysqlconnection conn = new Mysqlconnection (connectionString)) {PrepareCommand (CMD, conn, null, Cmdtype, Cmdte    XT, Commandparameters); int val = cmd.    ExecuteNonQuery (); Cmd.    Parameters.clear ();   return Val; }}////<summary>///Use an existing database connection to execute an SQL command (do not return a dataset)///</summary>//<param name= "Connection" > an existing number Database connection </param>//<param name= "cmdtype" > Command type (stored procedure, text, etc.) </param>//<param name= "Cmdtext" > stored procedure name or SQL command statement </param>//<param name= "Commandparameters" > Collection 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 (mysqlconnection connection, CommandType Cmdtype,   String cmdtext, params mysqlparameter[] commandparameters) {mysqlcommand cmd = new Mysqlcommand ();   PrepareCommand (cmd, connection, NULL, Cmdtype, Cmdtext, commandparameters); int val = cmd.   ExecuteNonQuery (); Cmd.   Parameters.clear ();  return Val; }//<summary>/Executes an SQL command with an existing SQL transaction (does not return a dataset)//</summary>//<remarks>///int result = ExecuteNonQuery (connstr  ING, CommandType.StoredProcedure, "Publishorders", New Mysqlparameter ("@prodid", 24)); </remarks>//<param name= "Trans" > an existing transaction </param>//<param name= "cmdtype" > Command type (stored procedure, text , etc.) </param>//<param name= "cmdtext" > stored procedure name or SQL command statement </param>//<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 (Mysqltransaction trans, CommandType Cmdtype, String cmdtext, params mysqlparameter[] Commandparameters   ) {Mysqlcommand cmd = new Mysqlcommand (); PrepareCommand (cmd, trans.   Connection, trans, 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 performed///</summary>//<remarks>///Mysqldatarea Der R = ExecuteReader (connstring, CommandType.StoredProcedure, "Publishorders", New Mysqlparameter ("@prodid", 24)); </remarks>//<param name= "connectionString" > a valid Connection string </param>//<param name= "Cmdtype" > Command type (stored procedure, text, etc.) </param>//<param name= "cmdtext" > stored procedure name or SQL command statement </param>//<param name= "Commandparameters" > Collection of parameters used to execute the command </param>//<returns> reader containing results </returns> public static Mysqldatareader ExecuteReader (String connectionString, CommandType cmdtype, String cmdtext, 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 parameters to Mysqlcommand object PrepareCommand (CMD, conn, null, Cmdtype, Cmdtext, Commandparame    ters);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= "connectionString" > a valid connection string &LT;/PA ram>//<param name= "cmdtype" > Command type (stored procedure, text, etc.) </param>//<param name= "Cmdtext" > stored procedure name or SQL command statement </param>//<param name= "Commandparameters" > Collection of parameters used to execute the command </param>///<returns ></returns> public static DataSet GetDataSet (String connectionString, CommandType cmdtype, string Cmdtext, para   Ms 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 no reader exists, try {//Call PreparecommanD method, set parameters for 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>//<remarks>///For example://Obje  CT obj = executescalar (connstring, CommandType.StoredProcedure, "Publishorders", New Mysqlparameter ("@prodid", 24)); </remarks>///<param name= "connectionString" > a valid Connection string </param>//<param name= "Cmdtype" > Command type (stored procedure, text, etc.) </param>//<param name= "cmdtext" > stored procedure name or SQL command statement </param>//<param name=  "Commandparameters" > set of parameters used to execute the command </param>//<returns> convert type to desired </returns> with Convert.to{type} public static OBJECT ExecuteScalar (String connectionString, CommandType cmdtype, String cmdtext, params mysqlparameter[] Commandparameters   ) {Mysqlcommand cmd = new Mysqlcommand (); using (mysqlconnection connection = new Mysqlconnection (connectionString)) {PrepareCommand (cmd, connection, NULL, CM    DType, Cmdtext, commandparameters); Object val = cmd.    ExecuteScalar (); Cmd.    Parameters.clear ();   return Val;  }}/////<summary> executes a command with the specified database connection and returns 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= "cmdtype" > Command type (Stored procedure, text, etc.) </param>//<param name= "cmdtext" > stored procedure name or SQL command statement </param>//<param name= "  Commandparameters "> The set 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 (mysqlconnection connection, CommandType Cmdtype, String cmdtext, params mysqlparameter[]   commandparameters) {Mysqlcommand cmd = new Mysqlcommand ();   PrepareCommand (cmd, connection, NULL, Cmdtype, Cmdtext, 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= "Cmdtype "> command type such as stored procedure or text </param>//<param name=" cmdtext "> Command text, for example: Select * from products</param>//&lt ;p Aram Name= "cmdparms" > Parameters for execute command </param> private static void PrepareCommand (Mysqlcommand cmd, mysqlconnection Conn, Mysqltransaction Trans, CommandType Cmdtype, String cmdtext, mysqlparameter[] 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 parm in cmdparms) cmd.   Parameters.Add (Parm); }  } }}

Hopefully this article will help you with your C # programming.