Reputation: 863
I am using Dapper (https://github.com/StackExchange/Dapper) in asp.net core myweb api project. I have a requirement to create a master record and insert a set of rows into a child table.
Ex. OrdersMaster table
create an order id in this table
OrderDetails table
ordermasterid
order items
How can I do this using Dapper? Please share some code snippets if possible.
Upvotes: 3
Views: 10479
Reputation: 16409
Using stored procedure as mentioned in other answer is good solution. My answer implements the same without Stored Procedure.
You have to use transaction. This way, either all the changes will be committed or rolled back. Code below assumes you are using Identity
as Primary Key. Please refer this question for discussion about @@IDENTITY
that I have used in below code.
Though code is not complete, I have put detailed comments to explain steps.
using (var connection = new SqlCeConnection("connection_string"))
{
connection.Open();
//Begin the transaction
using (var transaction = connection.BeginTransaction())
{
//Create and fill-up master table data
var paramMaster = new DynamicParameters();
paramMaster.Add("@XXX", ...);
....
//Insert record in master table. Pass transaction parameter to Dapper.
var affectedRows = connection.Execute("insert into OrdersMaster....", paramMaster, transaction: transaction);
//Get the Id newly created for master table record.
//If this is not an Identity, use different method here
newId = Convert.ToInt64(connection.ExecuteScalar<object>("SELECT @@IDENTITY", null, transaction: transaction));
//Create and fill-up detail table data
//Use suitable loop as you want to insert multiple records.
//for(......)
foreach(OrderItem item in orderItems)
{
var paramDetails = new DynamicParameters();
paramDetails.Add("@OrderMasterId", newId);
paramDetails.Add("@YYY", ...);
....
//Insert record in detail table. Pass transaction parameter to Dapper.
var affectedRows = connection.Execute("insert into OrderDetails....", paramDetails, transaction: transaction);
}
//Commit transaction
transaction.Commit();
}
}
Upvotes: 1
Reputation: 8665
I would implement the insert operations as a transactional stored procedure, and then call that from your .NET application.
You may need a table-valued type to pass in a list of data, like this:
CREATE TYPE List_Of_Items AS TABLE (
ItemID INT NOT NULL,
Quantity INT NOT NULL
)
The procedure might look like this
CREATE PROC Insert_Order_With_Details (
@CustomerId INT,
@Items List_Of_Items
) AS
BEGIN
BEGIN TRANSACTION
INSERT INTO OrdersMaster (CustomerId) VALUES @CustomerId
DECLARE @OrderID INT
SET @OrderID = SCOPE_IDENTITY() --last assigned id
INSERT INTO OrderDetails (OrderId, CustomerId, ItemId, Quantity)
SELECT @OrderID, @CustomerID, ItemID, Quantity
FROM @Items
COMMIT
END
Then in C#, I would suggest creating methods for creating your TVP. It is not as simple as you might like. This requires the using Microsoft.SqlServer.Server
and using Dapper.Tvp
.
//This is a shell to create any kind of TVP
private static void AddTableCore<T>(
this DynamicParametersTvp dp,
string tvpTypeName,
Func<T, SqlDataRecord> valueProjection,
IEnumerable<T> values,
string parameterTableName)
{
var tvp = values
.Select(valueProjection)
.ToList();
//If you pass a TVP with 0 rows to SQL server it will error, you must pass null instead.
if (!tvp.Any()) tvp = null;
dp.Add(new TableValueParameter(parameterTableName, tvpTypeName, tvp));
}
//This will create your specific Items TVP
public static void AddItemsTable(this DynamicParametersTvp dp, IEnumerable<Item> items, string parameterTableName = "Items")
{
var columns = new[]
{
new SqlMetaData("ItemID", SqlDbType.Int)
new SqlMetaData("Quantity", SqlDbType.Int)
};
var projection = new Func<Item, SqlDataRecord>(item =>
{
var record = new SqlDataRecord(columns);
record.SetInt32(0, item.Id);
record.SetInt32(1, item.Quantity);
return record;
});
AddTableCore(dp, "Items", projection, items, parameterTableName);
}
and then where you need to query you might do:
using (var cn = new SqlConnection(myConnectionString))
{
var p = new DynampicParametersTvp(new {
CustomerId = myCustomerId
});
p.AddItemsTable(items);
cn.Execute("Insert_Order_With_Details", p, commandType: CommandType.StoredProcedure);
}
The commandType
argument is super important. It defaults to plain SQL text and will error if you send the name of a proc.
If you want to put in multiple orders at once, you'll need to use table-valued parameters and the Dapper.Tvp package.
See this SO question Using Dapper.TVP TableValueParameter with other parameters as well as this documentation on TVP's from Microsoft https://learn.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine. I don't think all SQL vendors support TVPs.
Upvotes: 3