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.

Advertisements

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

  1. […] onto an object model. This is similar to my experiments (2 years ago already) that I developed here, but better optimized for performance as it is used at Stack […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: