Dapper: can I use parameters for anything?

dapper npgsql parameter-passing

Question

I am writing some code that will prepare my database for my application. In the code there are some repetitive SQL statements when I am creating a table, and I want to hide them in some methods (there are only two for now: creating the primary key and making the id of the table an auto-increment one in postgres). For the simple case of the primary key, I first wrote a function like this:

public void MakePrimaryKey(DbConnection conn, string tblName, string colName)
{
    conn.Execute(@"
ALTER TABLE ""@tblName""
    ADD CONSTRAINT ""@constrName"" PRIMARY KEY(""@colName"")
    ", new { tblName = tblName,
             constrName = tblName + "_pkey",
             colName = colName } );
}

After much fiddling around with errors and exceptions, I finally concluded that using parameters in this fashion is not really supported, so I switched to a traditional string.Format() call, and all was well.

But I am not really satisfied. Is this way of using parameters really not supported? If so, what are the places that I can safely use these parameters? Only for the variable parts of the SQL query - such as the places where I can use a stored procedure parameters?

Accepted Answer

If I remember correctly, Dapper uses a parameterized IDbCommand to execute its queries.

Parameterized queries are simply string replacement operations.

To be more specific, a parameterized query lets you have parameters in your query, sends the values for those parameters, and then SQL Server handles compiling the queries and passing the values for the parameters.

If you can't create a parameterized query with plain ADO.NET using the desired syntax, Dapper.NET isn't going to be able to do it for you either.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why