I've worked on lots of projects but never been in a position to push everyone to have a unit test for all the important pieces of the application.

Pushing for unit tests makes me feel like the resident nag, especially when it goes beyond making the proclaimation that we'll approach the project in this way and leads into my sitting down with people pointing out code that seems to work but isn't covered by any unit tests. I'd be inclined to follow up everyone's code with unit tests they could have written myself, but it's too much work for a single person.

Another difficulty is that while I get the concepts behind unit testing, writing a good unit test is proving more thought provoking and difficult than just the idea of having a test that covers a method call. Most of the articles about unit testing talk about it in terms of methods that don't seem to have as much complexity or methods that don't leverage external resources. It's easy, with articles like that to have an example of a method that adds two numbers and then has a test that asserts that two values added have a correct result.

In our case, we've got a somewhat large database that we are utilizing in most methods making how/what we test for much more tricky. For example, if we have a method:

public LoanApplication GetApplicationById(int appId){ ... }

What's the best way to test it? Thus far we've got the following things:
1. Make sure the result is not null.
2. Compare properties against database query (with a tested database library).

Our test for that method may look like the following:

public void TestGetApplicationById(){
AppService appService = new AppService();
LoanApplication testLoanApp = appService.GetApplicationById(1);
DataTable testTable = db.GetDataTable("select * from App where AppId=1");
Assert.AreEqual(testTable.Rows[0]["AppId"], testLoanApp.AppId);
// other fields test

How does one get tests like this right?