function OPDB{
param(
[alias('f')]$funName,
[alias('q')]$sql
)
$DBHost="服务器地址"
$DBName="数据库名"
$DBUser="账号"
$DBPwd="密码"
$conn = New-Object System.Data.SqlClient.SqlConnection
$sqlconn = "server=$DBHost;uid=$DBUser;pwd=$DBPwd;database=$DBName;charset=gbk"
$conn.ConnectionString=$sqlconn
$conn.open()
$cmd=new-object System.Data.SqlClient.SqlCommand
$cmd.Connection=$conn
#region Set-Data use update and insert
function Set-Data{
$cmd.CommandText=$sql
[void]$cmd.ExecuteNonQuery()
}
#endregion
#region Get-Data
function Get-Data{
$cmd.commandtext=$sql
$SqlAdapter = New-Object mysql.Data.mySqlClient.mySqlDataAdapter
$SqlAdapter.SelectCommand = $cmd
$DataSet = New-Object System.Data.DataSet
[void]$SqlAdapter.Fill($DataSet,"data")
return $dataset.tables['data']
}
#endregion
#判断传入值,并执行相应函数
if($funName -eq 'set'){
Set-Data
}
if($funName -eq 'get'){
Get-Data
}
$conn.Close()
}
#执行查询操作
OPDB get "SQL语句" | Export-Csv d:\query.csv -NoTypeInformation -Encoding Default
#执行插入修改操作
OPDB set "SQL语句"