So my recent work in Sharepoint has led me to the conclusion that I can’t ignore databases anymore. I need to get my learn on.

To that end I decided that the simplest way to teach myself about databases, specifically MSSQL, was to write a simple database application. I decided on something to track exchange 2007 mailstore sizes over time. I also have been learning powershell so I decided to use that to write to MSSQL.

Right now I have a simple flat database that I made in SQL 2005 Express with the following columns:
Name              Type
Entry                int
ServerName    nvarchar(50)
DB                  nvarchar(50)
SizeinGB         nvarchar(50)
Users              nvarchar(50)
DateStamp      nvarchar(50)

Like I said simple. I’ve been using the following script to insert data into the database.

</p>

#Create SQL client object and open a connection
$dbConn = new-object System.Data.SqlClient.SqlConnection “server=localhost\SQLExpress;database=ExchangeReports;trusted_connection=true;”
$dbConn.Open()
$sqlQuery = $dbConn.CreateCommand()


#Get data to insert
set-location C:\exchangehistory_v2.0
$file = Import-Csv mailDBdata-20100429-2106.csv


Write data to DB
$file
ForEach-Object{
$sqlQuery.CommandText = “INSERT INTO Mailstores (ServerName,DB,SizeinGB,Users,DateStamp) VALUES (‘$($_.server)’,’$($_.database)’,’$($_.sizegb)’,’$($_.usercount)’,’$($_.date)’)”
Write-Host
Write-Host $sqlQuery.CommandText
$result = $sqlQuery.ExecuteNonQuery()
}
$dbConn.Close()

Essentially it’s reading data from a CSV file into a variable and then writing the data in the variable into the database table. Very simple.

The insert statement took a while to figure out. Note, if you run into problems double check the data types you have set for your database columns. 🙂

Anyway, if I actually get this working in production I may post a more complete workup on the full database and application when it’s done.

Next up for me Sharepoint 2007 Business Data Catalog. Eeek!</span>