Monday, April 28, 2014

How to use Powershell to create a database using ADOX

function GetValue
{
    param
    (
    [string]$Name,
    [object]$obj
    )

    [string]$PName = $Name + " = "
    [string]$tempstr = $obj.GetObjectText_(0)

    $pos = $tempstr.IndexOf($PName)
    if ($pos -gt 0)
    {
        $pos = $pos + $PName.Length
        $tempstr = $tempstr.SubString($pos, ($tempstr.Length - $pos))
        $pos = $tempstr.IndexOf(";")
        $tempstr = $tempstr.SubString(0, $pos)
        $tempstr = $tempstr.Replace('"', "")
        $tempstr = $tempstr.Replace("}", "")
        $tempstr = $tempstr.Replace("{", "")
        $tempstr = $tempstr.Trim()
        if($tempstr.Length -gt 14)
        {
            if($obj.Properties_.Item($Name).CIMType -eq 101)
            {
                [System.String]$tstr = $tempstr.SubString(4, 2)
                $tstr = $tstr + "/"
                $tstr = $tstr + $tempstr.SubString(6, 2)
                $tstr = $tstr + "/"
                $tstr = $tstr + $tempstr.SubString(0, 4)
                $tstr = $tstr + " "
                $tstr = $tstr + $tempstr.SubString(8, 2)
                $tstr = $tstr + ":"
                $tstr = $tstr + $tempstr.SubString(10, 2)
                $tstr = $tstr + ":"
                $tstr = $tstr + $tempstr.SubString(12, 2)
                $tempstr = $tstr
            }
        }
        return $tempstr
    }
    else
    {
        return ""
    }
}


    $l = new-object -comobject Wbemscripting.SWbemLocator
    $svc = $l.ConnectServer(".", "root\cimv2")
    $svc.Security_.AuthenticationLevel=6
    $svc.Security_.ImpersonationLevel=3
    $ob = $svc.Get("Win32_Process")

    $ocat = new-object -comobject ADOX.Catalog
    $ocat.Create("Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + System.Environment.CurrentDirectory + "\Process.mdb")
    $otable = new-object -comobject ADOX.Table
    $otable.Name = "Processes_properties"

    foreach($prop in $ob.Properties_)
    {
        $ocolumn = new-object -comobject ADOX.Column
        $oColumn.Name = $prop.Name
        $oColumn.Type = 203
        $otable.Columns.Append($oColumn)
    }

    $ocat.Tables.Append($otable)
   
    $objs = $ob.Instances_(0)

    $rs = new-object -comobject ADODB.Recordset
    $rs.ActiveConnection = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + System.Environment.CurrentDirectory + "\Process.mdb"
    $rs.CursorLocation = 3
    $rs.LockType = 3
    $rs.Source = "Select * From Processes_Properties"
    $rs.Open()

    foreach($obj in $objs)
    {
        $rs.AddNew()
        foreach($prop in $obj.Properties_)
        {
            $rs.Fields($prop.Name).Value = GetValue $prop.Name $obj
        }
        $rs.Update()
    }

No comments:

Post a Comment