引述

Data Access


In the recent Scripting Games event 5 involved taking a list of scores from an Access database and calculating a number of statistical values from the data.

## open database connection

$conn = New-Object System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=scores.mdb")

$conn.Open()

 

## mean score

$cmd = New-Object System.Data.OleDb.OleDbCommand("Select Avg(Score) FROM Results", $conn)

$avg = [System.Math]::Truncate($cmd.ExecuteScalar())

 

## get maximum

$cmd = New-Object System.Data.OleDb.OleDbCommand("Select Max(Score) FROM Results", $conn)

$max = $cmd.ExecuteScalar()

 

## get minimum

$cmd = New-Object System.Data.OleDb.OleDbCommand("Select Min(Score) FROM Results", $conn)

$min = $cmd.ExecuteScalar()

 

## get the set of values

$cmd = New-Object System.Data.OleDb.OleDbCommand("Select Score FROM Results", $conn)

$data = $cmd.ExecuteReader()

 

$values = @()

while ($data.read()) {$values += $data.GetValue(0)}

 

$data.Close()

 

## calculate mode

$mode = ($values | sort | group | sort count -desc | select Name -first 1 )

 

## calculate median

$srtd = ($values | sort)

$med = $srtd[([System.Math]::Truncate(($srtd.length/2) + 0.5))]

 

$conn.Close()

## output results

Write-Host "Mean: " $avg

Write-Host "Mode: " $mode.name

Write-Host "Median: " $med

Write-Host "Highest score: " $max

Write-Host "Lowest score: " $min

I have used ADO.NET to access the database. At its simplest ADO.NET consists of a number of objects:



  • Connection

  • Command

  • Datareader


There are a number of data providers that support these objects including:



  • SQL – for SQL Server

  • OLE DB – for Access and other ole db sources

  • Oracle


In this instance we will use the ole db provider in order to utilise the Access database.

We start by creating and opening a connection to the database using System.Data.OleDb.OleDbConnection

The mean, minimum and maximum are all calculated in similar fashion. A command is created including the SQL need for the calculation and then a ScalarReader is executed. A scalarreader will only return a single result.

Note that for the mean we are using a truncate math function to only return the integer portion of the result as per the event instructions. In reality we would probably want the complete decimal form of the answer.

Calculating the mode is a little more involved. If there was an index on the data it would be trivial to write the SQL to calculate the mode (the value that occurs most often). As the index does not exist we do it the hard way.

Start by getting all of the values into an array. Notice we use ExecuteReader() rather than ExecuteScalar(). This allows us to retrieve all of the data and then step through the data. Having got the values into an array we use a pipeline to calculate the mode. We sort the values and then group them. Sorting on the grouped data allows us to find the group with the most members by a simple select statement.

The median is determined by sorting the array and taking the middle value.

The connection to the database is closed and the results output.

The really useful things from this event are the method of connecting to a database and retrieving data and using the pipeline for some of the calculations.

創作者介紹

PowerShell@HungYuWu

ayowu 發表在 痞客邦 PIXNET 留言(0) 人氣()