Get dictionary object from list

When you get dictionary object from a list in C#, make sure that you select distinct values for the dictionary key. Otherwise the compiler will throw run time error if there are duplicates.

lst.GroupBy(x=>x.id).Select(x=>x.First()).ToDictionary(r => r.id, k => k.enabled);

Here is an example of how to select distinct keys from list and to return them in dictionary object.

Lazy initialization in C#

Lazy<T> can be used in C# to delay the initialization of an object. It can be used in singleton architecture as given in the below example.

public static Lazy<MyClass> MyClassInstance = new Lazy<MyClass>(()=> new MyClass());

Using table type and batch update statements in MS SQL

Given below is an example on how to use table types. Table types can be used to pass data array data from code to SQL stored procedure instead of passing the data in CSV or XML format. The example also shows how to batch SQL update statements.

CREATE TYPE [dbo].[BIGIntArrayTemp] AS TABLE

(

       [Val] [bigint] NULL

)

GO

DECLARE @tableType [dbo].[BIGIntArrayTemp]

insert into @tableType (val) values      (1)

insert into @tableType (val) values      (2)

insert into @tableType (val) values      (4)

insert into @tableType (val) values      (8)

insert into @tableType (val) values      (7)

insert into @tableType (val) values      (4)

insert into @tableType (val) values      (6)   

             

DECLARE @records TABLE(records_id INT IDENTITY(1,1),SomeId BIGINT)

DECLARE @toprecords TABLE(SomeId BIGINT)

DECLARE @tmp TABLE(SomeId BIGINT,flag bit)

insert into @tmp(SomeId) values   (1)

insert into @tmp(SomeId) values   (2)

insert into @tmp(SomeId) values   (4)

insert into @tmp(SomeId) values   (8)

insert into @tmp(SomeId) values   (7)

insert into @tmp(SomeId) values   (4)

insert into @tmp(SomeId) values   (6)

insert into @tmp(SomeId) values   (10)

      

INSERT INTO @records (SomeId)

SELECT        SomeId

FROM          @tmp t

INNER JOIN  @tableType ids ON ids.Val =  t.SomeId

DECLARE @count             INT

DECLARE @batch_size INT

SET @batch_size = 100 --set batch size

INSERT INTO @toprecords (SomeId)

       SELECT top (@batch_size) SomeId FROM @records ORDER BY records_id desc

SELECT @count = COUNT(1) FROM @toprecords

      

WHILE (@count > 0)

BEGIN

       BEGIN TRY 

              BEGIN TRANSACTION --Begin transaction for each batch

              UPDATE g

              SET g.flag=1

              FROM @tmp g

                     INNER JOIN @toprecords t

              ON t.SomeId = g.SomeId

                    

              -- clean toprecords table variable

              DELETE FROM @toprecords

              -- remove the top records

              DELETE FROM dd

              FROM (

                     SELECT top (@batch_size) * FROM @records ORDER BY records_id desc

              ) dd

              -- select next top records

              INSERT INTO @toprecords (SomeId)

                     SELECT top (@batch_size) SomeId FROM @records ORDER BY records_id desc

              SELECT @count = count(1) FROM @toprecords

              COMMIT TRANSACTION -- commit transaction for each batch

       END TRY 

       BEGIN CATCH 

                     ROLLBACK TRANSACTION -- rollback if any error occurs

       END CATCH

END

SELECT * FROM @tmp

------------------------------

Another way to implement it without try...catch and transaction

DECLARE @tableType [dbo].[BIGIntArrayTemp]

insert into @tableType (val) values      (1)

insert into @tableType (val) values      (2)

insert into @tableType (val) values      (4)

insert into @tableType (val) values      (8)

insert into @tableType (val) values      (7)

insert into @tableType (val) values      (4)

insert into @tableType (val) values      (6)  

            

DECLARE @records TABLE(records_id INT IDENTITY(1,1),SomeId BIGINT)

DECLARE @updatelist TABLE(SomeId BIGINT)

DECLARE @tmp TABLE(SomeId BIGINT,flag bit)

insert into @tmp(SomeId) values   (1)

insert into @tmp(SomeId) values   (2)

insert into @tmp(SomeId) values   (4)

insert into @tmp(SomeId) values   (8)

insert into @tmp(SomeId) values   (7)

insert into @tmp(SomeId) values   (4)

insert into @tmp(SomeId) values   (6)

insert into @tmp(SomeId) values   (10)

     

DECLARE @count             INT

DECLARE @batch_size INT,@RecordCount INT

SET @batch_size = 2 --set batch size

INSERT INTO @records (SomeId)

       SELECT        SomeId

       FROM          @tmp t

       INNER JOIN  @tableType ids ON ids.Val =  t.SomeId

         

Select @RecordCount = @@RowCount --initialize RecordCount

     

WHILE (@RecordCount > 0)

BEGIN

       Delete top(@batch_size) r

       Output deleted.SomeId Into @updatelist

       From @records r

       Select @RecordCount = @@RowCount --update record count variable

       PRINT ('Record Count  --'+ convert(varchar,@RecordCount))

         

    UPDATE g

    SET g.flag=1

    FROM @tmp g

            INNER JOIN @updatelist t

    ON t.SomeId = g.SomeId

                   

    -- clean toprecords table variable

    Delete from @updatelist

    Waitfor Delay '00:00:00.5'

END

SELECT * FROM @tmp

--------------------------------

Table type data can be used in stored procedure to pass array data.

CREATE PROCEDURE [dbo].[xyz] 

       @ids dbo.[BIGIntArrayTemp] READONLY

AS 

BEGIN

       -- Proc body

END