Bulk insert update

this is my vb code for the exe i have created

Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports System.Data.OleDb
Public Class Form1
    Public conStr As String = ""
    Public connString As String
    Public strNewPath As String
    'Public Extension As String
    Public ackdatemonth As String
    Public ackdateday As String
    Public FilePath, ticketno, device, ackdate, port, vehicle_no, transporter_no, vehicle_id, finalmonth, zone, finalzone, location_code As String
    Dim sqlcon As SqlConnection
    Dim sqlqry, sql, insertqry, updateqry, zoneqry As String
    Dim dsObj, dsobj1, dsobj2 As DataSet
    Dim daObj As SqlDataAdapter
    Dim dt As DataTable
    Dim dr As SqlDataReader
    Dim cmd As SqlCommand
    Dim count As Integer
    Dim files() As String


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Timer1.Start()
    End Sub

    Private Shared Function InlineAssignHelper(Of T)(ByRef target As T, ByVal value As T) As T
        target = value
        Return value
    End Function
    Private Shared Function getMonth(ByVal monthabb As String)
        Try
            Select Case monthabb
                Case "01"
                    monthabb = "Jan"
                Case "02"
                    monthabb = "Feb"
                Case "03"
                    monthabb = "Mar"
                Case "04"
                    monthabb = "Apr"
                Case "05"
                    monthabb = "May"
                Case "06"
                    monthabb = "June"
                Case "07"
                    monthabb = "July"
                Case "08"
                    monthabb = "Aug"
                Case "09"
                    monthabb = "Sept"
                Case "10"
                    monthabb = "Oct"
                Case "11"
                    monthabb = "Nov"
                Case "12"
                    monthabb = "Dec"

            End Select

            Return monthabb
        Catch ex As Exception
            WriteLine(ex.Message)

        End Try
    End Function


    Private Sub Timer1_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Timer1.Tick
        conStr = "....."
        sqlcon = New SqlConnection(conStr)

       'get all the files in the array      
        files = Directory.GetFiles("pathe where file is located, "*.csv")
        For i = 211 To 250
            FilePath = files(i)
            Dim fs As New FileStream(FilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.None)
            Dim sr As New StreamReader(fs)
            Dim read As String = Nothing

            sr.ReadLine()
             While (InlineAssignHelper(read, sr.ReadLine())) IsNot Nothing

                Try

                    'getting the records by each line and splittng to get the fields
                    If IsDBNull(read) = False And read.ToString().Trim() <> "" Then
                        ticketno = ""
                        ticketno = read.ToString().Trim()
                        Dim ary() As String
                        Dim ackdateary() As String
                        ary = ticketno.Split(",")
                        If ary.Length = 6 Then
                            device = ary(0)
                            ackdate = ary(4)
                            ackdateary = ackdate.Split("-")
                            ackdatemonth = ackdateary(1)
                            ackdateday = ackdateary(2).Substring(0, 2)
                            port = ary(5)

                            'depending upon the above fields need to get more fields                             'from database

                            sqlqry = "select fd.vchvehicle_no,ds.numtransporter_no,ds.intvehicle_id,fm_latestrec$.zone,fd1.location_code from devicesettings$ ds INNER JOIN fmsvehicle_det$ fd on ds.intvehicle_id = fd.intvehicle_id inner join fm_latestrec$ on fm_latestrec$.intvehicle_id=ds.intvehicle_id inner join fm_location_det fd1 on ds.numtransporter_no = fd1.location_id WHERE ds.DeviceNumber = '" & device & "'"
                          
                            daObj = New SqlDataAdapter(sqlqry, sqlcon)

                           
                            dt = New DataTable
                            daObj.Fill(dt)

                            If dt.Rows.Count > 0 Then

                                vehicle_no = dt.Rows(0).Item("vchvehicle_no").ToString()

                                transporter_no = dt.Rows(0).Item("numtransporter_no").ToString()

                                vehicle_id = dt.Rows(0).Item("intvehicle_id").ToString()

                                zone = dt.Rows(0).Item("zone").ToString()

                                location_code = dt.Rows(0).Item("location_code").ToString()

                           End If
                               
                            'need to change the monthe to abbreviated form                          
                            finalmonth = getMonth(ackdatemonth)
                               
                            'need to get the required filed to check for duplication
                            sql = "Select * from count_analysisnews where vehicle_no = '" & vehicle_no & "' and dtmonth = '" & finalmonth & "'"

                            cmd = New SqlCommand(sql, sqlcon)
                            If sqlcon.State = ConnectionState.Closed Then sqlcon.Open()
                            dr = cmd.ExecuteReader()



                            If dr.Read() Then
                                insertqry = "update lcount_analysisnews set [" & ackdateday & "] = 'Y' where vehicle_no = '" & vehicle_no & "' and dtmonth = '" & finalmonth & "'"

                            Else
                                insertqry = "Insert into count_analysisnews (vehicle_no, dtmonth, location_id, intvehicle_id,[" & ackdateday & "]) values ('" & vehicle_no & "','" & finalmonth & "','" & transporter_no & "', '" & vehicle_id & "','Y') "
                            End If
                            dr.Close()
                            cmd = New SqlCommand(insertqry, sqlcon)


                            cmd.ExecuteNonQuery()

                            If sqlcon.State = ConnectionState.Open Then sqlcon.Close()





                        End If



                    End If
                Catch ex As Exception
                    Timer1.Start()
                End Try

            End While
            sr.Close()
            fs.Close()
        Next i

     
        Me.Dispose()
    End Sub
End Class

hi vishal...
 .net provides class SQLBULKCOPY in System.Data.SqlClient namespace...
for more details and example how to done it...just check below link.

http://www.sqlteam.com/article/use-sqlbulkcopy-to-quickly-load-data-from-your-client-to-sql-server

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

If this reply hepls your post…then "MARK AS ANSWER"!

Hi Vishal,

There is a class called SqlBulkCopy in .Net Framework which is been used insert the bulk data into sql.You can try with that class.It will take less time rather than its taking now.

H@ppy Coding!!!

Thanks,
Prasant

Hi,
Solution is Normalization...