Checking for Database NULL Values in C#

by fwhagen Tue, 09 October 2007

This really should have been more obvious to me, so why did I have to look it up?  If you need to check for NULL in a returned field from SQL (or any other datasource), you should the .Equals method on System.DBNull.Value.  I usually prefer the "==" notation for conditionals, but that's just me.  This is more efficient.  The code follows:

if (!dsSpecQuery.Tables[0].Rows[0]["device_type_end"].Equals(System.DBNull.Value)) 
    _EndDate = Convert.ToDateTime(dsSpecQuery.Tables[0].Rows[0]["device_type_end"]);

Of course, dsSpecQuery is a DataSet, and the field in question is DateTime out of SQLServer (not that it matters for the conditional).

Keywords: , ,

Filed Under: .NET | Programming | SQL

Book Review: Lord's Foul Bane - Stephen R Donaldson (1977)

by fwhagen Thu, 04 October 2007

Lord Foul's Bane is a fantasy classic that I have read before; twice, I believe. But it was so long ago, that I have been meaning to re-read it for awhile.  The books I have are so old, Amazon doesn't even have the images to link in.  Hopefully Wikipedia doesn't mind.

Lord Foul's Bane is the beginning of the story of Thomas Covenant called Unbeliever.  He is a modern day man afflicted by Leprosy.  He finds himself unwillingly thrust into The Land, a mystical realm that is able to resolve some of his nerve damage and remove his disease.  But he is wary, knowing it must be too good to be true.  He must deliver a message of Doom to the leaders of the Land, and resolve his inner turmoil of even being there.

Covenant is a reluctant hero with fears and flaws that match our own.  Donaldson's treatment of him is very deep and extremely satisfying when looking for a character that is more realistically complex.  He spends much of this book battling the psyche of the main character himself.  There are others, I am certain, that have written this series up far better than I.  For more, find such a review online.  This is the first book of the first trilogy of the Chronicles of the Unbeliever.  The 2 trilogies were so popular that Donaldson has started the 3rd and final a couple years ago.  The Chronicles of Thomas Covenant are, without a doubt, required reading for any fantasy fan.


Filed Under: Books

Database Access from VBScript (WSH)

by fwhagen Fri, 21 September 2007

Sometimes its necessary to script out data manipulation for deployments or automation.  The easiest vehicle in Windows is Windows Script Host (WSH) using VBScript.  Below is a very basic framework for accessing a database (MSSql) and looping a RecordSet.

''===// Retrieve Data from Database //=====
Const DATABASE = "Cellular"
Const DBUSERID = "app_user"
Const DBPASSWD = "app_pass"

'' Construct ConnectionString
szADOConn = "Provider=SQLOLEDB;Network Library=DBMSSOCN;Persist Security Info=True;" &_ 
            "Data Source=" & DBSERVER & ";" &_
            "DATABASE=" & DATABASE & ";" &_
            "User ID=" & DBUSERID & ";Password=" & DBPASSWD & ";" 

Set DBConn = CreateObject("ADODB.Connection") szADOConn        
    szSqlQuery = "SELECT PhoneID, Field2, etc  FROM Import_Name WHERE PhoneID <= 200"
Set cmd = CreateObject("ADOdb.Command")
    cmd.CommandType = &H0001  ''adCmdText
    cmd.ActiveConnection = DBConn
    cmd.CommandText = szSqlQuery
Set RecordSet = CreateObject("ADODB.Recordset")
Set RecordSet = cmd.Execute(szSQLlc)

Do While Not RecordSet.EOF
  WScript.Echo RecordSet("PhoneID")  

Set RecordSet = Nothing
Set cmd = Nothing
Set DBConn = Nothing

Of Course, it's possible to distill this down further, but I didn't for clarity (Consts) and it's better to not use implicit command objects by opening the database directly with the Recordset object.


Filed Under: SQL

The Emperor's Lottery

by fwhagen Thu, 30 August 2007

When I am Emperor, I will have a quiet policy to fire any IT worker who starts an office lottery pool.  Not only are they wasting the time of people that are trying to get good work done, but they obviously do not have the mathematical and statistical skills to properly perform a programming or development task correctly.

What they do on their own time is, of course, their own business.  I love those who contribute to Ignorance Taxes.


Filed Under: Rant

Proper ASP.NET Database Calls

by fwhagen Mon, 20 August 2007

Last week, I posted an entry about a simple call to an MS-SQL database with a warning that it should only ever be used in a quick and dirty scenario.  By that I mean when it doesn't make sense to integrate or create a Data Access Layer or class to do the heavy lifting for you.

The advantages of creating classes to do the data calls behind the scenes are many, not the least of which is easier development in the long run.  Take a look at the following code:

    Query qEmplQuery = new Query(); 
    qEmplQuery.ConnectionSettings = ConfigurationManager.ConnectionStrings["EmployeeDB"]; 
    qEmplQuery.StoredProcedure = "DW.PKG_HRPORTAL.PRC_EmployeeInfo"; 
    qEmplQuery.AddParameter("P_EmployeeNumber", szEmplNum); 
    qEmplQuery.MinutesCached = 12 * 60; 
    DataSet dsEmplQuery = qEmplQuery.RetrieveDataSet("P_EmployeeInfo");

Much easier than even the "simple" call I posted earlier, isn't it.  The beauty of this is that it is also an Oracle call (database agnostic:  the same call for Oracle or MS-SQL makes for easier and standardized development) and is cached for 12 hours.  We also have a coding standard that only parameterized Stored Procedures are used for any kind of data access. 

But the most important advantage is that the Query class contains all of the complex coding needed to make this as efficient and "correct" as you know how and never have to think about it again.  This is basic abstraction and Object Oriented Programming fundamentals.  I have used Enterprise Library factories for my calls in Query because of the desire for its good caching and efficient data and network layer calls, but you can use whatever you want. 

In my next series of posts, I will present details of the Query class in order to help build your own.

Keywords: , ,

Filed Under: .NET | DataBase | Programming

My Focus Must Be Off

by fwhagen Tue, 14 August 2007

I was exploring the statistics on this site the other day and made an interesting discovery.  My rant on The Fall of the Soviet Union, which was done out of annoyance of modern softening of history, is by far the most popular article I have written.  It has received more traffic than all of my other articles.  Which makes me think:  maybe coding is not of interest, but history and military technology.

Then I remember, I post what I know and to help me remember it.  I am not a historian, nor do I play one on TV.


Filed Under: Blog

Book Review: Up Front - Bill Mauldin (1945)

by fwhagen Thu, 09 August 2007

Bill Mauldin is probably the most famous cartoonist from World War II.  He was an infantry soldier in the Italian campaign who also worked for Stars and Stripes, the soldier-run newspaper.  If you were to see one of his strips, you would instantly recognize his work.

Up Front is a collection of some of Bill Mauldin's panels from the war, starring Willie and Joe, along with his own commentary on the war, his work and his own experiences.  They range from very funny to moving.  The text is in no particular order, the panels are roughly arranged by topic, and the stories are fine, indeed.  He gives the reader a feel for what the infantryman, the most important element of any army, went through while at the same time conveying a feeling that it truly can not be comprehended by any outside of the brotherhood of men who were there.

Bill Mauldin has been called the best cartoonist covering the war years and the plight of the foot soldier of all time.  I would agree, though I certainly have not been exposed to the full range.  I liken it to the book Company Commander, written by an average Joe made Captain and given the command of two subsequent companies during the invasion of France.  I tremendously enjoyed Up Front and it will be added to my permanent collection.  I anticipate that I will be reading it again many times, or at least referring back to it on many occasions.

Ed:  Wow, this is my third review in as many weeks!  But that's not entirely fair.  I started this book while still reading Fatal Terrain, which I reviewed a couple weeks late.  I did blaze through The Last Colony very quickly, though.  That was an excellent book!  And Up Front was a very easy read with a lot of space used for panel comics.


Filed Under: Books

Simple ASP.NET Database Query

by fwhagen Wed, 08 August 2007

Below is the most simple database query for C# to populate a GridView object:

int nItemID = 1; try { using (SqlConnection oSQLConn =
new SqlConnection(ConfigurationManager.ConnectionStrings["UserInfo"].ConnectionString)) { oSQLConn.Open(); using (SqlCommand oSQLCmd = new SqlCommand()) { oSQLCmd.Connection = oSQLConn; oSQLCmd.CommandText = "SELECT DisplayName, EMail FROM Users WHERE UserID = @PARAM_ItemID"; oSQLCmd.Parameters.AddWithValue("@PARAM_ItemID", nItemID); SqlDataReader oSQLReader = oSQLCmd.ExecuteReader(); GridView1.DataSource = oSQLReader; GridView1.DataBind(); } } } catch (Exception ex) { Label_ErrorMsg.Text = ex.Message; }

There are actually many reasons to never use this, including performance, caching, safe SQL, code reuse, standards, proper exception handling, etc, etc.  But sometimes you need a quick and dirty, non-production, piece of code for testing and/or development.  This is it.

Keywords: , ,

Filed Under: .NET | Programming | SQL

WebComic RSS Feeds

by fwhagen Wed, 08 August 2007

Almost everything I read regularly online now is through an RSS feed.  The few things I don't get through RSS are either not suited to RSS or don't come in a feed, but are too important to me to skip.  I use SharpReader to collect and browse all of these feeds.  It is not a perfect reader but does what I want it to do better than anything else I can find (for free).

I have always enjoyed comic strips and much prefer them on the web than any other format.  I have been reading Dilbert online since the old days when Scott Adams posted them himself on Usenet.  But not all comics online are available in an RSS feed.  And none of them were a few years ago (before RSS, of course).  I have tried over the years to script or code different ways of collecting strips with some success, into webpages or browsable collections, but something was always lacking.

Today I use a app I created to collect the img tags from the hosted sites and put them into RSS feeds for each strip.  It works for me.  The only problem is the RSS library for .NET that I used doesn't clear the old entries correctly, so the XML file get big over time.  Not huge, just big.  Now I can load my feed into SharpReader and have all of the feed-y goodness such as tracking which entries I've read, notification of new ones, and locking or saving the ones I want to save.  I have about a dozen strips now, including the fabulous XKCD all RSSed up.  If you'd like to use some, let me know and I will email you the link.  I will not post it here as I don't want them abused. 

Oh, the other problem:  I'm always looking for more webcomics.  Most that I like I just stumbled on, so I always take recommendations.  And yes, I will feed up a strip, even if I don't like it, for someone else.  Just the way I am, service-oriented.

Update [10 Aug 2007]:  Ha ha!  When I went to today, I noticed that he's put up an ATOM feed himself.  So I can remove that one from my collection list. 


Filed Under: Programming

Book Review: The Last Colony - John Scalzi (2007)

by fwhagen Sun, 05 August 2007

Scalzi's latest book is The Last Colony, which also marks the end of the OMW trilogy.  The first two books were very enjoyable, so obviously, I had to pick this one up too.  I think I should also note, that this is the only trilogy that I have bought the first run of entirely in hardcover.  They are that good.

This is the last book in the John Perry storyline, at least for now.  Perry and his ex-SpecialOps wife Jane Sagan are asked to be the administrators of a new colony against the demands of the Conclave of Worlds, which Humans are not part of.  Fittingly, the colony is named Roanoke as it will be hidden, or lost, from everyone.  Of course, it is not hidden forever, nor is the premise as simple as it appears.

An excellent, and quick read.  I have read every novel that Scalzi has written to date.  This is the best of them all.  You can tell that his style is maturing nicely in this novel, which was written as well as many of the giants of SciFi.  A more complex story than Old Man's War, more compelling than The Android's Dream, and packed with more intrigue than The Ghost Brigades.  I still think I liked OMW best, but that is because it was much more military than this one.  Can't recommend Scalzi enough for the true SciFi aficionado.


Filed Under: Books