2

I have a program in c# in VS that runs a mainform.

That mainform exports data to an SQL Database with stored procedures into tables. The data exported is a lot of data (600,000 + rows).

I have a problem tho. On my mainform I need to have a "database write out interval". This is a number of how many "rows" will be imported into the database.

My problem is however the steps on how to implement that interval. The mainform runs, and when the main program is done, the sql still takes IN data for another 5-10 minutes.

Therefore, if I close the mainform, the rest of the data will not me imported.

Do you professional programmers out there know a way where I can somehow communicate with SQL to only export data for a user-specified interval. T

his has to be done with my c# class.

I dont know where to begin.

I dont think a timer would be a good idea because differenct computers and cpu's perform differently. Any advice would be appreciated.

Improve this question
2
  • 1
    Have you heard about paragraphs? Use them. Commented Jun 29, 2010 at 13:40
  • Can you show the code you use to do the export, or at least describe it? I don't understand why this would be a problem - just don't send more than "n" rows to the database. You could also consider creating a SQL Server job to do the import from a file written b the "mainform". Commented Jul 6, 2010 at 0:28

3 Answers 3

Reset to default
2

If the data is of a fixed format (ie, there are going to be the same columns for every row and its not going to change much), you should look at Bulk Insert. Its incredibly fast at inserting large numbers of rows.

The basics are you write your data out to a text file (ie, csv, but you can specify whatever delimiter you want), then execute a BULK INSERT command against the server. One of the arguments is the path to the file you wrote out. It's a bit of a pain to use because you have to write the file in a folder on the server (or a UNC path that the server has access to) which leads to configuring windows shares or setting up FTP on the server. It sounds like exactly what you want to use, though.

Here's the MSDN documentation on BULK INSERT:
http://msdn.microsoft.com/en-us/library/ms188365.aspx

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

thanks for the response. But my issue is a bit complicated here. The mainform will have other uses for the user while the data is being exported to the database. What I need is: the user puts in "I want 50k rows". I need so that once 50k rows are executed, the database connection from my c# class closes after all the data is exported. Sorry for the confusion.
btw. Is there a stored procedure for bulk insert? For exmple: I have a c# class with my sql connection, I create my paramter for stored procedure. The stored procedure, once called, executes this bulk insert? thanks
@Tom Hangler's first question: I'm a bit confused as to what you mean by "exported". In the initial question, it sounded like your program would generate 600k rows and that would get put in the database. Now it sounds like you're requesting 50k rows from the DB. I think a bit more clarification as to what you're trying to accomplish would help.
@Tim Hangler's 2nd question: The BULK INSERT statement is just a T-SQL command. You can pass the file's path that your program generated to the procedure and the SP can execute the BULK INSERT statement, if that's what you want to do. You just have to make sure that the file is available to the server process.
What I mean by exported is that my mainform program takes in data. Theen it is exported in my specific format to a database. The interval is how much of that data from mainform I want exported to the database.
|
0

Instead of exporting all of your data to SQL and then trying to abort or manage the load a a better process might be to split your load into smaller chunks (10,000 records or so) and check whether the user wants to continue after each load. This gives you a lot more flexibility and control over the load then dumping all 600,000 records to SQL and trying to manage the process.

Also what Tim Coker mentioned is spot on. Even if your stored proc is doing some data manipulation it is a lot faster to load the data via bulk insert and run a query after the load to do any work you have to do then to run all 600,000 records through the stored proc.

Improve this answer

6 Comments

Problem is I want this to be least user-interactive as possible. Therefore I would maybe want something in c# that can keep track of this? Is it possible to have something that says ;check to see if database interval matches database row amount. If it does: close database connection.
if database interval matches database row amount can you clarify that, I'm not quiet sure what you're trying to do.
The database interval is the interval or "database rows" that the user will specify. This is the number of rows of the data coming through to the mainform that he/she wants exported to a database. Once that interval is completed, the data could still be comin in to the mainform but the database connection has to end .
Where is this data coming from? Another database? XML file? It seems that the best course of action might be to take a look at your datasource and see if you can't limit it to the number of rows the user specified.
the data is coming through a text file. Its untouchable and not an option. Its in my c# classes where I have to keep track of this. Thanks for your help
|
0

Like all the other comments before, i will suggest you to use BulkInsert. You will be amazed by how fast the performance is when it comes to large dataset and perhaps your concept about interval is no longer required. Inserting 100k of records may only take seconds.

Depends on how your code is written, ADO.NET has native support for BulkInsert through SqlBulkCopy, see the code below http://www.knowdotnet.com/articles/bulkcopy_intro1.html

If you have been using Linq to db for your code, there are already some clever code written as extension method to the datacontext which transform the linq changeset into a dataset and internally use ADO.NET to achieve the bulk insert

http://blogs.microsoft.co.il/blogs/aviwortzel/archive/2008/05/06/implementing-sqlbulkcopy-in-linq-to-sql.aspx

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.