. NET + MySQL Database Application

. NET + MySQL Development (Database Access) Favorites
New article: Mobile Database Development tutorial | old article: Using. Net to develop DB2 database applications (1)

The Code is as follows:
You need to download the mysql. Net Package MySQL connector/NET 1.0 from the MySQL official website and reference it to the current project.

Database category: sqlhelper. CS

///. Net accessing MySQL Database Class
// Http://blog.hnce.net

Using system;
Using system. Data;

Using mysql. Data. mysqlclient;

Namespace mysqldal
{
/// <Summary>
/// Summary of sqlhelper.
/// </Summary>
Public class sqlhelper
{

Public static readonly string conn_str = getconnstring. getmysqlconnstring ();
// You can directly write the MySQL database link string here, for example:
// "Data Source = 127.0.0.1; user id = slick; Password = 123456; database = test"

Public sqlhelper ()
{
//
// Todo: add the constructor logic here
//
}

/// <Summary>
/// Use the provided function to execute the SQL command and return a record set from the specified connected database
/// </Summary>
/// <Param name = "connectionstring"> sqlconnection valid SQL connection string </param>
/// <Param name = "commandtype"> commandtype: commandtype. Text, commandtype. storedprocedure </param>
/// <Param name = "commandtext"> SQL statement or stored procedure </param>
/// <Param name = "commandparameters"> sqlparameter [] parameter array </param>
/// <Returns> sqldatareader: record set of execution results </returns>

Public static mysqldatareader executereader (string connstring,
Commandtype parameter type, string parameter text, Params mysqlparameter [] parameter parms)
{
Mysqlcommand cmd = new mysqlcommand ();
Mysqlconnection conn = new mysqlconnection (connstring );

// Here we use try/catch because if this method throws an exception, we aim to close the database connection and then throw an exception,
// Because no datareader exists, commandbehaviour. closeconnection will not work afterwards.
Try
{
Preparecommand (CMD, Conn, null, partition type, plain text, plain parms );
Mysqldatareader RDR = cmd. executereader (commandbehavior. closeconnection );
Cmd. Parameters. Clear ();
Return RDR;
}
Catch
{
Conn. Close ();
Throw;
}
}

/// <Summary>
/// Use the provided function to execute the SQL command and return a record set from the specified connected database
/// </Summary>
/// <Param name = "connectionstring"> sqlconnection valid SQL connection string </param>
/// <Param name = "commandtype"> commandtype: commandtype. Text, commandtype. storedprocedure </param>
/// <Param name = "commandtext"> SQL statement or stored procedure </param>
/// <Returns> sqldatareader: record set of execution results </returns>
Public static mysqldatareader executereader (string connstring, commandtype primitive type, string plain text)
{
Return executereader (connstring, struct type, plain text, (mysqlparameter []) null );
}

/// <Summary>
/// Run the SQL command with parameters using the provided method. The returned value is the number of rows affected by the command.
/// </Summary>
/// <Param name = "connstring"> sqlconnection valid SQL connection string </param>
/// <Param name = "primitive type"> commandtype: commandtype. Text, commandtype. storedprocedure </param>
/// <Param name = "plain text"> SQL statement or stored procedure </param>
/// <Param name = "Parameters"> parameter set included in the SQL statement </param>
/// <Returns> INT: number of lines affected by the command </returns>

Public static int executenonquery (string connstring, commandtype
Parameter type, string parameter text, Params mysqlparameter [] parameter parameters)
{

Mysqlcommand cmd = new mysqlcommand ();

Using (mysqlconnection conn = new mysqlconnection (connstring ))
{
Preparecommand (CMD, Conn, null, partition type, plain text, parameter parameters );
Int val = cmd. executenonquery ();
Cmd. Parameters. Clear ();
Return val;
}
}

/// <Summary>
/// Run the SQL command using the provided method. The returned value is the number of rows affected by the command.
/// </Summary>
/// <Param name = "connstring"> sqlconnection valid SQL connection string </param>
/// <Param name = "primitive type"> commandtype: commandtype. Text, commandtype. storedprocedure </param>
/// <Param name = "plain text"> SQL statement or stored procedure </param>
/// <Returns> INT: number of lines affected by the command </returns>
Public static int executenonquery (string connstring, commandtype literal type, string literal text)
{
Return executenonquery (connstring, struct type, plain text, (mysqlparameter []) null );
}

/// <Summary>
/// Use the provided method to execute an SQL command with parameters. The returned value is the first column of the first line of the result set or an empty reference (if the result set is empty ).
/// </Summary>
/// <Param name = "connstring"> sqlconnection valid SQL connection string </param>
/// <Param name = "primitive type"> commandtype: commandtype. Text, commandtype. storedprocedure </param>
/// <Param name = "plain text"> SQL statement or stored procedure </param>
/// <Param name = "Parameters"> parameter set included in the SQL statement </param>
/// <Returns> </returns>

Public static string executescalar (string connstring, commandtype
Parameter type, string parameter text, Params mysqlparameter [] parameter parameters)
{
Mysqlcommand cmd = new mysqlcommand ();
Using (mysqlconnection conn = new mysqlconnection (connstring ))
{
Preparecommand (CMD, Conn, null, partition type, plain text, parameter parameters );
Return cmd. executescalar (). tostring ();
}

}

/// <Summary>
/// Run the SQL command using the provided method. The returned value is the first column or empty reference in the first line of the result set (if the result set is empty ).
/// </Summary>
/// <Param name = "connstring"> sqlconnection valid SQL connection string </param>
/// <Param name = "primitive type"> commandtype: commandtype. Text, commandtype. storedprocedure </param>
/// <Param name = "plain text"> SQL statement or stored procedure </param>
/// <Returns> </returns>
Public static string executescalar (string connstring, commandtype limit type, string limit text)
{
Return executescalar (connstring, struct type, plain text, (mysqlparameter []) null );
}

/// <Summary>
/// Run the SQL command with parameters using the provided method. The returned value is the dataset.
/// </Summary>
/// <Param name = "connstring"> sqlconnection valid SQL connection string </param>
/// <Param name = "primitive type"> commandtype: commandtype. Text, commandtype. storedprocedure </param>
/// <Param name = "plain text"> SQL statement or stored procedure </param>
/// <Param name = "Parameters"> parameter set included in the SQL statement </param>
/// <Returns> datat: result set of the command </returns>

Public static dataset executedataset (string connstring, commandtype
Parameter type, string parameter text, Params mysqlparameter [] parameter parameters)
{
Mysqlcommand cmd = new mysqlcommand ();

Using (mysqlconnection conn = new mysqlconnection (connstring ))
{

Preparecommand (CMD, Conn, null, partition type, plain text, parameter parameters );
Mysqldataadapter Ada = new mysqldataadapter (CMD );
Dataset DS = new dataset ();
Ada. Fill (DS );
Cmd. Parameters. Clear ();
Return Ds;
}
}

/// <Summary>
/// Run the SQL command using the provided method. The returned value is the dataset.
/// </Summary>
/// <Param name = "connstring"> sqlconnection valid SQL connection string </param>
/// <Param name = "primitive type"> commandtype: commandtype. Text, commandtype. storedprocedure </param>
/// <Param name = "plain text"> SQL statement or stored procedure </param>
/// <Returns> datat: result set of the command </returns>
Public static dataset executedataset (string connstring, commandtype plain type, string plain text)
{
Return executedataset (connstring, struct type, plain text, (mysqlparameter []) null );
}

/// <Summary>
/// Prepare for executing the command: Open the database connection, command statement, set the command type (SQL statement or stored procedure), function language.
/// </Summary>
/// <Param name = "cmd"> sqlcommand component </param>
/// <Param name = "conn"> sqlconnection component </param>
/// <Param name = "trans"> sqltransaction component, which can be null </param>
/// <Param name = "primitive type"> statement type: commandtype. Text, commandtype. storedprocedure </param>
/// <Param name = "plain text"> SQL statement, which can be a stored procedure </param>
/// <Param name = "paiparms"> SQL parameter array private </param>

Public static void preparecommand (mysqlcommand cmd, mysqlconnection
Conn, mysqltransaction trans, commandtype primitive type, string plain text,
Mysqlparameter [] extends parms)
{

If (conn. State! = Connectionstate. open)
Conn. open ();

Cmd. Connection = conn;
Cmd. commandtext = plain text;
 
If (trans! = NULL)
Cmd. Transaction = trans;

Cmd. commandtype = primitive type;

If (partition parms! = NULL)
{
Foreach (mysqlparameter parm in milliseconds parms)
Cmd. Parameters. Add (parm );
}
}
}
}

Read data: webform1.aspx. CS
Using system;
Using system. Data;
Using mysqldal;

Public const string SQL _select_content = "select catid, username, subject from supe_spaceitems ";

Public dataset get_dataset ()
{
Return sqlhelper. executedataset (sqlhelper. conn_str, commandtype. Text, SQL _select_content );
}

Private void page_load (Object sender, system. eventargs E)
{
// Place user code here to initialize the page
Datagrid1.datasource = get_dataset ();
Datagrid1.databind ();

}

The preceding sqlhelper. CS can be used to implement functions such as insertion, deletion, and query. The preceding is only a common query. You can also perform a query with parameters. Note that the parameter symbol of SQL Server is: @ and in MySQL :?

Because this is referenced in my project in layers, data is separated from logical processing and page display, and the code prototype does not work like this, this is just an example. You can adjust it when using it.