This article has been
excerpted from book "A Programmer's Guide to ADO.NET in C#".
As discussed earlier, working with Oracle databases is no different from working
with SQL Server or other databases. The only difference is the connection
string. You can use the OleDb or ODBC data provider to connect to an Oracle
database. On this section, I'll show you both ways (OleDb and ODBC) to access
Oracle 8i and 9i databases.
Accessing an Oracle 8i Database Using the ODBC Data Provider
To use an ODBC data provider, the first thing you need to do is add a reference
to the ODBC data provider and include the using statement in your application to
add the Microsoft.Data.Odbc namespace as follows:
using
Microsoft.Data.Odbc;
After that you follow same familiar steps: creating a connection string, adding
data from database to a data adapter, and filling a dataset. To test this sample
application, I created a Windows application, added a DataGrid control to the
form, and added the code in listing 11-5 to the Form_Load event.
Listing 11-5: Accessing an Oracle database using the ODBC data adapter
private void
Form1_Load(object sender, System.EventArgs
e)
{
string connString =
"Driver={Oracle ODBC Driver};" +
"Server=localhost;UID=system;PWD=manager;";
OdbcConnection conn = new
OdbcConnection(connString);
if (conn.State !=
ConnectionState.Open)
conn.Open();
OdbcDataAdapter da = new
OdbcDataAdapter("SELECT * FROM STDTABLE",
conn);
DataSet ds =
new DataSet("STDTABLE");
da.Fill(ds, "STDTABLE");
dataGrid1.DataSource = ds.DefaultViewManager;
// Close connection
if (conn.State ==
ConnectionState.Open)
conn.Close();
}
As you can see from listing 11-5, I created a connection with the Oracle ODBC
driver with the server as localhost, the user ID as System, and the password as
manager.
You may want to change the user ID and password if you're using something
different from those used in listing 11-5. After that I created a data adapter
by selecting data from STDTABLE, created a dataset, and filled the dataset by
calling the Fill method of OdbcDataAdapter. The last step was to bind the
dataset to a data grid.
The output of listing 11-5 looks like figure 11-37.
Figure 11-37: Data view in a data grid from an Oracle database
Accessing an Oracle 8i Database Using the OleDb Data Provider
If you don't have an ODBC driver for the Oracle database, OLEDB is another way
to access the database. You use the Oracle provider MSDAORA (see listing 11-6).
The DSN name is oracle, the user ID is system, and the password is manager. You
may want to change the user ID and password if you're not using the default. I
also created an instance of OleDbConnection in listing 11-6. You can add these
variables either publicity or privately in your class.
Listing 11-6: The connection string from the Oracle OleDb data provider
string connString =
"Provider=MSDAORA;DSN=oracle;"
+ "User ID=system;Password=manager";
OleDbConnection conn = new
OleDbConnection();
Once you have the connection string, you use this string to open a connection.
You work with the connection in the same way as before: creating a data adapter
or command, executing commands, filling datasets, and so on.
As you can see from Listing 11-7, I simple set the connection string and opened
the connection. After that I used connection as I've been doing with all the
other databases. It's the same steps as creating an OleDbDataAdapter: creating a
dataset, filling data from a database table STDTABLE, and binding the dataset to
data grid to display the data.
Listing 11-7: Viewing data from an Oracle database table
private void
ViewDataBtn_Click(object sender, System.EventArgs
e)
{
// Open connection
if not already open
conn.ConnectionString = connString;
if (conn.State !=
ConnectionState.Open)
conn.Open();
OleDbDataAdapter da =
new OleDbDataAdapter("SELECT
* FROM STDTABLE", conn);
DataSet ds =
new DataSet("STDTABLE");
da.Fill(ds, "STDTABLE");
dataGrid1.DataSource = ds.DefaultViewManager;
// Close connection
if (conn.State ==
ConnectionState.Open)
conn.Close();
}
As you've been doing in the other samples, you can create a SQL statement and
execute it against an Oracle database. As you can see from listing 1-8, I
created an INSERT statement to insert data into STDTABLE with three columns (MyId,
myName, and myAddress) with values and then called the
OleDbCommand.ExecuteNonQuery method to execute the command.
Listing 11-8: Executing an INSERT statement
string sql =
" ";
sql = "INSERT INTO
STDTABLE(MyId, myName, myAddress) "
+ "VALUES(1001, 'new name', 'new
address')";
try
{
// Create
Command object and Execute SQL statement
OleDbCommand cmd = new
OleDbCommand(sql, conn);
cmd.ExecuteNonQuery();
}
catch (OleDbException ae)
{
string strMessage =
"";
for (int
i = 0; i < ae.Errors.Count; i++)
{
strMessage += ae.Errors[i].Message +
" - " +
ae.Errors[i].SQLState + "\n";
}
MessageBox.Show(strMessage);
}
// Close connection
if (conn.State ==
ConnectionState.Open)
conn.Close();
Similar to listing 11-8, you can create UDATE and DELETE commands to update and
delete data from the database.
Working with an Oracle 9i Database
In the previous section, you saw a sample of the Oracle 8i database. In this
section, I will show you how to work with Oracle 9i databases using ODBC data
providers.
The Oracle 9i connection string looks like this:
string connString =
"Driver={Oracle in OraHome90};" +
"Server=localhost;UID=system;PWD=mahesh;";
Where Oracle in OraHome90 is the Oracle ODBC driver. I've used the user ID
system and the password mahesh. You need to change these to your user ID and
password. If you're using the default user ID and password, you can use the
system.manager or scott/tiger pairs as the user ID and password.
In this sample application, I'll create a database table called mytable with
four columns (Id, Name, Address, and Zip) of type integer, string, string and
integer, respectively. The Id column is the primary key column.
To test this application, I created a Windows application and added a DataGrid
control and three buttons to the form by dragging the controls from the toolbox
to the form and changing the name of the buttons to Create table, Fill data, and
Delete table. The Create Table button creates myTable and adds data to the
table. The Fill Data button reds the table and views the data in the DataGrid
control, and the Delete Table button remove the table from the database.
First, I added a reference to the Microsoft.Data.Odbc namespace and then added
the following variables in the beginning of my form class:
//connection string
for Oracle 9i
string connString =
"Driver={Oracle in OraHome90};" +
"Server=localhost;UID=system;PWD=mahesh;";
string sql =
" SELECT * FROM OraTable";
// Create a
connection
OdbcConnection conn = null;
// Create a command
OdbcCommand cmd = null;
OdbcDataAdapter da = null;
Now I create connection and command objects on the form load, as shown here:
private void
Form1_Load(object sender, System.EventArgs
e)
{
// Create a
connection and command
conn = new OdbcConnection(connString);
cmd = new OdbcCommand(sql, conn);
}
The Create table button handler creates a new table. The code of this button
handler looks like listing 11-9. Listing 11-9 also creates myTable and adds data
to the table.
Listing 11-9: Creating a new database table and adding data to it
private void
button1_Click(object sender, System.EventArgs
e)
{
try
{
if (conn.State !=
ConnectionState.Open)
conn.Open();
string sql =
"CREATE TABLE myTable" +
"(Id INTEGER CONSTRAINT PkeyMyId
PRIMARY KEY," +
"Name CHAR(50), Address CHAR(255),
Zip INTEGER)";
cmd = new OdbcCommand(sql, conn);
cmd.ExecuteNonQuery();
// Adding
records the table
sql = "INSERT INTO myTable(ID,
Name, Address, Zip) " +
"VALUES (1001, 'Mr. Galler Hall', "
+
" '23 Church Street, Pace City, NY',
32432 ) ";
cmd = new OdbcCommand(sql, conn);
cmd.ExecuteNonQuery();
sql = "INSERT INTO myTable(ID,
Name, Address, Zip) " +
"VALUES (1002, 'Dr. Dex Leech', "
+
" '3rd Aven, President Road, NJ',
743623) ";
cmd = new OdbcCommand(sql, conn);
cmd.ExecuteNonQuery();
sql = "INSERT INTO myTable(ID,
Name, Address, Zip) " +
"VALUES (1003, 'Lambert Mart', "
+
" '45 Petersburgh Ave, Jacksonville,
GA', 53492) ";
cmd = new OdbcCommand(sql, conn);
cmd.ExecuteNonQuery();
sql = "INSERT INTO myTable(ID,
Name, Address, Zip) " +
"VALUES (1004, 'Moann Texur', "
+
" '4th Street, Lane 3, Packville,
PA', 23433) ";
cmd = new OdbcCommand(sql, conn);
cmd.ExecuteNonQuery();
// close
connection
if (conn.State ==
ConnectionState.Open)
conn.Close();
}
catch (OdbcException ae)
{
MessageBox.Show(ae.Message.ToString());
}
}
The view data button handler reads data from myTable and views it in a DataGrid
control. Listing 11-10, which should look familiar, shows the View Data
button-click handler. I created a data adapter, filled a DataSet using the
OdbcDataAdapter's Fill method, and bound the DataSet to the DataGrid control
using DataGrid.DefaultViewManager.
Listing 11-10: Viewing myTable data in a DataGrid
private void
button2_Click(object sender, System.EventArgs
e)
{
try
{
if (conn.State !=
ConnectionState.Open)
conn.Open();
da = new OdbcDataAdapter("SELECT
* FROM myTable", conn);
DataSet ds =
new DataSet("ds");
da.Fill(ds, "myTable");
dataGrid1.DataSource = ds.DefaultViewManager;
// Close
connection
if (conn.State ==
ConnectionState.Open)
conn.Close();
}
catch (OdbcException ae)
{
MessageBox.Show(ae.Message.ToString());
}
}
The Delete table button handler removes myTable from the database. As you can
see from listing 11-11, I simply constructed a DROP TABLE SQL statement and
executing it using the OdbcCommand.Execute method.
Listing 11-11: Execution a DROP TABLE SQL statement using OdbcCommand
private void
button3_Click(object sender, System.EventArgs
e)
{
try
{
if (conn.State !=
ConnectionState.Open)
conn.Open();
// Construct
DROP TABLE query and execute it
string sql =
"DROP TABLE myTable";
cmd = new OdbcCommand(sql, conn);
cmd.ExecuteNonQuery();
// Close
connection
if (conn.State ==
ConnectionState.Open)
conn.Close();
}
catch (OdbcException ae)
{
MessageBox.Show(ae.Message.ToString());
}
}
Conclusion
Hope this article would have helped you in understanding
working
with Oracle Databases using ADO.NET. See my other articles on the website on ADO.NET.
![adobook.jpg]()
|
This essential guide
to Microsoft's ADO.NET overviews C#, then leads you toward deeper
understanding of ADO.NET. |