A lightweight SQL Helper Powershell Module (@DevScope)

Recently at DevScope we developed a nice powershell module to query databases that I want to share because its been of great use to me lately.

What differentiates this module is its simplicity and lighweight, with it you can:

    • Query any .Net Data Provider with only one cmdlet (SQLClient, OracleClient, OLEDB, ODBC,…)
      • For instance, with the same cmdlet you can query an Excel spreadsheet and insert its data into a SQL Server database
    • Easily execute a bulk copy into a SQL Server Database

You can download the module and a sample of usage at DevScope BI Samples CodePlex site:

https://devscopebisamples.codeplex.com/SourceControl/changeset/view/25843#576361

Examples of usage:

Query an Excel Spreadsheet


$dataSet = Invoke-DBCommand -providerName "System.Data.OleDb" `
		-connectionString "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='$currentPath\ExcelData.xlsx';Extended Properties=Excel 12.0" `
		-commandText "select * from [Sheet1$]" -verbose

Insert into SQL Server


$dataSet.Tables[0].Rows |% {	
	$numRows = Invoke-DBCommand -providerName "System.Data.SqlClient" `
		-connectionString "Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Dummy;Data Source=.\sql2012" `
		-executeType "NonQuery" `
		-commandText "insert into dbo.Products values (@id, @name, @datecreated)" `
		-parameters @{"@id"=$_.ProductKey;"@name"=$_.EnglishProductName;"@datecreated"=[datetime]::Now}					
}

Bulk Copy into SQL Server


Invoke-SqlBulkCopy -connectionString "Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Dummy;Data Source=.\sql2012" `
		-tableName "dbo.Products" `
		-data $dataSet.Tables[0] `
		-columnMappings @{"ProductKey" = "Id"; "EnglishProductName" = "Name"} -verbose
About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s