Monday, November 21, 2011

Diffference Between RegEx.IsMatch and the RegularExpressionAttribute

I was working on some code that stores a month as CCYYMM string. Of course I wanted to validate the string with Regex so I had the pattern

^20[0-2][0-9](0[1-9])|(1[0-2])$

Everything was fine as long as I was using Regex.IsMatch. The problems started when I used the RegularExpressionAttribute. Suddenly, I was getting errors everywhere. I constructed a little test to verify that there was a difference. What I found was that using the same string and pattern, yielded different results.

Here’s my unit test.

[TestMethod]
public void DifferenceBetweenIsMatchAndRegExAttribute()
{
  var pattern = "^20[0-2][0-9](0[1-9])|(1[0-2])$";
  int cnt = 0;
  var months = new string[]
  {
  "201001",
  "201002",
  "201003",
  "201004",
  "201005",
  "201006",
  "201007",
  "201008",
  "201009",
  "201010",
  "201011",
  "201012" };
  var attribute = new RegularExpressionAttribute(pattern);
  bool isMatchOk = false;
  bool isAttrOk = false;
 
  foreach (var month in months)
  {
    isMatchOk = System.Text.RegularExpressions.Regex.IsMatch(month, pattern);
    isAttrOk = attribute.IsValid(month);
 
    if (isMatchOk & isAttrOk)
    { cnt += 1; }
  }
 
  Assert.AreEqual(12, cnt);
}

You would expect the number of matches to be 12 but it’s actually 9. Why? Because the attribute is matching only the first half of our pattern. It stops at the pipe (|). To prove this, I flipped the sides of the pipe and got 3. If I add parens around both sides of the logical or I will get the answer I’m looking for.

^20[0-2][0-9]((0[1-9])|(1[0-2]))$

While it seems logical that the RegularExpressionAttribute would use Regex.IsMatch underneath, it apparently is not the case. I’m betting the reason has to do with JavaScript compatibility. Remember, the attribute must also validate client side.

Cheers!

Tuesday, April 26, 2011

Updated Code To Get Class Definition From Table Structure

My previous post on Script to Create MetadataType classes was to work with LINQ-To-SQL and partial classes. EF 4.1 introduces Code First which uses POCOs and here is the code to create a class definition from a table’s structure.

Enjoy.

SET NOCOUNT ON
declare @TableName varchar(256) = 'BusinessTypes'
declare @EntityName varchar(256) = 'BusinessType'
declare @TableSchema varchar(256) = 'dbo'

declare @ColumnName varchar(256)
    , @DataType varchar(256)
    , @NewDataType varchar(256)
    , @MaxLength int
    , @Nullable varchar(5)
    
declare @Lines table (Line varchar(1000))

insert into @Lines select 'public class ' + @EntityName
insert into @Lines select '{'

declare @DataTypes table (SqlDataType varchar(1000), DataType varchar(1000))

insert into @DataTypes (SqlDataType, DataType) values ('bit', 'bool')
insert into @DataTypes (SqlDataType, DataType) values ('char', 'string')
insert into @DataTypes (SqlDataType, DataType) values ('datetime', 'DateTime')
insert into @DataTypes (SqlDataType, DataType) values ('decimal', 'decimal')
insert into @DataTypes (SqlDataType, DataType) values ('int', 'int')
insert into @DataTypes (SqlDataType, DataType) values ('money', 'decimal')
insert into @DataTypes (SqlDataType, DataType) values ('ntext', 'string')
insert into @DataTypes (SqlDataType, DataType) values ('nvarchar', 'string')
insert into @DataTypes (SqlDataType, DataType) values ('smalldatetime', 'DateTime')
insert into @DataTypes (SqlDataType, DataType) values ('timestamp', 'byte[]')
insert into @DataTypes (SqlDataType, DataType) values ('uniqueidentifier', 'Guid')
insert into @DataTypes (SqlDataType, DataType) values ('varchar', 'string')

declare cols cursor for 
    select COLUMN_NAME, Data_type, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
    from INFORMATION_SCHEMA.COLUMNS
    where table_Name = @TableName and table_schema = @Tableschema
    order by ORDINAL_POSITION

open cols

fetch next from cols into  @ColumnName, @DataType, @MaxLength, @Nullable
while @@FETCH_STATUS = 0
begin
    select @NewDataType = DataType from @DataTypes where SqlDataType = @DataType
    
    if @DataType in ('varchar', 'char') and @Nullable = 'NO'
        insert into @Lines select char(9) + '[Required]'
        
    if @DataType in ('varchar', 'char')
        insert into @Lines select char(9) + '[StringLength(' + CAST(@MaxLength as varchar) + ')]'
    
    insert into @Lines select char(9) + 'public ' + @NewDataType + ' ' + @ColumnName + ' { get; set; }'
    
    insert into @Lines select char(9) + ''
    
    fetch next from cols into  @ColumnName, @DataType, @MaxLength, @Nullable
end

close cols
deallocate cols
insert into @Lines select '}'

select * FROM @Lines

SET NOCOUNT OFF

Tuesday, March 1, 2011

Replacement for ExpectedException in MS Test

This StackOverflow thread has a great solution for the issue that ExpectedException does not allow you to validate the error message. Look at the response from winSharp93 and the ExceptionAssert class. This is a much cleaner method and also shows some great ways to use generics.

Friday, February 18, 2011

Script To Grab Column Names As UML Properties

I was cooking up some UML diagrams for an existing DB and wanted to get all of the properties. It lists the table, column name in UML markup format and then the data type. The data type column is there so that all I have to do is order it by the second column and I’ll get all of the nulls so I can see if I’m getting a data type that I haven’t handled. The data type conversion is by no means complete but it’s a good start.

Enjoy!

select table_name, '+ ' + column_name + ' : ' +
    CASE WHEN DATA_TYPE in ('ntext', 'nvarchar', 'varchar', 'char') then 'string'
        WHEN DATA_TYPE = 'bit' then 'bool'
        WHEN DATA_TYPE = 'int' then 'int'
        WHEN DATA_TYPE = 'timestamp' then 'timestamp'
        WHEN DATA_TYPE = 'uniqueidentifier' then 'Guid'
        WHEN DATA_TYPE = 'money' then 'decimal'
        WHEN DATA_TYPE in ('smalldatetime', 'datetime') then 'datetime'
        END,
    DATA_TYPE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME not like '%aspnet%'
    and TABLE_NAME <> 'sysdiagrams'
order by 1, 2

Monday, January 10, 2011

SQL To Find String In Procedure/Function

I do a lot of work updating legacy systems. Almost every system needs the column names updated since the names are usually short and not very descriptive. Here’s a command for finding all store procedures/functions that use a specified column.

select s.name, a.name FROM sys.sql_modules m
inner join sys.all_objects a on m.object_id = a.object_id
inner join sys.schemas s on a.schema_id = s.schema_id
where m.definition like '%MyColumnName%'

INFORMATION_SCHEMA.ROUTINES is unreliable because it is limited to the first 4000 characters.

Enjoy!!

Monday, January 3, 2011

Read XML File Into SQL Server

Wayne Sheffield has a nice article on reading XML files into sql. You can find it here.