public static void CreateBD(DbContext context)
{
try
{
context.Database.CreateIfNotExists();
if (!context.Database.CompatibleWithModel(throwIfNoMetadata: true))
{
throw new Exception("Delete and Create DataBase");
}
}
catch
{
try
{
My.DeleteBD(context);
}
catch (Exception e)
{
using (var commande = new SqlCommand("ALTER DATABASE " + DataBaseName + " SET SINGLE_USER WITH ROLLBACK IMMEDIATE;DROP DATABASE " + DataBaseName + " ;", new SqlConnection(My.ConnectionString)))
{
commande.Connection.Open();
commande.ExecuteNonQuery();
}
}
context.Database.CreateIfNotExists();
}
}
public static void Execute()
{
// Create BD
using (var context = new EntityContext())
{
My.CreateBD(context);
}
// CLEAN
using (var context = new EntityContext())
{
context.EntitySimples.RemoveRange(context.EntitySimples);
context.SaveChanges();
}
// SEED
using (var context = new EntityContext())
{
for (int i = 0; i < 3; i++)
{
context.EntitySimples.Add(new EntitySimple {
ColumnInt = i
});
}
context.SaveChanges();
}
// TEST
using (var context = new EntityContext())
{
var connection = context.Database.Connection;
connection.Open();
using (var commande = connection.CreateCommand())
{
commande.CommandText = @"
if exists (select 1 from sys.procedures where name = 'PROC_Get_EntitySimple')
BEGIN
DROP PROCEDURE [dbo].[PROC_Get_EntitySimple]
END
" ;
commande.ExecuteNonQuery();
}
using (var commande = connection.CreateCommand())
{
commande.CommandText = @"
CREATE PROCEDURE [dbo].[PROC_Get_EntitySimple]
@ParameterID INT ,
@ParameterInt INT = NULL OUTPUT
AS
BEGIN
update EntitySimples
Set ColumnInt = @ParameterID ;
Set @ParameterInt = @ParameterID +1
END
" ;
commande.ExecuteNonQuery();
}
using (var commande = connection.CreateCommand())
{
commande.CommandText = @"
CREATE PROCEDURE [dbo].[PROC_Get_EntitySimple2]
@ParameterID INT ,
@ParameterInt INT = NULL OUTPUT
AS
BEGIN
update EntitySimples
Set ColumnInt = @ParameterID ;
Set @ParameterInt = @ParameterID +1
select * from EntitySimples
END
" ;
commande.ExecuteNonQuery();
}
}
// TEST
using (var context = new EntityContext())
{
var proc_Get_EntitySimple = new Proc_Get_EntitySimple()
{
ParameterID = 2
};
using (var tran = new TransactionScope())
{
context.Database.ExecuteStoredProcedure(proc_Get_EntitySimple);
}
var transaction = context.Database.BeginTransaction();
context.Database.ExecuteStoredProcedure(proc_Get_EntitySimple);
transaction.Rollback();
var list = context.EntitySimples.ToList();
}
// TEST
using (var context = new EntityContext())
{
var proc_Get_EntitySimple = new Proc_Get_EntitySimple2()
{
ParameterID = 2
};
using (var tran = new TransactionScope())
{
context.Database.ExecuteStoredProcedure <EntitySimple>(proc_Get_EntitySimple);
}
var transaction = context.Database.BeginTransaction();
context.Database.ExecuteStoredProcedure <EntitySimple>(proc_Get_EntitySimple);
transaction.Rollback();
var list = context.EntitySimples.ToList();
}
using (var context = new EntityContext())
{
var proc_Get_EntitySimple = new Proc_Get_EntitySimple()
{
ParameterID = 2
};
context.Database.ExecuteStoredProcedure(proc_Get_EntitySimple);
var list = context.EntitySimples.ToList();
}
}
public static void Execute()
{
// Create BD
using (var context = new EntityContext())
{
My.CreateBD(context);
}
// CLEAN
using (var context = new EntityContext())
{
context.EntitySimples.RemoveRange(context.EntitySimples);
context.SaveChanges();
}
// SEED
using (var context = new EntityContext())
{
for (int i = 0; i < 3; i++)
{
context.EntitySimples.Add(new EntitySimple {
ColumnInt = i
});
}
context.SaveChanges();
}
// TEST
using (var context = new EntityContext())
{
var connection = context.Database.Connection;
connection.Open();
using (var commande = connection.CreateCommand())
{
commande.CommandText = @"
if exists (select 1 from sys.procedures where name = 'PROC_Get_EntitySimple')
BEGIN
DROP PROCEDURE [dbo].[PROC_Get_EntitySimple]
END
" ;
commande.ExecuteNonQuery();
}
using (var commande = connection.CreateCommand())
{
commande.CommandText = @"
CREATE PROCEDURE [dbo].[PROC_Get_EntitySimple]
@ParameterID INT ,
@ParameterInt INT = NULL OUTPUT
AS
BEGIN
WAITFOR DELAY '00:00:05'
Select * from EntitySimples
Where ColumnInt = @ParameterID
Set @ParameterInt = @ParameterID +1
END
" ;
commande.ExecuteNonQuery();
}
}
// TEST
using (var context = new EntityContext())
{
var cts = new CancellationTokenSource();
var proc_Get_EntitySimple = new Proc_Get_EntitySimple()
{
ParameterID = 2
};
var entity = context.Database.ExecuteStoredProcedureAsync <EntitySimple>(proc_Get_EntitySimple, cts.Token);
//cts.Cancel();
entity.Wait();
var proc_Get_EntitySimple2 = new Proc_Get_EntitySimple()
{
ParameterID = 2
};
var entaity = context.Database.ExecuteStoredProcedureFirstOrDefaultAsync <EntitySimple>(proc_Get_EntitySimple2, cts.Token).Result;
}
using (var context = new EntityContext())
{
var cts = new CancellationTokenSource();
var proc_Get_EntitySimple = new Proc_Get_EntitySimple()
{
ParameterID = 2
};
var task = context.Database.ExecuteStoredProcedureAsync(proc_Get_EntitySimple, cts.Token);
// cts.Cancel();
task.Wait();
}
}
public static void Execute()
{
// Create BD
using (var context = new EntityContext())
{
My.CreateBD(context);
}
// CLEAN
using (var context = new EntityContext())
{
context.EntitySimples.RemoveRange(context.EntitySimples);
context.SaveChanges();
}
// SEED
using (var context = new EntityContext())
{
for (int i = 0; i < 3; i++)
{
context.EntitySimples.Add(new EntitySimple {
ColumnInt = i
});
}
context.SaveChanges();
}
// TEST
using (var context = new EntityContext())
{
var connection = context.Database.Connection;
connection.Open();
using (var commande = connection.CreateCommand())
{
commande.CommandText = @"
if exists (select 1 from sys.procedures where name = 'PROC_Get_EntitySimple')
BEGIN
DROP PROCEDURE [dbo].[PROC_Get_EntitySimple]
END
" ;
commande.ExecuteNonQuery();
}
using (var commande = connection.CreateCommand())
{
commande.CommandText = @"
CREATE PROCEDURE [dbo].[PROC_Get_EntitySimple]
@ParameterID INT ,
@ParameterDouble decimal(5,4) ,
@ParameterInt INT = NULL OUTPUT ,
@ParameterDoubleOutput decimal(5,4) = NULL OUTPUT
AS
BEGIN
update entitySimples
set ColumnDouble = @ParameterDouble
Select * from EntitySimples
Where ColumnInt = @ParameterID
Set @ParameterInt = @ParameterID +1
set @ParameterDoubleOutput = @ParameterDouble + 1
END
" ;
commande.ExecuteNonQuery();
}
}
// TEST
using (var context = new EntityContext())
{
try
{
var proc_Get_EntitySimple = new Proc_Get_EntitySimple()
{
ParameterID = 2, ParameterDouble = new decimal(35.809192)
};
var entity = context.Database.ExecuteStoredProcedureFirstOrDefault <EntitySimple>(proc_Get_EntitySimple);
var output = proc_Get_EntitySimple.ParameterInt;
}
catch (Exception e)
{
{
var proc_Get_EntitySimple = new Proc_Get_EntitySimple()
{
ParameterID = 2, ParameterDouble = new decimal(5.8001)
};
var entity = context.Database.ExecuteStoredProcedureFirstOrDefault <EntitySimple>(proc_Get_EntitySimple);
var output = proc_Get_EntitySimple.ParameterInt;
var output2 = proc_Get_EntitySimple.ParameterDoubleOutput;
}
//{
// var proc_Get_EntitySimple = new Proc_Get_EntitySimple() { ParameterID = 2 };
// var entity = context.Database.ExecuteStoredProcedureFirstOrDefault<EntitySimple>(proc_Get_EntitySimple);
// var output = proc_Get_EntitySimple.ParameterInt;
//}
}
var list = context.EntitySimples.ToList();
}
}