在Dapper中处理Oracle数据库连接 [英] Handle Oracle Database Connection in Dapper
问题描述
我试图连接到Oracle数据库并尝试执行查询。
I am trying to connect to the Oracle Database and trying to execute a query.
下面是我的模型类
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类为
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; }
}
}
现在尝试使用Dapper创建Controller,现在问题出在大多数试图连接到SQL数据库的论坛中。我正在尝试访问Oracle DB并以JSON格式返回结果。因此,如果我使用Oracle.ManagedDataAccess.Client给出
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();
}
}
}
它抛出一个错误,说dbconn.Query不在上下文中,我还尝试了TestAPIContext.Init甚至抛出错误。谁能建议我如何处理Dapper与Oracle的连接。我是ASP.NET和创建服务的新手。
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.
推荐答案
您没有传递SQL。另外,不需要显式关闭。您可以将代码包装在use中,通过SqlConnection.Dispose()调用SqlConnection.Close()。
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().
也许这是一个拼写错误,但重新运行应该被返回。应该从app.config和硬编码中读取Connectionstring,我也考虑将'id'作为参数。
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);
}
这篇关于在Dapper中处理Oracle数据库连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!