Problem with tableadapters, directdbMethods and parametertyp

phlorryn · Post by **phlorryn** » Wed 03 Mar 2010 12:21

Hi

We are currently in trial for dotConnect for Oracle, and we consider buying licences and move existing projects to new driver, from system.data.oracleclient. The main reason is performance, that is better, from our tests.

We are using strongtyped datasets and table adapters, and only in 1.5 days of testing your product, we already found problems that are not allowing us to continue with the new oracle connector.

I will list here two that are the most important, in order to know if there is maybe some config that is needed, or some workaround.

1. The numeric oracle fields (from db) are mapped in any new datatable we create with your adapter with int32, decimal, double by the case. The problem is that, for any new fillby method we add to tableadapter, all numeric parameters are created as decimal. We have some issues with that:
a. migrating existing datasets is dificult, because in entire BLL code, we used all strongtyped fields as decimals (as they were created by microsoft oracle client), and now we need to refactor entire code to match the new column types.
b. manually changing the column types to decimal is out of the question, since further management of the dataset schema (on schema evolution) is impossible. (changing the select string, to add another column for example, will duplicate all columns in model, because the existing one don't match the database type, but also, the dataset generator is not deleting them) - from this point, the dataset class is borken, and cannot be used any longer, withour redesigning from scratch.
c. A fillbycolumn method, will need decimal parameters all the time for any numeric field. But this will require additional work to convert parameters. Let's say that we have a table with locationId primary key numeric(4). The table generated in the datamodel, will have locationId int32 in this case. If we add a querry in the dataset designer for fillbylocationId, this method will require decimal, and we will not be able to use something like fillbyloactionId(model.locationRow.locationId) for a child table, and we need to manually make a conversion to decimal, and this is out of the question.

2. DirectDbAccessMethods (insert, update, delete) for table adapters are generated only in the first time, when you are initialy dragging the table from the server explorer, and only with "Optimistic concurency" on. If you put "optimistic concurency" off, the methods are not generated anylonger. And anyway, after this change, even if you put optimistic back, the methods for directdbaccess are not beeing generated anylonger. Optimistic concurency is also out of the question because the system is highly dynamic.

These issues are realy important to fix, and without some solution, we cannot proceed with dotConnect, even if it has best performance.

Thank you for support.

Florin

phlorryn · Post by **phlorryn** » Wed 03 Mar 2010 12:34

And another thing discovered few minutes ago:

if you have a table with a column beeing part from pk(multiple) and in the same time part of some unique constraint(multiple), in the model generated will be part only in one constraint, and in some situations, the pk is not even generated.

example:
userid = number(6)
locationId = number(3)
useraccount = varchar(50)

uk - locationid, useraccount
pk - userid,locationId

the keys will be wrongly generated in visual studio.

StanislavK · Post by **StanislavK** » Thu 04 Mar 2010 14:53

Using dotConnect for Oracle, you can generate typed DataSets of two kinds:
1. Devart DataSets, which can be created by Devart DataSet Wizard (Tools -> Oracle -> DataSet Wizard);
2. MS DataSets, which can be created, e.g., by dropping database objects from Server Explorer to DataSet Designer.

As I can understand, you are using the second type. Am I correct?

1. As for existing DataSets generated using OracleClient, please use Migration Wizard (Tools -> Oracle -> Migration Wizard) to use them with dotConnect for Oracle.

2. The problem you are encountering with parameters is that Oracle server ignores precision and scale properties of Number parameters. Hence, they should be always mapped to decimal. If you are sure about the type that will be passed, you can change the parameter type after a new FillBy method was added:

- in DataSet Designer, select the Properties grid of the corresponding method;
- open the Parameters collection;
- select the needed type in the DbType listbox.

3. We reproduced the problems with DirectDbAccessMethods and primary key generation. We will investigate them and notify you about the results.

phlorryn · Post by **phlorryn** » Fri 05 Mar 2010 08:19

Hello

First, thank you for your answers.

1. You are right, we are using msdatasetgenerator. We will try with DevArt wizard to see the results, and also migration tool you indicated.

2. About parameters - yes they are mapped as decimal - but the msdatagenerator using DevArt driver, is not doing the same for column mappings when creating table. This was confusing: why for column generation knows the exact type, and for paramters - that are mapped to column conditions, not, and use decimal instead. We will try the DevArt wizard and see the results. About parameter changes, we can do this easily by some code that can change the xml file of the dataset automatically, based on database schema.

3. For the issues with directDbMethods - we will try DevArt wizard to see if with this one, we still have issues.

We will be back with any information or result that we find.

Regards

Florin

phlorryn · Post by **phlorryn** » Fri 05 Mar 2010 10:50

Hi again

Tested the DevArt Wizard, and dataset manager and found some issues, or maybe things that are not documented, and we don't know how to reach solution:
- in wizard - you are not able to add more than one select command, as you would have more than one fill methods, with different parameters. this is a normal use for datasets.
- if you add this ones in dataset designer, the normal one, you also get to exceptions with directdbmethods. Also, if you still do this, and return to dataset manager (or wizard) you still can se only one select method, and after generate, you loose the other ones.
- the columns in datasets are still generated as different types for numeric: int, double etc. Why the same logic cannot apply to parameters that are bound to the same columns, in a parameterized select?
- you are unable to create update/insert/delete without optimistic concurency, even with DevArt wizard. How this one can be achieved - updates/deletes to be generates with conditions only for PK columns.
- DevArt Wizard, still does not recocnize PK columns, if the same columns are involved in another contraints.
- DevArt Wizard does not automatically recocnize FK relations from the database.
- DataSetManager, sometimes, even if using refresh, is not showing all datasets there.
- DataSetWizard - using TableAdapters - is not generating TableAdapters classes. They are created only in the XML of the dataset, but not in designer class. So, seems impossible to use it with strong typed dataset. Do we need to write all methods manually in the partial class of the dataset? Or, how to use the strong typed table adapters?

Thank you, and sorry for so many questions.

Florin

StanislavK · Post by **StanislavK** » Fri 05 Mar 2010 14:16

Concerning MS DataSets:

- We fixed the problem with generation of DirectDbAccessMethods. The fix will be included into the nearest build.
- We cannot guarantee the proper generation of primary keys in complicated cases. The reason is that we retrieve the table schema via standard Microsoft procedure, and the schema format used by it provides insufficient information on table keys. In the case of intersecting primary and unique keys, we can only recommend to set the keys manually.

As for DataSets generated by Devart DataSet Wizard:

- The problems with additional queries in typed DataSets are the known ones; we plan to fix them. We also plan to add the possibility of creating the TableAdapter classes, but cannot provide any timeframe.
- Oracle server provides precision and scale for number fields of the table, and does not for number parameters. Thus, we map table fields using scale and precision to prevent overhead, and have to map number parameters to decimal as no precision and scale are specified for them by Oracle server.
- As for the optimistic concurrency problem, please wait for the nearest build.
- Concerning DataSet Manager not showing all DataSets, could you please specify in more details the scenario needed to reproduce the problem? We cannot reproduce it at the moment.
- We couldn't reproduce the problem with foreign keys either. Could you please send us the definitions of tables for which the issue occurs?

Please tell us if something is unclear or if you have additional questions.

phlorryn · Post by **phlorryn** » Mon 08 Mar 2010 07:44

For the moment we are done with evaluation. We gathered some reports with benefits versus risks and efforts to move to DevArt, and the percentage of compatibility with our current developement framework. We will analyze the results and take a decision.

Thank you for all your answers and effort to test the issues provided. I hope that the benefits were on both sites.

Florin

StanislavK · Post by **StanislavK** » Wed 31 Mar 2010 16:56

We have fixed the problem with DirectDbAccessMethods generation. The fix is available in the new 5.60.120 build of dotConnect for Oracle, which can be downloaded from
http://www.devart.com/dotconnect/oracle/download.html
(trial version) or from Registered Users' Area (for users with valid subscription only).

For more information, please refer to
http://www.devart.com/forums/viewtopic.php?t=17514 .