PowerShell  ADO 

PowerShellADO is an ADO.NET Provider that can execute PowerShell commands and return the results as an ADO.NET data reader or dataset.

Using the PowerShellADO Adapter

Using PowerShellADO is similar to using any other ADO.NET provider. You can use connections, commands, data readers and data adapters.

Here's an example of using the PowerShellADO Adapter from code: 

									using ( PowerShellADOConnection conn = new PowerShellADOConnection("Data Source=(local)") ) {
									   string select = "select * from 'ls c:\\' where Length >= @p1";
									   PowerShellADOCommand cmd = new PowerShellADOCommand(select, conn);
									   cmd.Parameters.AddWithValue("@p1", 4000);
									   PowerShellADODataAdapter adapter = new PowerShellADODataAdapter(cmd);
									   using ( adapter ) {
										  DataTable table = new DataTable();
										  adapter.Fill(table);
										  // use table
									   }
									}

The PowerShellADO Adapter can be configured directly from the property designer without writing additional code. For example, using the PowerShellADO Data Source in ASP.NET is as easy as setting ConnectionString='Data Source=(local)', and then setting the SelectCommandText property to valid PowerShell Script:

The default behavior for PowerShellADO commands is a SQL-like syntax with the following elements:

  • Column names in the select clause map to properties in the returned PowerShell objects. If you specify "*" instead of an explicit list, all properties are returned.
  • The table name in the SQL statement corresponds with the PowerShell expression being evaluated and should be properly quoted.
  • The where clause can specify complex conditions including the "and" and "or" operators.
  • Command parameters can be specified with the @ prefix in the where clause.

PowerShelADO in ASP.NET

To use the PowerShellADO adapter In ASP.NET:

  1. Add a reference to the PowerShellADO Data Source, or drag the Data Source from the Visual Studio Toolbox onto your ASPX page in design time.
  2. Set the ConnectionString and SelectCommandText through the PowerShellADO Data Source property designer or through code.
  3. Finally, bind objects to the PowerShellADO Data Source and DataBind those objects

The result is that any ASP.NET objects can bind to PowerShell Scripts which can be dynamically updated through the SelectCommandText.


PowerShellADO In Winforms

Using the PowerShellADO Adapter in Winforms applications is identical to configuring other ADO.NET Adapters. Add a reference to the PowerShellADO Adapter or drag the Adapter from the Visual Studio Toolbox onto your winforms application. Then configure the Adapter with PowerShell Script in the Select statement and bind objects to the data source.

An example of a GridView bound to the PowerShellADO Adapter in a winforms application (demo included):



Executing Raw Scripts

In some cases, you might not want to depend on the SQL-like syntax used above. As an alternative, you can also have PowerShellADO execute a raw, complex, PowerShell expression. This is accomplished by setting the CommandType property of the PowerShellADOCommand object to CommandType.TableDirect. Here's an example: 

									using ( PowerShellADOConnection conn = new PowerShellADOConnection("Data Source=(local)") ) {
									   string select = "ls HKCU: | ?{ $_.Name.Length -gt $minlen } | select Name";
									   PowerShellADOCommand cmd = new PowerShellADOCommand(select, conn);
									   cmd.Parameters.AddWithValue("@minlen", 6);
									   PowerShellADODataAdapter adapter = new PowerShellADODataAdapter(cmd);
									   using ( adapter ) {
										  DataTable table = new DataTable();
										  adapter.Fill(table);
										  // use table
									   }
									}

In TableDirect mode, parameters added to the command will be inserted as PowerShell variables by changing the @ prefix for the $ prefix and thus accessible in any part of the PowerShell expression.