diff options
Diffstat (limited to 'mcs/class/System.Data/Test/ProviderTests/System.Data.OleDb.jvm/MonoTests.System.Data.Utils/DbTypeParametersCollection.cs')
| -rwxr-xr-x | mcs/class/System.Data/Test/ProviderTests/System.Data.OleDb.jvm/MonoTests.System.Data.Utils/DbTypeParametersCollection.cs | 559 |
1 files changed, 559 insertions, 0 deletions
diff --git a/mcs/class/System.Data/Test/ProviderTests/System.Data.OleDb.jvm/MonoTests.System.Data.Utils/DbTypeParametersCollection.cs b/mcs/class/System.Data/Test/ProviderTests/System.Data.OleDb.jvm/MonoTests.System.Data.Utils/DbTypeParametersCollection.cs new file mode 100755 index 00000000000..caae6740f7d --- /dev/null +++ b/mcs/class/System.Data/Test/ProviderTests/System.Data.OleDb.jvm/MonoTests.System.Data.Utils/DbTypeParametersCollection.cs @@ -0,0 +1,559 @@ +// +// Copyright (c) 2006 Mainsoft Co. +// +// Permission is hereby granted, free of charge, to any person obtaining +// a copy of this software and associated documentation files (the +// "Software"), to deal in the Software without restriction, including +// without limitation the rights to use, copy, modify, merge, publish, +// distribute, sublicense, and/or sell copies of the Software, and to +// permit persons to whom the Software is furnished to do so, subject to +// the following conditions: +// +// The above copyright notice and this permission notice shall be +// included in all copies or substantial portions of the Software. +// +// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, +// EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF +// MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND +// NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE +// LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION +// OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION +// WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. +// + + +using System; +using System.Data; +using System.Data.OleDb; +using System.Data.SqlClient; +using CollectionBase = System.Collections.CollectionBase; +using Sys = System; + +namespace MonoTests.System.Data.Utils.Data +{ + /// <summary> + /// A collection of DbTypeParameters that matches the column of specific a database table + /// </summary> + public class DbTypeParametersCollection : CollectionBase + { + #region Constructors + /// <summary> + /// Default constructor. + /// </summary> + public DbTypeParametersCollection() + { + } + + /// <summary> + /// Constructor. + /// Initializes a DbTypeParametersCollection with the specified TableName + /// </summary> + /// <param name="a_sTableName">Specifies the table name to set.</param> + public DbTypeParametersCollection(string a_sTableName) + { + m_sTableName = a_sTableName; + } + #endregion + + #region Members + private string m_sTableName; + #endregion + + #region Properties & Indexers + public string TableName + { + get + { + return m_sTableName; + } + set + { + m_sTableName = value; + } + } + /// <summary> + /// Gets or sets the DbTypeParameter at the specified index. + /// </summary> + /// <exception cref="ArgumentException">The column name specified by value.DBColumnName already exist in other DbTypeParameter in the DbTypeParametersCollection.</exception> + public DbTypeParameter this[int a_iIndex] + { + get + { + return((DbTypeParameter)List[a_iIndex]); + } + set + { + //Check that the collection does not already contain a DbTypeParameter with the same column name. + int l_iIndexOfValueColumnName = this.IndexOf(value.DbColumnName); + if (l_iIndexOfValueColumnName != -1 && l_iIndexOfValueColumnName != a_iIndex) + { + throw new ArgumentException("The column name specified by DbTypeParameter.DBColumnName already exist in other DbTypeParameter in the DbTypeParametersCollection.", "value"); + } + else + { + List[a_iIndex] = value; + } + } + } + + /// <summary> + /// Gets or sets the DbTypeParameter with the specified DBColumnName. + /// </summary> + /// <exception cref="ArgumentException">The column name specified by value.DBColumnName already exist in other DbTypeParameter in the DbTypeParametersCollection.</exception> + public DbTypeParameter this[string a_sDBColumnName] + { + get + { + return this[IndexOf(a_sDBColumnName)]; + } + set + { + this[IndexOf(a_sDBColumnName)] = value; + } + } + /// <summary> + /// A textual string that conatins the OleDb place holder for parameter ('?') for each of the parameters. + /// </summary> + public string OleDbParameterPlaceHolderList + { + get + { + return GetParameterPlaceHolderList("?, "); + } + } + /// <summary> + /// A textual string that conatins the MSSQL place holder for parameter ('@parame_name') for each of the parameters. + /// </summary> + public string SqlParameterPlaceHolderList + { + get + { + return GetParameterPlaceHolderList("{0}, "); + } + } + /// <summary> + /// A string that contains a comma delimited list of all default column names for the parameters held by this collection. + /// </summary> + public string ColumnsList + { + get + { + Sys.Text.StringBuilder l_sbColumnsList = new Sys.Text.StringBuilder();; + + foreach (DbTypeParameter l_oCurrent in this) + { + l_sbColumnsList.AppendFormat("{0}, ", l_oCurrent.DbColumnName); + } + + //remove last ', ' from values list: + l_sbColumnsList.Remove(l_sbColumnsList.Length -2, 2); + + return l_sbColumnsList.ToString(); + } + } + /// <summary> + /// A string that contains a comma delimited list of all values of the parameters held by this collection. + /// </summary> + public string ValuesList + { + get + { + Sys.Text.StringBuilder l_sbValuesList = new Sys.Text.StringBuilder(); + string l_sCurrentVal = string.Empty; + string l_sCurrentFormatting = string.Empty; + + foreach (DbTypeParameter l_oCurrent in this) + { + //Handle types with string representation different then ToString(). + l_sCurrentVal = (l_oCurrent.Value != DBNull.Value) ? l_oCurrent.Value.ToString() : "NULL"; + if (l_oCurrent.Value is bool) + { + l_sCurrentVal = ((bool)l_oCurrent.Value) ? "1" : "0"; + } + + //Set the correct foratting according to type. + l_sCurrentFormatting = (l_oCurrent.Value is string) ? "'{0}', " : "{0}, "; + + //appent the textual representation. + l_sbValuesList.AppendFormat(l_sCurrentFormatting, l_sCurrentVal); + } + + //remove last ', ' from values list: + l_sbValuesList.Remove(l_sbValuesList.Length -2, 2); + + return l_sbValuesList.ToString(); + } + } + #endregion + + #region Methods + #region Public + #region Sys.Collections.CollectionBase implementation + /// <summary> + /// Adds the specified DbTypeParameter to the DbTypeParametersCollection. + /// </summary> + /// <param name="a_oToAdd">The DbTypeParameter to add to the collection. </param> + /// <returns>The index of the new DbTypeParameter object.</returns> + /// <exception cref="ArgumentException">The column name specified by a_oToAdd.DBColumnName already exist in other DbTypeParameter in the DbTypeParametersCollection.</exception> + public virtual int Add( DbTypeParameter a_oToAdd ) + { + if (this.Contains(a_oToAdd.DbColumnName)) + { + throw new ArgumentException("The column name specified by DbTypeParameter.DBColumnName already exist in other DbTypeParameter in the DbTypeParametersCollection.", "a_oToAdd"); + } + return( List.Add( a_oToAdd ) ); + } + + /// <summary> + /// Adds a DbTypeParameter with the specified data to the DbTypeParametersCollection. + /// </summary> + /// <param name="a_sColumnName">Specifies the initial column name for the DbTypeParameter.</param> + /// <param name="a_sTypeName">Specifies the initial parameter type Name for the DbTypeParameter.</param> + /// <param name="a_oValue">Specifies the initial value for the DbTypeParameter.</param> + /// <returns>The index of the new DbTypeParameter object.</returns> + /// <exception cref="ArgumentException">The column name specified by a_sColumnName already exist in other DbTypeParameter in the DbTypeParametersCollection.</exception> + public virtual int Add(string a_sTypeName, object a_oValue) + { + DbTypeParameter l_oToAdd = new DbTypeParameter(a_sTypeName, a_oValue); + return this.Add(l_oToAdd); + } + + /// <summary> + /// Adds a DbTypeParameter with the specified data to the DbTypeParametersCollection. + /// </summary> + /// <param name="a_sColumnName">Specifies the initial column name for the DbTypeParameter.</param> + /// <param name="a_sTypeName">Specifies the initial parameter type Name for the DbTypeParameter.</param> + /// <param name="a_oValue">Specifies the initial value for the DbTypeParameter.</param> + /// <param name="a_iSize">Specifies the initial size for the DbTypeParameter</param> + /// <returns>The index of the new DbTypeParameter object.</returns> + /// <exception cref="ArgumentException">The column name specified by a_sColumnName already exist in other DbTypeParameter in the DbTypeParametersCollection.</exception> + public virtual int Add(string a_sTypeName, object a_oValue, int a_iSize) + { + DbTypeParameter l_oToAdd = new DbTypeParameter(a_sTypeName, a_oValue, a_iSize); + return this.Add(l_oToAdd); + } + + /// <summary> + /// Gets the location of the DbTypeParameter object in the collection. + /// </summary> + /// <param name="a_oToFind">The DbTypeParameter object to locate. </param> + /// <returns>The zero-based location of the DbTypeParameter in the collection, if found; otherwise, -1.</returns> + public virtual int IndexOf( DbTypeParameter a_oToFind) + { + return( List.IndexOf( a_oToFind ) ); + } + + /// <summary> + /// Gets the location of the DbTypeParameter object in the collection. + /// </summary> + /// <param name="a_oToFind">The DbTypeParameter object to locate. </param> + /// <returns>The zero-based location of the DbTypeParameter in the collection, if found; otherwise, -1.</returns> + public virtual int IndexOf( string a_sColumnName ) + { + for (int i=0; i<List.Count; i++) + { + if (this[i].DbColumnName.ToUpper() == a_sColumnName.ToUpper()) + { + return i; + } + } + + //Didn't find such DbTypeParameter: + return-1; + } + + /// <summary> + /// Determines whether the DbTypeParametersCollection contains a specific DbTypeParameter. + /// </summary> + /// <param name="a_oToFind">The DbTypeParameter to locate in the DbTypeParametersCollection</param> + /// <returns>true if the DbTypeParametersCollection contains the specified DbTypeParameter; otherwise, false.</returns> + public virtual bool Contains( DbTypeParameter a_oToFind ) + { + // If a_oToFind is not of type DbTypeParameter, this will return false. + return( List.Contains( a_oToFind ) ); + } + + /// <summary> + /// Determines whether the DbTypeParametersCollection contains a DbTypeParameter with specific column name (DBColumnName). + /// </summary> + /// <param name="a_sToFind">The column name to locate in the DbTypeParametersCollection</param> + /// <returns>true if the DbTypeParametersCollection contains a DbTypeParameter with specific column name; otherwise, false.</returns> + public virtual bool Contains( string a_sToFind ) + { + return (this.IndexOf(a_sToFind) > -1); + } + + /// <summary> + /// Creates an array of OleDbParameters based on the contents of this collection. + /// </summary> + /// <returns>An array of OleDbParameters based on the contents of this collection</returns> + public virtual OleDbParameter[] ToOleDbParameterArray() + { + OleDbParameter[] l_oParams = new OleDbParameter[this.Count]; + for (int i=0; i<this.Count; i++) + { + l_oParams[i] = new OleDbParameter(this[i].ParameterName, this[i].Value); + } + + return l_oParams; + } + + /// <summary> + /// Creates an array of SqlParameters based on the contents of this collection. + /// </summary> + /// <returns>An array of SqlParameters based on the contents of this collection</returns> + public virtual SqlParameter[] ToSqlParameterArray() + { + SqlParameter[] l_oParams = new SqlParameter[this.Count]; + for (int i=0; i<this.Count; i++) + { + l_oParams[i] = new SqlParameter(this[i].ParameterName, this[i].Value); + } + + return l_oParams; + } + + /// <summary> + /// Creates an array of objects based on the contents of this collection. + /// </summary> + /// <returns>An array of objects that contains all values of parameters in this collection</returns> + public virtual object[] ToValuesArray() + { + + object[] l_oParams = new object[this.Count]; + for (int i=0; i<this.Count; i++) + { + l_oParams[i] = this[i].Value; + } + + return l_oParams; + } + #endregion + #region Execute methods + /// <summary> + /// Builds and execute an INSERT command according to the DbTypeParameters in this collection, and the TableName property. + /// </summary> + /// <param name="a_sUniqueId">A unique identifier for the inserted row.</param> + /// <returns>The number of inserted rows (usually 1).</returns> + public virtual int ExecuteInsert(string a_sUniqueId) + { + int l_iRecordsInserted; + OleDbCommand l_cmdInsert = new OleDbCommand(); + l_cmdInsert.Connection = new OleDbConnection(ConnectedDataProvider.ConnectionString); + l_cmdInsert.CommandText = GetInsertCommandText(a_sUniqueId); + AddInsertCommandParameters(l_cmdInsert.Parameters); + + try + { + if (l_cmdInsert.Connection.State != ConnectionState.Open) + { + l_cmdInsert.Connection.Open(); + } +// Sys.Console.WriteLine(l_cmdInsert.CommandText); + l_iRecordsInserted = l_cmdInsert.ExecuteNonQuery(); + } + finally + { + l_cmdInsert.Connection.Close(); + } + + return l_iRecordsInserted; + } + + /// <summary> + /// Builds and executes an DELETE command according to the UniqueId parameter, and the TableName property. + /// </summary> + /// <param name="a_sUniqueId">The criteria for deleting.</param> + /// <returns>The number of deleted rows.</returns> + public virtual int ExecuteDelete(string a_sUniqueId) + { + return DbTypeParametersCollection.ExecuteDelete(this.TableName, a_sUniqueId); + } + + /// <summary> + /// Builds and executes an DELETE command according to the given TableName & UniqueId parameters. + /// </summary> + /// <param name="a_sTableName">The table to delete from.</param> + /// <param name="a_sUniqueId">The criteria for deleting.</param> + /// <returns>The number of deleted rows.</returns> + public static int ExecuteDelete(string a_sTableName, string a_sUniqueId) + { + int l_iRecordsDeleted; + OleDbCommand l_cmdDelete = new OleDbCommand(); + l_cmdDelete.Connection = new OleDbConnection(ConnectedDataProvider.ConnectionString); + l_cmdDelete.CommandText = String.Format("DELETE FROM {0} WHERE ID='{1}'", a_sTableName, a_sUniqueId); + try + { + if (l_cmdDelete.Connection.State != ConnectionState.Open) + { + l_cmdDelete.Connection.Open(); + } + l_iRecordsDeleted = l_cmdDelete.ExecuteNonQuery(); + } + finally + { + l_cmdDelete.Connection.Close(); + } + + return l_iRecordsDeleted; + } + + /// <summary> + /// Executes a select command that selects all columns specified by this collection, + /// from the table specified by this collections TableName property, + /// filtered by the given unique id. + /// The select command is executed using ExecuteReader. + /// </summary> + /// <param name="a_sUniqueId">The unique id to use as rows filter.</param> + /// <param name="a_oReader">Reader that holds the results of the select command.</param> + /// <param name="a_oConnection">A connection object that serves the reader a_oReader for retrivieng data.</param> + /// <remarks> + /// While the OleDbDataReader is in use, the associated OleDbConnection is open and busy serving the OleDbDataReader. + /// While in this state, no other operations can be performed on the OleDbConnection other than closing it. + /// This is the case until the Close method of the OleDbDataReader is called. + /// It is the users responsibility to close the OleDbConnection explicitly when it is no longer needed. + /// </remarks> + public virtual void ExecuteSelectReader(string a_sUniqueId, out OleDbDataReader a_oReader, out OleDbConnection a_oConnection) + { + OleDbCommand l_cmdSelect = BuildSelectCommand(a_sUniqueId); + l_cmdSelect.Connection.Open(); + a_oConnection = l_cmdSelect.Connection; + a_oReader = l_cmdSelect.ExecuteReader(); + } + /// <summary> + /// Executes a select command that selects all columns specified by this collection, + /// from the table specified by this collections TableName property, + /// filtered by the given unique id. + /// The select command is executed using ExecuteScalar. + /// </summary> + /// <param name="a_sUniqueId">The unique id to use as rows filter.</param> + /// <returns>The first column of the first row in the result set, or a null reference if the result set is empty.</returns> + public virtual object ExecuteSelectScalar(string a_sUniqueId) + { + object l_oReturnValue = null; + OleDbCommand l_cmdSelect = BuildSelectCommand(a_sUniqueId); + + try + { + l_cmdSelect.Connection.Open(); + l_oReturnValue = l_cmdSelect.ExecuteScalar(); + } + finally + { + if (l_cmdSelect.Connection.State != ConnectionState.Closed) + { + l_cmdSelect.Connection.Close(); + } + } + + return l_oReturnValue; + } + + #endregion + #endregion + + #region Callbacks + //All these methods are callbacks, that ensure type safty of elements within the base.List. + + protected override void OnInsert( int index, Object value ) + { + if ( value.GetType() != Type.GetType("MonoTests.System.Data.Utils.Data.DbTypeParameter") ) + throw new ArgumentException( "value must be of type DbTypeParameter.", "value" ); + } + + protected override void OnRemove( int index, Object value ) + { + if ( value.GetType() != Type.GetType("MonoTests.System.Data.Utils.Data.DbTypeParameter") ) + throw new ArgumentException( "value must be of type DbTypeParameter.", "value" ); + } + + protected override void OnSet( int index, Object oldValue, Object newValue ) + { + if ( newValue.GetType() != Type.GetType("MonoTests.System.Data.Utils.Data.DbTypeParameter") ) + throw new ArgumentException( "newValue must be of type DbTypeParameter.", "newValue" ); + } + + protected override void OnValidate( Object value ) + { + if ( value.GetType() != Type.GetType("MonoTests.System.Data.Utils.Data.DbTypeParameter") ) + throw new ArgumentException( "value must be of type DbTypeParameter." ); + } + #endregion + + #region Private + /// <summary> + /// Create a SQL text for an INSERT command with parameters ('?' notation). + /// The command uses the table specified in the TableName property, + /// and uses the columns specified in this parameters collection. + /// </summary> + /// <param name="a_sUniqueId">The Unique id for the row to be inserted.</param> + private string GetInsertCommandText(string a_sUniqueId) + { + string l_sCmd = string.Empty; + string l_sColumnsList = ColumnsList; + string l_sValuesList = OleDbParameterPlaceHolderList; + + l_sCmd = String.Format("INSERT INTO {0} (ID, {1}) VALUES ('{2}', {3})", m_sTableName, l_sColumnsList, a_sUniqueId, l_sValuesList); + + return l_sCmd; + } + + /// <summary> + /// Builds an OleDbParametersCollection for an INSERT command, according to the DbTypeParameters in this collection. + /// </summary> + /// <param name="a_oParams">The OleDbParameterCollection to be filled.</param> + private void AddInsertCommandParameters(OleDbParameterCollection a_oParams) + { + foreach (DbTypeParameter l_oCurrent in this) + { + a_oParams.Add(l_oCurrent.ParameterName, l_oCurrent.Value); + } + } + /// <summary> + /// Create a command object for a SELECT command . + /// The command uses the table specified in the TableName property, + /// and uses the columns specified in this parameters collection. + /// </summary> + /// <remarks>The ID column is not included in the selected columns of this command.</remarks> + /// <returns>An OleDbCommand for selecting the columns specified in this collection, from the table specified in the TableName property. </returns> + private OleDbCommand BuildSelectCommand(string a_sUniqueId) + { + string l_sColumnsList = ColumnsList; + string l_sCmdTxt; + OleDbConnection l_oConnection = new OleDbConnection(ConnectedDataProvider.ConnectionString); + OleDbCommand l_cmdSelect = new OleDbCommand(); + + //Build the command's text. + l_sCmdTxt = string.Format("SELECT {0} FROM {1} WHERE ID='{2}'", l_sColumnsList, this.TableName, a_sUniqueId); + + //Build the command object. + l_cmdSelect.CommandText = l_sCmdTxt; + l_cmdSelect.Connection = l_oConnection; + + return l_cmdSelect; + } + + /// <summary> + /// Create a string that conatins a place holder for each of the parameters. + /// </summary> + /// <param name="a_sSormatting">The format for each parameter in the list (defined in String.Format())</param> + /// <returns>A string that conatins a place holder for each of the parameters.</returns> + private string GetParameterPlaceHolderList(string a_sSormatting) + { + Sys.Text.StringBuilder l_sbValuesList = new Sys.Text.StringBuilder();; + + foreach (DbTypeParameter l_oCurrent in this) + { + l_sbValuesList.AppendFormat(a_sSormatting, l_oCurrent.ParameterName); + } + + //remove last ', ' from values list: + l_sbValuesList.Remove(l_sbValuesList.Length -2, 2); + + return l_sbValuesList.ToString(); + } + #endregion + + #endregion + } +} |