Limiting Number of Rows Inserted into a SQL Server Database

views:

answers:

+2 Q:

Limiting Number of Rows Inserted into a SQL Server Database

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.

+2 A:

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

Tim Coker 2010-06-29 13:43:39

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.

Tom Hangler 2010-06-29 14:00:32

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 2010-06-29 14:03:58

@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 Coker 2010-06-29 14:32:05

@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.

Tim Coker 2010-06-29 14:34:50

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.

Tom Hangler 2010-06-29 15:56:44

It still sounds like `BULK INSERT` is your best option. Can you provide specifics why this won't work in your situation? This might give us more insight into your situation and allow us to provide a better solution.

Tim Coker 2010-06-29 17:07:20

ok. I have a mainform class or program that takes in bunch of data from a text file. I have a class that converts and stores that data in a list or class. I wrote a database communication class which connects to an sql database and takes all this data from a file and formats it through my stored procedure and inserts it into my tables in SQL. Defaultly, when i start my mainform program, it automatically opens connection to sql and inserts data. I need a way to allow the user to specify how much of that data he/she wants to be exported to the sql database. The user will specify in my mainform..

Tom Hangler 2010-06-29 17:14:28

...class through a text entry box. When the program that reads in data runs, I need some way of keeping track of how many rows or iterations have been exported to the database. When it reaches the number that the user specified, the database needs to close the connection while my mainform program keeps on running. Sorry for the lack of details. Please ask if you need more details. thanks

Tom Hangler 2010-06-29 17:16:12

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.

Chris Pebble 2010-06-29 13:51:04

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.

Tom Hangler 2010-06-29 14:09:50

`if database interval matches database row amount` can you clarify that, I'm not quiet sure what you're trying to do.

Chris Pebble 2010-06-29 14:48:07

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 .

Tom Hangler 2010-06-29 14:57:41

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.

Chris Pebble 2010-06-29 15:11:41

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

Tom Hangler 2010-06-29 16:44:29

What you need to do is track how many records you've read from the text file and then stop reading the text file after that many records. If, for example, it's a CSV text file, you can count the number of lines you've read and stop after you've reached the limit.

Chris Pebble 2010-06-29 17:55:10

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

Fadrian Sudaman 2010-07-06 00:03:01

ansaurus

tags:

views:

answers:

Limiting Number of Rows Inserted into a SQL Server Database

related questions