RSS

Monthly Archives: July 2012

Source code generation example using CodeDom

This example demonstrates how to generate source code automatically using .NET framework. It is also intended to serve as a quick reference of common tasks when coding against CodeDom. The example generates a Ford Focus class type in Automobile Namespace and the class inherits Car class. The generated class source code is at the bottom of this article.

// Create a compile unit
CodeCompileUnit compileUnit = new CodeCompileUnit();
// Define a Namespace
CodeNamespace automobileNamespace = new CodeNamespace("Automobile");
// Import Namespaces
automobileNamespace.Imports.Add(new CodeNamespaceImport("System"));

compileUnit.Namespaces.Add(automobileNamespace);

Importing namespaces in this manner puts the using directive in the Automobile namespace scope. Difference
of putting using inside or outside of the namespace is explained at
Should Usings be inside or outside the namespace.
Read the rest of this entry »

Advertisements
 
Leave a comment

Posted by on July 30, 2012 in .NET, C#, Code Generation

 

Tags: ,

Select the first row of each group

This example demonstrates how to select the first records grouping by Salesman and Store.

Sales:

ID Store Salesman Amount
1 A Lin 120.00
2 A Lin 80.50
3 A Fields 200.00
4 B Lin 160.50
5 B Lin 70.00
6 A Fields 130.00
7 B Fields 180.00
8 A Fields 110.50

The result should be like this:

Result:

ID Store Salesman Amount
3 A Fields 200.00
7 B Fields 180.00
1 A Lin 120.00
4 B Lin 160.50

Read the rest of this entry »

 
Leave a comment

Posted by on July 25, 2012 in Database, Oracle, SQL

 

ORA-01790 when Union Null and Date

In Oracle 8i or earlierUnion Null and Date Type column will cause ORA-01790: expression must have same datatype as corresponding expression.

-- This query will fail
SELECT PayDate FROM Table1
UNION
SELECT '' FROM Table2

The solution is using To_Date(NULL)instead of Null.

SELECT PayDate FROM Table1
UNION
SELECT To_Date(NULL) FROM Table2

Reference:NULL in union causing ORA-01790

 
Leave a comment

Posted by on July 25, 2012 in Database, Oracle, SQL

 

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:

SampleTable:

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

 

NHibernate mappings for Composite Keys with associations

This example demonstrates how to work on mappings with associations for legacy database tables designed purely using multiple primary keys/composite keys. The article NHibernate and Composite Keys by Anne Epstein provides a thorough explanation on fundamental  composite keys mapping and supporting lazy loading by declaring the composite keys as a class object. Here we only focus on a simple code sample of the associations between entities with composite keys.

Considering 2 sample tables, Products and Orders:

Products:

Name Type
StoreID NUMBER PK
ProductID NUMBER PK
ProductName VARCHAR

Orders:

Name Type
StoreID NUMBER PK
ProductID NUMBER PK
OrderID NUMBER PK
Amount NUMBER

Read the rest of this entry »

 
Leave a comment

Posted by on July 11, 2012 in .NET, C#, NHibernate, ORM

 

Select values calculated between rows

The example here uses 2 different approaches one using JOIN and another using LAG analytic function. In the table below I want to find the months that A store profiting more than B store. What I need is to first calculate the sum of monthly sales amount of each store and then subtract them. Note this table is only for demonstration hence it’s not normalized.

Sales:

ID Store SaleMonth Amount
1 A Jan 120.00
2 A Feb 80.50
3 A Feb 200.00
4 B Jan 160.50
5 B Feb 70.00
6 A Feb 130.00
7 B Mar 180.00
8 A Mar 110.50

Read the rest of this entry »

 
Leave a comment

Posted by on July 2, 2012 in Database, Oracle, SQL