Monday, June 25, 2018

ROW_NUMBER() OVER Dynamic @sortExpression

Step 1: Create Table

CREATE TABLE [dbo].[Student](
[ID] [int] NULL,
[Name] [varchar](50) NULL,
[City] [varchar](50) NULL
) ON [PRIMARY]
GO

Step 2: Insert Records in the table

insert into Student values (1,'Jack','california')
insert into Student values(2,'Lira','texas')
insert into Student values(3,'Aron','sweden')

ID Name City
1 Jack california
3 Aron sweden
2 Lira texas

Step 3: Execute Query

Declare @SortExp varchar(50) ='City'
Declare @strSql varchar(2000)

set @strSql='SELECT ROW_NUMBER() OVER ( ORDER BY '+@SortExp+' Asc) RowNumber,*
FROM Student Order By RowNumber'
exec (@strSql)

Result:
RowNumber ID Name City
1 1 Jack california
2 3 Aron sweden
3 2 Lira texas

5:44 AM by Dilip kakadiya · 0

How to run angular app

Introduction

ASP.NET Core + Angular in VS2017
Installation
Archistructure
Demo Project
Angular Core Concepts
Building SPAs with Components
What is a Component?
Your app is a tree of Component

Overview of ASP.NET Core + Angular in VS2017

What is ASP.NET Core. ASP.NET Core is an open source and cloud-optimized web framework for developing modern web applications that can be developed and run on Windows, Linux and the Mac. It includes the MVC framework, which now combines the features of MVC and Web API into a single web programming framework.
Angular Angular is a platform and framework for building client applications in HTML and TypeScript. Angular is itself written in TypeScript. It implements core and optional functionality as a set of TypeScript libraries that you import into your apps.

Installation

Download and install the latest version of Node.js from here
SQL Server 2014 or above
NET Core 2.1 Preview (Win, MacOS, and Linux)

1 .NET Core 2.1 Preview 1 SDK from here
2 .NET Core 2.1 Preview 1 Runtime here

Visual Studio 2017 Preview

1.https://www.visualstudio.com/vs/preview/

Angular Item Templates extension

Archistructure

Demo Project

Open vs2017 create new project >>Web >> select .Net Core >> Asp.net core web application

Select Angular app template and click to OK

Now right click on your ClientApp Folder and select open containing folder

Write cmd on path and enter and run > npmInstall

Now run your application and automatically restore your npm packages.
See your project structure given below.

Angular Core Concepts

Building SPAs with Components

What is a Component?

A component is a reusable object
Made up of: Code(ts class) + html Template

Your app is a tree of Components

Now see the output of your first project.

You can check my code from here

5:07 AM by Dilip kakadiya · 0

Find and Remove Duplicate Rows from a SQL Server Table

Delete Record from table without creating Temp table.

Step 1: Create Table

CREATE TABLE [dbo].[Student](
[ID] [int] NULL,
[Name] [varchar](50) NULL,
[City] [varchar](50) NULL
) ON [PRIMARY]
GO

Step 2: Insert Records in the table

insert into Student values (1,'Jack','california')
insert into Student values(1,'Jack','california')
insert into Student values(1,'Jack','california')
insert into Student values(2,'Lira','texas')
insert into Student values(2,'Lira','texas')

ID Name City

1 Jack california

2 Lira texas

Step 3: Execute Query

;

--Ensure that any immediately preceding statement is terminated with a semicolon above

WITH cte

AS (SELECT ROW_NUMBER() OVER (PARTITION BY ID, Name, City

ORDER BY ( SELECT 0)) RN

FROM Student)

delete FROM cte

WHERE RN > 1;

Result:

ID Name City

1 Jack california

2 Lira texas

12:14 AM by Dilip kakadiya · 0

How to Create alphanumeric sequence in sql

To create an alphanumeric sequence like this:

AAAA0000
AAAA0001
AAAA0002
AAAA0003
.
.
.
AAAA9999
AAAB0000
AAAB0001
.
.
.
ZZZZ9999

spAlphaNumericIDGeneration will return next alphanumeric number.

tblItinerary :  table Name
TagName :  fileld Name

CREATE PROCEDURE spAlphaNumericIDGeneration
AS
declare
@alphabet varchar(4),
@number int,
@alphanumeric varchar(6),
@strNumber varchar(6),
@strAlphabet varchar(6),
@strAlphaNumeric varchar(7),
@Year varchar(4),
@intYear int

BEGIN

select @alphanumeric=max(TagName) from tblItinerary
if @alphanumeric=''
BEGIN
set @alphanumeric='AAA000'
END
set @alphabet=SUBSTRING(@alphanumeric,1,3)
set @number=SUBSTRING(@alphanumeric,4,3)

select @Year=Datepart(YEAR,getdate())
set @intYear=SUBSTRING(@Year,3,2)

if @number=999
BEGIN
set @number=1

;WITH
Tens (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),
Thousands(N) AS (SELECT 1 FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),
Millions (N) AS (SELECT 1 FROM Thousands t1 CROSS JOIN Thousands t2),
Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions),
CTE1 (A) AS (SELECT CHAR(N+64) FROM Tally WHERE N between 1 and 26),
CTE2 (B) AS (SELECT c1.A + c2.A FROM CTE1 c1 CROSS JOIN CTE1 c2),
CTE3 (C) AS (SELECT c1.A + c2.A + c3.A FROM CTE1 c1 CROSS JOIN CTE1 c2 CROSS JOIN CTE1 c3),
CTE4 (D) AS (SELECT c1.A + c2.A + c3.A + c4.A FROM CTE1 c1 CROSS JOIN CTE1 c2 CROSS JOIN CTE1 c3 CROSS JOIN CTE1 c4),
CTE AS (SELECT A, RN = 1 FROM CTE1 UNION ALL
SELECT B, RN = 2 FROM CTE2 UNION ALL
SELECT C, RN = 3 FROM CTE3 UNION ALL
SELECT D, RN = 4 FROM CTE4)

SELECT top 1 @strAlphabet = A FROM CTE WHERE RN =3 and A > @alphabet order by A
END
ELSE
BEGIN
set @number=@number+1
set @strAlphabet = @alphabet
END
set @strNumber=right(replicate('0',3)+cast(@number as varchar(15)),3)

set @strAlphaNumeric=ltrim(rtrim(@strAlphabet)) + ltrim(rtrim(@strNumber)) + Char(64 +@intYear)

Select @strAlphaNumeric As ID
END

12:00 AM by Dilip kakadiya · 0

Sunday, June 24, 2018

How we can call a JavaScript function on the change of a Dropdown List in MVC?

Create a JavaScript method:

Invoke the method:

<%:Html.DropDownListFor(x => x.SelectedProduct, new SelectList(Model.Customers, “Value”, “Text”), “Please Select a Customer”, new { id = “ddlCustomers”, onchange=” DrpIndexChanged ()” })%>

10:48 PM by Dilip kakadiya · 0

How to change the action name in MVC?

“ActionName” attribute can be used for changing the action name. Below is the sample code snippet to demonstrate more –

[ActionName(“TestActionNew”)]

public ActionResult TestAction()

{

return View();

}

So in the above code snippet “TestAction” is the original action name and in “ActionName” attribute, name — “TestActionNew” is given. So the caller of this action method will use the name “TestActionNew” to call this action.

10:47 PM by Dilip kakadiya · 0

How to enable Attribute Routing?

Just add the method — “MapMvcAttributeRoutes()” to enable attribute routing as shown below

public static void RegistearRoutes(RouteCollection routes)

{

routes.IgnoareRoute(“{resource}.axd/{*pathInfo}”);

//enabling attribute routing

routes.MapMvcAttributeRoutes();

//convention-based routing

routes.MapRoute

(

name: “Default”,

url: “{controller}/{action}/{id}”,

defaults: new { controller = “Customer”, action = “GetCustomerList”, id = UrlParameter.Optional }

);

}

10:45 PM by Dilip kakadiya · 0

.Net programming