CODEWITHSUNDEEP
asp.netasp.net-mvcasp.net-web-apidapper
trx
trx

Reputation: 2167

Handle Oracle Database Connection in Dapper

I am trying to connect to the Oracle Database and trying to execute a query.

So below is my Model Class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.ComponentModel.DataAnnotations.Schema;
using System.ComponentModel.DataAnnotations;

namespace TestAPI.Models
{
public class TestAPIModel
{
    [Key]
    public int PRIO_CATEGORY_ID { get; set; }
    public int LANG_ID { get; set; }
    public System.DateTime REC_DATE { get; set; }
    public int REC_USER { get; set; }
    public Nullable<int> RFCH_ID { get; set; }
    public string DESCR { get; set; }
    public string COL_DESCR { get; set; }
    public string ROW_DESCR { get; set; }
    public string ABBR { get; set; }
}
}

DBContext Class is 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;

namespace TestAPI.Models
{
public class TestAPIContext: DbContext
{
        public DbSet<TestAPIModel> details { get; set; }
}
}

Now trying to create the Controller with the Dapper, now the issue is in most of the forums it is trying to connect to SQL Database. I am trying to access Oracle DB and return the result in JSON format .So if I give

using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using Newtonsoft.Json;
using System.Web.Http.Description;
using TestAPI.Models;
using Dapper;

namespace TestAPI.Controllers
 {
  public class TestAPIModelsController : ApiController
   {
     // GET: api/TestAPIModels
    public IQueryable<TestAPIModel> Getdetails(int id)
    {
      OracleConnection dbConn = new OracleConnection("DATA SOURCE=AX;PASSWORD=CM;PERSIST SECURITY INFO=True;USER ID=AB");
      dbConn.Open();
      var strQuery = @"Select PRIO_CATEGORY_ID as PRIO,LANG_ID as LANG, REC_DATE as REC, REC_USER as RECUSER, DESCR,COL_DESCR AS COL,ROW_DESCR as DROW,ABBR from STCD_PRIO_CATEGORY_DESCR where REC_USER =  " +id;
      retrun dbConn.Query<TestAPIModel>();
      dbConn.Close();
  }
 }
}

It throws an error saying that the dbconn.Query is not in context and I also tried TestAPIContext.Init even that throws error. Can anyone please suggest me how to deal Oracle connection with the Dapper. I am new to ASP.NET and the Creating the services. kind of really stuck, any help is greatly appreciated.

Upvotes: 5

Views: 30822

Answers (2)

William Xifaras
William Xifaras

Reputation: 5312

You were not passing the SQL. Also, the explicit close is not needed. You can wrap the code in a using as under the hood SqlConnection.Dispose() calls the SqlConnection.Close().

Perhaps this a typo, but "retrun" should be "return". Connectionstring should be read from the app.config vs. hard coded and I'd also consider making 'id' a parameter.

using (var dbConn = new OracleConnection("DATA SOURCE=AX;PASSWORD=CM;PERSIST SECURITY INFO=True;USER ID=AB"))
{
      dbConn.Open();
      var strQuery = @"Select PRIO_CATEGORY_ID as PRIO,LANG_ID as LANG, REC_DATE as REC, REC_USER as RECUSER, DESCR,COL_DESCR AS COL,ROW_DESCR as DROW,ABBR from STCD_PRIO_CATEGORY_DESCR where REC_USER = " +id;
      return dbConn.Query<TestAPIModel>(strQuery);
}

Upvotes: 9

Mohammad Dayyan
Mohammad Dayyan

Reputation: 22458

I could handle this, with the following solution:

1. Install Oracle.ManagedDataAccess.Core Package from nuget.
2. Write ConnectionString as follows:

Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521))(CONNECT_DATA=(SID=sidName))); User Id=*****; Password=******;

PORT=1521 => Default port of Oracle is 1521, you can change it with your Oracle server port
0.0.0.0 => Oracle server IP or name
sidName => SidName of oracle server

3. C# snippet code:

protected IDbConnection GetChargingOracleDbConnection
{
    get
    {
        var oracleConnection = new OracleConnection("OracleConnectionString");
        oracleConnection.Open();
        return oracleConnection;
    }
}

var query = "SELECT Id, Name FROM Service";
using (var dbConnection = GetChargingOracleDbConnection)
{
    return dbConnection.Query<MyModel>(query).ToList();
}

Upvotes: 4

Related Questions