RSS

Monthly Archives: May 2012

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 »

Advertisements
 
Leave a comment

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

 

Tags: , , , , , ,

Oracle SQL syntax : CASE WHEN

A short example of the switch/case statement in Oracle SQL:
Assume there’s a table “Products” which has 3 columns “ProductName”, “Price” and “ProductDescription”.

SELECT CASE
         WHEN Price > '1000' THEN
          'Expensive'
         WHEN Price <= '1000' THEN
          'Cheap'
         ELSE
          ProductDescription
       END Price
  FROM Products
 WHERE ProductName = 'Keyboard'
 
Leave a comment

Posted by on May 18, 2012 in Oracle, SQL

 

Tags: , , , ,

Standard numeric format string quick reference

The look-up table below is implemented by ToString method with “en-US” CultureInfo. More detailed examples could be found at Standard Numeric Format Strings Output Examples.

Format
Specifier
Format
Name
Format
String
Output
(int 1234)
Output
(double 12345.6789)
C or c Currency C $1,234.00 $12,345.68
D or d Decimal D 1234 Format specifier
was invalid.
E or e Scientific E 1.234000E+003 1.234568E+004
F or f Fixed-point F 1234.00 12345.68
G or g General G 1234 12345.6789
N or n Number N 1,234.00 12,345.68
P or p Percentage P 123,400.00 % 1,234,567.89 %
R or r Round-trip R Format specifier
was invalid.
12345.6789
X or x Hexadecimal X 4D2 Format specifier
was invalid.

Read the rest of this entry »

 
Leave a comment

Posted by on May 16, 2012 in .NET, C#

 

Tags: , , ,

Not to select the first row in DataGridView by default

DataGridView makes the first row selected after DataBinding by default. I tried using DataGridView.ClearSelection() in the Form.Load event to unselect it with no avail. The problem is that data hasn’t been bond to the DataGridView in the Form.Load event. The method should be placed in the DataBindingComplete event of the DataGridView:

Private Sub DataGridView1_DataBindingComplete(ByVal sender As System.Object, _
    ByVal e As System.Windows.Forms.DataGridViewBindingCompleteEventArgs) _
    Handles DataGridView1.DataBindingComplete

    Dim gridView As DataGridView
    gridView = CType(sender, DataGridView)
    gridView.ClearSelection()
End Sub

Reference: C# DataGridView: First row is being selected by default

 
11 Comments

Posted by on May 14, 2012 in .NET, VB, Winform

 

Tags:

Insert delimiters between each character of a string

For example, to make the string “abcde” becomes “a,b,c,d,e” or “a b c d e”. What I’m going to do is to convert the string to a char array and then use String.Join to join the chars with the specific delimiters. There are different ways to accomplish it base on different versions of .NET Framework.

.NET 2.0
First using String.ToCharArray to convert the original string into a char array. Since String.Join takes a string array as the 2nd parameter, the char array needs to be converted into a string array. In .NET 2.0 we could use Array.ConverAll:

Read the rest of this entry »

 
Leave a comment

Posted by on May 9, 2012 in .NET, Lambda, LINQ, VB

 

Tags:

Oracle IN clause and NULL

Suppose in Oracle DB there is a table called MyTable which has a column called MyColumn. The value in MyColumn could be ‘A’, ‘B’, ‘C’, ‘D’, ‘E’ and NULL. I want to get the records where the value of MyColumn is not ‘A’ or ‘B’. Here is an intuitive query SQL:

SELECT t.* FROM MyTable WHERE MyColumn NOT IN ('A', 'B')

I thought it would return records with values ‘C’, ‘D’, ‘E’ and NULL in MyColumn. But it doesn’t return the results as I expected. I won’t be able to get the results where the value of MyColumn is NULL since a NULL can not be equated with anything. The query needs to be modified like this:

SELECT t.* FROM MyTable WHERE MyColumn NOT IN ('A', 'B') OR MyColumn IS NULL
 
Leave a comment

Posted by on May 7, 2012 in Oracle, SQL

 

Read from text file with multiple line formats using TextFieldParser

When using TextFieldParser to read fixed field width text file with optional fields, MalformedLineExceptions could happen. It happens because the imported line length is shorter than expected.

For example:

reader.SetFieldWidths(5, 8, -1)

' Exception will be thrown when the line is only 12 characters long or less
' because the 3rd field will be regarded as missing

We could prevent the exception by determining which format to use according to the line length.
Solution:

Read the rest of this entry »

 
Leave a comment

Posted by on May 4, 2012 in .NET, VB

 

Tags: