AccessDBProviderSample04

This sample shows how to overwrite container methods to support calls to the Copy-Item, Get-ChildItem, New-Item, and Remove-Item cmdlets. These methods should be implemented when the data store contains items that are containers. A container is a group of child items under a common parent item. The provider class in this sample derives from the System.Management.Automation.Provider.Containercmdletprovider class.

Demonstrates

Important

Your provider class will most likely derive from the System.Management.Automation.Provider.Navigationcmdletprovider

This sample demonstrates the following:

• Declaring the CmdletProvider attribute.
• Defining a provider class that derives from the System.Management.Automation.Provider.Containercmdletprovider class.
• Overwriting the System.Management.Automation.Provider.ContainerCmdletProvider.CopyItem method to change the behavior of the Copy-Item cmdlet which allows the user to copy items from one location to another. (This sample does not show how to add dynamic parameters to the Copy-Item cmdlet.)
• Overwriting the System.Management.Automation.Provider.Containercmdletprovider.Getchilditems* method to change the behavior of the Get-ChildItems cmdlet, which allows the user to retrieve the child items of the parent item. (This sample does not show how to add dynamic parameters to the Get-ChildItems cmdlet.)
• Overwriting the System.Management.Automation.Provider.Containercmdletprovider.Getchildnames* method to change the behavior of the Get-ChildItems cmdlet when the Name parameter of the cmdlet is specified.
• Overwriting the System.Management.Automation.Provider.Containercmdletprovider.Newitem* method to change the behavior of the New-Item cmdlet, which allows the user to add items to the data store. (This sample does not show how to add dynamic parameters to the New-Item cmdlet.)
• Overwriting the System.Management.Automation.Provider.Containercmdletprovider.Removeitem* method to change the behavior of the Remove-Item cmdlet. (This sample does not show how to add dynamic parameters to the Remove-Item cmdlet.)

Example

This sample shows how to overwrite the methods needed to copy, create, and remove items, as well as methods for getting the child items of a parent item.

using System;
using System.IO;
using System.Data;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Diagnostics;
using System.Collections.ObjectModel;
using System.Text;
using System.Text.RegularExpressions;
using System.Management.Automation;
using System.Management.Automation.Provider;
using System.ComponentModel;
using System.Globalization;

namespace Microsoft.Samples.PowerShell.Providers
{
   #region AccessDBProvider

    /// <summary>
   /// A PowerShell Provider which acts upon an Access database
   /// </summary>
   /// <remarks>
   /// This example implements the container overloads</remarks>
   [CmdletProvider("AccessDB", ProviderCapabilities.None)]
   public class AccessDBProvider : ContainerCmdletProvider
   {      

       #region Drive Manipulation

       /// <summary>
       /// Create a new drive.  Create a connection to the database file and set
       /// the Connection property in the PSDriveInfo.
       /// </summary>
       /// <param name="drive">
       /// Information describing the drive to add.
       /// </param>
       /// <returns>The added drive.</returns>
       protected override PSDriveInfo NewDrive(PSDriveInfo drive)
       {
           // check if drive object is null
           if (drive == null)
           {
               WriteError(new ErrorRecord(
                   new ArgumentNullException("drive"),
                   "NullDrive",
                   ErrorCategory.InvalidArgument,
                   null)
               );

               return null;
           }

           // check if drive root is not null or empty
           // and if its an existing file
           if (String.IsNullOrEmpty(drive.Root) || (File.Exists(drive.Root) == false))
           {
               WriteError(new ErrorRecord(
                   new ArgumentException("drive.Root"),
                   "NoRoot",
                   ErrorCategory.InvalidArgument,
                   drive)
               );

               return null;
           }


           // create a new drive and create an ODBC connection to the new drive
           AccessDBPSDriveInfo accessDBPSDriveInfo = new AccessDBPSDriveInfo(drive);

           OdbcConnectionStringBuilder builder = new OdbcConnectionStringBuilder();

           builder.Driver = "Microsoft Access Driver (*.mdb)";
           builder.Add("DBQ", drive.Root);

           OdbcConnection conn = new OdbcConnection(builder.ConnectionString);
           conn.Open();
           accessDBPSDriveInfo.Connection = conn;

           return accessDBPSDriveInfo;
       } // NewDrive

       /// <summary>
       /// Removes a drive from the provider.
       /// </summary>
       /// <param name="drive">The drive to remove.</param>
       /// <returns>The drive removed.</returns>
       protected override PSDriveInfo RemoveDrive(PSDriveInfo drive)
       {
           // check if drive object is null
           if (drive == null)
           {
               WriteError(new ErrorRecord(
                   new ArgumentNullException("drive"),
                   "NullDrive",
                   ErrorCategory.InvalidArgument,
                   drive)
               );

               return null;
           }

           // close ODBC connection to the drive
           AccessDBPSDriveInfo accessDBPSDriveInfo = drive as AccessDBPSDriveInfo;

           if (accessDBPSDriveInfo == null)
           {
               return null;
           }
           accessDBPSDriveInfo.Connection.Close();

           return accessDBPSDriveInfo;
       } // RemoveDrive

       #endregion Drive Manipulation

       #region Item Methods

       /// <summary>
       /// Retrieves an item using the specified path.
       /// </summary>
       /// <param name="path">The path to the item to return.</param>
       protected override void GetItem(string path)
       {
           // check if the path represented is a drive
           if (PathIsDrive(path))
           {
               WriteItemObject(this.PSDriveInfo, path, true);
               return;
           }// if (PathIsDrive...

           // Get table name and row information from the path and do 
           // necessary actions
           string tableName;
           int rowNumber;

           PathType type = GetNamesFromPath(path, out tableName, out rowNumber);

           if (type == PathType.Table)
           {
               DatabaseTableInfo table = GetTable(tableName);
               WriteItemObject(table, path, true);
           }
           else if (type == PathType.Row)
           {
               DatabaseRowInfo row = GetRow(tableName, rowNumber);
               WriteItemObject(row, path, false);
           }
           else
           {
               ThrowTerminatingInvalidPathException(path);
           }

       } // GetItem

       /// <summary>
       /// Set the content of a row of data specified by the supplied path
       /// parameter.
       /// </summary>
       /// <param name="path">Specifies the path to the row whose columns
       /// will be updated.</param>
       /// <param name="values">Comma separated string of values</param>
       protected override void SetItem(string path, object values)
       {
           // Get type, table name and row number from the path specified
           string tableName;
           int rowNumber;

           PathType type = GetNamesFromPath(path, out tableName, out rowNumber);

           if (type != PathType.Row)
           {
               WriteError(new ErrorRecord(new NotSupportedException(
                     "SetNotSupported"), "",
                  ErrorCategory.InvalidOperation, path));

               return;
           }

           // Get in-memory representation of table
           OdbcDataAdapter da = GetAdapterForTable(tableName);

           if (da == null)
           {
               return;
           }
           DataSet ds = GetDataSetForTable(da, tableName);
           DataTable table = GetDataTable(ds, tableName);

           if (rowNumber >= table.Rows.Count)
           {
               // The specified row number has to be available. If not
               // NewItem has to be used to add a new row
               throw new ArgumentException("Row specified is not available");
           } // if (rowNum...

           string[] colValues = (values as string).Split(',');

           // set the specified row
           DataRow row = table.Rows[rowNumber];

           for (int i = 0; i < colValues.Length; i++)
           {
               row[i] = colValues[i];
           }

           // Update the table
           if (ShouldProcess(path, "SetItem"))
           {
               da.Update(ds, tableName);
           }

       } // SetItem

       /// <summary>
       /// Test to see if the specified item exists.
       /// </summary>
       /// <param name="path">The path to the item to verify.</param>
       /// <returns>True if the item is found.</returns>
       protected override bool ItemExists(string path)
       {
           // check if the path represented is a drive
           if (PathIsDrive(path))
           {
               return true;
           }

           // Obtain type, table name and row number from path
           string tableName;
           int rowNumber;

           PathType type = GetNamesFromPath(path, out tableName, out rowNumber);

           DatabaseTableInfo table = GetTable(tableName);

           if (type == PathType.Table)
           {
               // if specified path represents a table then DatabaseTableInfo
               // object for the same should exist
               if (table != null)
               {
                   return true;
               }
           }
           else if (type == PathType.Row)
           {
               // if specified path represents a row then DatabaseTableInfo should
               // exist for the table and then specified row number must be within
               // the maximum row count in the table
               if (table != null && rowNumber < table.RowCount)
               {
                   return true;
               }
           }

           return false;

       } // ItemExists

       /// <summary>
       /// Test to see if the specified path is syntactically valid.
       /// </summary>
       /// <param name="path">The path to validate.</param>
       /// <returns>True if the specified path is valid.</returns>
       protected override bool IsValidPath(string path)
       {
           bool result = true;

           // check if the path is null or empty
           if (String.IsNullOrEmpty(path))
           {
               result = false;
           }

           // convert all separators in the path to a uniform one
           path = NormalizePath(path);

           // split the path into individual chunks
           string[] pathChunks = path.Split(pathSeparator.ToCharArray());

           foreach (string pathChunk in pathChunks)
           {
               if (pathChunk.Length == 0)
               {
                   result = false;
               }
           }
           return result;
       } // IsValidPath

       #endregion Item Overloads

       #region Container Overloads

       /// <summary>
       /// Return either the tables in the database or the datarows
       /// </summary>
       /// <param name="path">The path to the parent</param>
       /// <param name="recurse">True to return all child items recursively.
       /// </param>
       protected override void GetChildItems(string path, bool recurse)
       {
           // If path represented is a drive then the children in the path are 
           // tables. Hence all tables in the drive represented will have to be
           // returned
           if (PathIsDrive(path))
           {
               foreach (DatabaseTableInfo table in GetTables())
               {
                   WriteItemObject(table, path, true);

                   // if the specified item exists and recurse has been set then 
                   // all child items within it have to be obtained as well
                   if (ItemExists(path) && recurse)
                   {
                       GetChildItems(path + pathSeparator + table.Name, recurse);
                   }
               } // foreach (DatabaseTableInfo...
           } // if (PathIsDrive...
           else
           {
               // Get the table name, row number and type of path from the
               // path specified
               string tableName;
               int rowNumber;

               PathType type = GetNamesFromPath(path, out tableName, out rowNumber);

               if (type == PathType.Table)
               {
                   // Obtain all the rows within the table
                   foreach (DatabaseRowInfo row in GetRows(tableName))
                   {
                       WriteItemObject(row, path + pathSeparator + row.RowNumber,
                               false);
                   } // foreach (DatabaseRowInfo...
               }
               else if (type == PathType.Row)
               {
                   // In this case the user has directly specified a row, hence
                   // just give that particular row
                   DatabaseRowInfo row = GetRow(tableName, rowNumber);
                   WriteItemObject(row, path + pathSeparator + row.RowNumber,
                               false);
               }
               else
               {
                   // In this case, the path specified is not valid
                   ThrowTerminatingInvalidPathException(path);
               }
           } // else
       } // GetChildItems

       /// <summary>
       /// Return the names of all child items.
       /// </summary>
       /// <param name="path">The root path.</param>
       /// <param name="returnContainers">Not used.</param>
       protected override void GetChildNames(string path,
                                     ReturnContainers returnContainers)
       {
           // If the path represented is a drive, then the child items are
           // tables. get the names of all the tables in the drive.
           if (PathIsDrive(path))
           {
               foreach (DatabaseTableInfo table in GetTables())
               {
                   WriteItemObject(table.Name, path, true);
               } // foreach (DatabaseTableInfo...
           } // if (PathIsDrive...
           else
           {
               // Get type, table name and row number from path specified
               string tableName;
               int rowNumber;

               PathType type = GetNamesFromPath(path, out tableName, out rowNumber);

               if (type == PathType.Table)
               {
                   // Get all the rows in the table and then write out the 
                   // row numbers.
                   foreach (DatabaseRowInfo row in GetRows(tableName))
                   {
                       WriteItemObject(row.RowNumber, path, false);
                   } // foreach (DatabaseRowInfo...
               }
               else if (type == PathType.Row)
               {
                   // In this case the user has directly specified a row, hence
                   // just give that particular row
                   DatabaseRowInfo row = GetRow(tableName, rowNumber);

                   WriteItemObject(row.RowNumber, path, false);
               }
               else
               {
                   ThrowTerminatingInvalidPathException(path);
               }
           } // else
       } // GetChildNames

       /// <summary>
       /// Determines if the specified path has child items.
       /// </summary>
       /// <param name="path">The path to examine.</param>
       /// <returns>
       /// True if the specified path has child items.
       /// </returns>
       protected override bool HasChildItems(string path)
       {
           if (PathIsDrive(path))
           {
               return true;
           }

           return (ChunkPath(path).Length == 1);
       } // HasChildItems

       /// <summary>
       /// Creates a new item at the specified path.
       /// </summary>
       /// 
       /// <param name="path">
       /// The path to the new item.
       /// </param>
       /// 
       /// <param name="type">
       /// Type for the object to create. "Table" for creating a new table and
       /// "Row" for creating a new row in a table.
       /// </param>
       /// 
       /// <param name="newItemValue">
       /// Object for creating new instance of a type at the specified path. For
       /// creating a "Table" the object parameter is ignored and for creating
       /// a "Row" the object must be of type string which will contain comma 
       /// separated values of the rows to insert.
       /// </param>
       protected override void NewItem(string path, string type,
                                   object newItemValue)
       {
           string tableName;
           int rowNumber;

           PathType pt = GetNamesFromPath(path, out tableName, out rowNumber);

           if (pt == PathType.Invalid)
           {
               ThrowTerminatingInvalidPathException(path);
           }

           // Check if type is either "table" or "row", if not throw an 
           // exception
           if (!String.Equals(type, "table", StringComparison.OrdinalIgnoreCase)
               && !String.Equals(type, "row", StringComparison.OrdinalIgnoreCase))
           {
               WriteError(new ErrorRecord
                                 (new ArgumentException("Type must be either a table or row"),
                                     "CannotCreateSpecifiedObject",
                                        ErrorCategory.InvalidArgument,
                                             path
                                  )
                         );

               throw new ArgumentException("This provider can only create items of type \"table\" or \"row\"");
           }

           // Path type is the type of path of the container. So if a drive
           // is specified, then a table can be created under it and if a table
           // is specified, then a row can be created under it. For the sake of 
           // completeness, if a row is specified, then if the row specified by
           // the path does not exist, a new row is created. However, the row 
           // number may not match as the row numbers only get incremented based 
           // on the number of rows

           if (PathIsDrive(path))
           {
               if (String.Equals(type, "table", StringComparison.OrdinalIgnoreCase))
               {
                   // Execute command using ODBC connection to create a table
                   try
                   {
                       // create the table using an sql statement
                       string newTableName = newItemValue.ToString();

                       if (!TableNameIsValid(newTableName))
                       {
                           return;
                       }
                       string sql = "create table " + newTableName 
                                            + " (ID INT)";

                       // Create the table using the Odbc connection from the 
                       // drive.
                       AccessDBPSDriveInfo di = this.PSDriveInfo as AccessDBPSDriveInfo;

                       if (di == null)
                       {
                           return;
                       }
                       OdbcConnection connection = di.Connection;

                       if (ShouldProcess(newTableName, "create"))
                       {
                           OdbcCommand cmd = new OdbcCommand(sql, connection);
                           cmd.ExecuteScalar();
                       }
                   }
                   catch (Exception ex)
                   {
                       WriteError(new ErrorRecord(ex, "CannotCreateSpecifiedTable",
                                 ErrorCategory.InvalidOperation, path)
                                 );
                   }
               } // if (String...
               else if (String.Equals(type, "row", StringComparison.OrdinalIgnoreCase))
               {
                   throw new
                       ArgumentException("A row cannot be created under a database, specify a path that represents a Table");
               }
           }// if (PathIsDrive...
           else
           {
               if (String.Equals(type, "table", StringComparison.OrdinalIgnoreCase))
               {
                   if (rowNumber < 0)
                   {
                       throw new
                           ArgumentException("A table cannot be created within another table, specify a path that represents a database");
                   }
                   else
                   {
                       throw new
                           ArgumentException("A table cannot be created inside a row, specify a path that represents a database");
                   }
               } //if (String.Equals....
               // if path specified is a row, create a new row
               else if (String.Equals(type, "row", StringComparison.OrdinalIgnoreCase))
               {
                   // The user is required to specify the values to be inserted 
                   // into the table in a single string separated by commas
                   string value = newItemValue as string;

                   if (String.IsNullOrEmpty(value))
                   {
                       throw new
                           ArgumentException("Value argument must have comma separated values of each column in a row");
                   }
                   string[] rowValues = value.Split(',');

                   OdbcDataAdapter da = GetAdapterForTable(tableName);

                   if (da == null)
                   {
                       return;
                   }

                   DataSet ds = GetDataSetForTable(da, tableName);
                   DataTable table = GetDataTable(ds, tableName);

                   if (rowValues.Length != table.Columns.Count)
                   {
                       string message =
                            String.Format(CultureInfo.CurrentCulture,
                                            "The table has {0} columns and the value specified must have so many comma separated values",
                                                table.Columns.Count);

                       throw new ArgumentException(message);
                   }

                   if (!Force && (rowNumber >=0 && rowNumber < table.Rows.Count))
                   {
                       string message = String.Format(CultureInfo.CurrentCulture,
                                                        "The row {0} already exists. To create a new row specify row number as {1}, or specify path to a table, or use the -Force parameter",
                                                            rowNumber, table.Rows.Count);

                       throw new ArgumentException(message);
                   }
                   
                   if (rowNumber > table.Rows.Count)
                   {
                       string message = String.Format(CultureInfo.CurrentCulture,
                                            "To create a new row specify row number as {0}, or specify path to a table",
                                                table.Rows.Count);

                       throw new ArgumentException(message);
                   }

                   // Create a new row and update the row with the input
                   // provided by the user
                   DataRow row = table.NewRow();
                   for (int i = 0; i < rowValues.Length; i++)
                   {
                       row[i] = rowValues[i];
                   }
                   table.Rows.Add(row);

                   if (ShouldProcess(tableName, "update rows"))
                   {
                       // Update the table from memory back to the data source
                       da.Update(ds, tableName);
                   }

               }// else if (String...
           }// else ...

       } // NewItem

       /// <summary>
       /// Copies an item at the specified path to the location specified
       /// </summary>
       /// 
       /// <param name="path">
       /// Path of the item to copy
       /// </param>
       /// 
       /// <param name="copyPath">
       /// Path of the item to copy to
       /// </param>
       /// 
       /// <param name="recurse">
       /// Tells the provider to recurse subcontainers when copying
       /// </param>
       /// 
       protected override void CopyItem(string path, string copyPath, bool recurse)
       {
           string tableName, copyTableName;
           int rowNumber, copyRowNumber;

           PathType type = GetNamesFromPath(path, out tableName, out rowNumber);
           PathType copyType = GetNamesFromPath(copyPath, out copyTableName, out copyRowNumber);

           if (type == PathType.Invalid)
           {
               ThrowTerminatingInvalidPathException(path);
           }

           if (type == PathType.Invalid)
           {
               ThrowTerminatingInvalidPathException(copyPath);
           }

           // Get the table and the table to copy to 
           OdbcDataAdapter da = GetAdapterForTable(tableName);
           if (da == null)
           {
               return;
           }

           DataSet ds = GetDataSetForTable(da, tableName);
           DataTable table = GetDataTable(ds, tableName);
           
           OdbcDataAdapter cda = GetAdapterForTable(copyTableName);
           if (cda == null)
           {
               return;
           }

           DataSet cds = GetDataSetForTable(cda, copyTableName);
           DataTable copyTable = GetDataTable(cds, copyTableName);

           // if source represents a table
           if (type == PathType.Table)
           {
               // if copyPath does not represent a table
               if (copyType != PathType.Table)
               {
                   ArgumentException e = new ArgumentException("Table can only be copied on to another table location");

                   WriteError(new ErrorRecord(e, "PathNotValid",
                       ErrorCategory.InvalidArgument, copyPath));

                   throw e;
               }

               // if table already exists then force parameter should be set 
               // to force a copy
               if (!Force && GetTable(copyTableName) != null)
               {
                   throw new ArgumentException("Specified path already exists");
               }

               for (int i = 0; i < table.Rows.Count; i++)
               {
                   DataRow row = table.Rows[i];
                   DataRow copyRow = copyTable.NewRow();

                   copyRow.ItemArray = row.ItemArray;
                   copyTable.Rows.Add(copyRow);
               }
           } // if (type == ...
           // if source represents a row
           else
           {
               if (copyType == PathType.Row)
               {
                   if (!Force && (copyRowNumber < copyTable.Rows.Count))
                   {
                       throw new ArgumentException("Specified path already exists.");
                   }

                   DataRow row = table.Rows[rowNumber];
                   DataRow copyRow = null;

                   if (copyRowNumber < copyTable.Rows.Count)
                   {
                       // copy to an existing row
                       copyRow = copyTable.Rows[copyRowNumber];
                       copyRow.ItemArray = row.ItemArray;
                       copyRow[0] = GetNextID(copyTable);
                   }
                   else if (copyRowNumber == copyTable.Rows.Count)
                   {
                       // copy to the next row in the table that will 
                       // be created
                       copyRow = copyTable.NewRow();
                       copyRow.ItemArray = row.ItemArray;
                       copyRow[0] = GetNextID(copyTable);
                       copyTable.Rows.Add(copyRow);
                   }
                   else
                   {
                       // attempting to copy to a nonexistent row or a row
                       // that cannot be created now - throw an exception
                       string message = String.Format(CultureInfo.CurrentCulture, 
                                             "The item cannot be specified to the copied row. Specify row number as {0}, or specify a path to the table.",
                                                    table.Rows.Count);

                       throw new ArgumentException(message);
                   }
               }
               else
               {
                   // destination path specified represents a table, 
                   // create a new row and copy the item
                   DataRow copyRow = copyTable.NewRow();
                   copyRow.ItemArray = table.Rows[rowNumber].ItemArray;
                   copyRow[0] = GetNextID(copyTable);
                   copyTable.Rows.Add(copyRow);
               }
           } 

           if (ShouldProcess(copyTableName, "CopyItems"))
           {
               cda.Update(cds, copyTableName);
           }

       } //CopyItem

       /// <summary>
       /// Removes (deletes) the item at the specified path
       /// </summary>
       /// 
       /// <param name="path">
       /// The path to the item to remove.
       /// </param>
       /// 
       /// <param name="recurse">
       /// True if all children in a subtree should be removed, false if only
       /// the item at the specified path should be removed. Is applicable
       /// only for container (table) items. Its ignored otherwise (even if
       /// specified).
       /// </param>
       /// 
       /// <remarks>
       /// There are no elements in this store which are hidden from the user.
       /// Hence this method will not check for the presence of the Force
       /// parameter
       /// </remarks>
       /// 
       protected override void RemoveItem(string path, bool recurse)
       {
           string tableName;
           int rowNumber = 0;

           PathType type = GetNamesFromPath(path, out tableName, out rowNumber);
           
           if (type == PathType.Table)
           {
               // if recurse flag has been specified, delete all the rows as well
               if (recurse)
               {
                   OdbcDataAdapter da = GetAdapterForTable(tableName);
                   if (da == null)
                   {
                       return;
                   }

                   DataSet ds = GetDataSetForTable(da, tableName);
                   DataTable table = GetDataTable(ds, tableName);

                   for (int i = 0; i < table.Rows.Count; i++)
                   {
                       table.Rows[i].Delete();
                   }

                   if (ShouldProcess(path, "RemoveItem"))
                   {
                       da.Update(ds, tableName);
                       RemoveTable(tableName);
                   }
               }//if (recurse...
               else
               {
                   // Remove the table
                   if (ShouldProcess(path, "RemoveItem"))
                   {
                       RemoveTable(tableName);
                   }
               }
           }
           else if (type == PathType.Row)
           {
               OdbcDataAdapter da = GetAdapterForTable(tableName);
               if (da == null)
               {
                   return;
               }

               DataSet ds = GetDataSetForTable(da, tableName);
               DataTable table = GetDataTable(ds, tableName);

               table.Rows[rowNumber].Delete();

               if (ShouldProcess(path, "RemoveItem"))
               {
                   da.Update(ds, tableName);                   
               }
           }
           else
           {
               ThrowTerminatingInvalidPathException(path);
           }

       } // RemoveItem

       #endregion Container Overloads

       #region Helper Methods

      /// <summary>
      /// Checks if a given path is actually a drive name.
      /// </summary>
      /// <param name="path">The path to check.</param>
      /// <returns>
      /// True if the path given represents a drive, false otherwise.
      /// </returns>
      private bool PathIsDrive(string path)
      {
          // Remove the drive name and first path separator.  If the 
          // path is reduced to nothing, it is a drive. Also if its
          // just a drive then there wont be any path separators
          if (String.IsNullOrEmpty(
                      path.Replace(this.PSDriveInfo.Root, "")) ||
              String.IsNullOrEmpty(
                      path.Replace(this.PSDriveInfo.Root + pathSeparator, ""))

              )
           {
               return true;
           }
           else
           {
               return false;
           }
       } // PathIsDrive

       /// <summary>
       /// Breaks up the path into individual elements.
       /// </summary>
       /// <param name="path">The path to split.</param>
       /// <returns>An array of path segments.</returns>
       private string[] ChunkPath(string path)
       {
           // Normalize the path before splitting
           string normalPath = NormalizePath(path);

           // Return the path with the drive name and first path 
           // separator character removed, split by the path separator.
           string pathNoDrive = normalPath.Replace(this.PSDriveInfo.Root
                                          + pathSeparator, "");

           return pathNoDrive.Split(pathSeparator.ToCharArray());
       } // ChunkPath

       /// <summary>
       /// Adapts the path, making sure the correct path separator
       /// character is used.
       /// </summary>
       /// <param name="path"></param>
       /// <returns></returns>
       private string NormalizePath(string path)
       {
           string result = path;

           if (!String.IsNullOrEmpty(path))
           {
               result = path.Replace("/", pathSeparator);
           }

           return result;
       } // NormalizePath

       /// <summary>
       /// Chunks the path and returns the table name and the row number 
       /// from the path
       /// </summary>
       /// <param name="path">Path to chunk and obtain information</param>
       /// <param name="tableName">Name of the table as represented in the 
       /// path</param>
       /// <param name="rowNumber">Row number obtained from the path</param>
       /// <returns>what the path represents</returns>
       private PathType GetNamesFromPath(string path, out string tableName, out int rowNumber)
       {
           PathType retVal = PathType.Invalid;
           rowNumber = -1;
           tableName = null;

           // Check if the path specified is a drive
           if (PathIsDrive(path))
           {
               return PathType.Database;
           }

           // chunk the path into parts
           string[] pathChunks = ChunkPath(path);

           switch (pathChunks.Length)
           {
               case 1:
                   {
                       string name = pathChunks[0];

                       if (TableNameIsValid(name))
                       {
                           tableName = name;
                           retVal = PathType.Table;
                       }
                   }
                   break;

               case 2:
                   {
                       string name = pathChunks[0];

                       if (TableNameIsValid(name))
                       {
                           tableName = name;
                       }

                       int number = SafeConvertRowNumber(pathChunks[1]);

                       if (number >= 0)
                       {
                           rowNumber = number;
                           retVal = PathType.Row;
                       }
                       else
                       {
                           WriteError(new ErrorRecord(
                               new ArgumentException("Row number is not valid"),
                               "RowNumberNotValid",
                               ErrorCategory.InvalidArgument,
                               path));
                       }
                   }
                   break;

               default:
                   {
                       WriteError(new ErrorRecord(
                           new ArgumentException("The path supplied has too many segments"),
                           "PathNotValid",
                           ErrorCategory.InvalidArgument,
                           path));
                   }
                   break;
           } // switch(pathChunks...

           return retVal;
       } // GetNamesFromPath

       /// <summary>
       /// Throws an argument exception stating that the specified path does
       /// not represent either a table or a row
       /// </summary>
       /// <param name="path">path which is invalid</param>
       private void ThrowTerminatingInvalidPathException(string path)
       {
           StringBuilder message = new StringBuilder("Path must represent either a table or a row :");
           message.Append(path);

           throw new ArgumentException(message.ToString());
       }

       /// <summary>
       /// Retrieve the list of tables from the database.
       /// </summary>
       /// <returns>
       /// Collection of DatabaseTableInfo objects, each object representing
       /// information about one database table
       /// </returns>
       private Collection<DatabaseTableInfo> GetTables()
       {
           Collection<DatabaseTableInfo> results =
                   new Collection<DatabaseTableInfo>();

           // using ODBC connection to the database and get the schema of tables
           AccessDBPSDriveInfo di = this.PSDriveInfo as AccessDBPSDriveInfo;

           if (di == null)
           {
               return null;
           }

           OdbcConnection connection = di.Connection;
           DataTable dt = connection.GetSchema("Tables");
           int count;

           // iterate through all rows in the schema and create DatabaseTableInfo
           // objects which represents a table
           foreach (DataRow dr in dt.Rows)
           {
               String tableName = dr["TABLE_NAME"] as String;
               DataColumnCollection columns = null;

               // find the number of rows in the table
               try
               {
                   String cmd = "Select count(*) from \"" + tableName + "\"";
                   OdbcCommand command = new OdbcCommand(cmd, connection);

                   count = (Int32)command.ExecuteScalar();
               }
               catch
               {
                   count = 0;
               }

               // create DatabaseTableInfo object representing the table
               DatabaseTableInfo table =
                       new DatabaseTableInfo(dr, tableName, count, columns);

               results.Add(table);
           } // foreach (DataRow...

           return results;
       } // GetTables

       /// <summary>
       /// Return row information from a specified table.
       /// </summary>
       /// <param name="tableName">The name of the database table from 
       /// which to retrieve rows.</param>
       /// <returns>Collection of row information objects.</returns>
       private Collection<DatabaseRowInfo> GetRows(string tableName)
       {
           Collection<DatabaseRowInfo> results =
                       new Collection<DatabaseRowInfo>();

           // Obtain rows in the table and add it to the collection
           try
           {               
               OdbcDataAdapter da = GetAdapterForTable(tableName);

               if (da == null)
               {
                   return null;
               }

               DataSet ds = GetDataSetForTable(da, tableName);
               DataTable table = GetDataTable(ds, tableName);

               int i = 0;
               foreach (DataRow row in table.Rows)
               {
                   results.Add(new DatabaseRowInfo(row, i.ToString(CultureInfo.CurrentCulture)));
                   i++;
               } // foreach (DataRow...
           }
           catch (Exception e)
           {
               WriteError(new ErrorRecord(e, "CannotAccessSpecifiedRows",
                   ErrorCategory.InvalidOperation, tableName));
           }

           return results;

       } // GetRows

       /// <summary>
       /// Retrieve information about a single table.
       /// </summary>
       /// <param name="tableName">The table for which to retrieve 
       /// data.</param>
       /// <returns>Table information.</returns>
       private DatabaseTableInfo GetTable(string tableName)
       {
           foreach (DatabaseTableInfo table in GetTables())
           {
               if (String.Equals(tableName, table.Name, StringComparison.OrdinalIgnoreCase))
               {
                   return table;
               }
           }

           return null;
       } // GetTable

       /// <summary>
       /// Removes the specified table from the database
       /// </summary>
       /// <param name="tableName">Name of the table to remove</param>
       private void RemoveTable(string tableName)
       {
           // validate if tablename is valid and if table is present
           if (String.IsNullOrEmpty(tableName) || !TableNameIsValid(tableName) || !TableIsPresent(tableName))
           {
               return;
           }

           // Execute command using ODBC connection to remove a table
           try
           {
               // delete the table using an sql statement
               string sql = "drop table " + tableName;

               AccessDBPSDriveInfo di = this.PSDriveInfo as AccessDBPSDriveInfo;

               if (di == null)
               {
                   return;
               }
               OdbcConnection connection = di.Connection;

               OdbcCommand cmd = new OdbcCommand(sql, connection);
               cmd.ExecuteScalar();
           }
           catch (Exception ex)
           {
               WriteError(new ErrorRecord(ex, "CannotRemoveSpecifiedTable",
                         ErrorCategory.InvalidOperation, null)
                         );
           }

       } // RemoveTable

       /// <summary>
       /// Obtain a data adapter for the specified Table
       /// </summary>
       /// <param name="tableName">Name of the table to obtain the 
       /// adapter for</param>
       /// <returns>Adapter object for the specified table</returns>
       /// <remarks>An adapter serves as a bridge between a DataSet (in memory
       /// representation of table) and the data source</remarks>
       private OdbcDataAdapter GetAdapterForTable(string tableName)
       {
           OdbcDataAdapter da = null;
           AccessDBPSDriveInfo di = this.PSDriveInfo as AccessDBPSDriveInfo;

           if (di == null || !TableNameIsValid(tableName) ||!TableIsPresent(tableName))
           {
               return null;
           }

           OdbcConnection connection = di.Connection;

           try
           {
               // Create a odbc data adpater. This can be sued to update the
               // data source with the records that will be created here
               // using data sets
               string sql = "Select * from " + tableName;
               da = new OdbcDataAdapter(new OdbcCommand(sql, connection));

               // Create a odbc command builder object. This will create sql
               // commands automatically for a single table, thus
               // eliminating the need to create new sql statements for 
               // every operation to be done.
               OdbcCommandBuilder cmd = new OdbcCommandBuilder(da);

               // Set the delete cmd for the table here
               sql = "Delete from " + tableName + " where ID = ?";
               da.DeleteCommand = new OdbcCommand(sql, connection);

               // Specify a DeleteCommand parameter based on the "ID" 
               // column
               da.DeleteCommand.Parameters.Add(new OdbcParameter());
               da.DeleteCommand.Parameters[0].SourceColumn = "ID";

               // Create an InsertCommand based on the sql string
               // Insert into "tablename" values (?,?,?)" where
               // ? represents a column in the table. Note that 
               // the number of ? will be equal to the number of 
               // columnds
               DataSet ds = new DataSet();

               da.FillSchema(ds, SchemaType.Source);
               ds.Locale = CultureInfo.InvariantCulture;

               sql = "Insert into " + tableName + " values ( ";
               for (int i = 0; i < ds.Tables["Table"].Columns.Count; i++)
               {
                   sql += "?, ";
               }
               sql = sql.Substring(0, sql.Length - 2);
               sql += ")";
               da.InsertCommand = new OdbcCommand(sql, connection);

               // Create parameters for the InsertCommand based on the
               // captions of each column
               for (int i = 0; i < ds.Tables["Table"].Columns.Count; i++)
               {
                   da.InsertCommand.Parameters.Add(new OdbcParameter());
                   da.InsertCommand.Parameters[i].SourceColumn = 
                                    ds.Tables["Table"].Columns[i].Caption;
                
               }

               // Open the connection if its not already open                 
               if (connection.State != ConnectionState.Open)
               {
                   connection.Open();
               }
           }
           catch (Exception e)
           {
               WriteError(new ErrorRecord(e, "CannotAccessSpecifiedTable",
                 ErrorCategory.InvalidOperation, tableName));
           }

           return da;
       } // GetAdapterForTable

       /// <summary>
       /// Gets the DataSet (in memory representation) for the table
       /// for the specified adapter
       /// </summary>
       /// <param name="adapter">Adapter to be used for obtaining 
       /// the table</param>
       /// <param name="tableName">Name of the table for which a 
       /// DataSet is required</param>
       /// <returns>The DataSet with the filled in schema</returns>
       private DataSet GetDataSetForTable(OdbcDataAdapter adapter, string tableName)
       {
           Debug.Assert(adapter != null);

           // Create a dataset object which will provide an in-memory
           // representation of the data being worked upon in the 
           // data source. 
           DataSet ds = new DataSet();

           // Create a table named "Table" which will contain the same
           // schema as in the data source.
           //adapter.FillSchema(ds, SchemaType.Source);
           adapter.Fill(ds, tableName);
           ds.Locale = CultureInfo.InvariantCulture;

           return ds;
       } //GetDataSetForTable

       /// <summary>
       /// Get the DataTable object which can be used to operate on
       /// for the specified table in the data source
       /// </summary>
       /// <param name="ds">DataSet object which contains the tables
       /// schema</param>
       /// <param name="tableName">Name of the table</param>
       /// <returns>Corresponding DataTable object representing 
       /// the table</returns>
       /// 
       private DataTable GetDataTable(DataSet ds, string tableName)
       {
           Debug.Assert(ds != null);
           Debug.Assert(tableName != null);

           DataTable table = ds.Tables[tableName];
           table.Locale = CultureInfo.InvariantCulture;

           return table;
       } // GetDataTable

       /// <summary>
       /// Retrieves a single row from the named table.
       /// </summary>
       /// <param name="tableName">The table that contains the 
       /// numbered row.</param>
       /// <param name="row">The index of the row to return.</param>
       /// <returns>The specified table row.</returns>
       private DatabaseRowInfo GetRow(string tableName, int row)
       {
           Collection<DatabaseRowInfo> di = GetRows(tableName);

           // if the row is invalid write an appropriate error else return the 
           // corresponding row information
           if (row < di.Count && row >= 0)
           {
               return di[row];
           }
           else
           {
               WriteError(new ErrorRecord(
                  new ItemNotFoundException(),
                  "RowNotFound",
                  ErrorCategory.ObjectNotFound,
                  row.ToString(CultureInfo.CurrentCulture))
               );
           }

           return null;
       } // GetRow

       /// <summary>
       /// Method to safely convert a string representation of a row number 
       /// into its Int32 equivalent
       /// </summary>
       /// <param name="rowNumberAsStr">String representation of the row 
       /// number</param>
       /// <remarks>If there is an exception, -1 is returned</remarks>
       private int SafeConvertRowNumber(string rowNumberAsStr)
       {
           int rowNumber = -1;
           try
           {
               rowNumber = Convert.ToInt32(rowNumberAsStr, CultureInfo.CurrentCulture);
           }
           catch (FormatException fe)
           {
               WriteError(new ErrorRecord(fe, "RowStringFormatNotValid",
                   ErrorCategory.InvalidData, rowNumberAsStr));
           }
           catch (OverflowException oe)
           {
               WriteError(new ErrorRecord(oe, "RowStringConversionToNumberFailed",
                   ErrorCategory.InvalidData, rowNumberAsStr));
           }

           return rowNumber;
       } // SafeConvertRowNumber

       /// <summary>
       /// Check if a table name is valid
       /// </summary>
       /// <param name="tableName">Table name to validate</param>
       /// <remarks>Helps to check for SQL injection attacks</remarks>
       private bool TableNameIsValid(string tableName)
       {
           Regex exp = new Regex(pattern, RegexOptions.Compiled | RegexOptions.IgnoreCase);

           if (exp.IsMatch(tableName))
           {
               return true;
           }
           WriteError(new ErrorRecord(
               new ArgumentException("Table name not valid"), "TableNameNotValid",
                    ErrorCategory.InvalidArgument, tableName));
           return false;
       } // TableNameIsValid

       /// <summary>
       /// Checks to see if the specified table is present in the
       /// database
       /// </summary>
       /// <param name="tableName">Name of the table to check</param>
       /// <returns>true, if table is present, false otherwise</returns>
       private bool TableIsPresent(string tableName)
       {
           // using ODBC connection to the database and get the schema of tables
           AccessDBPSDriveInfo di = this.PSDriveInfo as AccessDBPSDriveInfo;
           if (di == null)
           {
               return false;
           }

           OdbcConnection connection = di.Connection;
           DataTable dt = connection.GetSchema("Tables");

           // check if the specified tableName is available
           // in the list of tables present in the database
           foreach (DataRow dr in dt.Rows)
           {
               string name = dr["TABLE_NAME"] as string;
               if (name.Equals(tableName, StringComparison.OrdinalIgnoreCase))
               {
                   return true;
               }
           }

           WriteError(new ErrorRecord(
               new ArgumentException("Specified Table is not present in database"), "TableNotAvailable",
                    ErrorCategory.InvalidArgument, tableName));

           return false;
       }// TableIsPresent

       /// <summary>
       /// Gets the next available ID in the table
       /// </summary>
       /// <param name="table">DataTable object representing the table to 
       /// search for ID</param>
       /// <returns>next available id</returns>
       private int GetNextID(DataTable table)
       {
           int big = 0;
           int id = 0;

           for (int i = 0; i < table.Rows.Count; i++)
           {
               DataRow row = table.Rows[i];

               object o = row["ID"];

               if (o.GetType().Name.Equals("Int16"))
               {
                   id = (int)(short)o;
               }
               else
               {
                   id = (int)o;
               }

               if (big < id)
               {
                   big = id;
               }
           }

           big++;
           return big;
       }

       #endregion Helper Methods

       #region Private Properties

       private string pathSeparator = "\\";
       private static string pattern = @"^[a-z]+[0-9]*_*$";

       private enum PathType { Database, Table, Row, Invalid };

       #endregion Private Properties
   }

   #endregion AccessDBProvider

   #region Helper Classes

  #region AccessDBPSDriveInfo

  /// <summary>
   /// Any state associated with the drive should be held here.
   /// In this case, it's the connection to the database.
   /// </summary>
   internal class AccessDBPSDriveInfo : PSDriveInfo
   {
       private OdbcConnection connection;

       /// <summary>
       /// ODBC connection information.
       /// </summary>
       public OdbcConnection Connection
       {
           get { return connection; }
           set { connection = value; }
       }

       /// <summary>
       /// Constructor that takes one argument
       /// </summary>
       /// <param name="driveInfo">Drive provided by this provider</param>
       public AccessDBPSDriveInfo(PSDriveInfo driveInfo)
           : base(driveInfo)
       { }

   } // class AccessDBPSDriveInfo

   #endregion AccessDBPSDriveInfo

   #region DatabaseTableInfo

   /// <summary>
   /// Contains information specific to the database table.
   /// Similar to the DirectoryInfo class.
   /// </summary>
   public class DatabaseTableInfo
   {
       /// <summary>
       /// Row from the "tables" schema
       /// </summary>
       public DataRow Data
       {
           get
           {
               return data;
           }
           set
           {
               data = value;
           }
       }
       private DataRow data;

       /// <summary>
       /// The table name.
       /// </summary>
       public string Name
       {
           get
           {
               return name;
           }
           set
           {
               name = value;
           }
       }
       private String name;

       /// <summary>
       /// The number of rows in the table.
       /// </summary>
       public int RowCount
       {
           get
           {
               return rowCount;
           }
           set
           {
               rowCount = value;
           }
       }
       private int rowCount;

       /// <summary>
       /// The column definitions for the table.
       /// </summary>
       public DataColumnCollection Columns
       {
           get
           {
               return columns;
           }
           set
           {
               columns = value;
           }
       }
       private DataColumnCollection columns;

       /// <summary>
       /// Constructor.
       /// </summary>
       /// <param name="row">The row definition.</param>
       /// <param name="name">The table name.</param>
       /// <param name="rowCount">The number of rows in the table.</param>
       /// <param name="columns">Information on the column tables.</param>
       public DatabaseTableInfo(DataRow row, string name, int rowCount,
                      DataColumnCollection columns)
       {
           Name = name;
           Data = row;
           RowCount = rowCount;
           Columns = columns;
       } // DatabaseTableInfo
   } // class DatabaseTableInfo

   #endregion DatabaseTableInfo

   #region DatabaseRowInfo

   /// <summary>
   /// Contains information specific to an individual table row.
   /// Analogous to the FileInfo class.
   /// </summary>
   public class DatabaseRowInfo
   {
       /// <summary>
       /// Row data information.
       /// </summary>
       public DataRow Data
       {
           get
           {
               return data;
           }
           set
           {
               data = value;
           }
       }
       private DataRow data;

       /// <summary>
       /// The row index.
       /// </summary>
       public string RowNumber
       {
           get
           {
               return rowNumber;
           }
           set
           {
               rowNumber = value;
           }
       }
       private string rowNumber;

       /// <summary>
       /// Constructor.
       /// </summary>
       /// <param name="row">The row information.</param>
       /// <param name="name">The row index.</param>
       public DatabaseRowInfo(DataRow row, string name)
       {
           RowNumber = name;
           Data = row;
       } // DatabaseRowInfo
   } // class DatabaseRowInfo

   #endregion DatabaseRowInfo

   #endregion Helper Classes
}

See Also

System.Management.Automation.Provider.Itemcmdletprovider

System.Management.Automation.Provider.Containercmdletprovider

System.Management.Automation.Provider.Navigationcmdletprovider

Designing Your Windows PowerShell Provider