SqlBulkCopy - Exception

[JaedenRuiner]

I'm working on shifting some of my SQL command operations into the SqlBulkCopy class, as I can gain real time processing updates along the way, and yet i'm discovering a minor annoyance with the Exception not being all too clear about what is going wrong.

The below code is part of a SqlWrapper class of mine, that operates as a glorified parsing and execution engine, allowing me to format and execute specific sql statements via the SqlCommand() and other classes.

the _bulk member is simply a reference to a setting that tells the engine to either SqlCommand.ExecuteNonQuery() on the whole thing, or to attempt using the SqlBulkCopy()
Note:

• the _ is a prefix for all class member fields
• MakeSelect() is a Shared function that interprets the information and generates a formatted SQL SELECT statement.

      If _bulk AndAlso (Count > 0) Then
         Dim sql As String = ""
         If MakeSelect(sql, _source, _columns, _condition) = SqlParseResult.NoError Then
            Dim rdr As SqlClient.SqlDataReader
            _cmd = New SqlCommand(sql, _db.Connection)
            _bcopy = New SqlBulkCopy(_db.Connection)
            Maxitems = _count
            rdr = _cmd.ExecuteReader()
            _bcopy.NotifyAfter = ((_count \ 1000) + 1) * 50
            _bcopy.DestinationTableName = _dest
            Try
               _bcopy.WriteToServer(rdr)
            Catch ex As Exception
               _error = SqlParseResult.SQLExecException
               _exmsg = ex.Message
               Me.Exception = ex
            End Try
         End If
      Else
         Return (ExecuteSQL() = SqlParseResult.NoError)
      End If

Basically, I grab my source and format it as a SELECT STATEMENT (even though this object could have initially been parsed from an INSERT INTO or a SELECT INTO statement) and then set my SqlBulkCopy Destination table and try to execute the WriteToServer()
(Note: I am listening to the SqlRowsCopied() event)

However, in all of this I am getting an Exception the moment I try to run this command, and the goofy thing is the Exception is:

There is already an open DataReader associated with this Command which must be closed first.

The odd parts, as you can probably guess, is that I haven't opened another DataReader for this Command. Count() property referenced at the beginning is a SqlCommand.ExecuteScalar() on the source table, but then I close out that command (=Nothing). There shouldn't be any "Reader" associated with anything at this juncture of my application.

There is a point to note, however:

The Command I'm parsing in this situation is a "SELECT INTO" which means the DB destination table has not yet been created. That is the only thing i could think of that might cause a problem and I am wondering if this Exception from the SqlBulkCopy() is simply a confused reaction to the missing Destination Table in the database.

Thanks

 

[MattP]

Try

rdr = _cmd.ExecuteReader(CommandBehavior.CloseConnection)

 

[JaedenRuiner]

Try

rdr = _cmd.ExecuteReader(CommandBehavior.CloseConnection)

Hm...

But that would close my current connection, yes? I don't want the connection closed, I just want to use my current connection to load through a SqlBulkCopy().

-edit-
Strike that. It does close the connection after completion of the task...which is not the behavior i'm looking for, and either way, the point is moot since that did not resolve the issue.

the sequence of events as it follows is this:

• Main Form Opens
  • Login Form Opens Modal
  • Returns and MainForm creates connection to DB.
• Admin Form Opens
  (this allows direct access to the DB for control and data manipulation/import etc. Not part of the App's Purpose, but necessary to keep everything encapsulated)
  • If Connection is Open (and it should be) FillDataset(). This basically runs the TA.Fill() on each of the datatables in the Typed Dataset, basically preparing them all for use within the application.
  • Run SQL - I've typed the SQL SelectInto/Insert Into command manually into a TextBox and then told the app to run it.
  • Parser Loads and interprets the sql.
• Progress Form Opens
  • Executes the Sl Command parser which runs my BulkCopy scenario.

at the end there is where the exception occurs. If at that same location, instead of doing the BulkCopy I do either:

• SqlCommand.ExecuteNonQuery()
• TA = New SqlDataAdapter(_db.connection)
  TA.SelectCOmmand = New Command(SelectStatement)
  DT = new DataTable()
  TA.Fill(DT)

I get no errors, so why would the BulkCopy cause a "DateReader Already Open" exception, when the DataAdapter filling a Table from the Same Source does not think one is open?

thanks

 

[MattP]

Both your _cmd and _bcopy are using _db.connection. You'll need to either open up a new connection for the _bcopy or set MultipleActiveResultSets = True.

 

[JaedenRuiner]

Both your _cmd and _bcopy are using _db.connection. You'll need to either open up a new connection for the _bcopy or set MultipleActiveResultSets = True.

As I thought.
Though my own commentary says that is moronic. You NEED the SqlCommand to create the Reader, which NEEDS a connection, and the SqlBulkCopy NEEDS a connection and NEEDS the Reader as a source, but why you need TWO different connections for ONE operation is...well...stupid and poor program design. *sigh* Most of the Connection and MARS stuff makes total sense, but I'm using the reader for the bulkcopy so it *shouldn't* consider them to be different readers on the same connection, they should be considered 1 reader on 1 connection.

I should have stuck with Delphi

Anyway, regardless, after I ExecuteReader() from the SqlCommand isn't there a way to pass that reader to the BulkCopy object without this conflict? (Other than MARS or Two connections?) I mean, where is the "other" "DataReader" since SqlBulkCopy is using the DataReader i pass it, that's 1 reader in my count. Should I close out the SqlCommand after I ExecuteReader()? Can I create the SqlBulkCopy() without a connection so it only uses the Reader connection?

Basically I want:

1 Connection
MARS=False
SqlBulkCopy()
(Allowing for AdHoc OPENROWSET() sources which i already have enabled, but this means they are usually SQL Commands and not DataTables)

I thought using a DataReader would achieve this, but I was wrong, so what method would suffice? Using a DataAdapter and Filling a DataTable() would sort of defeat the purpose:

This is my Source Select:
SELECT [Cust_id], [Cust_name], [BOL_ID], [mat_ref]
from OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\db\mydb.xls', [Sheet1$])

If I use a DataAdapter.Fill(DataTable) method, I haven't gained anything by using the bulkcopy, and I could just do a looping Fill(start, Amount, DataTable) repetitively to get the Progress updates, but that is...superfluous. I *want* XLS -> SqlServer DB.Table() with a SQLBulkCopy on 1 connection. HOw would i write that? (It may not be possible, which would just make my afternoon. *snicker*)

Thanks

 

[JaedenRuiner]

Well
In the Grand scheme of things I ended up making a connection manager for myself, which fits in nicely with all my Handler classes and such.

Basically, you were right that the dual connection usage was causing the exception, so to resolve that I maintain one primary connection to verify that the connection string builder has been populated as well as being myprimary connection for my Typed Dataset. All other connections are achieved by asking the wrapper to "GetConnection".

The idea was not to have to worry about what connections are where and how many etc. (i'm massively ADD so I hate forgetting to do things). The Collection that retains the connection defaultly adds a StateChange() and Disposed() handler. If the Connection.State is Changed to "Closed" it Disposes it and the dispose handler removes it from the collection automatically.
Keeps things neat.

Anyway, it appears that yea, with that in place, surprisingly, other things that weren't exactly causing "errors" are running smoother too. (Maybe i was stressing something with my previous method).

Well, Thanks for the help:
I think I learn this language just in time for the next one to replace it.

 

[cjard]

If you have a reader open on a connection, then that connection cannot be used for anything else until the reader is closed. Ergo, you cannot download and upload to the same database in firehose fashion using only one conenction. From a transactional point of view things would be awfully confusing if reading and writing were simultaneously allowed over one conenction. Ensuring they stay separate really helps the database manage data for multiple users.

One dog, one bone!