Category Archives: ADO.NET

Query using Oracle Parameter returns no result

I had a parameterized query with some string variables binding to the OracleParameters. The query works with certain parameters but doesn’t with others. Take a simplified database table with only 2 columns for example:


Name Type
Currency Char(3)
SerialNo Char(8)

My original query looks something like this. The first query works. The second runs normally without error message but returns no result when there are records matched in the database.

Dim currency As string = "USD"
Dim serialNo As string = "1311B"

'Quering with cmd1 works
Dim cmd1 As New OracleCommand("SELECT * FROM SampleTable WHERE Currency = :Currency")
cmd1.Parameters.Add(New OracleParameter("Currency", currency))

'Quering with cmd2 works but no record is returned when there are matching records in the database
Dim cmd2 As New OracleCommand("SELECT * FROM SampleTable WHERE SerialNo = :SerialNo")
cmd2.Parameters.Add(New OracleParameter("SerialNo", serialNo))

Read the rest of this entry »

Leave a comment

Posted by on July 23, 2012 in .NET, ADO.NET, Database, Oracle, SQL


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 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


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


A workaround to use SQL IN clause in TableAdapters

This article is a simple implementation on how to use parameterized SQL query with IN clause or unknown number of parameters in TableAdapter.

When modifying those ancient database access codes, SQL query strings appear in the form of concatenated string very often. These codes are hard to read, maintain and prone to SQL Injection. Using DataSet Designer can easily improve it by accessing database with parameterized commands. But DataSet Designer does not support automatically generating commands with parameterized IN clause.

The idea of this article is originated from Extending TableAdapters for Dynamic SQL on The Code Project. The article demonstrate a way to change the command text programmatically by extending the TableAdapter which is defined as a partial class from the DataSet Designer. What’s not covered is how to parameterized the command.
Read the rest of this entry »

Leave a comment

Posted by on May 22, 2012 in .NET, ADO.NET, Oracle, SQL, VB


Tags: , , , , , ,

BindingSource filter with ReportViewer

When binding a BindingSource to a ReportViewer after BindingSource.Filter, it doesn’t display filtered data. The ReportViewer still refers to the underlying data source of the BindingSource regardless of any filtering or sorting the BindingSource applies. It seems to be a bug in .NET. A simple walk around is using another BindingSource to be the bridge between the filtered BindingSource and the ReportViewer. Sample code as follows:

originalBindingSource.Filter = someFilterString
Dim tempBindingSource As New BindingSource(originalBindingSource, "")
Me.ReportViewer1.LocalReport.DataSources(0).Value = tempBindingSource
' ------------------------------------
Leave a comment

Posted by on March 1, 2012 in .NET, ADO.NET, VB, Winform