Track Alter stored procedure in Sql Server

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE trigger [Tracking_Alter_SP]
on database
for create_procedure, alter_procedure, drop_procedure,
create_table, alter_table, drop_table,
create_function, alter_function, drop_function
as set nocount on

SET ARITHABORT ON

declare @data xml
set @data = EVENTDATA()

insert into changelog(databasename, eventtype,
    objectname, objecttype, sqlcommand, loginname,hostname)
values(
@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'varchar(300)'),
@data.value('(/EVENT_INSTANCE/EventType)[1]', 'varchar(60)'),
@data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(300)'),
@data.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(50)'),
@data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'varchar(max)'),
@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(300)'),
HOST_NAME()
)
GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

Get Control in Gridview's Row command Event

<%-- Let us consider below is our grid.--%>

 
      
           
              

              
           
      
 

'Here is code behind.
Private Sub gridview_RowCommand(sender As Object, _

e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles gridview.RowCommand
 
Dim btnActive As Button = DirectCast(e.CommandSource, Button)
Dim index As Integer = DirectCast(btnActive.Parent.Parent, GridViewRow).RowIndex
Dim chkmail As CheckBox = DirectCast(gridview.Rows(index).FindControl("chkmail"), CheckBox) 

'Here i get control like btnactive.parent.parent.It may be different for you.
'It depends how many controls you use in grid view. 

End Sub

Performance of CTE(Common table expression) Vs Hash(#)Tables.

Let say we have one report and in which we want data from around 20 tables.

so i just created some 4 to 5 cte(Common table expression) for every 4 to 5 tables.

and then join cte's and get the data.but when i execute that stored procedure it will take around 10 Minutes.

i am really strange about this.

Now what i do just replace all cte(Common table expression) with Hash(#)table.

and it's executed with only around 15 seconds.

So i suggest when cte 1 or 2 then ok but 4 to 5 then you can use #tables.

This is work for me.Let's you guys try it.if you use cte and it will take too much time to execute.

Bind WebGrid in Asp.net MVC

I just learn how to bind grid in MVC. I hope you guys just know basic about MVC.
First of all You guys just have to install one package.for that got to Tools->Library Package Manager
and run command in output window
Command:- Install-Package RazorGenerator.Templating

1.) Below is controller code.

in above code i just create simple List of my model.you can also get that list from database also.

2.) Below is model code.

in above code simple properties are created.

3.) Below is view code.

Here is your view code. here one thing is that you have to take care is that is first line.i tried with @model MvcApplication1.Models.NiksModel but it means it accept model from controller.but here we are passing list from controller so you just have defined as list of your model.

Reference:
http://www.dotnetcurry.com/ShowArticle.aspx?ID=618

View Last Excuted Quries in Sql Server

View Last Excuted Quries in Sql Server

SELECT DEQS.LAST_EXECUTION_TIME AS [TIME]
,SUBSTRING(DEST.TEXT,(DEQS.STATEMENT_START_OFFSET/2)+1
,((CASE DEQS.STATEMENT_END_OFFSET WHEN -1 THEN DATALENGTH(DEST.TEXT)ELSE DEQS.STATEMENT_END_OFFSET
END - DEQS.STATEMENT_START_OFFSET)/2) + 1) AS STATEMENT_TEXT
,DEST.*
FROM SYS.DM_EXEC_QUERY_STATS AS DEQS
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(DEQS.SQL_HANDLE) AS DEST
ORDER BY DEQS.LAST_EXECUTION_TIME DESC

Comma seperated records in sql without loop or cursor

SELECT STUFF
(
(

SELECT ',' + T1.FULLNAME AS [text()]

FROM DBO.TABLE_1 AS T1

INNER JOIN DBO.TABLE_2 AS T2 ON T1.ID = T2.ID

FOR XML PATH('')
)
,1,1,''
)

DECLARE @COLS NVARCHAR(2000)

SELECT @COLS = COALESCE(@COLS + ',[' + CONVERT(NVARCHAR(100),T1.FULLNAME ) + ']','[' + CONVERT(NVARCHAR(100),T1.FULLNAME) + ']')

FROM DBO.TABLE_1 AS T1

INNER JOIN DBO.TABLE_2 AS T2 ON T1.ID = T2.ID

Insert and update data Using Xml file in Sql Server

'Making Xml 
 Dim sb As New System.Text.StringBuilder

        sb.Append("")


        For i = 0 To dt.Rows.Count - 1 

            sb.Append("
            sb.Append("PQty=""" & dt.Rows(i).Item("PQty") & """")

            sb.Append("PWeight=""" & ClearWhiteSpace(dt.Rows(i).Item("PWeight")) & """")            


            sb.Append("/>")

        Next

        sb.Append("")

-- Making Stored Procedure
CREATE PROCEDURE [dbo].[TestMasterInsert]    
    @MId Int,    
    @GId Int,
    @PId Int,
    @Name varchar(50),
    @Data text,
    @TranType  Char(1)
AS
BEGIN    
    SET NOCOUNT ON;    
    
    Declare @id As Int 
        SET @id = 0
        
        DECLARE @hDoc int exec sp_xml_preparedocument @hDoc OUTPUT, @Data
        
    IF @TranType = 'I'    
        BEGIN        
            Insert Into TestMaster Values (@GId,@PId,@Name)
        
            Select @id = SCOPE_IDENTITY()  
        
            INSERT INTO TestDetail
                SELECT @id,PQty,PWeight
                FROM   OPENXML (@hDoc, '/ROOT/SI',2)          
                WITH (
                       PQty       Decimal(18,3) '@PQty',
                       PWeight    Decimal(18,3) '@PWeight'
                     )   
          
             EXEC sp_xml_removedocument @hDoc
        END
    ELSE IF @TranType = 'U'        
        BEGIN
            
            UPDATE TestDetail     
            SET     
                  TestDetail.PQty = XMLTEST.PQty,    
                  TestDetail.PWeight = XMLTEST.PWeight
            FROM OPENXML(@hDoc, '/ROOT/SI',2)
                        WITH (DId Int '@DId',PQty Decimal(18,3) '@PQty',PWeight Decimal(18,3) '@PWeight') XMLTEST
            WHERE  TestDetail.DId = XMLTEST.DId
            
            EXEC sp_xml_removedocument @hDoc        
        END    
END