SQL

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 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().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() 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(orderNewObject);

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

Cheers!

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

GO – SQL Server

While working on SQL Server we all encounter GO statement quite often. Till today GO statement for me was just an indication sent out to SQL Server utilities about end of a batch of Transact – SQL.

Today while going through MSDN I found a new (surprising) thing about GO. Go can actually take an integer argument. Yes! it is true. Go takes an optional integer argument. Something like this,

GO [count]

As per MSDN,

count is a positive integer. The batch preceding GO will execute the specified number of times.

What this means is, suppose you have a script like the one below,

INSERT INTO TestGO VALUES (10, 'testing go')
GO 5000

Here, INSERT statement will be executed 5000 times and you will end up inserting 5000 records in table.

~eNjOy LiFe~
Currently Listening to: In the summer time – SHAGGY