Drop a SQL Server database from your .net code

Have you ever been in a situation where you need to provide uninstall funcitonality for database installation? Imagine you have just created your nice new database in Sql server and all the scripts have been created but at the last line something happens and you need to rollback. This also includes dropping the database and it isn't straight forward. Closing your connection(s) doesn't help as they don't close straight away. While looking for a solution I came accross one and decided to share it here.
This is the method I created:

private string GetKillProcessStatement(string database)
{
string result = string.Empty;
StringBuilder sql = new StringBuilder();

sql.AppendLine("DECLARE @SQL varchar(max)");
sql.AppendLine("SELECT @SQL = COALESCE(@SQL,'') + 'Kill ' + Convert(varchar, SPId) + ';'");
sql.AppendLine("FROM MASTER..SysProcesses");
sql.AppendLine("WHERE DBId = DB_ID('" + database + "') AND SPId <> @@SPId");
sql.AppendLine("SELECT @SQL");

SqlConnection dropDbSqlConn = new SqlConnection(_masterConnStr);
dropDbSqlConn.Open();

using (SqlCommand sqcomm = new SqlCommand())
{
sqcomm.Connection = dropDbSqlConn;

sqcomm.CommandType = CommandType.Text;
sqcomm.CommandText = sql.ToString();

sqcomm.Prepare();

object obj = sqcomm.ExecuteScalar();

if (obj!=null)
result = obj.ToString();
}
dropDbSqlConn.Close();
return result;
}
A few clarifications: _masterConnStr is the connection string to the master db on this server. If you need to create a db you will probably already have this one available. In this case it is a private member in the installer class. I explicitly need it to be inline sql as i don't want to install stored procs in other databases on the server. Database access rights to do that will be available because the user we require also needs to create dbs.
The inline sql when executed returns a list of kill statements like this one: 'Kill 11;Kill 23;Kill 45;' . The numbers will be the process ids in Sql Server. Once you get this you are just a simple ExecuteNonQuery of this string before you will be able to drop your database regardless of the number of connections on it. Ok I know there might be more arguments for connection initiated between your kill execute and your drop statement execute but there is cure for this as well. If this is a worry then execute this sql script before you issue the drop statement:
"ALTER DATABASE [" + database + "] SET SINGLE_USER WITH ROLLBACK IMMEDIATE";
I wouldn't be bothered to do so as in my case this is a newly installed db and there is no chance someone will know about it.
Back to the point I believe this is a good solution to ensure that you can drop a sql server db and the statement won’t fail because of open connections.

C# Regex to split multiple SQL statements

You know if you want to use SQLCommand to execute a big list of SQL Statements you cannot have the reserved SQL word GO in the script. So ideally you would want to split these statements to exclude the "GO"s and (perhaps in a transaction) execute each line separately. I wanted one of those for long time and was getting decent results with "^GO" + the multiline option, like this:

Regex regex = new Regex("^GO", RegexOptions.IgnoreCase | RegexOptions.Multiline);

But with xml columns in tables this quickly becomes useless - Yes I script data to insert statements also. Then finally today I found one that seems to be a lot better or at least works with all my data. Here it is:

string[] lines = Regex.Split(sql, "\\s+GO\\s+|^GO\\s+", RegexOptions.Multiline);


And NO I do not know what it means - I only know that it works ;)

“Scaling agile is the last thing you want to do” — Martin Fowler

Here's some alchoholic perspective about Scrum. I quite enjoyed reading it.
Now, seriously it is an excellent REAL world example and I recommend reading this article before beginning any scrum scaling.

SCRUM: Lessons learned

I wrote this article several months ago but only managed to publish it now which actually helped me improve it. Enjoy ;)

First project with Scrum (Lessons learned)

I know the first thought that will come to almost everyone’s mind when you read the title will be – yeah we all know that, but is it that simple in practice? This article tells a story about how all those “waterfall” years’ affect people’s thinking about releases and what we’ve learned from experience.

When on a sunny day in June we have had our first ever planning meeting I didn’t really realise what kind of roller coaster I am getting on. This planning meeting happened to be in the middle of a huge project aiming to release five products in total, consisting of three majorly reworked and two brand new products. To make things more clear about the size of the work: our decent spec build server takes 2+ hours to build the full set.

As the person responsible for the core products in the company at that time I welcomed the idea about scrum and joined forces with Rich and Sri to persuade senior management that we should go with implementing Scrum in an attempt to save this release which by that time was already 2 months late. While we had the full senior management support once we started doing sprints a few things seemed to have changed immediately in the entire organization. First everyone without really understanding Scrum expected that this “new way of working” will somehow “magically” enable us to release the software in a few weeks time and second they reminded us that they need the whole lot of functionality by a certain date and this is business critical. This of course added some more pressure to the team which was the last thing we needed.

Lesson number 1 - “End of the world” mentality
There is one phrase that will always remind me about this specific release – “good that we found it now”. This of course refers to discovering a fault and the fact that we’ve discovered it before doing the release. To me it pictures a big problem – we are so much focused on getting things right by a specific deadline that we forget a few simple facts – one - whatever we’ve found does not come from the end user hence might really not be that important to fix; and two - this is not the last release we will do and as long as we release often customers should get what they ask for. So the lesson learned was plan regular releases that add small amount of functionality to reduce the pressure.

Lesson number 2 – All the legacy stuff

Yes we’ve introduced Scrum in the middle of a big chaotically executed waterfall. We’ve inherited tons of legacy code (not documented, not unit tested and some of it not even integrated) and this affected our productivity. We had to add additional effort to fully automate builds and maintain continuous integration but yet once again we ended up with a whole lot if integration issues at the end. We had many problems with transforming from traditional to agile testing and perhaps we in fact made very little progress during the course of the project.
So is there a way to predict these problems? I would say no. But this doesn’t help a lot when senior management is asking for dates. So what would you do? First of all always answer with a range and don’t afraid if there are many unknowns to use a wide range. If management doesn’t like it explain this is due to too many unknowns and then ask for more time to research. If they don’t understand at least you know that there isn’t a better way to solve the problem and if they don’t understand then ask them do they really understand and support Scrum.

Lesson number 3 – Integration, integration…

Back to the time when we were in the middle of the mess and tried to sort it out we failed to start integrating straight away. Of course there was so much to do with sprint organisation and backlog management and all those unfinished features and task but still it was a mistake. We found out about it 2 months later when we realised that all the integration bits between four projects are now broken. So like on a typical waterfall project we started integration in the last 3 sprints and of course we paid the price for it. Now the word “integrate” is pretty much part of our DONE definition and really that says it all about integration.

Lesson number 4 - How long does regression testing take?

When I asked this question for just one of our products the answer I received was – two months if done by one tester. We should have really packed our stuff and gone home if we were to do that. Waterfall testers will refuse to think about automation. They will try to persuade you that it is extremely difficult and very slow to do and in fact not really worth the effort as it cannot cover “a lot” of the testing plan. Lesson learned was- throw the plan in the bin immediately and ask the whole team to find a better way to test. And the team effort will do it.

Lesson number 5 – Do we really understand it?

I was really pleased about the number of trainings we have done about Scrum and the overall understanding seemed to be good however it turns out to be a battle you have to win over and over again. Is it because of the waterfall mentality which is difficult to change or because some people get it and some not? I am not ready to give final answer however we all need to understand that coaching is needed pretty much all the time. And making assumptions that “yes after this training they really seem to get it” is not helping. We learned that we need to continue coaching with the same patience and enthusiasm as on day one.

Actually thanks to a lot of those “good that we found it now” problems we spent 2 months stabilizing the whole set of products. Really the root cause to me was the size of this work, the fact that we’ve been asked to release so much in one go and the deadline wasn’t any flexible.

The good point is that we learned a lot about Scrum, ourselves, software development, and why waterfall is bad. One year later we know learning never ends but I believe we appreciate what we’ve been through and we value what we’ve got.