使用EF操作Oracle数据库小计

2023-07-28 22:26:22

1、建表

CREATE TABLE item.ORDERS(

   ORDERID NUMBER(20) CONSTRAINT PK_ORDERS PRIMARY KEY,

   ORDERNO VARCHAR2(20),

   STOREID NUMBER(20),

   STOREOWNERID NUMBER(20),

   CUSTOMERID NUMBER(20),

   ORDERTYPE NUMBER(3),

   ORDERSTATUS NUMBER(3)

   );

2、ID自增列设置

create sequence ITEM_ORDERS_SEQ

minvalue 1    -- 最小值=1

maxvalue 999999999999999999999999999  -- 指定最大值

-- 或nomaxvalue      -- 没有最大值

-- NOCYCLE;      -- 不循环

start with 1   -- 从1开始

increment by 1  -- 每次递增1

cache 20;  

create or replace trigger ITEM_ORDERS_TRI

  before insert ON item.orders   --表名

  for each row

declare

  nextid number;

begin

  IF :new.ORDERID IS NULL or :new.ORDERID=0 THEN --ORDERID为列名

    select ITEM_ORDERS_SEQ.nextval

    into nextid

    from sys.dual;

    :new.ORDERID:=nextid;

  end if;

end ITEM_ORDERS_TRI;

3、nuget引用包（先删除已有的包，再按1、2、3次序依次引入）

使用EF操作Oracle数据库小计

4、连接串配置

<?xml version="1.0" encoding="utf-8"?>

<configuration>

  <configSections>

    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />

    <section name="oracle.manageddataaccess.client" type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess, Version=4.122.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />

    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->

  </configSections>

  <entityFramework>

    <defaultConnectionFactory type="Oracle.ManagedDataAccess.EntityFramework.OracleConnectionFactory,Oracle.ManagedDataAccess.EntityFramework,Version=6.122.1.0,Culture=neutral,PublicKeyToken=89b483f429c47342" />

    <providers>

      <provider invariantName="Oracle.ManagedDataAccess.Client" type="Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices, Oracle.ManagedDataAccess.EntityFramework, Version=6.122.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />

      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />

    </providers>

  </entityFramework>

  <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.122.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />

    </DbProviderFactories>

  </system.data>

  <connectionStrings>

    <add name="OracleDbContext" providerName="Oracle.ManagedDataAccess.Client" connectionString="User Id=item;Password=yungou;Data Source=order_db" />

  </connectionStrings>

  <oracle.manageddataaccess.client>

    <version number="*">

      <dataSources>

        <dataSource alias="order_db" descriptor="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.96)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl)))" />

      </dataSources>

      <edmMappings>

        <edmMapping dataType="number">

          <add name="bool" precision="1" />

          <add name="byte" precision="3" />

          <add name="int16" precision="5" />

          <add name="int32" precision="10" />

          <add name="int64" precision="19" />

        </edmMapping>

      </edmMappings>

    </version>

  </oracle.manageddataaccess.client>

  <runtime>

    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">

      <dependentAssembly>

        <publisherPolicy apply="no" />

        <assemblyIdentity name="Oracle.ManagedDataAccess" publicKeyToken="89b483f429c47342" culture="neutral" />

        <bindingRedirect oldVersion="4.121.0.0 - 4.65535.65535.65535" newVersion="4.122.1.0" />

      </dependentAssembly>

    </assemblyBinding>

  </runtime>

</configuration>

5、映射管理

using System.Data.Entity;

using System.Data.Entity.ModelConfiguration.Conventions;

namespace EF.Oracle

{

    /// <summary>

    /// OracleDbContext

    /// </summary>

    public class OracleDbContext : DbContext

    {

        /// <summary>

        /// OracleDbContext

        /// </summary>

        public OracleDbContext()

            : base("OracleDbContext")

        {

        }

        public DbSet<Order> Orders { get; set; }

        /// <summary>

        /// OnModelCreating

        /// </summary>

        /// <param name="modelBuilder"></param>

        protected override void OnModelCreating(DbModelBuilder modelBuilder)

        {

            modelBuilder.HasDefaultSchema("ITEM");

            modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();

            modelBuilder.Conventions.Remove<DatabaseGeneratedAttributeConvention>();

            modelBuilder.Entity<Order>().ToTable("ORDERS");

            //自增列需要在数据库建序列+触发器配合生成

            modelBuilder.Entity<Order>().HasKey(o => o.OrderId);

            modelBuilder.Entity<Order>().Property(o => o.OrderId).HasColumnName("ORDERID");

            modelBuilder.Entity<Order>().Property(o => o.OrderNo).HasColumnName("ORDERNO");

            modelBuilder.Entity<Order>().Property(o => o.StoreId).HasColumnName("STOREID");

            modelBuilder.Entity<Order>().Property(o => o.StoreOwnerId).HasColumnName("STOREOWNERID");

            modelBuilder.Entity<Order>().Property(o => o.CustomerId).HasColumnName("CUSTOMERID");

            modelBuilder.Entity<Order>().Property(o => o.OrderType).HasColumnName("ORDERTYPE");

            modelBuilder.Entity<Order>().Property(o => o.OrderStatus).HasColumnName("ORDERSTATUS");

        }

    }

}

/*

 * 自增列实现SQL

 *

create sequence ITEM_ORDERS_SEQ

minvalue 1    -- 最小值=1

maxvalue 999999999999999999999999999  -- 指定最大值

-- 或nomaxvalue      -- 没有最大值

-- NOCYCLE;      -- 不循环

start with 1   -- 从1开始

increment by 1  -- 每次递增1

cache 20;  

create or replace trigger ITEM_ORDERS_TRI

  before insert ON item.orders   --表名

  for each row

declare

  nextid number;

begin

  IF :new.ORDERID IS NULL or :new.ORDERID=0 THEN --ORDERID为列名

    select ITEM_ORDERS_SEQ.nextval

    into nextid

    from sys.dual;

    :new.ORDERID:=nextid;

  end if;

end ITEM_ORDERS_TRI;

*/

6、实体模型

using System;

using System.Collections.Generic;

using System.ComponentModel.DataAnnotations;

using System.ComponentModel.DataAnnotations.Schema;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace EF.Oracle

{

    public class Order

    {

        public long OrderId { get; set; }

        public string OrderNo { get; set; }

        public long StoreId { get; set; }

        /// <summary>

        /// 店主Id

        /// </summary>

        public long StoreOwnerId { get; set; }

        /// <summary>

        /// 买家Id

        /// </summary>

        public long CustomerId { get; set; }

        /// <summary>

        /// 订单类型

        /// </summary>

        public int OrderType { get; set; }

        /// <summary>

        /// 订单状态

        /// </summary>

        public int OrderStatus { get; set; }

    }

}

7、调用

using System;

using System.Data.Entity;

using System.Data.Entity.Core.Common.CommandTrees;

using System.Data.Entity.Core.Metadata.Edm;

using System.Linq;

using System.Text;

namespace EF.Oracle

{

    class Program

    {

        static OracleDbContext dbContext = new OracleDbContext();

        static void Main(string[] args)

        {

            dbContext.Database.Log = Console.WriteLine; //ef生成sql日志跟踪 https://www.cnblogs.com/huangxincheng/p/5911448.html

            var q = (from p in dbContext.Orders where p.OrderId >

                select p).Any();

            Console.WriteLine(q.ToString());

            CreateOrder();

            QueryOrder();

            EditOrder();

            QueryOrder();

            Console.WriteLine("ok");

            Console.Write("Press any key to continue... ");

            Console.ReadLine();

        }

        private static void CreateOrder()

        {

            DeleteOrder();

            Order o = new Order

            {

                OrderNo = "",

                CustomerId = ,

                OrderStatus = ,

                OrderType = ,

                StoreId = ,

                StoreOwnerId =

            };

            dbContext.Orders.Add(o);

            dbContext.SaveChanges();

        }

        private static void EditOrder()

        {

            var order = dbContext.Orders.FirstOrDefault(o => o.OrderNo.Equals(""));

            if (order != null)

            {

                order.CustomerId = ;

                order.OrderType = ;

                dbContext.SaveChanges();

            }

        }

        private static void DeleteOrder()

        {

            var order = dbContext.Orders.FirstOrDefault(o => o.OrderNo.Equals(""));

            if (order != null)

            {

                dbContext.Orders.Remove(order);

                dbContext.SaveChanges();

            }

        }

        private static void QueryOrder()

        {

            var order = dbContext.Orders.FirstOrDefault(o => o.OrderNo.Equals(""));

            if (order != null)

            {

                Console.WriteLine(string.Format("{0}-{1}-{2}", order.OrderId, order.OrderNo, order.CustomerId));

            }

        }

    }

}

8、输出

使用EF操作Oracle数据库小计

上一篇：TCP 之 TCP首部


下一篇：【亲测】Java 接口自动化步骤