Using PowerShell to send data from SQL Server to service via SOAP
<p>I'm fairly new to PowerShell and brand new (as in, today) to web services and SOAP. A vendor gave us documentation on their web service API that allows the creation of user accounts. I'm trying to use PowerShell to pull our users from SQL Server and send the data to their service. We will need to add users on an ongoing basis.</p>

<p>Below is a pared-down version of what I came up with and it actually seems to work; the vendor told me to include a <code>dry_run</code> parameter while testing and I'm getting a <code>dry_run_success</code> from the <code>response_type</code>.</p>

<p>My question is: Is this even close to being the appropriate way to do it with PowerShell?</p>

<pre><code># Open ADO.NET Connection to database
$dbConn = New-Object Data.SqlClient.SqlConnection;
$dbConn.ConnectionString = "Data Source=mydbserver;User ID=someuserid;Password=mypassword;Initial Catalog=mydatabase";
$dbConn.Open();
$sql = "select * from mytable";
$dbSqlCmd = New-Object Data.SqlClient.SqlCommand $sql, $dbConn;
$dbRd = $dbSqlCmd.ExecuteReader();

# Create a Web Service Proxy
$proxy = New-WebServiceProxy -Uri https://somedomain.com/service/wsdl
$namespace = $proxy.GetType().NameSpace
$param = New-Object($namespace + ".somemethod")

# Loop through records from SQL and invoke the web service
While ($dbRd.Read())
{
    $param.user_id = $dbRd.GetString(0)
    $param.password = $dbRd.GetString(1)
    $param.display_name = $dbRd.GetString(2)

    $request = $proxy.TheMethod($param)

    if ($request.response_type -eq 'error')
    {
        $request.error.extended_error_text
    }
}

# Clean up
$dbRd.Close();
$dbSqlCmd.Dispose();
$dbConn.Close();
</code></pre>