29 June 2011

Trigger is Sql Server 2008

Trigger is Sql Server 2008

Trigger Definition

A trigger is a special type of stored procedure that automatically runs when a language event executes. SQL Server includes two general types of triggers:
data manipulation language (DML) and data definition language (DDL) triggers. DML triggers can be used when INSERT, UPDATE, or DELETE statements modify data
in a specified table or view. DDL triggers fire stored procedures in response to a variety of DDL statements, which are primarily statements that begin with
CREATE, ALTER, and DROP. DDL triggers can be used for administrative tasks, such as auditing and regulating database operations.
Types of DML Triggers

AFTER Triggers

AFTER triggers are executed after the action of the INSERT, UPDATE, or DELETE statement is performed.
Specifying AFTER is the same as specifying FOR, which is the only option available in earlier versions of Microsoft SQL Server.
AFTER triggers can be specified only on tables.

INSTEAD OF Triggers

INSTEAD OF triggers are executed in place of the usual triggering action. INSTEAD OF triggers can also be defined on views with one or more base tables,
where they can extend the types of updates a view can support.

For more information about AFTER and INSTEAD OF triggers, see DML Trigger Planning Guidelines.

CLR Triggers

A CLR Trigger can be either an AFTER or INSTEAD OF trigger. A CLR trigger can also be a DDL trigger.
Instead of executing a Transact-SQL stored procedure, a CLR trigger executes one or more methods written in managed code that are members of an assembly
created in the .NET Framework and uploaded in SQL Server.

DDL Triggers
-DDL triggers execute in response to a variety of data definition language (DDL) events.
These events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations.

- Unlike DML triggers, they do not fire in response to UPDATE, INSERT, or DELETE statements on a table or view. Instead, they fire in response to a variety of
Data Definition Language (DDL) events. These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER, and DROP. Certain
system stored procedures that perform DDL-like operations can also fire DDL triggers
DDL triggers can be used for administrative tasks such as auditing and regulating database operations.

Use DDL triggers when you want to do the following:

-You want to prevent certain changes to your database schema.

-You want something to occur in the database in response to a change in your database schema.

-You want to record changes or events in the database schema.


DDL triggers fire only after the DDL statements that trigger them are run. DDL triggers cannot be used as INSTEAD OF triggers.

The following example shows how a DDL trigger can be used to prevent any table in a database from being modified or dropped.

CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT 'You must disable Trigger "safety" to drop or alter tables!'
ROLLBACK ;
DML Triggers

DML triggers are invoked when a data manipulation language (DML) event takes place in the database. DML events include INSERT, UPDATE, or DELETE statements that modify data in a specified table or view. A DML trigger can query other tables and can include complex Transact-SQL statements. The trigger and the statement that fires it are treated as a single transaction, which can be rolled back from within the trigger. If a severe error is detected (for example, insufficient disk space), the entire transaction automatically rolls back.

DML triggers are useful in these ways:

-They can cascade changes through related tables in the database; however, these changes can be executed more efficiently using cascading referential
integrity constraints.
-They can guard against malicious or incorrect INSERT, UPDATE, and DELETE operations and enforce other restrictions that are more complex than those defined with
CHECK constraints.
-Unlike CHECK constraints, DML triggers can reference columns in other tables. For example, a trigger can use a SELECT from another table to compare to the inserted
or updated data and to perform additional actions, such as modify the data or display a user-defined error message.
-They can evaluate the state of a table before and after a data modification and take actions based on that difference.
Multiple DML triggers of the same type (INSERT, UPDATE, or DELETE) on a table allow multiple, different actions to take place in response to
the same modification statement.


Types of DML Triggers

You can program the following types of DML Triggers:

AFTER Triggers

AFTER triggers are executed after the action of the INSERT, UPDATE, or DELETE statement is performed.
Specifying AFTER is the same as specifying FOR, which is the only option available in earlier versions of Microsoft SQL Server.
AFTER triggers can be specified only on tables.

INSTEAD OF Triggers

INSTEAD OF triggers are executed in place of the usual triggering action. INSTEAD OF triggers can also be defined on views with one or more base tables,
where they can extend the types of updates a view can support.

For more information about AFTER and INSTEAD OF triggers, see DML Trigger Planning Guidelines.

CLR Triggers

A CLR Trigger can be either an AFTER or INSTEAD OF trigger. A CLR trigger can also be a DDL trigger. Instead of executing a Transact-SQL stored procedure,
a CLR trigger executes one or more methods written in managed code that are members of an assembly created in the .NET Framework and uploaded in SQL Server.


22 June 2011

Reflection in C#

Reflection in C#

Reflection provides objects (of type Type) that encapsulate assemblies, modules and types. You can use reflection to dynamically create an instance of a type, bind the type to an existing object, or get the type from an existing object and invoke its methods or access its fields and properties. If you are using attributes in your code, Reflection enables you to access them.
// Using GetType to obtain type information: int i = 42;
System.Type type = i.GetType(); System.Console.WriteLine(type);
The output is:

System.Int32

// Using Reflection to get information from an Assembly:
System.Reflection.Assembly o = System.Reflection.Assembly.Load("mscorlib.dll");
System.Console.WriteLine(o.GetName());

The output is:

mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089


20 June 2011

Example for Array list in C# and Binary Search,Sort,Remove,Removeat

ArrayList Class

Implements the IList interface using an array whose size is dynamically increased as required.
The capacity of a ArrayList is the number of elements the ArrayList can hold. As elements are added to an ArrayList, the capacity is automatically increased as
required through reallocation.
//Example for Array list in C# and Binary Search,Sort,Remove,Removeat

using System;
using System.Collections;
public class SamplesArrayList
{

public static void Main()
{

ArrayList myAL = new ArrayList();
//Insert values in arraylist
for (int i = 0; i <= 10; i++)
myAL.Add(i * 2);

// Displays the ArrayList.
Console.WriteLine("The Int32 ArrayList contains the following:");
PrintValues(myAL);

// Locates a specific object that does not exist in the ArrayList.
Object myObject = 4;
FindMyObject(myAL, myObject);

// Locates an object that exists in the ArrayList.
Object myObjectSearch = 11;
FindMyObject(myAL, myObjectSearch);

// Removes the element containing "4".
myAL.Remove(4);

// Removes the element at index 5.
myAL.RemoveAt(5);

// Removes three elements starting at index 4.
myAL.RemoveRange(4, 3);

// Sorts the values of the ArrayList.
myAL.Sort();

//Print the Values
PrintValues(myAL);
}

///
/// Find the value in the array List
///
///
///
public static void FindMyObject(ArrayList myList, Object myObject)
{
//Store the index value of the search value
int myIndex = myList.BinarySearch(myObject);
//Check the index exist or not
if (myIndex < 0)
Console.WriteLine("The object to search for ({0}) is not found.", myObject);
else
Console.WriteLine("The object to search for ({0}) is at index {1}.", myObject, myIndex);
}

///
/// Print the array list
///
///
public static void PrintValues(IEnumerable myList)
{
foreach (Object obj in myList)
Console.Write(" {0}", obj);
Console.WriteLine();
}

}


Choose the right collection type for your needs in dotnet framework

Choose the right collection type for your needs,

-when you are not concerned about the order of the items in the collections, your first inclination should be to use a System.Collections.Generic.Dictionary

-The three basic operations (Add, Remove, and Contains) all operate quickly even if the collection contains millions of items.

-Using the new LinkedList collection can potentially help you improve performance in scenarios where you need to maintain order yet still achieve fast inserts.

-Unlike List, LinkedList is implemented as a chain of dynamically allocated objects.

-The downside of a linked list from a performance point of view is increased activity by the garbage collector as it has to traverse the entire list to make sure objects were not disposed of.
-Inserting an item into a LinkedList is much faster than doing so into a List

-Dictionary may be most appropriate. However, lookup is only fast in the average case, and certain datasets could cause that performance to degrade quickly.

-SortedDictionary, uses a balanced tree implementation as the underlying data store; this provides relatively fast lookups and maintains items in a sorted order but insertions will most likely be slower (and will vary based on the number of items in the collection)

-Alternatively, you can also use SortedList, which uses two separate arrays to maintain the keys and the values separately and to maintain them both in order (in the worse case you would have to shift all the keys and values).


Custom Collections

-In certain situations, you may not find that all of your requirements are implemented in any of the existing collection types in the Framework.

-It may be that you have a unique set of requirements or that you can still use an existing collection with minor changes.

If you decide you want to write a custom collection, you should first look into extending an existing collection type.

System.Collections.ObjectModel namespace.

These collections already implement the most needed interfaces and give you the baseline functionality you expect.


17 June 2011

Difference between namespace and assembly

Difference between namespace and assembly

Assembly
An Assembly is a logical unit of functionality that runs under the control of the .NET common language runtime (CLR). An assembly physically exists as a .dll file or as an .exe file.

An Assembly can contains one or many namespaces

Namespace
Namespaces are a way of grouping type names and reducing the chance of name collisions.

16 June 2011

15 June 2011

View in Sql Server 2008

View in Sql Server 2008

Creates a virtual table whose contents (columns and rows) are defined by a query. Use this statement to create a view of the data in one or more tables in the database.
For example, a view can be used for the following purposes:

-To focus, simplify, and customize the perception each user has of the database.

-As a security mechanism by allowing users to access data through the view, without granting the users permissions to directly access the underlying base tables.

-To provide a backward compatible interface to emulate a table whose schema has changed.

Example
--Partitioned view as defined on Server1
CREATE VIEW Customers
AS
--Select from local member table.
SELECT *
FROM CompanyData.dbo.Customers_33
UNION ALL
--Select from member table on Server2.
SELECT *
FROM Server2.CompanyData.dbo.Customers_66
UNION ALL
--Select from mmeber table on Server3.
SELECT *
FROM Server3.CompanyData.dbo.Customers_99

14 June 2011

Interface vs Abstract Class in C#

Interface vs Abstract Class

Multiple inheritance

Interfaces:A class may inherit several interfaces.
Abstract Class:A class may inherit only one abstract class.

Default implementation

Interfaces:An interface cannot provide any code, just the signature.
Abstract Class:An abstract class can provide complete, default code and/or just the details that have to be overridden.

Access Modifiers

Interfaces:An interface cannot have access modifiers for the subs, functions, properties etc everything is assumed as public
Abstract Class:An abstract class can contain access modifiers for the subs, functions, properties

Core VS Peripheral

Interfaces:Interfaces are used to define the peripheral abilities of a class. In other words both Human and Vehicle can inherit from a IMovable interface.
Abstract Class:An abstract class defines the core identity of a class and there it is used for objects of the same type.

Usage in Application

Interfaces:If various implementations only share method signatures then it is better to use Interfaces
Abstract Class:If various implementations are of the same kind and use common behaviour or status then abstract class is better to use.

Speed

Interfaces:Requires more time to find the actual method in the corresponding classes.
Abstract Class:Compare to Interface it is very fast

Fields and Constants

Interfaces:No fields can be defined in interfaces
Abstract Class:An abstract class can have fields and constrants defined


Main Limitation of Interface

If we add a new method to an Interface then we have to track down all the implementations of the interface and define implementation for the new method
in all derived class.

Main Limitation of Abstract Class

A class may inherit only one abstract class.

Normalization with Examples

Normalization with Examples

Normalization is the process of organizing data in a database.
This includes creating tables and establishing relationships between those tables according to rules designed both to protect
the data and to make the database more flexible by eliminating redundancy and inconsistent dependency.

Redundant data wastes disk space and creates maintenance problems.
If data that exists in more than one place must be changed, the data must be changed in exactly the same way in all locations. A customer address change is much easier to implement if that data is stored only in the Customers table and nowhere else in the database.

What is an "inconsistent dependency"? While it is intuitive for a user to look in the Customers table for the
address of a particular customer, it may not make sense to look there for the salary of the employee who calls on that customer.
The employee's salary is related to, or dependent on, the employee and thus should be moved to the Employees table.
Inconsistent dependencies can make data difficult to access because the path to find the data may be missing or broken.


First Normal Form

Eliminate repeating groups in individual tables.
Create a separate table for each set of related data.
Identify each set of related data with a primary key.
Do not use multiple fields in a single table to store similar data. For example, to track an inventory item that may come from two
possible sources, an inventory record may contain fields for Vendor Code 1 and Vendor Code 2.

What happens when you add a third vendor? Adding a field is not the answer; it requires program and table modifications and
does not smoothly accommodate a dynamic number of vendors. Instead, place all vendor information in a separate table called Vendors,
then link inventory to vendors with an item number key, or vendors to inventory with a vendor code key.

Second Normal Form

Create separate tables for sets of values that apply to multiple records.
Relate these tables with a foreign key.
Records should not depend on anything other than a table's primary key (a compound key, if necessary).
For example, consider a customer's address in an accounting system. The address is needed by the Customers table, but also by the Orders,
Shipping, Invoices, Accounts Receivable, and Collections tables. Instead of storing the customer's address as a separate entry in each
of these tables, store it in one place, either in the Customers table or in a separate Addresses table.

Third Normal Form

Eliminate fields that do not depend on the key.
Values in a record that are not part of that record's key do not belong in the table. In general, any time the contents of a group
of fields may apply to more than a single record in the table, consider placing those fields in a separate table.

For example, in an Employee Recruitment table, a candidate's university name and address may be included. But you need a complete list
of universities for group mailings. If university information is stored in the Candidates table, there is no way to list universities
with no current candidates. Create a separate Universities table and link it to the Candidates table with a university code key.

EXCEPTION: Adhering to the third normal form, while theoretically desirable, is not always practical.
If you have a Customers table and you want to eliminate all possible interfield dependencies,
you must create separate tables for cities, ZIP codes, sales representatives, customer classes, and any other factor that may be
duplicated in multiple records. In theory, normalization is worth pursing. However, many small tables may degrade performance or
exceed open file and memory capacities.


Other Normalization Forms

Fourth normal form, also called Boyce Codd Normal Form (BCNF), and fifth normal form do exist, but are rarely considered in practical
design. Disregarding these rules may result in less than perfect database design, but should not affect functionality.

For More Information Click

Partial Class in C#

Partial Class in C#

It is possible to split the definition of a class or a struct, or an interface over two or more source files.
Each source file contains a section of the class definition, and all parts are combined when the application is compiled.
There are several situations when splitting a class definition is desirable:

When working on large projects, spreading a class over separate files allows multiple programmers to work on it simultaneously.

When working with automatically generated source, code can be added to the class without having to recreate the source file.
Visual Studio uses this approach when creating Windows Forms, Web Service wrapper code, and so on.
You can create code that uses these classes without having to edit the file created by Visual Studio.

Example for Partial Class
public partial class Employee
{
public void DoWork()
{
}
}

public partial class Employee
{
public void GoToLunch()
{
}
}

At compile time, attributes of partial-type definitions are merged. For example, the following declarations:


Systems Development Life Cycle (SDLC), or Software Development Life Cycle


Systems Development Life Cycle (SDLC), or Software Development Life Cycle

13 June 2011

Uses of SET XACT_ABORT in Sql Server

Uses of SET XACT_ABORT in Sql Server

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back.

When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting.

Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.

other SET statement in Sql Server

Click here

6 June 2011

SSMS Tools Pack Free Download Used in Sql Server 2008 and R2

SSMS Tools Pack MUST be installed with elevated admin privileges

Sql Server Tools

Go to link

Click here to Download tool

Collections in Dotnet Framework

Collections in Dotnet Framework

Why Use Collections in dotnet?


-Individual elements serve similar purposes and are of equal importance.
-The number of elements is unknown or is not fixed at compile time.
-You need to support iteration over all elements.
-You need to support sorting of the elements.
-You need to expose the elements from a library where a consumer will expect a collection type.

Collections can vary, depending on how the elements are stored, how they are sorted, how searches are performed, and how comparisons are made. The Queue class and the Queue generic class provide first-in-first-out lists, while the Stack class and the Stack generic class provide last-in-first-out lists. TheSortedList class and the SortedList generic class provide sorted versions of the Hashtable class and the Dictionary generic class. The elements of a Hashtable or a Dictionary are accessible only by the key of the element, but the elements of a SortedList or a KeyedCollection are accessible either by the key or by the index of the element. The indexes in all collections are zero-based, except Array, which allows arrays that are not zero-based.

Singleton Class in C# with Example

Singleton class in C#

What is Singleton class?
A class that has only one instance, and you need to provide a global point of access to the instance.

How to achieve Singleton class?
Singleton provides a global, single instance by:
1. Making the class create a single instance of itself.
2. Allowing other objects to access this instance through a class method that returns a reference to the instance.A class method is globally accessible.

3. Declaring the class constructor as private so that no other object can create a new instance.

Uses of Singleton Class
1. The static initialization approach is possible because the .NET Framework explicitly defines how and when static variable initialization occurs.
2. The Double-Check Locking idiom described earlier in "Multithreaded Singleton" is implemented correctly in the common language runtime.

Demerits of Singleton Class
If your multithreaded application requires explicit initialization, you have to take precautions to avoid threading issues.

Singleton class vs. Static methods

1.Static classes don’t promote inheritance. If your class has some interface to derive from, static classes makes it impossible.
2.You cannot specify any creation logic with static methods.

When to User Singleton Class?
The below activities is common through out the application, Singleton increase the performance of the application

1. Logging
2. Database Access
3. Reading Configuration information.

Singleton provides a global, single instance by:

1. Making the class create a single instance of itself.
2. Allowing other objects to access this instance through a class method that returns a reference to the instance.
3. A class method is globally accessible.
4. Declaring the class constructor as private so that no other object can create a new instance.

The following implementation of the Singleton design pattern follows the solution presented in Design Patterns: Elements of Reusable Object-Oriented Software [Gamma95] but modifies it to take advantage of language features available in C#, such as properties:

The class is marked sealed to prevent derivation, which could add instances.

The instantiation is not performed until an object asks for an instance; this approach is referred to as lazy instantiation. Lazy instantiation avoids instantiating unnecessary singletons when the application starts.

Example :

using System;

public class Singleton
{
   private static Singleton instance;

   private Singleton() {}

   public static Singleton Instance
   {
      get
      {
         if (instance == null)
         {
            instance = new Singleton();
         }
         return instance;
      }
   }
}


Static Initialization of Singleton Class
One of the reasons Design Patterns [Gamma95] avoided static initialization is because the C++ specification left some ambiguity around the initialization order of static variables. Fortunately, the .NET Framework resolves this ambiguity through its handling of variable initialization:

Example:
public sealed class Singleton
{
   private static readonly Singleton instance = new Singleton();
 
   private Singleton(){}

   public static Singleton Instance
   {
      get
      {
         return instance;
      }
   }
}

The main disadvantage of this implementation, however, is that it is not safe for multithreaded environments. If separate threads of execution enter the Instance property method at the same time, more that one instance of the Singleton object may be created. Each thread could execute the following statement and decide that a new instance has to be created.

Multithreaded Singleton in C#

Static initialization is suitable for most situations. When your application must delay the instantiation, use a non-default constructor or perform other tasks before the instantiation, and work in a multithreaded environment, you need a different solution. Cases do exist, however, in which you cannot rely on the common language runtime to ensure thread safety, as in the Static Initialization example. In such cases, you must use specific language capabilities to ensure that only one instance of the object is created in the presence of multiple threads.

This double-check locking approach solves the thread concurrency problems while avoiding an exclusive lock in every call to the Instance property method. It also allows you to delay instantiation until the object is first accessed. In practice, an application rarely requires this type of implementation. In most cases, the static initialization approach is sufficient.

using System;
public sealed class Singleton
{
   private static volatile Singleton instance;
   private static object syncRoot = new Object();

   private Singleton() {}

   public static Singleton Instance
   {
      get
      {
         if (instance == null)
         {
            lock (syncRoot)
            {
               if (instance == null)
                  instance = new Singleton();
            }
         }

         return instance;
      }
   }
}

This approach ensures that only one instance is created and only when the instance is needed. Also, the variable is declared to be volatile to ensure that assignment to the instance variable completes before the instance variable can be accessed. Lastly, this approach uses a syncRoot instance to lock on, rather than locking on the type itself, to avoid deadlocks.

This double-check locking approach solves the thread concurrency problems while avoiding an exclusive lock in every call to the Instance property method. It also allows you to delay instantiation until the object is first accessed. In practice, an application rarely requires this type of implementation. In most cases, the static initialization approach is sufficient.

1 June 2011

Move next line in Multiline textbox in C#

Move next line in Multiline textbox

In C#

mtxtAlertMessage.Text += message+"\r\n" ;

Problem:Combobox Editable in C# Windows application and Asp.net

Problem:Combobox Editable in C# Windows application and Asp.net

Change the property of combobox from Dropdownstyle to DropDownList

or change in code at run time

this.ComboBox1.DropDownStyle = ComboBoxStyle.DropDown;

Consistency level in Azure cosmos db

 Consistency level in Azure cosmos db Azure Cosmos DB offers five well-defined consistency levels to provide developers with the flexibility...