SqlExecute Class

MSBuild Extension Pack

previous page next page
SqlExecute Class MSBuild Extension Pack Help 4.0.12.0
Valid TaskActions are:

Execute (Required: ConnectionString, Sql or Files Optional: CodePage, CommandTimeout, Parameters, Retry, UseTransaction, IgnoreScriptErrors, StripMultiLineComments Output: FailedScripts)

ExecuteRawReader (Required: ConnectionString, Sql Optional: CodePage, CommandTimeout, Parameters, Retry, UseTransaction, IgnoreScriptErrors Output: RawReaderResult, FailedScripts)

ExecuteReader (Required: ConnectionString, Sql Optional: CodePage, CommandTimeout, Parameters, Retry, UseTransaction, IgnoreScriptErrors Output: ReaderResult, FailedScripts)

ExecuteScalar (Required: ConnectionString, Sql Optional: CodePage, CommandTimeout, Parameters, Retry, UseTransaction, IgnoreScriptErrors Output: ScalarResult, FailedScripts)

Remote Execution Support: NA

Inheritance Hierarchy
SystemObject  Microsoft.Build.UtilitiesTask
    MSBuild.ExtensionPackBaseTask
      MSBuild.ExtensionPack.SqlServerSqlExecute

Namespace: MSBuild.ExtensionPack.SqlServer
Assembly: MSBuild.ExtensionPack (in MSBuild.ExtensionPack.dll) Version: 4.0.0.0

The SqlExecute type exposes the following members.

Constructors
  NameDescription
Public methodSqlExecute
Top
Properties
  NameDescription
Public propertyCodePage
Allows setting encoding code page to be used. Default is System.Text.Encoding.Default All code pages are listed here: http://msdn.microsoft.com/en-us/library/system.text.encoding
Public propertyCommandTimeout
Sets the timeout in seconds. Default is 30
Public propertyConnectionString
Sets the connection string to use for executing the Sql or Files
Public propertyFailedScripts
A list of failed scripts. Each will have metadata item ErrorMessage set to the error encountered.
Public propertyFiles
Sets the files to execute
Public propertyIgnoreScriptErrors
Ignore any script errors, i.e. continue executing any remaining scripts when an error is encountered. Failed scripts will be returned in the FailedScripts output item.
Public propertyParameters
Sets the parameters to substitute at execution time. These are CASE SENSITIVE.
Public propertyRawReaderResult
Gets the raw output from the reader
Public propertyReaderResult
Gets the output from a reader in an Item with metadata matching the names of columns. The first column returned will be used as the identity.
Public propertyRetry
Specifies whether files should be re-executed if they initially fail
Public propertyScalarResult
Gets the scalar result
Public propertySql
Sets the Sql to execute
Public propertyStripMultiLineComments
Specifies whether to parse out multi-line comments before executing. This can be handy if your comments contain GO statements. Please note that if your sql contains code with /* in it, then you should set this to false. Default is true.
Public propertyUseTransaction
Set to true to run the sql within a transaction
Top
Examples
Copy
<Project ToolsVersion="4.0" DefaultTargets="Default" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
    <PropertyGroup>
        <TPath>$(MSBuildProjectDirectory)\..\MSBuild.ExtensionPack.tasks</TPath>
        <TPath Condition="Exists('$(MSBuildProjectDirectory)\..\..\Common\MSBuild.ExtensionPack.tasks')">$(MSBuildProjectDirectory)\..\..\Common\MSBuild.ExtensionPack.tasks</TPath>
    </PropertyGroup>
    <Import Project="$(TPath)"/>
    <ItemGroup>
        <Files Include="C:\a\Proc1.sql"/>
        <Files Include="C:\a\Proc2.sql"/>
        <Files Include="C:\a\Proc3.sql"/>
        <File2s Include="C:\a\SQLQuery1.sql"/>
        <File2s Include="C:\a\SQLQuery2.sql"/>
    </ItemGroup>
    <Target Name="Default">
        <!-- Execute SQL and return a scalar -->
        <MSBuild.ExtensionPack.SqlServer.SqlExecute TaskAction="ExecuteScalar" UseTransaction="true" Sql="Select GETDATE()" ConnectionString="Data Source=desktop\Sql2008;Initial Catalog=;Integrated Security=True">
            <Output PropertyName="ScResult" TaskParameter="ScalarResult"/>
        </MSBuild.ExtensionPack.SqlServer.SqlExecute>
        <Message Text="$(ScResult)"/>
        <!-- Execute SQL and return the result in raw text form -->
        <MSBuild.ExtensionPack.SqlServer.SqlExecute TaskAction="ExecuteRawReader" UseTransaction="true" Sql="Select * from sys.tables" ConnectionString="Data Source=desktop\Sql2008;Initial Catalog=;Integrated Security=True">
            <Output PropertyName="RawResult" TaskParameter="RawReaderResult"/>
        </MSBuild.ExtensionPack.SqlServer.SqlExecute>
        <Message Text="$(RawResult)"/>
        <!-- Execute SQL and return the result in an Item. Each column is available as metadata -->
        <MSBuild.ExtensionPack.SqlServer.SqlExecute TaskAction="ExecuteReader" Sql="Select * from sys.tables" ConnectionString="Data Source=desktop\Sql2008;Initial Catalog=;Integrated Security=True">
            <Output ItemName="RResult" TaskParameter="ReaderResult"/>
        </MSBuild.ExtensionPack.SqlServer.SqlExecute>
        <Message Text="%(RResult.Identity) - %(RResult.object_id)"/>
        <!-- Execute some sql files -->
        <MSBuild.ExtensionPack.SqlServer.SqlExecute TaskAction="Execute" Retry="true" UseTransaction="true" Files="@(Files)" ConnectionString="Data Source=desktop\Sql2008;Initial Catalog=;Integrated Security=True"/>
        <!-- Use Parameter substitution -->
        <ItemGroup>
            <SqlFiles Include="createLinkedServer.sql"/>
            <SqlParameters Include="true">
                <name>%24(LINKEDSERVER)</name>
                <value>myserver\myinstance</value>
            </SqlParameters>
        </ItemGroup>
        <MSBuild.ExtensionPack.SqlServer.SqlExecute TaskAction="Execute" Files="@(SqlFiles)" ConnectionString="Data Source=desktop\Sql2008;Initial Catalog=;Integrated Security=True" Parameters="@(SqlParameters)" />
    </Target>
</Project>
See Also

Reference

MSBuild.ExtensionPack.SqlServer Namespace
previous page start next page