Hi,

Env: VB 2005 Prof, SQL Server 2005

In our application we have to import data from a textfile using transaction
scope. This textfile only contains valid SQL Statements (Inserts, Updates)
generated from another application.
This works fine as long as the SQL Statements are in a single line. But some
of the sql statements exceed single line and carryon for 2-3 lines. So
obviously, this process will fail when it encounters multiple line SQL
statements.

Ofcourse, there is a workdaround to use the streamreader. readtoend so that
the complete txtfile can be sent to command object without having to loop
through. But in this case, we are not handling each sql statement, but
passing the complete file in one go.

I believe this must be something quite straightforward, but haven't been
able to figure out a better way to handle multiple line sql statements?

any ideas and feedback appreciated.

thanks.

Private Sub ImportProcess()
Dim sr As StreamReader
sr = New StreamReader("Y:\filetobeimported.txt")
Dim line As String

Using ts As TransactionScope = New TransactionScope()

Dim myConnection As New SqlConnection(My.Settings.ProjectConnectionString)
Dim myCommand As New SqlCommand()
myCommand.CommandTimeout = 1
myCommand.Connection = myConnection
myConnection.Open()
RecordImported = 0

Do While Not sr.EndOfStream
line = sr.ReadLine
If Not line Is Nothing Then
myCommand.CommandText = line
myCommand.ExecuteNonQuery()
RecordImported = RecordImported + 1
End If
Loop

sr.Close()
sr = Nothing
myConnection.Close()
ts.Complete()
End Using

End Sub

Hi AnikSol,

Since each SQL statement in the text file is ended with the character ';',
we could make use of this feature to get a complete SQL statement once a
time.

The detailed steps are like the following.
1. Get the next line of text from the text file using StreamReader.ReadLine
method.
2. Check whether it is ended with the character ';'.
a. If yes, pass it to a command object to execute. Then go to step 1
until the stream reader goes to the end of the text file.
b. If not, get the next line of text and combine it to the previous
line of text we have got. Go to step 2.

The following is a code snippet.

Dim sr As New StreamReader("VPSShipExprocess.txt")
Dim cmdtxt As String = ""
Do While Not sr.EndOfStream
cmdtxt = sr.ReadLine()
Do While (Not cmdtxt.EndsWith(";") And Not sr.EndOfStream)
cmdtxt += sr.ReadLine()
Loop
If Not cmdtxt is Nothing Then
myCommand.CommandText = cmdtxt
myCommand.ExecuteNonQuery()
End If
Loop

sr.Close()
sr = Nothing

Hope this helps.
If you have anything unclear, please feel free to let me know.


Sincerely,
Linda Liu
Microsoft Online Community Support

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

Linda,

Thanks a lot.

regards