Question

How to catch check constraint violation error in Database.

Answer 1

Smart try-catch mechanism of C# is enough to handle the situation.

Now I will create one table with one check constraint like,

create table TestTable(
   id int not null primary key identity(1,1),
   sal int not null CHECK(sal >0)
)

Here you can see my sal column will not take any negative value as I have set one check constraint in this column.

When I will try to fire insert into TestTable(sal) values(-100) query to this table, It will throw error.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using System.Diagnostics;
using System.Data.SqlClient;

namespace myProject
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection con = new SqlConnection();
            con.ConnectionString = "Data Source=SERVERNAME;Initial Catalog=test;Integrated Security=True";
            con.Open();

            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "insert into TestTable(sal) values(-100)";
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;

            try
            {
                int Value = (Int32)cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                Console.Write("Error Occur in Database" +"\n" +"Error Code"+  ex.Number +"\n" + "Error Message" + ex.Message);
            }
            Console.ReadLine();
        }
    }
}