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!
I just came across this on my Twitter feed – a reference to a ” ‘Future of the web’ Turtle” at Open 09. Yup – a turtle. After some Googling about and learning more than I ever wanted to know about our green, aquatic co-travellers on Planet earth, I eventually went to 
Many moons ago I posted a piece on here –
One of my interests is in GIS systems – Geographical Information Systems – and other aspects of computerised and online mapping. Thanks to Googlemaps, it’s been possible for developers to create map-driven applications for nothing – Google allows access to their mapping infrastructure free for many applications, and it’s brilliant. To anyone who hasn’t taken a look or had a play, have a look at 
Well, both 
Here’s a quick hint for anyone who’s had the ‘can’t access empty property’ or ‘unknown property’ error in a WordPress Plugin (or the core WordPress installation) , here’s a quick fix to try before starting to tear your hair out. The error manifests itself with messages such as ‘Cannot access empty property in … ‘ or some similar wording. 
I’ve just upgraded various blogs I look after – including my own – to WordPress 2.8.5.