随笔 - 24, 文章 - 0, 评论 - 1, 引用 - 0
数据加载中……

Powershell with Database

通过ADO查询Access

$adOpenStatic = 3
$adLockOptimistic = 3 

$objConnection = New-Object -comobject ADODB.Connection
$objRecordset = New-Object -comobject ADODB.Recordset 

$objConnection.Open("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = $pwd\checksav.mdb")
$strQuery="Select * from CheckSAV"
$objRecordset.Open($strQuery, $objConnection,$adOpenStatic,$adLockOptimistic) 

#$objRecordSet=$objConnection.Execute($strQuery) 

$objRecordset.MoveFirst() 

do {$objRecordset.Fields.Item("ComputerName").Value; $objRecordset.MoveNext()} until 
    ($objRecordset.EOF -eq $True) 

$objRecordset.Close()
$objConnection.Close() 

 

通过ADO更新Access

$adOpenStatic = 3
$adLockOptimistic = 3 

$objConnection = New-Object -com "ADODB.Connection"
$objRecordSet = New-Object -com "ADODB.Recordset" 

$objConnection.Open("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = $pwd\checksav.mdb") 
$strQuery="Select * from CheckSAV"
$objRecordset.Open($strQuery, $objConnection,$adOpenStatic,$adLockOptimistic) 

$objRecordSet.AddNew()
$objRecordSet.Fields.Item("ComputerName").Value = "atl-ws-001"
$objRecordSet.Update() 

$objRecordSet.Close()
$objConnection.Close() 

 
通过ADO删除Access

$objConnection = New-Object -com "ADODB.Connection"
$objRecordSet = New-Object -com "ADODB.Recordset" 

$objConnection.Open("Provider = Microsoft.Jet.OLEDB.4.0; Data Source = $pwd\checksav.mdb") 
$strQuery="delete from checksav where computername='atl-ws-001'"
$objRecordSet=$objConnection.Execute($strQuery) 

$objConnection.Close() 

 

通过ADO查询Excel

$objConnection= New-Object -com "ADODB.Connection" 
$file="c:\ATT1741595.xls" 
$strQuery="Select * from [Sheet1$]" 
$users=@() 
$objConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;` 
Data Source=$file;Extended Properties=Excel 8.0;") 
$objRecordSet=$objConnection.Execute($strQuery) 
do { 
$users+=$objRecordSet.Fields.Item(9).Value 
$objRecordSet.MoveNext() 
} Until ($objRecordSet.EOF) 
$objConnection.Close() 

 

查询SqlServer

$conn = new-object system.data.oledb.oledbconnection
$connstring = "provider=sqloledb;data source=corp-alt-66;initial catalog=tracer2_dev_dfm;integrated security=SSPI"
$conn.connectionstring = $connstring
$conn.open() 

  

$sqlquery = "select ServerId, ServerName from Servers"
$cmd = New-Object system.data.oledb.oledbcommand
$cmd.connection = $conn
$cmd.commandtext = $sqlquery
$reader = $cmd.executereader()#将 CommandText 发送到 Connection 并生成一个 OleDbDataReader。OleDbCommand.ExecuteScalar 方法，执行查询，并返回查询所返回的结果集中第一行的第一列，忽略其他列或行。
while($reader.read())
  {
    $servId = $reader.getvalue(0)
    $computer = $reader.getvalue(1)
  } 

$reader.close()
$conn.close() 

 

更新Sqlserver

$conn = new-object System.Data.SqlClient.SqlConnection
$connstring = "provider=sqloledb;data source=corp-alt-66;initial catalog=tracer2_dev_dfm;integrated security=SSPI"
$conn.connectionstring = $connstring
$conn.open()


$query = "Insert into TestTable (ServerId, OSVersion) values ('$servId','$version')"
$cmd = New-Object system.data.oledb.oledbcommand
$cmd.connection = $conn
$cmd.commandtext = $query
$cmd.executenonquery()#针对 Connection 执行 SQL 语句并返回受影响的行数。
$conn.close()

 

$conn = new-object System.Data.SqlClient.SqlConnection 

$conn.ConnectionString = "server=localhost;database=Northwind;integrated security=SSPI" 

$cmd = new-object System.Data.SqlClient.SqlCommand 

$cmd.CommandText = "SELECT CategoryID, CategoryName, Description,Picture FROm Categories" 

$cmd.Connection = $conn 

$adapter = new-object System.Data.SqlClient.SqlDataAdapter 

$adapter.SelectCommand = $cmd 

$ds = new-object System.Data.DataSet 

$adapter.Fill($ds) 

$conn.close() 

$ds.Tables[0] 

 

  1. ##############################################################################
  2. ##
  3. ## Invoke-SqlCommand.ps1
  4. ##
  5. ## From Windows PowerShell Cookbook (O'Reilly)
  6. ## by Lee Holmes (http://www.leeholmes.com/guide)
  7. ##
  8. ## Return the results of a SQL query or operation
  9. ##
  10. ## ie:
  11. ##
  12. ##    ## Use Windows authentication
  13. ##    Invoke-SqlCommand.ps1 -Sql "SELECT TOP 10 * FROM Orders"
  14. ##
  15. ##    ## Use SQL Authentication
  16. ##    $cred = Get-Credential
  17. ##    Invoke-SqlCommand.ps1 -Sql "SELECT TOP 10 * FROM Orders" -Cred $cred
  18. ##
  19. ##    ## Perform an update
  20. ##    $server = "MYSERVER"
  21. ##    $database = "Master"
  22. ##    $sql = "UPDATE Orders SET EmployeeID = 6 WHERE OrderID = 10248"
  23. ##    Invoke-SqlCommand $server $database $sql
  24. ##
  25. ##    $sql = "EXEC SalesByCategory 'Beverages'"
  26. ##    Invoke-SqlCommand -Sql $sql
  27. ##
  28. ##    ## Access an access database
  29. ##    Invoke-SqlCommand (Resolve-Path access_test.mdb) -Sql "SELECT * FROM Users"
  30. ##   
  31. ##    ## Access an excel file
  32. ##    Invoke-SqlCommand (Resolve-Path xls_test.xls) -Sql 'SELECT * FROM [Sheet1$]'
  33. ##
  34. ##############################################################################
  36. param(
  37.     [string] $dataSource = ".\SQLEXPRESS",
  38.     [string] $database = "Northwind",      
  39.     [string] $sqlCommand = $(throw "Please specify a query."),
  40.     [System.Management.Automation.PsCredential] $credential
  41.   )
  44. ## Prepare the authentication information. By default, we pick
  45. ## Windows authentication
  46. $authentication = "Integrated Security=SSPI;"
  48. ## If the user supplies a credential, then they want SQL
  49. ## authentication
  50. if($credential)
  51. {
  52.     $plainCred = $credential.GetNetworkCredential()
  53.     $authentication =
  54.         ("uid={0};pwd={1};" -f $plainCred.Username,$plainCred.Password)
  55. }
  57. ## Prepare the connection string out of the information they
  58. ## provide
  59. $connectionString = "Provider=sqloledb; " +
  60.                     "Data Source=$dataSource; " +
  61.                     "Initial Catalog=$database; " +
  62.                     "$authentication; "
  64. ## If they specify an Access database or Excel file as the connection
  65. ## source, modify the connection string to connect to that data source
  66. if($dataSource -match '\.xls$|\.mdb$')
  67. {
  68.     $connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=$dataSource; "
  70.     if($dataSource -match '\.xls$')
  71.     {
  72.         $connectionString += 'Extended Properties="Excel 8.0;"; '
  74.         ## Generate an error if they didn't specify the sheet name properly
  75.         if($sqlCommand -notmatch '\[.+\$\]')
  76.         {
  77.             $error = 'Sheet names should be surrounded by square brackets, and ' +
  78.                        'have a dollar sign at the end: [Sheet1$]'
  79.             Write-Error $error
  80.             return
  81.         }
  82.     }
  83. }
  85. ## Connect to the data source and open it
  86. $connection = New-Object System.Data.OleDb.OleDbConnection $connectionString
  87. $command = New-Object System.Data.OleDb.OleDbCommand $sqlCommand,$connection
  88. $connection.Open()
  90. ## Fetch the results, and close the connection
  91. $adapter = New-Object System.Data.OleDb.OleDbDataAdapter $command
  92. $dataset = New-Object System.Data.DataSet
  93. [void] $adapter.Fill($dataSet)
  94. $connection.Close()
  96. ## Return all of the rows from their query
  97. $dataSet.Tables | Select-Object -Expand Rows

posted on 2009-04-13 14:13 nicktang 阅读(798) 评论(0)  编辑 收藏 引用 所属分类: Powershell

只有注册用户登录后才能发表评论。