Cool

Ah, got it. That makes sense.

This isn't specific to SQL Server, but rather the database that you pull the schema from.  So if you had mapped to Oracle, then this would have the Oracle data source in there. 

This property is used to create the INSERT and UPDATE commands.  Whether you are using INSERT and UPDATE sprocs or not, this collection gets use to determine how to build the INSERTs and UPDATEs within the DataSourceItem.  So if you have the need to support both SQL and ORACLE on the same BO and receiving an error on updates or inserts when swapping, then you could override this property within your base BO and handle this yourself.

I think this one was missed. I'm curious how that FieldNativeDbType property is used during access myself...

In the generated BO there were statements generated in the Initializae component like:

requiredField1.FieldType = System.Data.SqlDbType.Text;

Also in the Item Property Implementation section of the BO:

_FieldNativeDbTypes.Add("customers_int_codeid", (int)System.Data.SqlDbType.Int);

 

These do seem to be SQL specific. Is there another way to have the mapper generate both in the BO or a more generalized method. Basically I am trying to determine how far the framework can be used and where we need to begin implementing our custom code. The framework is goog and I want to make sure that we are working with it rather than against it.

 

I had jury duty and have been out of the office and the other developers were out of the office for other reasons.  This doesn't generally happen this way, but this is the nice thing about the forum and SF community...people have ideas and step-up!

There were a lot of good ideas and Greg was really on track here.  The BOs do not know or care about where the data comes from.  The only time that you will have an issue in regards to the BO is if you are creating custom queries (SqlCommand / OracleCommand) and then executing that command via a FillTable.  You can back things off a bit an in your base BO have a flag (property) that determines if you are running SQL or Oracle.  Then you can override the FillDataTable method on your base BO to accomodate.  For example, we have some logic in our base BO in our medical app that converts an SqlCommand into a FoxPro based command if necessary, but we always program for SQL Server in our BOs, this way all of the logic that converts back and forth is in the BaseBO.  In this example, it could accept an SqlCommand and then convert it into an OracleCommand (really not to difficult).

This would be my approach.  Hope it helps.

I don't think the BO is actually specific to either SQL or Oracle, as generated by the BO mapper. It interacts with DataTable objects.



What SF does to generate the SQL it needs to do inserts/updates/deletes it to look at the DataSourceItem and use it to build the appropriate SQL. Thus, by simply changing the type of data source item you add to the data sources collection, you get the appropriate SQL for that data source. Again the BO doesn't care, as long as the table is named the same in both db and the fields are named the same.



However, when you write Fill methods or other methods that end up querying the database, you typically will write SQL that is executed via a command object. The command object is provider specific (i.e. there is a sql command object and an oracle command object) and the SQL itself is specific to the provider (PL SQL vs T-SQL). This is all code you write yourself. So, as I said, the challenge will be to handle this aspect yourself.



I mentioned the QueryInformation object, as I believe this is used to generate the appropriate SQL based on some generic information. I.e. I think a data source item can take a QueryInformation object and then build the appropriate SQL for you, based on the data source you are using. I'm sure there are limitations to using this, but it might be useful to make your fill code provider agnostic.



Obviously the SF guys are the ones to clear this up, but as they are obviously busy, I'm just letting you know what I understand. Hopefully, it is helpful.

I just wanted to confirm on the complexity or if I was missing something. IE... when Strataframe generates the BO, if you initially select SQL Server, then the code generated is for SQL Server. It isn't as though the BO is generated in a way to handle both SQL and Oracle. Just needed to confirm this. Am I correct?

I think this is bit more complicated. First, using what Bill indicated, you need to make sure that the oracle data source is using the OracleDataSourceItem, not the SqlDataSourceItem (also check the connection string...I doubt the Oracle connection string looks that simular to the MS SQL server connection string):







However, you'll also have to handle your Fill/Data retrieval functions differently, as the SQL dialects used between MS SQL server and Oracle differ. If you handle that well, then you just switch data sources and you're good to go (I think..).



I'd look into the QueryInformation object. I believe this is used when you need to handle different types of SQL dialects and is what SF uses to generate SQL based on the type of data source item (i.e. SqlDataSourceItem vs. OracleDataSourceItem).



Hopefully one of the SF guys will chime in here...

Here's what I would do in the SetDataSources of the program.cs (the VB equivalent escapes me at the moment):

DataLayer.DataSources.Add(new SqlDataSourceItem("MySQLConn", "Data Source=MySQLServer;Initial Catalog=VALETDB;User ID=myuserid;Password=mypassword"));

DataLayer.DataSources.Add(new SqlDataSourceItem("MyOracleConn", "Data Source=MyOracleServer;Initial Catalog=VALETDB;User ID=myuserid;Password=mypassword"));

I have absolutely no clue how to write a connection string for an Oracle database, but this should give you an idea.  After these are set, then, based on some predefined event or condition, you would set the BO datasourcekey:

MyBO.DataSourceKey = "MyOracleConn";

MyBO.Fill();

You are off and running.  I think.

I haven't actually tried this, but, in theory, it sounds good.  Hope it helps.
Bill