C#用反射来封装数据库操作类

这几天在学习反射的应用,就有想法在封装数据库类的时候用上,下面就是这几天封装的类

用的泛型封装的,可以查询不同的表,不用每个表写不同的一长串的Sql语句了.感觉省事多了,

只是在多条件查询时Where后面的要自己手动输入传进去就OK了.

#define OLEDB_
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Reflection;

namespace WindowsFormsApp_反射的应用
{
#if OLEDB_
    using MyCommand = OleDbCommand;
    using MyConnection = OleDbConnection;
    using MyDataAdapter = OleDbDataAdapter;
    using MyDataReader = OleDbDataReader;
    using myParameter = OleDbParameter;
#elif SQL_
    using MyCommand = SqlCommand;
    using MyConnection =SqlConnection;
    using MyDataAdapter =SqlDataAdapter;
    using MyDataReader =SqlDataReader;
    using myParameter = SqlParameter;

#endif

    /// <summary>
    /// 泛型数据库操作类
    /// <para>泛型类要和操作的数据库中表的名称,字段名称都要一样</para>
    /// 
    /// 作者:       greenleaf1976
    /// 创建时间:   2017-07-17
    /// </summary>
    public class MYADO_T
    {
        private static readonly string myDataFileName = @"D:\MyAccessFile\myTonxilu.accdb";

        private static readonly string CONN_STRING = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={myDataFileName};Persist Security Info=False";

        public static MyConnection conn = null;

        public MYADO_T() { }
        ~MYADO_T() { conn.Dispose(); }

        /// <summary>
        /// 初始化conn对象(保证打开连接状态)
        /// </summary>
        private static void InitConnection()
        {
            if (conn == null)
                conn = new MyConnection(CONN_STRING);

            if (conn.State == ConnectionState.Closed)
                conn.Open();

            if (conn.State == ConnectionState.Broken)
            {
                conn.Close();
                conn.Open();
            }
        }

        /// <summary>
        /// 获得表中的数据
        /// </summary>
        /// <typeparam name="T">和表中相对应的类或结构体</typeparam>
        /// <param name="conditionsSql">多种查询条件,格式(mName='张三')</param>
        /// <returns>返回查询结果表</returns>
        public static DataTable GetDataTableAll<T>(string conditionsSql = null)
        {
            DataTable dt = new DataTable();
            MyCommand cmd = null;
            MyDataAdapter da = null;

            Type ty = typeof(T);//获得泛型类型

            //拼接Sql字符串
            string sql = $"SELECT * FROM [{ty.Name}]";
            if (conditionsSql != null)
                sql += $" WHERE {conditionsSql}";

            InitConnection();

            try
            {
                cmd = new MyCommand(sql, conn);
                da = new MyDataAdapter(cmd);
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                da.Dispose();
                cmd.Dispose();
                conn.Close();
            }
            return dt;

        }

        /// <summary>
        /// 更新指定条件的数据
        /// </summary>
        /// <typeparam name="T">和表中相对应的类或结构体</typeparam>
        /// <param name="conditionsSql">多种查询条件,格式(mName='张三' and ...)</param>
        /// <param name="t">用于更新数据库中的新数据</param>
        /// <returns>返回受影响的行数</returns>
        public static int UpdateDataTable<T>(string conditionsSql, T t)
        {
            int nRet = 0;
            string intoSql = null;
            string fileName = null;
            MyCommand cmd = null;
            List<myParameter> parList = new List<myParameter>();

            Type ty = t.GetType();  //获得泛型的类型

            //获得Sql查询语句和添加参数列表
            foreach (FieldInfo v in ty.GetFields())
            {
                fileName += v.Name + $"=@{v.Name},";    //拼接字段名和值的查询字符串
                myParameter par = new myParameter($"@{v.Name}", v.GetValue(t)); //添加参数列表到parList中
                parList.Add(par);
            }
            fileName = fileName.Substring(0, fileName.Length - 1);  //去掉最后一个,号

            //拼接Sql查询语句字符串
            intoSql = $"UPDATE [{ty.Name}] SET {fileName} WHERE {conditionsSql}";

            InitConnection();

            try
            {
                cmd = new MyCommand(intoSql, conn);
                cmd.Parameters.Clear();
                cmd.Parameters.AddRange(parList.ToArray());//添加参数列表
                nRet = cmd.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {

                cmd.Dispose();
                conn.Close();
            }

            return nRet;

        }

        /// <summary>
        /// 删除指定条件的数据
        /// </summary>
        /// <typeparam name="T">和表中相对应的类或结构体</typeparam>
        /// <param name="conditionsSql">多种查询条件,格式(mName='张三' and ...)</param>
        /// <returns>返回受影响的行数</returns>
        public static int DeleatDataTable<T>(string conditionsSql)
        {
            int nRet = 0;
            MyCommand cmd = null;

            Type typ = typeof(T);
            string sql = $"DELETE FROM {typ.Name} WHERE {conditionsSql}";
            try
            {
                InitConnection();
                cmd = new MyCommand(sql, conn);
                nRet = cmd.ExecuteNonQuery();

            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }

            return nRet;
        }

        /// <summary>
        /// 插入新的数据
        /// </summary>
        /// <typeparam name="T">和表中相对应的类或结构体</typeparam>
        /// <param name="t">要插入的新数据对象</param>
        /// <returns>成功返回True,失败False</returns>
        public static bool InsertDataTable<T>(T t)
        {
            int nRet = 0;
            string intoSql = null;
            string fileName = null;
            string fileValue = null;
            MyCommand cmd = null;
            List<myParameter> parList = new List<myParameter>();

            Type ty = t.GetType();

            foreach (FieldInfo v in ty.GetFields())
            {
                fileName += v.Name + ",";   //拼接列名字符串
                fileValue += $"@{v.Name},"; //拼接参数化值的字符串

                //创建新的参数化查询,并添加到列表中
                myParameter par = new myParameter($"@{v.Name}", v.GetValue(t));
                parList.Add(par);
            }
            fileName = fileName.Substring(0, fileName.Length - 1);  //去除最后的,号
            fileValue = fileValue.Substring(0, fileValue.Length - 1);   //去除最后的,号

            //拼接添加数据的Sql语句
            intoSql = $"INSERT INTO [{ty.Name}] ({fileName}) VALUES ({fileValue})";
           
            try
            {
                InitConnection();
                cmd = new MyCommand(intoSql, conn);
                cmd.Parameters.Clear();
                cmd.Parameters.AddRange(parList.ToArray());//添加参数列表
                nRet = cmd.ExecuteNonQuery();  
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
            }



            return nRet == 1;
        }

        /// <summary>
        /// 不断开连接的读取数据,多用于递归查询的调用
        /// </summary>
        /// <typeparam name="T">和表中相对应的类或结构体</typeparam>
        /// <param name="conditionsSql">多种查询条件,格式(mName='张三' and ...)</param>
        /// <returns>返回DataReader对象</returns>
        public static MyDataReader GetDataReaddr<T>(string conditionsSql)
        {
            MyDataReader drRead = null;
            MyCommand cmd = null;


            Type ty = typeof(T);
            string sql = $"SELECT * FROM [{ty.Name}]";
            if (conditionsSql != null)
                sql += $" WHERE {conditionsSql}";

            InitConnection();

            try
            {
                cmd = new MyCommand(sql, conn);
                drRead = cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                throw ex;
            }
           
            return drRead;
        }

        /// <summary>
        /// 查找指定条件,并返回单个对象
        /// </summary>
        /// <typeparam name="T">和表中相对应的类或结构体</typeparam>
        /// <param name="conditionsSql">多种查询条件,格式(mName='张三' and ...)</param>
        /// <returns>返回单个对象的数据</returns>
        public static T FindDataTable<T>(string conditionsSql)
        {
            
            MyDataReader drRead = null;

            Type ty = typeof(T);    //获得泛型的类型
            object obj =Activator.CreateInstance(ty);//根据类型创建新的object实例对象(不要实例成具体的类型)

            
            try
            {
                drRead = GetDataReaddr<T>(conditionsSql);    //读取数据库中的数据         
                if(drRead.Read())
                {
                    foreach (FieldInfo v in ty.GetFields())
                    {
                        //根据字段类型给字段赋值
                        switch(v.FieldType.FullName)
                        {
                            case "System.Int32"://int         
                                v.SetValue(obj, int.Parse(drRead[v.Name].ToString()));
                                break;

                            case "System.Double"://double
                                v.SetValue(obj, double.Parse(drRead[v.Name].ToString()));
                                break;

                            case "System.String"://string
                                v.SetValue(obj, drRead[v.Name].ToString());
                                break;

                            case "System.Object"://object
                                v.SetValue(obj, drRead[v.Name]);
                                break;

                            case "System.Boolean"://bool
                                v.SetValue(obj, bool.Parse(drRead[v.Name].ToString()));
                                break;

                            case "System.Char"://char
                                v.SetValue(obj, char.Parse(drRead[v.Name].ToString()));
                                break;

                            default:    //其他类型,以后用的时候再加上
                                throw new Exception("没有指定转换的类型!!!");

                        }                
                    }
                }
                else
                    return default(T);  //没有找到返回默认的泛型对象,不是null
                
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                drRead.Close();
                conn.Close();
            }
            //返回时转换成对应的泛型类型
            return (T)obj;
        }
    }
}

在程序中调用:

1.显示全部数据

 DataTable dt = MYADO_T.GetDataTableAll<mTable>();
            foreach (DataRow dr in dt.Rows)
            {
                foreach (object v in dr.ItemArray)
                {

                    Console.Write(v.ToString() + "\t");
                }
                Console.WriteLine();
            }

2.查询指定条件

返回一个对象

Form2 frm = new Form2();
            if(frm.ShowDialog()==DialogResult.OK)
            {
                mTable tl = MYADO_T.FindDataTable<mTable>("mName='张三2');
                if (tl.mName == null)
                    MessageBox.Show("没有找到数据");
                else
                    MessageBox.Show(tl.ToString());
            }

 

返回多个对象就返回DataTable 

DataTable dt = MYADO_T.GetDataTableAll<mTable>("mAge>30");
            foreach (DataRow dr in dt.Rows)
            {
                foreach (object v in dr.ItemArray)
                {

                    Console.Write(v.ToString() + "\t");
                }
                Console.WriteLine();
            }

 3.删除数据,这可以删除多个数据,只要符合条件的都会删除

int nRet = MYADO_T.DeleatDataTable<mTable>("mName='张三'");
            Console.WriteLine(nRet);

4.插入数据

 mTable tl = new mTable()
            {
                mName = "李雪琴",
                mSex = "女",
                mAge = 20,
                mFenlei = "明星",
                mTel = "132009833"
            };

//这名没有写泛型类型是因为编译器提示我简化了的.本来我是写了泛型类型的.
            bool bol = MYADO_T.InsertDataTable(tl);
            if (bol)
                MessageBox.Show("添加数据成功!");