Tuesday, August 17, 2010

LINQ to SQL – Generic Insert

Currently I’m working on a project’s data layer code. I’ve chosen LINQ to SQL for this purpose and created a helper library to perform CRUD operations in an easy and generic way.

Following is the code which insert any database object using LINQ:

   1: public static void Insert<T>(T entityData) where T : class
   2: {
   3:     using (TransactionScope trans = new TransactionScope())
   4:     {
   5:         using (App_Data.DBDataContext db = new App_Data.DBDataContext())
   6:         {
   7:             db.GetTable<T>().InsertOnSubmit(entityData);
   8:             db.SubmitChanges();
   9:             trans.Complete();
  10:         }
  11:     }
  12: }

Technorati Tags:

Few noteworthy points:

  • I’m using transaction scope but we can omit that, because for single update or insert, LINQ already provides transaction support. This is only required while making multiple inserts or updates
  • Conditional generics are used because GetTable<T>() requires T to be a reference type
  • Just like above method, update and delete can also be implemented

Usage

  • Suppose you have a database table Order
  • In LINQ to SQL, this table is represented as a class, in which table columns are represented as properties
  • To insert a new record for Order
    • Create new object of Order – Order orderNewObject = new Order();
    • Fill orderNewObject properties with desired values
    • Pass this new object to Insert method like this - Insert<Order>(orderNewObject);

Using above method we can easily perform database operation without even writing a single T-SQL statement.

Cheers!

Wednesday, August 11, 2010

SQL Server - NULL

There is a bit of inconsistency in the way SQL Server treats (read it as implements) NULLs. Following are some noteworthy points related to implementation and usage of NULL values in SQL Server:

  • 3 possible values of logical expression
    • TRUE
    • FALSE
    • UNKNOWN
  • UNKNOWN occurs when a logical expression involves NULL
    • NULL > 2 is UNKNOWN
    • NULL = NULL is UNKNOWN
    • X + NULL is UNKNOWN
    • (NOT (UNKNOWN)) IS UNKNOWN
  • Query filters (ON, WHERE, HAVING) treat UNKNOWN as FALSE
  • CHECK constraint treat UNKNOWN as TRUE
  • During comparison NULL = NULL is treated as not equal
  • UNIQUE constraint, UNION, EXCEPT, SORTING and GROUPING treats NULLs as equal

This information is quite handy when you are writing TSQL queries targeting data with NULL values.

Cheers!
Currently Playing – Not Afraid by Eminem

Technorati Tags: ,,

del.icio.us Tags: ,,