Dynamic Language Runtime

The DLR (Dynamic Language Runtime) is a runtime environment that adds a set of services for dynamic languages to the common language runtime (CLR). The DLR makes it easier to develop dynamic languages to run on the .NET Framework and to add dynamic features to statically typed languages (such as C# and Visual Basic).
The CLR always had dynamic capabilities. You could always use reflection, but its main goal was never to be a dynamic programming environment and there were some features missing. The DLR is built on top of the CLR and adds those missing features to the .NET platform.

Advantages of DLR:

  • Simplifies Porting Dynamic Languages to the .NET Framework
  • Enables Dynamic Features in Statically Typed Languages - Existing .NET Framework languages such as C# and Visual Basic can create dynamic objects and use them together with statically typed objects.
  • Provides Future Benefits of the DLR and .NET Framework
  • Enables Sharing of Libraries and Objects
  • Provides Fast Dynamic Dispatch and Invocation
Examples of dynamic languages are Lisp, Smalltalk, JavaScript, PHP, Ruby, Python, ColdFusion, Lua, Cobra, and Groovy.

Common Language Runtime

The CLR (Common Language Runtime) or the .NET runtime is the runtime execution environment of .NET Framework. Code running under the control of the CLR is often termed managed code and benefits from features such as cross-language integration, cross-language exception handling, enhanced security, versioning and deployment support, a simplified model for component interaction, and debugging and profiling services. The runtime provides the following benefits:

·          Performance improvements.
·          The ability to easily use components developed in other languages.
·          Extensible types provided by a class library.
·          Language features such as inheritance, interfaces, and overloading for object-oriented programming.
·          Support for explicit free threading that allows creation of multithreaded, scalable applications.
·          Support for structured exception handling.
·          Support for custom attributes.
·          Garbage collection.
·          Use of delegates instead of function pointers for increased type safety and security.

Runtime can be understood as a collection of external services that are required to execute a given compiled unit of code.

CLR (Common Language Runtime)

CLR Features in .Net 4.0

What is .NET Framework?

The .NET Framework is a revolutionary platform created by Microsoft for developing applications. The .NET Framework allows the creation of Windows applications, Web applications, Web Services, and pretty much anything else you can think of.

The .NET Framework is composed mainly of three parts:

  • An extensible set of development languages including C# and VB.NET, which must respect a specification named CLS (Common Language Specification). The base types used by these languages must also respect a specification names CTS (Common Type System). 
  • BCL (Base Class Library) - A set of base classes usable from programs developed in these languages.
  • A software layer respecting a specification named CLI (Common Language Infrastructure), which is responsible for the execution of .NET applications and knows only one language named IL (Intermediate Language). The CLI is responsible for the compilation of IL code into machine code during execution of an application. Consequently, the languages supported by .NET must have a compiler which will produce IL code. Microsoft’s implementation of the CLI is named CLR (Common Language Runtime).

What is .NET?

Overview of the .NET Framework - I

Overview of the .NET Framework - II

Overview of the .NET Framework - III

Why use DateTime.TryParse() instead of Convert.DateTime()

Convert.ToDateTime() uses DateTime.Parse() internally, with the current culture - unless you pass it null, in which case it returns DateTime.MinValue.

In short, Convert.ToDateTime() eliminates the code necessary to set the CultureInfo, which you'd have to do to use DateTime.Parse().

Note: If you're not sure string is a valid DateTime, use neither and instead, use DateTime.TryParse().

If you're sure the string is a valid DateTime, and you know the format, you could also consider the DateTime.ParseExact() or DateTime.TryParseExact() methods.

Please note that there may be certain cases when the DateTime.Parse() method fails (since Convert.ToDateTime() uses DateTime.Parse() internally, Convert.ToDateTime() will also fail).

Consider the scenario given below:

If user passes an invalid date format then the method DateTime.Parse() will throw a FormatException.

// throws FormatException, not a valid format
var dt1 = DateTime.Parse("");

// throws FormatException, February doesn’t have a 30th day, not a valid date
var dt2 = DateTime.Parse("02/30/2010 12:35");

Note: It is recommended to use DateTime.TryParse() method to avoid unexpected exceptions especially when accepting inputs from users.

It should be noted that DateTime.Parse() actually calls DateTime.TryParse() and just throws the exception in the event TryParse() returns false. That is, Parse() is roughly equivalent to:

// rough psuedo-code of Parse()
public DateTime Parse(string inputString)
     DateTime result;
     if (!DateTime.TryParse(inputString, out result))
         throw new FormatException(...);
     return result;

So calling DateTime.TryParse() directly is more efficient because it avoids the wrapper call, and it doesn’t allocate and throw an unneeded exception in the case of an error.

Triggers in Detail

Basically, triggers are classified into two main types:

  • AFTER Triggers
  • INSTEAD OF Triggers

AFTER Triggers

These triggers are fired only when all operations specified in the triggering SQL statement (INSERT, UPDATE or DELETE), including any referential cascade actions and constraint check, have executed successfully. AFTER is the default if FOR is the only keyword specified. AFTER triggers cannot be defined on views.

AFTER TRIGGERS can be classified into three types:

  • AFTER INSERT Trigger
  • AFTER UPDATE Trigger
  • AFTER DELETE Trigger

in_developer_id INT IDENTITY,
vc_developer_name VARCHAR (100),
dc_developer_salary DECIMAL (10, 2)

INSERT INTO Developer VALUES ('Anil', 1000);
INSERT INTO Developer VALUES ('Ricky', 1200);
INSERT INTO Developer VALUES ('Johnnie', 1100);
INSERT INTO Developer VALUES ('Sebastian', 1300);
INSERT INTO Developer VALUES ('Maria', 1400);

Creation of AFTER INSERT TRIGGER to insert the rows inserted into the table into another log table.

in_developer_id INT,
vc_developer_name VARCHAR(100),
dc_developer_salary DECIMAL(10, 2),
vc_log_action VARCHAR(100),
dt_log_timestamp DATETIME

AFTER INSERT Trigger This trigger is fired after an INSERT on the Developer table.

CREATE TRIGGER trgAfterInsert ON [dbo].[ Developer]
      DECLARE @in_developer_id INT;
      DECLARE @vc_developer_name VARCHAR (100);
      DECLARE @dc_developer_salary DECIMAL (10, 2);
      DECLARE @vc_log_action VARCHAR (100);

      SELECT @in_developer_id = i.in_developer_id FROM inserted i;     
      SELECT @vc_developer_name = i.vc_developer_name FROM inserted i; 
      SELECT @dc_ developer_salary = i.dc_ developer_salary FROM inserted i; 
      SET @vc_log_action = 'Inserted Record -- After Insert Trigger';

      INSERT INTO DeveloperLog (in_developer_id, vc_developer_name, dc_developer_salary, vc_log_action, dt_log_timestamp)
      VALUES (@in_developer_id, @vc_developer_name, @dc_developer_salary, @vc_log_action, GETDATE());

The CREATE TRIGGER statement is used to create the trigger.
THE ON clause specifies the table name on which the trigger is to be attached.
The FOR INSERT specifies that this is an AFTER INSERT trigger. In place of FOR INSERT, AFTER INSERT can be used. Both of them mean the same.

To test the Trigger we insert a row into the Developer table as: 

INSERT INTO Developer VALUES ('Christy', 1500);

Now, a record has been inserted into the Developer table. The AFTER INSERT trigger attached to this table inserts the record into the DeveloperLog as:

6   Christy  1500.00   Inserted Record -- After Insert Trigger       2013-01-21 08:05:55.700

AFTER UPDATE Trigger – This trigger is fired after an update on the table.

CREATE TRIGGER trgAfterUpdate ON [dbo].[Developer]
      DECLARE @in_developer_id INT;
      DECLARE @vc_developer_name VARCHAR (100);
      DECLARE @dc_developer_salary DECIMAL (10, 2);
      DECLARE @vc_log_action VARCHAR (100);

      SELECT @in_developer_id = i.in_developer_id FROM inserted i;     
      SELECT @vc_developer_name = i.vc_developer_name FROM inserted i; 
      SELECT @dc_developer_salary = i.dc_developer_salary FROM inserted i;   
      IF UPDATE (vc_developer_name)
            SET @vc_log_action = 'Updated Record -- After Update Trigger';
      IF UPDATE (dc_developer_salary)
            SET @vc_log_action = 'Updated Record -- After Update Trigger';

      INSERT INTO DeveloperLog (in_developer_id, vc_developer_name, dc_developer_salary, vc_log_action, dt_log_timestamp)
      VALUES (@in_developer_id, @vc_developer_name, @dc_developer_salary, @vc_log_action, GETDATE());

The AFTER UPDATE Trigger is created in which the updated record is inserted into the DeveloperLog table. There is no logical table updated like the logical table inserted. We can obtain the updated value of a field from the UPDATE (column_name) function.
We can use, IF UPDATE (vc_developer_name) to check if the column vc_developer_name has been updated.

To test the Trigger we update a record in the Developer table as:

UPDATE Developer SET dc_developer_salary = 2550 WHERE in_developer_id = 6

This inserts the row into the DeveloperLog table as:

6  Chris  2550.00  Updated Record -- After Update Trigger           2013-01-21 08:06:55.700

AFTER DELETE Trigger – This trigger is fired after a delete on the table.

CREATE TRIGGER trgAfterDelete ON [dbo].[Developer]
      DECLARE @in_developer_id INT;
      DECLARE @vc_developer_name VARCHAR (100);
      DECLARE @dc_developer_salary DECIMAL (10, 2);
      DECLARE @vc_log_action VARCHAR (100);

      SELECT @in_developer_id = d.in_developer_id FROM deleted d;
      SELECT @vc_developer_name = d.vc_developer_name FROM deleted d;  
      SELECT @dc_developer_salary = d.dc_developer_salary FROM deleted d;    
      SET @vc_log_action = 'Deleted -- After Delete Trigger';

      INSERT INTO DeveloperLog (in_developer_id, vc_developer_name, dc_developer_salary, vc_log_action, dt_log_timestamp)
      VALUES (@in_developer_id, @ vc_developer_name, @dc_developer_salary, @vc_log_action, GETDATE());

In this trigger, the deleted record’s data is picked from the logical deleted table and inserted into the DeveloperLog table. Delete the record from the Developer table.

A record has been inserted into the DeveloperLog table as:

6  Christy          2550.00  Deleted -- After Delete Trigger.  2008-04-26 12:52:13.867

All the triggers can be enabled / disabled on the table using the statement:


Specific Triggers can be enabled or disabled as:


This disables the After Delete Trigger named trgAfterDelete on the specified table.

Instead Of Triggers – This trigger is executed in place of the triggering SQL statement (it acts as an interceptor). The logic in the trigger can override the actions of the triggering statements.
If you define an Instead Of trigger on a table for the Delete operation, then try to delete rows, but the rows will not actually get deleted (unless you issue another delete instruction from within the trigger).

INSTEAD OF TRIGGERS can be classified into three types:

Example: Create an Instead Of Delete Trigger:

CREATE TRIGGER trgInsteadOfDelete ON [dbo].[Developer]
      DECLARE @in_developer_id INT;
      DECLARE @vc_developer_name VARCHAR (100);
      DECLARE @dc_developer_salary DECIMAL (10, 2);
      SELECT @in_developer_id = d.in_developer_id FROM deleted d;
      SELECT @vc_developer_name = d.vc_developer_name FROM deleted d;
      SELECT @dc_developer_salary = d.dc_developer_salary FROM deleted d;

            IF (@dc_developer_salary > 1200)
                  RAISERROR ('Cannot delete where salary > 1200', 16, 1);
                  DELETE FROM Developer WHERE in_developer_id = @in_developer_id;
                  INSERT INTO DeveloperLog (in_developer_id, vc_developer_name, dc_developer_salary, vc_log_action, dt_log_timestamp)
                  VALUES (@in_student_id,@vc_developer_name, @dc_developer_salary, 'Deleted -- Instead Of Delete Trigger', GETDATE());

This trigger will prevent the deletion of records from the table where dc_developer_salary > 1200. If such a record is deleted, the Instead Of Trigger will rollback the transaction, otherwise the transaction will be committed. 

Let’s try to delete a record with the dc_developer_salary >1200:

DELETE FROM Developer WHERE in_developer_id = 4

This will print an error message as defined in the RAISERROR statement:

Server: Msg 50000, Level 16, State 1, Procedure trgInsteadOfDelete, Line 15
Cannot delete where salary > 1200

Related Post: Triggers in SQL

Triggers in SQL

A trigger is a special type of stored procedure that is automatically invoked when the data in a specified table or view is modified. The INSERT, UPDATE and DELETE statements can invoke a trigger. A trigger has the capability to query other tables and includes complex SQL statements.

A trigger can be used to reference or update data in another database or even on another server. When a trigger is fired, it becomes a part of the transaction from which the original data was modified. If a server error occurs, the entire transaction is rolled back automatically.

Differentiate between DML and DDL triggers

  • DDL trigger is a new feature added to SQL Server 2005, whereas DML triggers were user even with SQL Server 7.0 and 2000.
  • DML triggers execute many SQL statements or procedure on execution of a DML statement, like INSERT, UPDATE or DELETE.
  • SQL Server 2005 permits a user to create a trigger that can perform an action in case a DDL statement is executed. These triggers are used when the user wants to performs an action when a schema change occurs.
  • A DDL trigger is a type of stored procedure that is executed automatically when an event occurs in the database server.
  • DDL triggers are executed in response to the various data definition language (DDL) events, such as CREATE, ALTER, and DROP statements.

Limitations of Triggers

  • The CREATE TRIGGER statement can apply to only one table. Moreover, it must be the first statement in the batch.
  • Permission to create triggers on a table or view defaults to the owner of the table or view.
  • Triggers can only be created in the current database, although they can reference objects outside the current database.
  • Triggers cannot be created on a temporary table or system table. Temporary tables can be created and referenced by a trigger. Instead of referencing system tables, you should reference the Information Schema Views.
  • If a table contains a foreign key with an UPDATE or DELETE cascading action defined, then INSTEAD OF UPDATE or INSTEAD OF DELETE triggers should not be defined on the table.
  • The TRUNCATE TABLE statement will not fire a DELETE trigger because this action is not logged.
  • The WRITETEXT statement, whether logged or unlogged, will not activate a trigger.

Q. What are the two main methods for enforcing business rules and data integrity at the server?
A. The two main methods for enforcing business rules and data integrity at the server are constraints and triggers. The main benefit of triggers over constraints is the capability to perform complex processing logic using SQL.

Related Post: Triggers in Detail

What is the difference between DELETE & TRUNCATE commands in SQL?

DELETE command removes the rows from a table based on the condition that we provide with a WHERE clause. TRUNCATE command actually removes all the rows from a table and there will be no data in the table after we run the TRUNCATE command.


  • TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
  • TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.
  • TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain.
  • TRUNCATE always locks the table and the data page but not each and every row.
  • TRUNCATE cannot be used on a table referenced by a FOREIGN KEY constraint.
  • TRUNCATE cannot be used with a WHERE clause.
  • TRUNCATE cannot activate a trigger because the operation does not log individual row deletions.
  • TRUNCATE can not be Rolled back using logs.
  • TRUNCATE is DDL Command.
  • TRUNCATE Resets identity of the table. The counter used by an identity for new rows is reset to the seed for the column.



  • DELETE is slower than TRUNCATE because it maintains log for every record.
  • DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
  • DELETE locks each row in a table for deletion.
  • DELETE can be used with or without a WHERE clause.
  • DELETE activates Triggers.
  • DELETE can be Rolled back using logs.
  • DELETE is DML Command.
  • DELETE does not reset identity of the table.

SYNTAX: DELETE FROM #table_name# WHERE #condition#

List the DDL, DML, DCL and TCL statements in SQL Server

DDL stands for Data Definition Language. These statements are used to define the database structure (also known as database schema). Given below are the DDL statements:

  • CREATE – CREATE is used for creating database objects, such as tables, views, indexes, etc.
  • DROP – DROP is used for deleting database objects.
  • ALTER – ALTER is used for modifying the structure of database objects.
  • RENAME – RENAME is used for renaming database objects.
  • TRUNCATE – TRUNCATE is used for deleting all records from a table.
  • COMMENT – COMMENT is used for adding comments to a data dictionary.

DML stands for Data Manipulation Language. These statements are used to manage data within database objects. Given below are the DML commands:

  • SELECT – SELECT is used to retrieve data from a database.
  • INSERT – INSERT is used to insert data into a table.
  • UPDATE – UPDATE is used to update existing data within a table.
  • DELETE – DELETE is used to delete all records from a table, the space for the records remain.
  • MERGE – MERGE is used to UPSERT operation (conditional INSERT/UPDATE).
  • CALL – CALL is used to call a PL/SQL or Java subprogram.
  • EXPLAIN PLAN – EXPLAIN PLAN is used to explain access path to data.
  • LOCK TABLE – LOCK TABLE is used to control concurrency.

DCL stands for Data Control Language. Given below are the DCL statements:

  • GRANT – GRANT command gives user's access privileges to database.
  • REVOKE – REVOKE command withdraws access privileges given with the GRANT command.

TCL stands for Transaction Control Language. The TCL statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

  • COMMIT – COMMIT saves the work done.
  • SAVEPOINT – SAVEPOINT identifies a point in a transaction to which you can later roll back.
  • ROLLBACK – ROLLBACK restores database to original state since the last COMMIT.
  • SET TRANSACTION – SET TRANSACTION changes transaction options like isolation level and what rollback segment to use.

Differentiate between DDL and DML commands

The Structured Query Language (SQL) comprises one of the fundamental building blocks of modern database architecture. SQL defines the methods used to create and manipulate relational databases on all major platforms.

SQL comes in many flavors. Oracle databases utilize their proprietary PL/SQL. Microsoft SQL Server makes use of Transact-SQL. However, all of these variations are based upon the industry standard ANSI SQL.

SQL commands can be divided into two main sublanguages: Data Definition Language (DDL) and Data Manipulation Language (DML).

Data Definition Language: The Data Definition Language (DDL) contains the commands used to create and destroy databases and database objects. These commands are primarily used by database administrators during the setup and removal phases of a database project. After the database structure is defined with DDL, database administrators and users can utilize the Data Manipulation Language (DML) to insert, retrieve and modify the data contained within it. Let's take a look at the structure and usage of four basic DDL commands:

CREATE - Installing a database management system (DBMS) on a computer allows you to create and manage many independent databases. For example, you may want to maintain a database of customer contacts for your sales department and a personnel database for your HR department. The CREATE command can be used to establish each of these databases on your platform. For example, the command:


creates an empty database named "employees" on your DBMS. After creating the database, your next step is to create tables that will contain data. Another variant of the CREATE command can be used for this purpose. The command:

CREATE TABLE personal_info (first_name char(20) not null, last_name char(20) not null, employee_id int not null)

establishes a table titled "personal_info" in the current database. In our example, the table contains three attributes: first_name, last_name and employee_id.

USE - The USE command allows you to specify the database you wish to work with within your DBMS. For example, if we're currently working in the sales database and want to issue some commands that will affect the employees’ database; we would preface them with the following SQL command:

USE employees

It's important to always be conscious of the database you are working in before issuing SQL commands that manipulate data.

ALTER - Once you've created a table within a database, you may wish to modify the definition of it. The ALTER command allows you to make changes to the structure of a table without deleting and recreating it. Take a look at the following command:

ALTER TABLE personal_info
ADD salary money null

This example adds a new attribute to the personal_info table -- an employee's salary. The "money" argument specifies that an employee's salary will be stored using a dollars and cents format. Finally, the "null" keyword tells the database that it's OK for this field to contain no value for any given employee.

DROP - The final command of the Data Definition Language, DROP, allows us to remove entire database objects from our DBMS. For example, if we want to permanently remove the personal_info table that we created, we'd use the following command:

DROP TABLE personal_info

Similarly, the command below would be used to remove the entire employees’ database:


Use this command with care! Remember that the DROP command removes entire data structures from your database. If you want to remove individual records, use the DELETE command of the Data Manipulation Language.
Data Manipulation Language: The Data Manipulation Language (DML) is used to retrieve, insert and modify database information. These commands will be used by all database users during the routine operation of the database. Let's take a brief look at the basic DML commands:

INSERT – The INSERT command in SQL is used to add records to an existing table. Returning to the personal_info example from the previous section, let's imagine that our HR department needs to add a new employee to their database. They could use a command similar to the one shown below:

INSERT INTO personal_info VALUES ('bart','simpson',12345,$45000)

Note that there are four values specified for the record. These correspond to the table attributes in the order they were defined: first_name, last_name, employee_id, and salary.

SELECT - The SELECT command is the most commonly used command in SQL. It allows database users to retrieve the specific information they desire from an operational database. Let's take a look at a few examples, again using the personal_info table from our employees’ database.
The command shown below retrieves all of the information contained within the personal_info table. Note that the asterisk is used as a wildcard in SQL. This literally means, "Select everything from the personal_info table."

SELECT * FROM personal_info

Alternatively, users may want to limit the attributes that are retrieved from the database. For example, the Human Resources department may require a list of the last names of all employees in the company. The following SQL command would retrieve only that information:

SELECT last_name FROM personal_info

Finally, the WHERE clause can be used to limit the records that are retrieved to those that meet specified criteria. The CEO might be interested in reviewing the personnel records of all highly paid employees. The following command retrieves all of the data contained within personal_info for records that have a salary value greater than $50,000:

SELECT * FROM personal_info WHERE salary > $50000

UPDATE - The UPDATE command can be used to modify information contained within a table, either in bulk or individually. Each year, our company gives all employees a 3% cost-of-living increase in their salary. The following SQL command could be used to quickly apply this to all of the employees stored in the database:

UPDATE personal_info SET salary = salary * 1.03

On the other hand, our new employee Bart Simpson has demonstrated performance above and beyond the call of duty. Management wishes to recognize his stellar accomplishments with a $5,000 raise. The WHERE clause could be used to single out Bart for this raise:

UPDATE personal_info SET salary = salary + $5000 WHERE employee_id = 12345

DELETE - Finally, let's take a look at the DELETE command. You'll find that the syntax of this command is similar to that of the other DML commands. Unfortunately, our latest corporate earnings report didn't quite meet expectations and poor Bart has been laid off. The DELETE command with a WHERE clause can be used to remove his record from the personal_info table:

DELETE FROM personal_info WHERE employee_id = 12345

Multithreaded Programming

A multithreaded program contains two or more parts that can run concurrently. Each part of such a program is called a thread, and each thread defines a separate path of execution. Thus multithreading is a specialized form of multitasking.

Process: A process is, in essence, a program that is executing.

Thread: A thread is a dispatchable unit of executable code.

Types of Multitasking: There are two distinct types of multitasking: Process-Based Multitasking and Thread Based Multitasking. C# and .NET Framework supports both the types.

Process-Based Multitasking: Process-Based Multitasking handles the concurrent execution of programs.

Thread Based Multitasking: Thread Based Multitasking deals with concurrent execution of pieces of the same program.

States of a Thread: There are various states of a thread as mentioned below:

1)       Running
2)       Ready to run – as soon as it gets CPU time.
3)       Suspended – a running thread can be suspended (temporary halt to its execution).
4)       Resume – a suspended thread can later be resumed.
5)       Blocked – a thread can be blocked when waiting for a response.
6)       Terminated – a thread can be terminated, in which case its execution ends and cannot be resumed.

Threads in .NET Framework: The .Net Framework defines two types of threads: foreground and background. By default, when we create a thread, it is a foreground thread, but it can be changed to a background thread.
A background thread is automatically terminated when all foreground threads in its process have stopped.

The classes that support multithreaded programming are defined in the System.Threading namespace.

The Thread Class: C# multithreading system is built upon the Thread class, which encapsulates a thread of execution. The Thread class is sealed. It defines several methods and properties that help manage threads.

Creating and Starting a Thread: To create a thread, we instantiate an object of type Thread. The simplest Thread constructor is shown below:

public Thread (ThreadStart entryPoint)

Here, entryPoint is the name of the method that will be called to begin execution of the thread.

ThreadStart is a delegate defined by the .NET Framework as shown below:

public delegate void ThreadStart ()

The entryPoint method must have a void return type and take no arguments.

The newly created thread starts running after we call its Start() method. Start() is defined by Thread and calls the method specified by entryPoint. Form of Start() is shown below:

public void Start()

Note: If we try to call Start() on a thread that has already been started, a ThreadStateException will be thrown.