AccessDBProviderSample03
This sample shows how to overwrite the
System.Management.Automation.Provider.Itemcmdletprovider.Getitem*
and
System.Management.Automation.Provider.Itemcmdletprovider.Setitem*
methods to support calls to the Get-Item
and Set-Item
cmdlets. The provider class in this sample
derives from the
System.Management.Automation.Provider.Itemcmdletprovider
class.
Demonstrates
Important
Your provider class will most likely derive from one of the following classes and possibly implement other provider interfaces:
- System.Management.Automation.Provider.Itemcmdletprovider class.
- System.Management.Automation.Provider.Containercmdletprovider class. See AccessDBProviderSample04.
- System.Management.Automation.Provider.Navigationcmdletprovider class. See AccessDBProviderSample05.
For more information about choosing which provider class to derive from based on provider features, see Designing Your Windows PowerShell Provider.
This sample demonstrates the following:
- Declaring the
CmdletProvider
attribute. - Defining a provider class that derives from the System.Management.Automation.Provider.Itemcmdletprovider class.
- Overwriting the
System.Management.Automation.Provider.Drivecmdletprovider.Newdrive*
method to change the behavior of the
New-PSDrive
cmdlet, allowing the user to create new drives. (This sample does not show how to add dynamic parameters to theNew-PSDrive
cmdlet.) - Overwriting the System.Management.Automation.Provider.Drivecmdletprovider.Removedrive* method to support removing existing drives.
- Overwriting the
System.Management.Automation.Provider.Itemcmdletprovider.Getitem*
method to change the behavior of the
Get-Item
cmdlet, allowing the user to retrieve items from the data store. (This sample does not show how to add dynamic parameters to theGet-Item
cmdlet.) - Overwriting the
System.Management.Automation.Provider.Itemcmdletprovider.Setitem*
method to change the behavior of the
Set-Item
cmdlet, allowing the user to update the items in the data store. (This sample does not show how to add dynamic parameters to theGet-Item
cmdlet.) - Overwriting the
System.Management.Automation.Provider.Itemcmdletprovider.Itemexists*
method to change the behavior of the
Test-Path
cmdlet. (This sample does not show how to add dynamic parameters to theTest-Path
cmdlet.) - Overwriting the System.Management.Automation.Provider.Itemcmdletprovider.Isvalidpath* method to determine if the provided path is valid.
Example
This sample shows how to overwrite the methods needed to get and set items in a Microsoft Access data base.
using System;
using System.IO;
using System.Data;
using System.Data.Odbc;
using System.Collections.ObjectModel;
using System.Text;
using System.Diagnostics;
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 a access database.
/// </summary>
/// <remarks>
/// This example implements the item overloads.
/// </remarks>
[CmdletProvider("AccessDB", ProviderCapabilities.None)]
public class AccessDBProvider : ItemCmdletProvider
{
#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 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>
/// 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);
// 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
#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