locked
Dapper .Net ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized on Insert RRS feed

  • Question

  • User-1877113880 posted

    Hello,

    Currently playing around with Dapper .Net I'm trying to insert a record into the db as shown here.

     using (var sqlCon = new SqlConnection(Context.ReturnDatabaseConnection()))
                {
                    sqlCon.Open();
    
                    var emailExists = sqlCon.Query<UserProfile>(@"SELECT UserId FROM User_Profile WHERE EmailAddress = @EmailAddress",
                                                                  new { EmailAddress = userRegister.EmailAddress.Trim() }).FirstOrDefault();
    
                    if (emailExists == null) // No profile exists with the email passed in, so insert the new user.
                    {
                        var userProfileEntity = new UserProfileEntity
                        {
                            UniqueId = Guid.NewGuid(),
                            Firstname = userRegister.Firstname,
                            Surname = userRegister.Surname,
                            EmailAddress = userRegister.EmailAddress,
                            Username = CreateUsername(userRegister.Firstname),
                            Password = EncryptPassword(userRegister.Password),
                            AcceptedTerms = true,
                            AcceptedTermsDate = System.DateTime.Now,
                            AccountActive = true,
                            CurrentlyOnline = true,
                            ClosedAccountDate = null,
                            JoinedDate = System.DateTime.Now
                        };
    
                        userProfile.UserId = SqlMapperExtensions.Insert(sqlCon, userProfileEntity); // Call the Dapper Extension method to insert the new record
                        userProfile.Firstname = userRegister.Firstname;
                        userProfile.Username = userProfile.Username;
                        userProfile.EmailAddress = userProfile.EmailAddress;
    
                        Registration.SendWelcomeEmail(userRegister.EmailAddress, userRegister.Firstname); // Send welcome email to new user.
                    }
                }

    On this line

    userProfile.UserId = SqlMapperExtensions.Insert(sqlCon, userProfileEntity); // Call the Dapper Extension method to insert the new record
    

    it calls the following

     public static long Insert<T>(this IDbConnection connection, T entityToInsert, IDbTransaction transaction = null, int? commandTimeout = null) where T : class
            {
                using (var tx = connection.BeginTransaction())
                {
                    var type = typeof(T);
    
                    var name = GetTableName(type);
    
                    var sb = new StringBuilder(null);
                    sb.AppendFormat("insert into {0} (", name);
    
                    var allProperties = TypePropertiesCache(type);
                    var keyProperties = KeyPropertiesCache(type);
    
                    for (var i = 0; i < allProperties.Count(); i++)
                    {
                        var property = allProperties.ElementAt(i);
                        if (keyProperties.Contains(property)) continue;
    
                        sb.Append(property.Name);
                        if (i < allProperties.Count() - 1)
                            sb.Append(", ");
                    }
                    sb.Append(") values (");
                    for (var i = 0; i < allProperties.Count(); i++)
                    {
                        var property = allProperties.ElementAt(i);
                        if (keyProperties.Contains(property)) continue;
    
                        sb.AppendFormat("@{0}", property.Name);
                        if (i < allProperties.Count() - 1)
                            sb.Append(", ");
                    }
                    sb.Append(") ");
                    connection.Execute(sb.ToString(), entityToInsert, transaction: transaction, commandTimeout: commandTimeout);
                    //NOTE: would prefer to use IDENT_CURRENT('tablename') or IDENT_SCOPE but these are not available on SQLCE
                    var r = connection.Query("select @@IDENTITY id");
                    tx.Commit();
                    return (int)r.First().id;
                }
            }

    which will loop through the entity I have provided and save it in the db well that's what I want it to do anyway. but when I run this and press insert I get the following Exception.

    ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized

    Can someone please tell me what im doing wrong here?

    Friday, November 14, 2014 1:09 AM

All replies

  • User-1716253493 posted

    Seem like the problem because transaction = null

    Friday, November 14, 2014 2:21 AM
  • User-1877113880 posted

    Yeah I tried passing in a transaction and it says you cant have parallel transactions.....
    Friday, November 14, 2014 3:05 AM
  • User-1877113880 posted

    Can anyone help with this please?

    Friday, November 14, 2014 6:11 AM