在PowerShell函数中返回DataSet / DataTable的奇怪行为 [英] Strange behavior in PowerShell function returning DataSet/DataTable

问题描述

这让我疯狂。我有一个来自多个脚本的库，它包含以下函数：

  function lib_open_dataset（[string] $ sql）{ 
 $ ds = new-objectSystem.Data.DataSet
 $ da = new-objectSystem.Data.SqlClient.SqlDataAdapter（$ sql，$ _conn_string）
 
 $ record_count = $ da.Fill（$ ds）
 
 return $ ds 
} 
  

这几乎是无处不在的，它工作正常，除非我通常需要这样做：

  $ ds = lib_open_dataset（$ some_sql）
 $ table = $ ds.Tables [0] 
 foreach（$ table.Rows中的$ row）{
＃etc 
}因此，我创建了一个新的简单包装函数，以避免对第一个表进行额外的取消引用：
 p> 
 
 
 函数lib_open_table（[string] $ sql）{
 $ ds = lib_open_dataset $ sql 
 return $ ds。表[0] 
} 
  
问题是从这里返回的是表的行集合，因为某些原因，而不是表本身。这会导致上面写的 foreach 行循环失败，并显示Can not index into a null array。例外。经过多次尝试和错误，我想出这个作品：
  foreach（$ row in $ table）{
＃etc 
} 
  
注意 $ table.Rows ，并在 foreach 语句中 $ table 此工作。因为 $ table 实际上指向Rows集合。如果语句
  return $ ds.Tables [0] 
  
据说是正确的，为什么函数返回表对象的子集合而不是表本身？
 
 
 
我猜想有一些东西在Powershell函数工作，这是导致这明显，但我不能弄清楚什么。 
解决方案
您可以使用逗号运算符将行集合包裹在数组中，以便在展开数组时，原始行集合eg：
 函数lib_open_table（[string] $ sql）{
 $ ds = lib_open_dataset $ sql 
 return，$ ds.Tables [0] 
} 
  
不能阻止PowerShell展开数组/集合。您可以做的最好的办法是解决该行为，将数组/集合包装到另一个单个元素数组中。
 
This is driving me crazy. I have a library I source from multiple scripts, which contains the following function:
function lib_open_dataset([string] $sql) {
    $ds = new-object "System.Data.DataSet"
    $da = new-object "System.Data.SqlClient.SqlDataAdapter" ($sql, $_conn_string)

    $record_count = $da.Fill($ds)

    return $ds
}
This is called pretty much everywhere and it works just fine, except that I normally have to do this:
$ds = lib_open_dataset($some_sql)
$table = $ds.Tables[0]
foreach ($row in $table.Rows) {
    # etc
}
So I created a new simple wrapper function to avoid the extra step of dereferencing the first table:
function lib_open_table([string] $sql) {
    $ds = lib_open_dataset $sql
    return $ds.Tables[0]
}
The problem is that what's being returned from here is the Rows collection of the table for some reason, not the table itself. This causes the foreach row loop written as above to fail with a "Cannot index into a null array." exception. After much trial and error I figured out this works:
foreach ($row in $table) {
    # etc
}
Note the difference between $table.Rows and just $table in the foreach statement. This works. Because $table actually points to the Rows collection. If the statement
return $ds.Tables[0]
is supposedly correct, why is the function returning a child collection of the table object instead of the table itself?

I'm guessing there's something in the way Powershell functions work that's causing this obviously, but I can't figure out what. 
解决方案
You can use the comma operator to wrap the rows collection in an array so that when the array is unrolled you wind up with the original rows collection e.g.:
function lib_open_table([string] $sql) {
    $ds = lib_open_dataset $sql    
    return ,$ds.Tables[0]
}
Essentially you can't prevent PowerShell from unrolling arrays/collections.  The best you can do is workaround that behavior by wrapping the array/collection within another, single element array.

                        
这篇关于在PowerShell函数中返回DataSet / DataTable的奇怪行为的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！