How to manage your SQL database schema

When I first started at the company I’m at now, I was a little surprised to see how updates to the database schema were handled.  You had to ensure that you created a SQL script for all of the changes, and then put them in a shared folder.  Each script would have a file name like ’01 – Alter some table.sql’, ’02 – Create some sproc.sql’, etc.

It was a bit of a nightmare especially when it came to syncing to multiple environments such as local, development, staging and production.

That’s when I introduced Microsoft’s SQL Server Data Tools. SQL Server Data Tools allows you to create a database project in Visual Studio which has all of the objects you would have in SQL Server, such as tables, views, stored procedures, linked servers, functions, custom types, certificates, etc.  The bonus feature is that Visual Studio will tell you if you have any errors, for example, a stored procedure that references an invalid column.    Traditionally, if you just made an update to a table, such as renaming or dropping a column, all of your dependent objects like stored procedures would break, but you wouldn’t know until it’s too late.

Within your database project, Visual Studio will tell you if you have any broken stored procedures.  Here’s what happened when I renamed a column in Visual Studio:

SQL error

I now know right away that I broke the stored procedure AdmGetUsersByClient.

Source control your schema

One of my favorite features of having a database project in Visual Studio is that you can now add it to source control like Git or TFS.  This gives you the ability to easily manage changes to the schema and also see a history of who change what.  You have a historical view of your database schema as well.  This was probably one of the greatest benefits that our company received when I introduced the database project concept.

Integrate with your continuous build server

In our workflow, we push out a nightly build to our QA environment.  This wasn’t possible before since the database had to be manually updated, but now with the database project, we can automatically push changes out of our application and database schema. The more processes that you can automate, the less chance you have for messing something up.

Deploy changes easily

When it comes time to push our application to our production environment, it becomes the DBA’s responsibility to update the database schema for us since the development team doesn’t have access to the production environment.  This is common practice for many companies and helps to minimize security vulnerabilities, and also puts the burden of being responsible for the production database on the DBA’s shoulders.  Using the SQL Server data tools, you can create DACPAC files, which are essentially ZIP files that contain a snapshot of your database project.  The DBA can then publish these using the sqlpackage command line tool.

Uncategorized

“Heartbleed” vulnerability found in OpenSSL. Make sure you are not vulnerable.

http://heartbleed.com/

The Heartbleed Bug is a serious vulnerability in the popular OpenSSL cryptographic software library. This weakness allows stealing the information protected, under normal conditions, by the SSL/TLS encryption used to secure the Internet. SSL/TLS provides communication security and privacy over the Internet for applications such as web, email, instant messaging (IM) and some virtual private networks (VPNs).

The Heartbleed bug allows anyone on the Internet to read the memory of the systems protected by the vulnerable versions of the OpenSSL software. This compromises the secret keys used to identify the service providers and to encrypt the traffic, the names and passwords of the users and the actual content. This allows attackers to eavesdrop on communications, steal data directly from the services and users and to impersonate services and users.

You can find out if your site is affected by visiting this page:

http://filippo.io/Heartbleed/

Security , , ,

Recursively chmod only files or directories in Linux

I had a bit of a problem recently where I wanted to recursively remove the executable bit from all files, but also ended up removing the bit from directories when I used “chmod -x * -R”, which then caused a web server running nginx to not serve up content from those directories any more.  I needed a way to fix this and only chmod directories and re-add the executable permission.  Fortunately, I found a way to do so.

Using the “find” command, I was able to accomplish this:

find . -type d -exec chmod 755 {} \;

Conversely, if I wanted to only chmod files, I could do this:

find . -type f -exec chmod 644 {} \;

The -type parameter allows me to specify only directories (d) or files (f).

Uncategorized , ,

LINQ query for a value in a set of ranges

A while back, I was coming up with a LINQ query that could see if value in the database was between a set of ranges.

Take for example, I have a Person record in a database, and there’s an Age field for the person.

Now I have a page that allows me to filter for people in certain age ranges.
For example, I can choose multiple range selections, such as “0-10″, “11-20″, “31-40″.
So in this case, I’d get back a list of people between 0 and 20, as well as 30 to 40, but not 21-30.

I’ve taken the age ranges and populated a List of ranges that looks like this:

class NumberRange
{
  decimal Min { get; set; }
  decimal Max { get; set; }
}

List<NumberRange> ageRanges = GetAgeRanges();

With a little bit of help from the small PredicateBuilder class, I was able to write an extension method for IQueryable.

static IQueryable<T> InRange<T>(this IQueryable<T> query, Expression<Func<T, decimal>> predicate, IEnumerable<NumberRange> ranges)
{
  var exp = PredicateBuilder.False<T>();

  foreach (var range in ranges)
  {
    exp = exp.Or(
      Expression.Lambda<Func<T, bool>>(Expression.GreaterThanOrEqual(predicate.Body, Expression.Constant(range.Min)), predicate.Parameters))
    .And(Expression.Lambda<Func<T, bool>>(Expression.LessThanOrEqual(predicate.Body, Expression.Constant(range.Max)), predicate.Parameters));
  }

  return query.Where(exp);
}

So now let’s say I have a collection of Persons (People) and I want to get people within only a specific range.
I could do the following

people.InRange(person => person.Age, ageRanges);

And get just the people in the range I want.
At work, we’re actually doing this for a range of prices, which is why I’m using the decimal type in this example. If you were doing ages, the Integer or Short type would be more appropriate.

Software Development , , , ,

The value of an ORM (Object-Relation Mapper)

Lately at work, we have been interviewing new candidates for software developers and one question that we like to ask is their experience with ORMs, like Entity Framework or NHibernate. Not surprisingly, we run across a few that don’t believe in them and would rather do everything with stored procedures.   I’m talking about simple select statements, or a simple insert statement.

I used to be with the camp that believed that you should leverage stored procedures as much as possible, especially for performance and security reasons, but throughout my career, my mindset has changed.

Here are some advantages over each:

Stored procedures

  • Stored procedures, or sprocs, have the benefit that you have full control over the SQL code that will be executed. If you’re able to write some efficient queries, this can come in really handy… however, I’ve seen sprocs that are really poorly written. For example, I saw one that inserted a bunch of data into a temp table, then deleted the unneeded data from the temp table.  This was a very wasteful operation.
  • You can set permissions for sprocs individually. This gives you more granular control over security, but then again, if you have a ton of sprocs, then this can be a pain to maintain.
  • Performance. It used to be that the fastest way to execute a SQL query was through a sproc, since the query plan would be compiled and saved. However, SQL server caches query plans for regular queries so there isn’t a performance benefit any more.
  • Easier to change on the fly. If you need to make a change to a sproc in production, it’s simple enough to do without redeploying your application.

ORMs

  • If you change your table schema and then re-generate your classes, there’s a good chance that your code won’t build anymore.  This is a good thing, since it catches all the errors and forces you to fix them. With sprocs, if you change your table schema, you now have a bunch errors in your sprocs and you may not even know it until you try to execute them.
  • One of the biggest advantages is of course time.  The amount of time that it saves you from manually generating mapping classes and entity/DTO classes is huge.
  • Easier to change databases.  Many ORMs have support for multiple database types. For example, if your company decided to switch from MS SQL Server to Oracle, the ORM could abstract that away for you and you would have to do a lot less work to switch over.
  • Better extensibility. ORMs like Entity Framework give you several code-generation templates that give you the choice of how you want your code to work.  You can of course also create your own T4 code generation template.  You can also configure some of these to use built-in caching mechanisms.
  • Easier to debug. MS SQL Server does have the ability to debug SQL but it’s not the same as debugging actual code.

Personally, I think the benefits of using an ORM like Entity Framework outweighs the benefits of using stored procedures for all your data access.  However, for more complex operations that work on a lot of data, I still use stored procedures.

In addition, it doesn’t make sense to put your business logic into stored procedures. Far too often, I see complex business logic inside of large sprocs which are harder to debug and prone to errors.

I’d love to hear some responses as to which you like better and why.

Software Development , , , ,

Do you have what it takes to “hack”?

Do you think you have what it takes to be a “hacker”? You can test out your knowledge of finding vulnerabilities and cracking ciphers legally by heading over to http://www.thisislegal.com .

They offer some basic security tutorials and some basic information on cryptography and ciphers.  Being knowledgeable with the web and web development also comes in handy.

Personally, I enjoy solving problems and puzzles so this was a lot of fun for me.  Feel free to take a look at my profile page too, which allows you to see how I am doing:  http://www.thisislegal.com/user/makotosan

 

Uncategorized

Now Hiring

DigitalRisk is now hiring for the following technical positions at their Maitland headquarters.

Business Intelligence Developer (1 opening in Orlando)

This position involves data analysis, data consolidation, layout, design and technical report development using SQL Reporting Services (SSRS) and the Information Builders (IBI) InfoAssist toolsets; performing analysis, design, development,  implementation, testing and deployment for all management reports and dashboards that utilize SSRS and IBI; and working with the DBA to ensure that the necessary reporting fields and structures are available within our data warehouse architecture.

Requirements: strong experience with SSRS, SharePoint 2010, MS SQL 2008/2008 R-2, and SSRS integration with SharePoint 2010

Sr. Business Analyst (1 opening in Orlando)

The SBA will be expected to help define the product roadmap and ensure that customer requests are identified, refined, documented and scheduled; work with Software Quality to ensure that the release is satisfactory to the user base and all necessary requirements have been incorporated; define and document process flows and find opportunities for process improvement; and implement process flows within a Business Process Management platform.

Requirements: strong experience with Visio and familiarity with BPMs

 

Software Developer (2 openings in Orlando)

This position involves front-end web and back end database development on several complex analytic and financial systems at various stages in the software development life cycle; architect, design, develop and release quality web based and middle tier software products utilizing Visual Studio 2010; develop and tune complex stored procedures (T-SQL) in SQL 2008; and write development documentation and test plans for the products developed.

Requirements: MS Visual Studio ASP.Net experience using C# language

 

Network Aministrator II ( 1 opening in Orlando)

This person will be responsible for performing Network Administration duties for networking hardware; maintenance and operation of IT Services; installation, configuration and maintenance of both physical and virtual computer servers; rolling out of hardware and software to ensure optimal deployment of resources; etc.

Requirements: Current Cisco CCNA Certification, MSCE, 6+ years of experience with Windows-based servers and Cisco networking, and experience with SAN

Read more »

Software Development , ,

Debugging .NET Windows Service apps more easily

Here at work, I keep seeing a couple of my fellow developers struggle to debug their Windows Service apps that they’re writing. What they have to do is remove the service, install the new version of the service, start the service, then attach the debugger.

If there’s an error when the service first starts up, the workaround is to introduce a Thread.Sleep before it actually does anything, giving you enough time to attach the debugger.

By default, you can’t directly run the service from within Visual Studio. If you try, you get the following message:

Windows Service Start Failure

If you look at the code that actually starts the service, you’ll see that it’s not that different from a console app.

By default, it looks something like this:

  
    internal static class Program
    {
        // The main entry point for the application.
        private static void Main()
        {
            ServiceBase[] ServicesToRun;
            ServicesToRun = new ServiceBase[] { new Service1() };
            ServiceBase.Run(ServicesToRun);
        }
    }

And the Service1 class looks like this:

    public partial class Service1 : ServiceBase
    {
        public Service1()
        {
            this.InitializeComponent();
        }

        protected override void OnStart(string[] args)
        {
            Console.WriteLine("Hello, world!");
        }

        protected override void OnStop()
        {
        }
    }

The code that runs when the service Starts happens in OnStart() . There are other events that you can override, such as OnPowerEvent, OnSessionChange, OnShutdown, but for the most part, you’d primarily be concerned about OnStart.

So how can we get a Windows Service to run like a console app? Pretty simply actually. I’m just going to post the solution here and walk you through it:

 
internal static class Program
    {
        // The main entry point for the application.
        private static void Main(string[] args)
        {
            var service = new Service1();
            if (Environment.UserInteractive)
            {
                service.Start(args);
                Thread.Sleep(Timeout.Infinite);
            }
            else
            {
                var servicesToRun = new ServiceBase[] { service };
                ServiceBase.Run(servicesToRun);
            }
        }
    }

First, you should change the signature of Main() to include an array of arguments.

 
private static void Main(string[] args)

Next, we’ll want to create a new instance of Service1 and keep that in a variable.

 
var service = new Service1();

Now comes the magic part. We want to be able to check whether the app is being ran as an application, or as a service. How can we determine that? Simple:

 
if (Environment.UserInteractive)

The above code checks if the application is being ran by the user, or as a service. If it’s being ran by the user, then we want to treat it like a console application, otherwise, run it as a service. This gives us the flexibility of being able to run it in two different modes.

Next, you’ll see me calling a method on Service1 called “Start” instead of “OnStart”. The problem is that OnStart is protected, which means we can only access that method from derived classes, and since Program is not deriving Service1, we can’t access that method. The simplest way to be able to call the “OnStart” method would be to just add a public method to Service1 that will do it for us:

public void Start(string[] args)
{
this.OnStart(args);
}

And now you can run and debug your service from Visual Studio without having to go through the trouble of installing it. Once you feel that you have it working, you can then install it by creating a Installer Project, or manually installing it using the “installutil”.

Software Development , ,

Properly disposing COM objects in your .NET code

Here at work, we are using a third party component where they provide a COM interface.

We’re using .NET code so we are using COM interop to use the COM object.

Remember back in the VB6 days when you would set your objects to null when you’re done with it? Well, so did someone else here, because that’s what I saw.

I’m going to show you an example using the Microsoft Excel Type Library.

Here’s some sample code that I wrote that will open up an Excel file and get the text value from a cell in the first worksheet.

    using System;

    using Microsoft.Office.Interop.Excel;

    internal class Program
    {
        #region Methods

        private static void Main(string[] args)
        {
            var app = new Application();
            var books = app.Workbooks;
            var book = books.Open(@"C:\Temp\doc.xlsx");
            var sheets = book.Sheets;
            var sheet = sheets.Item[1] as Worksheet;
            string cellText = string.Empty;

            if (sheet != null)
            {
                var cells = sheet.Cells;
                var cell = cells.Item["2", "B"] as Range;
                if (cell != null)
                {
                    cellText = cell.Text.ToString();
                    cells = null;
                    cell = null;
                }

                sheet = null;
                sheets = null;
            }

            app.Quit();

            books = null;
            book = null;
            app = null;
            Console.WriteLine(cellText);
            Console.ReadLine();
        }

        #endregion
    }

See what I did there, I set all of the object references to null, which is how it was done back in the VB6 days, but we’re using the .NET CLR. Things are different.

So what the program does is essentially fires up an instance of excel.exe, and then does what I asked it to do which in this case is to just get text from a cell.

The problem is that after my program finishes… excel.exe is still running even though I called “app.Quit()”. That’s because it can’t quit while I still have references to the objects. Simply setting the references to null didn’t do the trick. The managed runtime of the .NET CLR doesn’t know how to garbage collect unmanaged COM Objects, so even though these references are null, they won’t be properly disposed.

So how do you properly dispose of these references? By using Marshal.ReleaseComObject or Marshal.FinalReleaseComObject. Click on the links to read more about each method.

Here’s the new version of the program:

    using System;
    using System.Runtime.InteropServices;

    using Microsoft.Office.Interop.Excel;

    internal class Program
    {
        #region Methods

        private static void Main(string[] args)
        {
            var app = new Application();
            var books = app.Workbooks;
            var book = books.Open(@"C:\Temp\doc.xlsx");
            var sheets = book.Sheets;
            var sheet = sheets.Item[1] as Worksheet;
            string cellText = string.Empty;

            if (sheet != null)
            {
                var cells = sheet.Cells;
                var cell = cells.Item["2", "B"] as Range;
                if (cell != null)
                {
                    cellText = cell.Text.ToString();
                    Marshal.FinalReleaseComObject(cells);
                    Marshal.FinalReleaseComObject(cell);
                }

                Marshal.FinalReleaseComObject(sheet);
                Marshal.FinalReleaseComObject(sheets);
            }

            app.Quit();

            Marshal.FinalReleaseComObject(books);
            Marshal.FinalReleaseComObject(book);
            Marshal.FinalReleaseComObject(app);
            Console.WriteLine(cellText);
            Console.ReadLine();
        }

        #endregion
    }

Give it a try yourself. Run the app and keep an eye on task manager. When the app gets to the last line, Console.ReadLine(), you’ll notice that excel.exe is still running in the first version of the code. Now run the second version and wait for the program to get to the last line, you’ll notice that excel.exe is not runnning.

Useful links:
Mixing deterministic and non-deterministic cleanup

Software Development , , ,

Leading zeros in SQL

A while back at work, I ran across a SQL stored procedure called ‘fixLeadingZeros’ that has the below implementation.

It’s actually much longer than this since it does it for several columns…. and some columns have longer values. In other words, if the field is supposed to have 8 digits, it has 8 statements.  I couldn’t help but to feel a bit disgusted from it and had to share it.

UPDATE [SomeTable]
SET SomeColumn = '0' + [SomeColumn]
WHERE
len (SomeColumn) =4

UPDATE [SomeTable]
SET SomeColumn = '00' + SomeColumn
WHERE
len (SomeColumn) =3

UPDATE [SomeTable]
SET SomeColumn = '000' + SomeColumn
WHERE
len (SomeColumn) = 2

UPDATE [SomeTable]
SET SomeColumn = '0000' + SomeColumn
WHERE
len (SomeColumn) =1

UPDATE [Some]
SET SomeColumn = '00000'
WHERE
SomeColumn ='' OR SomeColumn is null

This could have been done a LOT easier.  If your goal is to update existing columns so that they have leading zeros, all you’d need to do is this (assuming the column is a varchar type already and we wanted a 5 digit value):

UPDATE [SomeTable]
SET SomeColumn = RIGHT('00000' +  SomeColumn, 5)

What this does is specifies a string that has 5 zeros and the value of the column, ensuring that we have at least a string with 5 zeros.  Then we just want a subset of those values, from the right. In this case, we only want 5 characters from the right.

Let’s say the value of SomeColumn is ’123′.   We are then combining ’00000′ with ’123′ to give us ’00000123′.

We then only take the 5 characters on the right, giving us ’00123′.

Voila! Leading zeros.

Software Development