I have a database that was created back in the days when I used to work with Lightswitch. I don’t use Lightswitch ever since Microsoft pulled the plug on it some time ago, but the database structure hasn’t changed.
I recently had a problem, in that a user tried to enter a value in a field that was longer than the maximum length set on the column. As we now have the max keyword to allow columns to be of any length, it seemed more sensible to use this on all varchar columns, rather than sticking with the somewhat arbitrary limits that were there, and having to go through the code base looking for places where I needed to add validation.
The problem was that changing a column from varchar(30) to varchar(max) is very easy in the designer, but a pain and a half if you want to do it with SQL. Given that I wanted to do this for every varchar column in every table, I didn’t really want to do it all by hand. However, if you try something like this…
alter table Customers alter column Name varchar(max)
…you’ll get an exception as the table is dependent on the default constraint. So, you need to drop that first. Now if you’re lazy like me, you won’t name your constraints, so it will have some snappy name like DF__Customers__Name__76DHG67TF. Even if you did name your constraints, youd have to know the name to be able to drop it before modifying the column.
This is all rather painful, so I decided a LinqPad script was the answer (as usual!). It is quite likely that you could do the whole thing in Linq, but as I don’t know of any way to modify the database structure in Linq, I decided the best way was to write some Linq to generate the SQL to do the job.
First I needed to work out what SQL was needed. Working with a single column on a single table, I came up with the following…
declare @df varchar(max)
declare @tableName nvarchar(max)
declare @columnName nvarchar(max)
declare @command nvarchar(max)
select @tableName = 'Customers'
select @columnName = 'Name'
select @df = (select top 1 name from sys.default_constraints
where parent_object_id = object_id(@tableName)
and parent_column_id = columnproperty(object_id(@tableName), @columnName, 'ColumnId'))
if @df <> ''
begin
select @command = 'alter table ' + @tableName + ' drop constraint ' + @df
print @command
execute sp_executeSQL @command
end
else
begin
print 'No default constraint found for ' + @columnName + ' on table ' + @tableName
end
select @command = 'alter table ' + @tableName + ' alter column ' + @columnName + ' varchar(max)'
select @command = 'alter table ' + @tableName + ' add constraint DF_'
+ @tableName + '_' + @columnName + ' default '''' for ' + @columnName
print @command
execute sp_executeSQL @command
print ' '
Lines 8-10 get the default constraint for the column, if there is one. If there is, we drop it (lines 14-16), then change the column length (line 23), and finally add a new (sensibly named) default constraint (lines 25-28). The script prints out what’s going on, so you can see what happened.
With that in hand, wrapping it in some Linq to query the database for suitable columns, and then writing the SQL to the results pane in LinqPad was pretty simple…
Console.WriteLine("declare @df varchar(max)");
Console.WriteLine("declare @tableName nvarchar(max)");
Console.WriteLine("declare @columnName nvarchar(max)");
Console.WriteLine("declare @command nvarchar(max)");
Console.WriteLine("");
foreach (MetaTable table in Mapping.GetTables().Where(t => t.TableName != "[sysdiagrams]"
&& t.RowType.DataMembers.Any(dm => dm.DbType != null && dm.DbType.ToLower().StartsWith("varchar")))) {
Console.WriteLine($"select @tableName = '{table.TableName}'");
foreach (MetaDataMember column in table.RowType.DataMembers.Where(dm => dm.DbType != null && dm.DbType.ToLower().StartsWith("varchar"))) {
Console.WriteLine($"select @columnName = '{column.Name}'");
Console.WriteLine("select @df = (select top 1 name from sys.default_constraints where parent_object_id = object_id(@tableName) and parent_column_id = columnproperty(object_id(@tableName), @columnName, 'ColumnId'))");
Console.WriteLine("");
Console.WriteLine("if @df <> ''");
Console.WriteLine("begin");
Console.WriteLine(" select @command = 'alter table ' + @tableName + ' drop constraint ' + @df");
Console.WriteLine(" print @command");
Console.WriteLine(" execute sp_executeSQL @command");
Console.WriteLine("end");
Console.WriteLine("else");
Console.WriteLine("begin");
Console.WriteLine(" print 'No default constraint found for column ' + @columnName + ' on table ' + @tableName");
Console.WriteLine("end");
Console.WriteLine("");
Console.WriteLine("select @command = 'alter table ' + @tableName + ' alter column ' + @columnName + ' varchar(max) not null'");
Console.WriteLine("print @command");
Console.WriteLine("execute sp_executeSQL @command");
Console.WriteLine("");
string constraintName = "DF_" + table.TableName.Replace("[", "").Replace("]", "") + "_" + column.Name;
Console.WriteLine($"select @command = 'alter table ' + @tableName + ' add constraint {constraintName} default '''' for ' + @columnName");
Console.WriteLine("print @command");
Console.WriteLine("execute sp_executeSQL @command");
Console.WriteLine("print ' '");
Console.WriteLine("");
Console.WriteLine("");
}
Console.WriteLine(" ");
}
However, this brought out an issue I had forgotten, which was a legacy from Lightswitch.
One of the (few) things I didn’t like about Lightswitch was the way they assumed that any non-null column in the database was required. This meant that you had to use nullable columns a lot, otherwise the user was forced to enter something, which was usually inappropriate and very annoying.
Now maybe it’s just me, but I don’t like null strings. I don’t know why the default value for strings in C# is null, it seems totally wrong to me. I would never (other than in the Lightswitch days) have a nullable varchar column in my database. However, it was too late, the design decision was made, and we’re stuck with it.
When I tried to run the SQL generated by the Linq above, I got errors, as I was trying to set a default value ('') on all varchar columns, even though many of these contained null.
So, it was back to LinqPad to do something about this. The following script generates SQL to set all null varchar values to be empty strings…
foreach (MetaTable table in Mapping.GetTables()
.Where(t => t.TableName != "[sysdiagrams]"
&& t.RowType.DataMembers
.Any(dm => dm.DbType != null
&& dm.DbType.ToLower().StartsWith("varchar")))) {
foreach (MetaDataMember column in table.RowType.DataMembers
.Where(dm => dm.DbType != null
&& dm.DbType.ToLower().StartsWith("varchar") && dm.CanBeNull)) {
Console.WriteLine($"update {table.TableName} set {column.Name}='' where {column.Name} is null");
}
}
Once that was run, the previous SQL could be run to alter the columns to be varchar(max).
Be First to Comment