Chris Rauber - Using Miado with Powershell

this.Blog.Find(entry => entry.IsHelpful);

Wednesday, April 30, 2008

Using Miado with Powershell

One of the nice things about Miado is that it wraps a lot of tedious, repetitive ADO.Net code and therefore greatly reduces the amount of code you actually do need to write.

Here's a quick example. One of the tasks I had to do at my last last gig was upload a set of prices from a CSV file every month. I wrote a PowerShell script to use Miado to do the DB inserts:

function BuildSql()
{
# build the SQL
$sb = New-Object System.Text.StringBuilder
$sb.Append("insert into VendorPrice(VendorID, FixedPrice, VariablePrice, ");
$sb.Append("CreationDate, LastUpdatedDate) ");
$sb.Append("values(@VendorID, @FixedPrice, @VariablePrice, ");
$sb.Append("current_timestamp, current_timestamp)");
return $sb.ToString();
}
[Reflection.Assembly]::LoadFile("C:\dev\VS2008\Miado\src\Miado\bin\Release\Miado.dll")
# setup the configuration
$connString = "Data Source=.\SQLEXPRESS;Initial Catalog=MyDatabase;User Id=user;Password=passw0rd"
$dbProvider = System.Data.SqlClient.SqlClientFactory::Instance;
$repository = Miado.MiadoRepositoryFactory::CreateMiadoRepository($dbProvider, $connString);
$sql = BuildSql;
foreach ( $row in Import-Csv prices.csv | Select VendorID, FixedPrice, VariablePrice )
{
$repository.ExecuteNonQuery($sql.ToString(), $row.VendorID, $row.FixedPrice, $row.VariablePrice);
}

As you can see, the script iterates over each row in the CSV file and inserts a record for each row. There's no need to write all the code necessary to create the DbConnection and DbCommand objects, nor do you have to dynamically create DbParameter objects for each row. Just pass the SQL and the variables (in order of replacement) into the IMiadoRepository .ExecuteNonQuery() call and Miado takes care of all the ugly details. One line for each insert - that's it!

Edit: This post was updated to reflect the changes in the 0.7.10530.1