asp.net 连接myql

首先先安装ODBC驱动MyODBC-standard-3.51.07-win.msi

<appSettings>
  <add key="data" value="Driver={MySQL ODBC 3.51 Driver};server=000.000.000.000;database=xx;uid=xx;password=xx;option=3"></add>

 </appSettings>

/*
 ********************************************************
  DB Access
 
  ******************************************************
*/
namespace Component
{
 using System;
 using System.Data;
 using System.Data.Odbc;
    using System.Data.Common;
 using System.IO;
 using System.Text;
 using System.Collections;
 
 public class DBManage:IDisposable
 {
  private string _conStr;
        private OdbcConnection _odbcCon;
  public DBManage()
  {
   _conStr = SysConfig.ConStr;
            _odbcCon = new OdbcConnection(_conStr);
  }
  public DBManage(string strcon)
  {
   _conStr = strcon;
            _odbcCon = new OdbcConnection(_conStr);
  }
  public void Dispose()
  {
   Dispose(true);
   GC.SuppressFinalize(true);
  }

  protected virtual void Dispose(bool disposing)
  {
   if (! disposing)
    return;

   if( _odbcCon != null  )
   {
    _odbcCon.Dispose();
    _odbcCon = null;
   }
   
  }

  /// <summary>
  /// update
  /// </summary>
  /// <param name="_sqlStr">sql</param>
  /// <returns> when success return true else false</returns>
  public bool SetData(String _sqlStr)
  {
   bool _success = false;
            OdbcCommand _command = new OdbcCommand();
            _command.Connection = _odbcCon;
   
   try
   {
                _odbcCon.Open();
    _command.CommandText = _sqlStr;
    //_command.CommandType = CommandType.StoredProcedure ;
    _command.ExecuteNonQuery();
    _success = true;
   }
   catch
   {
   
   }
   finally
   {
    _odbcCon.Close();
   }
   return _success;

  }

  /// <summary>
  /// update
  /// </summary>
  /// <param name="_sqlStr">sql</param>
  /// <param name="_list">reutrn string after run sql</param>
        /// <returns>when success return true else false</returns>
  public bool SetData(String _sqlStr,ref ArrayList _list)
  {
   bool _success = false;
   OdbcDataReader _reader ;   
   OdbcCommand _command = new OdbcCommand();
   _command.Connection = _odbcCon;
   
   try
   {
    _odbcCon.Open();
    _command.CommandText = _sqlStr;
    //_command.CommandType = CommandType.StoredProcedure;
    _reader=_command.ExecuteReader(CommandBehavior.CloseConnection);
    while(_reader.Read())
    {
     int _i=0;
     int _count = _reader.FieldCount ;
     for(_i=0;_i<_count;_i++)
     {
      _list.Add(_reader.GetValue(_i));
     }
    }
    _reader.Close();
    _success = true;
   }
   catch
   {
    
   }
   finally
   {
    
    _odbcCon.Close();
   }
   return _success;
  }
  
  /// <summary>
  /// get SqlDataReader
  /// </summary>
  /// <param name="_sqlStr">sql</param>
        /// <returns>OdbcDataReader</returns>
        public OdbcDataReader GetDataReader(String commandText)
  {
            OdbcDataReader _reader = null;   
   OdbcCommand _command = new OdbcCommand();
   _command.Connection = _odbcCon;

            try
            {
                if (_odbcCon.State != ConnectionState.Open)
                {
                    _odbcCon.Open();
                }
                _command.CommandText = commandText;
                _command.CommandType = CommandType.Text;
                _reader = _command.ExecuteReader(CommandBehavior.CloseConnection);               
            }
            catch { }
            return _reader;
  }

  /// <summary>
  ///get DataSet
  /// </summary>
  /// <param name="_sqlStr">sql</param>
  /// <returns></returns>
        public DataSet GetDataSet(String commandText)
  {
   DataSet _ds = new DataSet();
            OdbcCommand _command = new OdbcCommand();
            _command.Connection = _odbcCon;
   
   try
   {
                if (_odbcCon.State != ConnectionState.Open)
                {
                    _odbcCon.Open();
                }
                _command.CommandText = commandText;
                _command.CommandType = CommandType.Text;
                OdbcDataAdapter _oda = new OdbcDataAdapter(_command);
                _oda.Fill(_ds);    
   }
   catch
   {
                _ds = null;
            }
            finally
            {
                if (_odbcCon.State == ConnectionState.Open)
                {
                    _odbcCon.Close();
                }
            }
   return _ds;
  }
        public bool FillDataSet(string commandText, DataSet ds)
        {
            bool success = false;
            OdbcCommand _command = new OdbcCommand();
            _command.Connection = _odbcCon;

            try
            {
                if (_odbcCon.State != ConnectionState.Open)
                {
                    _odbcCon.Open();
                }
                _command.CommandText = commandText;
                _command.CommandType = CommandType.Text;
                OdbcDataAdapter _oda = new OdbcDataAdapter(_command);
                _oda.Fill(ds);
                success = true;
            }
            catch
            {
                //
            }
            finally
            {
                if (_odbcCon.State == ConnectionState.Open)
                {
                    _odbcCon.Close();
                }
            }
            return success;
        }
        public DataTableReader GetDataTableReader(string commandText)
        {
            DataSet _ds = new DataSet();
            DataTableReader _dtr;
            OdbcCommand _command = new OdbcCommand();
            _command.Connection = _odbcCon;

            try
            {
                if (_odbcCon.State != ConnectionState.Open)
                {
                    _odbcCon.Open();
                }
                _command.CommandText = commandText;
                _command.CommandType = CommandType.Text;
                OdbcDataAdapter _oda = new OdbcDataAdapter(_command);
                _oda.Fill(_ds);
                _dtr = _ds.CreateDataReader();
            }
            catch
            {
                _dtr = null;
            }
            finally
            {
                if (_odbcCon.State == ConnectionState.Open)
                {
                    _odbcCon.Close();
                }
            }
            return _dtr;
        }
  public bool TestSqlConnection()
  {
   bool _success = false;

   try
   {
    _odbcCon.Open();
    _success = true;
   }
   catch
   {
   }
   finally
   {
    _odbcCon.Close();
   }
   return _success;
  }
        public int ExecuteNonQuery(string commandText)
        {
            int retval = 0;
            OdbcCommand _command = new OdbcCommand();
            _command.Connection = _odbcCon;
            try
            {
                if (_odbcCon.State != ConnectionState.Open)
                {
                    _odbcCon.Open();
                }
                _command.CommandText = commandText;
                _command.CommandType = CommandType.Text;
                retval = _command.ExecuteNonQuery();
            }
            catch{}
            finally
            {
                _odbcCon.Close();
            }
            return retval;
        }
        public object ExecuteScalar(string commandText)
        {
            object retval = null;
            OdbcCommand _command = new OdbcCommand();
            _command.Connection = _odbcCon;
            try
            {
                if (_odbcCon.State != ConnectionState.Open)
                {
                    _odbcCon.Open();
                }
                _command.CommandText = commandText;
                _command.CommandType = CommandType.Text;
                retval = _command.ExecuteScalar();
            }
            catch { }
            finally
            {
                _odbcCon.Close();
            }
            return retval;
        } 
 }
}