Monday 24 July 2017

Difference between @@IDENTITY, SCOPE_IDENTITY() , IDENT_CURRENT.

All three functions return last-generated identity values. However, the scope and session on which last is defined in each of these functions differ.

 @@IDENTITY
SCOPE_IDENTITY

IDENT_CURRENT

 @@IDENTITY-  It returns the last identity value generated for any table in the current session, across all scopes.
SCOPE_IDENTITY- It returns the last identity value generated for any table in the current session and the current scope.
IDENT_CURRENT-It returns the last identity value generated for a specific table in any session and any scope.
GO
CREATE TABLE tbl_Test1
(
 Id int primary key identity(1,1),
 Name varchar(50),
 Email varchar(50)
)

 GO
CREATE TABLE tbl_Test2
(
 Id int primary key identity(1,1),
 Name varchar(50),
 Email varchar(50)
)
GO
CREATE PROC usp_InsertTest
(
@Name varchar(10),
@email varchar(10)
)
AS 
BEGIN
insert into tbl_Test2(Name,Email)
VALUES(@Name,@email)

 END

 GO
BEGIN
INSERT INTO tbl_Test1(Name,Email) VALUES('Sunil','sunil@gmail.com') --THIS INSERT QUERY EXECETED IN CURRENT SCOPE
EXEC dbo.usp_InsertTest 'Sunil','sunil@gmail.com'   -- REECORD Inserted with this Procedure in OUT of this scope
SELECT SCOPE_IDENTITY() 'SCOPE_IDENTITY'  -- Scope_identity() return last Identity of table, inserted in same(LOCAL) scope and same session 
SELECT @@IDENTITY '@@IDENTITY' --  @@identity return last Identity of table, inserted in any(Global Scope) scope and same session (Retrun identity of table record inserted by procedure usp_InsertTest )
SELECT IDENT_CURRENT('tbl_Test1') -- MAY BE DIFFERENT SESSION RETURN LAST IDENTITY OF TABLE PASSED IN PARAMENTER
END
Posted by at No comments:
Labels: , , ,

Sunday 23 July 2017

Difference Between Live And Bind Function In jQuery ?


These functions allow us to add event handler to any DOM object.
Bind method works only for the present tags in the page, which means the already existing DOM elements, not for the tags which are getting added after this method
or in the future.Now if you click on the <p> tag which we appended at run time, it will just alert the Live function, not the bind function. 
Bind method does not work for the future tag added in the page,  where live does work for that.

<p>This is a p tag</p>  
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.7/jquery.min.js" type="text/javascript"></script>   
<script>
    $("p").bind("click", function () { alert("P tag is clicked from bind function.") });
    $("p").live("click", function () { alert("P tag is clicked from live function.") });
    $("body").append("<p>This is a future p tag.</p>")
</script>
<body>
    <p>Existing P Tag in html page.</p>

</body>
Posted by at No comments:
Labels: , ,

Wednesday 19 July 2017

How to Handle/Log Exception Occur in Stored Procedure?

-- =============================================
-- Author:  <Suneel Kumar> 
-- Create date: <Create Date,17 July 2017> 
-- Description: <Manage Employee Details> 
-- =============================================
CREATE PROC [DBO].[usp_ManageEmployee]       

(                                                       
       @EmpCode        VARCHAR(100)=NULL,
       @EmpName        VARCHAR(100)=NULL,
       @Mobile         VARCHAR(100)=NULL,
       @EmailId        VARCHAR(100)=NULL,
       @EmpType        VARCHAR(100)=NULL,
       @IsActive       BIT=NULL,
       @salary         MONEY=NULL,                                                                      
       @MESSAGEOUT     VARCHAR(MAX)='' OUTPUT
)                                                                     

AS                                                         
BEGIN 

SET NOCOUNT ON;
BEGIN TRY

   INSERT INTO tblEmployee (EmpCode,EmpName,Mobile,Salary,EmailId,EmpType,IsActive)
    VALUES(@EmpCode,@EmpName,@Mobile,@Salary,@EmailId,@EmpType,@IsActive)

END TRY
BEGIN CATCH       
  SELECT @MESSAGEOUT= 'PROCNAME: usp_ManageEmployee ERRORSTATE :' + CAST(ERROR_STATE() AS VARCHAR)
                                                  + '  ERRORNUMBER :'+ CAST(ERROR_NUMBER() AS VARCHAR)
                                                  + '  ERRORLINE:'+ CAST(ERROR_LINE() AS VARCHAR)
                                                  + '  ERROR_MSG  '+ ERROR_MESSAGE()
   EXECUTE DBO.usp_LogError @PROCNAME='uspQUICKSEARCHBOOKINGS'

END CATCH
END 

GO


-- =============================================  
-- Author:  <Suneel Kumar>  
-- Create date: <Create Date,17 July 2017>  
-- Description: <Description,Insert,Error log in ErrorLog Table,>  
-- =============================================  
 
CREATE PROCEDURE [dbo].[usp_LogError]  
 @ProcName varchar(100)='', --Procedure name in which error is occured  
 @ErrorLogID [int] = 0 OUTPUT -- contains the ErrorLogID of the row inserted  
AS                               -- by uspLogError in the ErrorLog table  
BEGIN  
    SET NOCOUNT ON;  
    -- Output parameter value of 0 indicates that error  
    -- information was not logged  
    SET @ErrorLogID = 0;  
 
    BEGIN TRY  
        -- Return if there is no error information to log  
        IF ERROR_NUMBER() IS NULL  
            RETURN;  
        -- Return if inside an uncommittable transaction.  
        -- Data insertion/modification is not allowed when  
        -- a transaction is in an uncommittable state.  
        IF XACT_STATE() = -1  
        BEGIN  
            PRINT 'Cannot log error since the current transaction is in an uncommittable state. '  
                + 'Rollback the transaction before executing uspLogError in order to successfully log error information.';  
            RETURN;  
        END  
 
        INSERT [dbo].[ErrorLog]  
            (  
            [ErrorNumber],  
            [ErrorSeverity],  
            [ErrorState],  
            [ErrorProcedure],  
            [ErrorLine],  
            [ErrorMessage] ,
             ErrorTime
            )  
        VALUES  
            (            
            ERROR_NUMBER(),  
            ERROR_SEVERITY(),  
            ERROR_STATE(),  
            @ProcName,            
            ERROR_LINE(),  
            ERROR_MESSAGE()
            ,GETDATE()
            );  
 
        -- Pass back the ErrorLogID of the row inserted  
        SET @ErrorLogID = @@IDENTITY;  
    END TRY  
    BEGIN CATCH  
        PRINT 'An error occurred in stored procedure uspLogError: ';  
        EXECUTE [dbo].[usp_PrintError];  
        RETURN -1;  
    END CATCH  
END;



GO


-- =============================================
-- Author:  <Suneel Gupta>
-- Create date: <Create Date,17 July 2017,>
-- Description: <Description,if any error throw sp then print Error>
-- =============================================

create PROCEDURE [dbo].[usp_PrintError]
AS
BEGIN
    SET NOCOUNT ON;
    -- Print error information.
    PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) +
          ', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
          ', State ' + CONVERT(varchar(5), ERROR_STATE()) +
          ', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') +
          ', Line ' + CONVERT(varchar(5), ERROR_LINE());
    PRINT ERROR_MESSAGE();
END;                                   
Posted by at No comments:
Labels: , , , , ,

Tuesday 18 July 2017

What is the difference between a Local and a Global temporary table?

A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.
A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.
Posted by at No comments:
Labels: ,

Monday 17 July 2017

What is the difference between Var, Dynamic and object in c#


Object
The object class in C# represents the System.Object type, which is the root type in the C# class hierarchy. Generally we use this class when we cannot specify the object type at compile time, which generally happens, when we deal with interoperability.
           object salevalue = 100;

            Console.Write("print object type : " + salevalue.GetType());

            output--- print object type : System.Int32

            salevalue =salevalue + 100 ; // gives complite time error 
Var
The var type was introduced in C# 3.0. It is used for implicitly typed local variables and for anonymous types. The var keyword is generally used with LINQ.

 var amount;   // Initialization of the variable are required at time of declaration.

We cannot change the type of these variables at runtime. If the compiler can’t infer the type, it produces a compilation error.

           var amount1 = 100;
           amount1 = amount1 + 100;
           amount1 = "Hundred";

Dynamic
The dynamic type was introduced in C# 4.0. The dynamic type uses System.Object indirectly but it does not require explicit type casting for any operation at runtime, because it identifies the types at runtime only.

            dynamic amount = 100;
            amount = amount + 100;
            Console.Write("print object type : " + salevalue.GetType());
            amount="Hundred";
             Console.Write("print object type : " + salevalue.GetType());

            output:  print object type : System.Int32
            output:  print object type : System.String
Posted by at No comments:
Labels: ,

Wednesday 12 July 2017

CURSOR EXAMPLE IN SQL SERVER

--========= CURSOR SAMPLE EXAMPLE ============
/*
A cursor is temparory work area created in system memory when  a sql statement executed. Cursor contains information on selected statement and
the row of data access by it.This temporary work area is used to store the data retrieved from the database, and manipulate
this data.

*/
CREATE TABLE #tblEmployee
(
EmpId int primary key identity(1,1),
Mobile varchar(10)
)
insert into #tblEmployee (Mobile)
VALUES
('9899767121'),
('7546434534'),
('9656345433'),
('7898056566'),
('0986545445'),
('2342546456')

DECLARE  @EmpId int,@Mobile varchar(20)     -- declare variable which are used in cursor code block
DECLARE MyCursor CURSOR                     ---- declare cursor
 FOR --- There are an equal number of variables in the DECLARE CURSOR FOR statement as there are in the SELECT statement.        
 select EmpId,Mobile From #tblEmployee
 OPEN MyCursor -- OPEN Cursor
 FETCH NEXT FROM MyCursor INTO @EmpId,@Mobile   --- FETCH NEXT FROM  
 WHILE @@FETCH_STATUS=0 --- WHILE Statement
 BEGIN
   PRINT @Mobile
 
   FETCH NEXT FROM MyCursor INTO @EmpId,@Mobile
 END
 CLOSE MyCursor ---CLOSE statement - Releases the current data and associated locks, but permits the cursor to be re-opened
 DEALLOCATE MyCursor ---DEALLOCATE statement - Destroys the cursor
Posted by at No comments:
Labels: ,

Thursday 16 March 2017

Show Alert before session expire in asp.net web application using c#.


<style type="text/css">
        .modalBackground {
            background-color: Black;
            filter: alpha(opacity=60);
            opacity: 0.6;
        }

        .modalPopup {
            background-color: #FFFFFF;
            width: 300px;
            border: 3px solid #006990;
            border-radius: 12px;
            padding: 0;
        }

            .modalPopup .header {
                background-color: #006990;
                height: 30px;
                color: White;
                line-height: 30px;
                text-align: center;
                font-weight: bold;
                border-top-left-radius: 6px;
                border-top-right-radius: 6px;
            }

            .modalPopup .body {
                padding: 10px;
                min-height: 50px;
                text-align: center;
                font-weight: bold;
            }

            .modalPopup .btnSection {
                padding: 6px;
            }

            .modalPopup .yes, .modalPopup .no {
                height: 23px;
                color: White;
                line-height: 23px;
                text-align: center;
                font-weight: bold;
                cursor: pointer;
                border-radius: 4px;
            }

            .modalPopup .yes {
                background-color: #006990;
                border: 1px solid #006990;
            }

            .modalPopup .no {
                background-color: #9F9F9F;
                border: 1px solid #5C5C5C;
            }
    </style>

 <asp:LinkButton ID="lnkFake" runat="server" />
                        <asp:ModalPopupExtender ID="mpeTimeout" BehaviorID="mpeTimeout" runat="server" PopupControlID="pnlPopup"
                            TargetControlID="lnkFake" OkControlID="btnYes" CancelControlID="btnNo" BackgroundCssClass="modalBackground"
                            OnOkScript="ResetSession()">
                        </asp:ModalPopupExtender>
                        <asp:Panel ID="pnlPopup" runat="server" CssClass="modalPopup" Style="display: none">
                            <div class="header">
                                Session Expiring!
                            </div>
                            <div class="body">
                                Your Session will expire in&nbsp;<span id="seconds"></span>&nbsp;seconds.<br />
                                Do you want to reset?
                            </div>
                            <div class="btnSection" align="right">
                                <asp:Button ID="btnYes" runat="server" Text="OK" CssClass="yes" />
                               <%-- <asp:Button ID="btnNo" runat="server" Text="No" CssClass="no" />--%>
                            </div>
                        </asp:Panel>
                        <script type="text/javascript">
                            function SessionExpireAlert(timeout) {
                                var seconds = timeout / 1000;
                               // document.getElementsByName("secondsIdle").innerHTML = seconds;
                                document.getElementsByName("seconds").innerHTML = seconds;
                                setInterval(function () {
                                    seconds--;
                                    document.getElementById("seconds").innerHTML = seconds;
                                 //   document.getElementById("secondsIdle").innerHTML = seconds;
                                }, 1000);
                                setTimeout(function () {
                                    //Show Popup before 20 seconds of timeout.
                                    $find("mpeTimeout").show();
                                }, timeout - 60 * 1000);
                                setTimeout(function () {
                                    //alert(" Your Session has expired.");
                                    window.location = window.location.href;
                                   // window.location = "Expired.aspx";
                                }, timeout);
                            };
                            function ResetSession() {
                                //Redirect to refresh Session.
                                //window.location = window.location.href;
                                $.ajax({
                                    type: "POST",
                                    contentType: "application/json; charset=utf-8",
                                    url: '<%=ResolveUrl("~/area/login.aspx/ResetSession") %>',
                                    dataType: "json",
                                    success: function (data) {
                                        SessionExpireAlert(data.d);
                                    },
                                    error: function (result) {
                                        alert("Error");
                                    }
                                });
                            }
                        </script>

 protected void Page_Load(object sender, EventArgs e)
    {
 Response.Cache.SetCacheability(HttpCacheability.NoCache);
                if (!Page.IsPostBack)
                {
                    Session["Reset"] = true;
                    Configuration config = WebConfigurationManager.OpenWebConfiguration("~/Web.Config");
                    SessionStateSection section = (SessionStateSection)config.GetSection("system.web/sessionState");
                    int timeout = (int)section.Timeout.TotalMinutes * 1000 * 60;
                    ScriptManager.RegisterStartupScript(this, typeof(string), "SessionAlert", "SessionExpireAlert(" + timeout + ");", true);
                }
}

Use of Web Method to avoid reload page and maintain  page data

[WebMethod]
    public static string ResetSession()
    {
        int TimeOut = 0;
        try
        {
            HttpContext.Current.Session["Reset"] = true;
            Configuration config = WebConfigurationManager.OpenWebConfiguration("~/Web.Config");
            SessionStateSection section = (SessionStateSection)config.GetSection("system.web/sessionState");
            TimeOut = (int)section.Timeout.TotalMinutes * 1000 * 60;
            //ScriptManager.RegisterStartupScript(this, typeof(string), "SessionAlert", "SessionExpireAlert(" + timeout + ");", true);
        }
        catch (Exception ex)
        {

        }
        return TimeOut.ToString();
    }


IN WEB CONFIG-

 <sessionState timeout="2"/>
Posted by at No comments:
Labels: , , ,

Sql query to convert rows into column using Piovt or CTE.



create table #companyvalues
(
companyid           varchar(5),
paramkey            varchar(30),
paramvalue          integer
);

INSERT #companyvalues ([CompanyId], [ParamKey], [ParamValue]) VALUES (N'ABC', N'MinLength', 8)
INSERT #companyvalues ([CompanyId], [ParamKey], [ParamValue]) VALUES (N'ABC', N'MaxLength', 0)
INSERT #companyvalues ([CompanyId], [ParamKey], [ParamValue]) VALUES (N'ABC', N'IsAlphanumeric', 1)
INSERT #companyvalues ([CompanyId], [ParamKey], [ParamValue]) VALUES (N'ABC', N'IsCaseSensitive', 0)
INSERT #companyvalues ([CompanyId], [ParamKey], [ParamValue]) VALUES (N'ABC', N'IsSpecialChars', 0)
INSERT #companyvalues ([CompanyId], [ParamKey], [ParamValue]) VALUES (N'ABC', N'PasswordExpiryDays', 80)
INSERT #companyvalues ([CompanyId], [ParamKey], [ParamValue]) VALUES (N'ABC', N'AlertDays', 10)
INSERT #companyvalues ([CompanyId], [ParamKey], [ParamValue]) VALUES (N'XYZ', N'MinLength', 7)
INSERT #companyvalues ([CompanyId], [ParamKey], [ParamValue]) VALUES (N'XYZ', N'MaxLength', 1)
INSERT #companyvalues ([CompanyId], [ParamKey], [ParamValue]) VALUES (N'XYZ', N'IsAlphanumeric', 3)
INSERT #companyvalues ([CompanyId], [ParamKey], [ParamValue]) VALUES (N'XYZ', N'IsCaseSensitive', 1)
INSERT #companyvalues ([CompanyId], [ParamKey], [ParamValue]) VALUES (N'XYZ', N'IsSpecialChars', 1)
INSERT #companyvalues ([CompanyId], [ParamKey], [ParamValue]) VALUES (N'XYZ', N'PasswordExpiryDays', 90)
INSERT #companyvalues ([CompanyId], [ParamKey], [ParamValue]) VALUES (N'XYZ', N'AlertDays', 5)


;with companynames as
(
select distinct companyid compid
from #companyvalues
) ,
details as
(
select * from #companyvalues
)
select compid, max(MinLength) MinLength, max(MaxLength) MaxLength, max(IsAlphanumeric) IsAlphanumeric,
max(IsCaseSensitive) IsCaseSensitive, max(IsSpecialChars) IsSpecialChars, max(PasswordExpiryDays) PasswordExpiryDays,
max(AlertDays) AlertDays
from
(
select compid
, case when  paramkey = 'MinLength'          then paramvalue else -1 end  MinLength        
, case when  paramkey = 'MaxLength'          then paramvalue else -1 end  MaxLength        
, case when  paramkey = 'IsAlphanumeric'     then paramvalue else -1 end  IsAlphanumeric  
, case when  paramkey = 'IsCaseSensitive'    then paramvalue else -1 end  IsCaseSensitive  
, case when  paramkey = 'IsSpecialChars'     then paramvalue else -1 end  IsSpecialChars  
, case when  paramkey = 'PasswordExpiryDays' then paramvalue else -1 end  PasswordExpiryDays
, case when  paramkey = 'AlertDays'          then paramvalue else -1 end  AlertDays        
from companynames, details
where compid = companyid
) finalresult
group by compid
order by 1



select * from
(
select companyid,paramkey,paramvalue from #companyvalues
 )X
 PIVOT
 (
 MAX(paramvalue)
  FOR paramkey IN ([MinLength], [MaxLength],  [IsAlphanumeric],     [IsCaseSensitive],    [IsSpecialChars],  [PasswordExpiryDays],[AlertDays])  
 )Y     
Posted by at No comments:
Labels: ,
Subscribe to: Posts (Atom)