SQLite反射数据库写入或更新

约 389 字大约 1 分钟
2024-03-16
固定了某个表，以及数据库字段是直接转成驼峰的
如果想写成通用的就要把实体对应数据库或用特性标记列名
   public static int Insert_Sample_Detection_Project<T>(List<T> list, bool IsAdd)
        {
            string tableName = "sample_detection_project";
            Type type = typeof(T);
            PropertyInfo[] propertys = type.GetProperties();
            var IgnoreColumn = new List<string> { nameof(DetectionProjectResult.conditionValueOne), nameof(DetectionProjectResult.conditionValueTwo), nameof(DetectionProjectResult.detectionProjectNumber), nameof(DetectionProjectResult.detectionProjectGroupNumber) };
            if (IsAdd) IgnoreColumn.Add(nameof(DetectionProjectResult.id)); //非更新字段 去掉id
            var dbColumn = propertys.Where(t => !IgnoreColumn.Contains(t.Name)); //表列
            List<string> columns = dbColumn.Select(s => $"`{s.Name}`").ToList();
            columns = ConvertHumpsInArray(columns);

            var sqlary = new List<string>();
            var _SQLiteParameters = new List<SQLiteParameter>();
            for (int i = 0; i < list.Count; i++)
            {
                var currentParameters = (from t in propertys
                                         where !IgnoreColumn.Contains(t.Name)
                                         select new KeyValuePair<string, object>($"@{t.Name}{i}", t.GetValue(list[i]))).Select(s => new SQLiteParameter(s.Key, s.Value));
                _SQLiteParameters.AddRange(currentParameters);

                if (IsAdd) //新增
                {
                    string parameterNames = string.Join(",", currentParameters.Select(p => p.ParameterName));
                    sqlary.Add("(" + parameterNames + ")");
                }
                else
                {
                    StringBuilder usb = new StringBuilder();
                    var updates = from t in propertys
                                  where !IgnoreColumn.Contains(t.Name)
                                  && t.Name != nameof(DetectionProjectResult.id)
                                  select $" `{ConvertHumps(t.Name)}`=@{t.Name}{i}";

                    usb.AppendFormat(" UPDATE `{0}` SET ", tableName);
                    usb.Append(string.Join(",", updates));
                    usb.Append(" WHERE ");
                    usb.AppendFormat(" {0}={1}", nameof(DetectionProjectResult.id), $"@{nameof(DetectionProjectResult.id) + i}");
                    sqlary.Add(usb.ToString());
                }
            }

            StringBuilder sb = new StringBuilder();
            if (IsAdd)//新增
            {
                sb.AppendFormat(" INSERT INTO `{0}` ", tableName);
                sb.Append(" (").Append(string.Join(",", columns)).Append(") ")
                .Append(" VALUES ")
                .Append(string.Join(",", sqlary));
            }
            else
            {
                sb.Append(string.Join("; ", sqlary));
            }

            SQLiteConnection sqlcon = GetConnection();
            try
            {
                sqlcon.Open();
                using (SQLiteCommand cmd = sqlcon.CreateCommand())
                {
                    cmd.CommandText = sb.ToString();
                    cmd.Parameters.Clear();
                    cmd.Parameters.AddRange(_SQLiteParameters.ToArray());

                    int r = cmd.ExecuteNonQuery();
                    Debug.WriteLine("插入：" + r + "条！");
                    return r;
                }
            }
            catch (Exception ex) { }
            finally { if (sqlcon.State == System.Data.ConnectionState.Open) sqlcon.Close(); }
            return -1;
        }

        /// <summary>
        /// 驼峰转下划线，同步数据库的列名
        /// </summary>
        /// <param name="strarr"></param>
        /// <returns></returns>
        public static List<string> ConvertHumpsInArray(List<string> strarr)
        {
            List<string> xiahuaxian = new List<string>();
            for (int i = 0; i < strarr.Count; i++)
            {
                xiahuaxian.Add(ConvertHumps(strarr[i]));
            }
            return xiahuaxian;
        }

        public static string ConvertHumps(string str)
        {
            string pattern = @"(?<!^)([A-Z])";
            string replacement = "_$1";
            string underscoreCase = Regex.Replace(str, pattern, replacement);
            return underscoreCase.ToLower();
        }