div scroll

<div  style="height:500px;overflow-y: scroll;" >
</div>

Create Log file on the Same Directory

protected void btnClick_Click(object sender, EventArgs e)
        {
            int x = 10;
            TextBox1.Text = "";
            TextBox1.Text = "wwww";
            TextBox1.Text = "111111111111111111111111";        
             try
            {
                x = x + Convert.ToInt32(TextBox1.Text);
            }
            catch (Exception ex)
            {            
                System.IO.File.WriteAllText(Server.MapPath("Error/Date_"+DateTime.Now.ToString("dd-MM-yyyy")+"-ErrorLog-Time-HHmmss-" + DateTime.Now.ToString("HH-mm-ss") + ".txt"), ex.ToString());
             

            }
        }

Sql Trigger Insert,Update,Delete

create table tblMain
  (
   Id int not null Primary Key identity(1,1) ,
   Name varchar(100),
   dflag int,
   userId varchar(20)
    )


create table tblMain_Image
  (
  AutoId bigint not null Primary key identity(1,1),
    Id int  not null,
    Name varchar(100),
    userId varchar(20),
    dflag int,
Sys_Date datetime default getdate(),
sys_Time  varchar(20) default RIGHT(CONVERT(CHAR(20), GETDATE(), 22), 11)--For AM and PM
    )
----------------------------------INSERT----------------------------  
create trigger [dbo].[Triger_Insert_tblMain] ON  [dbo].[tblMain] FOR INSERT
AS
BEGIN
     set nocount on
    insert into [dbo].[tblMain_Image]
           (Id,Name,userId,dflag)
    SELECT Id,Name,userId,0
    FROM inserted;
 
END
----------------------------------INSERT-----------------------------------


----------------------------------UPDATE---------------------------------
create trigger [dbo].[Triger_Update_tblMain] ON  [dbo].[tblMain] FOR UPDATE
AS
BEGIN
    set nocount on
    insert into [dbo].[tblMain_Image]
           (Id,Name,userId,dflag)
    SELECT Id,Name,userId,1
    FROM inserted;
 
END
---------------------------------UPDATE---------------------------------


---------------------------DELETE------------------------------------
create  trigger Triger_Delete_tblMain on  [dbo].[tblMain]
FOR DELETE
AS
begin
 set nocount on
     insert into [dbo].[tblMain_Image]
           (Id,Name,userId,dflag)
    SELECT Id,Name,userId,2
    FROM deleted;
end
---------------------------DELETE------------------------------------

SQL Setting Default value of Date and time in Table

create table Mst_Category
(
Cat_Auto bigint not null primary key identity(1,1),
Cat_Id varchar(20) not null,
Cat_Name varchar(100),
userId varchar(20),
dflag int,
Sys_Date datetime default getdate(),
sys_Time  time default CONVERT(time, GETDATE())
)


create table Mst_Category
(
Cat_Auto bigint not null primary key identity(1,1),
Cat_Id varchar(20) not null,
Cat_Name varchar(100),
userId varchar(20),
dflag int,
Sys_Date datetime default getdate(),
sys_Time  varchar(20) default RIGHT(CONVERT(CHAR(20), GETDATE(), 22), 11)--For AM and PM
)

Data Annotations in entity framework

The following topics are covered on this page:

• The model
• Key
  • Composite Keys
• Required
• MaxLength and MinLength
• NotMapped
• ComplexType
• ConcurrencyCheck
• TimeStamp
• Table and Column
• DatabaseGenerated
• Index
  • Multiple-Column Indexes
• Relationship Attributes: InverseProperty and ForeignKey

click here for more 

• https://msdn.microsoft.com/en-us/data/jj591583.aspx

sql server Differnce between varchar and nvarchar

varchar DataType
-----------------------
Varchar means variable characters and it is used to store non-unicode characters. It will allocate the memory based on number characters inserted. Suppose if we declared varchar(50) it will allocates memory of 0 characters at the time of declaration. Once we declare varchar(50) and insert only 10 characters of word it will allocate memory for only 10 characters.

nvarchar DataType
----------------------
 nvarchar datatype same as varchar datatype but only difference nvarchar is used to store Unicode characters and it allows you to store multiple languages in database. nvarchar datatype will take twice as much space to store extended set of characters as required by other languages.

So if we are not using other languages then it’s better to use varchar datatype instead of nvarchar 

Separate quey and parameters in sql sp_executesql

DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @IntVariable INT
DECLARE @Lastlname varchar(30)
SET @SQLString = N'SELECT @LastlnameOUT = max(lname)
                   FROM pubs.dbo.employee WHERE job_lvl = @level'
SET @ParmDefinition = N'@level tinyint,
                        @LastlnameOUT varchar(30) OUTPUT'
SET @IntVariable = 35
EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@level = @IntVariable,
@LastlnameOUT=@Lastlname OUTPUT
SELECT @Lastlname

sp_executesql having multiple parameters

create table   Students
 (
 StudentId int primary key not null
 ,StudentName varchar(200) not null  
  )
insert into Students(StudentId,StudentName)values(1,'Pabitra Behera')
insert into Students(StudentId,StudentName)values(2,'Pabitra')
insert into Students(StudentId,StudentName)values(3,'d')
insert into Students(StudentId,StudentName)values(1,'Pabitra')

DECLARE @ExecStr NVARCHAR(4000),@studentId int,@Name varchar(50) ;
set @studentId=1
set @Name='Pabitra Behera'
SELECT @ExecStr = 'SELECT * FROM dbo.Students where StudentId=1 and StudentId=@studentId and StudentName=@Name';
EXEC sp_executesql @ExecStr,N'@studentId int,@Name varchar(50)',@studentId,@Name

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

DECLARE @ExecStr NVARCHAR(4000),@studentId int,@Name varchar(50) ;
set @studentId=1
set @Name='Pabitra Behera'
SELECT @ExecStr = 'SELECT * FROM dbo.Students where StudentId=@studentId and StudentName=@Name';
EXEC sp_executesql @ExecStr,N'@studentId int,@Name varchar(50)',@studentId,@Name
--------------------------------------------------------------------------------------

sp_executesql in procedure with separate query,parameter

CREATE PROCEDURE Myproc-- 'pabitra','@parm1OUT',''
    @parm varchar(10),
    @parm1OUT varchar(30) OUTPUT,
    @parm2OUT varchar(30) OUTPUT
    AS
      SELECT @parm1OUT='parm 1' + @parm
     SELECT @parm2OUT='parm 2' + @parm
GO
DECLARE @SQLString NVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @parmIN VARCHAR(10)
DECLARE @parmRET1 VARCHAR(30)
DECLARE @parmRET2 VARCHAR(30)
SET @parmIN=' returned'
SET @SQLString=N'EXEC Myproc @parm,
                             @parm1OUT OUTPUT, @parm2OUT OUTPUT'
SET @ParmDefinition=N'@parm varchar(10),
                      @parm1OUT varchar(30) OUTPUT,
                      @parm2OUT varchar(30) OUTPUT'

EXECUTE sp_executesql
    @SQLString,
    @ParmDefinition,
    @parm=@parmIN,
    @parm1OUT=@parmRET1 OUTPUT,@parm2OUT=@parmRET2 OUTPUT

SELECT @parmRET1 AS "parameter 1", @parmRET2 AS "parameter 2"
go
drop procedure Myproc

Alternative way for execute query like storeprocedure

 create table   Students
 (
 StudentId int primary key not null
 ,StudentName varchar(200) not null    
  )
insert into Students(StudentId,StudentName)values(1,'d')
               insert into Students(StudentId,StudentName)values(4,'Pabitra')
select * from Students

One parameter 

DECLARE @ExecStr NVARCHAR(4000);
SELECT @ExecStr = 'SELECT * FROM dbo.Students WHERE StudentName LIKE @StudentName';
EXEC sp_executesql @ExecStr, N'@StudentName varchar(15)', 'd';



More than one parameter 
DECLARE @ExecStr NVARCHAR(4000);
SELECT @ExecStr = 'SELECT * FROM dbo.Students WHERE StudentName LIKE @StudentName and StudentId LIKE @StudentId';
EXEC sp_executesql @ExecStr, N'@StudentName varchar(15),@StudentId int', 'd', 1;

like operator in sql server 2008

 I want to search with Example : Pabitra Behera
-----------------------------------------------
Case 1: Search From Start
select * from Mst_Emp where Cat_Name like  'pabitra'+'%'--Result: Pabitra Behera
select * from Mst_Emp where Cat_Name like  'Behera'+'%'---Result: No Result
create Procedure Ret_EmployeeData
as begin
select * from Mst_Emp where Cat_Name like  'pabitra'+'%'
end
-----------------------

Case 1: Search From Any Position
select * from Mst_Emp where Cat_Name like  '%'+'pabitra'+'%'--Result :Pabitra Behera
select * from Mst_Emp where Cat_Name like  'Behera'+'%'--Result :Pabitra Behera
create Procedure Ret_EmployeeData
as begin
select * from Mst_Emp where Cat_Name like  '%'+'pabitra'+'%'--Result :Pabitra Behera
end