数据库管理器学习记录

public class MySqlManager
{
    //数据库连接对象
    private MySqlConnection _conn;
    //数据库名字
    private string _dbName = "test";
    //数据库源
    private string _dataSource = "127.0.0.1";
    //用户名
    private string _userid = "root";
    //密码
    private string _pws = "123456";


    //连接
    public void Connect()
    {
        string str = string.Format("Server = {0};UserID = {1};Password ={2};Database = {3}",_dataSource,_userid,_pws,_dbName);
        _conn = new MySqlConnection(str);
    }

    //执行非查询命令
    public void ExecNonQuery(string sql)
    {
        MySqlCommand command = new MySqlCommand(sql, _conn);

        _conn.Open();
        command.ExecuteNonQuery();
        command.Dispose();
        _conn.Close();
    }

    //执行查询命令
    public List<T> ExecQuery<T>(string sql)
    {
        List<T> objList = new List<T>();
        //创建一个mysql命令
        MySqlCommand command = new MySqlCommand(sql, _conn);

        //打开数据库
        _conn.Open();

        MySqlDataReader reader = null;
        //获取数据库读取器
        reader = command.ExecuteReader();

        //开始读
        while(reader.Read())
        {
            T obj = Activator.CreateInstance<T>();
            FieldInfo[] fields = typeof(T).GetFields();

            for (int i = 0; i < reader.FieldCount; i++)
            {
                ReadField<T>(obj, fields[i], reader, i);
            }
            objList.Add(obj);
        }

        reader.Close();
        _conn.Close();
        command.Dispose();

        return objList;
    }

    public Dictionary<int,T> ExecQueryDic<T> (string sql)
    {
        Dictionary<int, T> objList = new Dictionary<int, T>();

        MySqlCommand command = new MySqlCommand(sql, _conn);
        MySqlDataReader reader = null;

        _conn.Open();

        reader = command.ExecuteReader();

        while(reader.Read())
        {
            T obj = Activator.CreateInstance<T>();
            FieldInfo[] fields = typeof(T).GetFields();

            for (int i = 0; i < fields.Length; i++)
            {
                ReadField<T>(obj, fields[i], reader,i);
            }

            FieldInfo fieldInfo = obj.GetType().GetField("id");
            int id = (int)fieldInfo.GetValue(obj);

            if(!objList.ContainsKey(id))
            {
                objList.Add(id, obj);
            } 
        }

        reader.Close();
        _conn.Close();
        command.Dispose();

        return objList;
    }

    //从数据库中读取数据 并赋值
    private void ReadField<T>(T obj,FieldInfo fieldInfo,MySqlDataReader reader,int ordinal)
    {
        Type type = fieldInfo.FieldType;
        System.Object value = null;

        if (type == typeof(int))
            value = reader.GetFieldValue<int>(ordinal);
        else if (type == typeof(byte))
            value = reader.GetFieldValue<byte>(ordinal);
        else if (type == typeof(uint))
            value = reader.GetFieldValue<uint>(ordinal);
        else if (type == typeof(string))
            value = reader.GetFieldValue<string>(ordinal);
        else if (type == typeof(float))
            value = reader.GetFieldValue<float>(ordinal);

        fieldInfo.SetValue(obj, value);

    }
}