你的位置:首页 > ASP.net教程

[ASP.net教程]如何使用ASP.NET Web API OData在Oracle中使用Entity Framework 6.x Code

 

环境: Visual Studio 2013 + .Net Framework 4.5.2

1.新建项目

2.安装OData,ODP.NET

安装的包:

 

下面是部分代码:

using System;using System.Collections.Generic;using System.Linq;using System.Web;namespace WebAppOdataEF.Models{  public class AUDIT_TEMPLATE  {    /// <summary>    /// Gets or sets the identifier.    /// 自增ID    /// </summary>    /// <value>The identifier.</value>    public decimal ID { get; set; }    /// <summary>    /// Gets or sets the audit template no.    /// 审批模板编码    /// </summary>    /// <value>The audit template no.</value>    public string AUDITTEMPLATENO { get; set; }    /// <summary>    /// Gets or sets the name of the audit template.    /// 审批模板名称    /// </summary>    /// <value>The name of the audit template.</value>    public string AUDITTEMPLATENAME { get; set; }    /// <summary>    /// Gets or sets the bill type no.    /// 单据类型    /// </summary>    /// <value>The bill type no.</value>    public string BILLTYPENO { get; set; }    /// <summary>    /// Gets or sets the remark.    /// 备注    /// </summary>    /// <value>The remark.</value>    public string REMARK { get; set; }    /// <summary>    /// Gets or sets the template status.    /// 状态1有效 0无效    /// </summary>    /// <value>The template status.</value>    public decimal TEMPLATESTATUS { get; set; }    /// <summary>    /// Gets or sets the company no.    /// 公司编码    /// </summary>    /// <value>The company no.</value>    public string COMPANYNO { get; set; }    /// <summary>    /// Gets or sets the create time.    /// 创建时间    /// </summary>    /// <value>The create time.</value>    public DateTime CREATETIME { get; set; }    /// <summary>    /// Gets or sets the create user no.    /// 创建人    /// </summary>    /// <value>The create user no.</value>    public string CREATEUSERNO { get; set; }    /// <summary>    /// Gets or sets the update time.    /// 更新时间    /// </summary>    /// <value>The update time.</value>    public DateTime UPDATETIME { get; set; }    /// <summary>    /// Gets or sets the update user no.    /// 更新人    /// </summary>    /// <value>The update user no.</value>    public string UPDATEUSERNO { get; set; }  }}

这个是实体代码,类名称和属性名称要和数据库中的表结构表名和字段名称一样,大小写都一样。

EF Context代码:

using System;using System.Collections.Generic;using System.Data.Entity;using System.Data.Entity.ModelConfiguration.Conventions;using System.Linq;using System.Web;namespace WebAppOdataEF.Models{  public class TemplatesContext : DbContext  {    static TemplatesContext()    {      Database.SetInitializer<TestsContext>(null);      //Database.SetInitializer(new CreateDatabaseIfNotExists<TestsContext>());      //Database.SetInitializer(new DropCreateDatabaseAlways<TestsContext>());      //Database.SetInitializer(new DropCreateDatabaseIfModelChanges<TestsContext>());    }    /// <summary>    /// Initializes a new instance of the <see cref="TemplatesContext"/> class.    /// </summary>    public TemplatesContext()      : base("name=SysBasicOracleDbContext")    {      this.Configuration.LazyLoadingEnabled = false;    }    /// <summary>    /// Gets or sets the templates.    /// </summary>    /// <value>The templates.</value>    public DbSet<AUDIT_TEMPLATE> Templates { get; set; }    protected override void OnModelCreating(DbModelBuilder modelBuilder)    {      modelBuilder.HasDefaultSchema("TEST");      modelBuilder.Conventions.Remove<OneToManyCascadeDeleteConvention>();      modelBuilder.Conventions.Remove<ManyToManyCascadeDeleteConvention>();      modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();    }  }}

  这里是你的用户,

"TEST" 一定要大写
modelBuilder.HasDefaultSchema("TEST");Controller部分代码:
using System;using System.Collections.Generic;using System.Linq;using System.Net;using System.Net.Http;using System.Web.Http;using System.Web.OData;using WebAppOdataEF.Models;namespace WebAppOdataEF.Controllers{  public class TemplatesController : ODataController  {    TemplatesContext db = new TemplatesContext();    private bool TestExists(int key)    {      return db.Templates.Any(p => p.ID == key);    }    protected override void Dispose(bool disposing)    {      db.Dispose();      base.Dispose(disposing);    }    [EnableQuery]    public IHttpActionResult Get()    {      return Ok(db.Templates);    }    [EnableQuery]    public SingleResult<AUDIT_TEMPLATE> Get([FromODataUri] int key)    {      IQueryable<AUDIT_TEMPLATE> result = db.Templates.Where(p => p.ID == key);      return SingleResult.Create(result);    }  }}

  

using System;using System.Collections.Generic;using System.Linq;using System.Net;using System.Net.Http;using System.Web.Http;using WebAppOdataEF.Models;using System.Data.Entity;using System.Data.Entity.Infrastructure;using System.Threading.Tasks;using System.Web.OData;namespace WebAppOdataEF.Controllers{  public class TestsController : ODataController  {    TestsContext db = new TestsContext();    private bool TestExists(int key)    {      return db.Tests.Any(p => p.ID == key);    }    protected override void Dispose(bool disposing)    {      db.Dispose();      base.Dispose(disposing);    }    [EnableQuery]    public IHttpActionResult Get()    {      return Ok(db.Tests);    }    [EnableQuery]    public SingleResult<TESTS> Get([FromODataUri] int key)    {      IQueryable<TESTS> result = db.Tests.Where(p => p.ID == key);      return SingleResult.Create(result);    }    public async Task<IHttpActionResult> Post(TESTS product)    {      if (!ModelState.IsValid)      {        return BadRequest(ModelState);      }      db.Tests.Add(product);      await db.SaveChangesAsync();      return Created(product);    }    public async Task<IHttpActionResult> Patch([FromODataUri] int key, Delta<TESTS> product)    {      if (!ModelState.IsValid)      {        return BadRequest(ModelState);      }      var entity = await db.Tests.FindAsync(key);      if (entity == null)      {        return NotFound();      }      product.Patch(entity);      try      {        await db.SaveChangesAsync();      }      catch (DbUpdateConcurrencyException)      {        if (!TestExists(key))        {          return NotFound();        }        else        {          throw;        }      }      return Updated(entity);    }    public async Task<IHttpActionResult> Put([FromODataUri] int key, TESTS update)    {      if (!ModelState.IsValid)      {        return BadRequest(ModelState);      }      if (key != update.ID)      {        return BadRequest();      }      db.Entry(update).State = EntityState.Modified;      try      {        await db.SaveChangesAsync();      }      catch (DbUpdateConcurrencyException)      {        if (!TestExists(key))        {          return NotFound();        }        else        {          throw;        }      }      return Updated(update);    }    public async Task<IHttpActionResult> Delete([FromODataUri] int key)    {      var product = await db.Tests.FindAsync(key);      if (product == null)      {        return NotFound();      }      db.Tests.Remove(product);      await db.SaveChangesAsync();      return StatusCode(HttpStatusCode.NoContent);    }  }}

  WebApiConfig代码:

using System;using System.Collections.Generic;using System.Linq;using System.Web.Http;using WebAppOdataEF.Models;using System.Web.OData.Builder;using System.Web.OData.Extensions;namespace WebAppOdataEF{  public static class WebApiConfig  {    public static void Register(HttpConfiguration config)    {      // Web API 配置和服务      ODataModelBuilder builder = new ODataConventionModelBuilder();      builder.EntitySet<TESTS>("Tests");      builder.EntitySet<TB_MENU>("TbMenus");      builder.EntitySet<AUDIT_TEMPLATE>("Templates");      config.MapODataServiceRoute(        routeName: "ODataRoute",        routePrefix: null,        model: builder.GetEdmModel());      // Web API 路由      //config.MapHttpAttributeRoutes();      //config.Routes.MapHttpRoute(      //  name: "DefaultApi",      //  routeTemplate: "api/{controller}/{id}",      //  defaults: new { id = RouteParameter.Optional }      //);    }  }}

  WebApiApplication代码:

using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Web.Http;using System.Web.Routing;namespace WebAppOdataEF{  public class WebApiApplication : System.Web.HttpApplication  {    protected void Application_Start()    {      GlobalConfiguration.Configure(WebApiConfig.Register);    }  }}

  WebConfig文件:

<?

<!--<connectionStrings>
<add name="TestsContext" providerName="Oracle.ManagedDataAccess.Client"
connectionString="User Id=system;Password=111111;Data Source=XE"/>
<add name="SysBasicOracleDbContext" providerName="Oracle.ManagedDataAccess.Client" connectionString="User Id=test;Password=test;Data Source=test" />
</connectionStrings>-->


<connectionStrings>
<add name="SysBasicOracleDbContext" connectionString="Data Source= (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
  (CONNECT_DATA =   (SERVER = DEDICATED)   (SERVICE_NAME = test)  ) );User ID=test;Password=test;Persist Security Info=True" providerName="Oracle.ManagedDataAccess.Client" /> </connectionStrings>  <system.web>  <compilation debug="true" targetFramework="4.5" />  <httpRuntime targetFramework="4.5" /> </system.web> <system.data>  <DbProviderFactories>   <remove invariant="Oracle.ManagedDataAccess.Client" />   <add name="ODP.NET, Managed Driver" invariant="Oracle.ManagedDataAccess.Client" description="Oracle Data Provider for .NET, Managed Driver"     type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />  </DbProviderFactories></system.data> <system.webServer>  <handlers>   <remove name="ExtensionlessUrlHandler-Integrated-4.0" />   <remove name="OPTIONSVerbHandler" />   <remove name="TRACEVerbHandler" />   <add name="ExtensionlessUrlHandler-Integrated-4.0" path="*." verb="*" type="System.Web.Handlers.TransferRequestHandler" preCondition="integratedMode,runtimeVersionv4.0" />  </handlers> </system.webServer> <runtime>  <assemblyBinding 

  

运行结果:

http://localhost:54577/Templates

http://localhost:54577/Templates(7)

http://localhost:54577/Templates?$count=true

http://localhost:54577/Templates?$count=true&$select=ID,REMARK

http://localhost:54577/Templates?$count=true&$select=ID,REMARK&$top=2&$skip=1

http://localhost:54577/Templates?$count=true&$select=ID,REMARK&$top=2&$skip=1&$orderby=ID%20desc

http://localhost:54577/Templates?$count=true&$filter=AUDITTEMPLATENO%20eq%20%27t5%27

请参考Odata.org官网:

基础操作:

http://www.odata.org/getting-started/basic-tutorial/#filter

高级操作:

http://www.odata.org/getting-started/advanced-tutorial/

Github地址:

http://odata.github.io/

https://github.com/OData/RESTier

 

 

参考文章:

二篇教程(英文版):

1.Using NuGet to Install and Configure Oracle Data Provider for .NET

2.Entity Framework Code First and Code First Migrations for Oracle Database

http://www.cnblogs.com/yjmyzz/p/how-to-use-code-first-in-oracle-with-entity-framework-6.html

http://www.cnblogs.com/shanyou/archive/2010/02/19/1669360.html

http://www.asp.net/web-api/overview/odata-support-in-aspnet-web-api/odata-v4/create-an-odata-v4-endpoint

http://www.odata.org/blog/how-to-use-web-api-odata-to-build-an-odata-v4-service-without-entity-framework/