asp.net mysql数据库连接_asp.net 连接 MySQL数据库

1.添加MySql.Data.dll引用

2.SQLHelper类：

private string strConn;

private MySqlConnection sqlConn;

private MySqlCommand sqlComm;

private string errMsg = null;

private int errNumber = 0;

#region Member

public string ErrorMessage

{

get { return errMsg; }

}

public int ErrorNumber

{

get { return errNumber; }

}

#endregion

public SqlHelper()

{

strConn = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;

}

//public SqlHelper(string dbName)

//{

//    strConn = ConfigurationManager.ConnectionStrings[dbName].ConnectionString;

//}

///

/// query db. get recordSet. Must close.

///

/// T-SQL command string

///

/// SqlDataReader Or Null

public MySqlDataReader ExecuteReader(string commandText)

{

MySqlDataReader sqlDR = null;

try

{

sqlConn = new MySqlConnection(strConn);

sqlConn.Open();

sqlComm = new MySqlCommand(commandText, sqlConn);

sqlDR = sqlComm.ExecuteReader(CommandBehavior.CloseConnection);

}

catch (MySqlException e)

{

errMsg = e.Message;

errNumber = e.Number;

if (sqlConn.State == ConnectionState.Open)

sqlConn.Close();

}

return sqlDR;

}

///

/// Opreation db. include: insert and update and delete.

///

/// T-SQL command string

/// True or False

public bool ExecuteNonQuery(string commandText)

{

bool result = true;

try

{

sqlConn = new MySqlConnection(strConn);

sqlConn.Open();

sqlComm = new MySqlCommand(commandText, sqlConn);

sqlComm.ExecuteNonQuery();

}

catch (MySqlException e)

{

result = false;

errMsg = e.Message;

errNumber = e.Number;

}

if (sqlConn.State == ConnectionState.Open)

sqlConn.Close();

return result;

}

///

/// Get a DataSet

///

/// T-SQL command string

/// DataSet type

public DataSet ExecuteDataSet(string commandText)

{

DataSet ds = new DataSet();

MySqlDataAdapter da = new MySqlDataAdapter(commandText, new MySqlConnection(strConn));

da.Fill(ds);

return ds;

}

///

/// Get a DataTable.

///

/// T-SQL command string

/// DataTable type

public DataTable ExecuteDataTable(string commandText)

{

DataTable dt = new DataTable();

MySqlDataAdapter da = new MySqlDataAdapter(commandText, new MySqlConnection(strConn));

da.Fill(dt);

return dt;

}

///

/// Execute a transacton.NonQuery.

///

/// T-SQL command string set

/// True or False

public bool ExecuteTransaction(List commandTextList)

{

bool result = true;

sqlConn = new MySqlConnection(strConn);

MySqlTransaction sqlTrans = null;

try

{

sqlConn.Open();

sqlTrans = sqlConn.BeginTransaction();

sqlComm = new MySqlCommand();

sqlComm.Connection = sqlConn;

sqlComm.Transaction = sqlTrans;

sqlComm.CommandType = CommandType.Text;

foreach (string commandText in commandTextList)

{

sqlComm.CommandText = commandText;

sqlComm.ExecuteNonQuery();

}

sqlTrans.Commit();

}

catch (MySqlException e)

{

sqlTrans.Rollback();

errMsg = e.Message;

errNumber = e.Number;

result = false;

}

if (sqlConn.State == ConnectionState.Open)

sqlConn.Close();

return result;

}

///

/// Execute a StroreProcedure.

///

/// StroeProcedure Name

/// Dictionary type

/// True or False. if execute failed, ErrorNumber=@@error and ErrorMessag is NULL.

public bool ExecuteProcedure(string storeProcedureName, Dictionary paraList)

{

bool result = true;

try

{

sqlConn = new MySqlConnection(strConn);

sqlConn.Open();

sqlComm = new MySqlCommand(storeProcedureName, sqlConn);

sqlComm.CommandType = CommandType.StoredProcedure;

foreach (KeyValuePair kvp in paraList)

{

sqlComm.Parameters.Add(new MySqlParameter(kvp.Key, kvp.Value));

}

int spReturnValue = (int)sqlComm.ExecuteScalar();

if (spReturnValue != 0)

{

errNumber = spReturnValue;

errMsg = null;

result = false;

}

}

catch (MySqlException e)

{

errMsg = e.Message;

errNumber = e.Number;

result = false;

}

if (sqlConn.State == ConnectionState.Open)

sqlConn.Close();

return result;         }