Solved

Read in large sql query from file VB.NET

Posted on 2011-03-04
7
746 Views
Last Modified: 2012-05-11
Hey guys does anyone know how to read in a large sql query from a file into vb.net?

I tried using a stream reader to read in the contents of the sql query but I'm losing all of the CRLF's and that's causing the ExecuteDataReader to fail.

I need to be able to preserve all of the CRLF's that take place in the query I guess...

Suggestions greatly appreciated!

Thanks!
0
Comment
Question by:romieb69
  • 4
  • 2
7 Comments
 
LVL 23

Assisted Solution

by:Rajkumar Gs
Rajkumar Gs earned 250 total points
ID: 35039727
Try this code

 Dim fileText as string = My.Computer.FileSystem.ReadAllText("c:\AnyFile.txt")

Raj
0
 

Author Comment

by:romieb69
ID: 35040264
That seems to do the same thing. CRLF's are not included in the string.

One Idea I had was maybe to do a line read and then append a CRLF at the end of each of those in a string builder ??? Hoping there is an easier way to do this though with less code.
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 35040302
That idea should work.

I have no computer right now to figure out your current issue.

Raj
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:romieb69
ID: 35040453
Well that was close... but not quite there yet.  Doing something like this certainly worked as far as preserving the CRLF's:

Dim strContents As New StringBuilder
Dim objReader As StreamReader
objReader = New StreamReader(FullPath)
    Try
           Do While objReader.Peek() >= 0
                strContents.Append(objReader.ReadLine())
                strContents.Append(vbCrLf)
            Loop
....

When you look at strContents.ToString you can see that all the carriage returns and line feeds are in place... but when I pass it into :   sqlCmd.CommandText = sqlStr.ToString
the value of sqlCmd.CommandText no longer has the crlf's ... it's all one long string with no CRLF delimeters.  

Humm so this seems to be an issue with Data.SqlClient.SqlCommand ... I wonder what would happen with SqlTransaction??  

0
 

Accepted Solution

by:
romieb69 earned 0 total points
ID: 35040600
Ok I think I got this figured out... couple things about this:

1. Do not use the word GO in your script. Causes errors.
2. Use ExecuteNonQuery


Attached is the code that works:

Hope this helps someone!!
=)
Imports System.Data.SqlClient
Imports System.IO
Imports System.Text
Public Class frmMain

    'Create a connection string 
    Public strConString As String
    Public conHistDB As New SqlConnection(strConString)
    Public sqlCmd As New SqlCommand
.....


Try
                sqlCmd.Connection = conHistDB
                sqlStr = GetFileContents(txtScriptsPath.Text + "\DropAndCreateAllTables2.sql")
                'sqlStr = My.Computer.FileSystem.ReadAllText(txtScripts.Text + "\DropAndCreateAllTables.sql")


                sqlCmd.CommandText = sqlStr.ToString
                sqlCmd.ExecuteNonQuery()

            Catch ex As Exception
                rtbActivityMon.AppendText(ex.Message.ToString)

            End Try
.....


Public Function GetFileContents(ByVal FullPath As String, _
       Optional ByRef ErrInfo As String = "") As StringBuilder

        Dim strContents As New StringBuilder
        Dim objReader As StreamReader

        Try

            objReader = New StreamReader(FullPath)
            Do While objReader.Peek() >= 0
                strContents.Append(objReader.ReadLine())
                strContents.Append(vbCrLf)
            Loop
        Catch ex As Exception
            rtbActivityMon.AppendText(ex.Message.ToString)
        End Try
        Return strContents
    End Function

Open in new window

0
 
LVL 33

Expert Comment

by:Todd Gerbert
ID: 35043756
I'm not sure if your issue & solution was the "GO" keyword/ExecuteNonQuery, or the CRLF thing, but just wanted to point out that File.ReadAllText leaves the CRLF's in tact, as they appear in the file.

If you're actually not seeing them after File.ReadAllText, could be that 1) When you hove the mouse over a string variable in Visual Studio while debugging it doesn't show the CRLF's, but they're there - or 2) the file you're reading from has a UNIX-style format, with newline's at the end of each line instead of Windows' carriage return/linefeed pair.

Probably neither here nor there anyway, just felt compelled to espouse my two cents. ;)
0
 

Author Closing Comment

by:romieb69
ID: 35081184
Accepted own solution because it works =)
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question