CODEWITHSUNDEEP
c#ms-accessdapper
RobinAtTech
RobinAtTech

Reputation: 1329

Dapper ORM Delete functionality with Access

Today when I was working with Access, I tried to do one test by changing one of the table name in Access but not updating in the code and trying to observe the behavior

For example, My code is 

Product product = new Product();                                                                                                 //Delete Works well
product.ProductNumber = "P1";
product.Description = "TestProduct1";

var deleteStatement = @"Delete from Product Where Description = @Description";
int outp = con.Execute(deleteStatement, new { Description = product.Description });

And my Product table in the database is

And my Product table in the database is

Before changing 'Description' as 'Descr' things work perfectly. But after changing it when I ran the code (didn't change anything in the code), it deletes entire table. I am confused. Because it is practical that, somebody accidentally changes the table name and not updating in the code. Why this issue is happening? and how to do a validity check for this?

Personally I think, if the field is not in Access database, it should return an exception or not doing anything

Update

Currently, I am doing the delete after checking through

con.Query<Products>("select ProductNumber, Description from products")

This will generate an exception if proper field names are not in the required table. But this couldn't be the answer for this question. I am expecting a proper answer from somebody

Upvotes: 2

Views: 521

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123399

I was able to reproduce your issue and it seems to be related to the fact that the original field name is Description, and the parameter declaration @Description causes Dapper to create an OleDbParameter whose .ParameterName is Description.

In the first case, when the column Description actually exists, the Access Database Engine recognizes this

var deleteStatement = @"Delete from Product Where Description = @Description";
//                                                ^^^^^^^^^^^

as a valid column name and the DELETE works as expected

After the column as been renamed to Descr, the Access Database Engine no longer recognizes Description as a field name (because it isn't) so it becomes another parameter placeholder. It seems that the result is the statement being evaluated as

Delete from Product Where Description = Description

and since the comparison of a parameter with itself, Description = Description, will always be TRUE then it is effectively the same as

Delete from Product Where TRUE

and all rows are deleted.

This condition can be avoided by ensuring that the parameter names we supply to Dapper do not match the field names. For example, the following code does throw an exception after the Description field has been renamed to Descr.

var deleteStatement = @"Delete from Product Where Description = @d";
int outp = con.Execute(deleteStatement, new { d = product.Description });

Upvotes: 1

Related Questions