Heim >Datenbank >MySQL-Tutorial >在.NET连接MySQL

在.NET连接MySQL

WBOY
WBOYOriginal
2016-06-01 13:16:281078Durchsuche

1、首先上MySQL网站下驱动:http://www.mysql.com/products/connector/

2、安装下载的安装包

3、我们在Visual Studio里创建一个Web Application,然后引入X:/XXXX/MySQL/MySQL Connector Net 6.3.6/Assemblies下的v2.0或v4.0里的DLL文件。

在MySQLHelper里面加入:

using MySql.Data.MySqlClient;

 在Web.config里面加入MySQL数据库的连接字符串:

<connectionstrings><add name="MySqlStr" connectionstring="server = localhost; user id = XXXX; password = XXXX; database = XXXX"></add></connectionstrings>

 4、封装好的MySQLHelper类

using System;using System.Collections.Generic;using System.Data;using System.Collections;using MySql.Data.MySqlClient;using System.Configuration;namespace DBUtility{/// <summary>/// The SQLHelper class is intended to encapsulate high performance,/// scalable best practices for common uses of SqlClient./// </summary>public abstract class MySQLHelper{	// Read the connection strings from the configuration file	public static readonly string ConnectionStringLocalTransaction = ConfigurationManager.ConnectionStrings["OraConnString1"].ConnectionString;	public static readonly string ConnectionStringInventoryDistributedTransaction = ConfigurationManager.ConnectionStrings["OraConnString2"].ConnectionString;	public static readonly string ConnectionStringOrderDistributedTransaction = ConfigurationManager.ConnectionStrings["OraConnString3"].ConnectionString;	public static readonly string ConnectionStringProfile = ConfigurationManager.ConnectionStrings["OraProfileConnString"].ConnectionString;	public static readonly string ConnectionStringMembership = ConfigurationManager.ConnectionStrings["OraMembershipConnString"].ConnectionString;	//Create a hashtable for the parameter cached	private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());	/// <summary>	/// Execute a database query which does not include a select	/// </summary>	/// <param name="connString">Connection string to database	/// <param name="cmdType">Command type either stored procedure or SQL	/// <param name="cmdText">Acutall SQL Command	/// <param name="commandParameters">Parameters to bind to the command	/// <returns></returns>	public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)	{	// Create a new MySql command	MySqlCommand cmd = new MySqlCommand();	//Create a connection	using (MySqlConnection connection = new MySqlConnection(connectionString))	{		//Prepare the command		PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);		//Execute the command		int val = cmd.ExecuteNonQuery();		cmd.Parameters.Clear();		return val;	}	}	/// <summary>	/// Execute an MySqlCommand (that returns no resultset) against an existing database transaction	/// using the provided parameters.	/// </summary>	/// <remarks>	/// e.g.:	///int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter(":prodid", 24));	/// </remarks>	/// <param name="trans">an existing database transaction	/// <param name="commandType">the CommandType (stored procedure, text, etc.)	/// <param name="commandText">the stored procedure name or PL/SQL command	/// <param name="commandParameters">an array of MySqlParamters used to execute the command	/// <returns>an int representing the number of rows affected by 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 an MySqlCommand (that returns no resultset) against an existing database connection	/// using the provided parameters.	/// </summary>	/// <remarks>	/// e.g.:	///int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter(":prodid", 24));	/// </remarks>	/// <param name="conn">an existing database connection	/// <param name="commandType">the CommandType (stored procedure, text, etc.)	/// <param name="commandText">the stored procedure name or PL/SQL command	/// <param name="commandParameters">an array of MySqlParamters used to execute the command	/// <returns>an int representing the number of rows affected by 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>	/// Execute a select query that will return a result set	/// </summary>	/// <param name="connString">Connection string	//// <param name="commandType">the CommandType (stored procedure, text, etc.)	/// <param name="commandText">the stored procedure name or PL/SQL command	/// <param name="commandParameters">an array of MySqlParamters used to execute the command	/// <returns></returns>	public static MySqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)	{	//Create the command and connection	MySqlCommand cmd = new MySqlCommand();	MySqlConnection conn = new MySqlConnection(connectionString);	try	{		//Prepare the command to execute		PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);		//Execute the query, stating that the connection should close when the resulting datareader has been read		MySqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);		cmd.Parameters.Clear();		return rdr;	}	catch	{		//If an error occurs close the connection as the reader will not be used and we expect it to close the connection		conn.Close();		throw;	}	}	/// <summary>	/// Execute an MySqlCommand that returns the first column of the first record against the database specified in the connection string	/// using the provided parameters.	/// </summary>	/// <remarks>	/// e.g.:	///Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter(":prodid", 24));	/// </remarks>	/// <param name="connectionString">a valid connection string for a SqlConnection	/// <param name="commandType">the CommandType (stored procedure, text, etc.)	/// <param name="commandText">the stored procedure name or PL/SQL command	/// <param name="commandParameters">an array of MySqlParamters used to execute the command	/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>	public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)	{	MySqlCommand cmd = new MySqlCommand();	using (MySqlConnection conn = new MySqlConnection(connectionString))	{		PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);		object val = cmd.ExecuteScalar();		cmd.Parameters.Clear();		return val;	}	}	/// <summary>	/// Execute a MySqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction	/// using the provided parameters.	/// </summary>	/// <param name="transaction">A valid SqlTransaction	/// <param name="commandType">The CommandType (stored procedure, text, etc.)	/// <param name="commandText">The stored procedure name or PL/SQL command	/// <param name="commandParameters">An array of MySqlParamters used to execute the command	/// <returns>An object containing the value in the 1x1 resultset generated by the command</returns>	public static object ExecuteScalar(MySqlTransaction transaction, CommandType commandType, string commandText, params MySqlParameter[] commandParameters)	{	if (transaction == null)		throw new ArgumentNullException("transaction");	if (transaction != null && transaction.Connection == null)		throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");	// Create a command and prepare it for execution	MySqlCommand cmd = new MySqlCommand();	PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);	// Execute the command & return the results	object retval = cmd.ExecuteScalar();	// Detach the SqlParameters from the command object, so they can be used again	cmd.Parameters.Clear();	return retval;	}	/// <summary>	/// Execute an MySqlCommand that returns the first column of the first record against an existing database connection	/// using the provided parameters.	/// </summary>	/// <remarks>	/// e.g.:	///Object obj = ExecuteScalar(conn, CommandType.StoredProcedure, "PublishOrders", new MySqlParameter(":prodid", 24));	/// </remarks>	/// <param name="conn">an existing database connection	/// <param name="commandType">the CommandType (stored procedure, text, etc.)	/// <param name="commandText">the stored procedure name or PL/SQL command	/// <param name="commandParameters">an array of MySqlParamters used to execute the command	/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>	public static object ExecuteScalar(MySqlConnection connectionString, CommandType cmdType, string cmdText, params MySqlParameter[] commandParameters)	{	MySqlCommand cmd = new MySqlCommand();	PrepareCommand(cmd, connectionString, null, cmdType, cmdText, commandParameters);	object val = cmd.ExecuteScalar();	cmd.Parameters.Clear();	return val;	}	/// <summary>	/// Add a set of parameters to the cached	/// </summary>	/// <param name="cacheKey">Key value to look up the parameters	/// <param name="commandParameters">Actual parameters to cached	public static void CacheParameters(string cacheKey, params MySqlParameter[] commandParameters)	{	parmCache[cacheKey] = commandParameters;	}	/// <summary>	/// Fetch parameters from the cache	/// </summary>	/// <param name="cacheKey">Key to look up the parameters	/// <returns></returns>	public static MySqlParameter[] GetCachedParameters(string cacheKey)	{	MySqlParameter[] cachedParms = (MySqlParameter[])parmCache[cacheKey];	if (cachedParms == null)		return null;	// If the parameters are in the cache	MySqlParameter[] clonedParms = new MySqlParameter[cachedParms.Length];	// return a copy of the parameters	for (int i = 0, j = cachedParms.Length; i 	/// Internal function to prepare a command for execution by the database	/// 	/// <param name="cmd">Existing command object	/// <param name="conn">Database connection object	/// <param name="trans">Optional transaction object	/// <param name="cmdType">Command type, e.g. stored procedure	/// <param name="cmdText">Command test	/// <param name="commandParameters">Parameters for the command	private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, CommandType cmdType, string cmdText, MySqlParameter[] commandParameters)	{	//Open the connection if required	if (conn.State != ConnectionState.Open)		conn.Open();	//Set up the command	cmd.Connection = conn;	cmd.CommandText = cmdText;	cmd.CommandType = cmdType;	//Bind it to the transaction if it exists	if (trans != null)		cmd.Transaction = trans;	// Bind the parameters passed in	if (commandParameters != null)	{		foreach (MySqlParameter parm in commandParameters)		cmd.Parameters.Add(parm);	}	}	/// <summary>	/// Converter to use boolean data type with MySql	/// </summary>	/// <param name="value">Value to convert	/// <returns></returns>	public static string MySqlBit(bool value)	{	if (value)		return "Y";	else		return "N";	}	/// <summary>	/// Converter to use boolean data type with MySql	/// </summary>	/// <param name="value">Value to convert	/// <returns></returns>	public static bool MySqlBool(string value)	{	if (value.Equals("Y"))		return true;	else		return false;	}}}
Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn