• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 689
  • Last Modified:

run a .sql file from vb.net or asp.net against Oracle

How can I run a .sql file from vb.net or asp.net?
Do I need to shell SQL*Plus and pass the arguments?

Thanks,
0
GoodJun
Asked:
GoodJun
  • 2
2 Solutions
 
reavesCommented:
You could read it is as a text file and then execute it with the command object... Is there a reason that wouldn't work?

--
Ryan Eaves
0
 
KBergerCommented:
Hi GoodJun!

Try this code!

Dim strSQLServer As String="MyServer"
Dim strSQLDatabase As String="MyDB"

Dim oStream As New System.IO.FileStream("C:\test.sql", IO.FileMode.Open)
Dim oReader As New System.IO.StreamReader(oStream)

Dim strSQL As String = oReader.ReadToEnd()
Dim oSQLConnection As New SqlConnection("Data Source=" + strSQLServer + ";Integrated Security=SSPI;Initial Catalog=" + strSQLDataBase)

oSQLConnection.Open()
Dim oAdapt As SqlDataAdapter = New SqlDataAdapter()
Dim oCmd As New SqlCommand(strSQL, oSQLConnection)
oAdapt.SelectCommand = oCmd
Dim oSet As New DataSet("Result")
oAdapt.Fill(oSet, "Result")


You open the sql-file ans read it into a string.
This string is then passed to a command-instance, attached to a valid connection.
The result of your query is then being written into a dataset.

Hope that helps!

Cheers, Kristof
0
 
GoodJunAuthor Commented:
Thanks guys,
The scenario is like this: I have inherited tons of .sql files that can manually run from sql*plus. I want to run them from vb.net. There are a lots of statements that can cause errors when use the oledbcommand object. like :
set termout off
spool c:\myfile.txt
prompt
....

I think these are specific to sql*plus (?, maybe I am wrong here). Is there a way I don't have to modify all the .sql files manually and run them from vb.net ?
0
 
GoodJunAuthor Commented:
I just want to close this question. I will split the points for you.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now