public void CreateQueryUserSpotDealsStoredProcedure(OptionDbCtx db) { string sql = @"if exists (select * from sys.objects where object_id=OBJECT_ID(N'QueryUserSpotDeals') and type='P') drop procedure QueryUserSpotDeals execute('create procedure QueryUserSpotDeals @name nvarchar(100), @take int, @skip int as begin select top (@take) * from( select ROW_NUMBER() over(order by (a.Id) desc ) as Rid, a.MainId as MainOrderId, b.ReportCount as ReportCount, b.Direction as Direction, b.CoinId as OrderPolicy, b.OrderTime as OrderTime, b.Count as Count, b.Price as Price, b.[State] as [State], a.[When] as DealTime, a.[Count] as DealCount, a.Price as DealPrice from SpotDeals a left join SpotOrders b on a.MainId=b.Id where a.MainTraderName=@name union select ROW_NUMBER() over(order by (a.Id) desc ) as Rid, a.SlaveId as MainOrderId, c.ReportCount as ReportCount, c.Direction as Direction, c.CoinId as OrderPolicy, c.OrderTime as OrderTime, c.Count as Count, c.Price as Price, c.[State] as [State], a.[When] as DealTime, a.[Count] as DealCount, a.Price as DealPrice from SpotDeals a left join SpotOrders c on a.SlaveId =c.Id where a.SlaveTraderId=@name ) d where d.Rid>@skip end')"; db.Database.ExecuteSqlCommand(sql); }
public void CreateTempSpotOrder(OptionDbCtx context) { var conn = context.Database.Connection.Database; var sql1 = "use [" + conn + "] "; var sql2 = @" SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON if not exists (select * from dbo.sysobjects where id = object_id(N'TempSpotOrders') and type='U') begin CREATE TABLE [dbo].[TempSpotOrders]( [Id] [int] NOT NULL, [TraderId] [int] NOT NULL, [CoinId] [int] NOT NULL, [Direction] [int] NOT NULL, [ReportCount] [decimal](18, 2) NOT NULL, [Price] [decimal](18, 2) NOT NULL, [Count] [decimal](18, 2) NOT NULL, [DoneCount] [decimal](18, 2) NOT NULL, [TotalDoneCount] [decimal](18, 2) NOT NULL, [OrderTime] [datetime] NOT NULL, [State] [int] NOT NULL, [RequestStatus] [int] NOT NULL, [DonePrice] [decimal](18, 2) NOT NULL, [IsBySystem] [bit] NOT NULL, [TotalDoneSum] [decimal](18, 2) NOT NULL, [Detail] [nvarchar](max) NULL, [OrderPolicy] [int] NOT NULL, CONSTRAINT [PK_dbo.TempSpotOrders] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] ALTER TABLE [dbo].[TempSpotOrders] WITH CHECK ADD CONSTRAINT [FK_dbo.TempSpotOrders.Coins_CoinId] FOREIGN KEY([CoinId]) REFERENCES [dbo].[Coins] ([Id]) ON DELETE CASCADE ALTER TABLE [dbo].[TempSpotOrders] CHECK CONSTRAINT [FK_dbo.TempSpotOrders.Coins_CoinId] ALTER TABLE [dbo].[TempSpotOrders] WITH CHECK ADD CONSTRAINT [FK_dbo.TempSpotOrders.Traders_TraderId] FOREIGN KEY([TraderId]) REFERENCES [dbo].[Traders] ([Id]) ON DELETE CASCADE ALTER TABLE [dbo].[TempSpotOrders] CHECK CONSTRAINT [FK_dbo.TempSpotOrders.Traders_TraderId] end "; var sql = sql1 + sql2; context.Database.ExecuteSqlCommand(sql); }
public void CreateQueryUserOrderDealsStoredProcedure(OptionDbCtx db) { String sql = @"if exists (select * from sys.objects where object_id=OBJECT_ID(N'QueryUserOrderDeals') and type='P') drop procedure QueryUserOrderDeals execute('create procedure QueryUserOrderDeals @name nvarchar(100), @take int, @skip int as begin select top (@take) * from( select row_number() over(order by d.DealTime desc) as Rid,* from( select a.MainOrderId as MainOrderId, b.ReportCount as ReportCount, b.Direction as Direction, b.OrderPolicy as OrderPolicy, b.OrderTime as OrderTime, b.Count as Count, b.Price as Price, b.[State]as [State], b.OrderType as [OrderType], a.[When] as DealTime, a.[Count] as DealCount, a.Price as DealPrice, con.Code as Code, con.Name as Name from Deals a left join Orders b on a.MainOrderId=b.Id left join Contracts con on a.ContractId =con.Id where a.MainName=@name union select a.SlaveOrderId as MainOrderId, c.ReportCount as ReportCount, c.Direction as Direction, c.OrderPolicy as OrderPolicy, c.OrderTime as OrderTime, c.Count as Count, c.Price as Price, c.[State] as [State], c.OrderType as [OrderType], a.[When] as DealTime, a.[Count] as DealCount, a.Price as DealPrice, con.Code as Code, con.Name as Name from Deals a left join Orders c on a.SlaveOrderId =c.Id left join Contracts con on a.ContractId =con.Id where a.SlaveName=@name ) d ) e where e.Rid>@skip end')"; db.Database.ExecuteSqlCommand(sql); }