Verify Database Access with PowerShell

Even though there is no SQL Client tooling installed on a machine, PowerShell can be used to execute SQL. I recently had to verify that I had been granted Dbo rights on a database that was on a server only reachable from a web server. The web server of course had no SQL tooling whatsoever installed. To do that I created a small Powershell script that only relies on the .NET Framework.

The typical recommendation for executing SQL commands from powershell is to use the Invoke-Sqlcmd cmdlet. I’m sure its a good tool, but unfortunately it isn’t installed by default. Sometimes installing extra software is not an option, so instead I’ve used PowerShell’s built in capability to create and use .NET objects. The Sql client objects are included in the default .NET framework installation, so this should work on any Windows machine.

Enums and Lookup Tables with EF Code First

With EntityFramework’s support for enums, there is no longer any need to include lookup tables in the model. But I do want to have them in the database for integrity, even with code first.

I’ve been thinking for some time about to handle enums with code first. The idea behind code first is to be able to write the code as close as possible to how object oriented code is normally written. For enums that means that the enum definition itself is the constraint that is used to ensure that only valid values are used in the code.

For databases it would be possible to use a column constraint, but the normal way is to use a lookup table where the valid values are present. Any column in the database mapped against the enum type is then a foreign key to the lookup table to ensure integrity of the data.

What I would prefer is a solution where the lookup table is present in the database, but not mapped against any entity in the code.

On Null or Why Empty Strings are not Same as Null

Null is an important, but sometimes hard concept. What’s the difference between an empty string and a null string? One of my first Stack Overflow questions was (NOT) NULL for NVARCHAR columns.

Some people claim that using null is always wrong or is some kind of voodoo.

This time Mike is wrong. There is a place for null, including Nullable<bool> and Nullable<int>.

Posted in C#, SQL on 2013-01-07 | Tagged

Avoid broken Views in SQL Server with Schemabinding

SQL Server views can get really weird when an underlying table is updated. I’ll show some examples and also the one must do for views to avoid problems.

The broken views problem is something I discovered when working on a production system. When I first observed it I was shocked. When I dug deeper into it I was even more shocked at the pure evilness this behaviour could cause. But let’s take it from the start with a simple table of colours and a view on top of it to start with.

CREATE TABLE [dbo].[Colours] (
    [Id]     INT           IDENTITY (1, 1) NOT NULL,
    [Colour] NVARCHAR (10) NOT NULL,
CREATE VIEW [dbo].[BadView]

Populate the table with some data and test the view.

Id          Colour
----------- ----------
1           Red
2           Green
3           Blue
4           White
5           Black

Now, let’s break havoc to the view. I’ll recreate the table with another column RGB which is inserted right after the Id column. The Colour column is renamed to ColourName. Before running the next test, I’ve inserted the appropriate RGB values in the new column.

Id          Colour
----------- ------
1           FF0000
2           00FF00
3           0000FF
4           FFFFFF
5           000000

This is what I consider weird behaviour. The output of the view still contains two columns with the names Id and Colour. That would be good – if it contained the right data. It doesn’t. The data is taken from the second column of the underlying table. If you’re not scared enough by this I have some more, even worse things to show…

Dissecting MVC Scaffolded Code for EF Updates

MVC and Entity Framework scaffolding is powerful, but I prefer to know what actually happens before accepting generated code. The code generated to update an entity when an edit has been done looked interesting, so I decided to have a closer look at it.

When an edit is done to an entity, the data is posted as a simple collection of form elements from the web browser. Remember that when working with MVC, we’re closer to the metal than with Web Forms and we are fully exposed to the stateless nature of the web. The gigantic hidden ViewState form field is gone, leaving us in a clean, but stateless world. When the updated data is posted to the server, the server has no more info about it than available in the request. The server doesn’t know where it came from and has to find out itself where, how and what part of it to update.

Tracking Changes

Tracking changes for objects moving across tiers, being disconnected from their original data source is always tricky. I’m still looking for the silver bullet to this problem and one step in the search is to investigate what other have done. In this post, I’ll take a closer look on how the Scaffolded code of MVC handles updates to EF Entities.

Software Development is a Job – Coding is a Passion

I'm Anders Abel, an independent systems architect and developer in Stockholm, Sweden.

profile for Anders Abel at Stack Overflow, Q&A for professional and enthusiast programmers

Code for most posts is available on my GitHub account.

Popular Posts



Powered by WordPress with the Passion for Coding theme.