SqlBulkCopy and Context Connection = T
I have created an assembly which I load into SQL 2005. However, if I set my connection string = context connection = true... I will get an error saying something like this feature could not be used in this context... So I changed my function to insert each row.... Now the issue I have is the transfer takes 4X as long.... Before I made the change I was using the bulkcopy by specifying the actual connection string....but I also had to specify the password in the string...and since I wanted to get way from this specification...I attempted the context route. So...is there any other way of using the bulkcopy feature or something like it using the context connection?
Private
SharedFunction BulkDataTransfer2(ByVal _tblNameAsString,ByRef _dtAs DataTable,ByRef emailLogAsString)AsBooleanDim successAsBoolean =FalseemailLog = emailLog & System.DateTime.Now.ToString &
" - bulk transfer2 - " & _tblName & vbCrLfDim insertStrAsString ="INSERT INTO " & _tblName &"("Dim valuesAsString =") Values("
Dim drowAs DataRow =NothingDim dColAs DataColumn =Nothing'add the column namesForEach dColIn _dt.ColumnsinsertStr = insertStr & dCol.ColumnName.ToString &
", "values = values &
"@" & dCol.ColumnName.ToString &", "Next'remove the last comma & form the final stringinsertStr = insertStr.Substring(0, insertStr.Length - 2)
values = values.Substring(0, values.Length - 2)
insertStr = insertStr & values &
")"Dim connStrAsString ="context connection = true"Dim connAsNew SqlConnection(connStr)Dim cmdAs SqlCommand =NothingUsing connTryconn.Open()
ForEach drowIn _dt.Rowscmd =
New SqlCommand(insertStr, conn)ForEach dColIn _dt.Columnscmd.Parameters.AddWithValue(
"@" & dCol.ColumnName.ToString, drow.Item(dCol.ColumnName.ToString))NextSqlContext.Pipe.ExecuteAndSend(cmd)
Nextsuccess =
TrueCatch exAs ExceptionemailLog = emailLog & System.DateTime.Now.ToString &
" " & ex.ToString & vbCrLfsuccess =
FalseFinallyTryconn.Close()
conn.Dispose()
Catch exAs Exceptionsuccess =
FalseEndTryEndTryEndUsingReturn successEndFunction
Not sure if it will be an appropriate solution for you, but that's how I do it when dealing with mass-insert and when I know exact structure of the data:
1. I create a CLR table-valued function that receives something (in your case it might be an XML-serialized DataTable object, but I would prefer to get rid of DataTables at all).
2. CLR-TVF deserealizes the Datatable and returns its rows having FillRow method extracting DataRow's column data into columns of TVF.
3. Use this CLR TVF to insert data with "INSERT ... FROM CLR_TVF()" statement.
?
Why not use the connection string (i.e., non-context connection), but use integrated authentication? Then you won't
have to hardcode any passwords...
I'm not sure why MS disabled SqlBulkCopy inside CLR routines, but I agree that it would be useful in some cases. If this is
something you'd like to see, why not file an issue at connect.microsoft.com, and
post back the URL to the issue here so that I can vote for
it!
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
I have created an assembly which I load into SQL 2005. However, if I set my
connection string = context connection = true... I will get an error saying
something like this feature could not be used in this context... So I changed
my function to insert each row.... Now the issue I have is the transfer takes
4X as long.... Before I made the change I was using the bulkcopy by specifying
the actual connection string....but I also had to specify the password in the
string...and since I wanted to get way from this specification...I attempted
the context route. So...is there any other way of using the bulkcopy feature
or something like it using the context connection?
size=2>
Private
size=2>Shared size=2>Function BulkDataTransfer2( color=#0000ff size=2>ByVal _tblName color=#0000ff size=2>As size=2>String, size=2>ByRef _dt size=2>As DataTable, size=2>ByRef emailLog size=2>As size=2>String) size=2>As size=2>Boolean
Dim success
As color=#0000ff size=2>Boolean = size=2>False
emailLog = emailLog & System.DateTime.Now.ToString &
color=#800000 size=2>" - bulk transfer2 - " & _tblName & vbCrLf
Dim insertStr
As color=#0000ff size=2>String = size=2>"INSERT INTO " & _tblName & color=#800000 size=2>"("
Dim values color=#0000ff size=2>As size=2>String = ") Values("
Dim drow color=#0000ff size=2>As DataRow = color=#0000ff size=2>Nothing
Dim dCol color=#0000ff size=2>As DataColumn = color=#0000ff size=2>Nothing
'add the column names size=2>
For color=#0000ff size=2>Each dCol size=2>In _dt.Columns
insertStr = insertStr & dCol.ColumnName.ToString &
color=#800000 size=2>", "
values = values &
"@" size=2> & dCol.ColumnName.ToString & size=2>", "
Next
'remove the last comma & form the final string
insertStr = insertStr.Substring(0, insertStr.Length - 2)
values = values.Substring(0, values.Length - 2)
insertStr = insertStr & values &
size=2>")"
Dim connStr
As color=#0000ff size=2>String = size=2>"context connection = true"
Dim conn color=#0000ff size=2>As size=2>New SqlConnection(connStr)
Dim cmd color=#0000ff size=2>As SqlCommand = color=#0000ff size=2>Nothing
Using conn
Try
conn.Open()
For color=#0000ff size=2>Each drow size=2>In _dt.Rows
cmd =
New SqlCommand(insertStr, conn)
For color=#0000ff size=2>Each dCol size=2>In _dt.Columns
cmd.Parameters.AddWithValue(
size=2>"@" & dCol.ColumnName.ToString, drow.Item(dCol.ColumnName.ToString))
Next
SqlContext.Pipe.ExecuteAndSend(cmd)
Next
success =
True
Catch ex color=#0000ff size=2>As Exception
emailLog = emailLog & System.DateTime.Now.ToString &
color=#800000 size=2>" " & ex.ToString & vbCrLf
success =
False
Finally
Try
conn.Close()
conn.Dispose()
Catch ex color=#0000ff size=2>As Exception
success =
False
End color=#0000ff size=2>Try
End color=#0000ff size=2>Try
End color=#0000ff size=2>Using
Return success
End color=#0000ff size=2>Function
