How to catch specific MS-SQL SQLExceptions in C# and VB.NET

November 20, 2015

To catch specific SQLExceptions using Microsoft.NET and Microsoft Sequel Server you can use the snippet below. You also need to look for the specific error numbers/codes you want to catch. To do this you can either execute the following query in the SQL Management Studio or you can use this
List of SQLException Numbers/Codes (Direct-DL around 15mb).

Samples

T-SQL Sample

SELECT * FROM master.dbo.sysmessages ORDER BY error ASC

1	SELECT * FROM master.dbo.sysmessages ORDER BY error ASC

C# Sample

private static void MySqlMethod()
{
	SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder
	{
		DataSource = "localhost",
		InitialCatalog = "Northwind",
		IntegratedSecurity = true
	};

	try
	{
		using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
		{
			connection.Open();
			using (SqlCommand command = connection.CreateCommand())
			{
				//inserting invalid sql (for example: column not exists)
				command.CommandText = "SELECT InvalidColumnName FROM Customers";
				command.ExecuteNonQuery();
			}
		}
	}
	catch (SqlException ex)
	{
		// Log the original exception here
		foreach (SqlError sqlError in ex.Errors)
		{
			switch (sqlError.Number)
			{
				case 207: // 207 = InvalidColumn
					//do your Stuff here
					break;
				case 547: // 547 = (Foreign) Key violation
					//do your Stuff here
					break;
				case 2601: // 2601 = (Primary) key violation
					//do your Stuff here
					break;
				default:
					//do your Stuff here
					break;
			}
		}
	}
}

private static void MySqlMethod()

{

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder

{

DataSource = "localhost",

InitialCatalog = "Northwind",

IntegratedSecurity = true

};

try

{

using (SqlConnection connection = new SqlConnection(builder.ConnectionString))

{

connection.Open();

using (SqlCommand command = connection.CreateCommand())

{

//inserting invalid sql (for example: column not exists)

command.CommandText = "SELECT InvalidColumnName FROM Customers";

command.ExecuteNonQuery();

}

catch (SqlException ex)

{

// Log the original exception here

foreach (SqlError sqlError in ex.Errors)

{

switch (sqlError.Number)

{

case 207: // 207 = InvalidColumn

//do your Stuff here

break;

case 547: // 547 = (Foreign) Key violation

//do your Stuff here

break;

case 2601: // 2601 = (Primary) key violation

//do your Stuff here

break;

default:

//do your Stuff here

break;

}

VB.NET Sample

Private Sub MySqlMethod()
	Dim builder As New SqlConnectionStringBuilder() With { _
		.DataSource = "localhost", _
		.InitialCatalog = "Northwind", _
		.IntegratedSecurity = True _
	}

	Try
		Using connection As New SqlConnection(builder.ConnectionString)
			connection.Open()
			Using command As SqlCommand = connection.CreateCommand()
				'inserting invalid sql (for example: column not exists)
				command.CommandText = "SELECT InvalidColumnName FROM Customers"
				command.ExecuteNonQuery()
			End Using
		End Using
	Catch ex As SqlException
		' Log the original exception here
		For Each sqlError As SqlError In ex.Errors
			Select Case sqlError.Number
				Case 207
					' 207 = InvalidColumn
					'do your Stuff here
					Exit Select
				Case 547
					' 547 = (Foreign) Key violation
					'do your Stuff here
					Exit Select
				Case 2601
					' 2601 = (Primary) key violation
					'do your Stuff here
					Exit Select
				Case Else
					'do your Stuff here
					Exit Select
			End Select
		Next
	End Try
End Sub

Private Sub MySqlMethod()

Dim builder As New SqlConnectionStringBuilder() With { _

.DataSource = "localhost", _

.InitialCatalog = "Northwind", _

.IntegratedSecurity = True _

}

Try

Using connection As New SqlConnection(builder.ConnectionString)

connection.Open()

Using command As SqlCommand = connection.CreateCommand()

'inserting invalid sql (for example: column not exists)

command.CommandText = "SELECT InvalidColumnName FROM Customers"

command.ExecuteNonQuery()

End Using

Catch ex As SqlException

' Log the original exception here

For Each sqlError As SqlError In ex.Errors

Select Case sqlError.Number

Case 207

' 207 = InvalidColumn

'do your Stuff here

Exit Select

Case 547

' 547 = (Foreign) Key violation

'do your Stuff here

Exit Select

Case 2601

' 2601 = (Primary) key violation

'do your Stuff here

Exit Select

Case Else

'do your Stuff here

Exit Select

End Select

End Try

End Sub

Compatibility:

.NET 2.0

.NET 3.0

.NET 3.5

.NET 4.0

.NET 4.5

.NET 4.6

If you have any questions or suggestions feel free to rate this snippet, post a comment or Contact Us via Email.

Related links:

How to get the current Database and Log Filesize using MSSQL

Administrator

May 22, 2015

1 Comment

To get the current Database and Log Filesize using MSSQL you can use the snippet below. Tested using SQL Server 2012.

Sample MSSQL

with database_files_CTE as
(
    select
        name, type_desc, physical_name, size_mb = convert(decimal(11, 2), size * 8.0 / 1024),
        space_used_mb = convert(decimal(11, 2), fileproperty(name, 'spaceused') * 8.0 / 1024) from sys.database_files
)
select name,type_desc as [type],physical_name as [path],size_mb as [Filesize],space_used_mb as [Filesize used],
case size_mb when 0 then 0 else convert(decimal(5, 2), space_used_mb / size_mb * 100) end as [Filesize used %]  from database_files_CTE;

Sample Output

How to drop Primary Key of a Table using MSSQL

Administrator

April 21, 2015

0 Comments

To drop Primary Key of a Table using MSSQL you can use the snippet below.

Sample MSSQL

ALTER TABLE tblTest
DROP CONSTRAINT PK_tblTest
GO

How to search stored procedures using MS-SQL

Administrator

April 15, 2015

1 Comment

To search stored procedures using MS-SQL you can use the snippet below.

Sample MSSQL

SELECT Name FROM sys.procedures WHERE name LIKE '%StoredProcName%'

How to split a delimited string to multiple rows using MSSQL

Administrator

March 24, 2015

6 Comments

To split a delimited string to multiple rows using MSSQL you can use the snippet below.

Sample MSSQL

CREATE FUNCTION dbo.StringSplit
(
    @Input       NVARCHAR(MAX),
    @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN (
		SELECT Number = ROW_NUMBER() OVER (ORDER BY Number),
        Item FROM (
					SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@Input, Number, CHARINDEX(@Delimiter, @Input + @Delimiter, Number) - Number)))
					FROM (SELECT ROW_NUMBER() OVER (ORDER BY ao.[object_id])
					FROM sys.all_objects AS ao CROSS APPLY sys.all_objects) AS n(Number)
					WHERE Number <= CONVERT(INT, LEN(@Input))
					AND SUBSTRING(@Delimiter + @Input, Number, 1) = @Delimiter
				  ) AS item);
GO

How to remove any post that is older than X days in WordPress

Administrator

February 23, 2015

4 Comments

To remove any post that is older than X days in WordPress you can use the query below.

Sample mySQL

DELETE FROM wp_posts WHERE post_type = 'post' AND DATEDIFF(NOW(), post_date) > X

How to use select statement in a delete statement using MSSQL

Administrator

February 20, 2015

4 Comments

To use select statement in a delete statement using MSSQL you can use the snippet below.

Sample MSSQL

DELETE TableOne
FROM   TableOne
       INNER JOIN TableTwo
               ON TableTwo.Id= TableOne.Id
                  AND ...
                       WHERE ...

How to get the size of all tables in MSSQL

Administrator

February 20, 2015

4 Comments

To get the size of all tables using MSSQL you can use the snippet below.

Sample MSSQL

SELECT 
    tab.NAME AS [Tablename],
    s.Name AS [Schema Name],
    part.rows AS [Count of Rows],
    SUM(a.total_pages) * 8 AS [Total Space in KB], 
    SUM(a.used_pages) * 8 AS [Used Space in KB], 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8  AS [Unused Space in KB]
FROM sys.tables tab
INNER JOIN sys.indexes idx ON tab.OBJECT_ID = idx.object_id
INNER JOIN sys.partitions part ON idx.object_id = part.OBJECT_ID AND idx.index_id = part.index_id
LEFT OUTER JOIN sys.schemas s ON tab.schema_id = s.schema_id
INNER JOIN sys.allocation_units a ON part.partition_id = a.container_id
WHERE tab.NAME NOT LIKE 'dt%' AND tab.is_ms_shipped = 0 AND idx.OBJECT_ID > 255 
GROUP BY tab.Name, s.Name, part.Rows
ORDER BY 4 DESC

How to use a select in update statement using MSSQL

Administrator

February 20, 2015

4 Comments

To use a select statement in a update statement using MSSQL you can use the snippet below.

Sample SQL

UPDATE TableOne
SET
    t1.ColumnOne = t2.ColumnOne,
    t1.ColumnTwo = t2.ColumnTwo
FROM
    TableOne AS t1
INNER JOIN
    TableTwo AS t2
ON
    TableOne.id = TableTwo.id

How to get monday date of current week in MSSQL

Administrator

February 19, 2015

4 Comments

To get the Monday date of the current week you can use the snippet below.

SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) AS Monday

CODE SNIPPETS

CODESNIPPETS FOR .NET, SQL, JAVASCRIPT, JQUERY and more

SQL

How to get the current Database and Log Filesize using MSSQL

Sample MSSQL

Sample Output

How to drop Primary Key of a Table using MSSQL

Sample MSSQL

How to split a delimited string to multiple rows using MSSQL

Sample MSSQL

How to remove any post that is older than X days in WordPress

Sample mySQL

How to use select statement in a delete statement using MSSQL

Sample MSSQL

How to get the size of all tables in MSSQL

Sample MSSQL

How to use a select in update statement using MSSQL

Sample SQL

How to get monday date of current week in MSSQL