using System; using System.Data; using System.Data.Common; using System.Xml; using System.ComponentModel; using System.Collections; using System.Diagnostics; using ByteFX.Data.MySqlClient; using OakLeaf.MM.Main.Data; namespace Apps4u.WinApp.Managers { /// <summary> /// MySql Server data access class /// </summary> [ToolboxItem(false)] public class a4uDataAccessMySql : OakLeaf.MM.Main.Data.mmDataAccessBase { /// <summary> /// Primary key name /// </summary> protected string PrimaryKeyName = ""; /// <summary> /// Connection /// </summary> protected MySqlConnection Connection; /// <summary> /// Transaction /// </summary> protected MySqlTransaction Transaction; /// <summary> /// Data Adapter /// </summary> protected MySqlDataAdapter DataAdapter = null; private MySqlCommandBuilder _commandBuilder = null; /// <summary> /// Command Builder /// </summary> protected MySqlCommandBuilder CommandBuilder { get { // If a Command Builder doesn't exist, create one if (this._commandBuilder == null) { this.CommandBuilder = new MySqlCommandBuilder(this.DataAdapter); } return this._commandBuilder; } set { this._commandBuilder = value; } } /// <summary> /// Begins an ADO.NET transaction, returning the connection and transaction objects /// </summary> /// <param name="conn">Connection object</param> /// <param name="trx">Transaction object</param> /// <param name="connOpen">Specifies if connection was opened</param> public override void TransactionBegin(out IDbConnection conn, out IDbTransaction trx, out bool connOpen) { connOpen = false; // Create a connection if one doesn't exist if (this.Connection == null) { this.Connection = this.CreateSqlConnection(); } // Open the connection if it's closed if (this.Connection.State == ConnectionState.Closed) { this.Connection.Open(); connOpen = true; } // Create the transaction object this.Transaction = this.Connection.BeginTransaction(); // Set the output parameters conn = this.Connection; trx = this.Transaction; } /// <summary> /// Sets up the data access object for a transaction started on another object /// </summary> /// <param name="conn">Connection object</param> /// <param name="trx">Transaction object</param> public override void TransactionBegin(IDbConnection conn, IDbTransaction trx) { this.Connection = (MySqlConnection)conn; this.Transaction = (MySqlTransaction) trx; } /// <summary> /// Rolls back the current transation /// </summary> public override void TransactionCommit() { if (this.Transaction != null) { this.Transaction.Commit(); this.Connection.Close(); this.TransactionClear(); } } /// <summary> /// Commits the current transaction /// </summary> public override void TransactionRollback() { if (this.Transaction != null) { this.Transaction.Rollback(); this.Connection.Close(); this.TransactionClear(); } } /// <summary> /// Clears the current transaction started on another object /// </summary> public override void TransactionClear() { this.Transaction = null; this.Connection = null; } /// <summary> /// Fill the specified DataSet table by executing the Fill command of the /// specified Data Adapter /// </summary> /// <param name="ds">DataSet</param> /// <param name="dbAdapter">Data Adapter used to fill the table</param> /// <param name="tableName">Table to be filled</param> /// <param name="clearOnFill">Clear on fill flag</param> public override void FillDataSet(DataSet ds, IDbDataAdapter dbAdapter, string tableName, bool clearOnFill) { // If the Select command's connection is null, use the existing connection // associated with the data access class, or create a new one if (dbAdapter.SelectCommand.Connection == null) { if (this.Connection == null) { this.Connection = this.CreateSqlConnection(); } dbAdapter.SelectCommand.Connection = this.Connection; } // Specify that column and primary key information is added to the DataTable dbAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; // If specified to do so, Translate any parameters for the specified .NET Data Provider if (this.ParameterTranslation) { this.TranslateParameters(dbAdapter.SelectCommand); } // Cast the data adapter then use it to fill the DataSet MySqlDataAdapter adapter = (MySqlDataAdapter)dbAdapter; // If specified to do so and the table already exists, clear it if (clearOnFill) { DataTable dt = ds.Tables[tableName]; if (dt != null) { dt.Clear(); } } // Fill the specified table in the DataSet adapter.Fill(ds, tableName); // Store the current select command for the current table in the // DataTable's extended properties this.SaveSelectCommand(ds, tableName, dbAdapter); } /// <summary> /// Fills the specified DataSet table by executing the Select Command /// </summary> /// <param name="ds">DataSet</param> /// <param name="command">Select Command object</param> /// <param name="tableName">Table name</param> /// <param name="clearOnFill">Clear on fill flag</param> public override void FillDataSet(DataSet ds, IDbCommand command, string tableName, bool clearOnFill) { // Create the Data Adapter MySqlDataAdapter DataAdapter = (MySqlDataAdapter)this.CreateDataAdapter(command); // Fill the DataSet this.FillDataSet(ds, DataAdapter, tableName, clearOnFill); } /// <summary> /// Fills the specified DataSet table by executing the specified command with its /// associated parameters /// </summary> /// <param name="ds">DataSet</param> /// <param name="command">Command to be executed</param> /// <param name="tableName">Name of table to be filled</param> /// <param name="cmdType">Command Type</param> /// <param name="dataParms">Data Parameters</param> /// <param name="clearOnFill">Clear on fill flag</param> public override void FillDataSet(DataSet ds, string command, string tableName, CommandType cmdType, IDbDataParameter[] dataParms, bool clearOnFill) { // Create a command object IDbCommand DataCommand = this.CreateCommand(command, this.Connection); // Set the Command Type DataCommand.CommandType = cmdType; // Add all specified parameters to the command object if (dataParms != null && dataParms.Length > 0) { foreach (IDbDataParameter parm in dataParms) { DataCommand.Parameters.Add(parm); } } // If specified to do so, translate parameters if (ParameterTranslation) { this.TranslateParameters(DataCommand); } // Fill the DataSet this.FillDataSet(ds, DataCommand, tableName, clearOnFill); } /// <summary> /// Returns an empty DataSet for the specified table name /// </summary> /// <param name="ds">DataSet</param> /// <param name="tableName">Table Name</param> /// <param name="command">Command to be executed to return empty DataSet</param> /// <returns>DataSet</returns> public override DataSet GetEmptyDataSet(mmDataSet ds, string tableName, string command) { // Create a SELECT command if one doesn't exist if (command == null || command == "") { command = "SELECT * FROM [" + tableName + "]"; } // Create a connection if one doesn't exist if (this.Connection == null) { this.Connection = this.CreateSqlConnection(); } // Create the data adapter DataAdapter = (MySqlDataAdapter)this.CreateDataAdapter(command, (IDbConnection)Connection); // Set the transaction in case one is currently in progress DataAdapter.SelectCommand.Transaction = this.Transaction; // Retrieve an empty DataSet DataAdapter.FillSchema(ds, SchemaType.Mapped, tableName); return ds; } /// <summary> /// Returns a DataTable containing a list of tables in the specified database /// </summary> /// <returns>DataTable containing a list of tables in the dbc</returns> public override DataTable GetListOfDbcTables() { // Create a connection if one doesn't exist if (this.Connection == null) { this.Connection = this.CreateSqlConnection(); } // Create and configure a data adapter MySqlDataAdapter schemaDA = (MySqlDataAdapter)this.CreateDataAdapter("SELECT * FROM INFORMATION_SCHEMA.TABLES " + "WHERE TABLE_TYPE = 'BASE TABLE' " + "AND TABLE_NAME <> 'dtproperties' " + "ORDER BY TABLE_TYPE", this.Connection); DataTable dt = new DataTable(); schemaDA.Fill(dt); return dt; } /// <summary> /// Returns a DataTable containing a list of fields for the specified database and table /// </summary> /// <param name="tableName">Table name</param> /// <returns>DataTable containing table fields</returns> public override DataTable GetListOfTableFields(string tableName) { // Create a connection if one doesn't exist if (this.Connection == null) { this.Connection = this.CreateSqlConnection(); } // Get the database column schema MySqlDataAdapter schemaDA = (MySqlDataAdapter)this.CreateDataAdapter("SELECT * FROM INFORMATION_SCHEMA.COLUMNS " + "WHERE TABLE_NAME = '" + tableName + "'", this.Connection); // Create a data table and fill it from the data adapter DataTable dt = new DataTable(); schemaDA.Fill(dt); return dt; } /// <summary> /// Returns a boolean value indicating if a transaction is in progress /// </summary> /// <returns>True if a transaction is in progress, otherwise, false</returns> public override bool IsTransactionInProgress() { return this.Transaction != null; } /// <summary> /// Creates, opens and returns a SQL connection /// </summary> /// <returns>SQL Connection</returns> public virtual MySqlConnection OpenSqlConnection() { MySqlConnection conn = this.CreateSqlConnection(); conn.Open(); return conn; } /// <summary> /// Saves the specified DataSet and returns the number of rows updated /// </summary> /// <param name="ds">DataSet to be saved</param> /// <param name="tableName">Name of table to be saved</param> /// <param name="primaryKeyName">Primary key name</param> /// <param name="retrieveAutoIncrementPK">Retrieve auto-increment PK flag</param> /// <param name="dbAdapter">Data adapter</param> /// <returns>Number of rows updated</returns> public override int SaveDataSet(DataSet ds, string tableName, string primaryKeyName, bool retrieveAutoIncrementPK, IDbDataAdapter dbAdapter) { this.PrimaryKeyName = primaryKeyName; int RowsUpdated = 0; // If a connection doesn't already exist, create a new one // Note: The Data Adapter object returns the connection to its // original open or closed state after filling the DataSet if (this.Connection == null) { this.Connection = this.CreateSqlConnection(); } // If a data adapter wasn't passed, create a new one now this.DataAdapter = (dbAdapter == null) ? (MySqlDataAdapter)this.CreateDataAdapter() : (MySqlDataAdapter)dbAdapter; // Get the Select Command object if (this.DataAdapter.SelectCommand == null) { this.DataAdapter.SelectCommand = (MySqlCommand)this.CreateUpdateSelectCommand(ds, tableName); } // Set the connection and current transaction (if any) this.DataAdapter.SelectCommand.Connection = this.Connection; this.DataAdapter.SelectCommand.Transaction = this.Transaction; // Get the Insert Command object if (this.DataAdapter.InsertCommand == null) { this.DataAdapter.InsertCommand = (MySqlCommand)this.CreateInsertCommand(); } // Set connection and current transaction (if any) this.DataAdapter.InsertCommand.Connection = this.Connection; this.DataAdapter.InsertCommand.Transaction = this.Transaction; // Get the Update Command object if (this.DataAdapter.UpdateCommand == null) { this.DataAdapter.UpdateCommand = (MySqlCommand)this.CreateUpdateCommand(); } // Set the connection and current transaction (if any) this.DataAdapter.UpdateCommand.Connection = this.Connection; this.DataAdapter.UpdateCommand.Transaction = this.Transaction; // Get the Delete Command object if (this.DataAdapter.DeleteCommand == null) { this.DataAdapter.DeleteCommand = (MySqlCommand)this.CreateDeleteCommand(); } // Set the connection and current transaction (if any) this.DataAdapter.DeleteCommand.Connection = this.Connection; this.DataAdapter.DeleteCommand.Transaction = this.Transaction; // If specified to do so, and the primary key is an autoincrement type, // configure the insert statement to retrieve the autoincrement value if (retrieveAutoIncrementPK && this.PrimaryKeyName != null) { if (ds.Tables[tableName].Columns[this.PrimaryKeyName].AutoIncrement) { // Add a SELECT statement to the insert command that pulls back the autoincrement value MySqlCommand InsertCommand = this.DataAdapter.InsertCommand; InsertCommand.CommandText = this.ConfigureInsertForAutoIncrement(InsertCommand.CommandText); // This code shouldn't have to be here!!! For some reason, // an insert command created by the CommandBuilder will NOT // return the value of the identity column. Rebuilding the // exact same insert command object by hand works!!! MySqlCommand InsertCmd = (MySqlCommand)this.CreateCommand(InsertCommand.CommandText, this.Connection); // Copy the parameters from the auto-created insert command object // to the new custom insert command object foreach (MySqlParameter parm in InsertCommand.Parameters) { InsertCmd.Parameters.Add(new MySqlParameter(parm.ParameterName, parm.MySqlDbType, parm.Size, parm.SourceColumn)); } // Copy the new custom insert command object to the data adapter DataAdapter.InsertCommand = InsertCmd; } } // Issue the update and return the number of rows updated try { RowsUpdated = DataAdapter.Update(ds, ds.Tables[tableName].ToString()); } // catch (SqlException e) // { // // Messages with a severity level of 10 or less are informational // // and indicate problems caused by mistakes in information that // // a user has entered. // // Severity levels from 11-16 are generated by the user. // // Severity levels from 17-25 indicate software or hardware errors // // When a level 17, 18, or 19 error occurs, you can continue working, // // although you may not be able to execute a particular statement // if (e.Class < 17) // { // // } // else // { // throw new SqlException(); // } // } finally { } return RowsUpdated; } /// <summary> /// Translates the parameters in the command object for the /// specific .NET Data Provider /// </summary> /// <param name="cmd">Command object</param> public override void TranslateParameters(IDbCommand cmd) { int Position = -1; string cmdText = cmd.CommandText; if (cmd.CommandType == CommandType.Text && cmdText.IndexOf('?') >= 0) { foreach (IDbDataParameter param in cmd.Parameters) { Position = cmdText.IndexOf('?'); if (Position >= 0) { cmdText = cmdText.Remove(Position, 1); cmdText = cmdText.Insert(Position, param.ParameterName); } } } cmd.CommandText = cmdText; } /// <summary> /// Creates a Delete Command object /// </summary> /// <returns>Delete Command object</returns> public override IDbCommand CreateDeleteCommand() { return this.CommandBuilder.GetDeleteCommand(); } /// <summary> /// Creates an Insert Command object /// </summary> /// <returns>Insert command object</returns> public override IDbCommand CreateInsertCommand() { return this.CommandBuilder.GetInsertCommand(); } /// <summary> /// Creates an Update command object /// </summary> /// <returns>Update command object</returns> public override IDbCommand CreateUpdateCommand() { return this.CommandBuilder.GetUpdateCommand(); } /// <summary> /// Creates a Select Command object used to generate /// insert, update, and delete command objects /// </summary> /// <returns>Select Command object</returns> public override IDbCommand CreateUpdateSelectCommand() { MySqlCommand UpdateSelectCommand = null; // If an UpdateSelectStatement has been specified, create a Select Command object if (this.UpdateSelectStatement != "") { UpdateSelectCommand = new MySqlCommand(this.UpdateSelectStatement); } return UpdateSelectCommand; } /// <summary> /// Configures the passed insertCommand string so it retrieves the /// autoincrement value of the newly added record /// </summary> /// <param name="insertCommand">Insert Command string</param> /// <returns>Insert Command string modified to retrieve the auto-increment value</returns> public override string ConfigureInsertForAutoIncrement(string insertCommand) { return insertCommand += " ; Select @@IDENTITY AS " + this.PrimaryKeyName; } /// <summary> /// Creates a SQL connection, returning it as an IDbConnection /// </summary> /// <returns>Connection</returns> public override IDbConnection CreateConnection() { // Create a connection based on the specified Connection string return new MySqlConnection(this.ConnectionString); } /// <summary> /// Creates a SQL connection returning it as a SqlConnection /// </summary> /// <returns></returns> public virtual MySqlConnection CreateSqlConnection() { // Create a SQL connection based on the specified connection string return new MySqlConnection(this.ConnectionString); } /// <summary> /// Creates a Command object based on the specified command text and connection. /// If a transaction is currently in process, adds it to the command object /// </summary> /// <param name="cmdText">Command Text</param> /// <param name="connection">Connection</param> /// <returns>Command Object</returns> public override IDbCommand CreateCommand(string cmdText, IDbConnection connection) { // Create a new command object MySqlCommand cmd = new MySqlCommand(cmdText, (MySqlConnection)connection); // If a transaction is currently in process, add it to the command object if (this.Transaction != null) { cmd.Transaction = this.Transaction; } return cmd; } /// <summary> /// Creates a Command object based on the specified command text /// </summary> /// <param name="cmdText">Command text</param> /// <returns>Command object</returns> public override IDbCommand CreateCommand(string cmdText) { // Create a new command object return new MySqlCommand(cmdText); } /// <summary> /// Creates a new SqlDataAdapter, passing the specified command object to the constructor /// </summary> /// <param name="command"></param> /// <returns>SqlDataAdapter</returns> public override IDataAdapter CreateDataAdapter(IDbCommand command) { return new MySqlDataAdapter((MySqlCommand)command); } /// <summary> /// Creates a new SqlDataAdapter, passing the specified command and connection objects /// to the constructor. /// </summary> /// <param name="command">Command object</param> /// <param name="connection">Connection object</param> /// <returns>SqlDataAdapter</returns> public override IDataAdapter CreateDataAdapter(string command, IDbConnection connection) { return new MySqlDataAdapter(command, (MySqlConnection)connection); } /// <summary> /// Creates a new SqlDataAdapter /// </summary> /// <returns>SqlDataAdapter</returns> public override IDataAdapter CreateDataAdapter() { return new MySqlDataAdapter(); } /// <summary> /// Creates a parameter object /// </summary> /// <returns>Parameter object</returns> public override IDbDataParameter CreateParameter() { return new MySqlParameter(); } /// <summary> /// Creates a parameter object with the specified name and value /// </summary> /// <param name="name">Parameter name</param> /// <param name="value">Parameter value</param> /// <returns>IDbDataParameter</returns> public override IDbDataParameter CreateParameter(string name, object value) { return new MySqlParameter(name, value); } /// <summary> /// Executes the specified command in the specified database and /// returns an XML Reader object /// </summary> /// <param name="command">Command to be executed</param> /// <returns>XmlReader</returns> public override XmlReader ExecXmlReader(string command) { // MySqlCommand Command; XmlReader xr = null; #region No XML read from ByteFX // bool AutoCloseConnection = true; // // // Create a connection if one doesn't exist // if (this.Connection == null) // { // this.Connection = this.CreateSqlConnection(); // } // // // Open the connection if it's not already open // if (this.Connection.State != ConnectionState.Open) // { // this.Connection.Open(); // } // else // { // AutoCloseConnection = false; // } // // // Create the Command object // Command = (MySqlCommand)CreateCommand(command, Connection); // // // Execute the Command // xr = Command.ExecuteXmlReader(); // // // if (AutoCloseConnection) // { // this.Connection.Close(); // } #endregion return xr; } } }
