使用Quartz.net + Topshelf完成服务调用（2）主list实体列表及子list实体列表采用Oracle.ManagedDataAccess大数据批量插入问题

凌霄玉阶非所愿

已于 2023-05-07 01:25:27 修改

阅读量223

点赞数

分类专栏： Quartz.Net Oracle C# 文章标签： oracle .net list c#

于 2023-05-07 01:06:50 首次发布

本文链接：https://blog.csdn.net/weixin_42064877/article/details/130537644

版权

C# 同时被 3 个专栏收录

36 篇文章 0 订阅

订阅专栏

Oracle

28 篇文章 1 订阅

订阅专栏

Quartz.Net

2 篇文章 0 订阅

订阅专栏

说明：因项目需要（加急），须调用第三方api接口（接口较多，须建表，设计表结构等等操作）采集数据到中间表，以便后期使用。基于Quartz.net + Topshelf完成，数据会一直增加，故此采用服务的方式。

逻辑代码不难，遇到一些小问题，便做下记录，以下是描述。

一：json分析，得到的数据为json类型，本地依据实体进行反序列化，发现json中存在许多json数组，例如：

{
	"code": "0",
	"message": "success",
	"data": [{
		"org_code": "1",
		"org_name": "1",
		"domain_id": "1",
		...
		...
		...等等
		"diags_diag": [], --json数组
		"ops_op": [],
		"exp": [],
		"icus_icu": [],
		...
		...
		...
		"medi_datetime": "2023-01-07 15:38:38"
	}]
}

以上数据需要在转换后插入到表中。根据以上json信息很显然发现一对多关系，以此来设计表，我们需要一张（人员信息sy_expense表），（sy_expense_diags_diag）表、（sy_expense_ops_op）表、（sy_expense_exp）表（sy_expense_icus_icu）表,设计表时，务必考虑好表与表之间的关系，我们要保证这几张表能根据json中的主键或唯一的字段进行关联。

二：Oracle.ManagedDataAccess使用案例，设计完表和实体类之后，我们开始进行获取转换，通过安装Nuget包管理工具Oracle.ManagedDataAccess进行批量插入，当然也有更好的方法来实现，例如sqlSugar这里不一一介绍，在插入的时候我们要注意对应，人员信息要对应下面的json数组中的信息，以下是使用Oracle.ManagedDataAccess的案例

using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace ConsoleApp6
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectStr = "User ID=lx;Password=lx;Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = orcl)))";
            GExcuteNonQuery(connectStr);
        }



        public static bool GExcuteNonQuery(string connectStr)
        {
            List<string> strList = new List<string>();
            List<string> strList2 = new List<string>();
            List<DateTime> dateList = new List<DateTime>();
            for (int i = 0; i < 20000; i++)
            {
                strList.Add(Guid.NewGuid().ToString("N"));
                strList2.Add("1");
                dateList.Add(DateTime.Now);
            }

            Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(connectStr);
            Oracle.ManagedDataAccess.Client.OracleCommand command = new Oracle.ManagedDataAccess.Client.OracleCommand();
            command.Connection = conn;
            command.ArrayBindCount = 20000;
            command.CommandText = @"insert into TEST(ID,NAME) values(:ID,:NAME) ";
            conn.Open();
            OracleParameter fid = new OracleParameter("ID", OracleDbType.Char);
            fid.Direction = ParameterDirection.Input;
            fid.Value = strList.ToArray();
            command.Parameters.Add(fid);

            OracleParameter fhspid = new OracleParameter("NAME", OracleDbType.Char);
            fhspid.Direction = ParameterDirection.Input;
            fhspid.Value = strList2.ToArray();
            command.Parameters.Add(fhspid);
            //这个调用将把参数数组传进SQL,同时写入数据库
            command.ExecuteNonQuery();
            return true;
        }
    }
}

三：简单封装方法：

（1）封装插入的语句

     /// <summary>
        /// DataTableToInsertSql
        /// </summary>
        /// <param name="tableName">要插入的表名</param>
        /// <returns></returns>
        public static string DataTableToInsertSql(string tableName)
        {
            var insertSql = new StringBuilder();
            string colsFiled = string.Empty,
                colsValue = string.Empty;
            try
            {
                DataTable dataTable = DbHelperOleDb.Query(string.Format(@"SELECT  a.* from {0} a where 0=1 ", tableName)).Tables[0];
                colsFiled = string.Join(",", dataTable.Columns.Cast<DataColumn>().Select(x => "" + x.ColumnName + "").ToArray());
                colsValue = string.Join(",", dataTable.Columns.Cast<DataColumn>().Select(x => ":" + x.ColumnName + "").ToArray());
                insertSql.AppendFormat("insert into {0} ({1}) values ({2}) ", tableName, colsFiled, colsValue);
            }
            catch (Exception ex) { }
            return insertSql.ToString();
        }

（2）日期转换，看情况使用，某些需要可以采用链式以及扩展方法

        /// <summary>
        /// 日期转换
        /// </summary>
        /// <param name="strDateTime"></param>
        /// <returns></returns>
        public static DateTime? CoverTDateTime(string strDateTime)
        {
            DateTime date;
            if (DateTime.TryParse(strDateTime, out date))
            {
                return date;
            }
            else
            {
                return null;
            }
        }

（3)读取json配置文件：

        public static ValueTuple<Dictionary<string, string>> HttpUrlValue()
        {
            Dictionary<string, string> keyValues = new Dictionary<string, string>();
            JObject jobj = new JObject();
            string assemblyPath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
            string projectDirectory = Path.GetDirectoryName(Path.GetDirectoryName(assemblyPath));
            string filePath = Path.Combine(projectDirectory, "JSON", "InterfaceAddress.json");
            using (StreamReader file = File.OpenText(filePath))
            {
                using (JsonTextReader reader = new JsonTextReader(file))
                {
                    jobj = (JObject)JToken.ReadFrom(reader);
                    foreach (JProperty property in jobj.Properties())
                    {
                        keyValues.Add(property.Name, property.Value.ToString());
                    }
                }
            }
            return new ValueTuple<Dictionary<string, string>>(keyValues);
        }
        #endregion

（4）反射查找主实体中子List

       /// <summary>
        /// 反射主实体中子List<T>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="data"></param>
        public static List<string> IterationModel<T>(T data)
        {
            List<string> propList = new List<string>();
            Type dataType = typeof(T);
            PropertyInfo[] properties = dataType.GetProperties();
            List<PropertyInfo> listProperties = new List<PropertyInfo>();
            foreach (PropertyInfo property in properties)
            {
                Type propertyType = property.PropertyType;
                if (propertyType.IsGenericType && propertyType.GetGenericTypeDefinition() == typeof(List<>))
                {
                    // 获取子实体的名称
                    Type entityType = propertyType.GetGenericArguments()[0];
                    string entityName = entityType.Name;
                    propList.Add(entityName);
                }
            }
            return propList;
        }

四：重点实现：大数据批量插入 ,通过反射查找主list实体中子list实体中泛型属性及非泛型属性并插入数据。以下是总结及代码：

1、不够细心 😅★★★★★

2、测试时，例如: 发现子list数据集总条数为27条，结果插入只有六条。原因是调用时，插入的总数传进去的是主实体的总条数，应该按照主list实体列表中某一个子list实体列表的所有总条数。★★★★

3、插入子数据集把数据类型给插入到表里了，这™的就尴尬😆 😅 😂 🤣，排查了许多遍，没发现问题，结果又根据GExcuteNonQuery方法去更改GExcuteNonQuery3，（方法命名需注意：尽量严格按照代码规范来），然后就改好了，可能是代码逻辑顺序问题★★★★

4、有问题一点点排查（一般都是细节、细节、细节），不要嫌麻烦，可以自己写个控制台、单元测试等，第一次遇到的问题，印象深刻，下次就记住了。★★★★

using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
using System.Collections.Generic;
using System.ComponentModel;
using System.Reflection;
using MangeOracle = Oracle.ManagedDataAccess.Client;
using System.Linq;
using System.Text;
--以上为引用


        /// <summary>
        /// 大数据批量插入主list实体
        /// </summary>
        /// <typeparam name="T">实体集合类型</typeparam>
        /// <param name="tableName">要插入数据的表名</param>
        /// <param name="count">要插入的总条数</param>
        /// <param name="entityList">插入的实体集合</param>
        /// <param name="msg">返回错误信息</param>
        /// <returns>是否插入成功</returns>
        public static bool GExcuteNonQuery<T>(string tableName, int count, List<T> entityList, string insertSql, ref string msg)
        {
            //connectionString为数据库链接字符串
            Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(connectionString.Replace("Provider=OraOLEDB.Oracle.1;", ""));
            Oracle.ManagedDataAccess.Client.OracleCommand command = new Oracle.ManagedDataAccess.Client.OracleCommand();
            bool isSuccessful = false;
            try
            {
                command.Connection = conn;
                command.ArrayBindCount = count;
                command.CommandText = insertSql;
                conn.Open();
                PropertyInfo[] properties = typeof(T).GetProperties();
                foreach (PropertyInfo property in properties)
                {
                    MangeOracle.OracleParameter Paramer = null;
                    if (Nullable.GetUnderlyingType(property.PropertyType) == typeof(DateTime))
                    {
                        Paramer = new MangeOracle.OracleParameter(property.Name, MangeOracle.OracleDbType.Date);
                    }
                    else if (property.PropertyType == typeof(decimal))
                    {
                        Paramer = new MangeOracle.OracleParameter(property.Name, MangeOracle.OracleDbType.Decimal);
                    }
                    else
                    {
                        Paramer = new MangeOracle.OracleParameter(property.Name, MangeOracle.OracleDbType.Varchar2);
                    }
                    Paramer.Direction = ParameterDirection.Input;
                    #region 赋值
                    if (Nullable.GetUnderlyingType(property.PropertyType) == typeof(DateTime))
                    {
                        Paramer.Value = entityList.Select(c => (DateTime?)property.GetValue(c, null)).ToArray();
                    }
                    else
                    {
                        Paramer.Value = entityList.Select(c => property.GetValue(c, null)).ToArray();
                    }
                    #endregion
                    command.Parameters.Add(Paramer);
                }
                //这个调用将把参数数组传进SQL,同时写入数据库
                isSuccessful = command.ExecuteNonQuery() > 0 ? true : false;
            }
            catch (Exception ex)
            {
                msg = ex.ToString();
                return false;
            }
            return isSuccessful;
        }




        /// <summary>
        /// 大数据批量插入(主list实体中子list实体转换插入)
        /// </summary>
        /// <typeparam name="T">主list实体</typeparam>
        /// <typeparam name="T2">子list实体</typeparam>
        /// <param name="count">插入的行数</param>
        /// <param name="entityList">主list实体集合</param>
        /// <param name="insertSql">插入语句insert into(id,name) table values(:id,:name)</param>
        /// <param name="inpatient_no">主键</param>
        /// <param name="msg">返回错误</param>
        /// <returns></returns>
        public static bool GExcuteNonQuery3<T, T2>(int count, List<T> entityList, string insertSql,string inpatient_no, ref string msg)
        {
            Oracle.ManagedDataAccess.Client.OracleConnection conn = new Oracle.ManagedDataAccess.Client.OracleConnection(connectionString.Replace("Provider=OraOLEDB.Oracle.1;", ""));
            Oracle.ManagedDataAccess.Client.OracleCommand command = new Oracle.ManagedDataAccess.Client.OracleCommand();
            bool isSuccessful = false;
            try
            {
                command.Connection = conn;
                command.ArrayBindCount = count;
                command.CommandText = insertSql;
                conn.Open();
                PropertyInfo[] properties = typeof(T).GetProperties();
                foreach (PropertyInfo property in properties)
                {
                    MangeOracle.OracleParameter Paramer = null;
                    if (property.PropertyType.IsGenericType && property.PropertyType.GetGenericTypeDefinition() == typeof(List<>))
                    {
                        #region 处理主list实体中子list实体中某个属性是泛型属性
                        var subProperties = typeof(T2).GetProperties();
                        foreach (var subProperty in subProperties)
                        {
                            var fieldValues = ((IEnumerable<T>)entityList).SelectMany(c => ((IEnumerable<T2>)property.GetValue(c, null)).Select(subC => subProperty.GetValue(subC, null))).ToArray();
                            if (Nullable.GetUnderlyingType(subProperty.PropertyType) == typeof(DateTime))
                            {
                                Paramer = new MangeOracle.OracleParameter(subProperty.Name, MangeOracle.OracleDbType.Date);
                                Paramer.Direction = ParameterDirection.Input;
                                Paramer.Value = fieldValues;
                            }
                            else
                            {
                                if (subProperty.PropertyType == typeof(decimal))
                                {
                                    Paramer = new MangeOracle.OracleParameter(subProperty.Name, MangeOracle.OracleDbType.Decimal);
                                }
                                else
                                {
                                    Paramer = new MangeOracle.OracleParameter(subProperty.Name, MangeOracle.OracleDbType.Varchar2);
                                }
                                Paramer.Direction = ParameterDirection.Input;
                                Paramer.Value = fieldValues.ToArray();
                            }
                            command.Parameters.Add(Paramer);
                        }
                        #endregion
                    }
                    else
                    {
                        #region else中处理主list实体中子list实体中某个属性不是泛型属性
                        object id = "";
                        List<string> portoList = new List<string>();
                        foreach (var entity in entityList)
                        {
                            id = entity.GetType().GetProperty(inpatient_no).GetValue(entity, null) ?? "";
                            List<T2> subEntities = entity.GetType().GetProperty(typeof(T2).Name).GetValue(entity, null) as List<T2>;
                            for (int i = 0; i < subEntities.Count; i++)
                            {
                                portoList.Add(id.ToString());
                            }
                        }
                        Paramer = new MangeOracle.OracleParameter(inpatient_no, MangeOracle.OracleDbType.Varchar2);
                        Paramer.Direction = ParameterDirection.Input;
                        Paramer.Value = portoList.ToArray();
                        command.Parameters.Add(Paramer);
                        #endregion
                    }
                }
                //这个调用将把参数数组传进SQL,同时写入数据库
                isSuccessful = command.ExecuteNonQuery() > 0 ? true : false;
            }
            catch (Exception ex)
            {
                msg = ex.ToString();
                return false;
            }
            return isSuccessful;
        }

五：调用说明：List<SY_EXPENSE_All>为主list列表，diags_diag为子实体**

调用：

               JObject objss = (JObject)JsonConvert.DeserializeObject(JsonResult);

                if (objss["code"].ToString() == "0")
                {
                    _logger.InfoFormat("状态为：{0}", objss["code"].ToString());
                    //List<SY_EXPENSE> expense = JsonConvert.DeserializeObject<List<SY_EXPENSE>>(objss["data"].ToString());
                    //string sql1 = DbHelperOleDb.DataTableToInsertSql("SY_EXPENSE");
                    //boolRiate = DbHelperOleDb.GExcuteNonQuery("SY_EXPENSE", expense.Count, expense, sql1, ref message);


                    List<SY_EXPENSE_All> diags_diag = JsonConvert.DeserializeObject<List<SY_EXPENSE_All>>(objss["data"].ToString());
                    string sql = DbHelperOleDb.DataTableToInsertSql("SY_EXPENSE_DIAGS_DIAG");
                    int totalCount = diags_diag.Sum(diag => diag.diags_diag.Count);
                    boolRiate = DbHelperOleDb.GExcuteNonQuery3<SY_EXPENSE_All, diags_diag>(totalCount, diags_diag, sql, "INPATIENT_NO", ref message);
                }

实体如下：


    public class SY_EXPENSE_All
    {
        /// <summary>
        /// 
        /// </summary>		
        private string _inpatient_no;
        public string INPATIENT_NO
        {
            get { return _inpatient_no; }
            set { _inpatient_no = value; }
        }
        /// <summary>
        /// 
        /// </summary>		
        private string _mr_no;
        public string MR_NO
        {
            get { return _mr_no; }
            set { _mr_no = value; }
        }
        /// <summary>
        /// 
        /// </summary>		
        private string _admission_times;
        public string ADMISSION_TIMES
        {
            get { return _admission_times; }
            set { _admission_times = value; }
        }

        public List<diags_diag> diags_diag { get; set; }
    }


 public class diags_diag
    {
        /// <summary>
        /// 
        /// </summary>		
        private string _pis_no;
        public string PIS_NO
        {
            get { return _pis_no; }
            set { _pis_no = value; }
        }
        ...
        ...
        ...
   }

凌霄玉阶非所愿

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
使用Quartz.net + Topshelf完成服务调用（2）主list实体列表及子list实体列表采用Oracle.ManagedDataAccess大数据批量插入问题

使用Quartz.net + Topshelf完成服务调用（2）主list实体列表及子list实体列表采用Oracle.ManagedDataAccess大数据批量插入问题
复制链接

扫一扫