Represents a single statement in a script.
This sample shows how to retrieve collection of statements that make up the script and execute them one by one.
string script =
"DROP TABLE DEPT;"+
"CREATE TABLE DEPT ("+
" DEPTNO NUMBER(38) NOT NULL,"+
" DNAME VARCHAR2(14),"+
" LOC VARCHAR2(13)"+
");"+
"INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');"+
"INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');"+
"INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');"+
"INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');"+
"SELECT * FROM DEPT";
OracleConnection myConn = new OracleConnection(
"User Id=Scott;Password=tiger;Data Source=Ora;");
OracleScript myScript = new OracleScript(script,myConn);
SqlStatementCollection myColl = myScript.Statements;
IDataReader myReader;
myConn.Open();
foreach (SqlStatement myStatement in myColl)
{
Console.WriteLine(myStatement.Text);
try
{
myReader = myStatement.Execute();
Console.WriteLine(" Records affected " + myReader.RecordsAffected);
while (myReader.Read())
{
Console.WriteLine(myReader.GetString(0));
}
myReader.Close();
Console.WriteLine();
}
catch
{
Console.WriteLine(" Failed");
}
}
myConn.Close();
Dim script As String = _
"DROP TABLE DEPT;" & _
"CREATE TABLE DEPT (" & _
" DEPTNO NUMBER(38) NOT NULL," & _
" DNAME VARCHAR2(14)," & _
" LOC VARCHAR2(13)" & _
");" & _
"INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');" & _
"INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');" & _
"INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');" & _
"INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');" & _
"SELECT * FROM DEPT"
Dim myConn As OracleConnection = New OracleConnection( _
"User Id=Scott;Password=tiger;Data Source=Ora;")
Dim myScript As OracleScript = New OracleScript(script, myConn)
Dim myColl As SqlStatementCollection = myScript.Statements
Dim myReader As IDataReader
myConn.Open()
For Each myStatement As SqlStatement In myColl
Console.WriteLine(myStatement.Text)
Try
myReader = myStatement.Execute()
Console.WriteLine(" Records affected " & myReader.RecordsAffected)
While myReader.Read()
Console.WriteLine(myReader.GetString(0))
End While
myReader.Close()
Console.WriteLine()
Catch ex As Exception
Console.WriteLine(" Failed")
End Try
Next myStatement
myConn.Close()