OracleDataProvider.GetDbTypeOracle returns incorrect value

[FrostyZoob]

I have come across an issue where SQL generated by SubSonic was performing very slowly, which was unexpected. I would run the generated SQL directly in Oracle and the same SQL would run much, much quicker. What gives? Well, after much debugging it turns out SubSonic was specifying an incorrect data type for bind variables. More specifically, it was telling my DB that certain columns were DbType.String (Unicode) when in fact they were not - they should have been marked as DbType.AnsiString (the columns are defined as VARCHAR2). Though the difference is seemingly minor, it was enough to cause Oracle to ignore the indicies which existed on my tables.

Example:

Say I have the following table in my schema:

CREATE TABLE suppliers
(
    supplier_id     number(10)      not null,
    supplier_name   varchar2(50)    not null
);

And I create an index on SUPPLIER_NAME:

create index IDX_SUPPLIERS_SUPPLIER_NAME on SUPPLIERS (SUPPLIER_NAME);

SubCommander would generate the following code in the GetTableSchema() method of the Suppliers class that it generates:

TableSchema.TableColumn colvarSupplierId = new TableSchema.TableColumn(schema);
colvarSupplierId.ColumnName = "SUPPLIER_ID";
colvarSupplierId.DataType = DbType.Decimal;
colvarSupplierId.MaxLength = 0;
colvarSupplierId.AutoIncrement = false;
colvarSupplierId.IsNullable = false;
colvarSupplierId.IsPrimaryKey = false;
colvarSupplierId.IsForeignKey = false;
colvarSupplierId.IsReadOnly = false;
colvarSupplierId.DefaultSetting = @"";
colvarSupplierId.ForeignKeyTableName = "";
schema.Columns.Add(colvarSupplierId);

TableSchema.TableColumn colvarSupplierName = new TableSchema.TableColumn(schema);
colvarSupplierName.ColumnName = "SUPPLIER_NAME";
colvarSupplierName.DataType = DbType.String; <<< PROBLEM!!
colvarSupplierName.MaxLength = 50;
colvarSupplierName.AutoIncrement = false;
colvarSupplierName.IsNullable = false;
colvarSupplierName.IsPrimaryKey = false;
colvarSupplierName.IsForeignKey = false;
colvarSupplierName.IsReadOnly = false;
colvarSupplierName.DefaultSetting = @"";
colvarSupplierName.ForeignKeyTableName = "";
schema.Columns.Add(colvarSupplierName);

Because the datatype is incorrect for the colvarSupplierName TableColumn, Oracle will ignore and not use the previously created index. As a result: When you're dealing with large sets of data (~500k rows, in my case) performance will suffer significantly.

After tracing the code, the problem seems to lie in OracleDataProvider.GetDbTypeOracle:

    public static DbType GetDbTypeOracle(string sqlType, int dataScale, int dataPrecision)
    {
        switch(sqlType)
        {
            case "varchar2":
            case "varchar":
            case "char":
            case "nchar":
            case "nvarchar2":
            case "rowid":
                return DbType.String;
            case "nclob":
            case "clob":
                return DbType.AnsiString;
            case "number":
            .
            .
            .

SubCommander calls the above method to determine which DataType to insert into generated code. Fixing the above should fix the problem.

Oracles documentation is a bit confusing, but if I understand this documentation correctly (http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm#i3253): CHAR, VARCHAR and VARCHAR2 types would be mapped to the DbType.AnsiString since they are "variable-length character strings" as opposed to NCHAR and NVARCHAR2 which "are Unicode datatypes that store Unicode character data.". The confusion comes from the fact that you can define the size of CHAR, VARCHAR and VARCHAR2 in bytes OR characters and that "Character semantics are useful for defining varying-width multibyte strings;". So...VARCHAR2 can hold a unicode string? Like I said: confusing.

[6pac]

I have run across a few typing issues as well and fixed them in my private copy.
I can't help asking, why are you running 2.x ? AFAIK, 3.x is a whole lot more mature. I'm only still interested because I wrote an MS Access provider for SS 2.0 about 3 years ago and I want to finalise that for 2.x before moving on to 3.x.

[FrostyZoob]

I can't help asking, why are you running 2.x ?

I would love to be using 3.x, but the company I work for doesn't have a build environment set up for .NET 3.5 (yet) so all the devs are stuck using VS2005 and .NET 2.0. Hopefully that'll change soon.

Edit: Also, this is the first time I've contributed to an open-source project. I feel a lot more comfortable contributing to something...less visible? So if I screw up, it's not as humiliating :)

[FrostyZoob]

I pushed a fix for this into my branch. I'm ready for a build whenever you are.