CODEWITHSUNDEEP
c#entity-frameworkoracle11g
spiritqueen
spiritqueen

Reputation: 759

ExecuteStoreCommand, multiple insert statement generates Invalid Character error

Is there a way to execute multiple insert statement using ExecuteStoreCommand in EF? I am using a StringBuilder to generate the multiple insert statements. The code basically looks like this

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")));

}

However, an Invalid Character error occurs when the query is executed by EF. I did some research and found out that it was the semicolon that was generating this error. But I can't remove the semicolon since its the 'end of the line' for Oracle. Any suggestions?

Also, I executed the generated string from the StringBuilder directly against the Database and it works for inserting 5 rows.

Upvotes: 2

Views: 1942

Answers (2)

spiritqueen
spiritqueen

Reputation: 759

I found the solution by trial and error.

It turns out, you need to enclose your multiple insert inside a BEGIN...END; statement, to enable the semicolon to be read as end-of-line delimiter.

My code now would read like:

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")));

}

Upvotes: 1

qujck
qujck

Reputation: 14580

Both ObjectContext.ExecuteStoreCommand and the newer DbContext.ExecuteSqlCommand as implied in their name will only execute a single command. I suggest you build up your commands in a List<> of update statements and execute them sequentially when they are all ready ...

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);
}

Upvotes: 0

Related Questions