Find Which Transaction is currently Running

SELECT s_tst.[session_id],
   s_es.[login_name] AS [Login Name],
   S_tdt.[database_transaction_begin_time] AS [Begin Time],
   s_tdt.[database_transaction_log_record_count] AS [Log Records],
   s_tdt.[database_transaction_log_bytes_used] AS [Log Bytes],
   s_tdt.[database_transaction_log_bytes_reserved] AS [Log Reserved],
   s_est.[text] AS [Last T-SQL Text],
   s_eqp.[query_plan] AS [Last Query Plan]
FROM sys.dm_tran_database_transactions s_tdt
   JOIN sys.dm_tran_session_transactions s_tst
      ON s_tst.[transaction_id] = s_tdt.[transaction_id]
   JOIN sys.[dm_exec_sessions] s_es
      ON s_es.[session_id] = s_tst.[session_id]
   JOIN sys.dm_exec_connections s_ec
      ON s_ec.[session_id] = s_tst.[session_id]
   CROSS APPLY sys.dm_exec_sql_text (s_ec.[most_recent_sql_handle]) AS s_est
   OUTER APPLY sys.dm_exec_query_plan (s_ec.[most_recent_sql_handle]) AS s_eqp
ORDER BY [Begin Time] ASC;

Core AJAX Call from Page

Javascript

function newXMLHttpRequest() {
    var xmlreq = false;
    if (window.XMLHttpRequest) {
        xmlreq = new XMLHttpRequest();
    } else if (window.ActiveXObject) {
        // Try ActiveX
        try {
            xmlreq = new ActiveXObject("Msxml2.XMLHTTP");
        } catch (e1) {
            // first method failed 
            try {
                xmlreq = new ActiveXObject("Microsoft.XMLHTTP");
            } catch (e2) {
                // both methods failed 
            }
        }
    }
    return xmlreq;
}

function GetUserInfo(PageURL) {
    var req = newXMLHttpRequest();

    req.open("POST", PageURL, true);
    req.setRequestHeader("Content-Type", "application/x-www-form-urlencoded");
    req.setRequestHeader("content-length", "0"); req.send('');

    req.onreadystatechange = function() {
        if (req.readyState == 4) {
            if (req.status == 200) {
                alert(req.responseText.toString());
            }
        }
    }
}

Page Method

Code in aspx page :

[System.Web.Services.WebMethod]
public static string CheckUserAvailability(string sUserId, string sUserName)
{
    return CommonFunctions.CheckUserAvailability(sUserId, sUserName);
}

HTML :
<a href="#" onclick="CheckAvailability()">Check User Availability</a>
<span id="lblCheck"></span>

Javascript :

function CheckAvailability() {
    PageMethods.CheckUserAvailability(document.getElementById('<%= hdnCurrentUserId.ClientID %>').value, document.getElementById('<%= txtUserName.ClientID %>').value, OnSucceededUser, OnFailedUser);

    return false;
}

function OnSucceededUser(result, userContext, methodName) {
    document.getElementById('lblCheck').innerHTML = result;
    return;
}

function OnFailedUser(error, userContext, methodName) {
    return;
}

LINQ Extended method for Update Data

Extended Method

public delegate void Func(TArg0 element);

/// 
/// Executes an Update statement block on all elements in an IEnumerable sequence.
/// 
/// The source element type.
/// The source sequence.
/// The update statement to execute for each element.
/// The numer of records affected.
public static int Update(this IEnumerable source, Func update)
{
    if (source == null) throw new ArgumentNullException("source");
    if (update == null) throw new ArgumentNullException("update");
    if (typeof(TSource).IsValueType)
        throw new NotSupportedException("value type elements are not supported by update.");

    int count = 0;
    foreach (TSource element in source)
    {
        update(element);
        count++;
    }
    return count;
}

Example :

int intUpdate = lstItem
                .Where(a => a.ItemGroupId.Equals(lngItemGroupId))
                .Update(b =>
                            {
                                b.GroupName = strGroupName;
                                b.GroupLocation = strLocation;
                            }
                );

Truncate Log

--BACKUP LOG  WITH TRUNCATE_ONLY
--Go


--============= For sql 2008 =====================
USE
GO
SELECT file_id, name FROM sys.database_files
GO
DBCC SHRINKFILE (2, TRUNCATEONLY); 

GET Database diffrence

----Declaration
BEGIN 
 DECLARE @Status_TableNotFound  INT = 1
 DECLARE @Status_FieldNotFound  INT = 2
 DECLARE @Status_FieldTypeChnage  INT = 3
 DECLARE @Status_FieldLengthChange INT = 4
ENd

--Get Table / Filed List
BEGIN
 SELECT 
  --- db1
  db1Objects.id AS db1Id
  , db1Objects.name AS db1TableName
  , db1Column.name AS db1FieldName 
  , db1Column.xType as db1FieldType 
  , UPPER(Type_Name(db1Column.xtype)) AS db1FieldTypeName
  , db1Column.length as db1FieldLength
  , db1Column.ColStat & 1 AS db1IsIdentity

  --- db2
  , ISNULL( db2Objects.id, 0) AS db2Id
  , ISNULL( db2Objects.name, '') AS db2TableName
  , ISNULL( db2Column.name, '') AS db2FieldName 
  , ISNULL( db2Column.xType, 0 ) as db2FieldType 
  , UPPER(ISNULL( Type_Name(db2Column.xtype), '' )) AS db2FieldTypeName
  , ISNULL( db2Column.length, 0 ) as db2FieldLength
  , ISNULL( db2Column.ColStat & 1, 0 ) AS db2IsIdentity
  , 0 AS Status
 INTO #FieldCheck
 FROM <DBName1>.dbo.SysObjects AS db1Objects 
  INNER JOIN <DBName1>.dbo.SysColumns AS db1Column ON db1Column.id = db1Objects.id
   AND db1Objects.xtype = 'U'
   AND db1Objects.category = 0
  
  LEFT OUTER JOIN <DBName2>.dbo.SysObjects AS db2Objects ON db2Objects.name = db1Objects.name
   AND db2Objects.xtype = 'U'
   AND db2Objects.category = 0
  LEFT OUTER JOIN <DBName2>.dbo.SysColumns AS db2Column ON db2Column.id = db2Objects.id
   AND db2Column.name = db1Column.name
END

--- Check for Status
BEGIN
 --- TableNotFound
 UPDATE #FieldCheck
 SET Status = @Status_TableNotFound
 WHERE db1TableName != db2TableName
 
 --- FieldNotFound
 UPDATE #FieldCheck
 SET Status = @Status_FieldNotFound
 WHERE db1FieldName != db2FieldName
  AND Status = 0
 
 --- FieldTypeChnage
 UPDATE #FieldCheck
 SET Status = @Status_FieldTypeChnage
 WHERE db1FieldName = db2FieldName
  AND db1FieldTypeName != db2FieldTypeName
  AND Status = 0
 
 --- FieldLengthChange
 UPDATE #FieldCheck
 SET Status = @Status_FieldLengthChange
 WHERE db1FieldLength != db2FieldLength
  AND Status = 0
END


--- TableNotFound
SELECT DISTINCT db1TableName AS TableNotFound
FROM #FieldCheck
WHERE Status = @Status_TableNotFound

--- FieldNotFound
SELECT DISTINCT 
 db1TableName AS TableName
 , db1FieldName FieldNotFound
 , 'ALTER TABLE ' + db1TableName + 
  ' ADD ' + db1FieldName + 
  ' ' + db1FieldTypeName + 
  ' ' + ( 
    CASE 
     WHEN db1FieldTypeName IN ( 'VARCHAR', 'NVARCHAR' )
      THEN '(' + CAST( db1FieldLength AS VARCHAR(10) ) + ')'
     ELSE ''
    END
        ) +
  '; UPDATE ' + db1TableName +
  ' SET  ' + db1FieldName + ' = ' +
  (
   CASE
    WHEN db1FieldTypeName LIKE '%CHAR%' 
      OR db1FieldTypeName LIKE '%TEXT%' 
      OR db1FieldTypeName LIKE '%XML%' 
     THEN ''''''
    WHEN db1FieldTypeName LIKE 'INT' 
      OR db1FieldTypeName LIKE 'SMALLINT'
      OR db1FieldTypeName LIKE 'BIGINT'
      OR db1FieldTypeName LIKE '%BINARY%'
      OR db1FieldTypeName LIKE 'float'
      OR db1FieldTypeName = 'BIT'
     THEN '0'
    ELSE 'object'
   END
  )
  ' ; '
FROM #FieldCheck
WHERE Status = @Status_FieldNotFound

--- FieldTypeChnage
SELECT DISTINCT db1TableName AS TableName, db1FieldName AS FieldName, db1FieldTypeName, db2FieldTypeName
FROM #FieldCheck
WHERE Status = @Status_FieldTypeChnage


--- FieldLengthChange
SELECT DISTINCT db1TableName AS TableName, db1FieldName AS FieldName, db1FieldTypeName AS FieldType, db1FieldLength, db2FieldLength
FROM #FieldCheck
WHERE Status = @Status_FieldLengthChange

DROP TABLE #FieldCheck

Find and Replace text in Datatable

SET NOCOUNT ON
DECLARE @FindName     VARCHAR(MAX) = 'AAA' --- <- Find Value
DECLARE @ReplaceName  VARCHAR(MAX) = 'BBB' --- <- Replace Value

DECLARE @Table TABLE
(
 ID INT IDENTITY(1,1)
 , TableName  VARCHAR(100)
 , ColumnName VARCHAR(100)
)
INSERT INTO @Table
 SELECT
  tableSchema.TABLE_SCHEMA + '.' + SysObjects.name
  , SysColumns.Name
 FROM sysobjects 
  INNER JOIN SysColumns ON SysColumns.id = sysobjects .id
   AND SysColumns.xtype IN ( 167, 175, 239, 231, 35, 99 )
  INNER JOIN information_schema.tables AS tableSchema ON tableSchema.TABLE_NAME = SysObjects.name
 WHERE sysobjects.xtype='U'
 ORDER BY sysobjects.Name, SysColumns.Name
 
DECLARE @NoOfRecord INT = 0 
DECLARE @CurrentRecord INT = 1
DECLARE @TableName VARCHAR(100) = ''
DECLARE @ColumnName VARCHAR(100) = ''
DECLARE @SQLCommand VARCHAR(MAX) = ''

SELECT @NoOfRecord = MAX(ID)
FROM @Table

WHILE (@CurrentRecord <= @NoOfRecord )
 BEGIN
  SELECT
   @TableName = TableName
   , @ColumnName = ColumnName
  FROM @Table
  WHERE ID = @CurrentRecord
  
  SET @SQLCommand = 'IF EXISTS(SELECT * FROM ' + @TableName + ' WHERE ' + @ColumnName + ' LIKE ''%' + @FindName + '%'')' +
   ' BEGIN ' +
   ' PRINT ''UPDATE ' + @TableName +
   ' SET ' + @ColumnName + ' = REPLACE(' + @ColumnName + ', ''''' + @FindName + ''''', ''''' + @ReplaceName + ''''') ''' +
   ' END '
   
  -- PRINT @SQLCommand
  EXECUTE(@SQLCommand)
  SET @CurrentRecord = @CurrentRecord + 1
 END