In the past I had used the sample web app to generate the classes for just those tables needed for my module. In this iteration, I decided to go the SubCommander route and generate the needed classes from the command-line. The SubSonic web-site has a batch file to handle this task, but I decided to use a command line that didn't date back to my college days. While the batch file is a workable solution, it really is showing its age, especially when compared with PowerShell.
SubCommander is an extremely powerful tool and has dozens of options and command-line parameters. Using it can be a daunting task. Fortunately, using a command-line script can significantly simplify the API, and when combined with the tab expansion features in PowerShell Plus makes it trivial to use.
function global:get-sqluser($username="", $password="", [switch]$forcesecure) {
# We are creating an object to which we'll add custom properties
$user = New-Object object | select-object UserName, Password
if ($username.length -eq 0) {
# No username was specified, so we should use Get-Credential to prompt for a user
# We also define a default username in order to suppress console output
# The results are added as synthetic properties to the PSObject we created above
$cred = Get-Credential "SqlUser"
$user.UserName = $cred.GetNetworkCredential().Username
if ($forcesecure)
{
$user.Password = $Cred.Password
}
else
{
$user.Password = $cred.GetNetworkCredential().Password
}
} else {
$newpassword = $password
# If we are using secure passwords, then we need to convert our string to a securestring
if ($forcesecure)
{
$newpassword = New-Object System.Security.SecureString
[char[]]$password | for-each {$newpassword.AppendChar($_)}
}
# In this case we can just create synthetic properties using the values passed to the function
$user.UserName = $username
$user.Password = $newpassword
}
# Return our synthetic object
$user
}
# This function uses SubSonic to generate the Data Access Layer.
function global:New-Dal(
$server = "localhost",
$db,
$username = "",
$password = "",
$namespace = "Test" ,
$lang = "vb",
$BuildPath = "D:\Batch\DAL",
$SonicPath = "D:\Program Files\SubSonic\SubSonic 2.0.3\SubCommander\",
$configfile = "",
[switch]$excludetables,
[switch]$excludeods,
[switch]$excludeviews) {
$sonic = "sonic.exe"
if ($configfile.length -gt 0)
{
if (Test-Path $configfile)
{
$generateconfig = "generate /config '$configfile'"
# Due to some difficulties with Invoke-Expression we'll change directories
# to excecute the command and then reset the directory when we are through.
$savepath = $pwd
Set-Location $SonicPath
if ($excludetables -eq $FALSE) {Invoke-Expression(".\$sonic $generateconfig")}
Set-Location $savepath
}
else
{
"Please provide a valid configuration file name when using the configfile parameter."
exit
}
}
else
{
# Use a database helper function that allows us to get a password in a secure manner.
# If the username is not an empty string then the function just returns the original username/password
$cred = get-sqluser $username $password
# Create some standard argument strings for SubCommander
$generatetables= "generatetables /override /out '$BuildPath' /lang $Lang"
$generateods= "generateODS /override /out '$BuildPath' /lang $Lang"
$generateviews = "generateviews /override /out '$BuildPath' /lang $Lang /viewStartsWith View"
"Removing and recreating the target directory: $BuildPath ..."
del $BuildPath -recurse -force
new-item $BuildPath -itemtype Directory -force | Out-Null
$username = $cred.username
$password = $cred.password
# Building our provider string. Everything is parameterized.
$Provider = "/server $server /db $db /userid $userName /password $password /generatedNamespace $namespace"
"Generate using connection string: "
"`tserver = $server"
"`tdb = $db"
"`tuserid = $userName"
"`tpassword = $password"
"`tgeneratedNamespace = $namespace"
"`n"
# Due to some difficulties with Invoke-Expression we'll change directories
# to excecute the command and then reset the directory when we are through.
$savepath = $pwd
Set-Location $SonicPath
if ($excludetables -eq $FALSE) {Invoke-Expression(".\$sonic $generatetables $provider")}
if ($excludeods -eq $FALSE) {Invoke-Expression( ".\$sonic $generateods $provider" )}
if ($excludeviews -eq $FALSE) {Invoke-Expression( ".\$sonic $generateviews $provider" )}
Set-Location $savepath
}
}
I have created a whole library of database related functions like Get-SqlUser, which I was able to re-use in the New-Dal function. To really see why PowerShell is such a powerful solution, lets look at a quick video where we see the new function in action.