Insert complex objects from C# into SQL Server without Entity Framework and data tables

Question

How to insert complex objects from C# into SQL Server without using Entity Framework and data tables (I thought about Dapper.Contrib but it is only for entities).

I'm using stored procedures and Dapper for now, but I can insert only one object and also - only with dynamic parameters so it not what I looking for.

For example:

DynamicParameters p = new DynamicParameters();
p.Add("ID", user.UserInformation.ID);
p.Add("DELEGATORID", user.UserRole.DelegateID ?? string.Empty);
p.Add("APPROVER", user.UserRole.Approver);
p.Add("DELEGATOR", user.UserRole.IsDelegator);
p.Add("SEARCHTEXT", searchText);

and then insert. But I need to do 2 more inserts.

For example - I want to get 3 complex objects to insert in the stored procedure and then execute 3 stored procedure for each object

Thanks.

user_1838819 · Answer

Ideally, you'd use an ORM that takes care of this but one option is to use raw SQL strings in that you build the entire query as a string (or in several strings), parameterize it and execute it. A quick google gives and example here; https://codereview.stackexchange.com/questions/59863/transaction-handling-for-multiple-sql-statements. Important points of note are;

Values are parameterized
The statements execute as a transaction (and so will all get rolled back in event of an issue

user_7297700 · Answer

How to insert complex objects from C# into SQL Server without using Entity Framework and data tables

SQL Server can parse XML, and newer versions can also parse JSON. So the simplest way to insert shaped data into SQL Server without an ORM is to send an XML or JSON document and parse it on the server.

Insert complex objects from C# into SQL Server without Entity Framework and data tables

2 Answers