SQL : Add non cluster index in SQL table by query

create table tblSample2008
(
id int primary key not null identity(1,1),
name varchar(100),
city varchar(50)
)

CREATE NONCLUSTERED INDEX city_MyCustom_NonclusterIndex
ON tblSample2008 (city asc)



CREATE TABLE [dbo].[tblSample2008](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](100) NULL,
[city] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

generate next number

DECLARE @MAXID INT

SELECT  @MAXID=ID_MAX FROM SDMIS_STUDENT_MASTER_18_19_Mapping_with_dist WHERE Dist_code='2105'

UPDATE SDMIS_STUDENT_MASTER_18_19_SUNDERGARH_2105

 SET @MAXID = ID = @MAXID+1 

where ID is null and assesment_status='N' AND TC_Status IS NULL;

 

DECLARE @MAXXXX INT

SELECT @MAXXXX =MAX(ID) FROM SDMIS_STUDENT_MASTER_18_19_SUNDERGARH_2105

WHERE  assesment_status='N' AND TC_Status IS NULL;

--PRINT @MAXXXX

UPDATE SDMIS_STUDENT_MASTER_18_19_Mapping_with_dist SET ID_MAX=@MAXXXX

WHERE Dist_code='2105'

c# date format dd/MM/yyyy hh:mm AM/PM

today in format : 28/09/2018 7:18 PM

c# code: DateTime.Now.ToString("dd/MM/yyyy h:mm tt")

The Other format for today is as follows


DateTime.Now.ToString("MM/dd/yyyy") Result : 09/28/2018


DateTime.Now.ToString("dddd, dd MMMM yyyy"): Result : Friday, 28 September 2018


DateTime.Now.ToString("dddd, dd MMMM yyyy HH:mm:ss"): Result : Friday, 28 September 2018 19:36:45


DateTime.Now.ToString("MM/dd/yyyy HH:mm"): Result : 09/28/2018 19:36


DateTime.Now.ToString("MM/dd/yyyy hh:mm tt"): Result : 09/28/2018 07:36 PM


DateTime.Now.ToString("MM/dd/yyyy H:mm"): Result : 09/28/2018 19:36


DateTime.Now.ToString("MM/dd/yyyy h:mm tt"): Result : 09/28/2018 7:36 PM


DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss"): Result : 09/28/2018 19:36:45


DateTime.Now.ToString("MMMM dd"): Result : September 28


DateTime.Now.ToString("yyyy’-‘MM’-‘dd’T’HH’:’mm’:’ss.fffffffK"): Result : 2018’-‘09’-‘28’T’19’:’36’:’45.4574160-07:00


DateTime.Now.ToString("ddd, dd MMM yyy HH’:’mm’:’ss ‘GMT’"): Result : Fri, 28 Sep 2018 19’:’36’:’45 ‘G9T’


DateTime.Now.ToString("yyyy’-‘MM’-‘dd’T’HH’:’mm’:’ss"): Result : 2018’-‘09’-‘28’T’19’:’36’:’45


DateTime.Now.ToString("HH:mm"): Result : 19:36


DateTime.Now.ToString("hh:mm tt"): Result : 07:36 PM


DateTime.Now.ToString("H:mm"): Result : 19:36


DateTime.Now.ToString("h:mm tt"): Result : 7:36 PM


DateTime.Now.ToString("HH:mm:ss"): Result : 19:36:45


DateTime.Now.ToString("yyyy MMMM"): Result : 2018 September

Delete duplicate row in sql , T-SQL: Deleting all duplicate rows but keeping one [duplicate]

  WITH cte AS (
  SELECT child_code,
     row_number() OVER(PARTITION BY child_code ORDER BY child_code) AS 'Duplicatecount'
  FROM SDMIS_STUDENT_MASTER_18_19  where SchoolRegNo='1234567' and Class=6
   and isnull(child_code,'')<>'')
DELETE cte WHERE Duplicatecount > 1

sql date format in dd/MM/yyyy hh:mm:ss

Suppose today date is 26/09/2018  6:42:18:997PM then the related query is as below

SELECT CONVERT(CHAR(10),GETDATE(),103) + ' ' + RIGHT(CONVERT(CHAR(26),GETDATE(),109),14)



Other Date Format: I take today as parameter i.e. 28 Septmber 2018 07:36 PM(Indian Time)
The Format and C# results are as follows.




DateTime.Now.ToString("MM/dd/yyyy") Result : 09/28/2018


DateTime.Now.ToString("dddd, dd MMMM yyyy"): Result : Friday, 28 September 2018


DateTime.Now.ToString("dddd, dd MMMM yyyy HH:mm:ss"): Result : Friday, 28 September 2018 19:36:45


DateTime.Now.ToString("MM/dd/yyyy HH:mm"): Result : 09/28/2018 19:36


DateTime.Now.ToString("MM/dd/yyyy hh:mm tt"): Result : 09/28/2018 07:36 PM


DateTime.Now.ToString("MM/dd/yyyy H:mm"): Result : 09/28/2018 19:36


DateTime.Now.ToString("MM/dd/yyyy h:mm tt"): Result : 09/28/2018 7:36 PM


DateTime.Now.ToString("MM/dd/yyyy HH:mm:ss"): Result : 09/28/2018 19:36:45


DateTime.Now.ToString("MMMM dd"): Result : September 28


DateTime.Now.ToString("yyyy’-‘MM’-‘dd’T’HH’:’mm’:’ss.fffffffK"): Result : 2018’-‘09’-‘28’T’19’:’36’:’45.4574160-07:00


DateTime.Now.ToString("ddd, dd MMM yyy HH’:’mm’:’ss ‘GMT’"): Result : Fri, 28 Sep 2018 19’:’36’:’45 ‘G9T’


DateTime.Now.ToString("yyyy’-‘MM’-‘dd’T’HH’:’mm’:’ss"): Result : 2018’-‘09’-‘28’T’19’:’36’:’45


DateTime.Now.ToString("HH:mm"): Result : 19:36


DateTime.Now.ToString("hh:mm tt"): Result : 07:36 PM


DateTime.Now.ToString("H:mm"): Result : 19:36


DateTime.Now.ToString("h:mm tt"): Result : 7:36 PM


DateTime.Now.ToString("HH:mm:ss"): Result : 19:36:45


DateTime.Now.ToString("yyyy MMMM"): Result : 2018 September 

all in one windows with proper priority (drop all procedure in a database in sql server,drop all user view in sql server database ,, drop all function in sql server database

GO
---drop all procedure in a database in sql server
declare @userProcedureName varchar(2000)
declare drop_proc_cur cursor
for select [name] from sys.objects where type = 'p'
open drop_proc_cur
fetch next from drop_proc_cur into @userProcedureName
while @@fetch_status = 0
begin
--print @userProcedureName;
   exec('drop procedure [' + @userProcedureName + ']')
   fetch next from drop_proc_cur into @userProcedureName
end
close drop_proc_cur
deallocate drop_proc_cur
GO
GO
-----drop all user view in sql server database 
DECLARE @sqlExecute VARCHAR(MAX) = ''
        , @crlf VARCHAR(2) = CHAR(13) + CHAR(10) ;

SELECT @sqlExecute = @sqlExecute + 'DROP VIEW ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(v.name) +';' + @crlf
FROM   sys.views v

--PRINT @sqlExecute;
EXEC(@sqlExecute);

GO
--drop all function in sql server database 
Declare @sqlExecute NVARCHAR(MAX) = N'';
SELECT @sqlExecute = @sqlExecute + N' DROP FUNCTION '
                   + QUOTENAME(SCHEMA_NAME(schema_id))
                   + N'.' + QUOTENAME(name)
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%';
print @sqlExecute
Exec sp_executesql @sqlExecute
GO
----drop all user-defined types from sql server database
declare @sqlExecute12 NVARCHAR(MAX) = N'';

SELECT @sqlExecute12 = @sqlExecute12 + N' DROP type '
                   + QUOTENAME(SCHEMA_NAME(schema_id))
                   + N'.' + QUOTENAME(name)
FROM sys.types
WHERE is_user_defined = 1
--print @sqlExecute;
Exec sp_executesql @sqlExecute12

drop all user-defined types from sql server database

drop all user-defined types from sql server database

declare @sqlExecute NVARCHAR(MAX) = N'';

SELECT @sqlExecute = @sqlExecute + N' DROP type '
                   + QUOTENAME(SCHEMA_NAME(schema_id))
                   + N'.' + QUOTENAME(name)
FROM sys.types
WHERE is_user_defined = 1
--print @sqlExecute;
Exec sp_executesql @sqlExecute

drop all user view in sql server database

drop all user view in sql server database 

DECLARE @sqlExecute VARCHAR(MAX) = ''
        , @crlf VARCHAR(2) = CHAR(13) + CHAR(10) ;

SELECT @sqlExecute = @sqlExecute + 'DROP VIEW ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(v.name) +';' + @crlf
FROM   sys.views v

--PRINT @sqlExecute;
EXEC(@sqlExecute);

drop all function in sql server database

drop all function in sql server database 

Declare @sqlExecute NVARCHAR(MAX) = N'';
SELECT @sqlExecute = @sqlExecute + N' DROP FUNCTION '
                   + QUOTENAME(SCHEMA_NAME(schema_id))
                   + N'.' + QUOTENAME(name)
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%';
print @sqlExecute
Exec sp_executesql @sqlExecute
GO

drop all procedure in a database in sql server , select all procedure in a sql server database

drop all procedure in a database in sql server
declare @userProcedureName varchar(2000)
declare drop_proc_cur cursor
for select [name] from sys.objects where type = 'p'
open drop_proc_cur
fetch next from drop_proc_cur into @userProcedureName
while @@fetch_status = 0
begin
--print @userProcedureName;
   exec('drop procedure [' + @userProcedureName + ']')
   fetch next from drop_proc_cur into @userProcedureName
end
close drop_proc_cur

deallocate drop_proc_cur




 select all procedure in a sql server database 

 select [name] from sys.objects where type = 'p'

code snippet for constructor ctor , shortcut key to create constructor

write ctor inside class and double tab the constructor will be created automatically.

ctor then double tab.

Type "ctor" + TABTAB

SQL REPLACE FUNCTION

SELECT REPLACE( 'YourInputString', 'RepaceCharacterName', 'ReplaceValuToTheCharater' )
SELECT REPLACE('PABITRA MICROSOFT RESEARCH', 'MICROSOFT', 'GOOGLE');
---OUT PUT: PABITRA GOOGLE RESEARCH

sql merged OR merge statement , use while bulk data is updated

Instead of using cursor use Merged statement for better performance.

Here is an sample code

 CREATE DATABASE PABITRA_MERGE_DB;

 USE PABITRA_MERGE_DB;

CREATE TABLE dbo.TargetTableToUpdate  -- target
(
 Id INT PRIMARY KEY,
 TName VARCHAR(20),
 City varchar(50),
 TotalCount INT
);
CREATE TABLE dbo.SourceTableTobeMatched  -- source
(
 Id INT,
 SName VARCHAR(20),
 City varchar(50)

);

INSERT TargetTableToUpdate VALUES
  (1, 'PABITRA','USA', 0),
  (2, 'ARYA', 'BHUBANESWAR', 0),
  (3, 'BRAJA','BANGALORE', 0),
  (5, 'MICROSOFT','MICROSOFT COP', 0),
  (6, 'GOOGLE','GOOGLE COP', 0),
  (8, 'YAHOO','YAHOO COP', 0);

INSERT SourceTableTobeMatched VALUES
  (1, 'PABITRA','USA'),
  (2, 'PABITRA', 'BHUBANESWAR'),
  (3, 'PABITRA','BANGALORE'),
  (4, 'ARYA','MICROSOFT COP'),
  (5, 'ARYA','GOOGLE COP'),
  (6, 'BRAJA','YAHOO COP')



  MERGE TargetTableToUpdate T
USING SourceTableTobeMatched S
ON T.ID = S.ID
WHEN MATCHED THEN
  UPDATE
  SET T.TotalCount =T.TotalCount+1;

  SELECT * FROM TargetTableToUpdate;
  SELECT * FROM SourceTableTobeMatched;

sql check invalid date isdate() function

IF(ISDATE('2019-12-23')='0')
PRINT '1'--invalid date
ELSE
PRINT 'OK'---valid date

Age calculation in sql

SELECT DATEDIFF(YY,'1989-11-23',GETDATE())--1989-11-23(YYYY-MM-DD)

SQL GENERATE MAX ID OF A MIXED ID I.E. CHARATER WITH NUMBER

CREATE TABLE [dbo].[Mst_Emp](
[Emp_Id] [VARCHAR](10) NOT NULL PRIMARY KEY,
[CountryId] [int] NULL,
[StateId] [int] NULL,
[CityId] [int] NULL,
[Fst_Name] [varchar](50) NULL,
[Lst_Name] [varchar](50) NULL,
[Email] [varchar](50) NULL,
[Gender] [varchar](50) NULL,
[D_O_B] [datetime] NULL,
[Password] [varchar](50) NULL,
[CPassword] [varchar](50) NULL,
[Image] [varchar](max) NULL)

CREATE PROCEDURE USP_Mst_Emp
(
@ACTION VARCHAR(50)
)
AS BEGIN  --BEGIN OF PROC
IF(@ACTION='GET_MAX_ID')
BEGIN --BEGIN OF IF
   DECLARE @EMP_MAX VARCHAR(9),@MAX INT,@SETMAX VARCHAR(10),@Emp_Id VARCHAR(10)
          IF EXISTS(SELECT 1 FROM Mst_Emp)
            BEGIN
             SELECT @EMP_MAX=RIGHT(MAX(ISNULL(Emp_Id,'')),3) FROM Mst_Emp
                 SET @MAX=CONVERT(INT,@EMP_MAX)+1;
                 SET @SETMAX=CONVERT(VARCHAR,@MAX);
                 IF(LEN(@SETMAX)=1)
                  SET @Emp_Id='E00'+@SETMAX;
                 IF(LEN(@SETMAX)=2)
                   SET @Emp_Id='E0'+@SETMAX;
                 IF(LEN(@SETMAX)=3)
                   SET @Emp_Id='E'+@SETMAX;
            END
             ELSE
              BEGIN
               SET @Emp_Id=+'E001';
             END
   SELECT  @Emp_Id AS 'MAX_ID'
END --END OF IF


END--END OF PROCEDURE


--EXECUTING THE PROCEDURE
EXEC USP_Mst_Emp 'GET_MAX_ID'

GOVT EMPLOYEE RETIREMENT IN SQL QUERY

SELECT CONVERT(VARCHAR,DOB,103) AS DOB,CONVERT(VARCHAR,(SELECT RETIREDATE FROM GOVT_EMP_RETIREMENT(DOB)),103) RETIREDATE,
(SELECT RETIRESTATUS FROM GOVT_EMP_RETIREMENT(DOB)) RTDSTS
FROM TBL_EMPLOYEE

CREATE FUNCTION [dbo].[GOVT_EMP_RETIREMENT]
(
@PDOB DATETIME
)
RETURNS
@TBLRETIRESTS TABLE
(
RETIREDATE DATETIME,
RETIRESTATUS VARCHAR(16)
)
AS
BEGIN

INSERT INTO @TBLRETIRESTS
SELECT CASE WHEN ((DAY(DATEADD(yy,60,@PDOB)))> 1) THEN DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,(DATEADD(yy,60,@PDOB)))+1,0)) ELSE DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,(DATEADD(month,-1,(DATEADD(yy,60,@PDOB)))))+1,0)) END 'RETIREMENT DATE'
,CASE WHEN ((DAY(DATEADD(yy,60,@PDOB)))> 1) THEN
CASE WHEN DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,(DATEADD(yy,60,@PDOB)))+1,0)) > GETDATE() THEN 'Blue' ELSE 'RED' END
ELSE
CASE WHEN DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,(DATEADD(month,-1,(DATEADD(yy,60,@PDOB)))))+1,0)) > GETDATE() THEN 'Blue' ELSE 'RED' END END 'RTD STATUS'
RETURN
END

how to enable lazy loading entire project using entitty framework in .net framework I use in Context class

I set it in the constructor of  Context class  i.e. this.Configuration.LazyLoadingEnabled = false;

Below is the code snippet.

 public partial class PabitraEntities : DbContext
    {
        public PabitraEntities()
            : base("name=PabitraEntities")
        {         
            this.Configuration.LazyLoadingEnabled = false;
        }

      protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            throw new UnintentionalCodeFirstException();
        }

  public virtual DbSet<CountryMaster> CountryMasters { get; set; }
}

html control in array for looping

foreach (HtmlControl htm in new HtmlControl[] { trCRCCName, trCRCCIdentification, trCRCCMobile }) htm.Visible = false;

sql : Generate Row number i.e. Row_Number() in a table

CREATE TABLE TblGen_ROW_NUMBER
(
Name VARCHAR(100),
City VARCHAR(100)
)
INSERT INTO TblGen_ROW_NUMBER(Name,City)VALUES('PABITRA','BHUBANESWAR')
INSERT INTO TblGen_ROW_NUMBER(Name,City)VALUES('ASHWIN','CUTTACK')
INSERT INTO TblGen_ROW_NUMBER(Name,City)VALUES('ARRYA','NAKHARA')
SELECT ROW_NUMBER() OVER(ORDER BY Name)AS ID, Name,City FROM TblGen_ROW_NUMBER
SELECT ROW_NUMBER() OVER(ORDER BY Name DESC)AS ID, Name,City FROM TblGen_ROW_NUMBER

sql : last date of the month

SELECT DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, GETDATE()) + 1, 0))
SELECT convert(varchar,DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, GETDATE()) + 1, 0)),103)

DataTable to JSON using c#, Ado.net

use this code :

Out Put :

used namespace 

using System.Collections.Generic;
using System.Web.Script.Serialization;

using System.Data;

public string DataTableToJSON()
    {
        DataTable dtCustomer = new DataTable();
        dtCustomer.Columns.AddRange(
                              new DataColumn[]
                              { new DataColumn("ID", typeof(int)),
                                new DataColumn("Name", typeof(string)),
                                new DataColumn("City", typeof(string))
                              }
                              );
        // Add value to the related column
        dtCustomer.Rows.Add(1001, "PABITRA BEHERA","Bhubaneswar");
        dtCustomer.Rows.Add(1002, "Arya Kumar", "Cuttack");
        dtCustomer.Rows.Add(1003, "Mr Roket(Braja)", "Nayagard");
        dtCustomer.Rows.Add(1004, "Mr Trupti", "Bhadrak");
     
        JavaScriptSerializer jsSerializer = new JavaScriptSerializer();
        List<Dictionary<string, object>> parentRow = new List<Dictionary<string, object>>();
        Dictionary<string, object> childRow;
        foreach (DataRow row in dtCustomer.Rows)
        {
            childRow = new Dictionary<string, object>();
            foreach (DataColumn col in dtCustomer.Columns)
            {
                childRow.Add(col.ColumnName, row[col]);
            }
            parentRow.Add(childRow);
        }
        return jsSerializer.Serialize(parentRow);

    }


Call the function in page load
i.e.
protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            Response.Write(DataTableToJSON()); 
        }

    }

HTML5 required attribute Custom user message

Scenario where I need the Custom Message

Here I used an input control  i.e. to input user as Customer name.

<input type="text" id="txtCustomerName"  required>

For validation  I need show " Customer name is required  !" where as the current input show me an message like "please fill out this field."

So I need to change the default error message to custom error message. My custom error message with tag is as follows.


<input type="text" id="txtCustomerName" required placeholder="Enter Customer name"
 oninvalid="this.setCustomValidity('Customer name is required  !')" oninput="setCustomValidity('')">




Details Code :

<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width">
  <title>JS Bin</title>
</head>
<body>
<form >
<input type="text" id="txtCustomerName"  required>
  <br>
  <br>
<input type="text" id="txtCustomerName" 
       required placeholder="Enter Customer name"

 oninvalid="this.setCustomValidity('Customer name is required  !')"
       oninput="setCustomValidity('')">
<br>
  <br>
 <input type="submit" value="SUBMIT">

</form>
</body>
</html>