December 29, 2012

Static Members


Static Members

  • static class , a method , members can be defined with the help of static keyword.
  • After defining a member as a static, member can be directly invoked by using class.
  • so static members are accessed without creating a instance of class.
  • They are slightly faster than instance methods
  • Static class contains only static methods and static members only.
  • So instance of the static members can not be created with new keyword.


            Eg : System.Math class is a static method.

            Another Example

namespace Persons
{

    public static class Employee
    {

        public static string ManagerName()
        {
            return "Sandeep";
        }
    }
}


In Code behind file.


protected void Page_Load(object sender, EventArgs e)
{

     string sManagerName = Persons.Employee.ManagerName();

}


December 24, 2012

@@TRANCOUNT


@@TRANCOUNT

 

@@TRANCOUNT Command returns no of active transactions in current connection / session

 

PRINT '@@TRANCOUNT'

PRINT @@TRANCOUNT

BEGIN TRAN

      PRINT 'FIRST TRANSCTION STARTED'

      PRINT 'OPEN TRANSCTION : '

      PRINT @@TRANCOUNT

COMMIT

PRINT 'FIRST TRANSCTION ENDED'

PRINT 'NOW OPEN TRANSCTION : '

      PRINT @@TRANCOUNT

Stored Procedure and Function in SQL

Difference between Stored procedure and functions in SQL Server



Sr No
 
Storde Procedures
Functions
1
Can be used in Select Statements
No
Yes
 
 
 
 
2
Can take input Parameters
yes
yes
 
 
 
 
3
Can take output parameters
yes
No
 
 
 
 
4
text, ntext, image & timestamps
returns
can not returns
 
 
 
 
5
Input / Output Parameters
both
input
 
 
 
 
6
Insert / UPDATE / Delete
allowed
Not Allowed
 
 
 
 
7
DML
allowed
Not Allowed
 
 
 
 
8
Select
 
only
 
 
 
 
9
Calling type
in SP Function can be called
Vice a versa not possible
 
 
 
 
10
Transaction
handled
Not handled
          11
WHERE/HAVING/SELECT
not allowd
can be used

December 22, 2012

SQL Constraints

SQL Constraints


Constraints are defined when a table is created or table is modified.

Constraints are used to limit the type of data that can go into a table.

Various types of constraints are:

·         NOT NULL
·         UNIQUE
     ·         PRIMARY KEY
    
     ·         FOREIGN KEY

·         CHECK
     ·         DEFAULT

SQL NOT NULL Constraint

NOT NULL Constrains specifies column does not accept / contain NULL values.

The NOT NULL constraint enforces a field to must contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.

The following SQL enforces the "[EmployeeId]" column and the "EmployeeCode" column to not accept NULL values:

CREATE TABLE [dbo].[EmployeeMaster](
                [EmployeeId] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
                [EmployeeCode] [varchar](30) NOT NULL,
                [FirstName] [varchar](100)  NULL
                 
 CONSTRAINT [PK_EmployeeMaster] PRIMARY KEY CLUSTERED
(
                [EmployeeId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY])

 


SQL UNIQUE Constraint


The UNIQUE constraint uniquely identifies each record in a database table.

The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns.

You can create  many UNIQUE Key constraints per table, but only one PRIMARY KEY per table.

SQL PRIMARY KEY Constraint


The PRIMARY KEY constraint uniquely identifies each record in a database table.

Primary keys must contain unique values.

A primary key column cannot contain NULL values.

Each table should have a primary key, and each table can have only ONE primary key.

SQL FOREIGN KEY Constraint


A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

Let's illustrate the foreign key with an example. Look at the following two tables:

DeptMapping

DeptID
EmpID
CreatedBy
1
1
sandeepm
2
1
sandeepm

EmployeeMaster

EmpID
Name
Age
1
sandeep
26
2
ABC
20

Here in above 2 tables “EmpID” is a primary key in EmployeeMaster Table, where as DeptID is a primary key in DeptMapping Table

 

Relation between these 2 tables can be created by foreign key.

Here EmpID column is referred as foreign key to DeptMapping Table

 So that the EmpID column in DeptMapping table can have only values from EmployeeMaster Table

 
SQL DEFAULT Constraint

The DEFAULT constraint is used to insert a default value into a column.

The default value will be added to all new records, if no other value is specified.

 

SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[DeptMapping](
                [DepartmentID] [int] NOT NULL,
                [EmployeeID] [numeric](18, 0) NOT NULL,
                [CreatedBy] [varchar](50) NULL,
                [CreatedDate] [datetime] NULL,
 CONSTRAINT [PK_DeptMapping] PRIMARY KEY CLUSTERED
(
                [DepartmentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
 
 
ALTER TABLE [dbo].[DeptMapping] ADD  CONSTRAINT [DF_DeptMapping_CreatedDate]  DEFAULT (getdate()) FOR [CreatedDate]
GO

Here column CreatedDate has been mapped with a default constrains, so when the values are not passed while inserting a data then default values are stored in column

In this case getdate() method is used.

So when new record inserted into table System date stored in column