Information
The topic you requested is included in another documentation set. For convenience, it's displayed below. Choose Switch to see the topic in its original location.

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 ContainerCmdletProvider class.

Demonstrates

System_CAPS_importantImportant

Your provider class will most likely derive from the NavigationCmdletProvider

This sample demonstrates the following:

  • Declaring the CmdletProvider attribute.

  • Defining a provider class that derives from the ContainerCmdletProvider class.

  • Overwriting the 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 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 GetChildNames method to change the behavior of the Get-ChildItems cmdlet when the Name parameter of the cmdlet is specified.

  • Overwriting the 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 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.

namespace Microsoft.Samples.PowerShell.Providers
{
  using System;
  using System.Collections.ObjectModel;
  using System.Data;
  using System.Data.Odbc;
  using System.Data.OleDb;
  using System.Diagnostics;
  using System.Globalization;
  using System.IO;
  using System.Management.Automation;
  using System.Management.Automation.Provider;
  using System.Text;
  using System.Text.RegularExpressions;

  #region AccessDBProvider

  /// <summary>
  /// A Windows PowerShell Provider that acts upon an Access database. 
  /// </summary>
  /// <remarks>This provider implements Drive and Item methods.</remarks>
  [CmdletProvider("AccessDB", ProviderCapabilities.None)]
  public class AccessDBProvider : ContainerCmdletProvider
  {
    #region Private Properties

    /// <summary>
    /// Characters used to valid table names.
    /// </summary>
    private static string pattern = @"^[a-z]+[0-9]*_*$";

    /// <summary>
    /// The valid path separator character.
    /// </summary>
    private string pathSeparator = "\\";

    /// <summary>
    /// Defines the types of paths to items.
    /// </summary>
    private enum PathType
    {
      /// <summary>
      /// Path to a database.
      /// </summary>
      Database,

      /// <summary>
      /// Path to a table item.
      /// </summary>
      Table,

      /// <summary>
      /// Path to a row item.
      /// </summary>
      Row,

      /// <summary>
      /// A path to an item that is not a database, table, or row.
      /// </summary>
      Invalid
    }

    #endregion Private Properties

    #region Drive Manipulation

    /// <summary>
    /// The Windows PowerShell engine calls this method when the New-Drive 
    /// cmdlet is run. This provider creates a connection to the database 
    /// file and sets the Connection property in the PSDriveInfo.
    /// </summary>
    /// <param name="drive">
    /// Information describing the drive to create.
    /// </param>
    /// <returns>An object that describes the new drive.</returns>
    protected override PSDriveInfo NewDrive(PSDriveInfo drive)
    {
      // Check to see if the drive object is null.
      if (drive == null)
      {
        WriteError(new ErrorRecord(
                                   new ArgumentNullException("drive"),
                                   "NullDrive",
                                   ErrorCategory.InvalidArgument,
                                   null));

        return null;
      }

      // Check to see if the 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;
    } // End NewDrive method.

    /// <summary>
    /// The Windows PowerShell engine calls this method when the Remove-Drive 
    /// cmdlet is run. This provider removes a drive from the Access database.
    /// </summary>
    /// <param name="drive">The drive to remove.</param>
    /// <returns>The drive to be removed.</returns>
    protected override PSDriveInfo RemoveDrive(PSDriveInfo drive)
    {
      // Check to see if the drive object is null.
      if (drive == null)
      {
        WriteError(new ErrorRecord(
                                   new ArgumentNullException("drive"),
                                   "NullDrive",
                                   ErrorCategory.InvalidArgument,
                                   drive));

        return null;
      }

      // Close the ODBC connection to the drive.
      AccessDBPSDriveInfo accessDBPSDriveInfo = drive as AccessDBPSDriveInfo;

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

      accessDBPSDriveInfo.Connection.Close();
      return accessDBPSDriveInfo;
    } // End RemoveDrive method.

    #endregion Drive Manipulation

    #region Item Methods

    /// <summary>
    /// The Windows PowerShell engine calls this method when the Get-Item 
    /// cmdlet is run.
    /// </summary>
    /// <param name="path">The path to the item to return.</param>
    protected override void GetItem(string path)
    {
      // Check to see if the supplied path is a drive.
      if (this.PathIsDrive(path))
      {
        WriteItemObject(this.PSDriveInfo, path, true);
        return;
      } // End if (PathIsDrive...) block.

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

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

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

    /// <summary>
    /// The Windows PowerShell engine calls this method when the Set-Item 
    /// cmdlet is run. This provider sets 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 = this.GetNamesFromPath(path, out tableName, out rowNumber);

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

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

      if (da == null)
      {
        return;
      }

      DataSet ds = this.GetDataSetForTable(da, tableName);
      DataTable table = this.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");
      } // End if (rowNum...) block.

      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);
      }
    } // End the SetItem method.

    /// <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 to see if the path represented is a drive.
      if (this.PathIsDrive(path))
      {
        return true;
      }

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

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

      DatabaseTableInfo table = this.GetTable(tableName);

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

      return false;
    } // End ItemExists method.

    /// <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 to see if the path is null or empty.
      if (String.IsNullOrEmpty(path))
      {
        result = false;
      }

      // Convert all separators in the path to a uniform chracter.
      path = this.NormalizePath(path);

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

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

      return result;
    } // End IsValidPath method.

    #endregion Item Overloads

    #region Container Overloads

    /// <summary>
    /// The Windows PowerShell engine calls this method when the Get-ChildItem 
    /// cmdlet is run. This provider returns either the tables in the database 
    /// or the rows of the table.
    /// </summary>
    /// <param name="path">The path to the parent item.</param>
    /// <param name="recurse">A Boolean value that indicates true to return all 
    /// child items recursively.
    /// </param>
    protected override void GetChildItems(string path, bool recurse)
    {
      // If the 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 (this.PathIsDrive(path))
      {
        foreach (DatabaseTableInfo table in this.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 (this.ItemExists(path) && recurse)
          {
            this.GetChildItems(path + this.pathSeparator + table.Name, recurse);
          }
        } // End foreach (DatabaseTableInfo...) block.
      }
      else
      {
        // Get the table name, row number and type of path from the
        // path specified
        string tableName;
        int rowNumber;

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

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

    /// <summary>
    /// Returns the names of all the child items.
    /// </summary>
    /// <param name="path">The root path.</param>
    /// <param name="returnContainers">Parameter 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 (this.PathIsDrive(path))
      {
        foreach (DatabaseTableInfo table in this.GetTables())
        {
          WriteItemObject(table.Name, path, true);
        } // End foreach (DatabaseTableInfo...) block.
      } 
      else
      {
        // Get type, table name and row number from path specified
        string tableName;
        int rowNumber;

        PathType type = this.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 this.GetRows(tableName))
          {
            WriteItemObject(row.RowNumber, path, false);
          } // End foreach (DatabaseRowInfo...) block.
        }
        else if (type == PathType.Row)
        {
          // In this case the user has directly specified a row, hence
          // just give that particular row.
          DatabaseRowInfo row = this.GetRow(tableName, rowNumber);
          WriteItemObject(row.RowNumber, path, false);
        }
        else
        {
          this.ThrowTerminatingInvalidPathException(path);
        }
      } // End else block.
    } // End GetChildNames method.

    /// <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 (this.PathIsDrive(path))
      {
        return true;
      }

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

    /// <summary>
    /// The Windows PowerShell engine calls this method when the New-Item 
    /// cmdlet is run. This method creates a new item at the specified path.
    /// </summary>
    /// <param name="path">The path to the new item.</param>
    /// <param name="type">The type of object to create. A "Table" object 
    /// for creating a new table and a "Row" object for creating a new row 
    /// in a table.
    /// </param>
    /// <param name="newItemValue">
    /// Object for creating a new instance 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 = this.GetNamesFromPath(path, out tableName, out rowNumber);

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

      // Check to see if the 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\"");
      }

      // The 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 and 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 (this.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 (!this.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));
          }
        } 
        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");
        }
      } 
      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");
          }
        } 
        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 = this.GetAdapterForTable(tableName);

          if (da == null)
          {
            return;
          }

          DataSet ds = this.GetDataSetForTable(da, tableName);
          DataTable table = this.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);
          }
        } // End else if (String...) block.
      } // End else block.
    } // End NewItem method.

    /// <summary>
    /// The Windows PowerShell engine calls this method when the Copy-Item 
    /// cmdlet is run. This method copies an item at the specified path to 
    /// the location specified.
    /// </summary>
    /// <param name="path">
    /// Path to the item to copy.
    /// </param>
    /// <param name="copyPath">
    /// Path to 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 = this.GetNamesFromPath(path, out tableName, out rowNumber);
      PathType copyType = this.GetNamesFromPath(copyPath, out copyTableName, out copyRowNumber);

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

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

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

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

      OdbcDataAdapter cda = this.GetAdapterForTable(copyTableName);
      if (cda == null)
      {
        return;
      }

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

      // If the 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 the table already exists then force parameter should be set 
        // to force a copy.
        if (!Force && this.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);
        }
      } 
      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] = this.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] = this.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] = this.GetNextID(copyTable);
          copyTable.Rows.Add(copyRow);
        }
      } 

      if (ShouldProcess(copyTableName, "CopyItems"))
      {
        cda.Update(cds, copyTableName);
      }
    } // End CopyItem method.

    /// <summary>
    /// The Windows PowerShell engine calls this method when the Remove-Item 
    /// cmdlet is run. This method 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 = this.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 = this.GetAdapterForTable(tableName);
          if (da == null)
          {
            return;
          }

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

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

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

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

        table.Rows[rowNumber].Delete();

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

    #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.Empty)) ||
                      String.IsNullOrEmpty(
                      path.Replace(this.PSDriveInfo.Root + this.pathSeparator, string.Empty)))
      {
        return true;
      }
      else
      {
        return false;
      }
    } // End PathIsDrive method.

    /// <summary>
    /// Separates 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 = this.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 + this.pathSeparator, 
                                  string.Empty);

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

    /// <summary>
    /// Makes sure that the correct path separator character is used.
    /// </summary>
    /// <param name="path">The path to be checked.</param>
    /// <returns>The path with the correct separator.</returns>
    private string NormalizePath(string path)
    {
      string result = path;

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

      return result;
    } // End NormalizePath method.

    /// <summary>
    /// Chunks the path and returns the table name and the row number 
    /// from the path.
    /// </summary>
    /// <param name="path">Path to chunk and to obtain information from.</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 to see if the path specified is a drive.
      if (this.PathIsDrive(path))
      {
        return PathType.Database;
      }

      // Chunk the path into parts.
      string[] pathChunks = this.ChunkPath(path);

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

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

              break;

        case 2:
              {
                string name = pathChunks[0];
                if (this.TableNameIsValid(name))
                {
                  tableName = name;
                }

                int number = this.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;
      } // End switch(pathChunks...) block.

      return retVal;
    } // End GetNamesFromPath method.

    /// <summary>
    /// Throws an argument exception stating that the specified path does
    /// not represent either a table or a row.
    /// </summary>
    /// <param name="path">Path that is not valid.</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 = (int)command.ExecuteScalar();
        }
        catch
        {
          count = 0;
        }

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

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

      return results;
    } // End GetTables method.

    /// <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 = this.GetAdapterForTable(tableName);

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

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

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

      return results;
    } // End GetRows method.

    /// <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 this.GetTables())
      {
        if (String.Equals(tableName, table.Name, StringComparison.OrdinalIgnoreCase))
        {
          return table;
        }
      }

      return null;
    } // End GetTable method.

    /// <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) || !this.TableNameIsValid(tableName) || !this.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));
      }
     } // End RemoveTable method.

    /// <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 || !this.TableNameIsValid(tableName) || !this.TableIsPresent(tableName))
      {
        return null;
      }

      OdbcConnection connection = di.Connection;

      try
      {
        // Create an ODBC data adapter. This can be used 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 an 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;
    } // End GetAdapterForTable method.

    /// <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)
    {
      // 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.Fill(ds, tableName);
      ds.Locale = CultureInfo.InvariantCulture;

      return ds;
    } // End GetDataSetForTable method.

    /// <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)
    {
      DataTable table = ds.Tables[tableName];
      table.Locale = CultureInfo.InvariantCulture;

      return table;
    } // End GetDataTable method.

    /// <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 = this.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;
    } // End GetRow method.

    /// <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>
    /// <returns>The integer equivalint row number.</returns>
    /// <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;
    } // End SafeConvertRowNumber method.

    /// <summary>
    /// Check to see if a table name is valid.
    /// </summary>
    /// <param name="tableName">Table name to validate</param>
    /// <returns>True if the table name is valid.</returns>
    /// <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;
    } // End TableNameIsValid method.

    /// <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 to see 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;
    } // End TableIsPresent method.

    /// <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;
    } // End GetNextId method.
    #endregion Helper Methods
  }

  #endregion AccessDBProvider

  #region Helper Classes

  #region DatabaseTableInfo

  /// <summary>
  /// Contains information specific to the database table.
  /// Similar to the DirectoryInfo class.
  /// </summary>
  public class DatabaseTableInfo
  {
    /// <summary>
    /// Data about a row in a table.
    /// </summary>
    private DataRow data;

    /// <summary>
    /// The name of a table in the database.
    /// </summary>
    private string name;

    /// <summary>
    /// The number of rows in a table.
    /// </summary>
    private int rowCount;

    /// <summary>
    /// Data about the columns in a table.
    /// </summary>
    private DataColumnCollection columns;

    /// <summary>
    /// Initializes a new instance of the DatabaseTableInfo class.
    /// </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)
    {
        this.Name = name;
        this.Data = row;
        this.RowCount = rowCount;
        this.Columns = columns;
    } // End DatabaseTableInfo constructor.

    /// <summary>
    /// Gets or sets the row data from the "tables" schema.
    /// </summary>
    public DataRow Data
    {
      get
      {
        return this.data;
      }

      set
      {
        this.data = value;
      }
    }

    /// <summary>
    /// Gets or sets the table name.
    /// </summary>
    public string Name
    {
      get
      {
        return this.name;
      }

      set
      {
        this.name = value;
      }
    }

    /// <summary>
    /// Gets or sets the number of rows in the table.
    /// </summary>
    public int RowCount
    {
      get
      {
        return this.rowCount;
      }

      set
      {
        this.rowCount = value;
      }
    }

    /// <summary>
    /// Gets or sets the column definitions for the table.
    /// </summary>
    public DataColumnCollection Columns
    {
      get
      {
        return this.columns;
      }

      set
      {
         this.columns = value;
      }
    }
  } // End DatabaseTableInfo class.

  #endregion DatabaseTableInfo

  #region DatabaseRowInfo

  /// <summary>
  /// Contains information specific to an individual table row.
  /// Analogous to the FileInfo class.
  /// </summary>
  public class DatabaseRowInfo
  {
    /// <summary>
    /// Gets or sets data information.
    /// </summary>
    public DataRow Data
    {
          get
          {
              return this.data;
          }

          set
          {
              this.data = value;
          }
      }

      private DataRow data;

      /// <summary>
      /// Gets or sets the row index.
      /// </summary>
      public string RowNumber
      {
          get
          {
              return this.rowNumber;
          }

          set
          {
              this.rowNumber = value;
          }
      }

      private string rowNumber;

      /// <summary>
      /// Initializes a new instance of the DatabaseRowInfo class.
      /// </summary>
      /// <param name="row">The row information.</param>
      /// <param name="name">The row index.</param>
      public DatabaseRowInfo(DataRow row, string name)
      {
          this.RowNumber = name;
          this.Data = row;
      } // DatabaseRowInfo
  } // class DatabaseRowInfo

  #endregion DatabaseRowInfo

  #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
  {
    /// <summary>
    /// Describes the ODBC connection.
    /// </summary>
    private OdbcConnection connection;

    /// <summary>
    /// Initializes a new instance of the AccessDBPSDriveInfo class.
    /// </summary>
    /// <param name="driveInfo">Drive provided by this provider</param>
    public AccessDBPSDriveInfo(PSDriveInfo driveInfo)
        : base(driveInfo)
    {
    }

    /// <summary>
    /// Gets or sets the ODBC connection information.
    /// </summary>
    public OdbcConnection Connection
    {
      get { return this.connection; }
      set { this.connection = value; }
    }
  } // End AccessDBPSDriveInfo class.

  #endregion AccessDBPSDriveInfo

  #endregion Helper Classes
}

Show: