ExecuteStoreCommand，多个insert语句生成无效字符错误

Question

有没有办法在EF中使用ExecuteStoreCommand执行多条insert语句？我使用StringBuilder来生成多个insert语句。代码基本上看起来像这样

StringBuilder _saveReasonQuery = new StringBuilder();
public void ApplyRules()
{
    using(var context = new SpecializedDBEntities())
    {
        foreach(var item in list)
        {
            //Do something
            SaveReason(item, reasonId);
        }
        context.GetObjectContext().ExecuteStoreCommand(_saveReasonQuery.ToString());
        ctx.SaveChanges();
    }
}

//Inside Save Reason
public void SaveReason(...)
{
     _saveReasonQuery.Append("INSERT INTO ORDER_IMPLEM_DTL_REASON ");
     _saveReasonQuery.Append("(ORDER_IMPLEM_REASON_ID, SALES_ORDER_IMPLEM_DTL_ID, REASON_ID, REASON_STAT_ID, SALES_ORDER_BRDCST_ID, CREA_BY, CREA_DT)");
     _saveReasonQuery.Append(" VALUES ");
     _saveReasonQuery.Append(String.Format("('0', '{0}', '{1}', '{2}', '{3}', '{4}', TO_DATE('{5}', 'YY-MM-DD HH:mi'));",
                                orderSpot.SALES_ORDER_IMPLEM_DTL_ID, reasonId, Common.Convert_Int64(orderSpot.CUESHT_STAT_ID),
                                orderSpot.SALES_ORDER_DTL_BRDCST_ID, userContext.User.USER_CD, DateTime.Now.ToUniversalTime().ToString("yy-MM-dd hh:mm")));

}

但是，当EF执行查询时，会出现无效字符错误。我做了一些研究，发现是分号产生了这个错误。但我不能删除分号，因为它是Oracle的“行尾”。有什么建议吗？

另外，我直接对数据库执行从StringBuilder生成的字符串，它可以插入5行。

Answer 1

我通过反复试验找到了解决方案。

结果是，您需要将多个insert包含在一个BEGIN...END；语句中，才能将分号读作行尾分隔符。

我的代码现在应该是这样的：

StringBuilder _saveReasonQuery = new StringBuilder();
public void ApplyRules()
{
    using(var context = new SpecializedDBEntities())
    {
        _saveReasonQuery.Append("BEGIN ");
        foreach(var item in list)
        {
            //Do something
            SaveReason(item, reasonId);
        }
        _saveReasonQuery.Append("END;");
        context.GetObjectContext().ExecuteStoreCommand(_saveReasonQuery.ToString());
        ctx.SaveChanges();
    }
}

//Inside Save Reason
public void SaveReason(...)
{
     _saveReasonQuery.Append("INSERT INTO ORDER_IMPLEM_DTL_REASON ");
     _saveReasonQuery.Append("(ORDER_IMPLEM_REASON_ID, SALES_ORDER_IMPLEM_DTL_ID, REASON_ID, REASON_STAT_ID, SALES_ORDER_BRDCST_ID, CREA_BY, CREA_DT)");
     _saveReasonQuery.Append(" VALUES ");
     _saveReasonQuery.Append(String.Format("('0', '{0}', '{1}', '{2}', '{3}', '{4}', TO_DATE('{5}', 'YY-MM-DD HH:mi'));",
                            orderSpot.SALES_ORDER_IMPLEM_DTL_ID, reasonId, Common.Convert_Int64(orderSpot.CUESHT_STAT_ID),
                            orderSpot.SALES_ORDER_DTL_BRDCST_ID, userContext.User.USER_CD, DateTime.Now.ToUniversalTime().ToString("yy-MM-dd hh:mm")));

}

Answer 2

ObjectContext.ExecuteStoreCommand和名称中所暗示的较新的DbContext.ExecuteSqlCommand都将只执行一条命令。我建议您将命令构建在update语句的List<>中，并在它们都准备就绪时按顺序执行它们……

public void ApplyRules()
{
    var saveQueries = new List<Tuple<string, object[]>>();
    foreach(var item in list)
    {
        //Do something
        saveQueries.Add(SaveQuery(reasonId, ...));
    }

    using(var context = new SpecializedDBEntities())
    {
        foreach(var saveQuery in saveQueries)
        {
            testContext.Database.ExecuteSqlCommand(saveQuery.Item1, saveQuery.Item2);
        }
        ctx.SaveChanges();
    }
}

private Tuple<string, object[]> SaveQuery(int reasonId, ...)
{
    const string query = 
         "INSERT INTO ORDER_IMPLEM_DTL_REASON " +
         "(ORDER_IMPLEM_REASON_ID, SALES_ORDER_IMPLEM_DTL_ID, REASON_ID, REASON_STAT_ID, SALES_ORDER_BRDCST_ID, CREA_BY, CREA_DT)" +
         " VALUES {0}, {0}, {1}, {2}, {3}, {4}, {5}";

    object[] values = new object[]{
        orderSpot.SALES_ORDER_IMPLEM_DTL_ID,
        reasonId,
        Common.Convert_Int64(orderSpot.CUESHT_STAT_ID),
        orderSpot.SALES_ORDER_DTL_BRDCST_ID, 
        userContext.User.USER_CD, 
        DateTime.Now.ToUniversalTime().ToString("yy-MM-dd hh:mm")
    };

    return new Tuple<string, object[]>(query, values);
}