Creating a light-weight Data Access Layer in C# (6)

Part 1: Retrieve information on tables and columns

Part 2: SELECT record, access record fields

Part 3: SELECT records, INSERT

Part 4: Generating C# classes

Part 5: Generated C# classes and polymorphism

Part 6: UPDATE record

Our DAL object has two collections, with dictNew holding new values, and dictRecord for previously inserted or retrieved values. To implement an Update() method, the Insert() method has to be fixed to save the inserted values:

   dictRecord = dictNew;
   dictNew = null;

Now we are ready to implement UPDATE. The parameters are collected similarly as described in part 3, which describes the INSERT operation:

private void Update(SqlConnection conn)
{
    if (IdentityColumn != null)
    {
        if (!dictRecord.ContainsKey(IdentityColumn))
            throw new Exception("UPDATE " + this.TableName + " without IDENTITY column set");

        StringBuilder sb = new StringBuilder();
        sb.Append("UPDATE " + TableName + " SET ");

        bool bFirst = true;
        foreach (string s in dictNew.Keys)
        {
            if (!bFirst)
                sb.Append(", ");
            sb.Append(s + " = @" + s);
            bFirst = false;
        }

        sb.Append(" WHERE " + IdentityColumn + " = @" + IdentityColumn);

        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;

        foreach (string s in dictNew.Keys)
        {
            if (dictNew[s] == null)
                cmd.Parameters.AddWithValue("@" + s, DBNull.Value);
            else
                cmd.Parameters.AddWithValue("@" + s, dictNew[s]);
        }

        cmd.Parameters.AddWithValue("@" + IdentityColumn, dictRecord[IdentityColumn]);
        cmd.CommandText = sb.ToString();

        foreach (string s in dictNew.Keys)
            if (dictRecord.ContainsKey(s))
                dictRecord[s] = dictNew[s];
            else
                dictRecord.Add(s, dictNew[s]);

        dictNew = null;
    }
    else
        throw new Exception("UPDATE " + this.TableName + " without IDENTITY column is not implemented.");
}

If the table does not have an IDENTITY column, the WHERE condition for the UPDATE is unknown and an exception is raised. If the identity column of the existing record is not set, an exception is raised, too.