PowerShell连接SQL SERVER数据库实现代码

1、核心代码

#配置信息

$Database = 'DemoDB'

$Server   = '"WIN-AHAU9NO5R6U\DOG"'

$UserName = 'kk'

$Password = '123456'

#创建连接对象

$SqlConn = New-Object System.Data.SqlClient.SqlConnection

#使用账号连接MSSQL

$SqlConn.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;user id=$UserName;pwd=$Password"

#或者以 windows 认证连接 MSSQL

#$SqlConn.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;Integrated Security=SSPI;"

#打开数据库连接

$SqlConn.open()

#执行语句方法一

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

$SqlCmd.connection = $SqlConn

$SqlCmd.commandtext = 'delete top(1) from dbo.B'

$SqlCmd.executenonquery()

#执行语句方法二

$SqlCmd = $SqlConn.CreateCommand()

$SqlCmd.commandtext = 'delete top(1) from dbo.B'

$SqlCmd.ExecuteScalar()

#方法三，查询显示

$SqlCmd.commandtext = 'select name,recovery_model_desc,log_reuse_wait_desc from sys.databases'

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

$SqlAdapter.SelectCommand = $SqlCmd

$set = New-Object data.dataset

$SqlAdapter.Fill($set)

$set.Tables[0] | Format-Table -Auto 

#关闭数据库连接

$SqlConn.close()

2、微软的官方帮助文档：https://technet.microsoft.com/zh-cn/magazine/hh289310.aspx