I am getting error Failed to convert parameter value from a List1 to a String`.

This line throws an error var reader = ExecuteReader(PrepareSqlWithParameters(sqlString), parameters, CommandType.Text) from ReadAll method.

My guidStr has value ["D5FAF478-CF43-40E1-BE79-BB90147A3194","2E79B23E-D264-4901-A065-7E0B7032A5D8"] and converted in in list to send those guids into my data access layer.

My controller

public ActionResult CheckRecords()
    {
        string guidsStr = HttpContext.Request["GUID"];
        var guids = new List<Guid>();
        foreach (var guid in Regex.Replace(guidsStr, "[\\[\\\"\\]]", "").Split(','))
        {
            Guid newGuid;
            if (Guid.TryParse(guid, out newGuid))
            {
                guids.Add(newGuid);
            }               
        }
        var result = Services.CheckRecords(guids);
        return Json(result, JsonRequestBehavior.AllowGet);


    }

Data Service layer

public List<IdIdentity> CheckRecords(List<Guid> guids)
        {
            

            string sql = "select a.OBSC_OBSID, a.OBSC_OBSID_SCH, a.EXCLUDED_OBSC_OBSID, a.EXCLUDED_OBSC_OBSID_SCH, c.OBSC_DESC, d.OBSC_DESC EXCLUDED_OBSC_DESC from OBSCODE_EXCLUSIONS A"  
                " inner join ORDERFORMITEMS B on a.OBSC_OBSID = b.OBSC_OBSID and a.OBSC_OBSID_sch = b.OBSC_OBSID_sch"  
                " inner join obscode c on a.OBSC_OBSID = c.OBSC_OBSID and a.OBSC_OBSID_sch = c.OBSC_OBSID_sch"  
                " inner join obscode d on a.EXCLUDED_OBSC_OBSID = d.OBSC_OBSID and a.EXCLUDED_OBSC_OBSID_SCH = d.OBSC_OBSID_sch"  
                " WHERE b.ORDERFORMITEM_ID IN(:"   PARAM_ORDERFORMITEM_ID   @") AND a.OBSC_OBSID < a.EXCLUDED_OBSC_OBSID";

            var parameters = new List<DbParameter>
                {
                    CreateParameter(PARAM_ORDERFORMITEM_ID, DbType.String, guids, DBNull.Value),
               
                };

            return ReadAll(sql, parameters, reader =>
                {
                      return new IdSchemeIdentity(
                        reader.ReadByName(T_OBSCODE_EXCLUSIONS.OBSC_OBSID, string.Empty),
                        reader.ReadByName(T_OBSCODE_EXCLUSIONS.OBSC_OBSID_SCH, string.Empty),
                        reader.ReadByName(T_OBSCODE_EXCLUSIONS.EXCLUDED_OBSC_OBSID, string.Empty),
                      
                    );
                }).ToList();
        }

ReadAll method

protected List<T> ReadAll<T>(string sqlString, List<DbParameter> parameters, ReadItemDel<T> readMethod)
        {
            var results = new List<T>();

            using (var reader = ExecuteReader(PrepareSqlWithParameters(sqlString), parameters, CommandType.Text))
                while (reader.Read())
                    results.Add(readMethod(reader));

            return results;
        }

Class

public class T_OBSCODE_EXCLUSIONS
    {
        
        public const string OBSC_OBSID = "OBSC_OBSID";
        public const int OBSC_OBSID_LENGTH = 30;

        public const string OBSC_OBSID_SCH = "OBSC_OBSID_SCH";
        public const int OBSC_OBSID_SCH_LENGTH = 30;

        public const string EXCLUDED_OBSC_OBSID = "EXCLUDED_OBSC_OBSID";
        public const int EXCLUDED_OBSC_OBSID_LENGTH = 100;

        public const string EXCLUDED_OBSC_OBSID_SCH = "EXCLUDED_OBSC_OBSID_SCH";
        public const int EXCLUDED_OBSC_OBSID_SCH_LENGTH = 100;

        public const string OBSC_DESC = "OBSC_DESC";
        public const int OBSC_DESC_LENGTH = 300;

        public const string EXCLUDED_OBSC_DESC = "EXCLUDED_OBSC_DESC";
        public const int EXCLUDED_OBSC_DESC_LENGTH = 300;

    }

CodePudding user response：

Transform the List<Guid> into a JSON array, eg ["<guid>","<guid>",...] then add that as a single varchar parameter and change the query

WHERE b.ORDERFORMITEM_ID IN( select value from openjson(@PARAM_ORDERFORMITEM_ID) ) AND a.OBSC_OBSID < a.EXCLUDED_OBSC_OBSID";

CodePudding user response：

I think your best bet is to use multiple parameters, one for each GUID value, and modify the query to reference them.

var parameters = guids.Select((g,n) =>
        CreateParameter($"{PARAM_ORDERFORMITEM_ID}_{n}", DbType.String, g.ToString(), DBNull.Value)).ToList();

var parameterNames = Enumerable.Range(0, guids.Count).Select(n => $"{PARAM_ORDERFORMITEM_ID}_{n}").ToList();

string sql = "select a.OBSC_OBSID, a.OBSC_OBSID_SCH, a.EXCLUDED_OBSC_OBSID, a.EXCLUDED_OBSC_OBSID_SCH, c.OBSC_DESC, d.OBSC_DESC EXCLUDED_OBSC_DESC from OBSCODE_EXCLUSIONS A"  
    " inner join ORDERFORMITEMS B on a.OBSC_OBSID = b.OBSC_OBSID and a.OBSC_OBSID_sch = b.OBSC_OBSID_sch"  
    " inner join obscode c on a.OBSC_OBSID = c.OBSC_OBSID and a.OBSC_OBSID_sch = c.OBSC_OBSID_sch"  
    " inner join obscode d on a.EXCLUDED_OBSC_OBSID = d.OBSC_OBSID and a.EXCLUDED_OBSC_OBSID_SCH = d.OBSC_OBSID_sch"  
    " WHERE b.ORDERFORMITEM_ID IN(:"   String.Join(",:", parameterNames)   @") AND a.OBSC_OBSID < a.EXCLUDED_OBSC_OBSID";