Try .NET /elinq/
Powered by Try .NET

SQL Server INSERT

1) Basic INSERT example

In SQL, INSERT requires an ad-hoc specification of columns to insert to. There is a similar concept in C# - value tuple.

ELINQ defines an extension method - @using() that accepts a tuple. The returned object has several RowXXX() overloads to create VALUES parameters in a type-safe way.

var name = "2018 Summer Promotion";
var discount = 0.15M;
var startDate = "20180601";
var expiredDate = "20180901";

var rows = DbContext.Database.Execute((Promotions promo) =>
{
    var set = promo.@using((promo.PromotionName, promo.Discount, promo.StartDate, promo.ExpiredDate));
    INSERT().INTO(set);
    VALUES(set.Row((name, discount, DataTypes.DATE.Raw(startDate), DataTypes.DATE.Raw(expiredDate))));
});

Console.WriteLine($"{rows} rows affected");

Insert a row from an object:

Promotions newPromo = new Promotions()
{
    PromotionName = "2018 Summer Promotion",
    Discount = 0.15M,
    StartDate = new DateTime(2018, 06, 01),
    ExpiredDate = new DateTime(2018, 09, 01)
};

var rows = DbContext.Database.Execute((Promotions promo) =>
{
    var set = promo.@using((promo.PromotionName, promo.Discount, promo.StartDate, promo.ExpiredDate));
    INSERT().INTO(set);
    VALUES(set.RowFrom(newPromo));
});

Console.WriteLine($"{rows} rows affected");

We can also override properties from the passed object by passing additional parameters to the RowFrom() method. In the next example discount will be inserted with DEFAULT:

var rows = DbContext.Database.Execute((Promotions promo) =>
{
    var set = promo.@using((promo.PromotionName, promo.StartDate, promo.ExpiredDate, promo.Discount));
    INSERT().INTO(set);
    VALUES(set.RowFrom(newPromo, DEFAULT()));
});

Console.WriteLine($"{rows} rows affected");

2) Insert and return inserted values

var query = DbContext.Promotions.Query((Promotions promo) =>
{
    var set = promo.@using((promo.PromotionName, promo.Discount, promo.StartDate, promo.ExpiredDate));
    INSERT().INTO(set);
    var r = OUTPUT(INSERTED<Promotions>());
    VALUES(set.RowFrom(newPromo));

    return r;
});

foreach (var promo in query)
    Console.WriteLine((promo.PromotionId, promo.PromotionName, promo.Discount, promo.StartDate, promo.ExpiredDate));

< BACK | HOME