Thursday 25 December 2008

ORA-12154 when using EF Oracle Provider

When using EFOracleProvider, ORA-12154 might occur if there are 2 Oracle clients installed on your machine. The problem is that EFOracleProvider is looking for for tnsnames.ora in client_2 folder instead of client_1. The workaround is copy tnsnames.ora to client_2 folder as well.

Thursday 11 December 2008

Find stored procedures/functions that contain some text in SQL Server

DECLARE @text AS nvarchar(4000)
SET @text = '%text to find%'
SELECT name, xtype FROM sysobjects o
  INNER JOIN syscomments c ON o.id = c.id
  WHERE c.text like @text


* This source code was highlighted with Source Code Highlighter.

Create CLR function in SQL Server 2005

-- Drops existing aggregate function, remove this line if not needed
DROP AGGREGATE StringConcat
GO
-- Drops existing assembly, remove this line if not needed
DROP ASSEMBLY SqlServerHelper
GO
CREATE ASSEMBLY SqlServerHelper from '\\AssemblyPath\SqlServerHelper.dll'
WITH PERMISSION_SET = SAFE
GO
EXEC sp_configure 'show advanced options' , '1';
go
reconfigure;
go
EXEC sp_configure 'clr enabled' , '1'
go
reconfigure;

CREATE AGGREGATE StringConcat (@value varchar(4000)) RETURNS nvarchar(4000)
EXTERNAL NAME SqlServerHelper.Concatenate

-- Sample execution of CRL aggregate method
select top 10 dbo.StringConcat(ExternalCode)
from decodeValues group by DirectionCode

* This source code was highlighted with Source Code Highlighter.

Find foreign key of table in SQL Server

DECLARE @ObjectName varchar(50)
SET @ObjectName = 'Customers'

SELECT f.name AS ForeignKey,
  OBJECT_NAME(f.parent_object_id) AS TableName,
  COL_NAME(fc.parent_object_id,
  fc.parent_column_id) AS ColumnName,
  OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
  COL_NAME(fc.referenced_object_id,
  fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
  ON f.OBJECT_ID = fc.constraint_object_id
WHERE OBJECT_NAME(f.referenced_object_id) = @ObjectName


* This source code was highlighted with Source Code Highlighter.

Tuesday 28 October 2008

Non-recursive implementation of Euclidean algorithm Greatest Common Divisor (C#)

We're familiar with Euclid's algorithm for finding greatest common divisor since from school, and implemented it for sure on basic programming classes. The below method does actually the same, but eliminates recursion (which is a bad thing) and does not uses any additional variables (which is a good thing). The function may look non-readable due to some tricks, but I hope the explanation below makes it clearer.

  1. uint NonRecursiveGCD(uint a, uint b)
  2. {
  3.   if (a < b)
  4.   {
  5.     a += b;
  6.     b = a - b;
  7.     a -= b;
  8.   }
  9.       
  10.   if (b == 0) return a;
  11.  
  12.   while ( a % b != 0 )
  13.   {
  14.     a += b;
  15.     b = a - b;
  16.     a -= b;
  17.     b %= a;
  18.   }
  19.   return b;
  20. }
* This source code was highlighted with Source Code Highlighter.


Lines 3-8 ensure that "a" is bigger than "b" and exchange them otherwise using a well known trick for exchanging variables without additional memory.

Line 10 takes care for case GCD(a,0), and returns "a" as the GCD.

The "while" loop in lines 12-18 actually implements the Euclid's algorithm by finding remainder of "a" and "b", then puts "b" into "a" and the remainder into "b".

Lines 14-16 use again the "swap" trick for exchanging variables without additional memory usage.

Line 19 returns the last remainder, which is also the GCD to the caller.

Any comments and suggestions are welcome.

Thursday 17 July 2008

NUnit Serialization Test

NUnit test that verifies that your class is fully serializable and desializable:

[Test]
public void SerializationTest()
{
 MyGenericCollection myCollection = new MyGenericCollection();
 SerializationHelper.SerializeNow(myCollection);
 myCollection = (MyGenericCollection)SerializationHelper.DeSerializeNow();
}


internal class SerializationHelper
{
  private static readonly string DefaultFilePath = "test.dat";

  internal static void SerializeNow(object c)
  {
    SerializeNow(c, DefaultFilePath);
  }

  internal static void SerializeNow(object c, string filepath)
  {
   FileInfo f = new FileInfo(filepath);
   using (Stream s = f.Open(FileMode.Create))
   {
      BinaryFormatter b = new BinaryFormatter();
    b.Serialize(s, c);
   }
  }

  internal static object DeSerializeNow()
  {
    return DeSerializeNow(DefaultFilePath);
  }

  internal static object DeSerializeNow(string filepath)
  {
    FileInfo f = new FileInfo(filepath);
    using (Stream s = f.Open(FileMode.Open))
    {
      BinaryFormatter b = new BinaryFormatter();
      return b.Deserialize(s);
    }
  }
}

"The constructor to deserialize an object of type 'MyGenericCollection' was not found."

This exception is thrown by an application that tries to deserialize an instance of MyGenericCollection. In my case it was thrown by BizTalk that stores current state of it variables by their serialization and saving into its database. Then it restores the previous state by deserialization.
MyGenericCollection is a class that derives from Dictionary<Key,value>, which is serializable.

So why the deserialization failed?
Because the deserialization is done within constructor which accepts SerializationInfo and StreamingContext, but constructors are not derived. It means that we need to to define such constructor:

public class MyGenericCollection : Dictionary<Key,Value>
{
protected MyGenericCollection(SerializationInfo info, StreamingContext context)
: base(info, context) { }
}