Access Modifiers

Access Modifiers

It is very important to understand when and where to use access modifiers.

1) private

2) public

3) protected

4) internal

5) protected internal

1) private : It is visible to only the same class .You cannot access the variable from any other class, even by creating an object or by inheriting it to a child class.

eg:

Class Parent

private Var A

2) public : It is visible through out the project and the variable can be accessed from other project.

eg:

Class Parent

public Var A

3) protected : It is visible inside the class. It cannot be accessed directly from other class. It has to be inherited to access it.

eg:

Class Parent

protected Var A

Class Child

Inherits Parent

base.A

4) internal : It is visible all over the project except from other project or applications

eg:

Class Parent

internal Var A

5) protected internal : It is visible all over the project except from other procect or applications. It can be accessed in other project only by inheriting the base class.

eg:

Class App1.Parent

protected internal Var A

Class App2.Child

Inherits Appl1.Parent

base.A

I am sorry for being very short in defining.

Stored Procedure

It is a precompiled set of sql statements. It is normally used where you have to work of big or complex queries or have some checks before executing any queries. Stored Procedures are not case sensitive.

Create a stored procedure

CREATE PROCEDURE spGETDate

AS

SELECT GETDATE() AS TodaysDate

Alter a stored procedure

It is used when you have to make some changes to the existing procedure.

ALTER PROCEDURE spGETDate

AS

SELECT GETDATE() + 1 AS NextDate

Passing variables to a stored procedure

You can pass any number of variables,but the variables should be defined with the data types and the variable name should be preceded with an @ symbol.

CREATE PROCEDURE spGETDate

@daystoaddorsubtract INT

AS

SELECT GETDATE() AS TodaysDate,GETDATE() + @daystoaddorsubtract AS 'ModifiedDate'

Calling the stored procedure

Procedure name and the values passed as parameters

spGETDATE 29

Stored Procedure with many checks and computation.

I am providing a basic example. many complex procedures can be written as per the requirement.

This procedure checks whether the passed data exist. If exist then it returns a -1 . If the data doesnt exist it inserts the data and returns the last inserted identity value. If the insert statement fails then it will return a 0 to indicate an error.

Thanks to Roopesh Thakkar for giving a hint about this interesting stored procedure.

Note: To run this procedure you should have a table named mytable with columns id,Vname,VAddress. id is a primary key with identity 1 and Vname and VAddress are varchar with size 1000

CREATE PROCEDURE spInsertMYTABLE

@Name VARCHAR(1000),

@Address VARCHAR(1000)

AS

IF NOT EXISTS (SELECT id FROM mytable WHERE VName = @Name)

BEGIN

DECLARE @error INT

INSERT INTO mytable(VName,VAddress)

VALUES(@Name,@Address)

SET @error = @@error

IF @error=0

SELECT scope_identity() AS 'ReturnValue'

ELSE

SELECT 0 AS 'ReturnValue'

END

ELSE

SELECT -1 AS 'ReturnValue'

I really liked the concept of stored procedures. It will make your life more simpler when you deal with complex and big queries.