StrataFrame Forum

Getting Oracle to work

http://forum.strataframe.net/Topic280.aspx

By John Frankewicz - 12/12/2005

Having a great deal of frustration trying to get this to really work with an Oracle 9i database.

I will try to go into detail to see what I am missing:

Oracle has a TNSNAMES.ORA file that looks like this:

TRAC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = medsvr)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = Trac)
    )
  )

The Host = medsvr goes to the host file where the ip for the server is found.

When I create a project I add a reference to the System.data.oracleclient assembly.

I create a business object and configure the project properties to look like this:

Data Source=Trac;User ID=medselect;password=tceles

I configure the business object properties and use Oracle. It knows all the tables.

Build the partial class and everything seems ok.

This is in the program.cs SetDataSources:

ConnectionManager.AddRequiredDataSourceItem("", "SQL Connection",

DataSourceTypeOptions.SqlServer, "MyDatabase", "This connection is used by WindowsApplication1.");

// Oracle Connection

ConnectionManager.AddRequiredDataSourceItem("MedSelect", "Oracle Connection",

DataSourceTypeOptions.Oracle, "Trac", "This connection is used by WindowsApplication1.");

Just for a test I hook into the ParentFormLoading and do the following query on the Users table this business object is associated with:

FillDataTable("Select * from Users");

I build and try to run and get exception:

{"Cannot open database \"MyDatabase\" requested by the login. The login failed.\rLogin failed for user 'YOUR-72486D6880\\hp1'."}

WHAT IS GOING ON?

 
By John Frankewicz - 12/12/2005

Additional information:

After poking around I noticed there is a DataSourceKey property on the Business Object. I notice that the program.cs file has a SetDataSources that has the following:

ConnectionManager.AddRequiredDataSourceItem("MedSelect", "Oracle Connection",

DataSourceTypeOptions.Oracle, "Trac", "This connection is used by WindowsApplication1.");

The first argument apparently is the key that must be put in the DataSourceKey property to associate the data source.

So is it true you always have to set this and it never defaults?

I still need to see if I got all the data from my source, but the exception went away.

 
By StrataFrame Team - 12/12/2005

Yes, the business object's default DataSourceKey property is "" or String.Empty which is the same DataSourceKey that the template puts in the calls to AddRequiredDataSourceItem. You can set the data source key in the component designer for the business object, so you only have to set the data source key once for all business objects of that class. You should never have to change the DataSourceKey in code unless you're business object is accessing both Oracle and SQL Server at runtime.



However, what I would do is comment out the first call to ConnectionManager.AddRequiredDataSourcItem that adds the SQL Server connection... you don't need it. Then you can change the Oracle data source's key from "MedSelect" to "" and that's the business object's default. Then make sure to default your business object's back to a DataSourceKey of "" and you'll be in business.



If you want to make sure that the query is pulling all of the data from the database, you can put a breakpoint in your application immediately after the call to your FillDataTable method. Then in your Watch window, you can add "MyBO.CurrentDataTable" and the result will be "{System.Data.DataTable}". You should then see a little magnifying glass the watch window's row... when you click it, you'll see grid showing you the contents of the CurrentDataTable of the business object. Or you could just check the "MyBO.Count" property and make sure that count of the records within the business object is the same as what should be returned by the query.



The error that you were getting telling you that you could not access database "MyDatabase" was because you're business object's key was still set to its default and the default data source item was the SQL Server data source item, which tried to connect to database "MyDatabase". So, your nice Oracle business object was trying to connect to SQL Server at runtime, and the application was telling you that it couldn't connect to the immaginary database.
By StrataFrame Team - 12/12/2005

Once you're program.cs file is configured properly, your SetDataSources method should look like this:



//------------------------------------

// Using the Connection Manager

//------------------------------------



//-- Set the information specific to this application and the data sources

// The application key:

ConnectionManager.ApplicationKey = "WindowsApplication1";

ConnectionManager.ApplicationDefaultTitle = "WindowsApplication1 Connection";

ConnectionManager.ApplicationDefaultDescription = "This application connection is used by WindowsApplication1";



//-- Set the required data source information so that the ConnectionManager can gather it

// SQL Connection

//ConnectionManager.AddRequiredDataSourceItem("", "SQL Connection",

// DataSourceTypeOptions.SqlServer, "MyDatabase", "This connection is used by WindowsApplication1.");

// Oracle Connection

ConnectionManager.AddRequiredDataSourceItem("", "MedSelect Connection",

DataSourceTypeOptions.Oracle, "Trac", "This connection is used by WindowsApplication1.");

// Access Connection

//ConnectionManager.AddRequiredDataSourceItem("", "Access Connection",

// DataSourceTypeOptions.MicrosoftAccess, "", "This connection is used by WindowsApplication1.");

// FoxPro Connection

//ConnectionManager.AddRequiredDataSourceItem("", "Visual Fox Pro Connection",

// DataSourceTypeOptions.VisualFoxPro, "", "This connection is used by WindowsApplication1.");



//-- Make the call to SetConnections which will gather the connection information, show the connection wizard

// if needed and set the DataSources collection on the DataLayer class.

ConnectionManager.SetConnections();





Notice how the SQL Server item is commented out, and the Oracle item is not commented out.