nomadicprs
New member
- Joined
- Nov 20, 2009
- Messages
- 1
- Programming Experience
- 3-5
I have a small console application that runs through a list of stock symbols on a sql server. It fills a datatable with the symbols and for each symbol it imports a csv file into a table called symbolselctedprices. It starts out really really fast and then slows to a crawl. I noticed when running in debuger I get a a wierd msg that says something about contextswitchdeadlock. Anyway I'm kind of new to the vbnet thing so could someone take a look at the below code and tell me if something just jumps out as you as my problem.
Imports System.IO
Imports System.Data.SqlClient
Imports System.Net
Module Module1
Sub Main()
'On Error Resume Next
Dim url As String
Dim SymbolSelect As String
Dim DCon As New SqlConnection()
DCon.ConnectionString = myconnectionstring
DCon.Open()
'DCon2.Open()
Dim dt2 As New DataTable("Symbols")
Dim dt As New DataTable()
Dim sql As String
sql = "Select [Symbol] From Symbols"
Dim objCommand As New SqlCommand(sql, DCon)
Dim da As New SqlDataAdapter(objCommand)
da.Fill(dt2)
Dim dr As DataRow
Dim line As String = Nothing
Dim i As Integer = 0
Dim data() As String
Dim row As DataRow
For Each dr In dt2.Rows
data = Nothing
sql = "Delete TempPrices"
objCommand = New SqlCommand(sql, DCon)
objCommand.ExecuteNonQuery()
SymbolSelect = dr("Symbol")
Console.WriteLine(SymbolSelect)
dt.Clear()
dt.Dispose()
objCommand.Dispose()
line = Nothing
i = 0
Using sr As StreamReader = File.OpenText("C:\Stocks\" & SymbolSelect & ".csv")
line = sr.ReadLine()
line = sr.ReadLine()
Do While line IsNot Nothing
data = line.Split(","c)
If data.Length > 0 Then
If i = 0 Then
For Each item In data
dt.Columns.Add(New DataColumn())
Next item
i += 1
End If
row = dt.NewRow()
row.ItemArray = data
dt.Rows.Add(row)
End If
line = sr.ReadLine()
Loop
End Using
Using copy As New SqlBulkCopy(DCon)
copy.ColumnMappings.Add(0, 0)
copy.ColumnMappings.Add(1, 1)
copy.ColumnMappings.Add(2, 2)
copy.ColumnMappings.Add(3, 3)
copy.ColumnMappings.Add(4, 4)
copy.ColumnMappings.Add(5, 5)
copy.ColumnMappings.Add(6, 6)
copy.DestinationTableName = "TempPrices"
copy.WriteToServer(dt)
End Using
sql = "Begin Transaction Insert Into stockquotes.dbo." & SymbolSelect & "Prices (DateC, OpenP, High, Low, [Close], Volume, AdjClose) Select DateC, OpenP, High, LOW, [Close], Volume, AdjClose FROM Master.dbo.TempPrices Commit;"
objCommand = New SqlCommand(sql, DCon)
objCommand.ExecuteNonQuery()
sql = "Delete TempPrices"
objCommand = New SqlCommand(sql, DCon)
objCommand.ExecuteNonQuery()
Console.WriteLine(SymbolSelect & " Updated")
Next
Imports System.IO
Imports System.Data.SqlClient
Imports System.Net
Module Module1
Sub Main()
'On Error Resume Next
Dim url As String
Dim SymbolSelect As String
Dim DCon As New SqlConnection()
DCon.ConnectionString = myconnectionstring
DCon.Open()
'DCon2.Open()
Dim dt2 As New DataTable("Symbols")
Dim dt As New DataTable()
Dim sql As String
sql = "Select [Symbol] From Symbols"
Dim objCommand As New SqlCommand(sql, DCon)
Dim da As New SqlDataAdapter(objCommand)
da.Fill(dt2)
Dim dr As DataRow
Dim line As String = Nothing
Dim i As Integer = 0
Dim data() As String
Dim row As DataRow
For Each dr In dt2.Rows
data = Nothing
sql = "Delete TempPrices"
objCommand = New SqlCommand(sql, DCon)
objCommand.ExecuteNonQuery()
SymbolSelect = dr("Symbol")
Console.WriteLine(SymbolSelect)
dt.Clear()
dt.Dispose()
objCommand.Dispose()
line = Nothing
i = 0
Using sr As StreamReader = File.OpenText("C:\Stocks\" & SymbolSelect & ".csv")
line = sr.ReadLine()
line = sr.ReadLine()
Do While line IsNot Nothing
data = line.Split(","c)
If data.Length > 0 Then
If i = 0 Then
For Each item In data
dt.Columns.Add(New DataColumn())
Next item
i += 1
End If
row = dt.NewRow()
row.ItemArray = data
dt.Rows.Add(row)
End If
line = sr.ReadLine()
Loop
End Using
Using copy As New SqlBulkCopy(DCon)
copy.ColumnMappings.Add(0, 0)
copy.ColumnMappings.Add(1, 1)
copy.ColumnMappings.Add(2, 2)
copy.ColumnMappings.Add(3, 3)
copy.ColumnMappings.Add(4, 4)
copy.ColumnMappings.Add(5, 5)
copy.ColumnMappings.Add(6, 6)
copy.DestinationTableName = "TempPrices"
copy.WriteToServer(dt)
End Using
sql = "Begin Transaction Insert Into stockquotes.dbo." & SymbolSelect & "Prices (DateC, OpenP, High, Low, [Close], Volume, AdjClose) Select DateC, OpenP, High, LOW, [Close], Volume, AdjClose FROM Master.dbo.TempPrices Commit;"
objCommand = New SqlCommand(sql, DCon)
objCommand.ExecuteNonQuery()
sql = "Delete TempPrices"
objCommand = New SqlCommand(sql, DCon)
objCommand.ExecuteNonQuery()
Console.WriteLine(SymbolSelect & " Updated")
Next