Monthly Archives: June 2012

Using Oracle SQL LIKE operator in parameterized OracleCommand

The SQL LIKE operator can be used in the CommandText with Oracle parameters. The syntax of comparing a OracleParameter using SQL LIKE operator is as follows:

Dim cmd As New OracleCommand
cmd.CommandText = "SELECT t.* FROM table t WHERE t.column LIKE :param || '%'"
cmd.CommandText = "SELECT t.* FROM table t WHERE t.column LIKE '%' || :param"
cmd.CommandText = "SELECT t.* FROM table t WHERE t.column LIKE '%' || :param || '%'"

Reference:Usage of Oracle binding variables with LIKE in C#

Leave a comment

Posted by on June 29, 2012 in .NET, ADO.NET, Oracle, SQL, VB


NHibernate & ORA-12571

When I was querying against Oracle database using NHibernate, I ran into Nhibernate.Exceptions.GenericADOException:{“could not update: … The InnerException of it was ORA-12571: TNS:packet writer failure. Resources on the net state that ORA-12571 happens generally when there is a network failure. But in my case it always happens on certain querys. Queries without string type parameters work normally. Turns out it is a Unicode to ASCII conversion problem. Adding a type attribute for string properties in the mapping file as follow can solve it:

<property name="SomePropertyName"  type="AnsiString" ></property>

The exception message is really misleading.


NHibernate and The Case of the Crappy Oracle Error Message

NHibernate and ORA-12571 Errors

Failed to UPDATE or INSERT using NHibernate

Leave a comment

Posted by on June 26, 2012 in .NET, NHibernate, ORM


NHibernate configuration for Oracle

Our team developing environment is a bit outdated. We’re using vs2005 with Oracle 8i client. The data provider is Microsoft’s System.Data.OracleClient. Here is a simple workable NHibernate configuration. For those applications developed using ODP.NET, simply replace the connection.driver_class setting like the sample below.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
    <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
    <property name="dialect">NHibernate.Dialect.Oracle8iDialect</property>
    <!--Using ADO.NET System.Data.OracleClient driver-->
    <property name="connection.driver_class">NHibernate.Driver.OracleClientDriver</property>
    <!--Using ODP.NET driver-->
    <!--<property name="connection.driver_class">NHibernate.Driver.OracleDataClientDriver</property>-->
    <property name="connection.connection_string">Data Source=XXXX;User ID=XXXX;Password=XXXX;Unicode=True</property>
    <property name="show_sql">true</property>

Reference:Nhibernate with Microsoft System.Data.OracleClient

Leave a comment

Posted by on June 22, 2012 in .NET, ADO.NET, NHibernate, Oracle, ORM


Highlight specific text in RichTextBox

TextBox is not capable of displaying text with multiple colors. We need to use RichTextBox in order to highlight specific text. The demo code here uses .NET Regular Expression to find the text and highlight them with red color.

Include this reference to use Regex class.

Imports System.Text.RegularExpressions

Highlight 8-digit numbers in the RichTextBox.

Dim regex As New Regex("[0-9]{8}")

For Each matchedText As Match In regex.Matches(Me.RichTextBoxApprovedVoucher.Text)
    Me.RichTextBox1.Select(matchedText.Index, matchedText.Length)
    Me.RichTextBox1.SelectionColor = Color.Red
Leave a comment

Posted by on June 18, 2012 in .NET, VB, Winform


Reset AutoIncrement column in DataTable

I have a auto-numbering column “AUTONUMBER” in a DataTable which is only used for showing the query result count, it is not related to any column in the database table. The AutoIncrementSeed and AutoIncrementStep properties of the column were set to 1. Before each query the DataTable.Clear() is called. I expected after every query the column would have started at 1 but instead it was incremented from the last number. The solution is simply add 4 lines after DataTable.Clear(). I don’t know exactly what happens under these code though…

'Add these 4 lines will reset the AutoIncrement column
dataTable.Columns("AUTONUMBER").AutoIncrementStep = -1
dataTable.Columns("AUTONUMBER").AutoIncrementSeed = -1
dataTable.Columns("AUTONUMBER").AutoIncrementStep = 1
dataTable.Columns("AUTONUMBER").AutoIncrementSeed = 1

Reference:Reset AutoIncrement in DataTable

1 Comment

Posted by on June 8, 2012 in .NET, ADO.NET, VB


GroupBox Validating event isn’t firing on ValidateChildren call

The Validating event code for GroupBoxes below checks if at lease 1 CheckBoxes checked in the group. It works with Form.AutoValidate enabled. But when Form.ValidateChildren() is called, this validating event isn’t firing.

' Validate if at least 1 of the CheckBoxes in the GroupBox is checked
Private Sub GroupBox_Validating(ByVal sender As System.Object, _
                                ByVal e As System.ComponentModel.CancelEventArgs) _
    Handles GroupBoxStatus.Validating, GroupBoxClass.Validating

    Dim isValid As Boolean = False
    For Each ctrl As Control In DirectCast(sender, Control).Controls
        If TypeOf ctrl Is CheckBox Then
            If DirectCast(ctrl, CheckBox).Checked Then
                isValid = True
                Exit For
            End If
        End If

    If isValid = False Then
        Me.ErrorProvider1.SetError(DirectCast(sender, Control), "Check at least one item")
        e.Cancel = True
        Me.ErrorProvider1.SetError(DirectCast(sender, Control), "")
        e.Cancel = False
    End If

End Sub

Read the rest of this entry »

Leave a comment

Posted by on June 7, 2012 in .NET, VB, Winform


Service Locator Pattern/Anti-Pattern?

I was thinking of writing some code on Service Locator pattern until recently I notice loads of resources on the internet state that it is an anti-pattern. Service Locator seems appealing when I first saw it implemented with Unity DI container in Project Silk, a Patterns & Practices article by Microsoft. But the idea of having an global variable like object resolving dependencies rather than just using a DI container bothers me a lot. After reading these arguments, I decided to avoid using Service Locator pattern in MVC.

Here is a thorough introduction of Service Locator and DI by Martin Fowler :
Inversion of Control Containers and the Dependency Injection pattern

These articles point out the cons of Service Locator:
Is IDependencyResolver an anti-pattern?
IoC.Resolve vs Constructor Injection
Service Locator is an Anti-Pattern by Mark Seemann

The purpose of IDependencyResolver in MVC 3 and 4 seems to be supporting legacy codes:
Why is MVC4 using the Service Locator Anti-Pattern
ASP.NET MVC 3 Service Location, Part 5: IDependencyResolver

Read the rest of this entry »

Leave a comment

Posted by on June 1, 2012 in .NET, ASP.NET, Design Pattern, MVC3