With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.
Course Of The Month
6 days, 18 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Read in large sql query from file VB.NET
Posted on 2011-03-04
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!
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!
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
4
-
2
7 Comments
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.
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.
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(objRead
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??
Dim strContents As New StringBuilder
Dim objReader As StreamReader
objReader = New StreamReader(FullPath)
Try
Do While objReader.Peek() >= 0
strContents.Append(objRead
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??
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!!
=)
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
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. ;)
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. ;)
Featured Post
Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat. The purpose of this eBook is to educate the reader about ransomware attacks.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied?
You can use SQL Server Initialize from Backup…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes. We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses
Course of the Month6 days, 18 hours left to enroll
724 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.