/bill's House O Insomnia : Data Access

Looking for some .NET Developers

William — Sat, 07 Jun 2008 02:19:26 GMT

A client of mine located in the Aiken/Columbia South Carolina Area has asked me if I had any friends who are .NET Developers and looking for a job. They are looking for at least one Junior Developer, on Mid-Level Developer and several Senior developers. I know these folks quite well and I can vouch for the following:

They pay extremely well
Generous benefits package
There's plenty of cool people on their staff
They are growing at an explosive rate so there's plenty of room for upward mobility. There's also a very strong focus on learning/teaching and career development.
They are an Agile/SCRUM shop
Currently developing with the following technologies:

Visual Studio Team System 2008 / .NET 3.5 Framework
Windows Communication Foundation
Windows Presentation Foundation
ASP.NET
LINQ
Silverlight
Windows Mobile 5/ Windows Mobile 6/ .NET Compact Framework
Sql Server 2005 / Sql Server 2008
Windows Installer for XML
Sandcastle

If you're in the area and looking for a job... if you know someone who looking for a job.... or if you're willing to move, please drop me a line using the Contact option on my blog or emailing me directly at WilliamRyan@gmail.com and I'll put you in contact with them. They are looking to hire folks immediately so I can probably get you an interview within a few days.

Junior Developer:

Ideal candidate will be a recent college graduate with a Computer Science of MIS degree. No experience is necessary for this position. The main soft-skill qualifications for this position are:

A true love for development
Strong desire to learn
A desire to stand out among one's peers

The hard-skill qualifications include a good theoretical understanding of at least two of the following:

Object Oriented Design and Analysis
Relational Database theory
Design Patterns
Familiarity with at least two of the following: C, C++, Java, C#, VB.NET

This is essentially a dream job for recent college grads b/c the job is being offered "no experience required". Since they are a SCRUM/Agile shop, you'll get to work extensively with their Senior and Mid level devs via Pair Programming. You'll be able to pick an area to specialize in which you will decide on for yourself. Additionally, you'll be using some of the most sought after Microsoft technologies available. Go to Monster.com or Dice.com and see for yourself how much demand there is for the above technologies and how much those jobs pay. You'll get to work with these on day 1 and essentially carve out a path for yourself using whichever technologies you like the best. If you want to be a "UI" expert, that's where you'll be put. If you want to be a Web Services dev, again, that's what you can focus on. If you want to be an expert buildmaster who specializes in installers, that's what you'll get to do. The company is looking for some excellent candidates and willing to offer a really fun and great paying job with plenty of training and career development available.

Mid-Level Developer:

The ideal candidate will have a computer science or MIS degree and 1-3 years of development experience. The ideal candidate will have two or more the following abilities:

Solid command of OOP/OOD using C++, C#, Java or VB.NET
Test Driven Design
Service Oriented Architecture
Familiarity with MySql, Sql Server (T-SQL), Oracle (PL/SQL)
Basic understanding of both Winforms and ASP.NET
Basic familiarity with Web Services, MSMQ, Enterprise Services/COM+, .NET Remoting, DCOM, J2EE, Java Beans

Just like the Junior development positions, these ones will provide a great deal of flexibility to learn and specialize the area of your choice. These are considered 'fast track' positions to move into the Senior Developer or Architect roles

Senior Developer:

The ideal candidate will be someone with at least 4 years experience. This person will love challenges and love coming up with solutions to problems that other people run from. They will be people that love figuring out how to do things that everyone else said can't be done. They will be the stereotypical Alpha-Geek, Someone that spends their spare time answering quiz questions like these:

All of the requirements for the Mid-Level Developer as well as several of the following skills:

Proficiency with T-Sql or PL/SQL. You should be able to write a 5 table join on your own. You should have written several stored procedures that were > 100 lines long.
Strong command of query tuning and ability to easily spot design flaws
Ability to design 'real' objects.

Someone who pays meticulous attention to the scope modifiers of their classes/functions/properties
Someone who understands the problems associated with coupling and strives to avoid it.
Someone who can write and use events in their classes without thinking twice about it.
Someone with a solid understanding of thread safety and thread synchronization
Someone who is comfortable using several different design patterns
Someone who is comfortable implementing several different interfaces in one object, using combinations of interface implementation and inheritance and someone who can use inheritance responsibly

Keeps up with technology, reads computer books regularly, has favorite blogs in multiple disciplines and/or blogs themselves, attends or speaks at user's groups, is active in one or more online forum or newsgroup
Someone who answers "Which is better, C# or VB.NET?" with "Either can be better depending on the circumstances but I'm comfortable writing in either of them" and then "But I've been learning F# lately and it's really been growing on me"
Experience with .NET Remoting, Web Services & Web Services Enhancements, MSMQ, Enterprise Services and/or WCF
Ability to create syntactically correct UML diagrams (meaning they can do more than write the letters "UML" on their resume) and if not, they are willing to learn to do this.
Understands data structures solidly and could easily implement their own HashTable or LinkedList
Someone who's always looking to come up with a newer, cooler, faster, and more secure way to do implement something
Experienced with either Biztalk server or Sharepoint
Takes pride in their code without falling in love with it or getting all defensive about it. Ability to acknowledge the fact that everyone makes mistakes and can talk about their mistakes/bugs without getting defensive.
Someone who is the first to raise their hand whenever the boss says "I need someone to learn ____________, do I have any volunteers"

Basically, if you love development, love creating new cutting edge solutions all the while making a lot of money and having a lot of fun you definitely should talk to these folks. If you're interested...... Please drop me a line using the Contact option on my blog or emailing me directly at WilliamRyan@gmail.com and I'll put you in contact with them.

The Microsoft® Windows Mobile Line of Business Solution Accelerator 2008!

William — Mon, 17 Mar 2008 13:04:00 GMT

Rob Tiffany has got the details... .

The main features include:

Intelligent resolution awareness
Synchronization Services
Windows Communication Foundations "Store and Forward"
MapPoint
LINQ
Custom Controls
Managed Stored Procedures and Triggers
Notifications and Online Help
Language Switching and Localization

Some of these (WCF, Synchronization Services) are a lot more thrilling than others but all in all it's a great step forward. With that said, I think MS really needs to work on a few things. When the iPhones first came out, I was lucky enough to have one sent to me. I liked it but didn't go gaga over it. After two days, I decided to sell it. I think UI wise, it was far ahead of a typical SmartPhone although the PocketPC edition phones gave it a lot better run for the money. But I know that my opinion is not typical. In fact, most iPhone users who have used or use Windows Mobile are really adamant about the superiority of the UI and usability. I think some of that is b/c they are comparing a 500.00 phone with a much cheaper one in many cases but across the board, the iPhone does probably have the advantage. Having started to toy with the iPhone SDK, I have to admit it's fairly easy to pick up (nowhere near as easy as the Compact Framework) so once more applications come out for it, it will make it all the more attractive to mainstream users. I have a lot of faith in Microsoft though and nothing would please me more than for Windows Mobile 7.0 to work so well that my obnoxious iPhone owning friends will have to pipe down about the greatness of the iPhone. The accelerator however, is definitely a step in the right direction.

Whitney Weaver is now blogging

William — Tue, 11 Mar 2008 21:38:00 GMT

My friend and co-worker,Whitney Weaver is now blogging at the Magenic blog site. Actually, he's been blogging for a little while now, I've just been a bum and not updated my blog in a while. Although he's certainly an up and coming programmer, Whit's main area of expertise is data, so if you're a data guy (or gal), you should check out his site

Coding Arguments - aka How to Start Religious Wars

William — Mon, 23 Jul 2007 04:33:00 GMT

I am lucky enough to work for a company that really encourages us to get together and learn/hang out etc. One way this happens is by Tech Nights. Basically, we have in office meetings which are recorded via Live Meeting, where someone picks a topic and teaches about it. Afterward, folks go to dinner and the company picks up the tab. Two weeks ago I had done the presentation and we all went out afterward. Two of our new guys were with us, and the rest of the folks there had all worked together at a particular client. Both of our new guys are really sharp, can talk their share of smack when necessary and been here long enough to feel comfortable speaking their minds if they want to.

The first discussion was regarding Frameworks. One of us mentioned that he liked Frameworks and believes in them. Being the smart a55 that I am, I asked "What in the h3ll does that mean?" . He proceeded to explain that he believed in code reuse and has build reusable code at every opportunity. Trying to have a little fun and encourage discussion, I asked him "Who doesn't believe in code reuse? But if that's the only goal, then a function gets the job done. Above functions, the whole reason for OOP is to facilitate reuse and simply building a bunch of objects that can be used sounds a lot more like an API than a Framework". The guy in question is a top notch coder and certainly understands the difference, he just had a little trouble using 'official definitions'. But asking what differentiates a Framework from an API turned out to be a pretty decent question to ask b/c neither have absolutely conclusive definitions that make them mutually exclusive. As such, it's precisely the type of argument that a bunch of nerds can argue into the ground over dinner.

I mentioned before that the 'rest of us' all worked at a certain client together. Personally, I'm pretty fond of standardization when it comes to coding. So even if I think my way is better, I'll use the predominant methodology in most cases simply b/c I think that in general, any improvements that doing it 'my' way brings will be more than offset by complications resulting from differing coding styles. If nothing else, it will usually mean that every time someone looks at it, they are going to ask "Why is it like this instead of ...". It ends up causing you to explain it over and over and have pedantic discussions with each new person that joins the team. Similarly, if you differ from what's being done, it's a virtual certainty that the implication of it will be that 'the other way is wrong'. Most of the folks I work/worked with are humble enough to acknolwedge that any given way they chose to implement something could have been better (any *real* coder will always acknolwedge it could have been done better. Anyone that says it couldn't is either egotistical or clueless). However, just because people are ok with admitting they didn't chose an optimal design methodology, that doesn't mean it's cool to rub it in their face. After all, you'll be on that side of the equation unless you never have to design stuff. Not that I recommend doing things wrong just to save people's egos, but some times, it's too easy to get so carried away with saying "this is right" or "this is wrong' that you forget to remember "It needs done". Another problem with doing things differently is that opinions are like Carrolls, everyone has one. What seems obvious as day to one person may seem utterly silly to another. Design decisions are all about tradeoffs and since we all see the value of tradeoffs differently, well you know...

Since a few of us worked at the same client, I decided to provoke a little bit. We had worked there at different times, except for me - I was there throughout the time each of the other guys was there. So one of them had really strong opinions about how well code should be formatted. So on the one side we had the following:

Upper case for Keywords and reserved words
Commas at the beginning of the line
Each column on a different line
Lining up words in the center
UPDATE Whatever SET Whatever WHERE WHATEVER
Immediately getting rid of any objects that aren't used or that were just used for testing
Using Source Control to track versioning differences.
Death to Hungarian Notation
Names should be self documenting - no acronyms, developer initials or anything that might lead to confusion about what the method should do.
Only including update information in the procedure's/parameter's documentation and leaving big changes to the source control system.

Since we didn't have a Source control system at the client (their choice not ours), part of this reasoning was problematic and I'm probably leaving a few things out,but overall, this characterizes Argument 1.

Argument 2 on the other hand was

Document everything in the procedure.
Have a dated comment showing who made the change every single time anything was changed
Leave backups and test objects in the database because they may be needed later
Minimal attention to formatting (although each person that advocated this was very careful about how their code performed. So it would be totally unfair to imply their work was sloppy - they just had more of a don't sweat the small stuff but make really sure the big stuff is right approach
Use initials for personal utility procedures so they can be found and used easily.

Personally, I am a nut about formatting. I'm really fastidious about formatting and would clearly rather have NO comments than out of date comments or too many comments - but that's not to say that I am anti-comments in any sense. I also am pretty fussy about source control, if you are going to have any configuration management, I can't stand it being done in comments inline or at the top of the code - it needs to be in a version control system. So on this one, the people that were on the project who preferred Argument 1 were in the minority. However at the table, we were in the Majority. This immediately led to a small trash talking session among friends. Little did we know the new guys were so interested in the topic. One of them seemed to be pretty opinionated but it wasn't until a week later that we realized how much so.

The thing is that while I consider myself a Data guy, my theoretical understanding of Relational Database theory is much stronger than say my DBA skills are. And although I'm admittedly a little fussy about formatting and the like, I have to admit that many of the best DBA's I've worked with are much more relaxed about formatting and commenting than I am. Many of them not being AppDev guys don't depend on Source Control and prefer to handle versioning issues inline. It's difficult to argue against competency so in the end, my utimate belief is to defer to the best guy on the project.

Have you encountered any discussions like this that you didn't really think would get people talking much but ended up getting people really involved? Personally, I think that as long as you're dealing with mature professionals, these sorts of things are great b/c folks are passionate about them, so you have smart people putting forth their best arguments, and you can't help but gain perspective and knowledge. On the other hand, if you deal with people who get defensive easy, or who have trouble admitting they don't know everything (funny how the more defensive they are , the less they actually know in most cases), well, this stuff will do nothing but cause bad blood.

So which side do you fall on? I'm guessing it's not this black and white in many cases, for instance, the comma issue could fall in either group.

What sorts of things have you come across that have stirred discussion? Any specific coding arguments, and if so, what?

ADO.NET Synchronization Services

William — Fri, 26 Jan 2007 15:10:00 GMT

So little time, so much cool new technology to play with .

My Update isn't working...

William — Thu, 25 Jan 2007 05:07:00 GMT

A while ago, I used to answer so many "My update isn't working. It doesn't throw exceptions but it doesn't do anything" type questions that I wrote this article . Those days seem long removed, or at least they did. Seems the problem is creeping back up with Tableadapters. In fact, one of the most common questions I'm seeing in some of the forums is "TableAdapter", "Access" "Not Updating" or some combination thereof. Now my 'real' answer is "Don't use TableAdapters and Don't use Access - and read my article. So other than the fact that TableAdapters are kind of lame, I wonder why they are causing so many problems.

Musings on the database formerly known as Sql Everywhere

William — Thu, 02 Nov 2006 01:53:00 GMT

The IM volume I received today about the Rollback to SqlCE issue was enough to border on overwhelming. Between PMs and some lazy SOB CRM guy trying to get me to do all his work for him, it was hard focusing on work today. (And seriously, if you are a CRM professional, telling me you don't know how to do a two table, one field inner join so i have to do it for you, makes you look like a Complete Moron. Try 'Bill, I'm really busy, can you do me a favor and write this join for me' next time, at least then my gag reflex won't kick in). But I digress.

Anyway, I've been getting a lot of email about my presentations on Everywhere. I guess I got people thinking about a few assertions I've made. In a nutshell, I made the following comments:

I love ADO.NET and in no way am I disparaging it, but in some scenarios, the disconnected model just doesn't fit. This has nothing to do with any deficiency in theory or implementation of ADO.NET. A Ferrari is a great vehicle but if you need to tow a heavy boat, it's not the right vehicle.
The one case where I don't think the ADO.NET architectue is well suited to handle is single user scenarios where you are *positive* that you'll only have one user connecting to your database.
Devices like Smartphones and PDAs proabably aren't going to be targeting multiple users simultaneously and in those cases, where you are resource constrained and pretty much assured of a single user, the database->dataset model is overly consumptive.

In case you don't understand why, let me explain. What does using a disconnected model bring to the table? Essentially it brings tremendous scability and at least as far as the server is concerned, the possiblity of improved performance. It is a Just In Time data access strategy that can greatly minimize locking and bottlenecks. Like anything, it needs to be used wisely but the fact you don't have to keep a persisent connection to the db is huge. This benefit isn't free. In exchange for it you have to pay a price in RAM b/c you're using cached copy of the data. In most cases, this tradeoff is well worth it. In some though, it makes very little sense. Think of a Smartphone. I have one user. Only one person needs to be hitting the db at once. I have the data persisted to disk and I have limited RAM. What benefit does sticking a dataset in between my UI and the backend db bring? None.

That's where the beauty of using SqlCE/SqlMobile/SqlEverywhere/SqlCE comes in. You can use the SqlCeResultset which is scrollable and updateable, and you can cut out the layer in the middle. Your logic is trimmed down b/c updates/inserts/deletes require no work on your part provided the table you're hitting is keyed.

But that has a problem. What if the data needs synchronized with a db server? Well, you can roll your own logic. Or you can use a familiar, well documented, well tested and peer reviewed mechanism. Unless you're getting paid by the hour and you have no compunction about robbing from your clients, use the latter.

So in short, on a SmartPhone or PDA, SqlCe/SqlMobile/SqlEverywhere/SqlCe is a seemlingly perfect solution for data access. Now that you can run it on a desktop, the same holds for single user scenarios there. And using a SqlCeResultset is *not* a replacement for ADO.NET. It's still ADO.NET in every sense of the word, it's just a evolution of one aspect of it. And a darned good on for single user scenarios.

A mobile database by any other name...

William — Wed, 01 Nov 2006 01:01:00 GMT

I'm really torn about this post. On the one hand Microsoft has repeated one of their most annoying feats. However they did it with a product that I love, so I feel a little bad dogging it out. What am I talking about? They renamed that little database that can run on your phone again! So first it was SqlCe, then it was Sql Mobile, then it was Sql Everywhere, now it's SqlCe with an emphasis on the fact that CE stands for Compact Edition. I'll stop now before I stick my foot in my mouth ;-)

Differentiating between "Denormalization" and excuse making.

William — Wed, 27 Sep 2006 05:47:00 GMT

I've actually got a lot to discuss about database stuff but wanted to start out explaining a very basic concept. All me to disgress for a moment. I've worked at a few places where there were some Cobol/AS 400 developers that were quite full of themselves. They knew how to handle business with COBOL/AS400 and it went straight to their heads. Along came round wheels, indoor plumbing, relational databases and object oreinted programming and they dismissed it as a fad. Then as reality repeatedly *** slapped them, they just turned into eternal pity parties lamenting how all the good jobs are moving to India and how relational databases suck. And as is often the case with people that think Relational Databases suck, they were heavy on opinion but really light on reasons to back it up. There was a perfect correlation between the degree to which they thought RDBMS's sucked and their lack of knowledge about them. How you can know something is lame without knowing anything about it [with repsect to technology] is beyond me. Sure, I can know that drinking out of a Port-O-Potty sucks without doing it, but I at least have to know what's in a Port-O-Potty to understand why drinking from it would suck.

So their criticisms came down to two things for the most part. First they claimed that joins had too much overhead. Next they claimed that Normalization was unnecessary and that in many cases, it was impossible to do. Back when I had my Apple 2e, perhaps joins would be too expensive. But in and of itself, it's a lame argument. [I won't even touch the fact that many of their Killer AS400 apps used to take 20 minutes to run even the simplest of queries]. And they would build in redundancies everywhere. They must have all surfed the internet long and hard for "Lame excuses to use when you get busted creating a really crappy db structure" and somewhere stumbled across the word http://en.wikipedia.org/wiki/Denormalization because that was their excuse for everything. One manager who fancied himself an architect was addicted to redundancies. He actually lamented the fact that the tool we were using only allowed 250 columns and we had *many* tables that used every last one. When the chickens came home to roost he decided he needed a good excuse. The president of the company decided to circumvent him and went to each of the developers asking why we were having all the problems we were experiencing. And everyone that wasn't a COBOL programmer who refused to change said that same thing - redundancies. He was in a bind. Then he called a meeting, with the president, and had a bunch of printouts about Denormalization and how that's the approach he used.

So people that don't know what they are talking about often use Denormalization and redundancy synonomously. The metaphor that comes to mind is this... If you fill your bathtub up with hot water, then find it's too hot and proceed to turn on the cold water until it becomes tolerable, that's not the same as never turning on the hot water in the first place.

This silly excuse usually shows itself with 'Historical' data, they'll claim they built in the redundancies to handle historical data. If you had a customers table with a SalesPersonID which relates to a name, they'll point out that if you change the name of the sales person, you'll affect all the records. This issue can usually be worked out with effective dates but that's another story. So I'll concede, the historical data issue is a valid place to have redundancies. However, and this is a big howerver!!!!!, it's only valid if the historical data is not to be changed in the future. If it's data that you might have to change or update, then the redundancies are just that, redundancies. And you can still use historical data and use relational modelling. If you have the SalesPersonID and Name in the Historical table, and you have cause to update it because of business requirements, it's not 'denormalization' and it's a bad way to do things.

You may think this is so obvious that it goes without saying. However I was in a situation not too long ago where a database was created that had redundancies All over the place. If you needed to change a Salesperson's name (for instance, someone gets married), you had to go into at least 12 different tables to change it. And if you missed one record, it was enough to seriously screw up the logic reporting was built on. Another wonderful feature was computed fields stored as hard coded values. Every week a new series of transactions would be imported. A few processes were run and the aggregate total of sales, fees etc were stored in multiple tables. Then the details that were used to create them were stored in another series of tables. If you changed data in one place and didn't change it everywhere else, reporting would be totally hosed. To say it was a nightmare to maintain would be putting it mildly. Yet the person that built this system claimed that it was done intentionally as a means to double check the values. So let me ask you this. If you have a table with CustomerID, Sales, Week, and you run a process to stick the aggregate of sales in one table, how is that any different than just using SUM? Rather, how can you claim it's 'better' or intentional? People like this often rationalize it with "Well if everyone did their job and just made sure they updated all the tables, there wouldn't be a problem.' True enough. But people make mistakes. If you can't force all the other changes to occur each time source data changes through triggers or integrity constraints, you can't guarantee changes will be made. Plain and simple.

When you see a nightmare database or program, it's often attributed to the ignorance of the person that created it. But pure ignorance will typically only produce bad stuff, not truly terrible stuff. To be truly terrible, it takes someone who's convinced their way is best, while being totally ignorant of the consequences. You can only make something so bad accidentally. No, the really horrendous things out there are always the result of deliberate action by someone or some group of people. And this begs the bigger question - why, in 2006, with all the books out there, internet sites etc, so people still fight normalization when using Relational Database Systems? And why or why do people that have no clue what they are doing and little to no desire to learn get to design critical systems?

I'm betting this is more common than it may seem. Anyone else still run into stuff like this?

Comment of the week - Sql Server Reporting Services vs. Crystal

William — Wed, 27 Sep 2006 05:27:00 GMT

A while ago, I posted this about how much a55 I think Crystal Reports sucks. A long time ago, back at like v.7, I thought Crystal was a decent product. And to be honest, it still is a decent product. But it comes with a lot of baggage and has a lot that just makes it lame. So I had almost total consenus on the Crystal Reports w/ .NET sucks side, I think there are two people that disagree. Ignoring the fact that SSRS is very new product and Crystal has been around forever, I think side by side it's a slam dunk. But one big mouth said this: (NOTE: I'm only posting a comment that was publicly posted on my site. So hopefully the guy won't be a cry baby about it and throw a temper tantrum over it)

"Either you guys are idiots or you have never used Crystal and don't realize the minimal functionality that SQL Reporting Services offers. I have spent the last week evaluating SSRS and am greatly dissapointed. I came up with a list of over 250 items that SSRS could not do. As for all you Access reporters, your probably the only people to ever purchase Microsoft's "Bob" product. "

So the guy claims to know enough about Crystal to have cataloged over 250 features that SSRS doesn't have. That means that including the features he'll admit it does have, he knows way more than 250 features. I think that would qualify you as a fairly advanced Crystal user if you'll pardon the oxymoron. And he spent one week with SSRS. And the only conclusion he can draw is that SSRS is definitely lacking those features and that it couldn't possibly be that he just doesn't know how to use them, right? And far be it from me to say anything positive about Access, but come on, being a hard core Crystal user still means you're the report ***, and that gives you credentials to snob no one. Certainly not Access developers and certainly not Bob users. Is he really claiming Bob was lame but Crystal Reports isn't? Pulllleeezz. But if you read the context of the comments, the people were pointing out that Access, as lame as it is, provides more effective funcionality. The fact this can even be debated speaks volumes don't you think? Is Access supposed to be first and foremost a report writer? Last time I looked it's main purpose was as a relational database (more precisely, a database that anyone can build with, that causes major headaches for the people that will inevitably be brought in to clean up the mess it made) not a report writer. Reports are one of its features but one would certainly expect that a mature product who's sole purpose is report writing ought to be a little better than a RDBMS product with reports added in.

So I offered a challenge, I asked him to post just 20 of his 250 features that CR can do that SSRS can't. I'll see if they really can't be done and post the results. If I'm wrong, I'll admit it. If he's wrong I'll point out what an a33hat he is. I actually believe there are probably 20 or so features missing, but certainly not major ones. Even assuming there were really 250 features missing, how many times do you need 250 features for a report? Typically you need Grouping, Subreporting, charts/graphs, conditional formatting, functions and the like. He didn't comment as to his evaluation of how each stacked up there - for reasons that are obvious. Anyway, I believe his 250+ number about as much as I believe in the easter bunny so although *someone* could probably post 20 things lacking, I doubt he can. One of us is very wrong here and hopefully it's not me. We'll have to wait and see.

Normalization really does matter

William — Thu, 07 Sep 2006 14:30:00 GMT

It seems like years ago, but I remember one of my professors giving us an assignment. It involved taking a database chock full of redundancies and writing the SQL Statements to make a few small updates. We thought it was an assignment to test our SQL Skills. However we were graded on how close to 'right' we got all of the changes. It wasn't about SQL at all, he could care less about SQL at this point. It was about illustrating, with much painful monotony, why "Normalization matters."

After we turned the assignment in, he asked everyone how long it took. Suffice to say it took everyone a long time. But the interesting thing was that not one single person got all of the changes correct. Many got close, but no one got all of them.

Then he explained some more why normalization matters. You see, not only did working with non-normalized data (as opposed to denormalized) take forever to do, it was very error prone. No matter how careful you are, if you do the same thing over and over, you're likely to miss something.

Afterward, he proceeded to give us a story from the 'real world'. He told us how some things in CS have great theoretical value but not much practical value. But he said that in general, good theory makes for good practice and no where is this more true than in DB Design. We were told that when we get out into the real world, we'll hear all sorts of 'Senior' folks trashing normalization as ivory tower stuff that works great in college but doesn't have value in the real world. He said that *when* we hear this, to immediately ask the person "At which level doesn't normalization make sense in the 'real world'? All of them or just certain forms" This was because, in his opinion, the only people that will trash normalization are ones who don't understand it. Granted, there were many caveats in the lectures. Sometimes it's best to denormalize (which, is something you do AFTER you've normalized - for the uninitiated). Many will design garbage and when the redundancies are pointed out will say "I totally do believe in normalization but I denormalized the design". If it wasn't normalized in the first place it couldn't have been denormalized, such statements are mere excuse making. He also pointed out that sometimes when time is of the essense and maintainability isn't a real issue, then normalization may not justify the costs. There were definitely exceptions to his rule, but one thing that always stuck with me was that folks that trash normalization almost always have never studied it very far.

So why am I babbling about this? It's simple. Some things can be totally screwed up and then fixed quickly/easily/inexpensively. I could get the color scheme of a CSS layout totally upside down and fix it pretty quickly in many cases. Other things are a little more difficult but still doable. Let's say I didn't use inheritance and coded some total redundant classes. Making the code modification won't be trivial but if my code is modular, it should be fairly straightforward considering the development tools in place these days. Other things though are deal breakers. Absolute and utter deal breakers. Database design is the epitome of a deal breaker. To those new to development, you may be asking why DB Design is so vastly different from other design issues.

Just imagine the following scenario. You have a Dog and a Cat class and they are in a running production app that's working great. You decide that you should have had a Mammal base class and decided to implement it. You guess it'll take you about 16 hours to find every instance and make the appropriate changes. So the app runs along, users are happy, you take your 16 hours, make your changes , test them (or test them, make them for many) and redeploy. No problems. Users are none the wiser and everyone's happy.

Now what if this was a production database? Well, if you have downtime on the db then you can create a replica of the data, make your changes, verify they worked correctly and then implement them at some time when no one is using the system. I've only worked in two places where such was the case. If the design flaws go out the door on a live system, you're stuck.

In the Dog Cat example, if you totally screwed something up, you can redeploy the previous version and in many cases it won't be too big of a deal (in some it obviously will but that's related to installation more than anything else). But what if you damaged a lot of data, the impact very well may (and probably will) be immediate. Moreoever, you are probably dealing with a moving target b/c the data is constantly changing. Even if you think you coded everythign correctly, new data may be introduced that you weren't counting on. And even then, even assuming you got everything right, many changes of this sort are going to cause service interruptions. Depending on what the db is used for, this could be a huge deal.

We all know that the further downstream it is that a bug is caught the more it costs to correct. We've all seen the upward trending curve. The thing is that in most case, with DB changes in a production system, that curve is very short and very steep. And if you do any of the following, you should be flogged:

Use Reserved words as object names, column names etc (they are called Reserved words for a reason)
Use inconsistent naming conventions
Use unintelligible naming conventions
Ignore data types. NVARCHAR isn't the solution to everything. Yes, I know you can cast dates held in VARCHAR fields to DATETIME Values but that's irrelevant
Fail to use keys. At a minimum you're going to have a lot of trouble getting tools to update data but that's the least of your worries
Use multiple fields to represent similar data and then using inconsistent rules for maintaining the data that they are populated with
Fail to enable integrity constraints. I once heard a developer, with a straight face, say he didn't believe in integrity contsraints b/c he was a 'real developer' and preferred to write his own. He contended that he wanted to make sure things were done right as opposed to being lazy and leaving it to Oracle to handle. You have to have the initials CC or have forgotten to take your lithium if you think you're going to do a better job writing your own integrity constraints than Oracle/Microsoft etc are. And for those of you that are going to get all pedantic and tell me of some obscure bug or situation where MS's or Oracle's constraints fail, I'd counter with this. Use their AND use your own. You can't guarantee that they only way the data will be accessed is through your killer app and it's uber integrity algorithms. So why leave it bare? If you're constraints are really that good, then it shouldn't ever be an issue anyway right?
Freestyling! Freestyling is cool as long as it's done in a Rap/BMX/Skateboard etc context. It has no place in the development world other than in R & D scenarios. RDBMS systems are well tested and rooted in very strong theoretical foundations. They have stood the test of time and they work. They aren't perfect but they are damn good and they work well when used correctly. Unless you really are a super genius, chances are that you're not going to have some superior way of doing things that the rest of the world has missed. And even if you did find one, the chances of you finding another one are slim to none. And why do these folks never seem to freestyle with other things like addition, subtraction, multiplication, division, gravity, intertia etc? What do you think would happen if you owed the IRS 10k and used your own math to compute that they actually owed you 10k? Why would it be any different with anything else? So if you're some Maverick has a ton of 'unique' ways of building your dbs - all you're doing is shoving the burden of your sloppiness onto someone else.
Failing to at a minimum follow the first two normal forms.

It's a matter of "you can get with the program or you can pay a ton of money to have someone else fix it, or spend a ton of time down the road fixing it yourself." And if this happens, you can't cry foul - you're the one that decided to break the rules.

Transferring data around with ADO.NET 2.0 - SqlBulkCopy

William — Sat, 26 Aug 2006 16:03:00 GMT

I'm working at a client on a data migration and found some results that were surprising. I expected there to be a difference but not one quite this large... Synopsis of Problem: Right now I'm working on a rather complex migration from a bunch of disparate...(read more)

To use Stored Procedures or not.

William — Wed, 14 Jun 2006 20:14:00 GMT

A while ago, Frans wrote Stored Procedures are Bad, M'Kay . None other than Adam Machanic chimed in, arguing essentially the opposite . The always insightful Anatoly Lubarsky gives his take on it and well, my head is spinning. I was Mr. Stored Procedure...(read more)

Where's the Extra Connection String Coming From?

William — Wed, 14 Jun 2006 00:33:00 GMT

One of the issues I came across when writing my new book was with ConnectionStrings. Examine the following configuration section: <connectionStrings> <add name="AdventureWorksString" providerName="System.Data.SqlClient" connectionString="Data...(read more)

Pablo Castro on ADO.NET 3.0

William — Mon, 12 Jun 2006 22:39:00 GMT

I'm bummed that I couldn't make it to Tech Ed, Sahil is presenting and everyone I know is up there. Anyway, i've been reading Kent Tegels liveblog of Pablo Castro's ADO.NET 3.0 presentation report on ADO.NET 3.0 and am green with envy. Pablo is one of...(read more)

Bill Vaughn's Blogging again

William — Mon, 12 Jun 2006 21:17:00 GMT

Well, I'm out of commission today but a bit restless. Getting caught up on a few things. Anyway, Bill Vaughn is blogging again ago but I've forgotten to post it. Anyway, if you aren't familiar with him, Bill is the man and there is sure to be a lot of...(read more)

Learn ADO.NET from the Best

William — Tue, 06 Jun 2006 21:29:00 GMT

Well, saying someone is 'the best' is a pretty big statement, but one thing is certain, any discussion of 'the greatest talent in ADO.NET' will have Sahil's name featured prominently. Just look at what his people who read his book say! Anyway, if you...(read more)

DataView.ToTable method in the 2.0 Framework

William — Mon, 05 Jun 2006 23:37:00 GMT

This is a really simple trick but it's way cool. Now that I have some time again, I've been trying to get up to speed with LINQ and ADO.NET 2.0. God I missed it. Anyway, this comes up in the newsgroups all the time so I figured I'd write up a quick sample...(read more)

Sahil's moving again...

William — Wed, 24 May 2006 16:02:00 GMT

Totally a day late and a dollar short on this (I think Sahil mailed me last week) but anyway, better late than never

New website - www.winsmarts.com <-- This is the suit/tie version of me.
New blog - http://blah.winsmarts.com
New RSS - http://blah.winsmarts.com/rss2.aspx

Using the SqlConnectionStringBuilder to guard against Connection String Injection Attacks

William — Sun, 15 Jan 2006 14:40:00 GMT

One new object in the ADO.NET 2.0 Library worth taking a look at is the SqlConnectionStringBuilder .

Considering how late it is in the game, you are probably aware of the dangers of Sql Injection Attacks . One the off chance you aren't, let me throw in my two cents and recommend that you familiarize yourself with them ASAP. Anyway, injection attacks are possible when you use dynamic string concatenation to build your Sql Statements and you don't paramaterize them or escape all possible malicious input. This presents a small problem b/c you need to employ user input in your applications in many cases, but every time you accept user input, you increase the surface are of an attack. And regrettably, there are too many cases where people 'solve' this problem by employing a solution worse than the problem itself. For instance, I've seen some people hell bent of using Dynamic Sql (and not parameters) solve the injection problem by disallowing all special characters. While this does address the problem directly, it has the unintended consequence of forcing the users to use less strong passwords. And most of the cases that I've seen where people implemented this rule, they increased the lenght of possible passwords to offset this vulnerability. Well, that 'solution' opens you up to other sorts of mischief. After all, if you only allow people ten characters for their password, there's only so much malicious sql they can push off on you. But assuming you don't increase the length of the password, you just pretty much hamstrung the user into have to adopt a weaker password scheme. I've bitched about this alot in the past and it's not really the focus of this article so I'll cut it short here.

So what about Connection String injection attacks? Well, yes, it's something you should guard against. When I was first learning .NET, I created this application that stored User Name/Password combinations in a Database, both of which were encrypted. This was a particularly sensitive application and I was still pretty naive about security. So I came up with a solution that entailed a good bit of extra maintenance but which I believed would give me extra security. What I did was create a Sql Server login for every user. So to even create the connection in the first place, I used their username/password combo to open the connection and then I ran an query that would check the authentication table, decrypt the username and password and pass back an output parameter indicating if they authenticated correctly or not.

This was not just a bad idea, it was terrible. For one thing, it was a nightmare to administer. Every time we added a user, I'd have to create a new Sql Server account and had to use a utility I wrote to hash the password. Other than being a pain in the neck, this made it impossible to add a new user for use with many of our applications without having to use my utlity (which people were unable to find on multiple occassions). Another problem was with connection pooling. If you have different connection strings for every user, it can have a serious adverse affect with Connection Pooling . Anyway, I thought all of this was worth it all in all b/c of the extra security benefit. Then Angel Saenz-Badillos saw a post I had that described what I was doing and he set me straight. You see, my whole concept was based on the fact that I didn't realize you could hack the connection string (don't ask me why, it should have been clear to me from the get go). But all it takes for someone to have caused some mischief for me was to add Max Pool Size 100000000; Min Pool Size 100000000 to the password field. Sure, we are used to limiting the field size in many cases for fields that we accept input from but it's not unheard of for someone to leave it the default size. Another trick that they could do is add "; Trusted_Connection = true". This will cause a 'last one wins' scenario so in the case of my app, the machine account would be used instead of the assigned user account and if it had more privileges than the user account did, well, there was just an escalation of privilege. Combined with a Sql Injection attack, this could cause some problems.

The good news is that in order to do much damage, the attacker would need to know the username and password or the machine itself would have to have sufficient permissions with the Sql Server to allow something troublesome to happen. But relying primarily on 'luck' to not get hacked is kind of a lame strategy. The other thing about Connection String Injection attacks is that if everything else is handled correctly (no unparamaterized queries allowed) then the attack surface is pretty much limited to non-permanent items or things that are just annoyances (although depending on the nature of your data, these annoyances could get quite serious very quickly).

So how do you address this issue? Fortunately the good folks on the DataWorks Team gave use the SqlConnectionStringBuilder. Basically, it handles the escaping issues for you and has the side benefit of being easier to use. For one thing you can use it just like you would you old connection strings if you're dead set on not changing your ways:

SqlConnectionStringBuilder OldSchool = new SqlConnectionStringBuilder("Data Source=Clock1;Initial Catalog=Pr0nStarz;User Id=CuckooBurd;Password=++dfjk33nmjKLLa;"); SqlConnection cn = new SqlConnection(OldSchool.ToString()); Now you can use the SqlConnectionStringBuilder just like this, it's as easy as pie:

private void DemoConnectionStringBuilder() { SqlConnectionStringBuilder ConnectionBuilder = new SqlConnectionStringBuilder(); ConnectionBuilder.DataSource = SecurityUtil.DecryptText(@"#*a0-%%% ^889333", SecurityUtil.CryptoProviders.TripleDES); ConnectionBuilder.InitialCatalog = SecurityUtil.DecryptText("pR9s4%~`"); //Here you can specify either ConnectionBuilder.IntegratedSecurity = true; //ConnectionBuilder.UserID = SecurityUtil.DecryptText("pR9s4%~`") ; //ConnectionBuilder.Password = SecurityUtil.DecryptText("!Cuck00zR~!e", SecurityUtil.CryptoProviders.TripleDES); using(SqlConnection MainConnection = new SqlConnection(ConnectionBuilder.ToString())){ MainConnection.Open(); //Do your thing MainConnection.Close(); } }
In the above example, I went with Integrated Security and commented out the username and password, but you can switch these back and forth. All of the properties like Max Pool Size, Pooling, Workstation ID, PacketSize, everything you normally manipulate is there and since you have intellisense support, you have cues as to just about everything you may need. Moreoever, if you want to be really safe, you can also create another wrapper class that exposes some of there properties but limits Max Pool Size or Min Pool Size in the accessors. For instance it could check one vs the other and if you aren't comfortable with the numbers, you can throw an exception, change them to something you like, or do whatever you see fit. Same goes for the UserName/Password and Trusted Connection. Although it will escape any attempt to try to put Trusted_Connection = true as a value, you can use your class to manually ensure that only one or the other is chosen. Right out of the Box the SqlConnectionStringBuilder is very powerful and can give you most of the functionality you'd ever want but there may be a case or two where you need everything to be even more granular - in which case you can easily implement such a scenario. Sure, as far as features go, this is more evolutionary than revolutionary, but it's a nice touch. If nothing else it will keep you from having to flip over to http://www.connectionstrings.com over and over when you are building new applications

-----

As an aside, I've listed the two primary targets for a Connection String injection attack but I'm not convniced that there aren't any other ones. If you can think of any other mischief you can cause via the connection string, be a pal and shoot me your idea - I'll include it as an addendum to this post and give you the credit.