Working with Oracle Databases using ADO.NET

Mahesh Chand
14y
34.2k
0
0
100
Article

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.