Powershell访问SQL Server数据库代码实例

支持所有版本的sqlserver。

你是否需要连接数据库？这里有一段代码演示如何查询和获取sql数据，只需非常简单正确的配置你的账户信息、服务器地址及sql语句就行：

$database                       = 'name_of_sqldatabase'
$server                         = '192.168.100.200'
$username                         = 'databaseusername'
$password                       = 'secretpassword'
 
$sqlquery                       = 'select * from testtable'
 
# accessing data base
$sqlconnection                  = new-object -typename system.data.sqlclient.sqlconnection
$sqlconnection.connectionstring = "data source=$server;initial catalog=$database;user id=$username;pwd=$password"
$sqlcmd                         = new-object system.data.sqlclient.sqlcommand
$sqlcmd.commandtext             = $sqlquery
$sqlcmd.connection              = $sqlconnection
$sqladapter                     = new-object system.data.sqlclient.sqldataadapter
$sqladapter.selectcommand       = $sqlcmd
$set                            = new-object data.dataset
 
# filling dataset
$sqladapter.fill($set)
 
# consuming data
$path = "$env:temp\report.hta"
$set.tables[0] | convertto-html | out-file -filepath $path
 
invoke-item -path $path