0

I am trying to insert into a postgresql database using dapper and simplecrud.

My entity has a VARCHAR primary key, which I understand means that I have to include the [Key] and [Required] tags within my POCO.

[Table("tube_data")]
class Tube : IEntity
{
    //my primary key (name matches column name)
    [Dapper.Key] [Dapper.Required] 
    public string tube_nr { get; } 

    //Constructors (probably not important)
    private Tube()
    {
    }

    public Tube(string tube_nr)
    {
        this.tube_nr = tube_nr;
    }

    //more code... just nullable properties (ints, floats and strings)
}

When I execute the function using

    _conn.Insert<Tube>(t);

I get a System.FormatException. "The string was not in a correct format". Looking at the stack trace, I can see that Dapper is calling System.Convert.ToInt64()

I have other entities with auto-incrementing primary keys, which work without a problem (so I know the NpgSqlConnection is working fine). In fact, all of my VARCHAR PK entities fail, while all of my auto-incrementing PK entities succeed.

I have a lot of properties, so I don't want to write out the SQL manually if I can avoid it.

How do I get SimpleCRUD to work properly with VARCHAR/string primary keys? I thought the [Key][Required] tags would do the trick.

Tom McSkimming
  • 23
  • 4
  • 1
    After analysing the source code, I have come to the conclusion that SimpleCRUD does not really support VARCHARs as primary keys. Nor does Dapper.Contrib.It expects them to be ints or longs. It's knock off time, but it looks like i will be writing the custom SQL queries first thing Monday morning. – Tom McSkimming Nov 30 '18 at 07:00

3 Answers3

1

Try Dapper.Contrib with [ExplicitKey]. This works with both string and int key.

ExplicitKey: Specify the property as a key explicitly which is not automatically generated by the database.

Popa Andrei
  • 2,299
  • 21
  • 25
  • Have you had success with this method? I actually already tried this and was having similar trouble. The insert method returns an int/long (cant remember which). I didnt spend too long playing around with it, so i might have missed something. – Tom McSkimming Dec 01 '18 at 01:09
  • Yes, it works. Did you check that the insert was done in the database? Insert() by default returns an int /long which is supposed to be the generated id, but since you have an explicit string key, the method returns 0. There is no problem because you already know the key of the record. – Popa Andrei Dec 01 '18 at 08:47
  • Tested and working. Marked as answer because it is a useful solution, even if it doesn't literally answer the question. – Tom McSkimming Dec 02 '18 at 23:12
0

Tested and working for me,

  • Use Dapper.Contrib with [ExplicitKey, Required]
  • define key type when insert: _conn.Insert<string,Tube>(t);
Hilman Karyadi
  • 1
  • 1
    Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community May 12 '23 at 10:50
0

Very late but SimpleCrud does work with the following code:

Use

[Key, Required]
private string tube_nbr { get; }

conn.Insert<string,Tube>(tube) // please note the string
BobsYourUncle
  • 1