[ ] |
, - , , .
SQL- . , , Red gate, - , - .
migrations, Hibernate Linq, , code first, , , code first .
, XML- SQL- . , ...
DbSyncSample. .
USE [DbSyncSample]
GO
/****** Object: Table [dbo].[Orders] Script Date: 06/01/2017 10:37:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Orders](
[Id] [int] IDENTITY(1,1) NOT NULL,
[OrderNumber] [nvarchar](50) NULL,
[OrderTime] [datetime] NULL,
[TotalCost] [decimal](18, 2) NOT NULL,
CONSTRAINT [PK_Orders] 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]
GO
CREATE NONCLUSTERED INDEX [IX_Orders_OrderNumber] ON [dbo].[Orders]
(
[OrderNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Details] Script Date: 06/01/2017 10:37:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Details](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Descript] [nvarchar](150) NULL,
[OrderId] [int] NULL,
[Cost] [decimal](18, 2) NOT NULL,
CONSTRAINT [PK_Details] 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]
GO
/****** Object: Trigger [Details_Modify] Script Date: 06/01/2017 10:37:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[Details_Modify]
ON [dbo].[Details]
AFTER INSERT,UPDATE
AS
BEGIN
UPDATE Orders
SET TotalCost = s.Total
FROM (
SELECT i.OrderId OId, SUM(d.Cost) Total
FROM Details d
JOIN inserted i ON d.OrderId=i.OrderId
GROUP BY i.OrderId
) s
WHERE Id=s.OId
END
GO
/****** Object: Trigger [Details_Delete] Script Date: 06/01/2017 10:37:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[Details_Delete]
ON [dbo].[Details]
AFTER DELETE
AS
BEGIN
UPDATE Orders
SET TotalCost = s.Total
FROM (
SELECT i.OrderId OId, SUM(d.Cost) Total
FROM Details d
JOIN deleted i ON d.OrderId=i.OrderId
GROUP BY i.OrderId
) s
WHERE Id=s.OId
END
GO
/****** Object: Default [DF_Details_Cost] Script Date: 06/01/2017 10:37:43 ******/
ALTER TABLE [dbo].[Details] ADD CONSTRAINT [DF_Details_Cost] DEFAULT ((0)) FOR [Cost]
GO
/****** Object: Default [DF_Orders_TotalCost] Script Date: 06/01/2017 10:37:43 ******/
ALTER TABLE [dbo].[Orders] ADD CONSTRAINT [DF_Orders_TotalCost] DEFAULT ((0)) FOR [TotalCost]
GO
/****** Object: ForeignKey [FK_Details_Orders] Script Date: 06/01/2017 10:37:43 ******/
ALTER TABLE [dbo].[Details] WITH CHECK ADD CONSTRAINT [FK_Details_Orders] FOREIGN KEY([OrderId])
REFERENCES [dbo].[Orders] ([Id])
GO
ALTER TABLE [dbo].[Details] CHECK CONSTRAINT [FK_Details_Orders]
GO
. nuget- Shed.DbSync.
XML :
class Program
{
private const string OrigConnString = "data source=.;initial catalog=FiocoKb;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework";
static void Main(string[] args)
{
// XML
var db = new Shed.DbSync.DataBase(OrigConnString);
var xml = db.GetXml();
File.WriteAllText("DbStructure.xml", xml);
}
}
DbStructure.xml :
0
1
int
4
false
true
false
2
nvarchar
100
true
false
false
3
datetime
8
true
false
false
4
decimal
9
false
false
false
1
CLUSTERED
true
true
false
1
1
false
2
NONCLUSTERED
false
false
false
2
1
false
1
4
((0))
1
int
4
false
true
false
2
nvarchar
300
true
false
false
3
int
4
true
false
false
4
decimal
9
false
false
false
1
CLUSTERED
true
true
false
1
1
false
1
2137058649
1
3
1
NO_ACTION
NO_ACTION
4
((0))
CREATE TRIGGER [dbo].[Details_Modify]
ON dbo.Details
AFTER INSERT,UPDATE
AS
BEGIN
UPDATE Orders
SET TotalCost = s.Total
FROM (
SELECT i.OrderId OId, SUM(d.Cost) Total
FROM Details d
JOIN inserted i ON d.OrderId=i.OrderId
GROUP BY i.OrderId
) s
WHERE Id=s.OId
END
SQL_TRIGGER
CREATE TRIGGER [dbo].[Details_Delete]
ON dbo.Details
AFTER DELETE
AS
BEGIN
UPDATE Orders
SET TotalCost = s.Total
FROM (
SELECT i.OrderId OId, SUM(d.Cost) Total
FROM Details d
JOIN deleted i ON d.OrderId=i.OrderId
GROUP BY i.OrderId
) s
WHERE Id=s.OId
END
SQL_TRIGGER
XML. DbSyncSampleCopy, :
class Program
{
private const string OrigConnString = "data source=.;initial catalog=DbSyncSample;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework";
private const string TargetConnString = "data source=.;initial catalog=DbSyncSampleCopy;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework";
static void Main(string[] args)
{
// XML
var dborig = new Shed.DbSync.DataBase(OrigConnString);
var xml = dborig.GetXml();
File.WriteAllText("DbStructure.xml", xml);
// ,
// Shed.DbSync.DataBase.ClearDb(TargetConnString);
//
var dbcopy = Shed.DbSync.DataBase.CreateFromXml(xml);
dbcopy.UpdateDb(TargetConnString);
// :
// dborig.UpdateDb(TargetConnString);
// dbcopy XML
}
}
, DbSyncSampleCopy , . .
. Shed.DbSync.DataBase.ClearDb(string connString)
, / .
static void SyncDb()
{
//
Shed.DbSync.DataBase.Syncronize(OrigConnString,
@"Struct\DbStructure.xml", //
@"Struct\Logs", //
@"Struct\update_script.sql" // (.)
// ,
//
);
}
() Version XML. :
. !
