C# 连接 MySql 对数据库的增删改查

本文固定连接：http://blog.csdn.net/u013108312/article/details/67632204

新建C#控制台应用程序，添加NHIbernate，NHibernate.Mapping.Attributes的引用。
创建Mysql数据，和表

1.进入mysql数据库：
mysql> use mysql; 
Database changed

2.给root用户设置新密码：  
mysql> update user set password=password("新密码") where user="root"; 
Query OK, 1 rows affected (0.01 sec) 
Rows matched: 1 Changed: 1 Warnings: 0

3.刷新数据库
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)  

4.退出mysql：
mysql> quit; 
Bye

Rebuild.bat

@echo off  
:begin
@echo ----------1, create all game database------------ 
mysql -uroot -p123456<createdb.sql
PAUSE

createdb.sql

drop database if exists testmysql;
create database testmysql character set utf8;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Database.Model {
    class User {
        public virtual int Id { get; set; }
        public virtual string Username{get;set;}
        public virtual string Password { get; set; }
        public virtual int Age{get;set;}
    }
}

Model Mapping文件夹

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using FluentNHibernate.Mapping;

namespace Database.Model.Mapping {
    class TestUserMap  : ClassMap<User> {
        public TestUserMap() {//这里的x是一个testuser的对象
            Id(x => x.Id).Column("id")   ;//设置id属性为主键
            Map(x => x.Username).Column("username");
            Map(x => x.Password).Column("password");
            Map(x => x.Age).Column("age");
            Table("user");
        }
    }
}

using FluentNHibernate.Cfg;
using NHibernate;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using FluentNHibernate.Cfg.Db;

namespace Database {
    class NHibernateHelper {
        private static ISessionFactory sessionFactory = null;//单利模式

        private static void InitializeSessionFactory() {
                sessionFactory =
                    Fluently.Configure().Database(MySQLConfiguration.Standard.ConnectionString(db => db.Server("127.0.0.1").Database("mysqltest").Username("root").Password("123456")))
                        .Mappings(x => x.FluentMappings.AddFromAssemblyOf<NHibernateHelper>()).BuildSessionFactory();
        }

        private static ISessionFactory SessionFactory {
            get {
                if (sessionFactory == null)
                    InitializeSessionFactory();
                return sessionFactory;
            }
        }

        public static ISession OpenSession() {
                return SessionFactory.OpenSession();
        }
    }
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Database.Model;

namespace Database.Manager {
    class UserManager {
        public IList<User> GetAllUser() {
            using (var session = NHibernateHelper.OpenSession()) {
                using (var transaction = session.BeginTransaction()) {
                    var userList = session.QueryOver<User>();
                    transaction.Commit();
                    return userList.List();
                }
            }
        }

        public IList<User> GetUserByUsername(string username)
        {
            using (var session = NHibernateHelper.OpenSession()) {
                using (var transaction = session.BeginTransaction()) {
                    var userList = session.QueryOver<User>().Where(user => user.Username == username);
                    transaction.Commit();
                    return userList.List();
                }
            }
        }

        public void SaveUser(User user)
        {
            using (var session = NHibernateHelper.OpenSession()) {
                using (var transaction = session.BeginTransaction()) {
                    session.Save(user);
                    transaction.Commit();
                }
            }
        }

        public void DeleteById(int id) {
            using (var session = NHibernateHelper.OpenSession()) {
                using (var transaction = session.BeginTransaction()) {
                    User tu = new User();
                    tu.Id = id;
                    session.Delete(tu);
                    transaction.Commit();
                }
            }
        }

        public void UpdateUser(User tu)
        {
            using (var session = NHibernateHelper.OpenSession()) {
                using (var transaction = session.BeginTransaction()) {
                    session.Update(tu);
                    transaction.Commit();
                }
            }
        }

        static void Main(string[] args) {
            UserManager testuserManager = new UserManager();
            IList<User> testuserList = testuserManager.GetAllUser();
            Console.WriteLine("-------testuserManager.GetAllUser()--------");
            foreach (User tu in testuserList)
            {
                Console.WriteLine(tu.Username);
            }
            Console.WriteLine("-------GetUserByUsername--------");
            IList<User> testuserList2 = testuserManager.GetUserByUsername("circle");
            foreach (User tu in testuserList2)
            {
                Console.WriteLine(tu.Username);
            }
            Console.WriteLine("--------------- save");
            User tu2 = new User();
            tu2.Username = "03"; tu2.Password = "03"; tu2.Age = 2;
            //testuserManager.SaveUser(tu2);
            Console.WriteLine("--------------- delete");
            //testuserManager.DeleteById(2);
            Console.WriteLine("--------------- update");
            User tu3 = testuserList2[0];
            tu3.Password = "1234567890";
            testuserManager.UpdateUser(tu3);

            Console.ReadKey();
        }
    }
}