June 2010


Although I have some personal thoughts on Edison and his tactics when it came to giving credit to those that assisted him (e.g. Nikola Tesla), the 1929 recording originally done on a pallophotophone and the work behind restoring it (see: gereports.com and retrothing.com) is still pretty impressive. It is not what I thought Thomas Edison would have sounded like, but of course he was 82 at the time.

Advertisements

Most of today’s business applications deal with relational databases, despite the apparent movement of many developers that feel we should abandon some of the most mature software technology in the industry and move to object databases. If you are using SQL Server as your relational database you may run into a scenario where you want to execute a parameterized SQL statement, but need to return the value of an identity column after it is generated. A simple way to this is to use an output parameter and execute an extra statement after the insert to get the identity column using the SCOPE_IDENTITY function.

/// <summary>/// Insert.  Insert a row in the Data Store.
/// </summary>
/// <param name="dataSourceCommand">The command object</param>
/// <param name="item">The item to save</param>
private static void InsertRow(DbCommand dataSourceCommand, ItemClass item)
{
     private const string SQL_INSERT =
@"INSERT INTO Table(name,lastChangeDate)
VALUES (@name,@lastChangeDate) SET @sak=SCOPE_IDENTITY()";

    try
    {
        dataSourceCommand.Parameters.Clear();

        string nameParameterName = string.Format("@{0}", _nameColumnName);
        DbParameter nameParm = dataSource.Command.CreateParameter();
        nameParm.ParameterName = nameParameterName;
        nameParm.DbType = System.Data.DbType.Int32;
        nameParm.Value = item.Name;
        dataSource.Command.Parameters.Add(nameParm);

        string lastChangeDateParameterName =             string.Format("@{0}", _lastChangeDateColumnName);
        DbParameter lastChangeDateParm = dataSource.Command.CreateParameter();
        lastChangeDateParm.ParameterName = lastChangeDateParameterName;
        lastChangeDateParm.DbType = System.Data.DbType.DateTime;
        lastChangeDateParm.Value = item.LastChangeDate;
        dataSource.Command.Parameters.Add(lastChangeDateParm);

        string identityParameterName = string.Format("@{0}", _identityName);
        DbParameter identityParm = dataSource.Command.CreateParameter();
        identityParm.ParameterName = identityParameterName;
        identityParm.DbType = System.Data.DbType.Int32;
        identityParm.Direction = System.Data.ParameterDirection.Output;
        dataSource.Command.Parameters.Add(identityParm);

        dataSource.Command.CommandText = SQL_INSERT;

        int i = dataSource.Command.ExecuteNonQuery();
        item.Identity = (int)identityParm.Value;
    }
    catch (Exception ex)
    {
        throw;
    }
}