MySQL C#教程

这是关于MySQL数据库的C#教程，包含了对MySQL数据库基本操作；

数据库访问组件MySql Connect/NET

MySql Connect/NET是MySQL官方提供给C#的接口，封装的非常好，操作MySQL就如同操作自家的SQLServer；

MySql Connect/NET

开始程序之旅

数据库模型层UserEntity.cs

using System;
using System.Collections.Generic;
using System.Text;

namespace AccessOfMysql.Entity
{
    public class MysqlConfig
    {
        //主机
        private string server;
        public string Server
        {
            get
            {
                return server;
            }
            set
            {
                server = value;
            }
        }

        //数据库
        private string database;
        public string Database
        {
            get
            {
                return database;
            }
            set
            {
                database = value;
            }
        }

        //用户id
        private string userid;
        public string Userid
        {
            get
            {
                return userid;
            }
            set
            {
                userid = value;
            }
        }

        //密码
        private string password;
        public string Password
        {
            get
            {
                return password;
            }
            set
            {
                password = value;
            }
        }
    }
    public class UserEntity
    {
        /// <summary>
        /// 用户id
        /// </summary>
        private uint id;
        public uint Id 
        {
            get
            {
                return id;
            }
            set
            {
                id = value;
            }
        }
        /// <summary>
        /// 用户姓名
        /// </summary>
        private string name;
        public string Name 
        {
            get
            {
                return name;
            }
            set 
            {
                name = value;
            }
        }
        /// <summary>
        /// 用户住址
        /// </summary>
        private string address;
        public string Address
        {
            get
            {
                return address;
            }
            set
            { 
                address = value; 
            }
        }
    }
}

数据库访问层UserAccess.cs

using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using AccessOfMysql.Entity;

namespace AccessOfMysql
{
    class UserAccess
    {
        private string connectString;
        
        public UserAccess(string connectString)
        {
            this.connectString = connectString;
        }

        public List<UserEntity> GetUserListFromDb()
        {
            string query = @"SELECT * 
                             FROM t_user";

            List<UserEntity> userEntityList = new List<UserEntity>();

            using (MySqlConnection connection = new MySqlConnection(this.connectString))
            {
                //打开数据库连接
                connection.Open();

                //创建SqlCommand对象
                MySqlCommand command = new MySqlCommand(query, connection);

                //执行SQL，返回查询结果
                using (MySqlDataReader dataReader = command.ExecuteReader())
                {
                    while (dataReader.Read())
                    {
                        UserEntity userEntity = new UserEntity();
                        userEntity.Id = dataReader.GetUInt32(0);
                        userEntity.Name = dataReader.GetString(1);
                        userEntity.Address = dataReader.GetString(2);
                        userEntityList.Add(userEntity);
                    }
                }
            }
            return userEntityList;
        }

        public void InserUserToDb(UserEntity user)
        {
            string query = @"INSERT INTO t_user (name, address) 
                             VALUES (@name, @address)";

            using (MySqlConnection connection = new MySqlConnection(this.connectString))
            {
                //打开数据库
                connection.Open();

                //创建SqlCommand
                MySqlCommand command = new MySqlCommand(query, connection);

                command.Parameters.AddWithValue("@name", user.Name);
                command.Parameters.AddWithValue("@address", user.Address);
                
                //执行SQL语句，不查询
                command.ExecuteNonQuery();
            }
        }

        public int GetUserCountFromDb()
        {
            string query = @"SELECT COUNT(*) 
                             FROM t_user";
            using (MySqlConnection connection = new MySqlConnection(connectString))
            {
                //打开数据库
                connection.Open();

                MySqlCommand command = new MySqlCommand(query, connection);
                //执行SQL，返回条目数
                int count = int.Parse(command.ExecuteScalar().ToString());
                
                return count;
            }
        }
    }
}

主程序Program.cs

using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using AccessOfMysql.Entity;

namespace AccessOfMysql
{
    class Program
    {
        static void Main(string[] args)
        {
           
            //数据库连接字符串（charset=utf8:解决插入汉字乱码问题）
            string connectString = "server=127.0.0.1;database=cjtdb;uid=root;pwd=root;charset=utf8";
            UserAccess userAccess = new UserAccess(connectString);
            try
            {
                //取得用户信息一览
                List<UserEntity> userEntityList = userAccess.GetUserListFromDb();
                foreach(UserEntity userEntity in userEntityList)
                {
                    Console.WriteLine("id={0}, name={1}, address={2}", 
                        userEntity.Id, userEntity.Name, userEntity.Address);
                }

                //插入用户信息
                UserEntity user = new UserEntity();
                user.Name = "李小龙";
                user.Address = "上海";
                userAccess.InserUserToDb(user);

                //统计用户信息总条数
                int count = userAccess.GetUserCountFromDb();
            }
            catch (MySqlException ex)
            {
                Console.WriteLine(ex.ToString());
            }       
        }
    }
}