访问数据库的泛型封装类

川长思鸟来 2023-07-19 04:53 13阅读 0赞

/// <summary>
            /// 新增
            /// </summary>
            /// <typeparam name="T">实体类</typeparam>
            /// <param name="t">对象</param>
            /// <param name="flag">返回主键还是都影响行数  false  受影响行数  true 主键值</param>
            /// <returns></returns>
            public int Insert<T>(T t, bool flag = false) 
            {
                List<PropertyInfo> propertyInfos = new List<PropertyInfo>(typeof(T).GetProperties());
    
                //获取主键
                PropertyInfo pkey = propertyInfos.Where(p => p.GetCustomAttributes(typeof(KeyAttribute), false).Length > 0).FirstOrDefault();
    
                propertyInfos.Remove(pkey);
    
                StringBuilder builder = new StringBuilder();
                builder.Append("insert into ").Append(typeof(T).Name).Append("(");
    
                StringBuilder valueBuilder = new StringBuilder();
                valueBuilder.Append(" values( ");
    
                for (int i = 0; i < propertyInfos.Count; i++)
                {
                    PropertyInfo item = propertyInfos[i];
                    if (item.GetValue(t) != null)
                    {
                        builder.Append(item.Name);
                        valueBuilder.Append("'");
                        valueBuilder.Append(item.GetValue(t));
                        valueBuilder.Append("'");
    
                        if (i < propertyInfos.Count - 1)
                        {
                            builder.Append(",");
                            valueBuilder.Append(",");
                        }
                    }
                }
    
                builder.Append(" ) ");
                valueBuilder.Append(" )");
    
                if (flag)
                    builder.Append(" output inserted.").Append(pkey.Name);
    
                builder.Append(valueBuilder);
    
                using (SqlConnection conn = new SqlConnection(_connstr))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(builder.ToString(), conn))
                    {
                        if (flag)
                            return Convert.ToInt32(cmd.ExecuteScalar());//返回最新的主键
                        else
                            return cmd.ExecuteNonQuery();//返回受影响行数
                    }
                }
    
            }
    
            /// <summary>
            /// 修改
            /// </summary>
            /// <typeparam name="T"></typeparam>
            /// <param name="t"></param>
            /// <returns></returns>
            public int Update<T>(T t)
            {
                List<PropertyInfo> propertyInfos = new List<PropertyInfo>(typeof(T).GetProperties());
    
                //获取主键
                PropertyInfo pkey = propertyInfos.Where(p => p.GetCustomAttributes(typeof(KeyAttribute), false).Length > 0).FirstOrDefault();
    
                propertyInfos.Remove(pkey);
    
                StringBuilder builder = new StringBuilder();
                builder.Append("update ").Append(typeof(T).Name).Append(" set ");
    
                for (int i = 0; i < propertyInfos.Count; i++)
                {
                    PropertyInfo item = propertyInfos[i];
                    if (item.GetValue(t) != null)
                    {
                        builder.Append(item.Name);
                        builder.Append("='");
                        builder.Append(item.GetValue(t));
                        builder.Append("'");
    
                        if (i < propertyInfos.Count - 1)
                            builder.Append(",");
                    }
                }
    
                builder.Append(" where ");
                builder.Append(pkey.Name);
                builder.Append("='");
                builder.Append(pkey.GetValue(t));
                builder.Append("'");
    
                using (SqlConnection conn = new SqlConnection(_connstr))
                {
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(builder.ToString(), conn))
                    {
                        return cmd.ExecuteNonQuery();
                    }
                }
            }