C# MySQL连接及数据写入和检索



1.代码

--------------------------------------------------------------------------------------

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

namespace WindowsFormsApplication3
{
    public partial class Form1 : Form
    {
        /// <summary>
        ///
        /// </summary>
        public Form1()
        {
            InitializeComponent();
        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void Form1_Load(object sender, EventArgs e)
        {

        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button1_Click(object sender, EventArgs e)
        {
            MySqlConnection pubsSQLConn = new MySqlConnection();  //db 连接
            String conStr = "server=localhost;user id=root;pwd=root;database=vb;port=3306"; //连接字符串
            pubsSQLConn.ConnectionString = conStr;
            pubsSQLConn.Open();

            MySqlTransaction tr = pubsSQLConn.BeginTransaction(); //事务
            String sql = "select * from user"; //如果是select之类语句，最好用个dataAdapter

            MySqlCommand cmd = new MySqlCommand(sql, pubsSQLConn, tr);
            MySqlDataAdapter adpt = new MySqlDataAdapter(cmd);
            DataTable table = new DataTable();
            //Dim ds As New DataSet
            BindingSource BS = new BindingSource();
            //Dim records As Integer = cmd.ExecuteNonQuery()  '执行
            adpt.Fill(table);
            //BS.DataSource = ds.Tables("信息数据库")
            dataGridView1.DataSource = table;
        }
        /// <summary>
        ///
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button2_Click(object sender, EventArgs e)
        {
            MySqlConnection pubsSQLConn = new MySqlConnection();  //db 连接
            String conStr = "server=localhost;user id=root;pwd=root;database=vb;port=3306"; //连接字符串
            pubsSQLConn.ConnectionString = conStr;
            pubsSQLConn.Open();

            MySqlTransaction tr = pubsSQLConn.BeginTransaction(); //事务
            String sql = "insert into user(userName,passWord,authority) values('test2006','test2006','test2006')"; //如果是select之类语句，最好用个dataAdapter

            MySqlCommand cmd = new MySqlCommand(sql, pubsSQLConn, tr);

            int records = cmd.ExecuteNonQuery();  //执行
            if (records >= 1)
            {
                tr.Commit();  //提交　　提交失败也可回滚  我这边就不定了．
            }
            else
            {
                tr.Rollback();
            }
        }
    }
}

-------------------------------------------------------------------------------------------------

DB表参照【VB MySQL】