There is a nice feature in ADO.NET that allows you to send multiple SQL statements to database in one roundtrip and receive results for all statements:

    var command = new SqlCommand(&quot;SELECT count(*) FROM TableA; SELECT count(*) FROM TableB;&quot;, connection);
    
    using(var reader = command.ExecuteReader())
    {
        reader.Read();
        resultA = reader.GetInt32(0);
        reader.NextResult();
        reader.Read();
        resultB = reader.GetInt32(0);
    }

Is there a similar feature in Dapper.NET?

Multiple SQL statements in one roundtrip using Dapper.NET

I am trying Dapper ORM and I am querying a a Posts table.

But I would like to get paged results ...

1 - How can I do this? Isn&#39;t there a helper for this?

2 - Can Dapper Query return an IQueryable?

Thank You,
Miguel


Dapper. Paging

Anyone know why Dapper returns an int from `.Execute(...)` ? 

I can&#39;t find this documented anywhere.


Why does Dapper&#39;s .Execute(...) return an int?

Dapper dot net has a `buffer` parameter (a bool), but as far as I can tell the *only* thing it does is cast the result to a list before returning it.

As per the [documentation][1]:

&gt; Dapper&#39;s default behavior is to execute your sql and buffer the entire
&gt; reader on return. This is ideal in most cases as it minimizes shared
&gt; locks in the db and cuts down on db network time.
&gt; 
&gt; However when executing huge queries you may need to minimize memory
&gt; footprint and only load objects as needed. To do so pass, buffered:
&gt; false into the Query method.

I&#39;m not sure how casting the result to a list accomplishes this. Am I missing something? My only idea is that it is supposed to set the `CommandBehavior` for the `ExecuteReader` to `CommandBehavior.SequentialAccess` (but it doesn&#39;t).

  [1]: https://github.com/StackExchange/Dapper#buffered-vs-unbuffered-readers

What does the buffered parameter do in Dapper dot net?

I just started playing around with Dapper. So far i love it. Does dapper not work with `TransactionScope`? I noticed that even if i never call `TransactionScope.Complete` then my changes are still committed to the database. If TransactionScope isn&#39;t supported now is there any plans in the future to support it? If not then you have to use traditional transaction management (System.Transactions.Transaction)? 

Update: I just talked to Sam over Twitter. It should work. I&#39;ll update it tomorrow morning (at work) with the details to see if anyone can figure out why my changes were still being committed to the db even when i never called complete.

Dapper &amp; TransactionScope?

What is the best way to write a query with IN clause using Dapper ORM when the list of values for the IN clause is coming from business logic? For example let&#39;s say I have a query:

&lt;!-- language-all: lang-sql --&gt;

    SELECT * 
      FROM SomeTable 
     WHERE id IN (commaSeparatedListOfIDs)

The `commaSeparatedListOfIDs` is being passed in from business logic and it can be any type of `IEnumerable(of Integer)`. How would I construct a query in this case? Do I have to do what I&#39;ve been doing so far which is basically string concatenation or is there some sort of advanced parameter mapping technique that I&#39;m not aware of?

SELECT * FROM X WHERE id IN (...) with Dapper ORM

I am interested in using Dapper - but from what I can tell it only supports Query and Execute.  I do not see that Dapper includes a way of Inserting and Updating objects.

Given that our project (most projects?) need to do inserts and updates, what is the best practice for doing Inserts and Updates alongside dapper?

Preferably we would not have to resort to the ADO.NET method of parameter building, etc.

The best answer I can come up with at this point is to use LinqToSQL for inserts and updates.  Is there a better answer?

Performing Inserts and Updates with Dapper

I am very impressed with the results of [Dapper Micro ORM][1] for stackoverflow.com. I am considering it for my new project and but I have one concern about that some times my project requires to have Stored Procedure and I have search a lot on web but not found anything with stored procedure. So is there any way to have Dapper work with a stored procedure?

Please let me know if it is possible otherwise I have to extend it in my way.


  [1]: https://github.com/StackExchange/dapper-dot-net

Is there a way to call a stored procedure with Dapper?

How do I perform an insert to database and return inserted identity with Dapper?

I&#39;ve tried something like this:

    string sql = &quot;DECLARE @ID int; &quot; +
                 &quot;INSERT INTO [MyTable] ([Stuff]) VALUES (@Stuff); &quot; +
                 &quot;SELECT @ID = SCOPE_IDENTITY()&quot;;

    var id = connection.Query&lt;int&gt;(sql, new { Stuff = mystuff}).First();

But it did&#39;t work.

@Marc Gravell thanks, for reply.
I&#39;ve tried your solution but, still same exception trace is below
    
    System.InvalidCastException: Specified cast is not valid

    at Dapper.SqlMapper.&lt;QueryInternal&gt;d__a`1.MoveNext() in (snip)\Dapper\SqlMapper.cs:line 610
    at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
    at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
    at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in (snip)\Dapper\SqlMapper.cs:line 538
    at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param) in (snip)\Dapper\SqlMapper.cs:line 456


How do I perform an insert and return inserted identity with Dapper?

I am new to the Dapper micro ORM. So far I am able to use it for simple ORM related stuff but I am not able to map the database column names with the class properties.

For example, I have the following database table:

    Table Name: Person
    person_id  int
    first_name varchar(50)
    last_name  varchar(50)

and I have a class called Person:
    
    public class Person 
    {
        public int PersonId { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

Please note that my column names in the table are different from the property name of the class to which I am trying to map the data which I got from the query result.

    var sql = @&quot;select top 1 PersonId,FirstName,LastName from Person&quot;;
    using (var conn = ConnectionFactory.GetConnection())
    {
        var person = conn.Query&lt;Person&gt;(sql).ToList();
        return person;
    }


The above code won&#39;t work as the column names don&#39;t match the object&#39;s (Person) properties. In this scenario, is there anything i can do in Dapper to manually map (e.g `person_id =&gt; PersonId`) the column names with object properties?


Manually map column names with class properties

I&#39;m currently using Entity Framework for my db access but want to have a look at Dapper. I have classes like this:

    public class Course{
       public string Title{get;set;}
       public IList&lt;Location&gt; Locations {get;set;}
       ...
    }

    public class Location{
       public string Name {get;set;}
       ...
    }

So one course can be taught at several locations. Entity Framework does the mapping for me so my Course object is populated with a list of locations. How would I go about this with Dapper, is it even possible or do I have to do it in several query steps?

How do I map lists of nested objects with Dapper

I would like to run multiple insert statements on multiple tables. I am using dapper.net. I don&#39;t see any way to handle transactions with dapper.net.

Please share your ideas on how to use transactions with dapper.net.


How to use transactions with dapper.net?

I&#39;m trying to use the multimapping feature of Dapper to return a list of ProductItems and associated Customers.  

```c#
[Table(&quot;Product&quot;)]
public class ProductItem
{
    public decimal ProductID { get; set; }        
    public string ProductName { get; set; }
    public string AccountOpened { get; set; }
    public Customer Customer { get; set; }
} 

public class Customer
{
    public decimal CustomerId { get; set; }
    public string CustomerName { get; set; }
}
```

My Dapper code:

```c#
var sql = @&quot;select * from Product p 
            inner join Customer c on p.CustomerId = c.CustomerId 
            order by p.ProductName&quot;;

var data = con.Query&lt;ProductItem, Customer, ProductItem&gt;(
    sql,
    (productItem, customer) =&gt; {
        productItem.Customer = customer;
        return productItem;
    },
    splitOn: &quot;CustomerId,CustomerName&quot;
);
```

This works fine, but I seem to have to add the complete column list to the &quot;splitOn&quot; parameter to return all the customers&#39; properties.  If I don&#39;t add &quot;CustomerName&quot;, it returns null.  Am I misunderstanding the core functionality of the multimapping feature? I don&#39;t want to have to add a complete list of column names each time. 

Correct use of multimapping in Dapper

I&#39;m trying to run SQL backups through a stored procedure through Dapper (the rest of my app uses Dapper so I&#39;d prefer to keep this portion running through it as well). It works just fine until the CommandTimeout kicks in.

    using (var c = SqlConnection(connstring))
    {
        c.Open();
        var p = new DynamicParameters();
        // fill out p

        c.Execute(&quot;xp_backup_database&quot;, p, commandType: CommandType.StoredProcedure);
    }

The only CommandTimeout setting I know of is in SqlCommand. Is there a way to set this via Dapper?

Adjusting CommandTimeout in Dapper.NET?

I&#39;ve been playing with Dapper, but I&#39;m not sure of the best way to handle the database connection.  

Most examples show the connection object being created in the example class, or even in each method.  But it feels wrong to me to reference a connection string in every clss, even if it&#39;s pulling from the web.config.  

My experience has been with using a `DbDataContext` or `DbContext` with Linq to SQL or Entity Framework, so this is new to me.  

How do I structure my web apps when using Dapper as my Data Access strategy?

How do I handle Database Connections with Dapper in .NET?

Using [tag:dapper], how can I insert a `C# List` to database.  Previously without **dapper** I used the below code to ***insert the List values to database***.

    try
	{                
		connection.Open();

		for (int i = 0; i &lt; processList.Count; i++)
		{
			string processQuery = &quot;INSERT INTO PROCESS_LOGS VALUES (@Id, @st_Time, @ed_Time, @td_Time)&quot;;
			command = new SqlCommand(processQuery, connection);
			command.Parameters.Add(&quot;Id&quot;, SqlDbType.Int).Value = processList[i].ID;
			command.Parameters.Add(&quot;st_Time&quot;, SqlDbType.DateTime).Value = processList[i].ST_TIME;
			command.Parameters.Add(&quot;ed_Time&quot;, SqlDbType.DateTime).Value = processList[i].ED_TIME;
			command.Parameters.Add(&quot;td_Time&quot;, SqlDbType.DateTime2).Value = processList[i].TD_TIME;
			dataReader.Close();
			dataReader = command.ExecuteReader();
		}

		connection.Close();
	}
    catch (SqlException ex)
	{
		//--Handle Exception
	}

I&#39;m familiar with fetching the data using **dapper** but this is my first try using **insert query**.  

I tried the below code, using `Exceute` linked to query but stuck up with looping;  I think using dapper tool, there is no need for looping statement.

    connection.Execute(processQuery ... );

**EDIT:**

    class ProcessLog
        {
            public int ID { get; set; }
            public DateTime ST_TIME { get; set; }
            public DateTime ED_TIME { get; set; }
            public DateTime TD_TIME { get; set; }
            public string frequency { get; set; }
        }

 

Please advice on this.  **FYI:** I&#39;m using `SQL Server 2008`.




How to insert a C# List to database using Dapper.NET

I have a dictionary of values Eg &quot;Name&quot;: &quot;Alex&quot;

Is there a way to pass this to Dapper as arguments for a query?

Here is an example showing what I want to do.

    IDictionary&lt;string, string&gt; args = GetArgsFromSomewhere();
    string query = &quot;select * from people where Name = @Name&quot;;
    var stuff = connection.Query&lt;ExtractionRecord&gt;(query, args);

How to create arguments for a Dapper query dynamically

Is there any way to use Dapper.NET with stored procs that return multiple result sets?

In my case, the first result set is a single row with a single column; if it&#39;s `0` then the call was successful, and the second result set will contain that actual rows/columns of data. (and if it was non-zero, an error occured and no second result set will be provided)

Any chance to handle this with Dapper.NET? So far, I&#39;m only ever getting back that single `0` - but nothing more.

**Update:** OK, it works fine - as long as the result set no. 2 is a single entity:

    Dapper.SqlMapper.GridReader reader = 
        _conn.QueryMultiple(&quot;sprocname&quot;, dynParams, 
        commandType: CommandType.StoredProcedure);

    int status = reader.Read&lt;int&gt;().FirstOrDefault();
    MyEntityType resultObj = reader.Read&lt;MyEntityType&gt;().FirstOrDefault();

Now, I have **yet another** requirement.

Dapper&#39;s multi-mapping (splitting up a single row returned from SQL Server into two separate entities) for that second result set doesn&#39;t seem to be supported as of yet (at least there doesn&#39;t seem to be an overload of `.Read&lt;T&gt;` that can handle multi-mapping).

How can I get split that row into two entities?

Dapper.NET and stored proc with multiple result sets

I&#39;ve written this code to project one to many relation but it&#39;s not working:

    using (var connection = new SqlConnection(connectionString))
    {
       connection.Open();
    
       IEnumerable&lt;Store&gt; stores = connection.Query&lt;Store, IEnumerable&lt;Employee&gt;, Store&gt;
                            (@&quot;Select Stores.Id as StoreId, Stores.Name, 
                                      Employees.Id as EmployeeId, Employees.FirstName,
                                      Employees.LastName, Employees.StoreId 
                               from Store Stores 
                               INNER JOIN Employee Employees ON Stores.Id = Employees.StoreId&quot;,
                            (a, s) =&gt; { a.Employees = s; return a; }, 
                            splitOn: &quot;EmployeeId&quot;);
    
       foreach (var store in stores)
       {
           Console.WriteLine(store.Name);
       }
    }

Can anybody spot the mistake?

**EDIT:**

These are my entities:

 

    public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public double Price { get; set; }
        public IList&lt;Store&gt; Stores { get; set; }

        public Product()
        {
            Stores = new List&lt;Store&gt;();
        }
    }

    public class Store
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public IEnumerable&lt;Product&gt; Products { get; set; }
        public IEnumerable&lt;Employee&gt; Employees { get; set; }

        public Store()
        {
            Products = new List&lt;Product&gt;();
            Employees = new List&lt;Employee&gt;();
        }
    }

**EDIT:**

I change the query to:

    IEnumerable&lt;Store&gt; stores = connection.Query&lt;Store, List&lt;Employee&gt;, Store&gt;
            (@&quot;Select Stores.Id as StoreId ,Stores.Name,Employees.Id as EmployeeId,
               Employees.FirstName,Employees.LastName,Employees.StoreId 
               from Store Stores INNER JOIN Employee Employees 
               ON Stores.Id = Employees.StoreId&quot;,
             (a, s) =&gt; { a.Employees = s; return a; }, splitOn: &quot;EmployeeId&quot;);

and I get rid of exceptions! However, Employees are not mapped at all. I am still not sure what problem it had with `IEnumerable&lt;Employee&gt;` in first query.





How do I write one to many query in Dapper.Net?

I&#39;ve been playing around with this for a bit, because it seems like it feels a lot like the [documented posts/users example][1], but its slightly different and isn&#39;t working for me.

Assuming the following simplified setup (a contact has multiple phone numbers):


	public class Contact
	{
		public int ContactID { get; set; }
		public string ContactName { get; set; }
		public IEnumerable&lt;Phone&gt; Phones { get; set; }
	}

	public class Phone
	{
		public int PhoneId { get; set; }
		public int ContactID { get; set; } // foreign key
		public string Number { get; set; }
		public string Type { get; set; }
		public bool IsActive { get; set; }
	}


I&#39;d love to end up with something that returns a Contact with multiple Phone objects.  That way, if I had 2 contacts, with 2 phones each, my SQL would return a join of those as a result set with 4 total rows.  Then Dapper would pop out 2 contact objects with two phones each.

Here is the SQL in the stored procedure:

    SELECT *
	FROM Contacts
		LEFT OUTER JOIN Phones ON Phones.ReferenceId=Contacts.ReferenceId
	WHERE clientid=1


I tried this, but ended up with 4 Tuples (which is OK, but not what I was hoping for... it just means I still have to re-normalize the result):


    var x = cn.Query&lt;Contact, Phone, Tuple&lt;Contact, Phone&gt;&gt;(&quot;sproc_Contacts_SelectByClient&quot;,
                                  (co, ph) =&gt; Tuple.Create(co, ph), 
                                              splitOn: &quot;PhoneId&quot;, param: p, 
                                              commandType: CommandType.StoredProcedure);


and when I try another method (below), I get an exception of &quot;Unable to cast object of type &#39;System.Int32&#39; to type &#39;System.Collections.Generic.IEnumerable`1[Phone]&#39;.&quot;


    var x = cn.Query&lt;Contact, IEnumerable&lt;Phone&gt;, Contact&gt;(&quot;sproc_Contacts_SelectByClient&quot;,
                                   (co, ph) =&gt; { co.Phones = ph; return co; }, 
                                                 splitOn: &quot;PhoneId&quot;, param: p,
                                                 commandType: CommandType.StoredProcedure);


Am I just doing something wrong? It seems just like the posts/owner example, except that I&#39;m going from the parent to the child instead of the child to the parent.


Thanks in advance


  [1]: http://code.google.com/p/dapper-dot-net/

Multi-Mapper to create object hierarchy

Does anyone know if is possible to pass [table-valued parameter](http://msdn.microsoft.com/en-us/library/bb675163.aspx) data to a stored procedure with Dapper? 





Does Dapper support SQL 2008 Table-Valued Parameters?

Using Dapper-dot-net...


The following yields no results in the data object:

    var data = conn.Query(@&quot;
        select top 25 
        Term as Label, 
        Type, 
        ID 
        from SearchTerms 
        WHERE Term like &#39;%@T%&#39;&quot;, 
        new { T = (string)term });

However, when I just use a regular String Format like:


    string QueryString = String.Format(&quot;select top 25 Term as Label, Type, ID from SearchTerms WHERE Term like &#39;%{0}%&#39;&quot;, term);
    var data = conn.Query(QueryString);

I get 25 rows back in the collection.  Is Dapper not correctly parsing the end of the parameter `@T`?

Does Dapper support the like operator?

If I have a simple query such as:

    string sql = &quot;SELECT UniqueString, ID  FROM Table&quot;;

and I want to map it to a dictionary object such as:

    Dictionary&lt;string, int&gt; myDictionary = new Dictionary&lt;string, int&gt;();      

How would I do this with Dapper?

I assume it is something like:

    myDictionary = conn.Query&lt;string, int&gt;(sql, new {  }).ToDictionary();

But can&#39;t figure out the proper syntax.




How to map to a Dictionary object from database results using Dapper Dot Net?

After reading [this article][1] I decided to take a closer look at the way I was using Dapper.

I ran this code on an empty database

	var members = new List&lt;Member&gt;();
	for (int i = 0; i &lt; 50000; i++)
	{
		members.Add(new Member()
		{
			Username = i.toString(),
			IsActive = true
		});
	}

	using (var scope = new TransactionScope())
	{
		connection.Execute(@&quot;
	insert Member(Username, IsActive)
	values(@Username, @IsActive)&quot;, members);

		scope.Complete();
	}

it took about 20 seconds. That&#39;s 2500 inserts/second. Not bad, but not great either considering the blog was achieving 45k inserts/second. Is there a more efficient way to do this in Dapper?

Also, as a side note, running this code through the Visual Studio debugger took **over 3 minutes!** I figured the debugger would slow it down a little, but I was really surprised to see that much.


**UPDATE**

So this

	using (var scope = new TransactionScope())
	{
		connection.Execute(@&quot;
	insert Member(Username, IsActive)
	values(@Username, @IsActive)&quot;, members);

		scope.Complete();
	}

and this

		connection.Execute(@&quot;
	insert Member(Username, IsActive)
	values(@Username, @IsActive)&quot;, members);

both took 20 seconds.

But this took 4 seconds!

    SqlTransaction trans = connection.BeginTransaction();
    
    connection.Execute(@&quot;
    insert Member(Username, IsActive)
    values(@Username, @IsActive)&quot;, members, transaction: trans);
    
    trans.Commit();


  [1]: https://www.gamasutra.com/view/news/170502/Indepth_SQL_Server__High_performance_inserts.php

Bulk inserts taking longer than expected using Dapper

Can someone please explain the difference between **Dapper.Rainbow** vs. **Dapper.Contrib**?

I mean when do you use SqlMapperExtensions.cs of Dapper.Contrib and when should you use Dapper.Rainbow?

Dapper.Rainbow VS Dapper.Contrib

Currently, I am trying to use Dapper ORM with Unit Of Work + Repository Pattern.

I want to use Unit of Work as opposed to a simple dapper Repository due to the fact that my insert and updates require a degree of transaction processing. I have been unable to find any useful examples as most seem to use Entity Framework and have leakage issue within the Unit of Work.

Could someone please point me in the right direction?

How to implement Unit Of Work pattern with Dapper?

I&#39;m using Dapper to map my entities to SQL Server CE. If I save a `DateTime` with `Kind=Utc`, when I read it back I get a `DateTime` with `Kind=Unspecified`, which leads to all kind of problems.


Example:

    var f = new Foo { Id = 42, ModificationDate = DateTime.UtcNow };
    Console.WriteLine(&quot;{0} ({1})&quot;, f.ModificationDate, f.ModificationDate.Kind);
    connection.Execute(&quot;insert into Foo(Id, ModificationDate) values(@Id, @ModificationDate)&quot;, f);
    var f2 = connection.Query&lt;Foo&gt;(&quot;select * from Foo where Id = @Id&quot;, f).Single();
    Console.WriteLine(&quot;{0} ({1})&quot;, f2.ModificationDate, f2.ModificationDate.Kind);

This code gives the following output:

    20/09/2012 10:04:16 (Utc)
    20/09/2012 10:04:16 (Unspecified)


I know I should be using a `DateTimeOffset`, but unfortunately SQL CE has no support for this type.

Is there a workaround? Can I tell Dapper to assume that all dates have `DateTimeKind.Utc`? And more generally, what are my options to customize the mapping?

---

EDIT: My current workaround is to patch the dates after Dapper has materialized the result, but it kind of smells...

    var results = _connection.Query&lt;Foo&gt;(sql, param).Select(PatchDate);

    ...

    static Foo PatchDate(Foo f)
    {
        if (f.ModificationDate.Kind == DateTimeKind.Unspecified)
            f.ModificationDate = DateTime.SpecifyKind(f.ModificationDate, DateTimeKind.Utc);
        return f;
    }


Get DateTime as UTC with Dapper

I have a stored  procedure in this format 
    
    CREATE PROCEDURE SP_MYTESTpROC
        @VAR1 VARCHAR(10),
        @VAR2 VARCHAR(20),
        @BASEID INT ,
        @NEWID INT OUTPUT
    As Begin
       INSERT INTO TABLE_NAME(username, firstname)
          select @VAR1, @VAR2 
          WHERE ID = @BASEID

       SET @NEWID = SCOPE_IDENTITY() AS INT
    END

I am calling this stored procedure from C# code using dapper. My question is: how do I pass in the output parameter to the stored procedure while using dapper?

All Dapper Solutions on Gang of Coders

Total of 28 Dapper Solutions