使用 OleDbParameter来写Access的更新没反应的解决办法

CodeSmith自定义模板生成的DAL，一开始生成的是MsSQL的。后来有需求，要转换成了Access。动软对Access生成支持不好，我手动替换了一次。然后把数据库从SQL导进Access。咋一看都没问题。
　　结果到了更新的部分，更新无任何变化：

#region Update

/// <summary>
/// 更新

/// </summary>    
/// <param name="dictionaryInfo">DictionaryInfo 实体对象</param>
public void UpdateByPrimaryKey(DictionaryInfo dictionaryInfo)
{
    StringBuilder strSql = new StringBuilder();
    strSql.Append("update dt_Dictionary set ");
    strSql.Append("dict_CreateDate=@dict_CreateDate,");
    strSql.Append("dict_Creator=@dict_Creator,");
    strSql.Append("dict_IsVisible=@dict_IsVisible,");
    strSql.Append("dict_Key=@dict_Key,");
    strSql.Append("dict_Type=@dict_Type,");
    strSql.Append("dict_Value=@dict_Value");

    strSql.Append(" where dict_ID=@dict_ID");
    OleDbParameter[] parameters = {
            new OleDbParameter("@dict_CreateDate", OleDbType.Date),
            new OleDbParameter("@dict_Creator", OleDbType.VarChar),
            new OleDbParameter("@dict_ID", OleDbType.Integer),
            new OleDbParameter("@dict_IsVisible", OleDbType.Boolean),
            new OleDbParameter("@dict_Key", OleDbType.Integer),
            new OleDbParameter("@dict_Type", OleDbType.Integer),
            new OleDbParameter("@dict_Value", OleDbType.VarChar)
        };
    parameters[0].Value = dictionaryInfo.CreateDate;
    parameters[1].Value = dictionaryInfo.Creator;
    parameters[2].Value = dictionaryInfo.ID;
    parameters[3].Value = dictionaryInfo.IsVisible;
    parameters[4].Value = dictionaryInfo.Key;
    parameters[5].Value = dictionaryInfo.Type;
    parameters[6].Value = dictionaryInfo.Value;
    DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
}

#endregion

运行……，但是却没有效果——更新之后一刷新，又还原了！数据库里面根本没改动。
后来查资料才知道。是微软的一个BUG。在更新Access数据库时，如果使用OleDbParameter。那么OleDbParameter参数的的定义顺序必须和查询语句中参数出现的顺序一致！

这里：strSql.Append(" where dict_ID=@dict_ID"); 和  new OleDbParameter("@dict_ID", OleDbType.Integer), 顺序不一致导致!
所以，只要把参数定义的顺序换过来就行了。修改CodeSmith模板，自动生成以下代码执行成功：

#region Update

/// <summary>
/// 更新

/// </summary>    
/// <param name="dictionaryInfo">DictionaryInfo 实体对象</param>
public void UpdateByPrimaryKey(DictionaryInfo dictionaryInfo)
{
    StringBuilder strSql = new StringBuilder();
    strSql.Append("update dt_Dictionary set ");
    strSql.Append("dict_CreateDate=@dict_CreateDate,");
    strSql.Append("dict_Creator=@dict_Creator,");
    strSql.Append("dict_IsVisible=@dict_IsVisible,");
    strSql.Append("dict_Key=@dict_Key,");
    strSql.Append("dict_Type=@dict_Type,");
    strSql.Append("dict_Value=@dict_Value");

    strSql.Append(" where dict_ID=@dict_ID ");
    OleDbParameter[] parameters = {
            new OleDbParameter("@dict_CreateDate", OleDbType.Date),
            new OleDbParameter("@dict_Creator", OleDbType.VarChar),
            new OleDbParameter("@dict_IsVisible", OleDbType.Boolean),
            new OleDbParameter("@dict_Key", OleDbType.Integer),
            new OleDbParameter("@dict_Type", OleDbType.Integer),
            new OleDbParameter("@dict_Value", OleDbType.VarChar),
            new OleDbParameter("@dict_ID", OleDbType.Integer)
        };
    parameters[0].Value = dictionaryInfo.CreateDate;
    parameters[1].Value = dictionaryInfo.Creator;
    parameters[2].Value = dictionaryInfo.IsVisible;
    parameters[3].Value = dictionaryInfo.Key;
    parameters[4].Value = dictionaryInfo.Type;
    parameters[5].Value = dictionaryInfo.Value;
    parameters[6].Value = dictionaryInfo.ID;
    DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
}

#endregion

Technorati 标签: OleDbParameter,Access,Update