Question SqlCommand Progress...

[JaedenRuiner]

I've looked into the manipulation of the SqlBulkCopy() which seems to be effective for some of what I'm wanting to do, but I was curious about two things:

In the initial example of the SqlBulkCopy() Class, it appears that one performs a SELECT statement in a SqlCommand as the Source, and then set a destination table name. Now they use one with matching columns, and I did see and understand the usage of the ColumnMappings collection, but for the usage of transferring data, does the SqlBulkCopy() create? Basically, is the SqlBulkCopy only an INSERT INTO, or can it also do a SELECT INTO?

Also, as much as I can use the SqlBulkCopy() for many things it is limited in others. I like (especially for large operations) to provide feedback to the user. when loading 12000 rows from an excel file via OLEDB Ad Hoc query, it takes a few seconds, and it is nice to let them know I'm processing it. Is there any linkage to the server itself (for I know that SqlCommand doesn't have them) that can allow a "Current Process Progress" or some other such update?

Most DB Servers don't have it (Oracle didn't), but I thought I'd ask just in case as I am new to the VB db programming (and sql server), and it has always been, in all my DB experience, a little frustrating to execute a INSERT statement and pretty much walk away cause you don't know how long it's going to take.

Anyway, thanks for the help.

 

[MattP]

The SqlBulkCopy has both BatchSize and NotifyAfter methods. The notify after method calls the SqlRowsCopied event handler when the number of rows you specify have been copied.

From there you should be able to determine a percentage complete by sending the number of rows copied / BatchSize to your GUI thread.

 

[JaedenRuiner]

um...yea...I said I already understood SqlBulkCopy.

SqlBulkCopy won't take an INSERT INTO statement, or an UPDATE statement, will it? As far as i can see with SqlBulkCopy is that it "is" (sort of) an INSERT statement, but you format it by providing the source selection, ColumnMappings, and dest table name.

I get all of this fine.

I'm looking to see if there is a progress event setup for:

SqlCommand.ExecuteNonQuery()

so whatever processing is occurring within that SqlCommand I can read events. Unless, of course, SqlBulkCopy will take a straight factored SQL statement and process it, regardless of SELECT, SELECT INTO, INSERT INTO, or UPDATE.

Thanks..

 

[cjard]

I think you misunderstand MattP's post. If you set the NotifyAfter property of the sqlBulkCOpy to, say, 500 then after 500 rows have been processed, the sqlBulkCopy raises a SqlRowsCopied event. If you have hooked that event then you can know the progress of the operation. See SqlBulkCopy.NotifyAfter Property (System.Data.SqlClient)

If you want to know the progress of a normal statement such as insert or update, you cant!
Well, to qualify that, INSERT only inserts one row at a time unless youre using a bulked transfer, but in each case when the insert finishes running you know how many rows were inserted. A SELECT INTO is atomic; you start the select running and then it finishes. You dont get a note half way through saying the select has collated half the rows that are needed, because databases dont work like that. Learn how to read a query execution plan and you'll understand what I mean; it's not jsut a case of gathering 1000 rows and after 500 have been gathered, the operation is half over. Most selects are way more complicated than that and as such, cannot be demarked as having progressed by a linear amount at any time. Any operation that uses a select (i.e. everything apart from basic insert) is subject to this flux