基于反射特性的简单数据库操作

超、凢脫俗 2021-11-08 23:31 315阅读 0赞

首先上一个SqlHelper

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

public class SqlHelper
        {
            public static string GetSqlConnectionString()
            {
                return ConfigurationManager.ConnectionStrings["State"].ConnectionString;
            }
           
            public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
            {
                using (SqlConnection conn = new SqlConnection(GetSqlConnectionString()))
                {
                    using (SqlCommand comm = conn.CreateCommand())
                    {
                        conn.Open();
                        comm.CommandText = sql;
                        comm.Parameters.AddRange(parameters);
                        return comm.ExecuteNonQuery();
                    }
                }
            }
           
            public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
            {
                using (SqlConnection conn = new SqlConnection(GetSqlConnectionString()))
                {
                    using (SqlCommand comm = conn.CreateCommand())
                    {
                        conn.Open();
                        comm.CommandText = sql;
                        comm.Parameters.AddRange(parameters);
                        return comm.ExecuteScalar();
                    }
                }
            }
            
            public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
            {
                using (SqlDataAdapter adapter = new SqlDataAdapter(sql, GetSqlConnectionString()))
                {
                    DataTable dt = new DataTable();
                    adapter.SelectCommand.Parameters.AddRange(parameters);
                    adapter.Fill(dt);
                    return dt;
                }
            }
    
            public static SqlDataReader ExecuteReader(string sqlText, params SqlParameter[] parameters)
            {
                //SqlDataReader要求，它读取数据的时候有，它独占它的SqlConnection对象，而且SqlConnection必须是Open状态
                SqlConnection conn = new SqlConnection(GetSqlConnectionString());//不要释放连接，因为后面还需要连接打开状态
                SqlCommand cmd = conn.CreateCommand();
                conn.Open();
                cmd.CommandText = sqlText;
                cmd.Parameters.AddRange(parameters);
                //CommandBehavior.CloseConnection当SqlDataReader释放的时候，顺便把SqlConnection对象也释放掉
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }

同时把配置文件的数据库链接配置写好

把BaseModel也添加好

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

public class BaseModel
        {
            public int ID { get; set; }
    
            public static IEnumerable<T> Find<T>() where T : class, new()
            {
                List<T> result = new List<T>();
    
                List<string> list = new List<string>();
                Type type = typeof(T);
    
                PropertyInfo[] propertyInfo = type.GetProperties();
                foreach (var item in propertyInfo)
                {
                    list.Add(item.Name);
                }
                string cloStr = string.Join(",", list);
    
                var conStr = ConfigurationManager.ConnectionStrings["State"].ConnectionString;
                string sql = "select " + cloStr + " from dbo.[" + type.Name + "]";
    
                SqlDataReader read = SqlHelper.ExecuteReader(sql);
    
                while (read.Read())
                {
                    T model = Activator.CreateInstance<T>();
                    for (int i = 0; i < read.FieldCount; i++)
                    {
                        PropertyInfo pi = type.GetProperty(read.GetName(i));
                        pi.SetValue(model, read.GetValue(i));
                    }
    
                    result.Add(model);
                }
    
                return result;
            }
    
            public static bool Insert<T>(T t)
            {
                bool result = false;
    
                Type type = typeof(T);
                
    
                StringBuilder sql = new StringBuilder();
                sql.Append("Insert into dbo.[" + type.Name + "]");
                sql.Append("(" + GetColmons(t) + ")");
                sql.Append(" values(" + GetValues<T>(t) + ")");
                result = SqlHelper.ExecuteNonQuery(sql.ToString()) > 0;
    
                return result;
            }
    
            public static bool Delete<T>(int id)
            {
                bool result = false;
                try
                {
                    Type type = typeof(T);
                    StringBuilder sql = new StringBuilder();
                    sql.Append("Delete dbo.[" + type.Name+"]");
                    sql.Append(" where id=" + id);
                    result = SqlHelper.ExecuteNonQuery(sql.ToString()) > 0;
                }
                catch
                {
    
                }
    
                return result;
            }
    
            public static string GetValues<T>(T t)
            {
                if (t == null)
                {
                    return string.Empty;
                }
    
                return string.Join(",",
                    t.GetType().GetRuntimeProperties().Select(p => string.Format("'{0}'", p.GetValue(t))).ToArray());
    
            }
    
            private static string GetColmons<T>(T t)
            {
                if (t == null)
                {
                    return string.Empty;
                }
    
                return string.Join(",", t.GetType().GetProperties().Select(p => p.Name).ToArray());
            }
        }

下面添加一个Model做测试

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

public class User:BaseModel
        {
            public string Name { get; set; }
    
            public int Age { get; set; }
    
            public long Phone { get; set; }
        }

然后是上层代码

![ContractedBlock.gif][] ![ExpandedBlockStart.gif][]

static void Main(string[] args)
            {
                Console.WriteLine("====Start=====");
                
    
    
                User user = new User();
                
                user.ID = 5;
                user.Name = "邹邹";
                user.Age = 18;
                user.Phone = 123123;
    
                bool _insertUser = BaseModel.Insert<User>(user);
    
                bool _delUser = BaseModel.Delete<User>(5);
    
                var _userList = BaseModel.Find<User>();
                if (_insertUser)
                {
                    Console.WriteLine("插入成功");
                }
                else
                {
                    Console.WriteLine("插入成功");
                }
                if (_delUser)
                {
                    Console.WriteLine("删除成功");
                }
                else
                {
                    Console.WriteLine("删除失败");
                }
    
                foreach (var item in _userList)
                {
                    Console.WriteLine($"ID:{item.ID}  -  名字{item.Name}  -  年龄:{item.Age}  -  手机号:{item.Phone}");
                }
    
                Console.ReadKey();
            }

![1629700-20190721184812933-1512352222.png][]

成功~~

转载于:https://www.cnblogs.com/zousc/p/11222208.html

[ContractedBlock.gif]: https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif
[ExpandedBlockStart.gif]: /images/20211108/7a92c5a9aebf4ce5beef4559e4c9baf8.png
[1629700-20190721184812933-1512352222.png]: /images/20211108/a998480ca52f4cb5890aebe5bdfc10f2.png