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
Important |
|---|
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 }
