C#-操作Mysql

 

Nuget添加库

 

 

 

 

公共类

 

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace mysql_nf
{
    class Mysql_Helper
    {



        private MySqlConnection myConnection;
        private MySqlCommand myCommand;
        private MySqlDataAdapter myAdapter;
        private MySqlTransaction myTransaction;
        string str_Con = "data source=172.20.168.210;user id=root;pwd=QSMC+12345;initial catalog=jinwei;allow zero datetime=true";
        //建立DB连接
        public  Mysql_Helper()
        {

            string contString = str_Con;
            try
            {
                myConnection = new MySqlConnection();
                myConnection.ConnectionString = contString;
                myConnection.Open();

            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                MessageBox.Show("连接失败！");
            }
            finally
            {
                myConnection.Close();
            }
        }
        //数据查询操作
        public DataTable executeQuery(String sql)
        {
            DataTable myTable;
            try
            {
                myCommand = myConnection.CreateCommand();
                myCommand.CommandText = sql;
                myAdapter = new MySqlDataAdapter(myCommand);
                DataSet mySet = new DataSet();
                myAdapter.Fill(mySet, "selectDa");
                myTable = mySet.Tables["selectDa"];
                return myTable;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                myConnection.Close();
            }
        }

        //数据插入,删除,更新操作
        public Boolean executeUpdate(String sql)
        {
            try
            {
                myCommand = myConnection.CreateCommand();
                myCommand.CommandText = sql;
                myCommand.ExecuteNonQuery();
                if (myTransaction == null)
                {
                    myConnection.Close();
                    myConnection = null;
                }
                return true;
            }
            catch (Exception ex)
            {
                if (myTransaction != null)
                {
                    myTransaction.Rollback();
                    myTransaction = null;
                    MessageBox.Show("数据发生错误，正在启用事务回滚！");
                }
                else if (myConnection == null)
                {
                    MessageBox.Show("请启用事务！");
                }
                else
                {
                    MessageBox.Show("发生错误！");
                }
                Console.WriteLine(ex);
                return false;
            }
            finally
            {
                myConnection.Close();
            }
        }
        //创建事务
        public void createTransaction()
        {
            try
            {
                myTransaction = myConnection.BeginTransaction();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                myConnection.Close();
            }
        }
        //提交事务
        public void commitTransaction()
        {
            try
            {
                if (myTransaction != null) myTransaction.Commit();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                myConnection.Close();
                myConnection = null;
            }
        }








    }
}