PowerShell访问数据库

DAIC

访问mssql

1. $server="192.168.8.99"
   
2. $Database="test"
   
3. $user="admin"
   
4. $Password="ppp123"
   
5. $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
   
6. $CnnString ="Server = $server; Database = $Database;User Id = $user; Password = $Password"
   
7. $SqlConnection.ConnectionString = $CnnString
   
8. $CC = $SqlConnection.CreateCommand();
   
9. $cc.CommandText="select * from tables "
   
10. $da=New-Object System.Data.SqlClient.SqlDataAdapter($cc)
    
11. $ds=New-Object System.Data.DataSet
    
12. $da.fill([system.data.dataset]$ds)
    
13. #查看获取的数据
    
14. $ds.Tables
    
15. #轮询列名
    
16. foreach($table in $ds.Tables){
    
17.     foreach($column in $table.Columns){
    
18.         $column.ColumnName
    
19.     }
    
20. }
    
21. #轮询所有数据
    
22. foreach($table in $ds.Tables){
    
23.     foreach($row in $table.Rows){
    
24.         foreach ($column in $table.Columns){
    
25.             $row[$column]
    
26.         }
    
27.     }
    
28. }
    
29. #以数组的方式获取列名
    
30. $ds.Tables[0].columns[0].columnname
    
31. #以数组的方式索引数据
    
32. $ds.Tables[0].rows[0][1]

复制代码

访问mysql

1. #[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
   
2. #[void][system.reflection.Assembly]::LoadFrom("C:\Program Files\MySQL\MySQL Connector Net 6.2.4\Assemblies\MySql.Data.dll")
   
3. [void][system.reflection.Assembly]::LoadFrom("C:\Program Files\MySQL\MySQL Connector Net 5.0.9\Binaries\.NET 2.0\MySql.Data.dll")
   
4. $server="192.168.8.99"
   
5. $Database="test"
   
6. $user="admin"
   
7. $Password="ppp123"
   
8. $charset="gb2312"
   
9. $connectionString = "server=$server;uid=$user;pwd=$Password;database=$Database;charset=$charset"
   
10. $connection = New-Object MySql.Data.MySqlClient.MySqlConnection
    
11. $connection.ConnectionString = $connectionString
    
12. $sql= "SELECT * FROM aaa"
    
13. $command = New-Object MySql.Data.MySqlClient.MySqlCommand($sql, $connection)
    
14. $dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($command)
    
15. $dataSet = New-Object System.Data.DataSet
    
16. $recordCount = $dataAdapter.Fill($dataSet)
    
17. #[System.Data.DataSet]的操作同mssql
    

复制代码

http://hi.baidu.com/shrekzz/item/e75f2b0bbffe9413ebfe3859