Find Wait and Blocks In SQL

This query the sys.dm_os_waiting_tasks DMV to show all waiting tasks currently active or blocked,
revealing the wait type, duration, and resource

SELECT blocking.session_id AS blocking_session_id ,
blocked.session_id AS blocked_session_id ,
waitstats.wait_type AS blocking_resource ,
waitstats.wait_duration_ms ,
waitstats.resource_description ,
blocked_cache.text AS blocked_text ,
blocking_cache.text AS blocking_text
FROM sys.dm_exec_connections AS blocking
INNER JOIN sys.dm_exec_requests blocked ON blocking.session_id = blocked.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(blocked.sql_handle) blocked_cache
CROSS APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle) blocking_cache
INNER JOIN sys.dm_os_waiting_tasks waitstats ON waitstats.session_id = blocked.session_id

Output:

o

Operators

Below is the list of operators used is C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApp
{
    class Operators
    {
        //Arithmetic: + - * / %
	//Assignment: = += -= /= %= ++ -- &= ^= != <>=
	//Comparision: == != > = <=
	//Logical operators: && || !
        //Bitwise operators: * | ^ <> ~
    }
}

Access Levels

Below is the example to understand the access modifier in C#

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApp
{
    public class AccessLevels
    {
        public AccessLevels() { 
        
        }
        public int intPublic; //unrestricted access.
        protected int intProtected;//own or derived class.
        internal int intInternal;//own assembly.
        protected internal int intProtectedInternal;//own assembly or derived.
        private int intPrivate;//own class only.

        //All are accessible in same class.
        public void AccessibleInSameClass() {
            intPublic = 0;
            intProtected = 0;
            intInternal = 0;
            intProtectedInternal = 0;
            intPrivate = 0;
        }
    }

    //Private members are not accessible in derived class.
    public class DerivedAccessLevels : AccessLevels {
        public void AccessibleLevels() {
            intPublic = 0;
            intProtected = 0;
            intInternal = 0;
            intProtectedInternal =0;
        }
    }

    //Only Public, Protected Internal And Internal are accessible in another class.
    public class AnotherClass {
        public void fnAccess(AccessLevels objAccessLevels) {
            objAccessLevels.intPublic = 0;
            objAccessLevels.intInternal = 0;
            objAccessLevels.intProtectedInternal = 0;
        }
    }
}

Objects in C#

(Almost) Everything is an object is .net and C#. We all know that int, char, string, date, double etc are the primitive data types. But all these data types are derived from system.object.

To understand this lets take an example of int data type. We all know that int is derived from System.Int32; but we can declare the variable of int type using below mentioned two ways

int i = new int();
int j = 0;

where first option is like abject instance creation. When we use the . (dot) operator on i then it will give us different methods like .ToString(), .Equals() for object equality, .GetType() to get type. Hence this proves that all these primitives data types also derived from object (system.object) base class.

datatype

 

Value Type and Reference Type

Value Type:

  • Small type that have their values allocated on the stack in memory.
  • Cannot be null; must always contain data.
  • When passed in method (by val), they are coped as new objects.So the original  value will never change only the copy will change.
  • Cost is low on performance and memory because values are small.
  • Derived from system.valuetype and are sealed (it cannot be inherited).

Reference Type:

  • Can consume significant resources but have more features.
  • Can be null and reference to heap-based (.Net managed) objects.
  • When passed in methods (by ref) only a reference to object is passed. Changes to reference data change the source.
  • No wasted resource because a copy of the object is not made.
  • Cost is an issue with performance and memory because references need to be managed by .Net and must be garbage collected.

 

Sending Email using SQL Server

Follow the below steps to configure email account and send email using SQL Server

–Enabling Database Mail
sp_configure ‘show advanced options’,1
reconfigure
go
sp_configure ‘Database Mail XPs’,1
reconfigure

–Creating a Profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = ‘SQLProfile’,
@description = ‘Mail Service for SQL Server’ ;

— Create a Mail account for gmail. We have to use our company mail account.
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = ‘SQL_Email_Account’,
@email_address = ‘abc@gmail.com’,
@mailserver_name = ‘smtp.gmail.com’,
@port=587,
@enable_ssl=1,
@username=’abc’,
@password=’password’

— Adding the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = ‘SQLProfile’,
@account_name = ‘SQL_Email_Account’,
@sequence_number =1

— Granting access to the profile to the DatabaseMailUserRole of MSDB
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = ‘SQLProfile’,
@principal_id = 0,
@is_default = 1 ;

–Sending Test Mail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘SQLProfile’,
@recipients = ‘abc@gmail.com’,
@body = ‘Database Mail Testing…’,
@subject = ‘Databas Mail from SQL Server’;

–Verifying, check status column
select * from sysmail_allitems

 

Exception Handling

The error-handling model in ASP.NET lets you handle errors easily at the method, page, and application levels of your web applications. Most applications will use some combination of these to handle problems when they arise.

Method level: In method you can handle the exception using try…Catch…Finally block.

private void anyRoutine( )
{
	try
	{
		// Routine code in this block
	}
        catch (Exception exc)
	{
	        // error handling in this block
	}
	finally
	{
		// cleanup performed in this block
	}
} // anyRoutine

Page level: Why would you want to use this approach? It allows you to handle all page-level errors in a uniform way, which can simplify error-handling code and make it more consistent and robust. The trick is in keeping all the error-handling code in one place in the original page by leveraging the Page_Error method

private void Page_Error(Object sender,
			System.EventArgs e)
{
	Exception lastError = null;
	// get the last error that occurred
	lastError = Server.GetLastError( );

	// do any logging, notifications, etc. here

	// set the URL of the page that will display the error and
	// include querystring parameters to allow the page to display
	// what happened
	Page.ErrorPage = "ErrorPage.aspx" +
		 "?PageHeader=Error Occurred" +
		 "&Message1=" + lastError.Message +
		 "&Message2=" +
		 "This error was processed at the page level";
 } // Page_Error

Application level: By handling all exceptions at the application level, rather than at the method or page level, you can process all errors for the application in a single location. Doing all error handling in one place in an application is key to writing effective code.

protected void Application_Error(Object sender, EventArgs e)
{
	const String EVENT_LOG_NAME = "Application";

	Exception lastException = null;
	System.Diagnostics.EventLog log = null;
	String message = null;

	// get the last error that occurred
	lastException = Server.GetLastError( );

	// create the error message from the message in the last exception along
	// with a complete dump of all of the inner exceptions (all exception
	// data in the linked list of exceptions)
	message = lastException.Message +
			  "\r\r" +
			  lastException.ToString( );

	// Insert error information into the event log Or Log the Exception in text file
	// perform other notifications, etc. here
	

	// clear the error and redirect to the page used to display the
	// error information
	Server.ClearError( );
	Server.Transfer("ErrorPage.aspx" +
			"?PageHeader=Error Occurred" +
			"&Message1=" + lastException.Message +
			"&Message2=" +
			"This error was processed at the application level");
} // Application_Error