Jun 04

SQL Server Case Sensitive Queries

Recently, I’ve been tasked to create a data mask function.

They want:

  • A number will be a number
  • An upper case letter will be upper case
  • A lower case letter will be lower case
  • Punctuation and symbols will remain the same

An example would be:

123-00-6789 would become 456-88-4321
Mark would become Trxz

To enhance obfuscation, it will only be used one-way, and a 1:1 character swap is not necessary. Therefore, an uppercase A could be an upper case Z and an upper case X in the same sentence.

Thanks to this article at Novick Software, I was able to come up with the following example for future use.


--Create a table variable
DECLARE @Tbl TABLE (Name VARCHAR(100))

--Insert same name with different case
INSERT INTO @Tbl VALUES ('mark')
INSERT INTO @Tbl VALUES ('Mark')

–Gets the 1st (case-insensitive) result
SELECT DISTINCT Name FROM @Tbl

–Gets case-sensitive distinct results
SELECT DISTINCT Name COLLATE sql_latin1_general_cp1_cs_as FROM @Tbl

–Gets the distinct count (case-insensitive)
SELECT COUNT(DISTINCT Name) FROM @Tbl

–Gets the distinct count (case-sensitive)
SELECT COUNT(DISTINCT NAME COLLATE sql_latin1_general_cp1_cs_as) FROM @Tbl

Posted in SQL
Jan 23

8 Things You Should Ask When Hiring A Software Developer Friend

I just read a post about some things you should find out when hiring a software developer.

I won’t spoil the meaning, so go and check it out!

Here are the highlights

  1. Ask about Development Process
  2. Ask about Development Practices
  3. Ask how they Stay Sharp
  4. Ask for Proof
  5. Ask how they Estimate
  6. Ask about Deadlines
  7. Ask about Cost
  8. Ask about Change

Well there you have it. Now go check out the post to fill in the blanks.

Aug 29

A New Look

Welcome dear reader!

You may have noticed that there is now a new look to the site. I appreciated the Twenty Ten Theme that came as the default wordpress install, but it really just wasn’t me. My personal preference is for a dark outside and a light inside. Therefore, I give you a theme is based on StudioPress by Daily Blog Tips.

But of course, I have modified it to really suit what feels best to me.

Screen real estate is very important to me, and I don’t like unnecessary white space where content could live.

Here are the updates that I have made (in no particular order):

  • Removed Monster RSS Image
    Right away, there was a monster “Grab our RSS” image. It isn’t that hard to find if you are interested in getting the RSS feed, but it really didn’t need to be an eye sore. I may add a smaller image somewhere above the fold in the near future, but for now, you can find the feed in the footer.
  • H1 & H2 headers inline
    Since both my title and subtitle aren’t very long, I decided to use the css display:inline-block, and give a slight margin between them. This minimizes all the horizontal white space between both lines.
  • Background Blue Top
    There was a blue line that was 30 pixels tall that only contained the search bar in the top-right corner. Also a waste of horizontal white space.
  • Search
    Just because the Background image was cropped, that didn’t mean that the layout would flow the way I wanted it to. Therefore, I made the search header position:absolute, and then positioned it in line with the Page tabs.
  • Excess Padding/Margins
    And finally comes the excess padding and margins in all areas (mostly at the top). I reduced many top paddings to zero, and in some cases, made the values negative. I believe that there is still sufficient space to separate the content, so as to not just bleed into the background or be pushed right next to it.
  • Hid default “Welcome” message
    There is a Theme Option to have some text spread out above both columns used for widgets. Unfortunately I didn’t have anything to say, and therefore it defaulted in having some predetermined (and hard coded – Yikes!) text to let me know how to update it in the backend. Since I wanted it hidden, I removed the necessary code in the sidebar.php file. At some point I may make this into a newsletter subscription area, but it’s not ready for that just yet.
  • Hard Coded Categories
    Right after the install, I began tweaking, updating my widgets. I added the Categories and Archive widgets to the left side, and the BlogRoll to the right. A quick page refresh and I see 2 categories sections in the left. Since it wasn’t anything specially placed (like the search widget), I decided to remove the hard coded category placement in the sidebar.php file. The widget section is handling that part just fine, thank you very much.

After finding the theme that I thought would be best, it only took me about an hour or so to make the tweaks. One thing that really helped out was the FireBug plugin for Firefox. I would tweak the number, and it modified it instantly. I would then update the theme, and then tweak the next section. Many others have positive reviews, and recommend all web developers to utilize it. Consider me another one of those users.

One thing that I did wish that this theme had are drop down menu for sub pages. This may be a tweak I add at another point in time. I believe that TwentyTen had it, so it may be a pretty simple copy/paste/tweak job. That will add in both my PHP and JQuery skills, eh?

Enjoy!

PS, I wanted to do some before/after screen shots, but my wireless card on my laptop was acting a fritz, so it is on hold until I get some more time.

Jul 11

.NET Connection Strings – To Encrypt or Not to Encrypt

Recently I was tasked with researching connection string best practices, and employing them at the day job. We want a better way to manage all of the connection strings.

I suggested moving the connection strings into the machine.config unencrypted. This will accomplish multiple things.

  • Centralizes connection strings to one area for all projects residing on that server
  • Easy to search, maintain, and update since no encryption/decryption is required
  • “Set it and Forget it” – Deployments to the various environments (DEV, STG, TRN, PRD) won’t need to update connection string after every deployment cycle

But there are some downsides

  • Need to save across multiple .NET machine.config sources
    The simple workaround is to keep a configSource file in the root, and utilize a batch file to copy into each frameworks config directory (except 1.1 and below)
  • Unencrypted connection strings means an intruder has easier access to information (more info below)
  • Updating a connection string for one project has the potential of disrupting every project on that server
    But I hope you are doing these updates during off hours, so the impact will be minimal.

I will make some mentions of things in this blog that I really hope is not news to anyone. Having your own 2-way hashing/encrypting algorithm will only slow down a hacker. My experience has been that you must “hide” the key, but still reference it. A good file search tool can quickly point to this unlock key to get your connection string. By my estimation, the tool I use can sift through 80 megabytes of data (including text files, images, and binary documents such as PDFs, Word and Excel documents) in less than 8 minutes. Then using a decompiler they can easily get your information.

I was once tasked to take over a project where the previous developer was holding the source code ransom for a bigger bonus. With the awesome experience in programming I had developed over the years, it took me about 45 minutes to get everything I needed into a nice small package. And that was just so I had something pretty to work with, not just mining for anything specific.

I’m sure that if a somewhat experience hacker ever got into your machine, an encrypted connection string is only going to slow him down by maybe 15 minutes, assuming he already has the tools necessary to do his digging. After all, it’s all about the time. If he’s already gained access, knows what he’s looking for, and wasn’t taught the Boy Scout model of “Be Prepared” (even if it’s to be used for evil), the chances of him being caught will be much higher as he’s doing some Google research while breaking into your system.

So, my recommendation is to just keep unencrypted connection strings in the machine.config file(s), as it will save your Operations department many compounding hours in maintenance over the lifetime of the servers.

Mar 23

Why You Need a DLL Page For Your .NET Projects

This post is all about User Acceptance Testing, and why you’ll want a DLL page.

In any good development environment, you’ll want to make sure you have some things covered.
1. Source Control (Source Safe, Vault, Subversion, etc)
2. Documentation Tools
3. Test Plan

With the test plan, you’ll want to mark which DLL you are testing against. That way, you can tell if a release has been updated, or if deployment procedures haven’t been strictly adhered to. Of course, without automatic DLL versioning in the latter part of Visual Studio releases (more on this later), you as the developer will need to make these part of your deployment habits.

In your project, create a DLL.aspx page, and drop the following Grid View source where necessary

<asp:GridView ID="gvDLL" runat="server" AutoGenerateColumns="false">
	<Columns>
		<asp:BoundField HeaderText="File Name" DataField="FileName" />
		<asp:BoundField HeaderText="Version" DataField="FileVersion" />
	</Columns>
</asp:GridView>

And finally, put this in your DLL.aspx.vb page.

Imports System.IO

Public Class DLL
	Inherits System.Web.UI.Page

	Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
		BindDlls()
	End Sub

	Protected Sub BindDlls()
		gvDLL.DataSource = GetDllInformation(Server.MapPath(".") + "bin")
		gvDLL.DataBind()
	End Sub

	Protected Function GetDllInformation(ByVal psFolder As String) As DataSet
		'Create the (empty) DataSet object...
		Dim dt As New DataTable("DLLInformation")
		dt.Columns.Add("FileName")
		dt.Columns.Add("FileVersion")
		Dim ds As New DataSet()
		ds.Tables.Add(dt)

		'Now gather all of the DLLs
		If Directory.Exists(psFolder) Then
			Dim sFiles As String() = Directory.GetFiles(psFolder, "*.dll", SearchOption.TopDirectoryOnly)
			For Each sFile In sFiles
				Dim fvi As FileVersionInfo = FileVersionInfo.GetVersionInfo(sFile)
				Dim dr As DataRow = ds.Tables(0).NewRow()
				Dim fi As New FileInfo(sFile)
				dr("FileName") = fi.Name
				dr("FileVersion") = fvi.FileVersion
				ds.Tables(0).Rows.Add(dr)
			Next
		End If

		Return ds
	End Function
End Class

To use it, simply send your testers to http://domain/DLL.aspx.

Enjoy!

Mar 17

Why hardcoding is bad, especially in .NET

So, I had a habit of doing inline navigation by doing the following “Lazy Loading” method (code from administration navigation that handles look up tables):

Private Sub ChangeNavigation(ByVal pLinkButton As LinkButton)
	lnkUsers.CssClass = ""
	pLinkButton.CssClass = "selected"
	Select Case pLinkButton.ID
		Case "lnkUsers"
			mvData.SetActiveView(vwUsers)
			BindUsers()

		Case "lnkDivisions"
			mvData.SetActiveView(vwDivisions)
			BindDivisions()

		Case "lnkWithdrawalTypes"
			mvData.SetActiveView(vwWithdrawalType)
			BindWithdrawalTypes()

		Case "lnkMemoTypes"
			mvData.SetActiveView(vwMemoType)
			BindMemoTypes()

		Case "lnkWithdrawalTypePrefix"
			mvData.SetActiveView(vwWithdrawalTypePrefix)
			BindWithdrawalTypePrefix()

	End Select
End Sub

Somewhere down the line, a new request was made to make a certain item “plural”. This then caused some unintended consequences of having run-time issues because the string didn’t compare. And let’s not get started on the fact that it’s case sensitive. It’s actually one of the main reasons why I try to utilize enumerations whenever possible… They’re short, simple, and will break if you type it wrong.

Well, due to the issue, I realized I should utilize .NET’s compiler and properties to my advantage. With that, I turned

Case "lnkDivisions"

to

Case lnkDivisions.ID

VOILA. It slices! It dices! It compiles! Make an update to the text and it will still work. Rename the control without updating the case statement, and it will not compile. You are forced to fix it without stumbling across it during User Acceptance Testing.

Happy Coding!

Mar 07

Google Voice on Windows Mobile 6.5 for free

I had a roommate a while ago that was completely addicted to Google. He was there when the first G1 came out with Android, and followed them ever since. He was adamant about their openness, their programs, their tools… everything. One of the things he signed up for was Google Voice. Intrigued by it, I too signed up and was accepted into their system. At the time, I was with Helio, and of course, it wasn’t very friendly with everything I had going for me. So I kept it on the back burner, but still retained it.

That was about a year and a half ago. Fast forward to last week. I don’t know why, but for some reason, I got the itch to get back into it. I think it’s because I’m moving, and would like to get a Magic Jack line. But instead of people needing to learn all of the possible numbers I may come across, I’ll just use GV to organize it for them.

Anyway, I start to toy with it with just a few of my friends. Figuring out how to make my GV number display when I call or SMS someone. Turns out, it can be a huge pain in the tuchas. For starters, in order to make a call without using the web interface, you must dial into GV (using your GV number), log in, select “make a call”, key in the person’s phone number (the actual number of the person, as you can’t do a look up based on your google contact list), and then it will connect you… Annoying, right? Just mildly. The quick work around is to program this as their contact number into your phone, and it looks like this:
GGGGGGGGGG,PPPP,2,FFFFFFFFFF
G=Your Google Phone Number
P=Your Pin
2=Option to call a person
F=The Friend you are calling
,=My phones way of pausing
Now I need to go through all of my contacts just so I can utilize GV? OK, I can write a script to do this, and then sync it.

Oh, but what about texting someone?

You can’t text your GV number and then have the phone number it goes to as the first parameter. At first I was disappointed to discover this, but then soon realized that it would be an even bigger pain, since I couldn’t just program that into my phone for easability. Instead, I found that many people recommended to ship a text to the person using the web interface. On a response, you’ll get a phone number (owned by google), that when connected to your GV number will make a connection to your contact. That’s a long way to say that it maps out the following:
My Phone Number – My Google Voice Number – My Friends Google Translated Number – My Friends Number.
Keep in mind that “My Friends Google Translated Number” is probably shared among other GV subscribers. I don’t know (or care) how it works, just know that if you try calling the number NOT from a Google Voice registered phone of yours, I don’t know what would happen. I’m actually a little scared to find out. What if it connects me to a pizza place, and they force me to order some pizza? OK, so that might not be too bad…

Anyway, you store this number, and then you just make sure to text/call that number, instead of their actual number. This means you won’t have to reprogram that extremely hectic number for speed dial; but you will need to coordinate the incoming google assigned numbers… that’s going to be a pain.

But when I tried using this method with my already signed up for GV buddy, I got his GV number, not a new one… D’OH! And on top of that, he got my old number, not my GV number… CRAP! But I found a quick setting in the GV web interface that solved my problem. It’s under:
Settings -> Voice Settings -> Calls -> Caller ID (Outgoing) -> Display my Google Voice number
Works like a champ, but only for SMS (as of right now)

Well, I now can have 100% for all of my mobile wielding friends for calling and texting. But what about calling the people who have landlines, where I can’t just shoot them a text to get their new google assigned number? CRAP AGAIN!

In actuality, Android, CrackBerry (BlackBerry) and IPhart (IPhone) have an app for that… but Windows Mobile (and other prominent smart mobile OS’s) were left out. So I did what any good developer would do… I utilized Google, and did some searching to see if anyone else had already figured out this problem. Here is what I found…

For calling, you’ll want iContact, iDialer, and GreenButton. The three of them together make it much easier to navigate the GV tasks of calling. Their links are as follows:
http://www.supware.net/winmo/icontact/
http://www.supware.net/winmo/idialer/
http://www.supware.net/winmo/greenbutton/

For SMS, you’ll want “Google Voice Easy SMS Version 2.0”. It’s a pretty lightweight app that will send messages over 3G or WiFi (in case you pay per SMS).
http://www.mattslifebytes.com/?p=160

And on my HTC HD2, I simply cleared out two of my shortcuts so that one can be iContact, and the other is Easy SMS. It is still a pain to get a text, close it, and then respond in Easy SMS, but that will be all the more reason to hurry up quicker to get an Android based phone, where it’s all built in.

Now for the fun task of letting everyone know I have a new number, and they should start using it…

Enjoy!

Mar 02

Simple SQL Pivot with Example

A colleague was interested in solving a reporting problem for our business users.  And as many times go, I ask him what he wants the end result to be, so that I can figure out how I would tackle the problem.  He explains that he wants the following:

License # #1 #2 #3
0001 2000 500
0002 500
0003 200 100

This table represents a Personal Identifier (Drivers License ID, Business ID, etc), followed by fine amounts (the lower number being the most recent fine).  Other examples will use column headers of other known values such as product lines (Apples, Oranges, etc), or dates (Jan 2011, Feb 2011, etc) as summation values.

Let’s use the following Table Variables as our data model:

DECLARE @Person TABLE (
	PersonID INT IDENTITY,
	LicenseNumber VARCHAR(10)
)
DECLARE @Ticket TABLE (
	TicketID INT IDENTITY,
	PersonID INT,
	TicketDate DATETIME,
	TicketAmount DECIMAL(19,2)
)

To me, it seemed that a “Pivot” table was in order.  Based on past experience, I knew that I needed to get the data in the following format, before doing the pivot…

License # Ticket Row Amount
0001 1 2000
0001 2 500
0002 1 500
0003 1 200
0003 2 100

To get the data like this, we would use the following SQL:

SELECT	P.LicenseNumber,
		ROW_NUMBER() OVER(PARTITION BY T.PersonID
			ORDER BY T.TicketDate DESC)
			TicketRowNumber,
		T.TicketAmount
FROM	@Person P
	LEFT JOIN @Ticket T ON P.PersonID = T.PersonID
ORDER BY 1, 2

Simple enough, right? We use the ROW_NUMBER with PARTITION BY to segment we want to reset the count each time a new PersonID is encountered. This will get us our column headers for the Pivot table.

Now, we just add in the Pivot call, and we can get our data as we want it. All together, that SQL will look like:

SELECT	*
FROM	(
	SELECT	P.LicenseNumber,
			ROW_NUMBER() OVER(PARTITION BY T.PersonID
				ORDER BY T.TicketDate DESC)
				TicketRowNumber,
			T.TicketAmount
	FROM	@Person P
		LEFT JOIN @Ticket T ON P.PersonID = T.PersonID
	) IV
PIVOT
(
	MAX(TicketAmount)
	FOR TicketRowNumber IN ([1], [2], [3], [4], [5])
) AS PivotTable

Some things to note: This does not handle dynamic columns. There are other examples out there that can cover this, so I will wait until I have a need to create another post for it.

And here is all of the SQL that will also generate some test data:

DECLARE @Person TABLE (
	PersonID INT IDENTITY,
	LicenseNumber VARCHAR(10)
)
DECLARE @Ticket TABLE (
	TicketID INT IDENTITY,
	PersonID INT,
	TicketDate DATETIME,
	TicketAmount DECIMAL(19,2)
)

INSERT INTO @Person VALUES ('0001')
INSERT INTO @Person VALUES ('0002')
INSERT INTO @Person VALUES ('0003')
INSERT INTO @Person VALUES ('0004')
INSERT INTO @Person VALUES ('0005')

INSERT INTO @Ticket VALUES (1, '1/1/2011', 500)
INSERT INTO @Ticket VALUES (3, '1/4/2011', 300)
INSERT INTO @Ticket VALUES (2, '1/5/2011', 200)
INSERT INTO @Ticket VALUES (1, '1/10/2011', 600)
INSERT INTO @Ticket VALUES (1, '2/1/2011', 2000)
INSERT INTO @Ticket VALUES (5, '3/1/2011', 250)

SELECT	*
FROM	(
	SELECT	P.LicenseNumber,
			ROW_NUMBER() OVER(PARTITION BY T.PersonID
				ORDER BY T.TicketDate DESC)
				TicketRowNumber,
			T.TicketAmount
	FROM	@Person P
		LEFT JOIN @Ticket T ON P.PersonID = T.PersonID
		) IV
PIVOT
(
	MAX(TicketAmount)
	FOR TicketRowNumber IN ([1], [2], [3], [4], [5])
) AS PivotTable

Enjoy!

Posted in SQL