Row Manipulation

May 4, 2010 at 1:11 PM

I feel really stupid asking this but I looked in the online help, http://www.sqlpsx.com/ and at first glance, did not see any cmdlet's that allows me to insert, delete or update a row in a table.

Is this support provided via some other cmdlet that has row actions as part of a parameter?

Please advise.

Thx

May 4, 2010 at 2:41 PM

I think I stumbled on my own answer.  The get and setsqldata is how to retrieve and update rows.  Hopefully someone will confirm this.

Coordinator
May 4, 2010 at 2:43 PM

That's correct set-sqldata is used for insert, update and delete (anything that doesn't return data) while get-sqldata is used for returning data. Both of these functions use SMO. Alternatively, if you prefer the adolib module contains several functions which use ADO.NET.

May 4, 2010 at 2:44 PM

Which is faster?  SMO or ADO?

Coordinator
May 4, 2010 at 5:06 PM

The query execution time it about the same using either SMO or ADO and both return an array of DataRow. The main difference is in connection time. It takes slightly longer to create an SMO database object than to create and ADO.NET connection, but only about 10 milliseconds. You can try yourself:

import-module sqlserver

import-module adolib

measure-command {invoke-query -server "$env:computername\sql2k8" -sql "Select GetDate()" }

measure-command {Get-SqlData -sqlserver "$env:computername\sql2k8" -dbname master -qry "Select GetDate()"}

 

May 4, 2010 at 6:04 PM

Your site does an excellent job of detailing all of the available cmdlet’s and parameters for SMO.  Do you know of one for ADO?

From: cmille19 [mailto:notifications@codeplex.com]
Sent: Tuesday, May 04, 2010 12:06 PM
To: Bruns, Joe (c)
Subject: Re: Row Manipulation [SQLPSX:211514]

From: cmille19

The query execution time it about the same using either SMO or ADO and both return an array of DataRow. The main difference is in connection time. It takes slightly longer to create an SMO database object than to create and ADO.NET connection, but only about 10 milliseconds. You can try yourself:

import-module sqlserver

import-module adolib

measure-command {invoke-query -server "$env:computername\sql2k8" -sql "Select GetDate()" }

measure-command {Get-SqlData -sqlserver "$env:computername\sql2k8" -dbname master -qry "Select GetDate()"}

Read the full discussion online.

To add a post to this discussion, reply to this email (SQLPSX@discussions.codeplex.com)

To start a new discussion for this project, email SQLPSX@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com

Coordinator
May 5, 2010 at 1:11 AM

We're working on adding online help for additional modules including adolib, but at the moment it isn't available. I hope have this done in next 30 days.