AccessDbProviderSample04 Code Sample
The following code shows the implementation of the Windows PowerShell provider described in Creating a Windows PowerShell Container Provider. This provider works on multi-layer data stores. For this type of data store, the top level of the store contains the root items and each subsequent level is referred to as a node of child items. By allowing the user to work on these child nodes, a user can interact hierarchically through the data store.
Code Sample
using System;
using System.IO;
using System.Data;
using System.Data.Odbc;
using System.Data.OleDb;
using System.Diagnostics;
using System.Collections.ObjectModel;
using System.Text;
using System.Text.RegularExpressions;
using System.Management.Automation;
using System.Management.Automation.Provider;
using System.ComponentModel;
using System.Globalization;
namespace Microsoft.Samples.PowerShell.Providers
{
#region AccessDBProvider
/// <summary>
/// A PowerShell Provider which acts upon an Access database
/// </summary>
/// <remarks>
/// This example implements the container overloads</remarks>
[CmdletProvider("AccessDB", ProviderCapabilities.None)]
public class AccessDBProvider : ContainerCmdletProvider
{
#region Drive Manipulation
/// <summary>
/// Create a new drive. Create a connection to the database file and set
/// the Connection property in the PSDriveInfo.
/// </summary>
/// <param name="drive">
/// Information describing the drive to add.
/// </param>
/// <returns>The added drive.</returns>
protected override PSDriveInfo NewDrive(PSDriveInfo drive)
{
// check if drive object is null
if (drive == null)
{
WriteError(new ErrorRecord(
new ArgumentNullException("drive"),
"NullDrive",
ErrorCategory.InvalidArgument,
null)
);
return null;
}
// check if drive root is not null or empty
// and if its an existing file
if (String.IsNullOrEmpty(drive.Root) || (File.Exists(drive.Root) == false))
{
WriteError(new ErrorRecord(
new ArgumentException("drive.Root"),
"NoRoot",
ErrorCategory.InvalidArgument,
drive)
);
return null;
}
// create a new drive and create an ODBC connection to the new drive
AccessDBPSDriveInfo accessDBPSDriveInfo = new AccessDBPSDriveInfo(drive);
OdbcConnectionStringBuilder builder = new OdbcConnectionStringBuilder();
builder.Driver = "Microsoft Access Driver (*.mdb)";
builder.Add("DBQ", drive.Root);
OdbcConnection conn = new OdbcConnection(builder.ConnectionString);
conn.Open();
accessDBPSDriveInfo.Connection = conn;
return accessDBPSDriveInfo;
} // NewDrive
/// <summary>
/// Removes a drive from the provider.
/// </summary>
/// <param name="drive">The drive to remove.</param>
/// <returns>The drive removed.</returns>
protected override PSDriveInfo RemoveDrive(PSDriveInfo drive)
{
// check if drive object is null
if (drive == null)
{
WriteError(new ErrorRecord(
new ArgumentNullException("drive"),
"NullDrive",
ErrorCategory.InvalidArgument,
drive)
);
return null;
}
// close ODBC connection to the drive
AccessDBPSDriveInfo accessDBPSDriveInfo = drive as AccessDBPSDriveInfo;
if (accessDBPSDriveInfo == null)
{
return null;
}
accessDBPSDriveInfo.Connection.Close();
return accessDBPSDriveInfo;
} // RemoveDrive
#endregion Drive Manipulation
#region Item Methods
/// <summary>
/// Retrieves an item using the specified path.
/// </summary>
/// <param name="path">The path to the item to return.</param>
protected override void GetItem(string path)
{
// check if the path represented is a drive
if (PathIsDrive(path))
{
WriteItemObject(this.PSDriveInfo, path, true);
return;
}// if (PathIsDrive...
// Get table name and row information from the path and do
// necessary actions
string tableName;
int rowNumber;
PathType type = GetNamesFromPath(path, out tableName, out rowNumber);
if (type == PathType.Table)
{
DatabaseTableInfo table = GetTable(tableName);
WriteItemObject(table, path, true);
}
else if (type == PathType.Row)
{
DatabaseRowInfo row = GetRow(tableName, rowNumber);
WriteItemObject(row, path, false);
}
else
{
ThrowTerminatingInvalidPathException(path);
}
} // GetItem
/// <summary>
/// Set the content of a row of data specified by the supplied path
/// parameter.
/// </summary>
/// <param name="path">Specifies the path to the row whose columns
/// will be updated.</param>
/// <param name="values">Comma separated string of values</param>
protected override void SetItem(string path, object values)
{
// Get type, table name and row number from the path specified
string tableName;
int rowNumber;
PathType type = GetNamesFromPath(path, out tableName, out rowNumber);
if (type != PathType.Row)
{
WriteError(new ErrorRecord(new NotSupportedException(
"SetNotSupported"), "",
ErrorCategory.InvalidOperation, path));
return;
}
// Get in-memory representation of table
OdbcDataAdapter da = GetAdapterForTable(tableName);
if (da == null)
{
return;
}
DataSet ds = GetDataSetForTable(da, tableName);
DataTable table = GetDataTable(ds, tableName);
if (rowNumber >= table.Rows.Count)
{
// The specified row number has to be available. If not
// NewItem has to be used to add a new row
throw new ArgumentException("Row specified is not available");
} // if (rowNum...
string[] colValues = (values as string).Split(',');
// set the specified row
DataRow row = table.Rows[rowNumber];
for (int i = 0; i < colValues.Length; i++)
{
row[i] = colValues[i];
}
// Update the table
if (ShouldProcess(path, "SetItem"))
{
da.Update(ds, tableName);
}
} // SetItem
/// <summary>
/// Test to see if the specified item exists.
/// </summary>
/// <param name="path">The path to the item to verify.</param>
/// <returns>True if the item is found.</returns>
protected override bool ItemExists(string path)
{
// check if the path represented is a drive
if (PathIsDrive(path))
{
return true;
}
// Obtain type, table name and row number from path
string tableName;
int rowNumber;
PathType type = GetNamesFromPath(path, out tableName, out rowNumber);
DatabaseTableInfo table = GetTable(tableName);
if (type == PathType.Table)
{
// if specified path represents a table then DatabaseTableInfo
// object for the same should exist
if (table != null)
{
return true;
}
}
else if (type == PathType.Row)
{
// if specified path represents a row then DatabaseTableInfo should
// exist for the table and then specified row number must be within
// the maximum row count in the table
if (table != null && rowNumber < table.RowCount)
{
return true;
}
}
return false;
} // ItemExists
/// <summary>
/// Test to see if the specified path is syntactically valid.
/// </summary>
/// <param name="path">The path to validate.</param>
/// <returns>True if the specified path is valid.</returns>
protected override bool IsValidPath(string path)
{
bool result = true;
// check if the path is null or empty
if (String.IsNullOrEmpty(path))
{
result = false;
}
// convert all separators in the path to a uniform one
path = NormalizePath(path);
// split the path into individual chunks
string[] pathChunks = path.Split(pathSeparator.ToCharArray());
foreach (string pathChunk in pathChunks)
{
if (pathChunk.Length == 0)
{
result = false;
}
}
return result;
} // IsValidPath
#endregion Item Overloads
#region Container Overloads
/// <summary>
/// Return either the tables in the database or the datarows
/// </summary>
/// <param name="path">The path to the parent</param>
/// <param name="recurse">True to return all child items recursively.
/// </param>
protected override void GetChildItems(string path, bool recurse)
{
// If path represented is a drive then the children in the path are
// tables. Hence all tables in the drive represented will have to be
// returned
if (PathIsDrive(path))
{
foreach (DatabaseTableInfo table in GetTables())
{
WriteItemObject(table, path, true);
// if the specified item exists and recurse has been set then
// all child items within it have to be obtained as well
if (ItemExists(path) && recurse)
{
GetChildItems(path + pathSeparator + table.Name, recurse);
}
} // foreach (DatabaseTableInfo...
} // if (PathIsDrive...
else
{
// Get the table name, row number and type of path from the
// path specified
string tableName;
int rowNumber;
PathType type = GetNamesFromPath(path, out tableName, out rowNumber);
if (type == PathType.Table)
{
// Obtain all the rows within the table
foreach (DatabaseRowInfo row in GetRows(tableName))
{
WriteItemObject(row, path + pathSeparator + row.RowNumber,
false);
} // foreach (DatabaseRowInfo...
}
else if (type == PathType.Row)
{
// In this case the user has directly specified a row, hence
// just give that particular row
DatabaseRowInfo row = GetRow(tableName, rowNumber);
WriteItemObject(row, path + pathSeparator + row.RowNumber,
false);
}
else
{
// In this case, the path specified is not valid
ThrowTerminatingInvalidPathException(path);
}
} // else
} // GetChildItems
/// <summary>
/// Return the names of all child items.
/// </summary>
/// <param name="path">The root path.</param>
/// <param name="returnContainers">Not used.</param>
protected override void GetChildNames(string path,
ReturnContainers returnContainers)
{
// If the path represented is a drive, then the child items are
// tables. get the names of all the tables in the drive.
if (PathIsDrive(path))
{
foreach (DatabaseTableInfo table in GetTables())
{
WriteItemObject(table.Name, path, true);
} // foreach (DatabaseTableInfo...
} // if (PathIsDrive...
else
{
// Get type, table name and row number from path specified
string tableName;
int rowNumber;
PathType type = GetNamesFromPath(path, out tableName, out rowNumber);
if (type == PathType.Table)
{
// Get all the rows in the table and then write out the
// row numbers.
foreach (DatabaseRowInfo row in GetRows(tableName))
{
WriteItemObject(row.RowNumber, path, false);
} // foreach (DatabaseRowInfo...
}
else if (type == PathType.Row)
{
// In this case the user has directly specified a row, hence
// just give that particular row
DatabaseRowInfo row = GetRow(tableName, rowNumber);
WriteItemObject(row.RowNumber, path, false);
}
else
{
ThrowTerminatingInvalidPathException(path);
}
} // else
} // GetChildNames
/// <summary>
/// Determines if the specified path has child items.
/// </summary>
/// <param name="path">The path to examine.</param>
/// <returns>
/// True if the specified path has child items.
/// </returns>
protected override bool HasChildItems(string path)
{
if (PathIsDrive(path))
{
return true;
}
return (ChunkPath(path).Length == 1);
} // HasChildItems
/// <summary>
/// Creates a new item at the specified path.
/// </summary>
///
/// <param name="path">
/// The path to the new item.
/// </param>
///
/// <param name="type">
/// Type for the object to create. "Table" for creating a new table and
/// "Row" for creating a new row in a table.
/// </param>
///
/// <param name="newItemValue">
/// Object for creating new instance of a type at the specified path. For
/// creating a "Table" the object parameter is ignored and for creating
/// a "Row" the object must be of type string which will contain comma
/// separated values of the rows to insert.
/// </param>
protected override void NewItem(string path, string type,
object newItemValue)
{
string tableName;
int rowNumber;
PathType pt = GetNamesFromPath(path, out tableName, out rowNumber);
if (pt == PathType.Invalid)
{
ThrowTerminatingInvalidPathException(path);
}
// Check if type is either "table" or "row", if not throw an
// exception
if (!String.Equals(type, "table", StringComparison.OrdinalIgnoreCase)
&& !String.Equals(type, "row", StringComparison.OrdinalIgnoreCase))
{
WriteError(new ErrorRecord
(new ArgumentException("Type must be either a table or row"),
"CannotCreateSpecifiedObject",
ErrorCategory.InvalidArgument,
path
)
);
throw new ArgumentException("This provider can only create items of type \"table\" or \"row\"");
}
// Path type is the type of path of the container. So if a drive
// is specified, then a table can be created under it and if a table
// is specified, then a row can be created under it. For the sake of
// completeness, if a row is specified, then if the row specified by
// the path does not exist, a new row is created. However, the row
// number may not match as the row numbers only get incremented based
// on the number of rows
if (PathIsDrive(path))
{
if (String.Equals(type, "table", StringComparison.OrdinalIgnoreCase))
{
// Execute command using ODBC connection to create a table
try
{
// create the table using an sql statement
string newTableName = newItemValue.ToString();
if (!TableNameIsValid(newTableName))
{
return;
}
string sql = "create table " + newTableName
+ " (ID INT)";
// Create the table using the Odbc connection from the
// drive.
AccessDBPSDriveInfo di = this.PSDriveInfo as AccessDBPSDriveInfo;
if (di == null)
{
return;
}
OdbcConnection connection = di.Connection;
if (ShouldProcess(newTableName, "create"))
{
OdbcCommand cmd = new OdbcCommand(sql, connection);
cmd.ExecuteScalar();
}
}
catch (Exception ex)
{
WriteError(new ErrorRecord(ex, "CannotCreateSpecifiedTable",
ErrorCategory.InvalidOperation, path)
);
}
} // if (String...
else if (String.Equals(type, "row", StringComparison.OrdinalIgnoreCase))
{
throw new
ArgumentException("A row cannot be created under a database, specify a path that represents a Table");
}
}// if (PathIsDrive...
else
{
if (String.Equals(type, "table", StringComparison.OrdinalIgnoreCase))
{
if (rowNumber < 0)
{
throw new
ArgumentException("A table cannot be created within another table, specify a path that represents a database");
}
else
{
throw new
ArgumentException("A table cannot be created inside a row, specify a path that represents a database");
}
} //if (String.Equals....
// if path specified is a row, create a new row
else if (String.Equals(type, "row", StringComparison.OrdinalIgnoreCase))
{
// The user is required to specify the values to be inserted
// into the table in a single string separated by commas
string value = newItemValue as string;
if (String.IsNullOrEmpty(value))
{
throw new
ArgumentException("Value argument must have comma separated values of each column in a row");
}
string[] rowValues = value.Split(',');
OdbcDataAdapter da = GetAdapterForTable(tableName);
if (da == null)
{
return;
}
DataSet ds = GetDataSetForTable(da, tableName);
DataTable table = GetDataTable(ds, tableName);
if (rowValues.Length != table.Columns.Count)
{
string message =
String.Format(CultureInfo.CurrentCulture,
"The table has {0} columns and the value specified must have so many comma separated values",
table.Columns.Count);
throw new ArgumentException(message);
}
if (!Force && (rowNumber >=0 && rowNumber < table.Rows.Count))
{
string message = String.Format(CultureInfo.CurrentCulture,
"The row {0} already exists. To create a new row specify row number as {1}, or specify path to a table, or use the -Force parameter",
rowNumber, table.Rows.Count);
throw new ArgumentException(message);
}
if (rowNumber > table.Rows.Count)
{
string message = String.Format(CultureInfo.CurrentCulture,
"To create a new row specify row number as {0}, or specify path to a table",
table.Rows.Count);
throw new ArgumentException(message);
}
// Create a new row and update the row with the input
// provided by the user
DataRow row = table.NewRow();
for (int i = 0; i < rowValues.Length; i++)
{
row[i] = rowValues[i];
}
table.Rows.Add(row);
if (ShouldProcess(tableName, "update rows"))
{
// Update the table from memory back to the data source
da.Update(ds, tableName);
}
}// else if (String...
}// else ...
} // NewItem
/// <summary>
/// Copies an item at the specified path to the location specified
/// </summary>
///
/// <param name="path">
/// Path of the item to copy
/// </param>
///
/// <param name="copyPath">
/// Path of the item to copy to
/// </param>
///
/// <param name="recurse">
/// Tells the provider to recurse subcontainers when copying
/// </param>
///
protected override void CopyItem(string path, string copyPath, bool recurse)
{
string tableName, copyTableName;
int rowNumber, copyRowNumber;
PathType type = GetNamesFromPath(path, out tableName, out rowNumber);
PathType copyType = GetNamesFromPath(copyPath, out copyTableName, out copyRowNumber);
if (type == PathType.Invalid)
{
ThrowTerminatingInvalidPathException(path);
}
if (type == PathType.Invalid)
{
ThrowTerminatingInvalidPathException(copyPath);
}
// Get the table and the table to copy to
OdbcDataAdapter da = GetAdapterForTable(tableName);
if (da == null)
{
return;
}
DataSet ds = GetDataSetForTable(da, tableName);
DataTable table = GetDataTable(ds, tableName);
OdbcDataAdapter cda = GetAdapterForTable(copyTableName);
if (cda == null)
{
return;
}
DataSet cds = GetDataSetForTable(cda, copyTableName);
DataTable copyTable = GetDataTable(cds, copyTableName);
// if source represents a table
if (type == PathType.Table)
{
// if copyPath does not represent a table
if (copyType != PathType.Table)
{
ArgumentException e = new ArgumentException("Table can only be copied on to another table location");
WriteError(new ErrorRecord(e, "PathNotValid",
ErrorCategory.InvalidArgument, copyPath));
throw e;
}
// if table already exists then force parameter should be set
// to force a copy
if (!Force && GetTable(copyTableName) != null)
{
throw new ArgumentException("Specified path already exists");
}
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow row = table.Rows[i];
DataRow copyRow = copyTable.NewRow();
copyRow.ItemArray = row.ItemArray;
copyTable.Rows.Add(copyRow);
}
} // if (type == ...
// if source represents a row
else
{
if (copyType == PathType.Row)
{
if (!Force && (copyRowNumber < copyTable.Rows.Count))
{
throw new ArgumentException("Specified path already exists.");
}
DataRow row = table.Rows[rowNumber];
DataRow copyRow = null;
if (copyRowNumber < copyTable.Rows.Count)
{
// copy to an existing row
copyRow = copyTable.Rows[copyRowNumber];
copyRow.ItemArray = row.ItemArray;
copyRow[0] = GetNextID(copyTable);
}
else if (copyRowNumber == copyTable.Rows.Count)
{
// copy to the next row in the table that will
// be created
copyRow = copyTable.NewRow();
copyRow.ItemArray = row.ItemArray;
copyRow[0] = GetNextID(copyTable);
copyTable.Rows.Add(copyRow);
}
else
{
// attempting to copy to a nonexistent row or a row
// that cannot be created now - throw an exception
string message = String.Format(CultureInfo.CurrentCulture,
"The item cannot be specified to the copied row. Specify row number as {0}, or specify a path to the table.",
table.Rows.Count);
throw new ArgumentException(message);
}
}
else
{
// destination path specified represents a table,
// create a new row and copy the item
DataRow copyRow = copyTable.NewRow();
copyRow.ItemArray = table.Rows[rowNumber].ItemArray;
copyRow[0] = GetNextID(copyTable);
copyTable.Rows.Add(copyRow);
}
}
if (ShouldProcess(copyTableName, "CopyItems"))
{
cda.Update(cds, copyTableName);
}
} //CopyItem
/// <summary>
/// Removes (deletes) the item at the specified path
/// </summary>
///
/// <param name="path">
/// The path to the item to remove.
/// </param>
///
/// <param name="recurse">
/// True if all children in a subtree should be removed, false if only
/// the item at the specified path should be removed. Is applicable
/// only for container (table) items. Its ignored otherwise (even if
/// specified).
/// </param>
///
/// <remarks>
/// There are no elements in this store which are hidden from the user.
/// Hence this method will not check for the presence of the Force
/// parameter
/// </remarks>
///
protected override void RemoveItem(string path, bool recurse)
{
string tableName;
int rowNumber = 0;
PathType type = GetNamesFromPath(path, out tableName, out rowNumber);
if (type == PathType.Table)
{
// if recurse flag has been specified, delete all the rows as well
if (recurse)
{
OdbcDataAdapter da = GetAdapterForTable(tableName);
if (da == null)
{
return;
}
DataSet ds = GetDataSetForTable(da, tableName);
DataTable table = GetDataTable(ds, tableName);
for (int i = 0; i < table.Rows.Count; i++)
{
table.Rows[i].Delete();
}
if (ShouldProcess(path, "RemoveItem"))
{
da.Update(ds, tableName);
RemoveTable(tableName);
}
}//if (recurse...
else
{
// Remove the table
if (ShouldProcess(path, "RemoveItem"))
{
RemoveTable(tableName);
}
}
}
else if (type == PathType.Row)
{
OdbcDataAdapter da = GetAdapterForTable(tableName);
if (da == null)
{
return;
}
DataSet ds = GetDataSetForTable(da, tableName);
DataTable table = GetDataTable(ds, tableName);
table.Rows[rowNumber].Delete();
if (ShouldProcess(path, "RemoveItem"))
{
da.Update(ds, tableName);
}
}
else
{
ThrowTerminatingInvalidPathException(path);
}
} // RemoveItem
#endregion Container Overloads
#region Helper Methods
/// <summary>
/// Checks if a given path is actually a drive name.
/// </summary>
/// <param name="path">The path to check.</param>
/// <returns>
/// True if the path given represents a drive, false otherwise.
/// </returns>
private bool PathIsDrive(string path)
{
// Remove the drive name and first path separator. If the
// path is reduced to nothing, it is a drive. Also if its
// just a drive then there wont be any path separators
if (String.IsNullOrEmpty(
path.Replace(this.PSDriveInfo.Root, "")) ||
String.IsNullOrEmpty(
path.Replace(this.PSDriveInfo.Root + pathSeparator, ""))
)
{
return true;
}
else
{
return false;
}
} // PathIsDrive
/// <summary>
/// Breaks up the path into individual elements.
/// </summary>
/// <param name="path">The path to split.</param>
/// <returns>An array of path segments.</returns>
private string[] ChunkPath(string path)
{
// Normalize the path before splitting
string normalPath = NormalizePath(path);
// Return the path with the drive name and first path
// separator character removed, split by the path separator.
string pathNoDrive = normalPath.Replace(this.PSDriveInfo.Root
+ pathSeparator, "");
return pathNoDrive.Split(pathSeparator.ToCharArray());
} // ChunkPath
/// <summary>
/// Adapts the path, making sure the correct path separator
/// character is used.
/// </summary>
/// <param name="path"></param>
/// <returns></returns>
private string NormalizePath(string path)
{
string result = path;
if (!String.IsNullOrEmpty(path))
{
result = path.Replace("/", pathSeparator);
}
return result;
} // NormalizePath
/// <summary>
/// Chunks the path and returns the table name and the row number
/// from the path
/// </summary>
/// <param name="path">Path to chunk and obtain information</param>
/// <param name="tableName">Name of the table as represented in the
/// path</param>
/// <param name="rowNumber">Row number obtained from the path</param>
/// <returns>what the path represents</returns>
private PathType GetNamesFromPath(string path, out string tableName, out int rowNumber)
{
PathType retVal = PathType.Invalid;
rowNumber = -1;
tableName = null;
// Check if the path specified is a drive
if (PathIsDrive(path))
{
return PathType.Database;
}
// chunk the path into parts
string[] pathChunks = ChunkPath(path);
switch (pathChunks.Length)
{
case 1:
{
string name = pathChunks[0];
if (TableNameIsValid(name))
{
tableName = name;
retVal = PathType.Table;
}
}
break;
case 2:
{
string name = pathChunks[0];
if (TableNameIsValid(name))
{
tableName = name;
}
int number = SafeConvertRowNumber(pathChunks[1]);
if (number >= 0)
{
rowNumber = number;
retVal = PathType.Row;
}
else
{
WriteError(new ErrorRecord(
new ArgumentException("Row number is not valid"),
"RowNumberNotValid",
ErrorCategory.InvalidArgument,
path));
}
}
break;
default:
{
WriteError(new ErrorRecord(
new ArgumentException("The path supplied has too many segments"),
"PathNotValid",
ErrorCategory.InvalidArgument,
path));
}
break;
} // switch(pathChunks...
return retVal;
} // GetNamesFromPath
/// <summary>
/// Throws an argument exception stating that the specified path does
/// not represent either a table or a row
/// </summary>
/// <param name="path">path which is invalid</param>
private void ThrowTerminatingInvalidPathException(string path)
{
StringBuilder message = new StringBuilder("Path must represent either a table or a row :");
message.Append(path);
throw new ArgumentException(message.ToString());
}
/// <summary>
/// Retrieve the list of tables from the database.
/// </summary>
/// <returns>
/// Collection of DatabaseTableInfo objects, each object representing
/// information about one database table
/// </returns>
private Collection<DatabaseTableInfo> GetTables()
{
Collection<DatabaseTableInfo> results =
new Collection<DatabaseTableInfo>();
// using ODBC connection to the database and get the schema of tables
AccessDBPSDriveInfo di = this.PSDriveInfo as AccessDBPSDriveInfo;
if (di == null)
{
return null;
}
OdbcConnection connection = di.Connection;
DataTable dt = connection.GetSchema("Tables");
int count;
// iterate through all rows in the schema and create DatabaseTableInfo
// objects which represents a table
foreach (DataRow dr in dt.Rows)
{
String tableName = dr["TABLE_NAME"] as String;
DataColumnCollection columns = null;
// find the number of rows in the table
try
{
String cmd = "Select count(*) from \"" + tableName + "\"";
OdbcCommand command = new OdbcCommand(cmd, connection);
count = (Int32)command.ExecuteScalar();
}
catch
{
count = 0;
}
// create DatabaseTableInfo object representing the table
DatabaseTableInfo table =
new DatabaseTableInfo(dr, tableName, count, columns);
results.Add(table);
} // foreach (DataRow...
return results;
} // GetTables
/// <summary>
/// Return row information from a specified table.
/// </summary>
/// <param name="tableName">The name of the database table from
/// which to retrieve rows.</param>
/// <returns>Collection of row information objects.</returns>
private Collection<DatabaseRowInfo> GetRows(string tableName)
{
Collection<DatabaseRowInfo> results =
new Collection<DatabaseRowInfo>();
// Obtain rows in the table and add it to the collection
try
{
OdbcDataAdapter da = GetAdapterForTable(tableName);
if (da == null)
{
return null;
}
DataSet ds = GetDataSetForTable(da, tableName);
DataTable table = GetDataTable(ds, tableName);
int i = 0;
foreach (DataRow row in table.Rows)
{
results.Add(new DatabaseRowInfo(row, i.ToString(CultureInfo.CurrentCulture)));
i++;
} // foreach (DataRow...
}
catch (Exception e)
{
WriteError(new ErrorRecord(e, "CannotAccessSpecifiedRows",
ErrorCategory.InvalidOperation, tableName));
}
return results;
} // GetRows
/// <summary>
/// Retrieve information about a single table.
/// </summary>
/// <param name="tableName">The table for which to retrieve
/// data.</param>
/// <returns>Table information.</returns>
private DatabaseTableInfo GetTable(string tableName)
{
foreach (DatabaseTableInfo table in GetTables())
{
if (String.Equals(tableName, table.Name, StringComparison.OrdinalIgnoreCase))
{
return table;
}
}
return null;
} // GetTable
/// <summary>
/// Removes the specified table from the database
/// </summary>
/// <param name="tableName">Name of the table to remove</param>
private void RemoveTable(string tableName)
{
// validate if tablename is valid and if table is present
if (String.IsNullOrEmpty(tableName) || !TableNameIsValid(tableName) || !TableIsPresent(tableName))
{
return;
}
// Execute command using ODBC connection to remove a table
try
{
// delete the table using an sql statement
string sql = "drop table " + tableName;
AccessDBPSDriveInfo di = this.PSDriveInfo as AccessDBPSDriveInfo;
if (di == null)
{
return;
}
OdbcConnection connection = di.Connection;
OdbcCommand cmd = new OdbcCommand(sql, connection);
cmd.ExecuteScalar();
}
catch (Exception ex)
{
WriteError(new ErrorRecord(ex, "CannotRemoveSpecifiedTable",
ErrorCategory.InvalidOperation, null)
);
}
} // RemoveTable
/// <summary>
/// Obtain a data adapter for the specified Table
/// </summary>
/// <param name="tableName">Name of the table to obtain the
/// adapter for</param>
/// <returns>Adapter object for the specified table</returns>
/// <remarks>An adapter serves as a bridge between a DataSet (in memory
/// representation of table) and the data source</remarks>
private OdbcDataAdapter GetAdapterForTable(string tableName)
{
OdbcDataAdapter da = null;
AccessDBPSDriveInfo di = this.PSDriveInfo as AccessDBPSDriveInfo;
if (di == null || !TableNameIsValid(tableName) ||!TableIsPresent(tableName))
{
return null;
}
OdbcConnection connection = di.Connection;
try
{
// Create a odbc data adpater. This can be sued to update the
// data source with the records that will be created here
// using data sets
string sql = "Select * from " + tableName;
da = new OdbcDataAdapter(new OdbcCommand(sql, connection));
// Create a odbc command builder object. This will create sql
// commands automatically for a single table, thus
// eliminating the need to create new sql statements for
// every operation to be done.
OdbcCommandBuilder cmd = new OdbcCommandBuilder(da);
// Set the delete cmd for the table here
sql = "Delete from " + tableName + " where ID = ?";
da.DeleteCommand = new OdbcCommand(sql, connection);
// Specify a DeleteCommand parameter based on the "ID"
// column
da.DeleteCommand.Parameters.Add(new OdbcParameter());
da.DeleteCommand.Parameters[0].SourceColumn = "ID";
// Create an InsertCommand based on the sql string
// Insert into "tablename" values (?,?,?)" where
// ? represents a column in the table. Note that
// the number of ? will be equal to the number of
// columnds
DataSet ds = new DataSet();
da.FillSchema(ds, SchemaType.Source);
ds.Locale = CultureInfo.InvariantCulture;
sql = "Insert into " + tableName + " values ( ";
for (int i = 0; i < ds.Tables["Table"].Columns.Count; i++)
{
sql += "?, ";
}
sql = sql.Substring(0, sql.Length - 2);
sql += ")";
da.InsertCommand = new OdbcCommand(sql, connection);
// Create parameters for the InsertCommand based on the
// captions of each column
for (int i = 0; i < ds.Tables["Table"].Columns.Count; i++)
{
da.InsertCommand.Parameters.Add(new OdbcParameter());
da.InsertCommand.Parameters[i].SourceColumn =
ds.Tables["Table"].Columns[i].Caption;
}
// Open the connection if its not already open
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
}
catch (Exception e)
{
WriteError(new ErrorRecord(e, "CannotAccessSpecifiedTable",
ErrorCategory.InvalidOperation, tableName));
}
return da;
} // GetAdapterForTable
/// <summary>
/// Gets the DataSet (in memory representation) for the table
/// for the specified adapter
/// </summary>
/// <param name="adapter">Adapter to be used for obtaining
/// the table</param>
/// <param name="tableName">Name of the table for which a
/// DataSet is required</param>
/// <returns>The DataSet with the filled in schema</returns>
private DataSet GetDataSetForTable(OdbcDataAdapter adapter, string tableName)
{
Debug.Assert(adapter != null);
// Create a dataset object which will provide an in-memory
// representation of the data being worked upon in the
// data source.
DataSet ds = new DataSet();
// Create a table named "Table" which will contain the same
// schema as in the data source.
//adapter.FillSchema(ds, SchemaType.Source);
adapter.Fill(ds, tableName);
ds.Locale = CultureInfo.InvariantCulture;
return ds;
} //GetDataSetForTable
/// <summary>
/// Get the DataTable object which can be used to operate on
/// for the specified table in the data source
/// </summary>
/// <param name="ds">DataSet object which contains the tables
/// schema</param>
/// <param name="tableName">Name of the table</param>
/// <returns>Corresponding DataTable object representing
/// the table</returns>
///
private DataTable GetDataTable(DataSet ds, string tableName)
{
Debug.Assert(ds != null);
Debug.Assert(tableName != null);
DataTable table = ds.Tables[tableName];
table.Locale = CultureInfo.InvariantCulture;
return table;
} // GetDataTable
/// <summary>
/// Retrieves a single row from the named table.
/// </summary>
/// <param name="tableName">The table that contains the
/// numbered row.</param>
/// <param name="row">The index of the row to return.</param>
/// <returns>The specified table row.</returns>
private DatabaseRowInfo GetRow(string tableName, int row)
{
Collection<DatabaseRowInfo> di = GetRows(tableName);
// if the row is invalid write an appropriate error else return the
// corresponding row information
if (row < di.Count && row >= 0)
{
return di[row];
}
else
{
WriteError(new ErrorRecord(
new ItemNotFoundException(),
"RowNotFound",
ErrorCategory.ObjectNotFound,
row.ToString(CultureInfo.CurrentCulture))
);
}
return null;
} // GetRow
/// <summary>
/// Method to safely convert a string representation of a row number
/// into its Int32 equivalent
/// </summary>
/// <param name="rowNumberAsStr">String representation of the row
/// number</param>
/// <remarks>If there is an exception, -1 is returned</remarks>
private int SafeConvertRowNumber(string rowNumberAsStr)
{
int rowNumber = -1;
try
{
rowNumber = Convert.ToInt32(rowNumberAsStr, CultureInfo.CurrentCulture);
}
catch (FormatException fe)
{
WriteError(new ErrorRecord(fe, "RowStringFormatNotValid",
ErrorCategory.InvalidData, rowNumberAsStr));
}
catch (OverflowException oe)
{
WriteError(new ErrorRecord(oe, "RowStringConversionToNumberFailed",
ErrorCategory.InvalidData, rowNumberAsStr));
}
return rowNumber;
} // SafeConvertRowNumber
/// <summary>
/// Check if a table name is valid
/// </summary>
/// <param name="tableName">Table name to validate</param>
/// <remarks>Helps to check for SQL injection attacks</remarks>
private bool TableNameIsValid(string tableName)
{
Regex exp = new Regex(pattern, RegexOptions.Compiled | RegexOptions.IgnoreCase);
if (exp.IsMatch(tableName))
{
return true;
}
WriteError(new ErrorRecord(
new ArgumentException("Table name not valid"), "TableNameNotValid",
ErrorCategory.InvalidArgument, tableName));
return false;
} // TableNameIsValid
/// <summary>
/// Checks to see if the specified table is present in the
/// database
/// </summary>
/// <param name="tableName">Name of the table to check</param>
/// <returns>true, if table is present, false otherwise</returns>
private bool TableIsPresent(string tableName)
{
// using ODBC connection to the database and get the schema of tables
AccessDBPSDriveInfo di = this.PSDriveInfo as AccessDBPSDriveInfo;
if (di == null)
{
return false;
}
OdbcConnection connection = di.Connection;
DataTable dt = connection.GetSchema("Tables");
// check if the specified tableName is available
// in the list of tables present in the database
foreach (DataRow dr in dt.Rows)
{
string name = dr["TABLE_NAME"] as string;
if (name.Equals(tableName, StringComparison.OrdinalIgnoreCase))
{
return true;
}
}
WriteError(new ErrorRecord(
new ArgumentException("Specified Table is not present in database"), "TableNotAvailable",
ErrorCategory.InvalidArgument, tableName));
return false;
}// TableIsPresent
/// <summary>
/// Gets the next available ID in the table
/// </summary>
/// <param name="table">DataTable object representing the table to
/// search for ID</param>
/// <returns>next available id</returns>
private int GetNextID(DataTable table)
{
int big = 0;
int id = 0;
for (int i = 0; i < table.Rows.Count; i++)
{
DataRow row = table.Rows[i];
object o = row["ID"];
if (o.GetType().Name.Equals("Int16"))
{
id = (int)(short)o;
}
else
{
id = (int)o;
}
if (big < id)
{
big = id;
}
}
big++;
return big;
}
#endregion Helper Methods
#region Private Properties
private string pathSeparator = "\\";
private static string pattern = @"^[a-z]+[0-9]*_*$";
private enum PathType { Database, Table, Row, Invalid };
#endregion Private Properties
}
#endregion AccessDBProvider
#region Helper Classes
#region AccessDBPSDriveInfo
/// <summary>
/// Any state associated with the drive should be held here.
/// In this case, it's the connection to the database.
/// </summary>
internal class AccessDBPSDriveInfo : PSDriveInfo
{
private OdbcConnection connection;
/// <summary>
/// ODBC connection information.
/// </summary>
public OdbcConnection Connection
{
get { return connection; }
set { connection = value; }
}
/// <summary>
/// Constructor that takes one argument
/// </summary>
/// <param name="driveInfo">Drive provided by this provider</param>
public AccessDBPSDriveInfo(PSDriveInfo driveInfo)
: base(driveInfo)
{ }
} // class AccessDBPSDriveInfo
#endregion AccessDBPSDriveInfo
#region DatabaseTableInfo
/// <summary>
/// Contains information specific to the database table.
/// Similar to the DirectoryInfo class.
/// </summary>
public class DatabaseTableInfo
{
/// <summary>
/// Row from the "tables" schema
/// </summary>
public DataRow Data
{
get
{
return data;
}
set
{
data = value;
}
}
private DataRow data;
/// <summary>
/// The table name.
/// </summary>
public string Name
{
get
{
return name;
}
set
{
name = value;
}
}
private String name;
/// <summary>
/// The number of rows in the table.
/// </summary>
public int RowCount
{
get
{
return rowCount;
}
set
{
rowCount = value;
}
}
private int rowCount;
/// <summary>
/// The column definitions for the table.
/// </summary>
public DataColumnCollection Columns
{
get
{
return columns;
}
set
{
columns = value;
}
}
private DataColumnCollection columns;
/// <summary>
/// Constructor.
/// </summary>
/// <param name="row">The row definition.</param>
/// <param name="name">The table name.</param>
/// <param name="rowCount">The number of rows in the table.</param>
/// <param name="columns">Information on the column tables.</param>
public DatabaseTableInfo(DataRow row, string name, int rowCount,
DataColumnCollection columns)
{
Name = name;
Data = row;
RowCount = rowCount;
Columns = columns;
} // DatabaseTableInfo
} // class DatabaseTableInfo
#endregion DatabaseTableInfo
#region DatabaseRowInfo
/// <summary>
/// Contains information specific to an individual table row.
/// Analogous to the FileInfo class.
/// </summary>
public class DatabaseRowInfo
{
/// <summary>
/// Row data information.
/// </summary>
public DataRow Data
{
get
{
return data;
}
set
{
data = value;
}
}
private DataRow data;
/// <summary>
/// The row index.
/// </summary>
public string RowNumber
{
get
{
return rowNumber;
}
set
{
rowNumber = value;
}
}
private string rowNumber;
/// <summary>
/// Constructor.
/// </summary>
/// <param name="row">The row information.</param>
/// <param name="name">The row index.</param>
public DatabaseRowInfo(DataRow row, string name)
{
RowNumber = name;
Data = row;
} // DatabaseRowInfo
} // class DatabaseRowInfo
#endregion DatabaseRowInfo
#endregion Helper Classes
}
See Also
Colaborar conosco no GitHub
A fonte deste conteúdo pode ser encontrada no GitHub, onde você também pode criar e revisar problemas e solicitações de pull. Para obter mais informações, confira o nosso guia para colaboradores.
