Two main ways to interact with the database: using a stored procedure vs. using a direct SQL statement. Here's a brainstorm of the pros and cons to each:
| Technique (called from managed code): | Pro | Con |
| Stored Procedure |
|
|
| Sql Statement |
|
|
In general (of what I've seen), many enterprise apps will use Stored Procs, while sometimes a RAD will use raw SQL statements.
What has your experience been?
The other day I had a to investigate some data corruption. A certain table started out with one set of data, I ran an upgrade process, and QA told me it wasn't upgrading correctly. The problem was that I needed to compare two snapshots of SQL data, from the same table. I know there are many ways to solve this problem. The way I solved it was with the open-source MassDataHandler.
One of the main purposes of this free tool is to convert data back and forth between an Xml format and stored SQL data in a database. So, with the "Use Existing Data" feature, I took a snapshot of the table (using a select clause to specify the columns and order) and saved it as an Xml file. I then ran some black-box upgrade process, and took a second snapshot of the table. Because I now had two xml files, I could easily just diff them (I prefer using Beyond Compare for this).
The idea was to reduce the problem from an unknown state (how to compare Sql data) to a known one (how to compare two Xml files).
This is a free, open-source tool to assist with database unit testing. The new version offers several new features:
You can download it here.
At every company I've been, I see source code with old code commented out instead of deleted. In a sense, this is almost using commenting like a primitive form of version control. I see pros & cons to this approach:
Pro:
Con:
I think ideally old and obsolete code should be removed instead of commented out (makes for leaner, easier-to-read code), but I can see the other approach in certain development cultures.
And of course, the first three options can be case insensitive and trimmed white space (i.e. "tRUe" becomes "TRUE").
I had talked about using Convert.ToString to convert different objects to string, so I'd initially look at it's related method: Convert.ToBoolean. But one quickly sees that that won't handle all the cases (and with good reason). The only literal string it takes from this group is "true"/"false". For example, it would handle converting the integer 1, but not the literal string "1". Having a single function that just converts these different inputs to a boolean is a nice convenience. Here's a sample:
public static bool ConvertToBoolean(string strVal)
{
if (string.IsNullOrEmpty(strVal))
return false;
strVal = strVal.ToUpper().Trim();
if (strVal == "TRUE" || strVal == "T" || strVal == "1"
|| strVal == "YES" || strVal == "Y")
return true;
else if (strVal == "FALSE" || strVal == "F" || strVal == "0"
|| strVal == "NO" || strVal == "N")
return false;
else
throw new ArgumentException("Cannot convert '"
+ strVal + "' to Boolean.");
}
Some projects have the local superstar. Any whenever anyone has a question, rather than figure it out for themselves (or even try to), they just ask their local star. There are two problems to this approach: (1) It eventually burdens that "star", and (2) It weakens the average guy's ability to find out info for himself.
Sometimes you need the answer right away, so you'll just ask someone. (And it would take too long to figure out yourself). However, most of the time it's worth first taking a stab at it yourself. Here are some ideas:
While having other star coworkers is a nice safety net, it is a luxury that may not always be there. And during those times, it will be nice to have had the practice at figuring out answers for oneself. Besides, the more someone practices at solving problems, the more likely it is that they'll become that goto guy that everyone else asks questions too.
Everyone knows how to use SQL Management Studio, but there are two other useful tools that SQL Server 2005 has.
