Blogger Jeff = new Blogger

I moved my 5 gigabytes worth of digital photos from my Mac to my PC tonight. This is usually a pretty easy thing to do, as you just copy your “Originals” folder in the iPhoto directory to it’s new destination. I didn’t care about copying the photo albums from iPhoto, but the problem I ran into was getting to the keywords my wife and I added for every stinking photo. iPhoto uses it’s own proprietary method for storing keywords, so I needed to export them somehow.

Thankfully I found the iphotokeywordutilities app that did the trick for me. It’s a little tricky to get going, as you have to :
1.download iphotokeywordutilities
2.Open iPhoto and select the photos whose keywords you want to export.
3. You then click on the applescript from the downloaded zip

What iphotokeywordutilities does is take the iPhoto keyword and embed it right into the image’s IPTC keyword attribute.

I was tasked with this a month or so ago, and came upon a great site called http://www.wikimatrix.org/. It even has a wizard that asks you a number of questions, and then recomends different wiki apps for you.

I ended up downloading and trying 4 different Wikis. These ranged from one written in javascript, to a php based one, and even “asp.old” one. I went with ScrewTurn Wiki, which is a .Net based, Open Source app, written in C#. ScrewTurn comes with an installer, that creates an IIS site for you. One big requirement I had was that the Wiki content pages themselves had to be created outside of the Wiki, because I was using a script to pull out the API class data. Screwturn is also very easy to configure out of the box, has a support forum, and is skinnable.

You can download it from here.

I’ll have to download this and take a spin.

Two things I’ve read about that sound neat off the bat :

(1) Automatic auditing : one of my recent posts detailed how to do this. As I said, I end up doing this on practically every project, so it’s nice Microsoft is baking this right in.

(2) Date only and Time only datatypes : HALLELUJAH! I can’t tell you how many times this has screwed me when doing date comparison, and all I’m concerned with is a date, but the time element throws it off.

One of the more common tasks I perform from project to project is the creation of audit tables. The easiest way I know how to do this is to create a copy of the table I want to audit, and then add a trigger to the parent table. For my example I’ll use my ever-so-popular MLB database and my TEAMS table. The schema for TEAMS looks like:

CREATE TABLE [dbo].[TEAMS](
    [TEAM_ID] [int] IDENTITY(1,1) NOT NULL,
    [TEAM_NAME] [varchar](50) NOT NULL,
    [CITY] [varchar](50) NOT NULL,
    [LEAGUE] [nchar](2) NULL
) ON [PRIMARY]

GO

The audit table for TEAMS will be an exact copy, with a couple of modifications. First, TEAM_ID needs to be a copy, so it’s no longer an identity column, The second change I made was to add an AUDIT_DT column to capture when the change occurred :

CREATE TABLE [dbo].[TEAMS_AUDIT](
    [TEAM_ID] [int] NOT NULL,
    [TEAM_NAME] [varchar](50) NOT NULL,
    [CITY] [varchar](50) NOT NULL,
    [LEAGUE] [nchar](2) NULL,
    [AUDIT_DT] [smalldatetime] NOT NULL DEFAULT (getdate())
) ON [PRIMARY]

I then add a trigger to the TEAMS table to capture updates and deletes (I only want to capture when the original column changes, not when it’s added) :

CREATE TRIGGER TRG_TEAMS
   ON  TEAMS
   AFTER UPDATE,DELETE
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    INSERT INTO [MLB].[dbo].[TEAMS_AUDIT]
           ([TEAM_ID]
           ,[TEAM_NAME]
           ,[CITY]
           ,[LEAGUE])
     SELECT [TEAM_ID]
           ,[TEAM_NAME]
           ,[CITY]
           ,[LEAGUE]
    FROM DELETED
END
GO

That’s it! A couple of things you may want to consider when creating an audit table is :

• Remember that the audit table gets an insert for each update/delete to the parent table. This can have some performance ramifications. One of the things I do is not put any indexes on the audit table.
• You’ll want to purge the data from this table from time to time (or back it up).

Tags: SQL Server

Here’s my two cents on what I think it takes to be a great software developer:

Knowledgeable : If the person doesn’t know what they’re talking about, then it doesn’t matter if they have every other trait on this list. Certifications are nice, but anyone can cram for one or go to one of those nifty sights that list the actual exam questions.

Nothing beats good old fashioned work experience. How the person obtains their knowledge isn’t all that important. I’ve worked with quite a few people who weren’t Comp Sci majors, or weren’t “certified”, or never even graduated from college, who could run circles around other developers who were. In this day and age, so much information is available at your fingertips via the internet that if you take the time, you can learn on your own. Which brings me to my next trait …

Works well independently : With technology changing so often, a good developer has to be able to learn things on their own. A lot of people,however, have trouble with this, and need to “training” from others to learn things. I’m not saying training or learning from others is bad, but if a person needs a lot of hand holding, then they’re going to cost their company more money in regards to time and resources.

Self-Motivated : I think this one speaks for itself, but I have been around a lot of people who need to be kicked and pushed if you want them to get things done. These people usually don’t last too long.

Humility : This is a tough one, and some people may disagree that this is an important trait. I’m sure we’ve all been around a hot-shot developer or two in our time. They’re real nice to have on a team because of their skills, but man can they get annoying. I’ve been with a company that had to “trim some fat”, and surprisingly, they let the best developer go not because he made too much money, but because of his cocky personality. The great developers I’ve been around don’t have much of an ego.

Excellent Communication Skills : This can be a tough one to grasp too. I know for me, it took a couple of years and almost cost me my second job. It wasn’t that I couldn’t talk, but rather, that I didn’t talk. I was shy and tried to stay hidden. The great developers I’ve been around aren’t afraid to ask the tough questions and make sure no questions go unanswered.

Plug and Play : Great developers are able to be plugged into any project and produce the same great results each time. It has little to do with programming languages but more to do with them applying the same work effort to every project.

Can handle criticism : All programmers make mistakes and write bugs (some more than others). However, I have seen some guys get real uptight and defensive when their mistakes are bought up to them. With communication with others being such an important part of a developer’s job, at some point, someone is going to criticize something you do.

Willing to teach others : Younger devs need mentors. It is the responsibility of the more experienced devs to be willing to pass down their knowledge. I’m sure we’ve all worked with a guy or two who wants to keep everything they know “in the vault”.

Accountability : This one’s a tough one too, but the good ones know when to step up and take responsibility for something.

Organized : Not so much the “tidy work area” kind of organized, but more like knowing where everything is, documenting meetings, emails, phone calls.

Enjoys what they do : I think most developers like what they do, but some are just more passionate than others.

Tags: software developer traits

When my daughters surf the internet, it’s usually via my Macbook and Safari. I make them use Safari because you can set it up to require every domain to be approved before they can access it.

I had to send my Macbook away to Apple care a few days ago, and my girls used my Dell to access the internet. Of course this concerned me, because I had no way to filter the websites they were visiting. I then found FoxFilter which is a Firefox add on that allows you to approve web sites in a variety of ways. I have it set to it’s “Whitelist” setting, which is nothing more than a text area box where you enter the permitted domains.

PLEASE NOTE : I just tried to install FilterFox on my Macbook and found out it is not available for Mac OS!

Tags: Safe Web Browsing, Firefox, Foxfilter

Have you ever had to ask a user to input a lot of information at once? Has one of your business requirements ever had you stagger your data input over a series of pages? Have you ever had a client insist on the ability to cycle through each one of those pages, and then display a summary at the end? Well then my friend, if you haven’t ever used ASP.Net’s Wizard control, then you’re missing all the fun.

What the Wizard control does is allow you to collect all the information you need in a series of steps. The control comes built in with functionality to cycle through each of your steps. In this post, we’re going to write a quick page, collecting two fields, add some form validation, and then displaying the results in a summary at the end.

First thing you need to do is add a web form to a project, and drag the Wizard control onto it. We’ll add an additional step to make our code look like:

<asp:Wizard ID="Wizard1" runat="server" >
    <WizardSteps>
        <asp:WizardStep runat="server" title="Step 1">
        </asp:WizardStep>
        <asp:WizardStep runat="server" title="Step 2">
        </asp:WizardStep>
        <asp:WizardStep runat="server" title="Summary">
        </asp:WizardStep>
    </WizardSteps>
</asp:Wizard>

What these three steps represent, of course, is a different step in our process. The Wizard control itself is derived from the View Control. We could accomplish these same steps with the View control, but would have to add a lot of what the Wizard control gives us (navigation from step to step,etc). If we run this now, we see what we get “out of the box” from our Wizard control :

Let’s add the following to our code :

<asp:Wizard ID="Wizard1" runat="server" >
    <WizardSteps>
        <asp:WizardStep runat="server" title="Step 1">
            <asp:Label ID="lblFirstName" runat="server" Text="First Name" /> :
            <asp:TextBox ID="txtFirstName" runat="server" />
        </asp:WizardStep>
        <asp:WizardStep runat="server" title="Step 2">
            <asp:Label ID="lblState" runat="server" Text="State" /> :
            <asp:DropDownList ID="ddlState" runat="server">
                <asp:ListItem Text="Please select a state" Value="" />
                <asp:ListItem Text="PA" Value="PA" />
                <asp:ListItem Text="MD" Value="MD" />
                <asp:ListItem Text="DE" Value="DE" />
            </asp:DropDownList>
        </asp:WizardStep>
        <asp:WizardStep runat="server" title="Summary">
             <table border=1>
                <tr><td>First Name :</td><td><% Response.Write(txtFirstName.Text); %></td></tr>
                <tr><td>State :</td><td><% Response.Write(ddlState.SelectedValue); %></td></tr>
             </table>
        </asp:WizardStep>
    </WizardSteps>
</asp:Wizard>

This should be pretty self-explanatory, as we’ve added labels and controls to steps one and two, and then summary information to step three. If you run this, you can cycle through the steps, and the information, and get a summary at the end. You can also cycle back through and adjust the data as well. The final part missing is data validation. This too is easy to do, as all we have to do is add a validation summary control and whatever validation controls we need. We’ll do so, and adjust our now complete code to look like this :

<asp:ValidationSummary runat="server">
    </asp:ValidationSummary>
<asp:Wizard ID="Wizard1" runat="server" >

    <WizardSteps>
        <asp:WizardStep runat="server" title="Step 1">
            <asp:Label ID="lblFirstName" runat="server" Text="First Name" /> :
            <asp:TextBox ID="txtFirstName" runat="server" />
            <asp:RequiredFieldValidator ID="valFirstname"
                runat="server" ErrorMessage="First Name is required"
ControlToValidate="txtFirstName">*</asp:RequiredFieldValidator>
        </asp:WizardStep>
        <asp:WizardStep runat="server" title="Step 2">
            <asp:Label ID="lblState" runat="server" Text="State" /> :
            <asp:DropDownList ID="ddlState" runat="server">
                <asp:ListItem Text="Please select a state" Value="" />
                <asp:ListItem Text="PA" Value="PA" />
                <asp:ListItem Text="MD" Value="MD" />
                <asp:ListItem Text="DE" Value="DE" />
            </asp:DropDownList>
            <asp:RequiredFieldValidator ID="reqState"
                runat="server" ErrorMessage="State is required"
ControlToValidate="ddlState">*</asp:RequiredFieldValidator>
        </asp:WizardStep>
        <asp:WizardStep runat="server" title="Summary">
             <table border=1>
                <tr><td>First Name :</td><td><% Response.Write(txtFirstName.Text); %></td></tr>
                <tr><td>State :</td><td><% Response.Write(ddlState.SelectedValue); %></td></tr>
             </table>
        </asp:WizardStep>
    </WizardSteps>
</asp:Wizard>

Now if we try to skip to Step 2 without entering a name, we’ll see :

There you have it, a rather simple example that should get you on your way. The last part to this, once the Wizard is complete would be to save the contents to a data source.

Tags: ASP.Net, C#

I get asked from time to time if SQL Server supports cascading deletes, and the answer is Yes. SQL Server does so via foreign key constraints with the DELETE CASCADE flag set. In the following example, after creating the objects and inserting some data, we delete a USR_ID from the parent data. After querying the child table (USER_PHONE) a second time, we can see that the cascading delete worked :

CREATE TABLE USERS
(
    USR_ID int
    ,CONSTRAINT [PK_Temp_Users1] PRIMARY KEY CLUSTERED ([USR_ID])
)

CREATE TABLE USER_PHONE
(
    USR_ID int
    ,CONSTRAINT [PK_Temp_Users2] PRIMARY KEY CLUSTERED ([USR_ID])
)
ALTER TABLE [dbo].USER_PHONE  WITH CHECK ADD
CONSTRAINT [FK_Temp_UsersPhone_Users] FOREIGN KEY([USR_ID])
REFERENCES [dbo].[Users] ([USR_ID])
ON DELETE CASCADE
GO

INSERT INTO USERS
    SELECT 1 UNION SELECT 2 UNION SELECT 3

INSERT INTO USER_PHONE
    SELECT 1 UNION SELECT 2 UNION SELECT 3

SELECT * FROM USER_PHONE
DELETE USERS WHERE USR_ID=2
SELECT * FROM USER_PHONE

DROP TABLE USER_PHONE
DROP TABLE USERS

Tags: SQL Server

I know there have been a ton of times I’ve had to deal directly with Comma Delimited files. Searching through the contents for certain pieces of data is usually not a whole lot of fun. However with Linq, things become a bit more enjoyable!

To start off, we’ll create a csv file with the following contents :

"Mets","New York","NL"
"Marlins","Florida","NL"
"Orioles","Baltimore","AL"
"Pirates","Pittsburgh","NL"
"Phillies","Philadelphia","NL"

Save that some place and name it “teams.csv”. Then we’ll add the following namespace to our class file :

using System.Data.OleDb

Then using OleDb, we can access the csv file, and load a data table into a data adapter :

String conn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\;
    Extended Properties=""Text;HDR=No;FMT=Delimited""";

OleDbConnection cn = new OleDbConnection(conn);
OleDbCommand cmd = new OleDbCommand(@"SELECT * FROM C:\Temp\teams.csv", cn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);

cn.Open();

DataTable dt = new DataTable();

da.Fill(dt);

At this point, we are good to go to query the datatable with Linq. Our entire function looks like this :

static void QueryCsv()
{

    OleDbConnection cn = new OleDbConnection (@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=
C:\;Extended Properties=""Text;HDR=No;FMT=Delimited""");
    OleDbCommand cmd = new OleDbCommand(@"SELECT * FROM C:\Temp\teams.csv", cn);
    OleDbDataAdapter da = new OleDbDataAdapter(cmd);

    cn.Open();

    DataTable dt = new DataTable();

    da.Fill(dt);

    var teams = from r in dt.AsEnumerable()
                where r.Field(2) == "NL"
                select new { City = r.Field(0), TeamName= r.Field(1) };

    foreach (var team in teams)
    {
        Console.WriteLine(String.Format("The {0} {1}",team.TeamName,team.City));
    }

    Console.ReadLine();

    cn.Close();

}

If you run this you should see :

Now if your life is like mine, the client will always throw a screwball at you and say something like : “We need to return everything from the csv file where the record also exists in this other data source.” Linq makes this easy too. We’ll create a quick class to demonstrate to represent the other data source :

public class Mascot
{
    public string Name;
    public string City;
    public string Player;
}

We’ll then create a list with our data in it

List mlb = new List
{
    new Mascot{City="Philadelphia",Name="Philllies",Player="Cole Hammels"},
    new Mascot{City="Boston",Name="Red Sox",Player="Curt Schilling"},
    new Mascot{City="Florida",Name="Marlins",Player="Dontrelle Willis"},
    new Mascot{City="Cleveland",Name="Indians",Player="Jake Westbrook"},
    new Mascot{City="Baltimore",Name="Braves",Player="Eric Bedard"},
    new Mascot{City="Los Angeles",Name="Dodgers",Player="Randy Wolf"}
};

Adjusting our original code, we can query and join the two data sources to display some common data :

static void QueryCsvWithJoin()
{

	 String conn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\;
	     Extended Properties=""Text;HDR=No;FMT=Delimited""";

	 List mlb = new List
	 {
	     new Mascot{City="Philadelphia",Name="Philllies",Player="Cole Hammels"},
	     new Mascot{City="Boston",Name="Red Sox",Player="Curt Schilling"},
	     new Mascot{City="Florida",Name="Marlins",Player="Dontrelle Willis"},
	     new Mascot{City="Cleveland",Name="Indians",Player="Jake Westbrook"},
	     new Mascot{City="Baltimore",Name="Braves",Player="Eric Bedard"},
	     new Mascot{City="Los Angeles",Name="Dodgers",Player="Randy Wolf"}
	 };

	 OleDbConnection cn = new OleDbConnection(conn);
	 OleDbCommand cmd = new OleDbCommand(@"SELECT * FROM C:\Temp\teams.csv", cn);
	 OleDbDataAdapter da = new OleDbDataAdapter(cmd);

	 cn.Open();

	 DataTable dt = new DataTable();

	 da.Fill(dt);

	 var teams = from r in dt.AsEnumerable()
	             join o in mlb on r.Field(1) equals o.City
	             where r.Field(2) == "NL"
	             select new { City = r.Field(0), TeamName= r.Field(1),Player=o.Player }; 

	 foreach (var team in teams)
	 {
	     Console.WriteLine(String.Format("Pitcher {0} from the {1} {2}", team.Player,team.TeamName, team.City));
	 }

	 Console.ReadLine();

	 cn.Close(); 

}

When run this, we get

Tags: .Net, C#, Linq, CSV, Comma Delimited

The ROW_NUMBER function is a neat little enhancement that makes a fairly common task in Sql pretty easy : generating a row number for a result set. You’ve always been able to do this Sql, but had to do something like the following :

CREATE TABLE #temp
(
	RowNumber int identity
	,LastName varchar(100)
	,HireDate datetime
)

INSERT INTO #temp(LastName
	,HireDate)
SELECT LastName
	,HireDate
FROM dbo.Employees
ORDER BY HireDate DESC,LastName DESC

SELECT * FROM #temp

Results :

Why would you want to return a unique row number in a result set? A lot of times it’s for more complex comparisons, or maybe for a paging function.

The ROW_NUMBER function makes this step easier :

SELECT ROW_NUMBER() OVER(ORDER BY HireDate DESC,LastName DESC) AS 'RowNumber'
	,LastName
	,HireDate
FROM dbo.Employees

Results :

All we’re basically doing here is telling the result set how the RowNumber will be assigned. This is doing the same thing as our first example.

However, the ROW_NUMBER function has a PARTITION attribute we can set. What this does is first group our data by one or more columns, and then assign the RowNumber column. For instance in our example above, we could partition the data on TitleOfCourtesy(Mr.,Mrs.,etc). What this means is for each unique instance of TitleOfCourtesy, we assign a RowNumber. So our Sql looks like this :

SELECT ROW_NUMBER() OVER(PARTITION BY TitleOfCourtesy
			ORDER BY HireDate DESC,LastName DESC) AS 'RowNumber'
	,LastName
	,HireDate,TitleOfCourtesy
FROM dbo.Employees
ORDER BY ROWNUMBER

Which gives us this result set:

Tags: Sql Server 2005