C#反射在数据库连接时的应用

 

反射的应用

1.查找指定ID的数据

//查找指定id的数据,返回指定的对象

               private T FindData<T>(int id)

        {

            Type ty = typeof(T);

 

//在这里创建实例对象的时候不能转成指定的类型,只能是Object对象

//转成指定类型后面就赋值不成功

 

                            // T t=(T)Activator.CreateInstance(ty);

//原来我是这么写的,也能创建实例对象,但后面怎么赋值都不行,全是NULL,

//特别要注意这点,要赋值完后才转成指定的类型

 

            object obj = Activator.CreateInstance(ty);

 

            string sql = $"select * from [{ty.Name}] where ID={id}";

            OleDbConnection conn = new OleDbConnection(connString);

            OleDbCommand cmd = new OleDbCommand(sql, conn);

            conn.Open();

            OleDbDataReader dr = cmd.ExecuteReader();

            dr.Read();

            foreach (var v in ty.GetFields())

            {

                if(v.FieldType.FullName=="System.Int32")     //在这判断字段类型,我这只有两种类型int,string,如果类型多就要多写几种判断

                                     {

                    obj.GetType().GetField($"{v.Name}").SetValue(obj, int.Parse(dr[$"{v.Name}"].ToString()));       //第一种方法赋值

                                     }

                else

                                     {

                    ty.GetField(name: $"{v.Name}").SetValue(obj, dr[$"{v.Name}"].ToString());          //第二种方法赋值

                                     }

            }

                            dr.Close();

            conn.Close();

            //var vname = obj.GetType().GetField("mAge");

            //vname.SetValue(obj, 23);

           

                            //赋值完了才转成指定的类型

            return (T)obj;

        }

 

2.添加数据到数据库中

               private bool Insert_Into<T>(T t)

        {

            int nRet = 0;

            string intoSql = null;

            string fileName = null;

            string fileValue = null;

            List<OleDbParameter> parList = new List<OleDbParameter>();

 

            Type ty = t.GetType();

            foreach (System.Reflection.FieldInfo v in ty.GetFields())

            {

                fileName += v.Name + ",";

                fileValue += $"@{v.Name},";

                OleDbParameter par = new OleDbParameter($"@{v.Name}", v.GetValue(t));

                parList.Add(par);

            }

            fileName = fileName.Substring(0, fileName.Length - 1);

            fileValue = fileValue.Substring(0, fileValue.Length - 1);

            intoSql = $"INSERT INTO [{ty.Name}] ({fileName}) VALUES ({fileValue})";

 

 

 

            using (OleDbConnection conn = new OleDbConnection(connString))

            {

                using (OleDbCommand cmd = new OleDbCommand(intoSql, conn))

                {

                    cmd.Parameters.Clear();

                    cmd.Parameters.AddRange(parList.ToArray());

                    conn.Open();

                    nRet = cmd.ExecuteNonQuery();

                    conn.Close();

                }

            }

 

            return nRet == 1;

        }

 

3.修改数据

 

private bool UpdateData<T>(int id,T t)

        {

            int nRet = 0;

            string intoSql = null;

            string fileName = null;

          

            List<OleDbParameter> parList = new List<OleDbParameter>();

 

            Type ty = t.GetType();

            foreach (System.Reflection.FieldInfo v in ty.GetFields())

            {

                fileName += v.Name + $"=@{v.Name},";

               

                OleDbParameter par = new OleDbParameter($"@{v.Name}", v.GetValue(t));

                parList.Add(par);

            }

            fileName = fileName.Substring(0, fileName.Length - 1);

          

            intoSql = $"UPDATE [{ty.Name}] SET {fileName} WHERE ID={id}";

 

 

 

            using (OleDbConnection conn = new OleDbConnection(connString))

            {

                using (OleDbCommand cmd = new OleDbCommand(intoSql, conn))

                {

                    cmd.Parameters.Clear();

                    cmd.Parameters.AddRange(parList.ToArray());

                    conn.Open();

                    nRet = cmd.ExecuteNonQuery();

                    conn.Close();

                }

            }

 

            return nRet == 1;

        }

 

4.删除数据

 

private bool DeleteData<T>(int id)

        {

            int nRet = 0;

            string intoSql = null;

           

       

 

            Type ty = typeof(T);         

            intoSql = $"DELETE FROM [{ty.Name}] WHERE ID={id}";

            using (OleDbConnection conn = new OleDbConnection(connString))

            {

                using (OleDbCommand cmd = new OleDbCommand(intoSql, conn))

                {

                   

                    conn.Open();

                    nRet = cmd.ExecuteNonQuery();

                    conn.Close();

                }

            }

 

            return nRet == 1;

 

        }

 

5.读取所有数据,返回一个所有数据的表

 

private DataTable GetDataTable<T>()

        {

            DataTable dt = new DataTable();

            Type ty = typeof(T);

            string sql = $"SELECT * FROM [{ty.Name}]";

            using (OleDbConnection conn = new OleDbConnection(connString))

            {

                using (OleDbCommand cmd = new OleDbCommand(sql, conn))

                {

                    conn.Open();

                    OleDbDataAdapter da = new OleDbDataAdapter(cmd);

                    da.Fill(dt);           

                }

                conn.Close();

            }

 

            return dt;

        }

上一篇:数据库简单命令的使用


下一篇:lua中 table 重构index/pairs元方法优化table内存占用