Public Declaration of SQLBulkCopy and csvDataReader
Posted on 2007-08-07
I have an interesting situation. I have a sub routine that loops through all the dropdownlists on my page and then adds properties to both my sqlbulkcopy object and my csvDataReader object. Or at least that is what i would like it to do. Here's some of my code:
Public Sub ProcessMappings(ByVal _c As Control)
For Each c In _c.Controls
If (TypeOf c Is DropDownList) Then
ddl = CType(c, DropDownList)
If InStr(ddl.ID, "ddlMap") > "0" Then
If ddl.SelectedValue <> "" Then
bulkCopy.ColumnMappings.Add(Mid(ddl.ID, InStr(ddl.ID, "p") + 1, Len(Len(ddl.ID) - InStr(ddl.ID, "p"))), ddl.SelectedValue)
If c.HasControls() Then
On submit i want to do the following:
Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
Dim Delimeter As String
If (Request.QueryString("DataSource") = "CSV") Then
Delimeter = ","
ElseIf (Request.QueryString("DataSource") = "TAB") Then
Delimeter = Chr(9)
Delimeter = Chr(9)
Dim filepath As String = Server.MapPath(".") & "\LeadImports\" & Request.QueryString("FileName")
Dim objConn As SqlConnection = New SqlConnection(System.Configuration.ConfigurationManager.AppSettings("strConn1"))
Dim csvData As New CsvDataReader(filepath, Delimeter, Encoding.Default)
Dim bulkCopy As New SqlBulkCopy(objConn)
If Request.QueryString("HasHeaders") = "True" Then
csvData.Settings.HasHeaders = True
csvData.Settings.Delimiter = Request.QueryString("DataSource")
bulkCopy.DestinationTableName = "IndividualImportStaging"
bulkCopy.BulkCopyTimeout = 1000000000
'' call WriteToServer which starts import
The problem is when i try to declare my sqlbulkcopy and csvDataReader instances as public variables at the top of the page: 1) I can't access the connection property for either, 2) they seem to choke on the few properties i am able to access.
Any solutions? I know this isn't hard I'm just stuck for exactly how .NET wants this.