PowerShell 数据库MSSQL MYSQL操作简易示例代码

MSSQL封装模块

$conn 
function open{
    param($db,$server,$usr,$passwd)    
    $global:conn=New-Object System.Data.SqlClient.SqlConnection
    $global:conn.ConnectionString="Data Source=$server;Initial Catalog=$db;user id=$usr;pwd=$passwd"
    $global:conn.Open()
    #$global:conn
    "连接数据库成功"
}
function execute($sql){
    $cmd=New-Object System.Data.SqlClient.SqlCommand($sql,$conn)   
    $cmd.ExecuteNonQuery()
    "执行成功"
}
function scalar($sql){
    $cmd=$conn.CreateCommand() 
    $cmd.CommandText=$sql 
    $cmd.ExecuteScalar() 
}
function query($sql){
    $cmd=New-Object System.Data.SqlClient.SqlCommand($sql,$conn)    
    $adpt=new-object System.Data.SqlClient.SqlDataAdapter($cmd)
    $set=New-Object System.Data.DataSet
    $adpt.Fill($set)
    $set[0].Tables[0]
    #$cmd.ExecuteNonQuery()
}
function close{
    $global:conn.Close()
    "关闭成功"
}

调用模块

#Invoke-Expression .\mssql.ps1
. .\mssql.ps1
hello
open -db "##" -server "192.168.##.##" -usr "sa" -passwd "#######"
query -sql "select * from people where ###='###'"
scalar -sql "select  count(*)  from people "

MYSQL封装模块

#如果是安装了MySQL .Net Connector
[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
#如果是直接通过MySQL.Data.dll文件
#$mySQLDataDLL = "C:\scripts\mysql\MySQL.Data.dll"
#[void][system.reflection.Assembly]::LoadFrom($mySQLDataDLL)
$conn 
function open{
    param($db,$server,$usr,$passwd)    
    $global:conn=New-Object MySql.Data.MySqlClient.MySqlConnection
    $global:conn.ConnectionString="Server=$server;Uid=$usr;Pwd=$passwd;database=$db;"
    $global:conn.Open()
    #$global:conn
    "连接数据库成功"
}
function execute($sql){
    $cmd=New-Object MySql.Data.MySqlClient.MySqlCommand($sql,$conn)   
    $cmd.ExecuteNonQuery()
    "执行成功"
}
function scalar($sql){
    $cmd=$conn.CreateCommand() 
    $cmd.CommandText=$sql 
    $cmd.ExecuteScalar() 
}
function query($sql){
    $cmd=New-Object MySql.Data.MySqlClient.MySqlCommand($sql,$conn) 
    $adpt=new-object MySql.Data.MySqlClient.MySqlDataAdapter($cmd)    
    $set=New-Object System.Data.DataSet
    $adpt.Fill($set)
    $set[0].Tables[0]
    #$cmd.ExecuteNonQuery()
}
function close{
    $global:conn.Close()
    "关闭成功"
}

调用模块

#Invoke-Expression .\mysql.ps1
. .\mysql.ps1
open -db "###" -server "192.168.##.##" -usr "root" -passwd "######"
query -sql "select * from staff where ###='###'"
scalar -sql "select count(*) from staff"