文章標題很長，但主要是要記錄出現「OracleParameterCollection 只接受非 Null 的 OracleParameter 型別物件，不接受 OracleParameter 物件。」這個錯誤時的解決方式，這是出現在專案使用 Enterprise Library Data Access Application Block 存取 Oracale 資料庫的情況，同樣的存取方式如果是換成是對 MS SQL Server 做存取時就不會有問題，而對 Oracle 資料庫存取時就必須要使用另一種方式來解決。
錯誤畫面：
開發使用環境：
Visual Studio 2012, Oracle XE, ASP.NET MVC 4,
.NET Framework 4.5,
Enterprise Library 6 - Data Access Application.
對 MS SQL Server 取得資料的處理
先看看對 MS SQL Server 資料庫取得資料的程式，
CategoryRepository.cs - GetOne Method
/// <summary>/// Gets the one./// </summary>/// <param name="id">The id.</param>/// <returns></returns>public Category GetOne(int id)
{string sqlStatement = "select * from Categories where CategoryID = @CategoryID";
try {DataAccessor<Category> accessor =
this.Db.CreateSqlStringAccessor<Category>(sqlStatement,
new CategoryIDParameterMapper(), new CategoryMapper());var data = accessor.Execute(new object[] { id }).FirstOrDefault();
return data;}
catch { throw;}
}
public class CategoryIDParameterMapper : IParameterMapper
{public void AssignParameters(DbCommand command, object[] parameterValues)
{command.Parameters.Add(new SqlParameter("CategoryID", parameterValues[0]));
}
}
public class CategoryMapper : IRowMapper<Category>
{ public Category MapRow(IDataRecord reader) { Category item = new Category();item.CategoryID = int.Parse(reader["CategoryID"].ToString());
item.CategoryName = reader["CategoryName"].ToString(); item.Description = reader["Description"].ToString(); return item;}
}
這邊我所使用了 EntLib DAAB 裡面的 DataAccessor, IRowMapper, IParameterMapper，能夠讓程式碼更為簡潔，而且可以重複使用 IRowMapper 與 IParameterMapper，當其他 Method 使用到相同 Parameter 結構或是資料對應輸出時，就可以不必重複輸入一樣的程式碼內容。
為了做個比對，以下是一樣使用 EntLib DAAB 但沒有使用 IParamMapper 與 IRowMapper 的程式碼內容，
/// <summary>/// Gets the categories./// </summary>/// <returns></returns>public List<Category> GetCategories(){ List<Category> categories = new List<Category>();string sqlStatement = "select * from Categories order by CategoryID";
using (DbCommand comm = Db.GetSqlStringCommand(sqlStatement))using (IDataReader reader = this.Db.ExecuteReader(comm))
{ while (reader.Read()) { Category item = new Category();item.CategoryID = int.Parse(reader["CategoryID"].ToString());
item.CategoryName = reader["CategoryName"].ToString(); item.Description = reader["Description"].ToString();categories.Add(item);
}
}
return categories;}
/// <summary>/// Gets the one./// </summary>/// <param name="id">The id.</param>/// <returns></returns>public Category GetOne(int id)
{string sqlStatement = "select * from Categories where CategoryID = @CategoryID";
Category item = new Category(); using (DbCommand comm = Db.GetSqlStringCommand(sqlStatement)) {comm.Parameters.Add(new SqlParameter("CategoryID", id));
using (IDataReader reader = this.Db.ExecuteReader(comm))
{ if (reader.Read()) {item.CategoryID = int.Parse(reader["CategoryID"].ToString());
item.CategoryName = reader["CategoryName"].ToString(); item.Description = reader["Description"].ToString();}
}
}
return item;}
在上面的程式碼裡可以看到兩個 Method 裡都有做資料對應的處理，同樣的處理作法卻重複出現，如果是改用 IRowMapper, IParameterMapper, DataAccessor 的方式來處理就會簡潔許多，如下：
/// <summary>/// Gets the categories ./// </summary>/// <returns></returns>public List<Category> GetCategories(){string sqlStatement = "select * from Categories order by CategoryID";
try {DataAccessor<Category> accessor =
this.Db.CreateSqlStringAccessor<Category>(sqlStatement, new CategoryMapper());
var categories = accessor.Execute().ToList();
return categories;}
catch { throw;}
}
/// <summary>/// Gets the one./// </summary>/// <param name="id">The id.</param>/// <returns></returns>public Category GetOne(int id)
{string sqlStatement = "select * from Categories where CategoryID = @CategoryID";
try {DataAccessor<Category> accessor =
this.Db.CreateSqlStringAccessor<Category>(sqlStatement,
new CategoryIDParameterMapper(), new CategoryMapper());var data = accessor.Execute(new object[] { id }).FirstOrDefault();
return data;}
catch { throw;}
}
而如果是不使用 EntLib DAAB 而是使用傳統的 ADO.NET 方式呢？
/// <summary>/// Gets the categories./// </summary>/// <returns></returns>public List<Category> GetCategories(){ List<Category> categories = new List<Category>();string sqlStatement = "select * from Categories order by CategoryID";
using (SqlConnection conn = new SqlConnection(this.ConnectionString))
using (SqlCommand command = new SqlCommand(sqlStatement, conn))
{command.CommandType = CommandType.Text;
command.CommandTimeout = 180;
if (conn.State != ConnectionState.Open) conn.Open(); using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { Category item = new Category();item.CategoryID = int.Parse(reader["CategoryID"].ToString());
item.CategoryName = reader["CategoryName"].ToString(); item.Description = reader["Description"].ToString();categories.Add(item);
}
}
}
return categories;}
/// <summary>/// Gets the one./// </summary>/// <param name="id">The id.</param>/// <returns></returns>public Category GetOne(int id)
{string sqlStatement = "select * from Categories where CategoryID = @CategoryID";
Category item = new Category();using (SqlConnection conn = new SqlConnection(this.ConnectionString))
using (SqlCommand comm = new SqlCommand(sqlStatement, conn))
{comm.Parameters.Add(new SqlParameter("CategoryID", id));
if (conn.State != ConnectionState.Open) conn.Open(); using (IDataReader reader = comm.ExecuteReader()) { if (reader.Read()) {item.CategoryID = int.Parse(reader["CategoryID"].ToString());
item.CategoryName = reader["CategoryName"].ToString(); item.Description = reader["Description"].ToString();}
}
}
return item;}
對 Oracle 取得資料的處理
離題有點太遠了，趕緊拉回來……
上面所看到的是資料庫為 MS SQL Server 的情況，如果資料庫換做是使用 Oracle XE 的情況，一樣是使用 EntLib DAAB, DataAccessor, IRowMapper, IParameterMapper，程式碼如下：
CategoryRepository.cs - GetOne() Method
/// <summary>/// Gets the one./// </summary>/// <param name="id">The id.</param>/// <returns></returns>public Category GetOne(int id)
{string sqlStatement = "select * from \"Categories\" where \"CategoryID\" = :CategoryID";
try {DataAccessor<Category> accessor =
this.Db.CreateSqlStringAccessor<Category>(sqlStatement,
new CategoryIDParameterMapper(), new CategoryMapper());var data = accessor.Execute(new object[] { id }).FirstOrDefault();
return data;}
catch { throw;}
}
CategoryIDParameterMapper, CategoryMapper
public class CategoryIDParameterMapper : IParameterMapper
{public void AssignParameters(DbCommand command, object[] parameterValues)
{command.Parameters.Add(new OracleParameter("CategoryID", parameterValues[0]));
}
}
public class CategoryMapper : IRowMapper<Category>
{ public Category MapRow(IDataRecord reader) { Category item = new Category();item.CategoryID = int.Parse(reader["CategoryID"].ToString());
item.CategoryName = reader["CategoryName"].ToString(); item.Description = reader["Description"].ToString(); return item;}
}
問題發生在 CategoryIDParameterMapper 裡面，如果將 OracleParamerer 加入到 DbCommand 的方式改使用一般的方式，如下：
public class CategoryIDParameterMapper : IParameterMapper
{public void AssignParameters(DbCommand command, object[] parameterValues)
{OracleParameter param = new OracleParameter("CategoryID", OracleDbType.Long, 11);
param.Value = parameterValues[0];
command.Parameters.Add(param);
}
}
還是會出現同樣的錯誤，
DbCommand.CreateParameter 方法
解決的方式就是使用 DbCommand.CreateParameter 方法，透過 DbCommand 建立 DbParameter 物件的新執行個體，修改後的 CategoryIDParameterMapper 如下：
public class CategoryIDParameterMapper : IParameterMapper
{public void AssignParameters(DbCommand command, object[] parameterValues)
{var param = command.CreateParameter();
param.ParameterName = "CategoryID";param.Value = parameterValues[0];
command.Parameters.Add(param);
}
}
修改後就不會出現錯誤，執行結果如下：
參考資料：
MSDN - DbCommand.CreateParameter 方法
簡述 Oracle + Enterprise Library 5.0 Data Access Application Block 的操作
Enterprise Library DAAB + Oracle：Provider 使用 ODP.NET
以上
沒有留言:
張貼留言