Monday, August 3, 2009

How to Read and Run the SQL Script file using VB.Net?

Imports System.IO
Imports System.Text

Public Function ExecuteSQLScript() As Boolean
Dim conn As New SqlConnection("uid=test;pwd=test;database=test;server=kannaserver")
Try
Private fileUrl As String = ""
fileUrl = "D:\RunScript\ScriptFile.sql"

Dim request As Net.WebRequest = Net.WebRequest.Create(fileUrl)
Using sr As New StreamReader(request.GetResponse().GetResponseStream(),
System.Text.UTF8Encoding.Default)'it will read actual content what exist in the file.
conn.Open()
While Not sr.EndOfStream
Dim sb As New System.Text.StringBuilder()
Dim cmd As SqlCommand = conn.CreateCommand()
While Not sr.EndOfStream
Dim str As String = sr.ReadLine()
If str IsNot Nothing AndAlso str.ToUpper().Trim().Equals("GO") Then
Exit While
End If
sb.AppendLine(str)
End While
cmd.CommandText = sb.ToString()
cmd.CommandTimeout = 0 '0 means Infinity Time
cmd.ExecuteNonQuery()
End While
End Using
Return True
Catch ex As Exception
Return False
Finally
If conn.State = ConnectionState.Open Then
conn.Close()
End If
End Try
End Function

No comments:

 
Feedback Form