SqlStatementCollection Class
Represents collection of statements that constitute Oracle 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 oracleConn = new OracleConnection(
"User Id=Scott;Password=tiger;Data Source=Ora;");
OracleScript oracleScript = new OracleScript(script,oracleConn);
SqlStatementCollection oracleColl = oracleScript.Statements;
IDataReader oracleReader;
oracleConn.Open();
foreach (SqlStatement oracleStatement in oracleColl)
{
Console.WriteLine(oracleStatement.Text);
try
{
oracleReader = oracleStatement.Execute();
Console.WriteLine(" Records affected " + oracleReader.RecordsAffected);
while (oracleReader.Read())
{
Console.WriteLine(oracleReader.GetString(0));
}
oracleReader.Close();
Console.WriteLine();
}
catch
{
Console.WriteLine(" Failed");
}
}
oracleConn.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 oracleConn As OracleConnection = New OracleConnection( _
"User Id=Scott;Password=tiger;Data Source=Ora;")
Dim oracleScript As OracleScript = New OracleScript(script, oracleConn)
Dim oracleColl As SqlStatementCollection = oracleScript.Statements
Dim oracleReader As IDataReader
oracleConn.Open()
For Each oracleStatement As SqlStatement In oracleColl
Console.WriteLine(oracleStatement.Text)
Try
oracleReader = oracleStatement.Execute()
Console.WriteLine(" Records affected " & oracleReader.RecordsAffected)
While oracleReader.Read()
Console.WriteLine(oracleReader.GetString(0))
End While
oracleReader.Close()
Console.WriteLine()
Catch ex As Exception
Console.WriteLine(" Failed")
End Try
Next oracleStatement
oracleConn.Close() |