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:
Like I said simple. I’ve been using the following script to insert data into the database.
#Create SQL client object and open a connection
$dbConn = new-object System.Data.SqlClient.SqlConnection “server=localhost\SQLExpress;database=ExchangeReports;trusted_connection=true;”
$sqlQuery = $dbConn.CreateCommand()
#Get data to insert
$file = Import-Csv mailDBdata-20100429-2106.csv
Write data to DB
$sqlQuery.CommandText = “INSERT INTO Mailstores (ServerName,DB,SizeinGB,Users,DateStamp) VALUES (‘$($_.server)’,’$($_.database)’,’$($_.sizegb)’,’$($_.usercount)’,’$($_.date)’)”
$result = $sqlQuery.ExecuteNonQuery()
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>