Delete All Tables ,Store Procedures,Views and Functions in SQL

Delete All Tables

--Delete All Keys

DECLARE @Sql NVARCHAR(500) DECLARE @Cursor CURSOR
SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT DISTINCT sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql
WHILE (@@FETCH_STATUS = 0)
BEGIN
Exec SP_EXECUTESQL @Sql
FETCH NEXT FROM @Cursor INTO @Sql
END
CLOSE @Cursor DEALLOCATE @Cursor
GO
EXEC sp_MSForEachTable 'DROP TABLE ?'
GO

Delete All Stored Procedures

declare @procName varchar(500)
declare cur cursor
    for select [name] from sys.objects where type = 'p'
open cur

fetch next from cur into @procName
      while @@fetch_status = 0
      begin
            if @procName <> 'DeleteAllProcedures'
                  exec('drop procedure ' + @procName)
                  fetch next from cur into @procName
      end

close cur
deallocate cur

Delete All Views


declare @procName varchar(500)
declare cur cursor
    for select [name] from sys.objects where type = 'v'
open cur

fetch next from cur into @procName
      while @@fetch_status = 0
      begin
                  exec('drop view ' + @procName)
                  fetch next from cur into @procName
      end
close cur
deallocate cur

Delete All Functions


declare @procName varchar(500)
declare cur cursor
    for select [name] from sys.objects where type = 'fn'
open cur

fetch next from cur into @procName
      while @@fetch_status = 0
      begin
                  exec('drop function ' + @procName)
                  fetch next from cur into @procName
      end

close cur
deallocate cur

Explain About Method Shadowing

These guys were pretty clear about the topic Smile | :) Thumbs up, codeproject is getting better by the day Big Grin | :-D 
 
In addition to those, just a small point of view on why and when you should use new keyword
 
Ok, back to the basics. When you inherit from a class, the methods marked with either public or protected will be available to the child class. right? So while designing, you decide that I might need more expanded behavior or different behavior from these methods. Let's take an example.
public class Bird {
     public void Eat() {
          Console.WriteLine("Eating....");
     }
 
     public virtual void Fly() {
          Console.WriteLine("Flying....");          
     }
}
Few things to notice, since every bird is eating, you know that's not going to change, so you don't intend to change it's behavior in child classes. But some birds can't fly, so you know it's going to change, so you are marking that method with virtual keyword. You can achieve the same thing, by abstract classes but it's a different lesson Smile | :) 
 
So let's implement 2 birds classes
public class Robin : Bird {
     public override void Fly() {
          Console.WriteLine("Flying Fast....");
     }
}
public class Ostrich : Bird {
     public override void Fly() {
          Console.WriteLine("Cannot fly");
     }
}
Now, I am going do something crazy Big Grin | :-D Laugh all you want, but I could not come up with a better example this quickly. But I know it'll help you understand the concept.
 
So let's imagine, you are want to include disabled birds who can't eat. Now this was not expected in the original design. I.e. it was not intended in the design. So you can go back to the original design and decorate the Eat() method with override.
 
But this is not possible when you are using someone else's code. I.e. you don't have the source code to edit and update but only the compiled DLL. So now what? That's where new keyword helps you out.
public class DisabledBird : Bird {
     public override void Fly() {
          Console.WriteLine("Cannot fly :(");
     }
 
     public new void Eat() {
          Console.WriteLine("Cannot eat :(");
     }
}
The new keyword serves 2 purposes.
1. Allows you to hide methods of parent classes and implement a different method for the same signature. 
2. Allows you to keep track of methods that you did like that. So that it would not confuse you when you look in to the code later on.
 
NOTE: This is NOT mean that you should not design your code or overriding can be just ignored. If you do that, you'll quickly lose the ability to maintain your code. This should be only used for exceptional cases like when you are using a toolkit or a library.
 
Damn, this turned out to be an article :P Apologies

Explain About Constructor


Constructors in C# :
Broadly speaking, it is a method in the class which gets executed when its object is created. Usually we put the initialization code in the constructor. Writing a constructor in the class is pretty simple, have a look at the following sample :
 
public class mySampleClass
{
public mySampleClass()
{
// This is the constructor method.}// rest of the class members goes here.
When the object of this class is instantiated this constructor will be executed. Something like this :

mySampleClass obj = new mySampleClass()// At this time the code in the constructor will // be executed

Constructor Overloading :
C# supports overloading of constructors, that means we can have constructors with different set of parameters. So our class can be like this :
public class mySampleClass
{
public mySampleClass()
{
// This is the no parameter constructor method.// First Constructor}public mySampleClass(int Age)
{
// This is the constructor with one parameter.// Second Constructor}public mySampleClass(int Age, string Name)
{
// This is the constructor with two parameters.// Third Constructor}// rest of the class members goes here.

Well, note here that call to the constructor now depends on the way you instantiate the object. For example :
mySampleClass obj = new mySampleClass()// At this time the code of no parameter // constructor (First Constructor) will be executedmySampleClass obj = new mySampleClass(12)// At this time the code of one parameter // constructor(Second Constructor) will be // executed.
The call to the constructors is completely governed by the rules of the overloading here.
Calling Constructor from another Constructor:
You can always make the call to one constructor from within the other. Say for example : 
public
 class mySampleClass
{
public mySampleClass(): this(10)
{
// This is the no parameter constructor method.// First Constructor}public mySampleClass(int Age) 
{
// This is the constructor with one parameter.// Second Constructor}

Very first of all let us see what is this syntax :    
public mySampleClass(): this(10)
Here this refers to same class, so when we say this(10), we actually mean execute the public mySampleClass(int Age) method.The above way of calling the method is called initializer. We can have at the most one initializer in this way in the method.
Another thing which we must know is the execution sequence i.e., which method will be executed when. Here if I instantiate the object as 
mySampleClass obj = new mySampleClass()
Then the code of public mySampleClass(int Age) will be executed before the code of mySampleClass(). So practically the definition of the method : 
public
 mySampleClass(): this(10)
{
// This is the no parameter constructor method.// First Constructor
is equivalent to :
public mySampleClass()
{
mySampleClass(10) 
// This is the no parameter constructor method.// First Constructor
Note that only this and base (we will see it further) keywords are allowed in initializers, other method calls will raise the error.
This is sometimes called Constructor chaining.
Behavior of Constructors in Inheritance :
Let us first create the inherited class.
public class myBaseClass
{
public myBaseClass()
{
// Code for First Base class Constructor}public myBaseClass(int Age)
{
// Code for Second Base class Constructor}// Other class members goes here}public class myDerivedClass : myBaseClass// Note that I am inheriting the class here.{public myDerivedClass()
{
// Code for the First myDerivedClass Constructor.}public myDerivedClass(int Age):base(Age)
{
// Code for the Second myDerivedClass Constructor.}// Other class members goes here
Now what will be the execution sequence here :

If I create the object of the Derived class as 

myDerivedClass obj = new myDerivedClass()
Then the sequence of execution will be 

1. public myBaseClass() method
2. and then public myDerivedClass() method.
Note: If we do not provide initializer referring to the base class constructor then it executes the no parameter constructor of the base class.
Note one thing here : We are not making any explicit call to the constructor of base class neither by initializer nor by the base() keyword, but it is still executing. This is the normal behavior of the constructor.
If I create the object of the Derived class as 

myDerivedClass obj = 
new myDerivedClass(15)
Then the sequence of execution will be 

1. public myBaseClass(int Age) method.
2. and then public myDerivedClass(int Age) method.
Here the new keyword base has come into picture. This refers to the base class of the current class. So, here it refers to the myBaseClass. And base(10) refers to the call to myBaseClass(int Age) method.
Also note the usage of Age variable in the syntax : 
public myDerivedClass(int Age):base(Age).
Understanding it is left to the reader.
Static Constructors :
This is a new concept introduced in C#. By new here I mean that it was not available for the C++ developers. This is a special constructor and gets called before the first object is created of the class. The time of execution cannot be determined, but it is definitely before the first object creation - could be at the time of loading the assembly.
The syntax of writing the static constructors is also damn simple. Here it is :
public class myClass
{
static myClass()
{
// Initialization code goes here.// Can only access static members here.}// Other class methods goes here
}
 
Notes for Static Constructors :

1. There can be only one static constructor in the class.
2. The static constructor should be without parameters. 
3. It can only access the static members of the class.
4. There should be no access modifier in static constructor definition.
Ok fine, all the above points are fine but why is it like that? Let us go step by step here.
Firstly, the call to the static method is made by the CLR and not by the object, so we do not need to have the access modifier to it.
Secondly, it is going to be called by CLR, who can pass the parameters to it, if required, No one, so we cannot have parameterized static constructor.
Thirdly, Non-static members in the class are specific to the object instance so static constructor, if allowed to work on non-static members, will reflect the changes in all the object instances, which is impractical. So static constructor can access only static members of the class.
Fourthly, Overloading needs the two methods to be different in terms to methods definition, which you cannot do with Static Constructors, so you can have at the most one static constructor in the class.
Now, one question raises here, can we have the two constructors as  
public class myClass
{
static myClass()
{
// Initialization code goes here.// Can only access static members here.}public myClass()
{
// Code for the First myDerivedClass Constructor.}// Other class methods goes here}

This is perfectly valid, though doesn't seem to be in accordance with overloading concepts. But why? Because the time of execution of the two method are different. One is at the time of loading the assembly and one is at the time of object creation.
FAQs Regd. 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.

Access Modifiers in Dot Net


Access modifiers are keywords used to specify the declared accessibility of a member or a type.

Why to use access modifiers?


Access modifiers are an integral part of object-oriented programming. They support the concept of encapsulation, which promotes the idea of hiding functionality. Access modifiers allow you to define who does or doesn't have access to certain features.

In C# there are 5 different types of Access Modifiers.
Modifier
Description
public
There are no restrictions on accessing public members.
private
Access is limited to within the class definition. This is the default access modifier type if none is formally specified
protected
Access is limited to within the class definition and any class that inherits from the class
internal
Access is limited exclusively to classes defined within the current project assembly
protected internal
Access is limited to the current assembly and types derived from the containing class. All members in current project and all members in derived class can access the variables.

public

The public keyword is an access modifier for types and type members. Public access is the most permissive access level.

There are no restrictions on accessing public members.

Accessibility: 
  • Can be accessed by objects of the class
  • Can be accessed by derived classes
Example: In the following example num1 is direct access.

using System;

namespace AccessModifiers
{
    class Program
    {
        class AccessMod
        {
            public int num1;
        }
        static void Main(string[] args)
        {
            AccessMod ob1 = new AccessMod();

            //Direct access to public members
            ob1.num1 = 100;

            Console.WriteLine("Number one value in main {0}", ob1.num1);
            Console.ReadLine();
        }
    }
}

private

Private access is the least permissive access level.

Private members are accessible only within the body of the class or the struct in which they are declared.

Accessibility: 
  • Cannot be accessed by object
  • Cannot be accessed by derived classes
Example: In the following example num2 is not accessible outside the class.

using System;

namespace AccessModifiers
{
    class Program
    {
        class AccessMod
        {
            public int num1;
            int num2;
        }
        static void Main(string[] args)
        {
            AccessMod ob1 = new AccessMod();

            //Direct access to public members
            ob1.num1 = 100;

            //Access to private member is not permitted

            ob1.num2 = 20;
            Console.WriteLine("Number one value in main {0}", ob1.num1);
            Console.ReadLine();
        }
    }
}

The above program will give compilation error, as access to private is not permissible. In the below figure you can see the private member num2 is not available.

 
private.jpg
protected

A protected member is accessible from within the class in which it is declared, and from within any class derived from the class that declared this member.

A protected member of a base class is accessible in a derived class only if the access takes place through the derived class type.

Accessibility: 
  • Cannot be accessed by object
  • By derived classes
using System;

namespace AccessModifiers
{
    class Program
    {
        class Base
        {
            protected int num1;
        }

        class Derived : Base
        {
            public int num2;
            static void Main(string[] args)
            {
                Base ob1 = new Base();
                Derived ob2 = new Derived();
                ob2.num1 = 20;

                // Access to protected member as it is inhertited by the Derived class
                ob2.num2 = 90;

                Console.WriteLine("Number2 value {0}", ob2.num2);
                Console.WriteLine("Number1 value which is protected {0}", ob2.num1);
                Console.ReadLine();
            }
        }
    }
}

In the above program we try to access protected member in main it is not available as shown in the picture below that num1 is not listed in intellisense.

protected.jpg
 
internal

The internal keyword is an access modifier for types and type members. We can declare a class as internal or its member as internal. Internal members are accessible only within files in the same assembly (.dll).

In other words, access is limited exclusively to classes defined within the current project assembly.

Accessibility:

In same assembly (public) 
  • Can be accessed by objects of the class
  • Can be accessed by derived classes
In other assembly (internal) 
  • Cannot be accessed by object
  • Cannot be accessed by derived classes
protected internal

The protected internal accessibility means protected OR internal, not protected AND internal.

In other words, a protected internal member is accessible from any class in the same assembly, including derived classes.

The protected internal access modifier seems to be a confusing but is a union of protected and internal in terms of providing access but not restricting. It allows:  
  • Inherited types, even though they belong to a different assembly, have access to the protected internal members.
  • Types that reside in the same assembly, even if they are not derived from the type, also have access to the protected internal members.

Default access


A default access level is used if no access modifier is specified in a member declaration. The following list defines the default access modifier for certain C# types:

enum: The default and only access modifier supported is public.

class: The default access for a class is private. It may be explicitly defined using any of the access modifiers.

interface: The default and only access modifier supported is public.

struct: The default access is private with public and internal supported as well.

The default access may suffice for a given situation, but you should specify the access modifier you want to use to ensure proper application behavior.

Note: Interface and enumeration members are always public and no access modifiers are allowed.