Applied PowerSMO! I

Now that we have PowerSMO! we can start making use of it. The first example will be building a test database. Whenever I work on an new database application or write labs for a course that involves databases I need to make test database with some data in them. T-SQL is just fine for defining tables and such, but as soon as I want to fill those tables with some sample data it gets a bit tedious… I just want to directly inject some C# into my T-SQL to manipulate strings, generate random data and so on. Now with PowerSMO! I can, in effect, do just that.

This blog article assumes you have some familiarity with PowerSMO!, SQL Server, and PowerShell. The purpose of this blog article is to show how to make use of PowerSMO! to do some typical database operations.

We start by making a database.

PS C:\demos> $server = SMO_Server
PS C:\demos> $testdb = SMO_Database $server "TestDB_1"
PS C:\demos> $testdb.DatabaseOptions.RecoveryModel="Simple"
PS C:\demos> $testdb.Create()
PS C:\demos>

To start with we need a reference to an instance of SQL Server. We are using the get-SMO_Server function from PowerSMO! to get this reference. Note that short form of the function is used, dropping the “get-” prefix. This is a handy feature of PowerShell.

$testdb is the test database we are making. SMO_Database requires a reference to a server and a name. I usually have a common prefix for test database names, we will how that is useful shortly. Also I set the RecoveryMode to “Simple” so I don’t end up with a big log on database that is really a throwaway anyhow. Lastly the $testdb doesn’t really exist until Create() is called on it, that’s what makes SMO issue the appropriate T-SQL commands to the server to create the database.

Since I, and probably you too, have a standard way to create a test database we should we should capture our ad hoc script in a function so we can reuse it. Actually we are going to make two functions here, one to create a test database name, and another to make an actual test database. We will see shortly that this will make it a lot easier to maintain things over time.

function global:get-TestDatabaseName
([string]$suffix)
{
"TestDB_{0}" -f $suffix;
}
function global:new-TestDatabase
(
[Microsoft.SqlServer.Management.Smo.Server]$server,
[string]$name_suffix)
{
$name = get-TestDatabaseName $name_suffix;
$testdb = SMO_Database $server $name;
$testdb.DatabaseOptions.RecoveryModel="Simple";
$testdb.Create();
}

get-TestDatabaseName is used to create a name for a test database. All test databases have the prefix “TestDB_”. The -f operator is the PowerShell formatting operator. It replaces {0} with the first parameter that follows it and {1} with the second and so on.

The new-TestDatabase requires a Server and a string as input. The body of the function duplicates our ad hoc script but uses the get-TestDatabaseName to generate the name of the database we want to add. Let’s try it out…

PS C:\demos> new-TestDatabase $server 3
PS C:\demos> new-TestDatabase $server 4
PS C:\demos> new-TestDatabase $server 5

It’s pretty easy to add a lot of databases. In fact you will probably find that often you end up with a lot of trash databases you want to get rid of and sometimes you just want to clean out your test databases and start over. Because we have a standard prefix for out test database that fairly easy to do. To make things even easier let’s add a function that finds all of our test databases.

function global:get-TestDatabases
([Microsoft.SqlServer.Management.Smo.Server]$server)
{
$pat = get-TestDatabaseName "*";
$server.Databases | ?{$_.Name -like $pat}
}

The get-TestDatabases function makes a pattern for test database names using the get-TestDatabaseName function. It then passes the databases it finds in $server through a -like filter that uses this pattern to eliminate the database that are not test databases.

PS C:\demos> get-TestDataBases $server | %{$_.name}
TestDB_1
TestDB_2
TestDB_3
TestDB_4
TestDB_5
PS C:\demos>

Now we can see we have made a good sized population of test databases. However it is pretty easy to get rid of all of them.

PS C:\demos> TestDatabases $server | %{$_.Drop()}
PS C:\demos> TestDatabases $server | %{$_.name}
PS C:\demos>

We use the get-TestDatabases function to pipe each of the databases into a script that calls the Drop() method on each one. A quick check shows we were successful. Ok, let’s put our test database back into the server for the rest of the things we want to do.

PS C:\demos> new-TestDatabase $server 1
PS C:\demos> $testdb = $server.Databases[(TestDatabaseName 1)]
PS C:\demos> $testdb.Name
TestDB_1
PS C:\demos>

My test systems have a few Windows users, Dawn, Don, and SqlAdmin, that I use for testing. They are all ordinary Windows users with no special priveleges and the all have logins on the Sql Server instance I’m going to do testing with. We can check to see if they are there easily.

PS C:\demos> $server.logins |  %{$_.name}
Ambler
AuditLogin
BUILTIN\Administrators
Frank
Joe
MyAsmLogin
NT AUTHORITY\SYSTEM
PARSEC5\Administrator
PARSEC5\Dawn
PARSEC5\Don
PARSEC5\SqlAdmin
PARSEC5\SQLServer2005MSFTEUser$PARSEC5$MSSQLSERVER
PARSEC5\SQLServer2005MSSQLUser$PARSEC5$MSSQLSERVER
PARSEC5\SQLServer2005SQLAgentUser$PARSEC5$MSSQLSERVER
sa
PS C:\demos>

If you look about half-way down the list you will see Dawn, Don and SqlAdmin. Of course we can refine this a bit more to list only the logins we are interested in.

PS C:\demos> $server.logins | 
    ?{$_.name -like "*\Dawn" -or $_.name -like "*\Don"
    -or $_.name -like "*\SqlAdmin"} | %{$_.name}
PARSEC5\Dawn
PARSEC5\Don
PARSEC5\SqlAdmin
PS C:\demos>

Here we use the logical -or operator and the -like pattern matching operator to filter out the logins to just the standard ones we use. In fact we should make add this to a library of functions we use when we build test databases.

function global:Get-StandardTestLogins
([Microsoft.SqlServer.Management.Smo.Server]$server)
{
$server.logins | 
    ?{$_.name -like "*\Dawn" -or 
    $_.name -like "*\Don" -or
    $_.name -like "*\SqlAdmin"}
}

The Get-StandardTestLogin function is a bit different from the ad hoc script we put together; It outputs a login object instead of just a name. Note that it uses a typed parameter for input, it requries a Server as input parameter. We can still use it to get the list of names though, even though it outputs login objects.

PS C:\demos> StandardTestLogins $server | %{$_.name}
PARSEC5\Dawn
PARSEC5\Don
PARSEC5\SqlAdmin
PS C:\demos>

If it turns out our test logins are not on the system we could use PowerShell to add them. PowerShell integrates support for WMI, Window Management Instrumentation, and is a conventional way to script new users into a Windows system or enterprise. We are not going to cover those features in this blog article though.

There is one last thing we have to check for out test logins. As the name implies SqlAdmin is supposed to be in the sysadmin role for SqlServer. That’s straight forward to check.

PS C:\demos> $server.logins["PARSEC5\SqlAdmin"].IsMember("sysadmin")
True
PS C:\demos>

Looks like we are good to go for our test logins.

Now that we know that our standard test logins are there, lets add the corresponding users to our test database.

PS C:\demos> foreach ($login in StandardTestLogins $server)
>> {
>> $user = SMO_User $testdb $login.name
>> $user.login = $login.name
>> $user.Create()
>> }
>>
PS C:\demos> $testdb.users | %{$_.name}
dbo
guest
INFORMATION_SCHEMA
PARSEC5\Dawn
PARSEC5\Don
PARSEC5\SqlAdmin
sys
PS C:\demos>

We use the PowerShell foreach command to iterate through each of our test logins. We then make a new user, $user, in the $testdb with a name the same as the login name, which is a pretty typical way to add users to a database. Then we fill out the login property of $user with the corresponding login name. Last we call the Create() method on $user. A SMO user object, like just about all new objects in SMO, do not exist in the database until after the Create() method has been called on them.

We confirm that our test users were added by listing the names of the users in $testdb.

One of the nice things about having the Get-StandardTestLogins is that we can use it to create the users for our test databases. This allows us to to keep track of out standard test logins in one place, we don’t need to constantly copy the list of them everywhere we need them.

We should turn this foreach loop into a function so we can re-use for future test databases.

function global:new-StandardTestUsers
([Microsoft.SqlServer.Management.Smo.Database]$database)
{
foreach ($login in StandardTestLogins $database.Parent)
{
$user = SMO_User $database $login.name
$user.login = $login.name
$user.Create()
}
}

Here the new-StandardTestUsers functions requires that a database be passed into it. The body of the function is the same as the ad hoc script we wrote except that the reference to the server is gotten from the $database itself. Now, as you can see below, we just pass in a reference to our test database to the new-StandardTestUsers function to add all of our test users.

PS C:\demos> new-StandardTestUsers $testdb
PS C:\demos>

Now to finish out our test database we will add a table using SMO objects, then populate the table with some random data. The table will have an order number, a customer name and value column. The order number column will be the PRIMARY KEY.

First of all we need to make a table. In case you don’t remember to parameters to construct a table the get-SMO_ctors function will remind you.

PS C:\demos> SMO_ctors (SMOT_Table)
Table()
Table(Database database, String name)
Table(Database database, String name, String schema)
PS C:\demos>

We are not going to work with database schemas in this blog article, I’ll save that for a later one. We’ll use the second constructor.

PS C:\demos> $orders = SMO_Table $testdb "Orders"
PS C:\demos>

Now that we have a table we need to create the columns for it. Again get-SMOctors can be used to find out what parameters we need to pass to the get-SMOColumn function.

PS C:\demos> SMO_ctors (SMOT_column)
Column()
Column(SqlSmoObject parent, String name)
Column(SqlSmoObject parent, String name, DataType dataType)
PS C:\demos>

We can create a column, specify it name and type in one operation. We will need to make a DataType, so let’s check what the constructor options are for it.

PS C:\demos> smo_ctors (SMOT_DataType)
DataType()
DataType(SqlDataType sqlDataType)
DataType(SqlDataType sqlDataType, Int32 precisionOrMaxLength)
DataType(SqlDataType sqlDataType, Int32 precision, Int32 scale)
DataType(SqlDataType sqlDataType, String type)
DataType(SqlDataType sqlDataType, String type, String schema)
DataType(XmlSchemaCollection xmlSchemaCollection)
DataType(UserDefinedDataType userDefinedDataType)
DataType(UserDefinedType userDefinedType)

This, in turn requires us to make a SqlDataType, which is an enum. We can look to see what the possible enumerated values are for this too.

PS C:\demos> SMO_enum (SMOT_SqlDataType)
value__
None
BigInt
Binary
Bit
Char
DateTime
Decimal
Float
Image
Int
Money
NChar
NText
NVarChar
NVarCharMax
Real
SmallDateTime
SmallInt
SmallMoney
Text
Timestamp
TinyInt
UniqueIdentifier
UserDefinedDataType
UserDefinedType
VarBinary
VarBinaryMax
VarChar
VarCharMax
Variant
Xml
SysName
Numeric
PS C:\demos>

No big suprise here, it has all the SQL datatypes were are use to using in SQL Server. Now we can make some columns and add them to our $orders table.

PS C:\demos> $orders = SMO_Table $testdb "Orders"
PS C:\demos> $order_number = SMO_Column $orders "Order Number"      
    (SMO_DataType "Int")
PS C:\demos> $order_number = SMO_Column $orders "Order Number" 
    (SMO_DataType "Int")
PS C:\demos> $orders.Columns.Add($order_number)
PS C:\demos> $orders.Columns.Add($customer_name)
PS C:\demos> $value = SMO_Column $orders "Value"
    (SMO_DataType "Money")
PS C:\demos> $orders.Columns.Add($value)

First we create a table named “Orders”. A reference to the table is in variable $orders. Each column is made using the SMOColumn function. Note that the datatype for the column is defined using the SMODataType function.

Now that we have our table we need to make the $order_number column a primary key.

PS C:\demos> $pk = SMO_Index $orders "Orders__PK"
PS C:\demos> smo_enum (SMOT_IndexKeyType)
value__
None
DriPrimaryKey
DriUniqueKey
PS C:\demos> $pk.IndexKeyType=(SMOT_IndexKeyType)::DriPrimaryKey
PS C:\demos> $inx_col = SMO_IndexedColumn $pk "Order Number"
PS C:\demos> $pk.IndexedColumns.Add($inx_col)
PS C:\demos> $orders.Indexes.Add($pk)
PS C:\demos> $orders.Create()
PS C:\demos>

The index takes a little more work. First we make an SMOIndex.and name it “OrdersPK”. An index might include more than one column and those columns are specified in SMOIndexedColumn objects. We need to make an SMOIndexed column for each column in the index. This index is only a single column, however, so we only need to create a single SMOIndexedColumn. The SMOIndexedColumn is added to the index, $pk'. Once we have added the SMO_IndexedColumn to the SMO_Index we can add the index to the$orders` table.

Lastly we call the Create() method on $orders to make the table on the server.

Now we can add some data to the table. We can make use of the System.Random class from the .NET framework to generate some of the data. There aren’t any “row” objects for tables in SMO. We just use standard T-SQL to do inserts to add data to a table.

To fill out our table we need to produce some triples that consist of an order number, customer name, and a value. We have 30 customers with 1000 orders whose value ranges from 1 to 1000.00. With PowerShell we can use a pipeline to generate triples. Here is a simple example that generates 10 random triples.

PS C:\demos> (1..10) | %{"{0} : {1} : {2}" -f $_, 
    $rand.next(1,10), $rand.next()}
1 : 5 : 2119806400
2 : 6 : 1944455664
3 : 7 : 323953648
4 : 9 : 1680451911
5 : 4 : 820794382
6 : 4 : 1793724046
7 : 8 : 1206229307
8 : 5 : 1466323995
9 : 6 : 1123175179
10 : 1 : 1559943559
PS C:\demos>

The first part of the pipeline is an array, initialized with values from 1 to 10. The second part of the pipeline formats the numbers from the array along with some random numbers. Now we will use this technique generate the T-SQL insert statments. To get started let’s make a helper function that will create the insert statement from three input parameters.

PS C:\demos> function get-insert
>> ($number, $customer, $value)
>> {
>> "INSERT INTO Orders Values ({0}, '{1}', {2})" -f
>> $number, $customer, $value
>> }
>>
PS C:\demos> insert 1 "joe" 102.32
INSERT INTO Orders Values (1, 'joe', 102.32)
PS C:\demos>

The get-insert function uses the -f operator to build an insert statement from the three parameters passed into it.

PS C:\demos> (1..10) |
    %{insert $_ ("Name_" + $rand.next(1,30)) 
    ($rand.next(1,1000000)/100.1)}
INSERT INTO Orders Values (1, 'Name_15', 1127.23276723277)
INSERT INTO Orders Values (2, 'Name_12', 9753.04695304695)
INSERT INTO Orders Values (3, 'Name_23', 9560.27972027972)
INSERT INTO Orders Values (4, 'Name_15', 2843.98601398601)
INSERT INTO Orders Values (5, 'Name_13', 2798.22177822178)
INSERT INTO Orders Values (6, 'Name_13', 4531.40859140859)
INSERT INTO Orders Values (7, 'Name_11', 4825.18481518482)
INSERT INTO Orders Values (8, 'Name_26', 8539.9000999001)
INSERT INTO Orders Values (9, 'Name_22', 9863.88611388611)
INSERT INTO Orders Values (10, 'Name_10', 4556.79320679321)
PS C:\demos>

Here we have used the get-insert function generate ten insert’s. Next we need a way to execute those insert statements. A SMO database object has a number methods that can execute a SQL statement, much like a SqlCommand does in ADO.NET. In this case we will use the ExecuteNonQuery method of $database to insert 1000 orders.

PS C:\demos> (1..1000) | 
    %{insert $_ ("Name_" + $rand.next(1,30))
    ($rand.next(1,1000000)/100.1)} | 
    %{$testdb.ExecuteNonQuery($_)}
PS C:\demos>

We put an array with a thousand numbers into a pipeline, each number is used as an order number. The customer name is just the string “name_” with a suffix that is a random number from 1 to 30. And then for the value we generate a random number. These constructed values are passed into the get-insert function to make an insert statement. The insert statement is then used a the parameter to the ExectureNonQuery method on $testdb.

We can see the row count of the table is now 1000.

PS C:\demos> $orders.refresh()
PS C:\demos> $orders.rowcount
1000
PS C:\demos>

Note that before we check the rowcount we refresh the $orders table.

To finish up we will make use of the fact that can also execute a select statement against the Orders database.

PS C:\demos> $dataset = $testdb.ExecuteWithResults(
    "SELECT * FROM Orders")
PS C:\demos> $table = $dataset.tables[0]
PS C:\demos> $table.Rows | format-table -autosize
Order Number Customer Name     Value
------------ -------------     -----
           1 Name_29       1837.6124
           2 Name_27       3723.3666
           3 Name_1        3221.3886
...

The ExecuteWithResults method of $database returns an ADO Dataset. We can extract the rows from the first table in the Dataset and pipe them into a PowerShell format-table command to see what they contain.

We have seen that we can use PowerSMO! to create a test database, add tables to it and then fill these tables with some random data. Along the way we have seen how to use PowerSMO! to do a number of typical database operation like checking out what logins exist or adding users to database.

Well that’s it for this article, there will be more later.

Dan dan@pluralsight.com