Lyris ListManager & PowerShell

When working with Lyris ListManager this week along with an existing account automation process, I ran into the issue of needing to automatically subscribing people to a list which has restrictions in place to require the use of confirmation messages generally. Well with a programmatic solution in mind, send & replying to e-mail confirmation messages just won’t cut it. Below is a simple sample of how to with PowerShell automatically enroll users into an existing ListManager list.

function ListEnrollment
{
	param([array]$myuser)
	$myconn = New-Object System.Data.SqlClient.SqlConnection("Data Source=LYRIS-DBServer; Initial Catalog=Lyris; Integrated Security=SSPI")
	$myconn.Open()
	$mycmd = $myconn.CreateCommand()
	
	$mydate = Get-Date -Format "yyyy-mm-dd HH:mm:ss"
	$mydisplay = "$myuser[2] $myuser[3]"
	
	$mycmd.CommandText = "Insert Into members_ (Domain_,UserNameLC_,EmailAddr_,FullName_,List_,DateJoined_) VALUES ('MyDomain','$myuser[0]','$myuser[1]','$mydisplay','MyListName','$mydate')"
	$mylog += $mycmd.CommandText
	Write-Host $mycmd.CommandText
	$mycmd.ExecuteNonQuery()
	$myconn.Close()
}

To call this function just use

ListEnrollment("username","email","FirstName","LastName")

This offers up a fairly simple solution to automate this potentially cumbersome manual process. As written this will use whatever credentials the PowerShell process is running to authenticate against the database.

If wanted, you can ignore the DateJoined_ and FullName_ columns as they are not required though useful. The Lyris database itself has triggers built-in upon insertion to do the heavy lifting for the rest of the required columns.