I’m currently doing some development work using ASP.NET against an Oracle database.  I have to say that I’ve had more frustrating development experiences, but most of those involved mainframe computers or…oh yes….Visual BASIC 6.0 against Oracle.  Just what is it about Oracle and Microsoft?  Gah!

Anyway – rant over.  In this piece I’d like to share a few useful tips for developing with ASP.NET and Oracle if you’re used to developing with ASP.NET and SQL Server.  There’s nothing magic here, and I’m no expert, but hopefully these pointers might assist anyone else in the position that I’ve found myself in!

Identity Fields

One thing that looks missing from Oracle in the first instance is the ‘Identity’ field that is often used a Primary key field in SQL Server.  It IS possible to implement this in Oracle – one has to use what’s called a ‘Sequence’ and either include a trigger on the ID field of the table to give you the sequence number added automatically or remember to add it via the INSERT command:

CREATE SEQUENCE table_seq
    MINVALUE 1
    MAXVALUE 999999999999999999999999999
    START WITH 1
    INCREMENT BY 1
    CACHE 20;

This generates a sequence called table_seq, starting at 1, incrementing by 1 each time, and going up to a VERY large number!  The CACHE 20 line tells Oracle to generate a cache of 20 values from the sequence.  To use this sequence after creation, you can access it via an INSERT command as follows:

INSERT INTO datatable
(id, name)
VALUES
(table_seq.nextval, 'Joe Pritchard');

the ‘id’ field is the PK field of the table, and the table_seq.nextval gets the next value from the sequence.  To create a truly ‘auto incrementing’ PK field, you create a trigger on the table:

create trigger datatable_trigger
before insert on datatable
for each row
begin
select table_seq.nextval into :new.id from dual;
end;

Run this and then you can add a new row to the table without specifying the id field:

INSERT INTO datatable
(name)
VALUES
( 'Joe Pritchard');

Boolean Fields

Oracle doesn’t support them.  the best approach I’ve found is to have an integer field and treat 0 as false and 1 as true.  This then works well with ASP.NET checkboxes.  For example:

<asp:TemplateField HeaderText=”Is Admin.” SortExpression=”IsAdministrator” > 

<ItemTemplate > <asp:CheckBox runat=”server” ID=”IsAdministrator” Text=’<%# Bind(“IsAdministrator”) %>‘ Checked=’<%# Bind(“IsAdministrator”) %>‘ />  

</ItemTemplate>
 </asp:TemplateField>

Don’t forget the provider Name

When setting up a SQLDataSource control, don’t forget to specify the provide Name in the ConnectionString.  If you do, the error message obtained is not exactly meaningful at first glance, referring as it does to Unicode!  

<asp:SqlDataSource ID=”SqlDataSource1″ runat=”server” ConnectionString=”<%$ ConnectionStrings:ConnectionString %>“ ProviderName=”<%$ ConnectionStrings:ConnectionString.ProviderName %>“

This also requires you to specify the Provider in the ConnectionString:

 < add name=“ConnectionString“ connectionString=“Data Source=www.myserver.co.uk;User ID=jp;Password=test;Unicode=True“ providerName=“System.Data.OracleClient“/>

Watch table and field name lengths

This can be extremely frustrating.  And I mean extremely!  If you are likely to find yourself explicitly specifying the table name and the field name in a SELECT statement, for example, then the combained length MUST NOT exceed 30 character (this includes the ‘.’ separating table and field – so keep table and field names as short as is practicable.

Quote marks around table and field names

When putting SQL statements together for use by SQLDataSource or other ASP.NET controls that use the OracleClient provider, don’t forget to surround the Oracle field and table names with quotation marks:

SELECT "id", "name" FROM "names"

or

SELECT "names"."id", "names"."name" FROM "names"

Parameter Handling

If you are using Parameters with a SQLDataSource control, don’t forget that the OracleClient uses a colon instead of the ‘@’ sign:

DeleteCommand=’DELETE FROM “moad_agrippa_users” WHERE “UserID” = :UserID’

The other thing to note is that the parameter does not require quotation marks around it.

I hope this piece has been useful – it will act as an aide-memoire for me the next time I come back to work on Oracle / ASP.NET sites!