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;
using Newtonsoft.Json.Linq;
using System.Data;
using System.Data.SqlClient;

namespace Procurement.Helpers
{
    public class SqlHelper
    {
        /// <summary>
        /// 构建mysql参数数组
        /// </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.Int;
        //            }
        //            else if (dataType == "int")
        //            {
        //                paramModel.SqlDbType = SqlDbTypes.Int;
        //            }
        //            else if (dataType == "bigint")
        //            {
        //                paramModel.SqlDbType = SqlDbTypes.BigInt;
        //            }
        //            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();
        //}

        /// <summary>
        /// 构建sql参数数组
        /// </summary>
        /// <param name="json"></param>
        /// <returns></returns>
        public static SqlParameter[] CreateSqlParameters(JsonElement json)
        {
            Dictionary<string, JsonElement> dict = JsonSerializer.Deserialize<Dictionary<string, JsonElement>>(json);
            List<SqlParameter> listParams = new List<SqlParameter>();

            foreach (KeyValuePair<string, JsonElement> d in dict)
            {
                string paramName = d.Key.ToString();
                object paramValue = null;
                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;
                }
                SqlParameter param = new SqlParameter { ParameterName = "@" + paramName, Value = paramValue};
                listParams.Add(param);
            }
            return listParams.ToArray();
        }

    }
}