none
ExecuteNonQuery limitations RRS feed

  • Question

  • Short explaination, I need to write the results of a query done against a SQLite database as XML in a memo field of an Access database. The problem seems to be that the results from a query can sometimes be thousands of rows which creates one very big XML string and the command is to "complex". How does one stick such a large string into a SQL query to get it into the database.

    Long explaination, we have 3 seperate divisions with their own databases using SQLite, Access, and MS SQL Express. We have a single limited database used for reporting. This reporting database holds a checklist, each check has a row in the checks table. A check is performed by running a SQL query against a division's database, the reporting database doesn't hold the results from the query, it only holds the number of results (failures) for that check. What we would like to do is store the results in the reporting table as XML. Since each check can result in a different report with any number of different fields and data types it seams XML would be the best format. We don't need to query the data, we just need it in a format that can be writting to Excel or displayed on a web page, again xml seems like the best fit. below is the code.

    //creates xTable object from datatable
    //xTable is a custom Excel datatable with formating
    xtable = new Premonition.Excel.xTable(dtResults);
    //ToString serializes object to XML string
    results = xtable.ToString();
    //format results string for SQL command
    results = NHAdb.FormatValueString(results);
    //create command
    NHAdb.Command.CommandText = string.Format("UPDATE Check SET Results = '{0}' WHERE ID = {1}", results, ID);
    //execute command
    NHAdb.Command.ExecuteNonQuery();
    Tuesday, November 17, 2009 8:12 PM

Answers

  • Use a parameterized query instead of concatenation:

    NHAdb.Command.Parameters.Add("@p1", typeof(string), xtable.ToString()));
    NHAdb.Command.Parameters.Add("@p2", typeof(int), id));
    .CommandText = "update ... set Results=@p1 where id = @p2"
    • Marked as answer by Mattastica Friday, November 20, 2009 5:02 PM
    Tuesday, November 17, 2009 9:07 PM
    Moderator

All replies

  • Use a parameterized query instead of concatenation:

    NHAdb.Command.Parameters.Add("@p1", typeof(string), xtable.ToString()));
    NHAdb.Command.Parameters.Add("@p2", typeof(int), id));
    .CommandText = "update ... set Results=@p1 where id = @p2"
    • Marked as answer by Mattastica Friday, November 20, 2009 5:02 PM
    Tuesday, November 17, 2009 9:07 PM
    Moderator
  • Matt,

    That did the trick. It has worked so far with my sample data up to 5.8MB for a single string field so I'm guessing it will work in real world scenarios of up to 10 times that size. 


    Thanks,
    Matt
    Wednesday, November 18, 2009 6:38 PM