SQL Dependency with Cache (server side) For MVC and ASP.NET

Step 1 :

ALTER DATABASE [XYZ] SET ENABLE_BROKER with rollback immediate;
ALTER DATABASE [XYZ] SET TRUSTWORTHY ON;

Step 2 :


   
     
       
         
       
     
   

Step 3 :

public DataSet GetReaderByCmdCaching(string command, int keyID)
        {
            object classlock = new object();
            this.GetConnection();
            System.Data.DataSet returnValue = new DataSet();
            string strKey = "readerdata" + keyID;
            string strKeyNavigation = "readerdatanavigation" + keyID;
            if (HttpContext.Current.Cache[strKey] == null)
            {
                lock (classlock)
                {
                    System.Data.SqlClient.SqlDependency.Start(this.connectionString);
                    this.sqlCommand.CommandText = command;
                    SqlCacheDependency dependency = new SqlCacheDependency("CacheDatabaseConnection", "TableName");
                    SqlDataAdapter da = new SqlDataAdapter(this.sqlCommand);
                    da.Fill(returnValue);
                    HttpContext.Current.Cache.Insert(strKey, returnValue, dependency);
                }
            }
            else
            {
                returnValue = (System.Data.DataSet)HttpContext.Current.Cache[strKey];
            }
           
            return returnValue;
        }

Step 4 :

DataSet dsTemp = GetReaderByCmdCaching("Name of SP or Query", UniqueID);

Step 5 :

You are done, whenever any data change in Table it generate the Cache :) Happy Coding.

Sql Server Email Validation in Store procedure

IF not (

CHARINDEX(' ',LTRIM(RTRIM(@email))) = 0

AND LEFT(LTRIM(@email),1) <> '@'

AND RIGHT(RTRIM(@email),1) <> '.'

AND CHARINDEX('.',@email ,CHARINDEX('@',@email)) - CHARINDEX('@',@email ) > 1

AND LEN(LTRIM(RTRIM(@email ))) - LEN(REPLACE(LTRIM(RTRIM(@email)),'@','')) = 1

AND CHARINDEX('.',REVERSE(LTRIM(RTRIM(@email)))) >= 3

AND (CHARINDEX('.@',@email ) = 0 AND CHARINDEX('..',@email ) = 0)

)

begin

   // TO DO

end

custom paging using store procedure in sql server with row number

input parameter for sp

, @PageSize INT = 10

, @PageIndex INT = 1

store procedure definitions

SELECT XYZID, XYZType, InCylinder,InSystem,XXXX
FROM (
SELECT XYZes.XYZID
 , XYZes.XYZType
 , ISNULL((SELECT SUM(TABLEPQR.XYZVolume) FROM TABLEPQR WHERE TABLEPQR.XYZID = XYZes.XYZID AND TABLEPQR.AssetTypeID IN (1, 3) ), 0) AS InCylinder
 , ISNULL((SELECT SUM(TABLEPQR.XYZVolume) FROM TABLEPQR WHERE TABLEPQR.XYZID = XYZes.XYZID AND TABLEPQR.AssetTypeID = 2 ), 0) AS InSystem
 , ISNULL(
  ISNULL((SELECT SUM(TABLEXYZ.XYZVolume) FROM TABLEXYZ WHERE TABLEXYZ.XYZTypeID = XYZes.XYZID AND TABLEXYZ.Activity = 'XXXX'), 0)
  +
  ISNULL((SELECT SUM(TABLENNN.XYZVolume) FROM TABLENNN WHERE TABLENNN.XYZTypeID = XYZes.XYZID AND TABLENNN.Activity = 'XXXX'), 0)
  , 0) AS XXXX,
ROW_NUMBER() OVER (ORDER BY XYZID) AS RowNum
FROM XYZes ) AS SOD
WHERE SOD.RowNum BETWEEN ((@PageIndex-1)*@PageSize)+1
AND @PageSize*(@PageIndex)

select @RecordCount = COUNT(*) From XYZes
return @RecordCount 

Jquery DataTable with Drill Down option

IN YOUR VIEW USE THE BELOW FUNCTION

Write below function in your document.ready event, this will bind "+" td.control img with function which will open drill down.

$("#myDataTable").on("click", "td.control img", function (event) {

                var nTr = this.parentNode.parentNode;
                var i = $.inArray(nTr, anOpen);

                if (i === -1) {

                    $('img', this.parentNode).attr('src', sImageUrl + "drildown_close.jpg");
                    oTable.fnOpen(nTr, fnFormatDetails(oTable, nTr), '');
                    anOpen.push(nTr);
                }
                else {
                    $('img', this.parentNode).attr('src', sImageUrl + "drildown_open.jpg");
                    oTable.fnClose(nTr);
                    anOpen.splice(i, 1);
                }
            });

  Below function used to open a div as drill down with passed parameter and data which return by method.

            function fnFormatDetails(oTable, nTr) {
                var sOut = '';
                $('#DivLoading').show();
                $.ajax({
                    "url": '@Url.Content("method name to fetch data")',
                    data: { para: paraID },
                    "async": false,
                    cache: false,
                    "dataType": "text",
                    "success": function (json) {
                        sOut = json;
                        $('#DivLoading').hide();
                    },
                    error: function (response) {
                        //// debugger;
                    }
                });

                return sOut;
            }

            /*Functions used for nasted data binding END*/

You can use this function in PHP or .NET code , as this is Jquery stuff so you don't need to worry about any platform.

Update only Those records which are affected using Update Tirgger

COLUMNS_UPDATED

500 is a bit mask for checking number of columns affected or you want to check ...

IF (COLUMNS_UPDATED() & 500 > 0)
    BEGIN
            INSERT INTO TableName
            select  'update' as [Action], * from inserted 
    END

For more update or info use below link ...

http://technet.microsoft.com/en-us/library/ms186329.aspx

Paging same like EF take and skip in Sql 2012 ... awesome work :)

select * from TableName order by ID desc
offset 0 rows fetch next 20 rows only

Here 0 means starting from and 20 means how many records you want to fetch ....

Another great things in 2012 is ... you can use "Through" steatement direct inside the catch block ...

BEGIN TRY
DECLARE @VarToTest INT
SET @VarToTest = 'C'
END TRY
BEGIN CATCH
THROW
END CATCH


Quite superb ....