SQL PAGING

 Paging: 

   PageIndex: n

   PageSize:   m

Start Index: n*m-m

End Index: n*m-1

SQL LEFT JOIN BEHIND

 

select * from Ramki 

Id Mpin ParentId Status

1 474 0 1

3 100 1 0

4 475 1 1

5 600 0 1

6 700 0 0

Expected Result: Parent , child along with status should active

MpinParent MpinChild

474 475

600 NULL

Correct Query: 

select r1.Mpin as MpinParent,r2.Mpin as MpinChild from Ramki r1 

left join Ramki r2 on r1.Id = r2.ParentId    and  r2.Status = 1   where r1.ParentId = 0    and r1.Status = 1

Try for learn: 

Bug in this: 

select r1.Mpin as MpinParent,r2.Mpin as MpinChild from Ramki r1 

left join Ramki r2 on r1.Id = r2.ParentId    and r1.Status = 1  and  r2.Status = 1   where r1.ParentId = 0   

SQL BASIC

 

DECLARE @parInt int  =  22

select FORMAT(@parInt,'0000000')

declare @parString nvarchar(20) = '22'

select format( cast(@parString as int),'000000')

Trigger: Create trigger with all CRUD ( insert, update ,delete )

 alter trigger SynchTriggerForTestDataFactoryLog

on dbo.TestDataFactoryLogOnPremise

after update,insert,delete 

as 

begin

if(ROWCOUNT_BIG() = 0)

return;

declare @activity varchar(20);

if exists(SELECT * from inserted) and exists (SELECT * from deleted)

begin

    SET @activity = 'UPDATE'

    insert into TestSyncOcrLog select 'TestDataFactoryLogOnPremise' as SourceName,Id as SourceID,0 as IsSyncForOnPrmise,1 as IsSyncForCloud,@activity as Activity

from inserted

end

If exists (Select * from inserted) and not exists(Select * from deleted)

begin

   SET @activity = 'INSERT';

   insert into TestSyncOcrLog select 'TestDataFactoryLogOnPremise' as SourceName,Id as SourceID,0 as IsSyncForOnPrmise,1 as IsSyncForCloud,@activity as Activity

from inserted

end

If exists(select * from deleted) and not exists(Select * from inserted)

begin 

   SET @activity = 'DELETE';

  insert into TestSyncOcrLog select 'TestDataFactoryLogOnPremise' as SourceName,Id as SourceID,0 as IsSyncForOnPrmise,1 as IsSyncForCloud,@activity as Activity

from deleted

end

end

CONVERT SQL TABLE TO C# CLASS

CONVERT SQL TABLE TO C# CLASS

CONVERT SQL TABLE TO  JAVA CLASS

declare @TableName sysname = 'TableName'

declare @Result varchar(max) = 'public class ' + @TableName + '

{'

select @Result = @Result + '

    public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }

'

from

(

    select 

        replace(col.name, ' ', '_') ColumnName,

        column_id ColumnId,

        case typ.name 

            when 'bigint' then 'long'

            when 'binary' then 'byte[]'

            when 'bit' then 'bool'

            when 'char' then 'string'

            when 'date' then 'DateTime'

            when 'datetime' then 'DateTime'

            when 'datetime2' then 'DateTime'

            when 'datetimeoffset' then 'DateTimeOffset'

            when 'decimal' then 'decimal'

            when 'float' then 'double'

            when 'image' then 'byte[]'

            when 'int' then 'int'

            when 'money' then 'decimal'

            when 'nchar' then 'string'

            when 'ntext' then 'string'

            when 'numeric' then 'decimal'

            when 'nvarchar' then 'string'

            when 'real' then 'float'

            when 'smalldatetime' then 'DateTime'

            when 'smallint' then 'short'

            when 'smallmoney' then 'decimal'

            when 'text' then 'string'

            when 'time' then 'TimeSpan'

            when 'timestamp' then 'long'

            when 'tinyint' then 'byte'

            when 'uniqueidentifier' then 'Guid'

            when 'varbinary' then 'byte[]'

            when 'varchar' then 'string'

            else 'UNKNOWN_' + typ.name

        end ColumnType,

        case 

            when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier') 

            then '?' 

            else '' 

        end NullableSign

    from sys.columns col

        join sys.types typ on

            col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id

    where object_id = object_id(@TableName)

) t

order by ColumnId

set @Result = @Result  + '

}'

print @Result

DROP ALL TABLES FROM DB INCLUDING FOREIGN KEYS

 DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR

SET @Cursor = CURSOR FAST_FORWARD FOR

SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_SCHEMA + '].[' +  tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + '];'

FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1

LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME

OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql

WHILE (@@FETCH_STATUS = 0)

BEGIN

Exec sp_executesql @Sql

FETCH NEXT FROM @Cursor INTO @Sql

END

CLOSE @Cursor DEALLOCATE @Cursor

GO

EXEC sp_MSforeachtable 'DROP TABLE ?'

GO

Tabular Data Stream Protocol: TDS

https://msdn.microsoft.com/en-in/library/dd357628.aspx