C# MySqlCommandBuilder.Dispose方法代码示例

        public MySqlTableContext( DataTable dataTable, MySqlConnection connection )
        {
            this.Connection = connection;

            this.DataTable = dataTable;
            this.DataAdapter = new MySqlDataAdapter(
                string.Format( "SELECT * FROM {0} WHERE 1=0",
                this.DataTable.TableName ), this.Connection );

            this.DataAdapter.UpdateBatchSize = 50;

            // Using workaround for MySQL Connector bug described at:
            // http://bugs.mysql.com/bug.php?id=39815
            // Dispose the builder before setting adapter commands.
            MySqlCommandBuilder builder = new MySqlCommandBuilder( this.DataAdapter );
            MySqlCommand updateCommand = builder.GetUpdateCommand();
            MySqlCommand insertCommand = builder.GetInsertCommand();
            MySqlCommand deleteCommand = builder.GetDeleteCommand();
            builder.Dispose();
            this.DataAdapter.UpdateCommand = updateCommand;
            this.DataAdapter.InsertCommand = insertCommand;
            this.DataAdapter.DeleteCommand = deleteCommand;

            this.DataAdapter.RowUpdating += new MySqlRowUpdatingEventHandler( DataAdapter_RowUpdating );
            this.DataAdapter.RowUpdated += this.OnRowUpdated;

            // Create a command to fetch the last inserted id
            identityCommand = this.Connection.CreateCommand();
            identityCommand.CommandText = "SELECT LAST_INSERT_ID()";

            this.RefreshIdentitySeed();
        }

        public void AutoIncrementColumns()
        {
            execSQL("DROP TABLE IF EXISTS Test");
            execSQL("CREATE TABLE Test (id int(10) unsigned NOT NULL auto_increment primary key)");
            execSQL("INSERT INTO Test VALUES(NULL)");

            MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
            MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
            DataSet ds = new DataSet();
            da.Fill(ds);
            Assert.AreEqual(1, ds.Tables[0].Rows[0]["id"]);
            DataRow row = ds.Tables[0].NewRow();
            ds.Tables[0].Rows.Add(row);

            try
            {
                da.Update(ds);
            }
            catch (Exception ex)
            {
                Assert.Fail(ex.Message);
            }

            ds.Clear();
            da.Fill(ds);
            Assert.AreEqual(1, ds.Tables[0].Rows[0]["id"]);
            Assert.AreEqual(2, ds.Tables[0].Rows[1]["id"]);
            cb.Dispose();
        }

    public void UpdateDecimalColumns()
    {
      execSQL("CREATE TABLE Test (id int not null auto_increment primary key, " +
        "dec1 decimal(10,1))");

      MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
      MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
      DataTable dt = new DataTable();
      da.Fill(dt);
      DataRow row = dt.NewRow();
      row["id"] = DBNull.Value;
      row["dec1"] = 23.4;
      dt.Rows.Add(row);
      da.Update(dt);

      dt.Clear();
      da.Fill(dt);
      Assert.AreEqual(1, dt.Rows.Count);
      Assert.AreEqual(1, dt.Rows[0]["id"]);
      Assert.AreEqual(23.4, dt.Rows[0]["dec1"]);
      cb.Dispose();
    }

        public bool UpdateTable(DataTable table, string tableName)
        {
            try
            {
                TableHelper.SetDefaultColumnValues(table);

                var con = CONNECTION.OpenCon();

                var adapter = new MySqlDataAdapter(string.Format(@"SELECT * FROM {0}", tableName), con);
                var cmd = new MySqlCommandBuilder(adapter);

                adapter.Update(table);

                cmd.Dispose();
                adapter.Dispose();
                CONNECTION.CloseCon(con);

                return true;
            }
            catch (Exception ex)
            {
                SLLog.WriteError(new LogData
                {
                    Source = ToString(),
                    FunctionName = "UpdateTable Error!",
                    Ex = ex,
                });
                return false;
            }
        }

        public void UpdateDataSet()
        {
            execSQL("DROP TABLE IF EXISTS Test");
            execSQL("CREATE TABLE Test (id INT NOT NULL, blob1 LONGBLOB, text1 LONGTEXT, PRIMARY KEY(id))");
            execSQL("INSERT INTO Test VALUES( 1, NULL, 'Text field' )");

            try
            {
                MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
                MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
                DataTable dt = new DataTable();
                da.Fill(dt);

                string s = (string)dt.Rows[0][2];
                Assert.AreEqual("Text field", s);

                byte[] inBuf = Utils.CreateBlob(512);
                dt.Rows[0].BeginEdit();
                dt.Rows[0]["blob1"] = inBuf;
                dt.Rows[0].EndEdit();
                DataTable changes = dt.GetChanges();
                da.Update(changes);
                dt.AcceptChanges();

                dt.Clear();
                da.Fill(dt);
                cb.Dispose();

                byte[] outBuf = (byte[])dt.Rows[0]["blob1"];
                Assert.AreEqual(inBuf.Length, outBuf.Length,
                          "checking length of updated buffer");
                for (int y = 0; y < inBuf.Length; y++)
                    Assert.AreEqual(inBuf[y], outBuf[y], "checking array data");
            }
            catch (Exception ex)
            {
                Assert.Fail(ex.Message);
            }
        }

        public void QuietOpenAndClose()
        {
            execSQL("CREATE TABLE Test (id INT, PRIMARY KEY(id))");
              execSQL("INSERT INTO Test VALUES(1)");

              using (MySqlConnection c = new MySqlConnection(GetConnectionString(true)))
              {
            MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", c);
            MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
            Assert.IsTrue(c.State == ConnectionState.Closed);
            DataTable dt = new DataTable();
            da.Fill(dt);
            Assert.IsTrue(c.State == ConnectionState.Closed);
            Assert.AreEqual(1, dt.Rows.Count);

            dt.Rows[0][0] = 2;
            DataRow[] rows = new DataRow[1];
            rows[0] = dt.Rows[0];
            da.Update(dt);
            Assert.IsTrue(c.State == ConnectionState.Closed);

            dt.Clear();
            c.Open();
            Assert.IsTrue(c.State == ConnectionState.Open);
            da.Fill(dt);
            Assert.IsTrue(c.State == ConnectionState.Open);
            Assert.AreEqual(1, dt.Rows.Count);
            cb.Dispose();
              }
        }

        public static void ImportTeams(string file)
        {
            int teamsImported = 0;
            int importErrors = 0;
            int teamsToImport = 0;

            SetStatusBarMsg("Importing teams info...", "Yellow");

            BackgroundWorker worker = new BackgroundWorker();
            worker.WorkerReportsProgress = true;

            worker.DoWork += delegate(object s, DoWorkEventArgs args)
            {
                OracleConnection cn = null;
                OracleCommand cmd = null;
                OracleDataReader rdr = null;
                OracleDataAdapter adp = null;
                OracleCommandBuilder bldr = null;

                MySqlConnection cnMySql = null;
                MySqlCommand cmdMySql = null;
                MySqlDataReader rdrMySql = null;
                MySqlDataAdapter adpMySql = null;
                MySqlCommandBuilder bldrMySql = null;

                //DataTable tblPlayer = null;
                DataTable tbl = null;
                DataRow row;

                string sql;
                //long teamId;
                int i;

                int totalPicks = 0;

                DataSet dsTeams = new DataSet();

                dsTeams.ReadXml(file);

                try
                {
                    cn = createConnectionSDR();

                    teamsToImport = dsTeams.Tables["team"].Rows.Count;

                    foreach (DataRow xmlRow in dsTeams.Tables["team"].Rows)
                    {
                        totalPicks = 0;

                        if (xmlRow["teamid"].ToString().Trim() != "")
                        {

                            if (ConfigurationManager.AppSettings["DraftType"].ToUpper() == "NFL")
                            {

                                #region Picks

                                try
                                {
                                    sql = "select count(*) from draftorder where teamid = " + xmlRow["teamid"];
                                    cmd = new OracleCommand(sql, cn);
                                    adp = new OracleDataAdapter(cmd);

                                    tbl = new DataTable();

                                    adp.Fill(tbl);

                                    if (tbl.Rows.Count > 0)
                                    {
                                        totalPicks = int.Parse(tbl.Rows[0][0].ToString());
                                    }
                                }
                                finally
                                {
                                    cmd.Dispose();
                                    adp.Dispose();
                                    tbl.Dispose();
                                }

                                #endregion

                                #region 6 Matrix Notes

                                //import the 4 matrix notes
                                for (i = 1; i <= 6; i++)
                                {
                                    if (xmlRow["note" + i.ToString()].ToString().Trim() != "")
                                    {
                                        sql = "select * from espnews.drafttidbits where referencetype = 2 and tidbitorder = " + i.ToString() + " and referenceid = " + xmlRow["teamid"];
                                        cmd = new OracleCommand(sql, cn);
                                        adp = new OracleDataAdapter(cmd);
                                        bldr = new OracleCommandBuilder(adp);

                                        tbl = new DataTable();

                                        adp.Fill(tbl);

                                        if (tbl.Rows.Count == 0)
                                        {
                                            row = tbl.Rows.Add();
//.........这里部分代码省略.........

        public void InsertDateTimeValue()
        {
            using (MySqlConnection c = new MySqlConnection(conn.ConnectionString +
            ";allow zero datetime=yes"))
              {
            c.Open();
            MySqlDataAdapter da = new MySqlDataAdapter("SELECT id, dt FROM Test", c);
            MySqlCommandBuilder cb = new MySqlCommandBuilder(da);

            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("id", typeof(int)));
            dt.Columns.Add(new DataColumn("dt", typeof(DateTime)));

            da.Fill(dt);

            DateTime now = DateTime.Now;
            DataRow row = dt.NewRow();
            row["id"] = 1;
            row["dt"] = now;
            dt.Rows.Add(row);
            da.Update(dt);

            dt.Clear();
            da.Fill(dt);
            cb.Dispose();

            Assert.AreEqual(1, dt.Rows.Count);
            Assert.AreEqual(now.Date, ((DateTime)dt.Rows[0]["dt"]).Date);
              }
        }

        private void SaveConditions()
        {
            if (!bInputConditionOK) { return; }
            if (ConditionTable.Rows.Count > 0)
            {
              //  DataRow dr_Con = ConditionTable.Rows[c1FlexGrid_Conditions.Row - 1];
                try
                {
                    MySqlCommandBuilder myCommand = new MySqlCommandBuilder(da_Condition);
                    da_Condition.Update(ConditionTable);
                    myCommand.Dispose();
                }
                catch (DBConcurrencyException DBCe)
                {
                    mySave.InsertRow((int)Definition.Message.D_DEBUG, "button_Condition_Save_Click: \r\n" + DBCe.ToString());
                }
                catch (Exception ex) { mySave.InsertRow((int)Definition.Message.D_ALARM, ex.ToString()); }

                c1FlexGrid_Conditions.Update();
                bConditionTableChanged = false;
                bInputConditionOK = true;
                SaveTimeWarningAlarm();
            }
        }

        public static void GetSchoolsFromSDR()
        {
            int teamsToImport = 0;
            int teamsImported = 0;

            SetStatusBarMsg("Importing schools...", "Yellow");

            BackgroundWorker worker = new BackgroundWorker();
            worker.WorkerReportsProgress = true;

            worker.DoWork += delegate(object s, DoWorkEventArgs args)
            {
                OracleConnection cn = null;
                OracleCommand cmd = null;
                OracleDataReader rdr = null;
                DataTable tbl = null;

                try
                {
                    cn = createConnectionSDR();

                    if (cn != null)
                    {
                        String sql = "select * from espnews.news_teams where league_id = 'NCAAB'";
                        cmd = new OracleCommand(sql, cn);
                        rdr = cmd.ExecuteReader();

                        tbl = new DataTable();

                        tbl.Load(rdr);

                        rdr.Close();
                        rdr.Dispose();

                        MySqlConnection myCn = createConnectionMySql();

                        teamsToImport = tbl.Rows.Count;

                        foreach (DataRow row in tbl.Rows)
                        {
                            sql = "select * from teams where id = " + row["team_id"].ToString();

                            MySqlCommand myCmd = new MySqlCommand(sql, myCn);
                            MySqlDataAdapter myAdp = new MySqlDataAdapter(myCmd);
                            MySqlCommandBuilder myBldr = new MySqlCommandBuilder(myAdp);
                            DataTable myTbl = new DataTable();
                            DataRow myRow;

                            try
                            {
                                myAdp.Fill(myTbl);

                                if (myTbl.Rows.Count == 0)
                                {
                                    myRow = myTbl.Rows.Add();
                                }
                                else
                                {
                                    myRow = myTbl.Rows[0];
                                }

                                myRow["id"] = Convert.ToInt32(row["team_id"]);
                                myRow["name"] = row["team_name"].ToString();
                                myRow["tricode"] = row["abbrev_4"].ToString();
                                myRow["city"] = row["city_st_name"].ToString();
                                myRow["league"] = row["league_id"].ToString();

                                DataSet dsLogos = getSchoolLogos(new Int32[] { Convert.ToInt32(row["team_id"].ToString()) });

                                if (dsLogos != null)
                                {
                                    myRow["logo"] = "\\\\HEADSHOT01\\Images\\" + dsLogos.Tables[0].Rows[0]["IMAGEPATH"].ToString().ToUpper().Replace(".TGA", "_256.TGA");
                                    myRow["swatch"] = "\\\\HEADSHOT01\\Images\\" + dsLogos.Tables[0].Rows[0]["SWATCHPATH"].ToString().ToUpper();
                                }

                                myAdp.Update(myTbl.GetChanges());
                                myTbl.AcceptChanges();

                                teamsImported++;

                                worker.ReportProgress(teamsImported / teamsToImport);
                            }
                            finally
                            {
                                if (myCmd != null) myCmd.Dispose();
                                if (myTbl != null) myTbl.Dispose();
                                if (myAdp != null) myAdp.Dispose();
                                if (myBldr != null) myBldr.Dispose();
                            }
                        }
                    }
                    else
                    {
                        System.Windows.MessageBox.Show("There was a problem connecting to the SDR database");
                    }
                }
                finally
                {
                    if (cmd != null) cmd.Dispose();
                    if (tbl != null) tbl.Dispose();
//.........这里部分代码省略.........

 private void SaveCommands()
 {
     if (!bInputCommandOK) { return; }
        try
        {
        MySqlCommandBuilder myCommand = new MySqlCommandBuilder(da_Commands);
        da_Commands.Update(CommandTable);
        bCommandTableChanged = false;
        bInputCommandOK = true;
        myCommand.Dispose();
        }
        catch (DBConcurrencyException DBCe)
        {
        mySave.InsertRow((int)Definition.Message.D_DEBUG, "button_Command_Save_Click: \r\n" + DBCe.ToString());
        }
        catch (Exception ex) { mySave.InsertRow((int)Definition.Message.D_ALARM, ex.ToString()); /* MessageBox.Show(ex.ToString());*/ }
 }

        public Boolean recordSave(String primaryId)
        {
            Boolean newRecord = m_state.Equals(State.Add);
            if (newRecord)
            {
                DataTable dataTable = m_dataSet.Tables[m_tableName];
                DataRow newDataRow = dataTable.NewRow();
                newDataRow[ColumnData.GetName(m_idField)] = primaryId;
                updateFields(newDataRow);
                Object[] columns = newDataRow.ItemArray;
                dataTable.Rows.Add(newDataRow);
                MySqlCommandBuilder mySqlCommandBuilder = new MySqlCommandBuilder(m_sqlDataAdapter);
                m_sqlDataAdapter.Update(m_dataSet, m_tableName);
                m_dataSet.AcceptChanges();
                mySqlCommandBuilder.Dispose();
                m_dataRow = newDataRow;
            }
            else // Edit
            {
                try
                {
                    // http://www.codersource.net/csharp_adonet_tutorial_ed.html
                    m_dataRow.BeginEdit();
                    updateFields(m_dataRow);
                    m_dataRow.EndEdit();
                    if (m_dataRow.HasErrors)
                    {
                        DataColumn[] errorColumns = m_dataRow.GetColumnsInError();
                        for (int error = 0; error < errorColumns.Length; error++)

                        {
                            // TODO: Handle column validation errors
                            MessageBox.Show("Validation error with column " + errorColumns[error].ColumnName);
                        }
                        if (errorColumns.Length != 0)
                        {
                            return false;
                        }
                    }
                    else
                    {

                        /*DataRow[] dataRows = new DataRow[]{m_dataRow};
                        m_dataSet.Merge(dataRows);
                        DataSet dsChanges = m_dataSet.GetChanges(DataRowState.Modified);
                        if (dsChanges.HasErrors)
                        {
                           m_dataSet.RejectChanges();
                        }
                        else
                        {
                            MySqlCommandBuilder mySqlCommandBuilder = new MySqlCommandBuilder(m_sqlDataAdapter);
                            m_sqlDataAdapter.Update(dsChanges, m_tableName);
                            m_dataSet.AcceptChanges();
                        }*/

                        MySqlCommandBuilder mySqlCommandBuilder = new MySqlCommandBuilder(m_sqlDataAdapter); // Last one wins
                        DataRow[] dataRows = new DataRow[]{m_dataRow};
                        m_sqlDataAdapter.Update(dataRows);
                        mySqlCommandBuilder.Dispose();
                    }
                }
                catch (System.Data.DBConcurrencyException e)
                {
            //DB.printDataSetContents(m_dataSet, "TableIO.recordSave()");
                    // http://msdn.microsoft.com/en-us/library/tf579hcz(VS.80).aspx
                    MessageBox.Show("Another user has modified the current record.\n"
                                   + "To perform your modifications:\n"
                                   + "\t1. Jot down your changes\n"
                                   + "\t2. Cancel your changes\n"
                                   + "\t3. Modify the record again"
                                   + "\n\n" + e.Message
                                   + "\n\n" + e.StackTrace,
                                   "Changes Cannot be Saved");
                    return false;
                }
            }
            return true;
        }

        public String recordDelete()
        {
            String id = m_gui.getCurrentEditor().getPrimaryId();
            DataRow[] dataRows = m_dataSet.Tables[m_tableName].Select(ColumnData.GetName(m_idField) + " = " + id);
            if (dataRows.Length != 0)
            {
                try
                {
                    DataRow m_dataRow = dataRows[0];
                    m_dataRow.Delete();
                    MySqlCommandBuilder mySqlCommandBuilder = new MySqlCommandBuilder(m_sqlDataAdapter);
                    m_sqlDataAdapter.Update(m_dataSet, m_tableName);
                    m_dataSet.AcceptChanges();
                    mySqlCommandBuilder.Dispose();
                    m_dataRow = null;
                }
                catch(MySqlException e)
                {
                    switch (e.Number)
                    {
                        case 1451:
                            return "This record cannot be deleted because it is used in other tables\n\n"
                                 + e.GetBaseException().Message;
                        default:
                            return "MySqlException: " + e.Number.ToString();
                    }
                }
            }
            return "";

            /*
            String query = "DELETE FROM "
                         + m_tableName
                         + " WHERE "
                         + ColumnData.GetName(m_idField)
                         + " = "
                         + m_dataRow[0].ToString();
            m_dataRow.Delete();
            m_sqlDataAdapter.Update(m_dataset);
            m_dataSet.AcceptChanges();
            m_dataRow = null;
            setState(TableIO.State.View);
            viewRecord();
            */
        }

    public void UnsignedTypes()
    {
      execSQL("CREATE TABLE Test (b TINYINT UNSIGNED PRIMARY KEY)");

      MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", conn);
      MySqlCommandBuilder cb = new MySqlCommandBuilder(da);

      DataTable dt = new DataTable();
      da.Fill(dt);

      DataView dv = new DataView(dt);
      DataRowView row;

      row = dv.AddNew();
      row["b"] = 120;
      row.EndEdit();
      da.Update(dv.Table);

      row = dv.AddNew();
      row["b"] = 135;
      row.EndEdit();
      da.Update(dv.Table);
      cb.Dispose();

      execSQL("DROP TABLE IF EXISTS Test");
      execSQL("CREATE TABLE Test (b MEDIUMINT UNSIGNED PRIMARY KEY)");
      execSQL("INSERT INTO Test VALUES(20)");
      MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test WHERE (b > ?id)", conn);
      cmd.Parameters.Add("?id", MySqlDbType.UInt16).Value = 10;
      using (MySqlDataReader dr = cmd.ExecuteReader())
      {
        dr.Read();
        Assert.AreEqual(20, dr.GetUInt16(0));
      }
    }

        public static bool SaveCategory(Category category)
        {
            MySqlConnection cn = null;
            MySqlCommand cmd = null;
            MySqlDataAdapter adp = null;
            MySqlCommandBuilder bldr = null;
            DataTable tbl = null;
            DataRow row = null;

            bool saved = false;

            try
            {
                cn = createConnectionMySql();

                String sql = "select * from categories where categoryid = " + category.ID;

                cmd = new MySqlCommand(sql, cn);
                adp = new MySqlDataAdapter(cmd);
                bldr = new MySqlCommandBuilder(adp);
                tbl = new DataTable();

                adp.Fill(tbl);

                if (tbl.Rows.Count == 0)
                {
                    row = tbl.Rows.Add();
                }
                else
                {
                    row = tbl.Rows[0];
                }

                row["categoryname"] = category.FullName;
                row["tricode"] = category.Tricode;

                foreach (Tidbit tidbit in category.Tidbits)
                {
                    updateTidbitMySql(tidbit.ReferenceType, category.ID, tidbit.TidbitOrder, tidbit.TidbitText, tidbit.Timecode, tidbit.Enabled);
                }

                adp.Update(tbl.GetChanges());
                tbl.AcceptChanges();

                saved = true;
            }
            finally
            {
                if (cmd != null) cmd.Dispose();
                if (adp != null) adp.Dispose();
                if (bldr != null) bldr.Dispose();
                if (tbl != null) tbl.Dispose();
                if (cn != null) cn.Close(); cn.Dispose();
            }

            return saved;
        }