Dapper multi insert returns inserted objects

Question

Using Dapper I would like to implement a method that accepts IEnumberabletype objects User. Now Userit looks like this:

public class User
{
  public int UserId { get; internal set; }
  public DateTime DateCreated { get; internal set; }
  public DateTime DateChanged { get; internal set; }
  public string Username { get; set; }
}

The fact is that UserId, DateCreatedand DateChangedshould never be set through an object, therefore, a keyword internal. Instead, the database will populate these values.

Since objects therefore change as part of the insert operation, I want to return another object IEnumerableof type objects User, but this time with the corresponding properties populated.

I recently realized that I can let Dapper go through objects Userin the IEnumerablefollowing way:

public int Insert(IEnumerable<User> users)
{
  string sql = string.Format("INSERT INTO [User] (Username) VALUES (@Username)");
  return GetOpenConnection().Execute<User>(sql, users);
}

, foreach. , Execute .

, Query :

public IEnumerable<User> Insert(IEnumerable<User> users)
{
  string sql = string.Format("INSERT INTO [User] (Username) VALUES (@Username) SELECT * FROM [User] WHERE UserId = scope_identity()");
  return GetOpenConnection().Query<User>(sql, users);
}

InvalidOperationException " (, ..) ".

. ?

IEnumerable, Query ? , IDbTransaction Query , User , Query.

"" Dapper ?

Answer 1

Dapper.Net, Query

 connection.Query<Object>("your_query",your_list) 
 //connection.Query<Object>: use to select IEnumrable<object> from db
 //connection.QueryMultiple: use to execut multiple query at once then read result one by one 

var sql = 
@"
select * from Customers where CustomerId = @id
select * from Orders where CustomerId = @id
select * from Returns where CustomerId = @id";

using (var multi = connection.QueryMultiple(sql, new {id=selectedId}))
{
  var customer = multi.Read<Customer>().Single();
  var orders = multi.Read<Order>().ToList();
  var returns = multi.Read<Return>().ToList();
   ...
} 

Execute for multi insert update

Execute("your_query",your_list, your_transaction);

,

// **using transaction depend on your needs**

//

  string query = @"Insert Into _TableName ( _columns) 
                                  OUTPUT INSERTED.* 
                                values ( _parameters )"; //parameters should be same as object properties name to let dapper do correct mapping 

[OUTPUT INSERTED. *] , , [OUTPUT INSERTED.Id]. return only id

//

 for (int i = 0; i < youList.Count-1; i++)
                {
                    youList[i] = DbConnection.Query<object>(query, youList[i]).FirstOrDefault();
                } // for loop is better for preformance

// SqlBulkCopy