Database.ExecuteNonQuery Database.ExecuteNonQuery Database.ExecuteNonQuery Method

Definition

Executes a collection of batches in the context of the database where there are no results returned.

Overloads

ExecuteNonQuery(StringCollection) ExecuteNonQuery(StringCollection) ExecuteNonQuery(StringCollection)

Executes a batch in the context of the database where there are no results returned.

ExecuteNonQuery(String) ExecuteNonQuery(String) ExecuteNonQuery(String)

Executes a statement in the context of the database where there are no results returned.

ExecuteNonQuery(StringCollection, ExecutionTypes) ExecuteNonQuery(StringCollection, ExecutionTypes) ExecuteNonQuery(StringCollection, ExecutionTypes)

Runs a collection of batches in the context of the database where there are no results returned for the specified execution type only.

ExecuteNonQuery(String, ExecutionTypes) ExecuteNonQuery(String, ExecutionTypes) ExecuteNonQuery(String, ExecutionTypes)

Executes a batch in the context of the database where there are no results returned for the specified execution type only.

ExecuteNonQuery(StringCollection) ExecuteNonQuery(StringCollection) ExecuteNonQuery(StringCollection)

Executes a batch in the context of the database where there are no results returned.

public:
 void ExecuteNonQuery(System::Collections::Specialized::StringCollection ^ sqlCommands);
public void ExecuteNonQuery (System.Collections.Specialized.StringCollection sqlCommands);
Public Sub ExecuteNonQuery (sqlCommands As StringCollection)
Parameters
sqlCommands
StringCollection StringCollection StringCollection

A StringCollection system object value that contains more than one Transact-SQL command.

Examples

'Connect to the local, default instance of SQL Server.  
Dim srv As Server  
srv = New Server  

'Reference the AdventureWorks2012 database.  
Dim db As Database  
db = srv.Databases("AdventureWorks2012")  

'Run a Transact-SQL command on the database.  
db.ExecuteNonQuery("CHECKPOINT")  

'Run a Transact-SQL command with results and display.  
Dim ds As DataSet  
ds = db.ExecuteWithResults( _  
"SELECT * FROM Person.Address")  
Dim t As DataTable  
Dim r As DataRow  
Dim c As DataColumn  
For Each t In ds.Tables  
   For Each r In t.Rows  
      For Each c In r.Table.Columns  
          Console.WriteLine( _  
          c.ColumnName + " = " + r(c).ToString)  
      Next  
   Next  
Next  

PowerShell

$srv = new-Object Microsoft.SqlServer.Management.Smo.Server("(local)")  
$db = New-Object Microsoft.SqlServer.Management.Smo.Database  
$db = $srv.Databases.Item("AdventureWorks2012")  
$db.ExecuteNonQuery("CHECKPOINT")  
$ds = $db.ExecuteWithResults("SELECT * FROM Person.Address")  
Foreach ($t in $ds.Tables)  
{  
   Foreach ($r in $t.Rows)  
   {  
      Foreach ($c in $t.Columns)  
      {  
          Write-Host $c.ColumnName "=" $r.Item($c)  
      }  
   }  
}  

Remarks

The StringCollection value of the sqlCommands parameter makes it possible to send a Transact-SQL script with multiple commands.

ExecuteNonQuery(String) ExecuteNonQuery(String) ExecuteNonQuery(String)

Executes a statement in the context of the database where there are no results returned.

public:
 void ExecuteNonQuery(System::String ^ sqlCommand);
public void ExecuteNonQuery (string sqlCommand);
Public Sub ExecuteNonQuery (sqlCommand As String)
Parameters
sqlCommand
String String String

A String value that contains the Transact-SQL command.

Examples

VB

'Connect to the local, default instance of SQL Server.  
Dim srv As Server  
srv = New Server  

'Reference the AdventureWorks2012 database.  
Dim db As Database  
db = srv.Databases("AdventureWorks2012")  

'Run a Transact-SQL command on the database.  
db.ExecuteNonQuery("CHECKPOINT")  

'Run a Transact-SQL command with results and display.  
Dim ds As DataSet  
ds = db.ExecuteWithResults( _  
"SELECT * FROM Person.Address")  
Dim t As DataTable  
Dim r As DataRow  
Dim c As DataColumn  
For Each t In ds.Tables  
   For Each r In t.Rows  
      For Each c In r.Table.Columns  
          Console.WriteLine( _  
          c.ColumnName + " = " + r(c).ToString)  
      Next  
   Next  
Next  

PowerShell

$srv = new-Object Microsoft.SqlServer.Management.Smo.Server("(local)")  
$db = New-Object Microsoft.SqlServer.Management.Smo.Database  
$db = $srv.Databases.Item("AdventureWorks2012")  
$db.ExecuteNonQuery("CHECKPOINT")  
$ds = $db.ExecuteWithResults("SELECT * FROM Person.Address")  
Foreach ($t in $ds.Tables)  
{  
   Foreach ($r in $t.Rows)  
   {  
      Foreach ($c in $t.Columns)  
      {  
          Write-Host $c.ColumnName "=" $r.Item($c)  
      }  
   }  
}  

ExecuteNonQuery(StringCollection, ExecutionTypes) ExecuteNonQuery(StringCollection, ExecutionTypes) ExecuteNonQuery(StringCollection, ExecutionTypes)

Runs a collection of batches in the context of the database where there are no results returned for the specified execution type only.

public:
 void ExecuteNonQuery(System::Collections::Specialized::StringCollection ^ sqlCommands, Microsoft::SqlServer::Management::Common::ExecutionTypes executionType);
public void ExecuteNonQuery (System.Collections.Specialized.StringCollection sqlCommands, Microsoft.SqlServer.Management.Common.ExecutionTypes executionType);
Public Sub ExecuteNonQuery (sqlCommands As StringCollection, executionType As ExecutionTypes)
Parameters
sqlCommands
StringCollection StringCollection StringCollection

A StringCollection System object value that contains more than one Transact-SQL command.

executionType
Microsoft.SqlServer.Management.Common.ExecutionTypes Microsoft.SqlServer.Management.Common.ExecutionTypes Microsoft.SqlServer.Management.Common.ExecutionTypes

An Microsoft.SqlServer.Management.Common.ExecutionTypes object value that specifies the execution type.

Examples

VB

'Connect to the local, default instance of SQL Server.  
Dim srv As Server  
srv = New Server  

'Reference the AdventureWorks2012 database.  
Dim db As Database  
db = srv.Databases("AdventureWorks2012")  

'Run a Transact-SQL command on the database.  
db.ExecuteNonQuery("CHECKPOINT")  

'Run a Transact-SQL command with results and display.  
Dim ds As DataSet  
ds = db.ExecuteWithResults( _  
"SELECT * FROM Person.Address")  
Dim t As DataTable  
Dim r As DataRow  
Dim c As DataColumn  
For Each t In ds.Tables  
   For Each r In t.Rows  
      For Each c In r.Table.Columns  
          Console.WriteLine( _  
          c.ColumnName + " = " + r(c).ToString)  
      Next  
   Next  
Next  

PowerShell

$srv = new-Object Microsoft.SqlServer.Management.Smo.Server("(local)")  
$db = New-Object Microsoft.SqlServer.Management.Smo.Database  
$db = $srv.Databases.Item("AdventureWorks2012")  
$db.ExecuteNonQuery("CHECKPOINT")  
$ds = $db.ExecuteWithResults("SELECT * FROM Person.Address")  
Foreach ($t in $ds.Tables)  
{  
   Foreach ($r in $t.Rows)  
   {  
      Foreach ($c in $t.Columns)  
      {  
          Write-Host $c.ColumnName "=" $r.Item($c)  
      }  
   }  
}  

Remarks

The StringCollection value of the sqlCommands parameter makes it possible to send a Transact-SQL script with multiple commands.

The executionType parameter is used to filter the types of commands that are run. For example, you can specify that the execution continue after encountering an error with the <xref:Microsoft.SqlServer.Management.Common.ExecutionTypes.ContinueOnError> option.

ExecuteNonQuery(String, ExecutionTypes) ExecuteNonQuery(String, ExecutionTypes) ExecuteNonQuery(String, ExecutionTypes)

Executes a batch in the context of the database where there are no results returned for the specified execution type only.

public:
 void ExecuteNonQuery(System::String ^ sqlCommand, Microsoft::SqlServer::Management::Common::ExecutionTypes executionType);
public void ExecuteNonQuery (string sqlCommand, Microsoft.SqlServer.Management.Common.ExecutionTypes executionType);
Public Sub ExecuteNonQuery (sqlCommand As String, executionType As ExecutionTypes)
Parameters
sqlCommand
String String String

A String value that contains a Transact-SQL command.

executionType
Microsoft.SqlServer.Management.Common.ExecutionTypes Microsoft.SqlServer.Management.Common.ExecutionTypes Microsoft.SqlServer.Management.Common.ExecutionTypes

An Microsoft.SqlServer.Management.Common.ExecutionTypes object value that specifies the execution type.

Examples

VB

'Connect to the local, default instance of SQL Server.  
Dim srv As Server  
srv = New Server  

'Reference the AdventureWorks2012 database.  
Dim db As Database  
db = srv.Databases("AdventureWorks2012")  

'Run a Transact-SQL command on the database.  
db.ExecuteNonQuery("CHECKPOINT")  

'Run a Transact-SQL command with results and display.  
Dim ds As DataSet  
ds = db.ExecuteWithResults( _  
"SELECT * FROM Person.Address")  
Dim t As DataTable  
Dim r As DataRow  
Dim c As DataColumn  
For Each t In ds.Tables  
   For Each r In t.Rows  
      For Each c In r.Table.Columns  
          Console.WriteLine( _  
          c.ColumnName + " = " + r(c).ToString)  
      Next  
   Next  
Next  

PowerShell

$srv = new-Object Microsoft.SqlServer.Management.Smo.Server("(local)")  
$db = New-Object Microsoft.SqlServer.Management.Smo.Database  
$db = $srv.Databases.Item("AdventureWorks2012")  
$db.ExecuteNonQuery("CHECKPOINT")  
$ds = $db.ExecuteWithResults("SELECT * FROM Person.Address")  
Foreach ($t in $ds.Tables)  
{  
   Foreach ($r in $t.Rows)  
   {  
      Foreach ($c in $t.Columns)  
      {  
          Write-Host $c.ColumnName "=" $r.Item($c)  
      }  
   }  
}  

Remarks

The executionType parameter is used to filter the types of commands that are run. For example, you can specify that the execution continue after encountering an error with the <xref:Microsoft.SqlServer.Management.Common.ExecutionTypes.ContinueOnError> option.

Applies to