Sunday, 15 January 2017

SQL Server - List all Triggers in a Database

You can use the below query to list ass Triggers in your database.
It lists the Trigger name, table name and the type of trigger (update / delete / insert etc).

SELECT 
     sysobjects.name AS trigger_name 
    ,USER_NAME(sysobjects.uid) AS trigger_owner 
    ,s.name AS table_schema 
    ,OBJECT_NAME(parent_obj) AS table_name 
    ,OBJECTPROPERTY( id, 'ExecIsUpdateTrigger') AS isupdate 
    ,OBJECTPROPERTY( id, 'ExecIsDeleteTrigger') AS isdelete 
    ,OBJECTPROPERTY( id, 'ExecIsInsertTrigger') AS isinsert 
    ,OBJECTPROPERTY( id, 'ExecIsAfterTrigger') AS isafter 
    ,OBJECTPROPERTY( id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
    ,OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects 

INNER JOIN sysusers 
    ON sysobjects.uid = sysusers.uid 

INNER JOIN sys.tables t 
    ON sysobjects.parent_obj = t.object_id 

INNER JOIN sys.schemas s 
    ON t.schema_id = s.schema_id 

WHERE sysobjects.type = 'TR' 

Sunday, 11 December 2016

C# - Update Querystring Values in a Url

We will look into a solution to update querystring values in a Url.

using System.Web;

// url has the complete url with querystring 
// ex: http://bing.com?abc=1xyz=20
var uriBuilder = new UriBuilder(url);

var query = HttpUtility.ParseQueryString(uriBuilder.Query);

// Existing Querystring parameter xyz value will be updated from 20 to 10
query["xyz"] = "10";

// As QS Parameter hello doesn't existed, it will be added.
query["hello"] = "world";

// Set updated querystring to the Url.
uriBuilder.Query = query.ToString();

// New Url: http://bing.com?abc=1xyz=10&hello=world
Response.Redirect(uriBuilder.ToString()); 

Remember to import namespace "System.Web"

Thursday, 8 December 2016

ASP.NET MVC C# - Redirect to External Website from Controller Action Method

Refer the below code snippet for redirection from ASP.NET MVC Controller to an external website.
The MVC Controller method "Redirect()", is used for redirection from MVC Controller.

public ActionResult Index()
{
    return Redirect("http://www.google.com");
}

Redirect() method can be used for redirecting to an internal url as well.
Find the sample code below:
public ActionResult Index()
{
    return Redirect("/Customer/List");
}

Monday, 24 October 2016

SQL - While Loop Syntax

Below is the WHILE LOOP Syntax in SQL

DECLARE @cnt INT = 1;

WHILE @cnt BETWEEN 1 AND 20
BEGIN

PRINT @cnt;
SET @cnt = @cnt+1;

END

SQL Doesn't have For Loop, instead you have to use While loop.

SQL - For Loop Syntax

SQL doesn't have For Loop, instead you have to use While loop.
The syntax is as below.


DECLARE @cnt INT = 1;

WHILE @cnt BETWEEN 1 AND 20
BEGIN

PRINT @cnt;
SET @cnt = @cnt+1;

END


Thursday, 18 August 2016

SQL Server - Get DB Object Names, Count

SELECT sobjects.name
FROM sysobjects sobjects
WHERE sobjects.xtype = 'U'

The above example lists all the tables in the database.
You can modify the query to access other details about the db objects.
Also the same can be used to count the number of objects (tables / stored procedures) in the database.

Below are the reference to the other XTYPE values corresponding to each objects.

-- AF: Aggregate function (CLR)
-- C: CHECK constraint
-- D: Default or DEFAULT constraint
-- F: FOREIGN KEY constraint
-- L: Log
-- FN: Scalar function
-- FS: Assembly (CLR) scalar-function
-- FT: Assembly (CLR) table-valued function
-- IF: In-lined table-function
-- IT: Internal table
-- P: Stored procedure
-- PC: Assembly (CLR) stored-procedure
-- PK: PRIMARY KEY constraint (type is K)
-- RF: Replication filter stored procedure
-- S: System table
-- SN: Synonym
-- SQ: Service queue
-- TA: Assembly (CLR) DML trigger
-- TF: Table function
-- TR: SQL DML Trigger
-- TT: Table type
-- U: User table
-- UQ: UNIQUE constraint (type is K)
-- V: View
-- X: Extended stored procedure

Thursday, 21 July 2016

Windows 2012 - Install IIS on Windows Server 2012 R2

Installing and configuring IIS on Windows 2012 R2 is well explained in the below article with step by step screenshots.
Below is the link to the article: