Working with Property Proxy Validators

I’ve been using Enterprise Library and Property Proxy Validators to do some validation for the web search form I’m developing. The form has a few fields relevant to a person that are optional to search on, however there is a minimum to search on either Last Name or SSN.

In the RuleSet I setup for the search none of the Validators seem to really give the ability to handle checking for atleast 1 not null. The way I got around this was setup however you want your validators to be for regular data. Make sure that the empty string case can be successful either in your regular expressions or add a String Lenght Validator LowerBound 0 Upperbound 0 inside an OR composite validator along with the rest of your validations.

Currently this setup will give you pass for all nulls, the way I overcame this was to setup a Property Comparison Validator inside an And Composition Validator on my Last Name field having it compare to the SSN field set to NOT Equal. Which gives us a truth table similiar to:

LastName: “Smith” Pass –  SSN: “123%” Pass – Overall Pass

LastName: “” Pass –  SSN: “123%” Pass – Overall Pass

LastName: “Smith” Pass –  SSN: “” Pass – Overall Pass

LastName: “” Pass –  SSN: “” Pass – Overall FAIL which is what we want to happen.

There’s only one slight caveat to doing it this, entering exact same values with real text into the last name field and SSN field will generate the case that we have set for nulls.

LastName “123%” Fail – SSN “123%” Pass – Overall FAIL.

Overall i think this is an acceptable trade off, especially with the vastly different validation types. Now if you were going to use this method to compare First Name and Last Name, you would need to weigh if it would be worth making it impossible to search for a person with the same first name as last name. Of course if the search handles wild cards it could easily be worked around with just an explanation to take off 1 letter.

One last thing I needed to figure out was how to stop the property proxy validators from firing when I hit my reset button because it sets it to an valid state making every field null (not suprising since we just set the fields not accept all nulls). Maybe this more obvious to others or perhaps people that have just worked more using ASP.NET’s page validation but the only thing needed done to fix this was add to my button

<asp:button id=”btnReset” runat=”server” On_Click=”btnReset_Click” CausesValidation=”false” />

BloggingContext.ApplicationInstance.CompleteRequest();

Writting LOBs / CLOBs to oracle using C# ASP.NET 2.0

As a software developer sadly we are still bogged down by data access layers to legacy databases (Oracle 10g for this example). Now I’m sure many of you are questioning my calling oracle 10g a “legacy” system but with LINQ2SQL I am praying for the death of this behemoth forever. Until my day comes to laugh in the face of the oracle “gods” I am unfortunately forced to develop and support this beast.

Now if any of you have ever attempted to store XML or any other large object to oracle using a CLOB or LOB column I am sure you have encountered very high levels of frustration. I finally had the unfortunateness to be the next victim to this. Attempting to use Enterprise Library the way it’s intended to write to oracle with LOBs is just pretty much out of the question. So it comes down to either using the System.Data.OracleClient directly or using Oracle ODP.Net Oracle Client.

Searching for a solution to this problem pointed out very few solutions. The bulk of them require running dynamic sql straight from .NET to oracle. While this may work (I found this solution to only work haphazardly resulting in random oracle errors, highly suprising) many of us that are subjucated by oracle most likey have DBA policies that prevent this method from being at best, frowned upon. Trying to over come the limitation of not being able use oracle parameters to pass in the clob to a stored proc yet still need to avoid running dynamic sql was quite a challenge. I found nothing that specifically outlined my result.

Lets get down to work first lets get some basic assumptions.

Table XML_TBL— GUID— XML_CLOB — Input By/Date

USP_Insert_XML (p_guid in varchar2, p_inputby in varchar2, cur_OUT OUT ref_cursor)

AS

BEGIN

INSERT INTO XML_TBL(  GUID, XML_CLOB, INPUT_BY, INPUT_DATE)

VALUES ( p_guid, EMPTY_CLOB(), p_inputby, systime);

OPEN cur_OUT FOR

SELECT XML_CLOB

FROM XML_TBL

WHERE GUID = p_guid

FOR UPDATE;

END;

So pretty much a standard insert proc, EMPTY_CLOB() is basically just a fancy null since all LOB columns must be initialized during an insert. The magic is going to occur from the fact we are locking this row for update and passing back the reference to this table inside the ref cursor. Now to the .NET code

string connectionString = ConfigurationManager.ConnectionStrings[OracleConnectionString].ConnectionString;
byte[] clobByte = Encoding.Unicode.GetBytes(xmlStringToSave);

using (OracleConnection oracleConnection = new OracleConnection(connectionString))
{
oracleConnection.Open();
OracleTransaction transaction = oracleConnection.BeginTransaction();

OracleCommand oracleCommand = new OracleCommand(“USP_Insert_XML”, oracleConnection, transaction);
oracleCommand.CommandType = CommandType.StoredProcedure;

OracleParameter p1 = new OracleParameter(“p_guid”, OracleType.VarChar);
p1.Direction = ParameterDirection.Input;
p1.Value = guid;

OracleParameter p2 = new OracleParameter(“p_input_by”, OracleType.VarChar);
p2.Direction = ParameterDirection.Input;
p2.Value = “inputBy”;

OracleParameter p3 = new OracleParameter(“cur_OUT”, OracleType.Cursor);

p3.Direction = ParameterDirection.Output;
oracleCommand.Parameters.Add(p1)

oracleCommand.Parameters.Add(p2)

oracleCommand.Parameters.Add(p3)

OracleDataReader odr = oracleCommand.ExecuteReader();
odr.Read();

OracleLob clobColumn = odr.GetOracleLob(0);
clobColumn.Write(clobByte, 0, clobByte.Length);
transaction.Commit();

oracleConnection.Close();
}

So what we have done here is Open a Connection to Oracle. Build a new Transaction, during this transaction we will invoke a stored proc which returns a ref cursor to a table’s clob/Lob data column. Call read on the datareader, next we are going to get clob column from the data reader. In this example since the only column returned is the clob column it’s being accessed directly at column 0.  So make sure to change GetOracleLob(0) to either use GetOrdinal and the column name or put the correct column number in for yourself.

After we get the column we’re going to write to it as a stream (which is why we converted the xmlString to a byte array), call comit to save the xml or lob and close the connection.

I spent about 3 days researching this and could not find a clear example in one place on the interwebs so I hope this helps save you alot of time and headache. So in conclusion it’s possible to write an Oracle clob using only .NET code and stored procedures in this way, keep in mind for the writing to work successfully the oracle app user needs to have update (and possibly insert) access to this table, however they will not need to have select access for this or require the need to run dynamic sql straight from .NET

BloggingContext.ApplicationInstance.CompleteRequest();