如何使用 invoke-sqlcmd 访问数据集中表的特定列? [英] How to use invoke-sqlcmd to access a specific column of a table in a dataset?

问题描述

正如在这个线程中所讨论的，用户 jkdba 建议使用不同的方法来访问表的列.由于原始线程正在修复一个附带问题，因此我打开了该线程以了解如何使用 invoke-sqlcmd 访问表的特定列.如果有人有不同的方法，请不要犹豫分享.:)

As discussed from this thread, User jkdba suggested a different method to access a column of a table. Since the original thread was fixing a side problem, I opened this thread to find out how to use invoke-sqlcmd to access a specific column of a table. If anyone has a different approach, please don't hestitate to share. :)

推荐答案

首先，您可以在使用数据适配器方法时使用相同的方法来访问列值.我更喜欢使用与 SqlCommand 和 DataAdapter 等效的 PowerShell(Invoke-SQLCmd)，因为它的代码少得多，可读性强，而且对任何可能正在查看它的非开发人员都很友好.作为旁注，Invoke-SQLCmd 主要进行相同的底层 Dot Net 调用.

First, you can use the same methods of accessing column values when using your Data Adapter method. I prefer to use the PowerShell equivalent (Invoke-SQLCmd) to SqlCommand and DataAdapter as it is a lot less code, it's readable, and it is friendly to any non-devs that might be looking at it. As a side note Invoke-SQLCmd is mostly make the same underlying Dot Net calls.

因此，在我进入 Invoke-SQLCmd 和基本对象属性访问之前，您可以使用与您其他帖子中的 $DataSet 对象相同的属性访问技术，就像这样:

So before I get into Invoke-SQLCmd and basic object property accessing you can use the same property accessing techniques with your $DataSet object from your other post just like this:

• 这将返回表或数据表对象 $DataSet.Tables.
• 这将返回数据表对象 $DataSet.Tables.ColumnName 的所有列值.

• This will return the table or data table object $DataSet.Tables.
• This will return all of the column values of the data table object $DataSet.Tables.ColumnName.

当您使用 Invoke-SQLCmd 时，它会返回一个充满 Dot Net DataRow 的 PowerShell 数组对象.基本上是一样的，只是代码少了.

When you use Invoke-SQLCmd, it will return a PowerShell array object full of Dot Net DataRows. It is basically the same just less code.

运行 Invoke-Sqlcmd:

 ## Run Query and Get Date
 $SQLResults = Invoke-Sqlcmd -ServerInstance 'Server\Instance' -Database 'DatabaseName' -Query 'select * from mytable'
 ## You can always see all of the properties and methods associated with the result object by running the command below
 $SQLResults | Get-Member
 ## The above will show the PowerShell understood properties and implicit stuff it does.
 ## Adding -Force to the Get-Member call will show the true datatypes and properties.

获取列的所有值:

 ## If you just want to list all of the values for a column you would do variable.property name aka results.columnname
 $SQLResults.MyColumnName
 ## If The column name has a space in it, you can do this
 $SQLResults.'My Column Name'
 ## Both of the above results will dump all of the values from the query results for the column of 'MyColumnName'

访问一行的每一列:

 foreach($Row in $SQLResults)
 {
     ## this would print the value of each column for reach row one by one.
     $Row.ColumnName
     $Row.ColumnName1
     $Row.ColumnName2
 }

在结果中添加一列:

您可以使用Add-Member 函数在逐行处理后轻松地向结果中添加一列.

You can easily add a column to the results after performing some row by row processing by using the Add-Member function.

 foreach($Row in $SQLResults)
 {
     ## some sort of row by row processing
     if($Row.ColumnName -ilike 'some*value')
     {
         $Row | Add-Member -MemberType NoteProperty -Name 'MYNewColumnName' -Value 'IfTrueLogicValue'
     }
     else
     {
         $Row | Add-Member -MemberType NoteProperty -Name 'MYNewColumnName' -Value 'IfFalseLogicValue'
     }

     ##Be Sure to output the row
     $Row
 }

这篇关于如何使用 invoke-sqlcmd 访问数据集中表的特定列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！