Databases
StackOverflow2013
Postsdbo
POHow to insert an IEnumerable<T> collection with dapper-dot-net
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to insert an IEnumerable<T> collection with dapper-dot-net
    primarykey
    data
    text
    <p>Yep, there are <a href="https://stackoverflow.com/questions/5957774/performing-inserts-and-updates-with-dapper">questions here</a> and <a href="https://stackoverflow.com/questions/6379478/clarification-of-dapper-example-code">here</a> about how to insert records with dapper-dot-net. However, the answers, while informative, didn't seem to point me in the right direction. Here is the situation: moving data from SqlServer to MySql. Reading the records into an <code>IEnumerable&lt;WTUser&gt;</code> is easy, but I am just not getting something on the insert. First, the 'moving records code': </p> <pre><code>// moving data Dim session As New Session(DataProvider.MSSql, "server", _ "database") Dim resources As List(Of WTUser) = session.QueryReader(Of WTUser)("select * from tbl_resource") session = New Session(DataProvider.MySql, "server", "database", _ "user", "p@$$w0rd") // *edit* - corrected parameter notation with '@' Dim strInsert = "INSERT INTO tbl_resource (ResourceName, ResourceRate, ResourceTypeID, ActiveYN) " &amp; _ "VALUES (@ResourceName, @ResourceRate, @ResourceType, @ActiveYN)" Dim recordCount = session.WriteData(Of WTUser)(strInsert, resources) // session Methods Public Function QueryReader(Of TEntity As {Class, New})(ByVal Command As String) _ As IEnumerable(Of TEntity) Dim list As IEnumerable(Of TEntity) Dim cnn As IDbConnection = dataAgent.NewConnection list = cnn.Query(Of TEntity)(Command, Nothing, Nothing, True, 0, CommandType.Text).ToList() Return list End Function Public Function WriteData(Of TEntity As {Class, New})(ByVal Command As String, ByVal Entities As IEnumerable(Of TEntity)) _ As Integer Dim cnn As IDbConnection = dataAgent.NewConnection // *edit* if I do this I get the correct properties, but no data inserted //Return cnn.Execute(Command, New TEntity(), Nothing, 15, CommandType.Text) // original Return statement Return cnn.Execute(Command, Entities, Nothing, 15, CommandType.Text) End Function </code></pre> <p>cnn.Query and cnn.Execute call the dapper extension methods. Now, the WTUser class (note: the column name changed from 'WindowsName' in SqlServer to 'ResourceName' in MySql, thus the two properties pointing to the same field):</p> <pre><code>Public Class WTUser // edited for brevity - assume the following all have public get/set methods Public ActiveYN As String Public ResourceID As Integer Public ResourceRate As Integer Public ResourceType As Integer Public WindowsName As String Public ResourceName As String End Class </code></pre> <p>I am receiving an exception from dapper: "WTUser is not supported by Dapper." This method in DataMapper (dapper):</p> <pre><code> private static Action&lt;IDbCommand, object&gt; CreateParamInfoGenerator(Type OwnerType) { string dmName = string.Format("ParamInfo{0}", Guid.NewGuid()); Type[] objTypes = new[] { typeof(IDbCommand), typeof(object) }; var dm = new DynamicMethod(dmName, null, objTypes, OwnerType, true); // &lt;&lt; - here // emit stuff // dm is instanced, now ... foreach (var prop in OwnerType.GetProperties().OrderBy(p =&gt; p.Name)) </code></pre> <p>At this point OwnerType = </p> <blockquote> <p>System.Collections.Generic.List`1[[CRMBackEnd.WTUser, CRMBE, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]], mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</p> </blockquote> <p>It seems like OwnerType should be <code>CRMBackEnd.WTUser</code> ... not <code>List&lt;CRMBackEnd.WTUser&gt;</code> ... ??? because what is happening is that the collection properties are being iterated: Count, Capacity, etc. <strong>What am I missing?</strong></p> <p><strong>Update</strong> </p> <p>If I modified session.WriteData as:</p> <pre><code>Public Function WriteData(Of TEntity As {Class, New})(ByVal Command As String, _ ByVal Entities As IEnumerable(Of TEntity)) _ As Integer Dim cnn As IDbConnection = dataAgent.NewConnection Dim records As Integer For Each entity As TEntity In Entities records += cnn.Execute(Command, entity, Nothing, 15, CommandType.Text) Next Return records End Function </code></pre> <p>records are inserted nicely ... but I didn't think this would be necessary given examples like:</p> <pre><code>connection.Execute(@"insert MyTable(colA, colB) values (@a, @b)", new[] { new { a=1, b=1 }, new { a=2, b=2 }, new { a=3, b=3 } } ).IsEqualTo(3); // 3 rows inserted: "1,1", "2,2" and "3,3" </code></pre> <p>... from <a href="http://code.google.com/p/dapper-dot-net/" rel="nofollow noreferrer">dapper-dot-net</a></p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USIAbstract
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to insert an IEnumerable<T> collection with dapper-dot-net
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to insert an IEnumerable<T> collection with dapper-dot-net
      1. USba__friend
      1. VTFavorite
    3. VO
      singulars
      1. POHow to insert an IEnumerable<T> collection with dapper-dot-net
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload