• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1061
  • Last Modified:

Public Declaration of SQLBulkCopy and csvDataReader

LearnedOne

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)
                    End If
                    csvData.Columns.Add("varchar")
                End If
            End If
            If c.HasControls() Then
                ProcessMappings(c)
            End If
        Next
    End Sub

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)
        Else
            Delimeter = Chr(9)
        End If


        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
        End If
        csvData.Settings.Delimiter = Request.QueryString("DataSource")

        objConn.Open()
        bulkCopy.DestinationTableName = "IndividualImportStaging"
        bulkCopy.BulkCopyTimeout = 1000000000

        ProcessMappings(Me)


        '' call WriteToServer which starts import
        bulkCopy.WriteToServer(csvData)
        objConn.Close()

    End Sub

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.
0
davidcahan
Asked:
davidcahan
1 Solution
 
clockwatcherCommented:
Why not just pass it to your subroutine?

Public Sub ProcessMappings(ByVal _c As Control, bulkCopy as SqlBulkCopy, csvData as CsvDataReader)

        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)
                    End If
                    csvData.Columns.Add("varchar")
                End If
            End If
            If c.HasControls() Then
                ProcessMappings(c, bulkCopy, csvData)
            End If
        Next
    End Sub

------------

Protected Sub btnSubmit_Click

....
 ProcessMappings(Me, bulkCopy, csvData)
....

End Sub
0
 
davidcahanAuthor Commented:
doh....sometimes I feel completely stupid.  That was the ticket.  Thanks
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now