Stored Prcocedure

 USE [DeviceDB]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[sp_RptNewAccVelocity]

@SubscriberId int,

@ReportLength int = null,

@Order char(30) = null

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

DECLARE @SQL_STATEMENT nvarchar(3000);

SET @SQL_STATEMENT = 'SELECT c.CreatedDate AS ''AccCreatedDate'', b.AccountId, c.AccountCode, a.DeviceId FROM dbo.tblTransaction a WITH(NOLOCK) INNER JOIN ( ';

SET @SQL_STATEMENT += 'SELECT MIN(CreatedDate) AS CreatedDate, AccountId FROM dbo. tblTransaction WITH(NOLOCK)';

SET @SQL_STATEMENT += 'WHERE SubscriberId = '+ CAST(@SubscriberId as varchar(20)) + ' ';

   

IF (@ReportLength IS NOT NULL)

SET @SQL_STATEMENT += 'AND CreatedDate >= DATEADD(day, - '+ CAST(@ReportLength as varchar(20))   +', GETUTCDATE()) ';

SET @SQL_STATEMENT += 'GROUP BY AccountId ) b ON a.AccountId = b.AccountId AND a.CreatedDate = b.createdDate ';

SET @SQL_STATEMENT += 'INNER JOIN dbo.tblAccount c WITH(NOLOCK) ON c.AccountId=b.AccountId ';

      

SET @SQL_STATEMENT += 'WHERE a.SubscriberId = '+ CAST(@SubscriberId as varchar(20)) + ' ';

   

IF (@ReportLength IS NOT NULL)

SET @SQL_STATEMENT += 'AND c.CreatedDate >=  DATEADD(day, - '+ CAST(@ReportLength as varchar(20))   +', GETUTCDATE()) ';

IF (@Order IS NOT NULL)

SET @SQL_STATEMENT += 'ORDER BY ' + CAST(@Order as varchar(30)) + ' ';

EXEC sp_executesql @SQL_STATEMENT; // print @SQL_Statement

END

Java script Obfuscator Tool

https://www.javascriptobfuscator.com/Javascript-Obfuscator.aspx

Alert allow copy

prompt("Copy to clipboard: Ctrl+C, Enter", crypted);

Encryption in Javascript

<script src="Scripts/tripledes.js"></script>

<script src="Scripts/mode-ecb.js"></script>

function encrypt() {

    var keyHex = CryptoJS.enc.Utf8.parse("mkadm11n");

    var crypted = CryptoJS.DES.encrypt("Message", keyHex, {

        mode: CryptoJS.mode.ECB,

        padding: CryptoJS.pad.Pkcs7

    });

}

Insert 1M Records by SQL Server

declare @id int

declare @accountID bigint

declare @IP varchar(20)

select @id = 1

while @id <= 1000000

begin

//Insert 1st Table

INSERT INTO tblE2Account values('TestAccount' + CAST(@id AS VARCHAR(10)) , 'TestName',14,1,'E2TestBY',GETDATE(),'E2TestBY',GETDate(), NULL,0,0,'63,119','10.101.1.115',2)

// Take ID 

Select @accountID = Max(E2AccountId) From tblE2Account where CreatedBy='E2TestBY'

   
// Take ID and put in next Insert

INSERT INTO tblE2IP values('999.9.9.'+ CAST(@id AS VARCHAR(10)) , '23.1167,113.25','No Hostname','Kuala Lumpur','Kuala Lumpur','MY','59200','AS38199 Macro Lynx Sdn Bhd, Internet Service Provider, Malaysia',1,1,'E2TestBY',GETDATE(),NULL,NULL)

Select @IP = '999.9.9.' + CAST(@id AS VARCHAR(10))

INSERT INTO tblE2Transaction values(@IP, null, 2,14,@accountID,1,'1010017387,20.00,0','E2TestBY',GETDATE(),NULL,NULL)

select @id = @id + 1

end


//Avarage 30k records int 20 mins

Dictionary

static readonly Dictionary<string, DeviceType> MappedDeviceType;

static fnMapDeviceType()

{

    MappedDeviceType = new Dictionary<string, DeviceType>();

    MappedDeviceType.Add("Windows NT 6.3", DeviceType.Windows);

    MappedDeviceType.Add("Windows NT 6.3; WOW64", DeviceType.Windows);

    MappedDeviceType.Add("Windows NT 10.0", DeviceType.Windows);

    MappedDeviceType.Add("Windows NT 10.0; WOW64", DeviceType.Windows);

    MappedDeviceType.Add("CPU iPhone OS 9_3_2 like Mac OS X", DeviceType.Iphone);

}

//Mapping Method

public static DeviceType MapDeviceType(string device)

{

    DeviceType mapValue = DeviceType.Unknown;

    if (!string.IsNullOrWhiteSpace(device))

    {

        if (Enum.TryParse(device, true, out mapValue))

            return mapValue;

        if (MappedDeviceType.TryGetValue(device, out mapValue))

            return mapValue;

    }

    return mapValue;

}

---------------------

//Contain en with key

   string word = "en/my";

            var mappingA = new Dictionary<string, string>()

            {

                { "en", "en-gb" },

                { "cn", "cn-zh" },

            };

string listPerson = mappingA.Where(x => word.Contains(x.Key)).Select(y => y.Value).FirstOrDefault();

WCF Data Access Layer

Entity Framework
It's an Object Relational Mapping (ORM) Tool.

• Communicate with Database
• Map database data to object-oriented data.

Entity Framework 3 Approach

Database First approach 
Create database with tables, columns, relations etc. and Entity framework will generate Model classes (Business entities) and Data Access Layer code.

Model First approach
Model classes and relationship between them will be defined manually using Model designer in Visual studio, and Entity Framework will generate Data Access Layer and Database with tables, columns, relations automatically.


Code First approach 
Manually POCO classes (simple .NET classes) will be created. Relationship between those classes will be defined by means of code.When the application executes for the first time Entity framework will generate Data Access Layer and Database with tables, columns and relations automatically in the database server.


DBSet
The collection of all the entities that query from the database.