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

1．查找指定ID的数据

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

               private T FindData(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)

        {

            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(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(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()

        {

            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;

        }

签名:GreenLeaf1976