Basic Parameterized SQL

Download dapper eBook

Example

Dapper makes it easy to follow best practice by way of fully parameterized SQL.

Bobby Tables

Parameters are important, so dapper makes it easy to get it right. You just express your parameters in the normal way for your RDBMS (usually @foo, ?foo or :foo) and give dapper an object that has a member called foo. The most common way of doing this is with an anonymous type:

int id = 123;
string name = "abc";
connection.Execute("insert [KeyLookup](Id, Name) values(@id, @name)",
    new { id, name });

And... that's it. Dapper will add the required parameters and everything should work.

Using your Object Model

You can also use your existing object model as a parameter:

KeyLookup lookup = ... // some existing instance
connection.Execute("insert [KeyLookup](Id, Name) values(@Id, @Name)", lookup);

Dapper uses the command-text to determine which members of the object to add - it won't usually add unnecessary things like Description, IsActive, CreationDate because the command we've issued clearly doesn't involve them - although there are cases when it might do that, for example if your command contains:

// TODO - removed for now; include the @Description in the insert

It doesn't attempt to figure out that the above is just a comment.

Stored Procedures

Parameters to stored procedures work exactly the same, except that dapper cannot attempt to determine what should/should-not be included - everything available is treated as a parameter. For that reason, anonymous types are usually preferred:

connection.Execute("KeyLookupInsert", new { id, name },
    commandType: CommandType.StoredProcedure);

Stats

Contributors: 1
2015-11-10
Licensed under: CC-BY-SA

Not affiliated with Stack Overflow
Rip Tutorial: info@zzzprojects.com

Download eBook