none
definition column from sys.check_constraints is coming as null in ado.net datareader when the field is not null when executed from sssms RRS feed

  • Question

  • I am trying to write a C# program to read all the check constraints for a given table from ms sql server database. I am using sys.check_constraints table to get this information, when I tried to read this using ado.net datareader the column 'definition' from this table is always coming as empty though it has value.

    SQL Script

    GO
    
    /****** Object:  Table [dbo].[Customer]    Script Date: 7/10/2019 3:24:22 PM ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[Customer](
    	[Id] [bigint] IDENTITY(1,1) NOT NULL,
    	[CompanyId] [smallint] NOT NULL,
    	[Prefix] [varchar](4) NOT NULL,
    	[FirstName] [varchar](50) NOT NULL,
    	[LastName] [varchar](50) NOT NULL,
    	[MiddleName] [varchar](50) NULL,
    	[Suffix] [varchar](4) NULL,
    	[NickName] [varchar](10) NULL,
    	[ProfilePictureName] [varchar](50) NULL,
    	[Company] [varchar](100) NULL,
    	[DateOfBirth] [date] NOT NULL,
    	[DateOfDeath] [date] NULL,
    	[Gender] [char](1) NOT NULL,
    	[Type] [varchar](10) NOT NULL,
    	[IsActive] [char](1) NULL,
    	[CreatedBy] [varchar](50) NOT NULL,
    	[CreatedOn] [datetime2](7) NOT NULL,
    	[UpdatedBy] [varchar](50) NULL,
    	[UpdatedOn] [datetime2](7) NULL,
    	[Process] [varchar](100) NULL,
    	[MessageId] [varchar](50) NOT NULL,
    	[SysStartDate] [datetime2](2) GENERATED ALWAYS AS ROW START NOT NULL,
    	[SysEndDate] [datetime2](2) GENERATED ALWAYS AS ROW END NOT NULL,
     CONSTRAINT [PK_CUSTOMER] PRIMARY KEY CLUSTERED 
    (
    	[Id] ASC,
    	[CompanyId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
    	PERIOD FOR SYSTEM_TIME ([SysStartDate], [SysEndDate])
    ) ON [PRIMARY]
    WITH
    (
    SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[MSSQL_TemporalHistoryFor_601769201] )
    )
    GO
    
    ALTER TABLE [dbo].[Customer] ADD  DEFAULT ('Y') FOR [IsActive]
    GO
    
    ALTER TABLE [dbo].[Customer]  WITH CHECK ADD  CONSTRAINT [CUSTOMER_PREFIX_CHECK] CHECK  (([Prefix]='Dr' OR [Prefix]='Miss' OR [Prefix]='Ms' OR [Prefix]='Mrs' OR [Prefix]='Mr'))
    GO
    
    ALTER TABLE [dbo].[Customer] CHECK CONSTRAINT [CUSTOMER_PREFIX_CHECK]
    GO
    
    ALTER TABLE [dbo].[Customer]  WITH CHECK ADD  CONSTRAINT [CUSTOMER_SUFFIX_CHECK] CHECK  (([Suffix]='RN' OR [Suffix]='MD' OR [Suffix]='ESQ' OR [Suffix]='DO' OR [Suffix]='DDS' OR [Suffix]='DDM' OR [Suffix]='III' OR [Suffix]='II' OR [Suffix]='I' OR [Suffix]='Sr' OR [Suffix]='Jr'))
    GO
    
    ALTER TABLE [dbo].[Customer] CHECK CONSTRAINT [CUSTOMER_SUFFIX_CHECK]
    GO
    
    ALTER TABLE [dbo].[Customer]  WITH CHECK ADD  CONSTRAINT [CUSTOMER_TYPE_CHECK] CHECK  (([Type]='Customer' OR [Type]='VENDOR' OR [Type]='INSIDER' OR [Type]='ANALYST' OR [Type]='INVESTOR'))
    GO
    
    ALTER TABLE [dbo].[Customer] CHECK CONSTRAINT [CUSTOMER_TYPE_CHECK]
    GO
    
    
    
    
    CREATE PROCEDURE [dbo].[GetCheckConstraints_Dup](@tableName as varchar(200))
    AS
    BEGIN
    select 
        col.[name] as column_name,
        con.[definition] As [constraint_value]
    from sys.check_constraints con
        left outer join sys.objects t
            on con.parent_object_id = t.object_id
        left outer join sys.all_columns col
            on con.parent_column_id = col.column_id
            and con.parent_object_id = col.object_id
    where t.[name]=@tableName and con.is_disabled=0
    order by con.name;
    End;
    
    GO
    
    exec GetCheckConstraints_Dup 'Customer'
    


    C# Code

            public static List<Constraints> GetConstraints(string tableName,string connectionString)
            {
                var constraints = new List<Constraints>();
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    //SqlDataReader
                    connection.Open();
                    SqlCommand cmd = new SqlCommand("DBO.GetCheckConstraints_Dup", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@tableName", SqlDbType.VarChar).Value = tableName;
    
                    using (SqlDataReader dataReader = cmd.ExecuteReader())
                    {
                        while (dataReader.Read())
                        {
                            constraints.Add(new Constraints() { FieldName = dataReader["column_name"].ToString(), Constraint = ConstraintType.Check, ConstraintValue = dataReader["constraint_value"].ToString() });
                         
                        }
                    }
                }
    
                return constraints;
            }
    

    Wednesday, July 10, 2019 7:20 PM

All replies

  • When you call your sproc from SQL directly (via SSMS) does it return the correct values with the correct column names? 

    When you call this code from your C# app is it being called using a connection string for a user that would have permissions to get this data?



    Michael Taylor http://www.michaeltaylorp3.net

    Wednesday, July 10, 2019 9:44 PM
    Moderator
  • Hi Ala,

    Thank you for posting here.

    Based on my test, I could not reproduce your problem. I could get the correct answer according to the procedure. I suggest that you could deleted the table and procedure and recreate them.

    Result:

    If the problem still exists, please feel free to let us know.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 11, 2019 2:51 AM
    Moderator