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

Posted on 2003-03-25
Medium Priority
Last Modified: 2008-03-17
How can I run a .sql file from vb.net or asp.net?
Do I need to shell SQL*Plus and pass the arguments?

Question by:GoodJun
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
  • 2

Accepted Solution

reaves earned 189 total points
ID: 8206440
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

Assisted Solution

KBerger earned 186 total points
ID: 8208727
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)

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
LVL 10

Author Comment

ID: 8211080
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

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 ?
LVL 10

Author Comment

ID: 8895536
I just want to close this question. I will split the points for you.

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

764 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