Connecting to Oracle with BDC in C#.NET

Generator QR Code in C#.NET Connecting to Oracle with BDC

Connecting to Oracle is similar to connecting to SQL, as we fully explained in chapter 2. There are just a few differences that we need to mention. Within the application definition file, you ll need to set authentication. Of course, you can use Single Sign-On, which was explained in chapter 3. But this is required only if you want to map your Active Directory credentials to the Oracle database credentials.

Setting up SSO for Oracle is the same as setting up SSO for SQL. If you re connecting without SSO, you need to set the authentication type to PassThrough, even though PassThrough isn't used and the username and password are stored within the ADF as clear text. (See figure B.1.) Third-party products such as BDC Meta Man can write the application definition file for you, allowing you to quickly get up and running with the Business Data Catalog for an Oracle data source. One requirement is to have the Oracle client installed on the web front-end server that s connecting to the database.

In addition to the preceding, you ll need to have the Oracle client installed on the web front-end server that s connecting to Oracle. Another problem with Oracle and BDC is that Oracle stored procedures aren t supported. Therefore, in order to connect to Oracle stored procedures, you ll need to wrap them with a web service. Creating web services is explained in appendix C.

Using web services allows you to connect to just about any data source, as you can create the connection from within your web methods and expose the data. This is great for data sources that don t have a direct way of connecting to them, and perhaps rely on ODBC. Also, web services can help you get around certain issues, such as BDC not offering any support for Oracle stored procedures. In this appendix, we ll explore how to author your own WCF web services to provide the data in a format that works well with the Business Data Catalog. It s sometimes assumed that, because an application exposes data via web services, you can consume those web services with the Business Data Catalog. Often that s not the case, as we ve seen with SAP. The web services really need to provide the data in a format that s acceptable and works well with BDC. Of course, you don t have to write your web service as a WCF, but this is generally becoming useful, as you can pass your data securely using WCF, and you can use multiple end points, giving you additional options for protocols and transports. If you re unfamiliar with WCF, there s a useful article available on the MSDN: http://msdn.microsoft.com/en-us/library/ms731082.aspx.

Web services that you create should have three methods for each entity that you want to connect to. These correspond to:

1 2 3

The method shouldn t return a data set or XML but a list of objects. This helps BDC understand the data type of each property. Before you create your web methods, you need to create a class to serve as a data contract that describes the entity you re connecting to, as shown in listing C.1. Also in listing C.1, you can see an example web service that connects to a Customer table in Northwind. It then creates a data set, but reads the data into a Customer object, which is then returned. The GetCustomersFinder method exposes all of the rows, as with a finder method in your ADF.

public List<Customers> GetCustomersFinder() Connects to data { source and List<Customers> customersList = new creates DataSet List<Customers>(); string sqlQuery = "Select * From dbo.[Customers] "; using (SqlConnection sqlConnection = new SqlConnection(connectionString)) { sqlConnection.Open(); SqlCommand comm = new SqlCommand(sqlQuery, sqlConnection); DataSet dataSet = new DataSet(); SqlDataAdapter adapter = new SqlDataAdapter(comm); adapter.Fill(dataSet, "dbo.[Customers]"); foreach (DataRow dr in dataSet.Tables[0].Rows) { Customers customers = new Customers();

(String)dr["CustomerID"];

customers.CustomerID = dr["CustomerID"] is DBNull string.Empty :

customers.CompanyName = dr["CompanyName"] is DBNull string.Empty : (String)dr["CompanyName"]; customers.ContactName = dr["ContactName"] is DBNull string.Empty : (String)dr["ContactName"]; customers.ContactTitle = dr["ContactTitle"] is DBNull string.Empty :