Beginning Oracle in ASP.NET
So we have started a new project that requires Oracle instead of SQL Server. As to save some people some hassle, let me enlighten you on things that took me hours to figure out.
First off: Everything must be in quotes, this isn't too bad with formviews and gridviews since you can surround the entire statement with single quotes, but you still have to put them around every table and column, EX: "Table"."Column". If you are doing a statement from the code-behind, you have to use \" to cancel out the end of the string. This is, of course, only a helpful tip if you are not using Oracle's version of stored procedures, which will most likely be in another topic.
Second: The dreaded "ORA-00972: Identifier is too long" error. Every time I've gotten this error it has been due to missing quotes, so double check your quotes around the tables and columns.
Third: What??? No Boolean or Bit????? Yes it's true; Oracle has no database value for a boolean. Now, in most cases, you can just write a simple conversion function and that is the end of it. In my case I had to do a 2-way Bind within a gridview. This took a good 5 hours, including researching (also known as googling) for 3 1/2 (which basically came to the conclusion that it wasn't possible) and then my boss came up with a brilliant idea which I will pass on to readers.
Create a new .ascx file and put one checkbox in it with an ID (I chose, cbCheckBox) and then throw in this code behind:
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
public partial class OracleCheckBox : System.Web.UI.UserControl
{
public object Checked
{
get
{
if (cbCheckBox.Checked)
return 1;
else
return 0;
}
set
{
if (value.ToString() == "0")
cbCheckBox.Checked = false;
else
cbCheckBox.Checked = true;
}
}
}
Bind to the ascx's Checked and you are good to go. Make sure you use the Number datatype and not char on the Oracle end.
That's all for now, but I'm sure the more I work with Oracle the more tips I will be passing on. Cheers.
Return to Blog Index
Jeremy Blanchette