Akram@microsoft786: May 2015

Friday, 29 May 2015

Numeric Validation

<script type="text/javascript">
     function NumericValidation(eventObj) {
         var keycode;

         if (eventObj.keyCode) //For IE
             keycode = eventObj.keyCode;
         else if (eventObj.Which)
             keycode = eventObj.Which; // For FireFox
         else
             keycode = eventObj.charCode; // Other Browser

         if (keycode != 8) //if the key is the backspace key
         {
             if (keycode < 48 || keycode > 57) //if not a number
                 return false; // disable key press
             else
                 return true; // enable key press
         }
     }
    </script>

Friday, 22 May 2015

Random Number in Sql Server

Random Number in Sql Server
--------------------------

declare @RandomNumber bigint
SELECT @RandomNumber= CAST(RAND() *

100000000 AS bigint)
print @RandomNumber

Google Map Current Loacatation

Copy the Code And Past in Aspx Page and Run You Find the Current Locatation
------------------------------------------------------------------

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default3.aspx.cs" Inherits="Default3" %>

<!DOCTYPE html>
<html>
    <head>
        <script src="http://maps.google.com/maps/api/js?sensor=false">
        </script>
        <script>
            if (navigator.geolocation) {
                navigator.geolocation.getCurrentPosition(showCurrentLocation);
            }
            else {
                alert("Geolocation API not supported.");
            }

            function showCurrentLocation(position) {
                var latitude = position.coords.latitude;
                var longitude = position.coords.longitude;
                var coords = new google.maps.LatLng(latitude, longitude);

                var mapOptions = {
                    zoom: 15,
                    center: coords,
                    mapTypeControl: true,
                    mapTypeId: google.maps.MapTypeId.ROADMAP
                };

                //create the map, and place it in the HTML map div
                map = new google.maps.Map(
                document.getElementById("mapPlaceholder"), mapOptions
                );

                //place the initial marker
                var marker = new google.maps.Marker({
                    position: coords,
                    map: map,
                    title: "Current location!"
                });
            }
        </script>
    </head>
    <style>
    #mapPlaceholder {
        height: 400px;
        width: 700px;
    </style>
    <body>
        <div>
        <h2>HTML5 Show Location on GoogleMap Sample</h2>
        <div id="mapPlaceholder"></div>
        </div>
    </body>
</html>

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

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

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

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

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 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
)

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 time default CONVERT(time, GETDATE())
)
----------------------------------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------------------------------------

Akram@microsoft786