DEMOMySql/MicroServices/Procurement/Procurement.Host/Helpers/SqlHelper.cs

using System.Text;
using System.Threading.Tasks;
using System;
using System.Collections.Generic;
using Procurement.ViewModel;
using System.Text.Json;
using Procurement.EntityFrameworkCore.SqlRepositories;
using Procurement.Controllers;
using System.Collections;
using Procurement.Enums;
using Volo.Abp.Domain.Repositories;
using Procurement.Services;

namespace Procurement.Helpers
{
    public class SqlHelper
    {
        /// <summary>
        /// ¹¹½¨sql²ÎÊýÊý×é
        /// </summary>
        /// <param name="json"></param>
        /// <returns></returns>
        public static async Task<SqlParameterViewModel[]> CreateSqlParameters(ISqlRepository sqlRepository,string proc,JsonElement json)
        {
            Dictionary<string, JsonElement> dict = JsonSerializer.Deserialize<Dictionary<string, JsonElement>>(json);

            Common common = new Common(sqlRepository);
            var items = await common.GetCommonItems("", "parameterlist", proc, "");

            List<SqlParameterViewModel> listParams = new List<SqlParameterViewModel>();

            foreach(KeyValuePair<string,object> item in items)
            {
                foreach (var d in (ArrayList)item.Value)
                {
                    Dictionary<string, object> param = (Dictionary<string, object>)d;
                    string paramMode = param["ParamMode"].ToString().ToUpper();
                    string paramName = param["ParamName"].ToString();
                    string dataType = param["DataType"].ToString().ToLower();
                    object paramValue = null;
                    if (dict.ContainsKey(paramName))
                    {
                        JsonElement valueObj = dict[paramName];
                        switch (valueObj.ValueKind)
                        {
                            case JsonValueKind.Null:
                                paramValue = null;
                                break;
                            case JsonValueKind.Number:
                                paramValue = valueObj.GetDouble();
                                break;
                            case JsonValueKind.False:
                                paramValue = false;
                                break;
                            case JsonValueKind.True:
                                paramValue = true;
                                break;
                            case JsonValueKind.Undefined:
                                paramValue = null;
                                break;
                            case JsonValueKind.String:
                                paramValue = valueObj.GetString();
                                break;
                        }
                    }

                    SqlParameterViewModel paramModel = new SqlParameterViewModel { ParameterName = "@" + paramName, Value = paramValue, SqlDbType = SqlDbTypes.VarChar };
                    if (dataType == "tinyint")
                    {
                        paramModel.SqlDbType = SqlDbTypes.Int16;
                    }
                    else if (dataType == "int")
                    {
                        paramModel.SqlDbType = SqlDbTypes.Int32;
                    }
                    else if (dataType == "bigint")
                    {
                        paramModel.SqlDbType = SqlDbTypes.Int64;
                    }
                    else if (dataType == "decimal")
                    {
                        paramModel.SqlDbType = SqlDbTypes.Decimal;
                    }
                    else if (dataType == "date" || dataType == "datetime")
                    {
                        paramModel.SqlDbType = SqlDbTypes.DateTime;
                    }
                    if (paramMode == "OUT")
                    {
                        paramModel.Direction = SqlParamDirection.Output;
                    }
                    listParams.Add(paramModel);
                }
            }
            return listParams.ToArray();
        }

    }
}