Build a. NET Application on the Oracle Database with Microsoft Visual Studio 2010. by John Paul Cook. Learn the basic yet essential processes involved in building a. Technical articles, content and resources for IT Professionals working in Microsoft technologies. Stepsbystep Connecting to Microsoft SQL Server database from Oracle using Database Gateway for SQL Server. Build a. NET Application on the Oracle Database with Microsoft Visual Studio 2. John Paul Cook. Learn the basic yet essential processes involved in building a. NET application that uses an Oracle database. Published June 2. With the popularity of Microsofts. NET Framework, many developers are hungry for information about the best means of integrating. NET applications with Oraclenot only in terms of basic connectivity, but also in relationship to effective and efficient application development using Microsoft Visual Studio 2. The Visual Studio 2. In this article, Ill explain the basic yet essential processes involved in building a C or Visual Basic. NET application that uses an Oracle database, including How to add project references to support Oracle class libraries in your. NET project. How to create Oracle Database connection strings. How to work with Connection, Command, and Data. Reader objects. You will have the opportunity to apply what you have learned in three practice labs, ranging in difficulty from the relatively simple to the more complex. If you are already familiar with building. NET applications for Oracle Database with Visual Studio 2. Visual Studio 2. 01. For information and labs about how to secure your application, see my article Securing a. NET Application on the Oracle Database. Also, see the OTN. NET Developer Center for technical articles covering a range of Oracle. NET application lifecycle issues. Note that the free Oracle Developer Tools for Visual Studio, available for download from OTN, provides a Visual Studio add in that makes the development of. NET apps on Oracle much easier and more intuitive. That subject is beyond our scope here, however see the Oracle Developer Tools for Visual Studio Product Center for more information. NET Data Provider. In addition to basic Oracle client connectivity software,. NET applications require the use of what is known as a managed data provider where managed refers to code managed by the. NET framework. The data provider is the layer between the. NET application code and the Oracle client connectivity software. In almost every case, the best performance is achieved by using a provider optimized for a specific database platform instead of the generic. NET OLE DB data provider. Oracle, Microsoft, and third party vendors all offer. NET data providers optimized for an Oracle database. Oracle and Microsoft make their Oracle data providers available for free. Microsofts provider for the. NET Framework is deprecated. Oracle continues to support and develop their. NET data provider, Oracle Data Provider for. NET ODP. NET. In this article, we will use ODP. NET, which is included with the Oracle Database or as a separate download. ODP. NET provides standard ADO. NET data access, while exposing Oracle database specific features, such as XML DB, data access performance optimizations, and Real Application Clusters load balancing and fast connection failover. The current ODP. NET version, 1. Oracle Database 9i Release 2 servers and higher versions. The database server can be on Windows, Linux, UNIX, or any other operating system platform Oracle database supports. When ODP. NET and Oracle client software are installed, application development using Visual Studio can begin. Its a good idea to confirm client connectivity before starting development. If you can connect to Oracle Database using Oracle client software, such as SQLPlus on the same machine as Visual Studio, then your Oracle client side software is properly installed and configured. If you are new to Oracle, see the section Installing. NET Products in the Oracle Database 2 Day Developers Guide for background information regarding installing and configuring ODP. NET specifically, or to the Oracle Database Documentation Library for general information about Oracle Database. Creating a Project in Visual Studio 2. Lets create an ODP. NET application that retrieves data from the Oracle database. Later, well see how to perform error handling with ODP. NET and handle an additional data retrieval scenario. After starting Visual Studio, the first task is to create a project. You can either click New Project as shown below or select File New Project. Figure 1 Creating a new project in Visual Studio 2. A New Project dialog box appears. On the left side of the dialog box under Installed Templates, select the programming language of your choice. In our example, Visual Basic was chosen. On the middle of the dialog, choose a project template. To keep things simple, a Windows Forms Application is selected. Figure 2 Using the New Project dialog. Youll want to specify meaningful names for the project name we used Ora. Win. App and the solution name we used Ora. Win. App. A solution contains one or more projects. When a solution contains only one project, many people use the same name for both. Notice that there is a dropdown list box allowing you to specify which version of the. NET Framework you want to target. If you are writing applications for an older version of the. NET Framework, you should select the appropriate version from the dropdown list. Click the Ok button to continue. Adding a Reference Because our project must connect to an Oracle database, it is necessary to add a reference to the ODP. NET DLL containing the data provider of our choice. Within the Solution Explorer on the right side of Visual Studio, select the project name, right click and select Add Reference. Alternatively, you can go to the menu bar and select Project and then select Add Reference. Figure 3 Adding a reference. The Add Reference dialog box appears. Select the. NET tab. Visual Studio will construct a list of. NET components available for you to add to your project. This may take several seconds. When complete, you can click on the Component Name column to alphabetize the component list. Figure 4 Selecting the ODP. NET Managed Data Provider for. NET 4. ODP. NET is found under the Oracle. Data. Access component name. Select Oracle. Data. Access from the list, then click the OK button to make the ODP. NET data provider known to your project. Be sure to select the correction version. Since this is a. NET 4 project, the 4. Oracle. Data. Access was chosen here, but you can chose any ODP. NET version beginning with 4. Visual BasicC Statements After adding references, it is standard practice to add Visual Basic Imports statements or C using statements. Technically, these statements are not required, but they do allow you to refer to database objects without using lengthy, fully qualified names. By convention, these statements appear at or near the top of a code file, before the namespace or class declaration. Imports Oracle. Data. Access. Client Visual Basic ODP. NET Oracle managed providerusing Oracle. Data. Access. Client C ODP. NET Oracle managed provider. If you added the reference, Intellisense will help you complete the addition of an Imports or using statement as shown in Figure 5. Figure 5 Adding an Imports statement in Visual Basic. Connection Strings and Objects An Oracle connection string is inseparable from Oracle names resolution. In this article, we will connect to Oracles sample HR schema with user id hr and password hr. The tnsnames. ora file is an Oracle network configuration file that defines database addresses for establishing connections. Suppose we had a database alias of Ora. Db defined in a tnsnames. Ora. DbDESCRIPTIONADDRESSLISTADDRESSPROTOCOLTCPHOSTORASRVRPORT1. CONNECTDATASERVERDEDICATEDSERVICENAMEORCLThe Ora. Db alias defines the database address connection information for the client. To use the Ora. Db alias defined in the tnsnames. Dim oradb As String Data SourceOra. Db User Idhr Passwordhr Visual Basicstring oradb Data SourceOra. Db User Idhr Passwordhr CYou can modify the connection string to obviate the need for the tnsnames. Simply replace the name of the alias with how it would be defined in a tnsnames. The database alias in this article is specific to my database setup. Click here for more information about creating a database alias for your database. Visual Basic Dim oradb As String Data SourceDESCRIPTION ADDRESSPROTOCOLTCPHOSTORASRVRPORT1. CONNECTDATASERVICENAMEORCL User Idhr Passwordhr Cstring oradb Data SourceDESCRIPTION ADDRESSPROTOCOLTCPHOSTORASRVRPORT1.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2017
Categories |