Geek Answers Handbook

How to move 10,000 rows of data from a stored procedure to succeed using C # winform

The situation is as follows:

  • UserX wants information about PlayerY.
  • I am currently getting detailed information about PlayerY and running two sql scripts that return two datasets - each of which is approximately 10,000 records.
  • Then I manually copy and paste these sets into an Excel workbook and send an email to UserX

Current planned approach:

  • I will create a simple WinForm or WPF for UserX that will accept PlayerY details.
  • I will create two stored procedures that will take input from the form.

How does data come from the database to the UserX client?

I ask because the datasets are quite large and there may be unforeseen problems with some approaches due to size. If the stored processes first transferred the data to the actual server tables, then take the second step and move the data from the server tables to Excel? Or do I just need the stored procedures to return datasets, since it will be easy to move them directly to XL without any intermediate server tables?

+4
source share
2 answers

If you plan on getting a thr 'c # entry,

Instead of retrieving 10,000 or more records one at a time, select a specific number. e.g. 1000, 10 times. This will speed up the process and reduce the load on the memory.

You do not need to create a staging server table to store the dataset.

Using DocumentFormat.OpenXml.Spreadsheet in C #, you can write this dataset to Xlsx format or create an xml file using System.xml or use a third-party tool / create your own excel xml to create the xls format.

+2
source

I would suggest that you create a CSV file from the results returned by your stored procedure at runtime using C #, the .csv file can be opened in Excel, so you still have an advantage as an application at the end

Also see this link, so I did not recommend you use Automation of Office

Paragraph taken from http://support.microsoft.com/kb/257757 Microsoft does not currently recommend or support Automation of Microsoft Office applications from any automated, non-interactive client application or component (including ASP, ASP.NET, DCOM and NT Services), because Office may exhibit erratic behavior and / or deadlock when Office is running in this environment.

+1
source

More articles:


All Articles