Microsoft KB Archive/308448

Knowledge Base

How To Access an Oracle Database by Using the OLE DB .NET Data Provider and Visual C# .NET

Article ID: 308448

Article Last Modified on 5/13/2007

---

APPLIES TO

• Microsoft .NET Framework 1.1 Service Pack 1
• Microsoft ADO.NET 1.1
• Microsoft Visual C# .NET 2002 Standard Edition
• Microsoft Visual C# .NET 2003 Standard Edition

---

This article was previously published under Q308448

For a Microsoft Visual Basic .NET version of this article, see 308071.

This article refers to the following Microsoft .NET Framework Class Library namespaces:

• System.Data.OleDb

IN THIS TASK

SUMMARY

• Requirements
• Steps to Access an Oracle Database

REFERENCES

SUMMARY

This article demonstrates how to use the ADO.NET OLE DB managed provider to access an Oracle database.

back to the top

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:

• Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server, or Windows NT 4.0 Server
• Oracle Client tools (installed on the computer)
• Microsoft Visual Studio .NET

This article assumes that you are familiar with the following topics:

• Visual Studio .NET
• ADO.NET fundamentals and syntax
• Oracle connectivity

back to the top

Steps to Access an Oracle Database

1. In Oracle, create a table named TestTable as follows:

   Create Table TestTable (c1 char(5));
                       

2. Insert data into TestTable as follows:

   Insert into TestTable c1 values('Test1');
   Insert into TestTable c1 values('Test2');
   Insert into TestTable c1 values('Test3');
                       

3. Start Visual Studio .NET.
4. Create a new Windows Application project in Visual C# .NET.
5. Make sure that your project contains a reference to the System.Data namespace, and add a reference to this namespace if it does not.
6. Drag a Button control to Form1, and change its Name property to btnTest.

7. Use the using statement on the System, System.Data, and System.Data.OleDb namespaces so that you are not required to qualify declarations in those namespaces later in your code.

   using System;
   using System.Data;
   using System.Data.OleDb;
                       

8. Switch to Form view, and double-click btnTest to add the click event handler. Add the following code to the handler:

   String sConnectionString =
       "Provider=MSDAORA.1;User ID=myUID;password=myPWD;
        Data Source=myOracleServer;Persist Security Info=False";
   String mySelectQuery =
       "SELECT * FROM TestTable where c1 LIKE ?";
   
   OleDbConnection myConnection = new OleDbConnection(sConnectionString);
   OleDbCommand myCommand = new OleDbCommand(mySelectQuery, myConnection);
   
   myCommand.Parameters.Add("@p1", OleDbType.Char, 5).Value = "Test%";
   myConnection.Open();
   OleDbDataReader myReader = myCommand.ExecuteReader();
   int RecordCount=0;
   try
   {
       while (myReader.Read())
       {
           RecordCount = RecordCount + 1;
       MessageBox.Show(myReader.GetString(0).ToString());
       }
       if (RecordCount == 0)
       {
       MessageBox.Show("No data returned");
       }
       else
       {
       MessageBox.Show("Number of records returned: " + RecordCount);
       }
   }
   catch (Exception ex)
   {
       MessageBox.Show(ex.ToString());
   }
   finally
   {
       myReader.Close();
       myConnection.Close();
   }
                       

9. Save your project.
10. On the Debug menu, click Start to run your project.
11. Click the button to display the data.

back to the top

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

176936 INFO: Visual Basic Accessing an Oracle Database Using ADO

For more information about ADO.NET objects and syntax, see the following topic in the Microsoft .NET Framework SDK documentation or MSDN Online:

Accessing Data with ADO.NET
http://msdn2.microsoft.com/en-us/library/e80y5yhx(vs.71).aspx

back to the top

Keywords: kbhowtomaster kbsystemdata KB308448

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.