public static void AllOperatin(string connectionString)
{
string sql = $@"EXEC all_operation";
DirectDataManager ddmAll = new DirectDataManager(sql, connectionString);
ddmAll.ExecuteQuery();
}
/// <summary>
/// Удаление записей из Репорта, которых нет в основной таблицы
/// </summary>
/// <param name="conn"></param>
public static void DeleteEstimate(string connectionString)
{
string sql = $@"BEGIN TRANSACTION;
DELETE mSR
FROM SequoiaReport.dbo.Estimate AS mSR
WHERE mSR.SequoiaKaId NOT IN (
SELECT mSE.Id
FROM Sequoia.dbo.Estimate AS mSE)
COMMIT;";
DirectDataManager ddmCreate = new DirectDataManager(sql, connectionString);
ddmCreate.ExecuteQuery();
}
/// <summary>
/// Обновление записей в репорте, которые поменялись
/// </summary>
/// <param name="connectionString"></param>
public static void UpdateEstimate(string connectionString)
{
string sql = $@"BEGIN TRANSACTION;
UPDATE mSR
SET
mSR.EstimSum=mSE.EstimSum,
mSR.FactSum=mSE.FactSum,
mSR.TargetSum=mSE.TargetSum
FROM SequoiaReport.dbo.Estimate AS mSR,
Sequoia.dbo.Estimate AS mSE
WHERE mSR.SequoiaKaId=mSE.Id AND (
mSR.EstimSum != mSE.EstimSum OR
mSR.FactSum != mSE.FactSum OR
mSR.TargetSum != mSE.TargetSum)
COMMIT;";
DirectDataManager ddmCreate = new DirectDataManager(sql, connectionString);
ddmCreate.ExecuteQuery();
}
static void Main(string[] args)
{
string connectionStringSequoiaKA = "SequoiaKAContext";
string connectionStringSequioaReport = "SequoiaReportContext";
string sql = $@"SELECT
mEstim.Id,
mEstim.EstimSum,
mEstim.TargetSum,
mEstim.FactSum
--mEstim.Category_Id
FROM
dbo.Estimate AS mEstim
WHERE mEstim.Year_Id=67 AND mEstim.Client_Id=1 AND Category_Id=146";
DirectDataManager ddm = new DirectDataManager(sql, connectionStringSequoiaKA);
var Estimations = ddm.ToList <Estimate>().ToList();
bool ExistsTable = ExiststDataBase(connectionStringSequioaReport, "Estimate");
if (!ExistsTable)
{
string sqlCreateTable = $@" CREATE TABLE [dbo].[Estimate] (
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[SequoiaKaId] [bigint] NOT NULL,
[EstimSum] [decimal](18, 2) NOT NULL,
[TargetSum] [decimal](18, 2) NOT NULL,
[FactSum] [decimal](18, 2) NOT NULL);" ;
DirectDataManager ddmCreate = new DirectDataManager(sqlCreateTable, connectionStringSequioaReport);
ddmCreate.ExecuteQuery();
}
//DeleteEstimate(connectionStringSequioaReport);
//InsertIntoEstimate(connectionStringSequioaReport);
//UpdateEstimate(connectionStringSequioaReport);
AllOperatin(connectionStringSequioaReport);
//Console.ReadKey();
}
/// <summary>
/// Добавление записей, которых еще нет в репорте
/// </summary>
/// <param name="connectionString"></param>
public static void InsertIntoEstimate(string connectionString)
{
string sql = $@"BEGIN TRANSACTION;
INSERT INTO SequoiaReport.dbo.Estimate
([SequoiaKaId],
[EstimSum],
[TargetSum],
[FactSum])
SELECT mSE.Id,
mSE.EstimSum,
mSE.TargetSum,
mSE.FactSum
FROM Sequoia.dbo.Estimate AS mSE
WHERE NOT EXISTS (
SELECT 1
FROM SequoiaReport.dbo.Estimate AS mSR
WHERE mSE.Id=mSR.SequoiaKaId)
COMMIT;";
DirectDataManager ddmCreate = new DirectDataManager(sql, connectionString);
ddmCreate.ExecuteQuery();
}