3

I was wondering if its possible to run a TSQL query like Truncate Table x using Dapper?

What I've tried:

using (var con = DB.Connection)
{
    con.Open();
    var ret = con.Execute("Truncate Table [Y].[X]");
}

PS. I do not want to create a SP for it.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Masoud Andalibi
  • 3,168
  • 5
  • 18
  • 44
  • 2
    What happened when you tried this? Did it fail? If so, how? – Mureinik Jul 14 '18 at 06:12
  • @Mureinik when i execute it. it turns -1 – Masoud Andalibi Jul 14 '18 at 06:31
  • I have no idea what is "Dapper" but truncate table is done with a simple query, which can be execute by any external application. There is no different between SSMS to any application that you develop. Same as you can execute using SSMS you can execute using "SQL Server Operation Studio" or any app that you develop.... as long as you have the permission – Ronen Ariely Jul 14 '18 at 06:51

1 Answers1

6

You seem to think that returning -1 indicates a problem. But according to the documentation Truncate:

Removes all rows from a table or specified partitions of a table, without logging the individual row deletions.

And Dapper's Execute method returns the rows affected.

So -1 seems reasonable to me.

Edit to add

After further investigation of the source code in GitHub, Dapper's Execute extension under-the-hood calls SqlMapper's ExecuteImpl, which (due to the way it's called can only go two ways; but basically both) ends up returning the result of IDbCommand's ExecuteNonQuery. So with that piece of the puzzle in place, Martin Smith's information from MSDN's ExecuteNonQuery applies:

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1

Richardissimo
  • 5,596
  • 2
  • 18
  • 36
  • [For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1.](https://msdn.microsoft.com/en-us/library/system.data.common.dbcommand.executenonquery(v=vs.110).aspx) – Martin Smith Jul 14 '18 at 16:32