ExecuteNonQuery
This method is used to execute a raw-SQL directly towards the database. It returns the number of rows affected during the execution. This method supports all types of RDMBS data providers.
Code Snippets
Below is a code that deletes all the rows from the [dbo].[Person]
table from the database.
using (var connection = new SqlConnection(connectionString))
{
var affectedRows = connection.ExecuteNonQuery("DELETE FROM [dbo].[Person];");
}
Passing of Parameters
You can pass a parameter via the following objects.
- IDbDataParameter
- Anonymous Types
- ExpandoObject
- Dictionary<string, object>
- QueryField/QueryGroup
IDbDataParameter
using (var connection = new SqlConnection(connectionString))
{
var param = new
{
IsEnabled = new SqlParameter("_", true),
LastAccessDateUtc = new SqlParameter("_", DateTime.UtcNow.AddMonths(-6).Date)
};
var commandText = "UPDATE IsEnabled = @IsEnabled FROM [dbo].[Person] WHERE ([LastAccessDateUtc] = @LastAccessDateUtc);";
var affectedRows = connection.ExecuteNonQuery(commandText, param);
}
The name of the parameter is not required. The library is replacing it with the actual name of the property passed from the object.
Anonymous Types
using (var connection = new SqlConnection(connectionString))
{
var param = new
{
IsEnabled = true,
LastAccessDateUtc = DateTime.UtcNow.AddMonths(-6).Date
};
var commandText = "UPDATE IsEnabled = @IsEnabled FROM [dbo].[Person] WHERE ([LastAccessDateUtc] = @LastAccessDateUtc);";
var affectedRows = connection.ExecuteNonQuery(commandText, param);
}
ExpandoObject
using (var connection = new SqlConnection(connectionString))
{
var param = new ExpandoObject() as IDictionary<string, object>;
param.Add("IsEnabled", true);
param.Add("LastAccessDateUtc", DateTime.UtcNow.AddMonths(-6).Date );
var commandText = "UPDATE IsEnabled = @IsEnabled FROM [dbo].[Person] WHERE ([LastAccessDateUtc] = @LastAccessDateUtc);";
var affectedRows = connection.ExecuteNonQuery(commandText, param);
}
Dictionary<string, object>
using (var connection = new SqlConnection(connectionString))
{
var param = new Dictionary<string, object>
{
{ "IsEnabled", true },
{ "LastAccessDateUtc", DateTime.UtcNow.AddMonths(-6).Date }
};
var commandText = "UPDATE IsEnabled = @IsEnabled FROM [dbo].[Person] WHERE ([LastAccessDateUtc] = @LastAccessDateUtc);";
var affectedRows = connection.ExecuteNonQuery(commandText, param);
}
QueryField/QueryGroup
using (var connection = new SqlConnection(connectionString))
{
var param = new []
{
new QueryField("IsEnabled", true),
new QueryField("LastAccessDateUtc", DateTime.UtcNow.AddMonths(-6).Date)
};
var commandText = "UPDATE IsEnabled = @IsEnabled FROM [dbo].[Person] WHERE ([LastAccessDateUtc] = @LastAccessDateUtc);";
var affectedRows = connection.ExecuteNonQuery(commandText, param);
}
Or via QueryGroup.
using (var connection = new SqlConnection(connectionString))
{
var param = new QueryGroup(new []
{
new QueryField("IsEnabled", true),
new QueryField("LastAccessDateUtc", DateTime.UtcNow.AddMonths(-6).Date)
});
var commandText = "UPDATE IsEnabled = @IsEnabled FROM [dbo].[Person] WHERE ([LastAccessDateUtc] = @LastAccessDateUtc);";
var affectedRows = connection.ExecuteNonQuery(commandText, param);
}
Array Parameters (for the IN keyword)
You can pass an array of values if you are using the IN
keyword.
using (var connection = new SqlConnection(connectionString))
{
var param = new
{
Keys = new [] { 10045, 10102, 11004 }
};
var commandText = "DELETE FROM dbo].[Person] WHERE Id IN (@Keys);";
var affectedRows = connection.ExecuteNonQuery(commandText, param);
}
You can also use the types defined at the Passing of Parameters section when passing a parameter.
Executing a Stored Procedure
There are 2 ways of executing a stored procedure. First, simply pass the name of the stored procedure and set the command type to CommandType.StoredProcedure
.
using (var connection = new SqlConnection(connectionString))
{
var param = new { LastAccessDateUtc = DateTime.UtcNow.AddMonths(-6).Date };
var affectedRows = connection.ExecuteNonQuery("[dbo].[sp_DisablePeopleState](@LastAccessDateUtc);",
param, commandType: CommandType.StoredProcedure);
}
Or, simply use the native SQL calls like below.
using (var connection = new SqlConnection(connectionString))
{
var affectedRows = connection.ExecuteNonQuery("EXEC [dbo].[sp_DisablePeopleState](@LastAccessDateUtc);",
new { LastAccessDateUtc = DateTime.UtcNow.AddMonths(-6).Date });
}
Notice in the second call, there is semi-colon at the end of the command text and the command type was not set.