All about Stored Procedures

Example

 

CREATE PROCEDURE PROC_NAME

(

          @MYNAME VARCHAR(10),

          @YOURNAME VARCHAR(10),

          @RESULT1 VARCHAR(10) OUTPUT,

          @RESULT2 VARCHAR(10) OUTPUT

)

AS

BEGIN

SET NOCOUNT ON;   

         INSERT INTO TABLE_NAME(MYNAME,YOURNAME) VALUES(@MYNAME,@YOURNAME);

           SET @RESULT1 = @@IDENTITY

          SET @RESULT2=SCOPE_IDENTITY()

END

 

DECLARE @RESULT1 INT

DELCARE @RESULT2 INT

EXEC PROC_NAME ‘ST1’,’ST2’,@RESULT1 OUTPUT,@RESULT2 OUTPUT

Code Access Security

.NET has two kinds of security:

1. Role Based Security 2. Code Access Security

CLR allows code to perform only those operations that the code has permission to perform. So CAS is the CLR's security system that enforces security policies by preventing unauthorized access to protected resources and operations. Using the Code Access Security, you can do the following:

• Restrict what your code can do
• Restrict which code can call your code
• Identify code

We can create code groups to meet our requirements based on the evidence provided by .NET Framework. Site, Strong Name, Zone, URL are some of the types of evidence.

 

 

Yield keyword in C#

Yield Keyword in .net

Yield keyword is used to return a value in between the iteration of a loop.

Ex:-

public static IEnumerable<int> Square(int min, int max)

{

    for (int i = min; i <= max; i++)

    {

        yield return i*i;

    }

}

Now each time this method is called it will return the square of current value within a given range and it’s also maintains the state between calls.

foreach (int i in Square(1, 10))

{

    Response.Write(i.ToString() + " ");

}

output will be like 1,4,9,16,25,36,49,64,81,100.

 

 

Few ADO.NET points for reference

FieldCount : is used to get number of colums in a row by using sqldatareader

IsClosed is used to find whether the sqldatareader is closed or not.

Good Example of stored procedure

//Create the Connection Object
SqlConnection ConnectionObject = new SqlConnection(ConnectionString);
//Create the Command Object
SqlCommand CommandObject = new SqlCommand("StoredProcedureName", ConnectionObject);
//Specify to CommandObject that you intend to execute a Stored Procedure
CommandObject.CommandType = CommandType.StoredProcedure;
//Create an SQL Parameter object
SqlParameter ParameterObject = new SqlParameter();
//Specify the name of the SQL Parameter
ParameterObject.ParameterName = "Parameter1";
//Assign the Parameter value
ParameterObject.Value = "Some Value";
//Specify the Database DataType of the Parameter
ParameterObject.DbType = DbType.String;
//Specify the type of parameter - input-only(default), output-only, bidirectional
ParameterObject.Direction = ParameterDirection.Input;
//Associate the Parameter to the Command Object
CommandObject.Parameters.Add(ParameterObject);
//Open the connection
ConnectionObject.Open();
//Execute the command
int Records_Affected = CommandObject.ExecuteNonQuery();
//Close the Connection
ConnectionObject.Close();


Executing and getting data from two queries using datareader

- By using NextResult() method
- Example

//Create the SQL Query with 2 Select statements
string SQLQuery = "Select * from Customers;Select * from Employees;";
//Create the Connection Object
SqlConnection ConnectionObject = new SqlConnection(ConnectionString);
//Create the Command Object
SqlCommand CommandObject = new SqlCommand(SQLQuery, ConnectionObject);
//Open the connection
ConnectionObject.Open();
//Execute the command. Now reader object will have 2 tables of data.
SqlDataReader ReaderObject = CommandObject.ExecuteReader();
//Loop thru the tables in the DataReader object
while (ReaderObject.NextResult())
{
while (ReaderObject.Read())
{
//Do Something
}
}
//Close the Reader
ReaderObject.Close();
//Close the Connection
ConnectionObject.Close();


Uses of using Stored procedures

1) Better Performance - as they are precompiled one's
2) Security - we can specify who has the rights to execute
3) Reduce Network traffic - Just we need to specify the name of SP instead of sending a big sql query

Differences between Datareader and Dataset

DataReader
1. DatReader works on a Connection oriented architecture.
2. DataReader is read only, forward only. It reads one record at atime. After DataReader finishes reading the current record, it moves to the next record. There is no way you can go back to the previous record. So using a DataReader you read in forward direction only.
3. Updations are not possible with DataReader.
4. As DataReader is read only, forward only it is much faster than a DataSet.
DataSet
1. DataSet works on disconnected architecture.
2. Using a DataSet you can move in both directions. DataSet is bi directional.
3. Database can be updated from a DataSet.
4. DataSet is slower than DataReader.

Access modifiers in C#

Access Modifiers
=================

Public
can be accessed by any other code in the same assembly or another assembly that references it.

Private
can only be accessed by code in the same class or struct.

Protected
can only be accessed by code in the same class or struct, or in a derived class.

Internal
can be accessed by any code in the same assembly, but not from another assembly.

Protected Internal
can be accessed by any code in the same assembly, or by any derived class in another assembly.

Some points

1) structs members cannot be declared as protected because they doesn't support inheritance.
2) destructors cannot have access modifiers.
3) internal is the default access modifier for class, struct, interface.
4) Interface members are always public.
5) Enumeration members are always public.

Indexers in C#

- used to treat an object as an array.
- syntax

this [argument list]
{

get
{

// your get block code

}

set
{

// your set block code

}

}

- Example

using System;



namespace Indexer_example1

{

class Program

{

class IndexerClass

{

private string[] names = new string[10];



public string this[int i]

{

get

{

return names[i];

}

set

{

names[i] = value;

}

}

}



static void Main(string[] args)

{

IndexerClass Team = new IndexerClass();

Team[0] = "Rocky";

Team[1] = "Teena";

Team[2] = "Ana";

Team[3] = "Victoria";

Team[4] = "Yani";

Team[5] = "Mary";

Team[6] = "Gomes";

Team[7] = "Arnold";

Team[8] = "Mike";

Team[9] = "Peter";



for (int i = 0; i < 10; i++)

{

Console.WriteLine(Team[i]);

}

Console.ReadKey();

}

}

}

FAQ's on Constructors

1. Is the Constructor mandatory for the class ?

Yes, It is mandatory to have the constructor in the class and that too should be accessible for the object i.e., it should have a proper access modifier. Say for example we have the private constructor in the class then it is of no use as it cannot be accessed by the object, so practically it is no available for the object. In such conditions it will raise an error.

2. What if I do not write the constructor ?

In such case the compiler will try to supply the no parameter constructor for your class behind the scene. Compiler will attempt this only if you do not write the constructor for the class. If you provide any constructor ( with or without parameters), then compiler will not make any such attempt.

3. What if I have the constructor public myDerivedClass() but not the public myBaseClass() ?

It will raise an error. If either the no parameter constructor is absent or it is in-accessible ( say it is private ), it will raise an error. You will have to take the precaution here.

4. Can we access static members from the non-static ( normal ) constructors ?

Yes, We can. There is no such restriction on non-static constructors. But there is one on static constructors that it can access only static members.