我们在Asp.Net Core Web API应用程序中使用Oracle,需要从Core应用程序访问Oracle数据库。要将Oracle数据库与.Net Core应用程序一起使用,我们有Oracle库,它将帮助我们管理数据库访问的逻辑。因此,我们必须安装以下bata的软件包。
Install-Package Oracle.ManagedDataAccess.Core -Version 2.12.0-beta2
添加 Oracle 数据库连接现在我们已准备好与数据库相关的所有内容,如数据库,表和SP等。要从Web API访问数据库,我们必须像往常一样在“appsettings.json”文件中创建连接字符串。
{ "Logging": { "IncludeScopes": false, "Debug": { "LogLevel": { "Default": "Warning" } }, "Console": { "LogLevel": { "Default": "Warning" } } }, "ConnectionStrings": { "EmployeeConnection": "data source=mukesh:1531;password=**********;user id=mukesh;Incr Pool Size=5;Decr Pool Size=2;" } } 创建一个仓储为了保持关注点的分离,我们在这里使用Repository。在Web API项目中创建一个新文件夹作为“仓储库”,并创建一个“IEmployeeRepository”接口和一个它的实现类“EmployeeRepository”,它将实现到IEmployeeRepository。
namespace Core2API.Repositories { public interface IEmployeeRepository { object GetEmployeeList(); object GetEmployeeDetails(int empId); } }以下是实现了IEmployeeRepository的EmployeeRepository类。它需要访问配置中的数据库连接串,因此我们在构造函数中注入IConfiguration。所以,我们已经准备好使用配置对象了。除此之外,我们还有GetConnection()方法,该方法将从appsettings.json获取连接字符串,并将其提供给OracleConnection以创建连接并最终返回连接。我们已经实现了“IEmployeeRepository”,它有两个方法,如GetEmployeeDetails和GetEmployeeList。
using Core2API.Oracle; using Dapper; using Microsoft.Extensions.Configuration; using Oracle.ManagedDataAccess.Client; using System; using System.Data; namespace Core2API.Repositories { public class EmployeeRepository : IEmployeeRepository { IConfiguration configuration; public EmployeeRepository(IConfiguration _configuration) { configuration = _configuration; } public object GetEmployeeDetails(int empId) { object result = null; try { var dyParam = new OracleDynamicParameters(); dyParam.Add("EMP_ID", OracleDbType.Int32, ParameterDirection.Input, empId); dyParam.Add("EMP_DETAIL_CURSOR", OracleDbType.RefCursor, ParameterDirection.Output); var conn = this.GetConnection(); if (conn.State == ConnectionState.Closed) { conn.Open(); } if (conn.State == ConnectionState.Open) { var query = "USP_GETEMPLOYEEDETAILS"; result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure); } } catch (Exception ex) { throw ex; } return result; } public object GetEmployeeList() { object result = null; try { var dyParam = new OracleDynamicParameters(); dyParam.Add("EMPCURSOR", OracleDbType.RefCursor, ParameterDirection.Output); var conn = this.GetConnection(); if(conn.State == ConnectionState.Closed) { conn.Open(); } if (conn.State == ConnectionState.Open) { var query = "USP_GETEMPLOYEES"; result = SqlMapper.Query(conn, query, param: dyParam, commandType: CommandType.StoredProcedure); } } catch (Exception ex) { throw ex; } return result; } public IDbConnection GetConnection() { var connectionString = configuration.GetSection("ConnectionStrings").GetSection("EmployeeConnection").Value; var conn = new OracleConnection(connectionString); return conn; } } } public IDbConnection GetConnection() { var connectionString = configuration.GetSection("ConnectionStrings").GetSection("EmployeeConnection").Value; var conn = new OracleConnection(connectionString); return conn; }为了在.Net Core中使用Oracle的数据类型,我们使用的是OracleDyamicParameters类,它将提供管理Oracle参数行为的一系列方法。
using Dapper; using Oracle.ManagedDataAccess.Client; using System.Collections.Generic; using System.Data; namespace Core2API.Oracle { public class OracleDynamicParameters : SqlMapper.IDynamicParameters { private readonly DynamicParameters dynamicParameters = new DynamicParameters(); private readonly List<OracleParameter> oracleParameters = new List<OracleParameter>(); public void Add(string name, OracleDbType oracleDbType, ParameterDirection direction, object value = null, int? size = null) { OracleParameter oracleParameter; if (size.HasValue) { oracleParameter = new OracleParameter(name, oracleDbType, size.Value, value, direction); } else { oracleParameter = new OracleParameter(name, oracleDbType, value, direction); } oracleParameters.Add(oracleParameter); } public void Add(string name, OracleDbType oracleDbType, ParameterDirection direction) { var oracleParameter = new OracleParameter(name, oracleDbType, direction); oracleParameters.Add(oracleParameter); } public void AddParameters(IDbCommand command, SqlMapper.Identity identity) { ((SqlMapper.IDynamicParameters)dynamicParameters).AddParameters(command, identity); var oracleCommand = command as OracleCommand; if (oracleCommand != null) { oracleCommand.Parameters.AddRange(oracleParameters.ToArray()); } } } } 在Startup.cs中配置依赖