Invoke-SqlCmd vs .Net Framework based Sql Server Command

Question

Just started getting my feet wet with interacting with SQL Server using PowerShell and have got this query:

For performing any sql server database operation, using .net framework based objects is my preferred choice mainly because of the reason that I come from C# background. For example:

$sqlCommand = New-Object System.Data.SqlClient.SqlCommand

However, I found that we can also use Invoke-SqlCmd cmdlet available in SqlServerCmdletSnapin100. As:

Add-PSSnapin SqlServerCmdletSnapin100
Set-Location SQLSERVER:\sql\DatabaseInstance\databases\TestDatabase
Invoke-SqlCmd -Query "SELECT * FROM [File]" -User "UserName" -Password "UserPassword"

Wondering, why one should ever go for using Invoke-SqlCmd instead of .net framework based objects?

Was Invoke-SqlCmd made available through PowerShell because some population out there is not familiar with .net framework.
Or
Are there any particular features offered by Invoke-SqlCmd for which we must use it.

Please guide.

Cole9350 · Accepted Answer · 2014-04-02 15:50:55Z

Invoke-SqlCmd was made available through powershell by snapins, not by default. All it does is call the sqlcmd.exe, which existed before powershell, and was used to send sql commands through cmd. Now that we have direct access to .Net, we don't need it. There is nothing Invoke-sqlcmd can do that you can't do with .Net, SqlClient will always be the better choice IMO, because it is faster and you'll never get this "different behavior" Microsoft refers to.

http://technet.microsoft.com/en-us/library/ms162773.aspx

SQL Server Management Studio uses the Microsoft .NET Framework SqlClient for execution in regular and SQLCMD mode in Query Editor. When sqlcmd is run from the command line, sqlcmd uses the ODBC driver. Because different default options may apply, you might see different behavior when you execute the same query in SQL Server Management Studio in SQLCMD Mode and in the sqlcmd utility.

Collectives™ on Stack Overflow

Invoke-SqlCmd vs .Net Framework based Sql Server Command

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related