插入多个 JSON 字符串时出错

Question

这段代码可以工作，但它只能将一个json字符串插入sql db。如果 json 字符串是多个，它将只插入一个 json 字符串，并出现此错误

usp_insert_schedule指定了太多参数。

有人可以给我教程链接或至少指导我。

using Newtonsoft.Json;
//Object Root
public class jObt
{
    public string empid { get; set; }
    public string status { get; set; }
}
private void insrt_jString()
{
    iScedule isc = new iScedule();
    //sql stored proc
    String Query = iSQL.STORED_PROCEDURE_SCH_ADD;
    //sql class for insert
    iEmp usr = new iEmp(Query);
    string barc =  txtBarCode.Text.Trim(' ');
    string ddate = isc.get_date();
    //http://get w/ barc & ddate as param
    string input = isc.jValue(barc, ddate);
    string input = @"{""empid"":""B14"",""status""}{""empid"":""B15"",""status"":""Morning""}";
    var output = input.Split(new[] { '{', '}' }, StringSplitOptions.RemoveEmptyEntries)
           .Select(x => "{" + x + "}")
           .ToList();
    foreach (var json in output) 
    {
        jObt ob = JsonConvert.DeserializeObject<jObt>(json);
        usr.Insert_Schedule(ob.empid, ob.status);
    }   
}
//Class for Insert_Schedule 
public class iEmp: iCon
{
    public string Query;
    public string ComString { get; protected set; }
    public iEmp(string ComS): base(ComS) 
    {
        this.Query = ComS;
        Comm.Parameters.Clear();
        Comm.CommandType = CommandType.StoredProcedure;
        Comm.CommandText = this.ComString;
        Comm.Connection = Conn;
    }
    //SCHEDULE 
    public void Insert_Schedule(string empid, string status) 
    {
        Comm.Parameters.AddWithValue("@empid", empid);
        Comm.Parameters.AddWithValue("@status", status);
        Comm.ExecuteNonQuery(); 
    }
}

存储过程

CREATE PROC [dbo].[usp_insert_schedule] 
@empid [varchar](32), 
@status [varchar](20)
AS
INSERT INTO Schedule(Ctrl_ID, Status)
     VALUES (@empid, @status)

Answer 1

基于下面的循环

foreach (var json in output) 
{
    jObt ob = JsonConvert.DeserializeObject<jObt>(json);
    usr.Insert_Schedule(ob.empid, ob.status);
}

如果 json 包含多个对象usr.Insert_Schedule则会多次调用该方法。现在让我们来看看下面usr.Insert_Schedule方法的定义

public void Insert_Schedule(string empid, string status) 
{
    Comm.Parameters.AddWithValue("@empid", empid);
    Comm.Parameters.AddWithValue("@status", status);
    Comm.ExecuteNonQuery();
}

第二次调用usr.Insert_Schedule时，您不会清除 Comm 的Parameters属性。相反，您再次添加相同的参数（@empid和@status），这就是您收到usp_insert_schedule has too many arguments specified错误的原因。在分配下一组参数之前，您需要清除Comm的参数，如下所示

public void Insert_Schedule(string empid, string status) 
{
    // clear the parameters
    Comm.Parameters.Clear();
    Comm.Parameters.AddWithValue("@empid", empid);
    Comm.Parameters.AddWithValue("@status", status);
    Comm.ExecuteNonQuery();
}