2018-06-05

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

public class MysqlManager
{
string ip = "127.0.0.1"; //本机地址
string username = "root";
string password = "123456";
string dbName = "qikuedu";

MySqlConnection connet;
MySqlCommand command;
public MysqlManager()
{
    connet = new MySqlConnection();
    connet.ConnectionString = "server=" + ip + ";user id=" + username + ";password=" + password + ";database=" + dbName;
    connet.Open();
    Console.WriteLine("打开数据库成功");

    command = connet.CreateCommand();


    ////创建的脚本  产生空表格
    //Dictionary<string, string> skills = new Dictionary<string, string>();
    //skills.Add("id", "int primary key");
    //skills.Add("name", "varchar(10)");
    //skills.Add("attack", "int");
    //skills.Add("ranges", "float");
    ////根据字典创建表
    //CreateTable("Skill", skills);  //Bill表格的名字，不区分大小写


    //增加信息
    //InsertInto("skill");

    //删除信息
    //DeletFrom("skill");

    //改信息
    //UpdateSet("skill");

    //查信息
    //SelectFrom("skill");

//------------------------------------------------------------------------
User user = new User();
user.id = 1;
user.username = "123456@qq.com";
user.nickname = "小马哥";
user.password = "123456";

    //创建空表格
    //CreateTable<User>();
    //插入数值
    //InsertInto(user);
    //删除数据
    //DeleteFrom<User>(1);
    //改
    //查
    List<User> users = SelectFrom<User>();
}

//CreateTable<T>(T t)where T:class,new()   冒号后说明T必须是类 new是意思必须有构造函数  
//CreateTable<T>(T t)where T:struck   冒号说明T必须是值类型 值类型不能new
//CreateTable<T>(T t)where T:User，new() 说明参数必须是User类

//CreateTable(user); 上方法
//void CreateTable<T>(T t) where T : class  等同创建  下方法

void CreateTable<T>() where T :class
{
    Type type = typeof(T);  //大写T代表类，小写t代表对象
    string tableName = type.Name; //这是类的名字，就是以后生成表格的名字
    System.Reflection.FieldInfo[] fileds = type.GetFields(); //这里面是User里的几个字段名字
    string sql = "create table " + tableName + " (";
    foreach (var item in fileds)
    {

        //string dbType = "";
        //if (item.FieldType.Name=="String")   //item.Name是列名字，item.FieldType.Name是对应列名字的类型
        //{
        //    dbType = "Text";   //因为没有string类型，所以需要强转
        //}
        //if (item.FieldType.Name=="Int")
        //{
        //    dbType = "Int";
        //}
        //if (item.FieldType.Name == "Float")
        //{
        //    dbType = "Float";
        //}
        if (item.Name == "id")
            sql += item.Name + " " + CS2DB(item.FieldType) + " primary key auto_increment,"; //设置k键
        else
            sql += item.Name + " " + CS2DB(item.FieldType) + ",";
    }
    sql = sql.TrimEnd(',')+")";
    ExcuteSql(sql);
}
string CS2DB(Type type)
{
    string dbType = "";
    if (type.Name == "String")
    {
        dbType = "Text";
    }
    else if (type.Name == "Int32")
    {
        dbType = "Int";
    }
    else if (type.Name == "Float")
    {
        dbType = "Float";
    }
    return dbType;
}
//插入数值
void InsertInto<T>(T t) where T:class
{
    Type type = typeof(T);  
    string tableName = type.Name;
    System.Reflection.FieldInfo[] fileds = type.GetFields(); 
    string sql = "insert into " + tableName + " values(";
    foreach (var item in fileds)
    {           
            string value = item.GetValue(t).ToString();
            sql += "'"+value + "' ,";         
    }
    sql = sql.TrimEnd(',') + ")";
    ExcuteSql(sql);
}
//删除
void DeleteFrom<T>(int id)
{
    string tableName = typeof(T).Name;
    string sql = "delete from " + tableName + " " + "where id = " + id;
    ExcuteSql(sql);
}
//查
List<T> SelectFrom<T>()where T:class,new()
{
    Type type = typeof(T);
    string tableName = type.Name;
    string sql = "select * from " + tableName;

    command.CommandText = sql;
    MySqlDataReader reader = command.ExecuteReader(); //以阅读器查询

    List<T> array = new List<T>();
    while (reader.Read())  //只要有数据就可以读
    {
        T t = new T();  //一个人就是一个t
        int count = reader.FieldCount;  //获取表格有几列
        for (int i = 0; i < count; i++)
        {
            string key = reader.GetName(i);//列名
            object value = reader.GetValue(i);//列值

            type.GetField(key).SetValue(t,value); //获取表格数值，把数值填充到t，最后返回t
            //type.GetField("username").SetValue(t, "123456@qq.com");
            //type.GetField(key).SetValue(t,value);

            Console.WriteLine(key + ":" + value);
        }
        array.Add(t);
    }
    return array;
}

//--------------------------------------------------------------------------
void CreateTable(string tabelName, Dictionary<string, string> infos)
{
    string sql = "create table " + tabelName + "(";  //表的名字
    foreach (var item in infos)
    {
        sql += item.Key + " " + item.Value + ",";   //列名+列类型
    }
    sql = sql.TrimEnd(',') + ");";
    ExcuteSql(sql);
}

void ExcuteSql(string sql)
{
    Console.WriteLine("sql->" + sql);
    command.CommandText = sql;
    command.ExecuteNonQuery();  //数据库
}
//增
void InsertInto(string tableName)
{
    string sql = "insert into " + tableName + " " + "values(1,'暴风雪',10,10)";
    ExcuteSql(sql);
}


//删
void DeletFrom(string tableName)
{
    string sql = "delete from " + tableName + " " + "where id = 1";  //是通过索引删除全部
    ExcuteSql(sql);
}
//改
void UpdateSet(string tableName)
{
    string sql = "update " + tableName + " " + "set name = '无间' where id = 1"; //通过索引查询，将名字更改
    ExcuteSql(sql);
}
//查
void SelectFrom(string tableName)
{
    string sql = "select * from " + tableName;

    command.CommandText = sql;
    MySqlDataReader reader = command.ExecuteReader(); //以阅读器查询


    while (reader.Read())  //只要有数据就可以读
    {
        int count = reader.FieldCount;  //获取表格有几列
        for (int i = 0; i < count; i++)
        {
            string key = reader.GetName(i);//列名
            object value = reader.GetValue(i);//列值
            Console.WriteLine(key + ":" + value);
        }
    }
}

}