Retrieving data from SQL server in batches

Question

I have a Silverlight web application.

I am inserting records into a table (SQL Database), loaded from a csv file. I tried loading +- 15 000 records and it throws me out with the following error: The remote server returned an error: NotFound.

I gather it is because it is just too much data to insert at once because when I split it into 'batches', say 100 at a time, it inserted into the table no probs. Even 500 at a time was too much.

What I do after I insert it into the table, is read from that same table the data and put it into a datagrid. This is so the user can see that it was inserted successfully and also monitor as the records that were inserted are processed.

Now obviously I am getting the same error when attempting to load the 15000 +- records back to a datagrid.

My question is how can I read the records in the table also in batches?

Hope somebody can help.

Many thanks,

Neill

EDIT

To test I made a change to the OperationContract:

Originally

[OperationContract]
public List<send_box> GetSendingItems()
{
    return (from a in smsData.send_boxes
            orderby a.sb_log descending
            select a).ToList();
}

Changed To

[OperationContract]
public List<send_box> GetSendingItems()
{
    List<send_box> sendBoxList = (from a in smsData.send_boxes
            orderby a.sb_log descending
            select a).ToList();

    return sendBoxList;
}

The result are returned from the database, but when I try to return it to the application: --> return sendBoxList

Then it throws out the "The remote server returned an error: NotFound." error. Hope this extra info will assist

Regards

Neill

score 4 · Answer 1 · edited May 23 '17 at 12:33

4

I'm not sure if this is your exact problem based on the limited error message you posted (stack trace would help to diagnose this better), but I am assuming that you are using a WCF service to pass your data to the database and back and the service has a maximum message size set which you would need to increase to allow for larger amounts of data to be passed. Here's an article that talks about this wcf-how-to-increase-message-size-quota

edited May 23 '17 at 12:33

Community

1
1

answered Aug 29 '11 at 10:15

Dmitry Samuylov

1,554
2
14
37

Yes am using a WCF service, but already tried increasing message size quota to no avail. No too sure about one thing though. I added the basicHttpBinding in Web.config, but there was already a customBinding. Should that custom binding stay in as well? – Neill Aug 29 '11 at 10:32

score 2 · Answer 2 · answered Aug 29 '11 at 10:15

2

This looks like a job to perform with a bulk insert mechanism like SqlBulkCopy (work flow: transfer the csv file to the server, build up a data table or something and insert in one go). Using batches is a good idea anyhow, e.g. 1000 items at a time should not be a problem.

Side remark: why on earth would you want to display 15000 records in a grid? There is no point... E.g. would it not be better to e.g. show the last 10 inserted records, together with a total count?

answered Aug 29 '11 at 10:15

jeroenh

26,362
10
73
104

The user needs to see all the records as they are being processed by triggers in the database. I have set it to insert 100 at a time, that is working fine, but how do i read 100, return those 100, read the next 100, return those etc etc – Neill Aug 29 '11 at 10:33
@Neill - As per me you need to read first in the Client side means .net side and display in the datagrid that you get in Dataset(XML) then u r You r able to process that from dataset easly. i have face this prob and i get this type of solution so just try this. This thing will reduce your db call. Check this link this my own blow try it - http://sqlmca.wordpress.com/2009/07/29/how-to-get-data-from-dataset-into-sqlserver-table-by-using-openxml-method/ – KuldipMCA Aug 29 '11 at 11:25

score 2 · Accepted Answer · answered Aug 29 '11 at 10:42

2

I am not sure what is your exact business requirement. But going by one of your response, you said user wnats to see all the records. I am not sure what the user would be able to do if all the 15,000 records fail. May be it would be a good idea to show a kind of summary saying out of lets say 15000, 10000 passed and 5000 failed. And provide drill down or navigation link to explore the success or failure records.

Secondly, even though user wants to see all records, I don't think its user friendly to display more than 100 records in a grid. 100 is also extreame for me. You can implement paging functionality to limit the number of records displayed in the grid.

answered Aug 29 '11 at 10:42

Nilesh Gule

1,511
12
13

If I understand it correctly, then a paging functionality would mean displaying a certain amount of records, and if the user scrolls down, fetching the next "number of records" and displaying those records. if I understand this correctly then this would be absolutely fine. Could you point me in the right direction as to how to implement this kind of solution. Thanks – Neill Aug 29 '11 at 10:54
look here:http://stackoverflow.com/questions/4578781/customized-paging-with-repeater-and-sql – Dalex Aug 29 '11 at 13:36
You can look at this article which offers in depth knowledge about paging sorting etc in Silverlight data grid http://www.codeproject.com/Articles/83906/Silverlight-4-Datagrid-Sorting-Grouping-Filtering – Nilesh Gule Aug 29 '11 at 13:53

Retrieving data from SQL server in batches

3 Answers3