C# OleDbCommandBuilder.GetDeleteCommand方法代码示例

        // update by the dataset from the Form
        public void Update(string dbName, string tabName)
        {
            OleDbConnection con = getCon(dbName);
            string sql = "select * From " + tabName;
            sda = new OleDbDataAdapter(sql, con);

            OleDbCommandBuilder builder = new OleDbCommandBuilder(sda);
            sda.InsertCommand = builder.GetInsertCommand();
            sda.DeleteCommand = builder.GetDeleteCommand();
            sda.UpdateCommand = builder.GetUpdateCommand();

            this.ds = new DataSet();
            sda.Fill(this.ds, tabName);
        }

        public void Insert(DataTable dt, String tableName)
        {
            using (connection = new OleDbConnection(connString))
            {
                connection.Open();
                String query = "SELECT * FROM " + tableName;

                using(OleDbCommand command = new OleDbCommand(query, connection))
                {
                    using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
                    {
                        using (OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(adapter))
                        {
                            adapter.DeleteCommand = commandBuilder.GetDeleteCommand();
                            adapter.InsertCommand = commandBuilder.GetInsertCommand();
                            adapter.UpdateCommand = commandBuilder.GetUpdateCommand();
                        }
                    }
                }
            }
        }

        public void ExtractTableParameters(string TableName, System.Data.IDbDataAdapter adapter, out DatabaseCache InsertCache, out DatabaseCache DeleteCache, out DatabaseCache UpdateCache, out DatabaseCache IsExistCache, out System.Data.DataTable dt)
        {
            adapter.SelectCommand.CommandText = "select top 1 * from " + TableName;

            DataSet ds = new DataSet();

            dt = adapter.FillSchema(ds, SchemaType.Source)[0];

            dt.TableName = TableName;

            OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter as OleDbDataAdapter);

            builder.ConflictOption = ConflictOption.OverwriteChanges;
            //builder.SetAllValues = false;
            OleDbCommand InsertCmd = builder.GetInsertCommand(true);
            builder.ConflictOption = ConflictOption.OverwriteChanges; InsertCache = new DatabaseCache(InsertCmd.CommandText, InsertCmd.Parameters);
            InsertCache.CurrentTable = dt;

            foreach (DataColumn c in dt.Columns)
            {
                if (c.AutoIncrement)
                {
                    InsertCache.IsHaveAutoIncrement = true;
                    InsertCache.SQL += ";Select @@IDENTITY;";
                    break;
                }
            }

            OleDbCommand UpdateCmd = builder.GetUpdateCommand(true);
            UpdateCache = new DatabaseCache(UpdateCmd.CommandText, UpdateCmd.Parameters);
            UpdateCache.CurrentTable = dt;

            OleDbCommand DeleteCmd = builder.GetDeleteCommand(true);
            DeleteCache = new DatabaseCache(DeleteCmd.CommandText, DeleteCmd.Parameters);
            DeleteCache.CurrentTable = dt;

            IsExistCache = new DatabaseCache(DeleteCmd.CommandText, DeleteCmd.Parameters);
            IsExistCache.CurrentTable = dt;
            IsExistCache.SQL = IsExistCache.SQL.Replace("DELETE FROM [" + TableName + "]", "Select count(1) from [" + TableName + "] with(nolock) ");
        }

        public static bool OpenConnexion()
        {
            _bddConnection = new OleDbConnection(ConnexionWords);
            _bddCommand = new OleDbCommand
            {
                Connection = _bddConnection,
                CommandText = "SELECT * from Clients",
                CommandType = CommandType.Text
            };
            _bddAdapter = new OleDbDataAdapter(_bddCommand);
            OleDbCommandBuilder bCB = new OleDbCommandBuilder(_bddAdapter);
            _bddDataTable = new DataTable("Clients");


            if (_bddConnection == null)
            {
            }
            else if (_bddConnection.State == ConnectionState.Open)
            {
                new ClientListException("La base de donnée est déja ouverte !");
                return false;
            }
            if (!File.Exists(Settings.Default.BDDPath))
            {
                new ClientListException("La base de donnée n'existe pas !");
                return false;
            }

            _bddAdapter.UpdateCommand = bCB.GetUpdateCommand();
            _bddAdapter.DeleteCommand = bCB.GetDeleteCommand();
            _bddAdapter.InsertCommand = bCB.GetInsertCommand();

            _bddConnection.Open();
            _bddAdapter.Fill(_bddDataTable);
            return true;
        }

        private void saveChanges_DoWork(object sender, DoWorkEventArgs e)
        {
            try
            {

                string selectStatement = "SELECT * From [members];";
                OleDbConnection conn = new OleDbConnection(thisWindow.secureConnectionString);
                conn.Open();
                OleDbDataAdapter OleDbDa = new OleDbDataAdapter();
                OleDbDa.SelectCommand = new OleDbCommand(selectStatement, conn);
                OleDbCommandBuilder cb = new OleDbCommandBuilder(OleDbDa);
                cb.QuotePrefix = "[";
                cb.QuoteSuffix = "]";
                OleDbDa.InsertCommand = cb.GetInsertCommand();
                OleDbDa.DeleteCommand = cb.GetDeleteCommand();
                OleDbDa.UpdateCommand = cb.GetUpdateCommand();
                OleDbDa.Update(dataSet, "mainTable");
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show("An error occured while trying to save. Message: " + ex.Message);
            }
        }

        private void GenerateReport(DataTable dtReports)
        {
            Base.CleanTable(Base.tblReports, Base.cnnStrReports);

            try
            {
                DataRow drReports;

                string sqlStr = "SELECT * FROM " + Base.tblReports;

                OleDbConnection cnn = new OleDbConnection(Base.cnnStrReports);
                OleDbDataAdapter oda = new OleDbDataAdapter(sqlStr, cnn);
                OleDbCommandBuilder ocb = new OleDbCommandBuilder(oda);

                cnn.Open();

                DataSet ds = new DataSet();
                DataTable dt = new DataTable();
                DataRow dr;

                oda.Fill(ds, Base.tblReports);
                dt = ds.Tables[Base.tblReports];

                for (int i = 0; i < dtReports.Rows.Count; i++)
                {
                    dr = dt.NewRow();
                    drReports = dtReports.Rows[i];
                    dr[0] = drReports[0];
                    dr[1] = drReports[1];
                    dt.Rows.Add(dr);
                }

                oda.DeleteCommand = ocb.GetDeleteCommand();

                if (oda.Update(ds, Base.tblReports) == 1)
                    ds.AcceptChanges();
                else
                    ds.RejectChanges();

                sqlStr = null;

                cnn.Close();

                dt.Dispose();
                ds.Dispose();
                ocb.Dispose();
                oda.Dispose();
                cnn.Dispose();

                dr = null;
                dt = null;
                ds = null;
                ocb = null;
                oda = null;
                cnn = null;


                this.Activate();
                rptViewCount.Focus();

                rptViewCount.ZoomMode = Microsoft.Reporting.WinForms.ZoomMode.FullPage;

                this.PageRanksTableAdapter.Fill(this.reportsDataSet.PageRanks);
                this.rptViewCount.RefreshReport();


                Base.CleanTable(Base.tblReports, Base.cnnStrReports);
            }
            catch (Exception ex)
            {
                MessageBox.Show(errReport + ex.Message, errReportHeader, MessageBoxButtons.OK, MessageBoxIcon.Error, MessageBoxDefaultButton.Button1, MessageBoxOptions.RightAlign);
                Base.Loading(this, false);
                return;
            }
            finally
            {
            }
        }

        public static int Save(DataTable tb,string tbName)
        {
            if (连接数据库() == true)
            {

                tb.TableName = "dbo." + tbName;
                OleDbDataAdapter da = new OleDbDataAdapter(string.Format("select * from {0}", "dbo." + tbName), 数据库操作对象);
                da.MissingSchemaAction = MissingSchemaAction.Ignore;
                OleDbCommandBuilder CommandBuilder = new OleDbCommandBuilder(da);
                da.InsertCommand = CommandBuilder.GetInsertCommand();
                da.DeleteCommand = CommandBuilder.GetDeleteCommand();
                da.UpdateCommand = CommandBuilder.GetUpdateCommand();
                return da.Update(tb);
            }
            return -1;
        }

        /// <summary>
        /// Pre Condition: The paremeters will be in order of passing the dataset and the string table name
        /// Post Condition: The update dataset will be persisted in the data base
        /// Description: This method will update the database based on the update
        /// </summary>
        /// <param name="pDataSet"> The dataset that contains the updated records</param>
        /// <param name="pStrTableName"> The table name will be updated in the database </param>
        public void SaveData(DataSet pDataSet, string pStrTableName)
        {
            string strQuery = "SELECT * FROM " + pStrTableName;

            OleDbDataAdapter dbDA = new OleDbDataAdapter(strQuery, _dbConn);


            try
            {

                // setup the command builders
                OleDbCommandBuilder dbBLD = new OleDbCommandBuilder(dbDA);
                dbDA.InsertCommand = dbBLD.GetInsertCommand();
                dbDA.UpdateCommand = dbBLD.GetUpdateCommand();
                dbDA.DeleteCommand = dbBLD.GetDeleteCommand();

                // subsrcibe to the OleDBRowUpdateEventHandler
                dbDA.RowUpdated += new OleDbRowUpdatedEventHandler(onRowUpdated);
                _dbConn.Open();
                dbDA.Update(pDataSet, pStrTableName);
                pDataSet.Tables[pStrTableName].AcceptChanges();
                _dbConn.Close();
                pStrTableName = null;

            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
            }
            finally
            {
                _dbConn.Close();
            }
        }

    private string CleanPageImages(string fullPath)
    {
        string msg = "Cleaned";

        try
        {
            while (true)
            {
                string tbl = "pics";
                string sqlStr = "SELECT * FROM " + tbl;

                OleDbConnection cnn = new OleDbConnection(Base.cnnStrPics);
                OleDbDataAdapter oda = new OleDbDataAdapter(sqlStr, cnn);
                OleDbCommandBuilder ocb = new OleDbCommandBuilder(oda);

                cnn.Open();

                DataSet ds = new DataSet();
                DataTable dt = new DataTable();
                DataRow dr;

                ocb.QuotePrefix = "[";
                ocb.QuoteSuffix = "]";
                oda.Fill(ds, tbl);

                dt = ds.Tables[tbl];

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dr = dt.Rows[i];

                    if ((dr["location"].ToString().Trim()).Contains(fullPath))
                    {
                        dr.Delete();

                        oda.DeleteCommand = ocb.GetDeleteCommand();

                        if (oda.Update(ds, tbl) == 1)
                        {
                            ds.AcceptChanges();
                            msg = "Cleaned";
                            i--;
                        }
                        else
                        {
                            ds.RejectChanges();
                            msg = "Rejected";
                            break;
                        }
                    }
                }

                cnn.Close();

                ds.Dispose();
                dt.Dispose();
                ocb.Dispose();
                oda.Dispose();
                cnn.Dispose();

                ds = null;
                ocb = null;
                oda = null;
                dr = null;
                dt = null;
                cnn = null;

                break;
            }
        }
        catch (Exception ex)
        {
            msg = ex.Message;
        }
        finally
        {
        }

        return msg;
    }

    private string RemoveImages(string[] ids)
    {
        string msg = string.Empty;

        try
        {
            while (true)
            {
                string tbl = "pics";
                string sqlStr = "SELECT * FROM " + tbl;

                OleDbConnection cnn = new OleDbConnection(Base.cnnStrPics);
                OleDbDataAdapter oda = new OleDbDataAdapter(sqlStr, cnn);
                OleDbCommandBuilder ocb = new OleDbCommandBuilder(oda);

                cnn.Open();

                DataSet ds = new DataSet();
                DataTable dt = new DataTable();
                DataRow dr;

                ocb.QuotePrefix = "[";
                ocb.QuoteSuffix = "]";
                oda.Fill(ds, tbl);

                dt = ds.Tables[tbl];

                foreach (string id in ids)
                {
                    bool found = false;

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        dr = dt.Rows[i];

                        if (dr["id"].ToString().Trim() == id.Trim())
                        {
                            found = true;

                            dr.Delete();
                        }
                    }

                    if (found)
                    {
                        oda.DeleteCommand = ocb.GetDeleteCommand();

                        if (oda.Update(ds, tbl) == 1)
                        {
                            ds.AcceptChanges();

                            msg = "Removed";
                        }
                        else
                        {
                            ds.RejectChanges();
                            msg = "Rejected";
                            break;
                        }
                    }
                    else
                    {
                        msg = "Image Not Found And Cannot Be Removed...";
                        break;
                    }
                }

                cnn.Close();

                ds.Dispose();
                dt.Dispose();
                ocb.Dispose();
                oda.Dispose();
                cnn.Dispose();

                ds = null;
                ocb = null;
                oda = null;
                dr = null;
                dt = null;
                cnn = null;

                break;
            }
        }
        catch (Exception ex)
        {
            msg = ex.Message;
        }
        finally
        {
        }

        return msg;
    }

    public string NodesErase(string fullPath, string parentPath, string tbl, string legal)
    {
        string msg = string.Empty;

        if (tLegal == legal.Trim() && isDaysLeft)
        {
            fullPath = fullPath.Trim();
            parentPath = parentPath.Trim();
            tbl = tbl.Trim();

            string sqlStr = "SELECT * FROM " + tbl;

            try
            {
                OleDbConnection cnn = new OleDbConnection(Base.cnnStr);
                OleDbDataAdapter oda = new OleDbDataAdapter(sqlStr, cnn);
                OleDbCommandBuilder ocb = new OleDbCommandBuilder(oda);

                cnn.Open();

                DataSet ds = new DataSet();
                DataTable dt = new DataTable();
                DataRow dr;

                ocb.QuotePrefix = "[";
                ocb.QuoteSuffix = "]";
                oda.Fill(ds, tbl);

                bool found = false;

                dt = ds.Tables[tbl];

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    dr = dt.Rows[i];

                    if ((dr["fullpath"].ToString().Trim() + "\\").Contains(fullPath + "\\"))
                    {
                        found = true;

                        dr.Delete();

                        oda.DeleteCommand = ocb.GetDeleteCommand();

                        if (oda.Update(ds, tbl) == 1)
                        {
                            msg = CleanPageImages(fullPath);

                            if (msg != "Cleaned")
                                return msg;

                            ds.AcceptChanges();
                            msg = "Erased";
                            i--;
                        }
                        else
                        {
                            ds.RejectChanges();
                            msg = "Rejected";
                            break;
                        }
                    }
                }

                if (!found)
                    msg = "Not Found";

                cnn.Close();

                ds.Dispose();
                dt.Dispose();
                ocb.Dispose();
                oda.Dispose();
                cnn.Dispose();

                ds = null;
                ocb = null;
                oda = null;
                dr = null;
                dt = null;
                cnn = null;

                NodesReSort(parentPath, tbl);
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            finally
            {
                tbl = null;
                sqlStr = null;
            }
        }
        else
            msg = errInvalidLegal;

        return msg;
    }

        /// <summary>
        /// Pre-condition:  The parameters contain the updated dataset and the table name respectively
        /// Post-Condition: The updated dataset will be persisted in the database.
        /// Description:    This method will update the database based on
        ///                 the updated records in the dataset for the specified table.
        /// </summary>
        /// <param name="DataSet_param"></param>
        /// <param name="strTableName_param"></param>
        public void saveData(DataSet pDataSet, string pStrTableName)
        {
            //Specify SELECT Statment for data adapter
            string strSQL = "SELECT * FROM " + pStrTableName;
            //Create an instance of the data adaopter
            OleDbDataAdapter dbDA = new OleDbDataAdapter(strSQL, _dbConn);

            try
            {
                //Set up the command builder - not suitable for large databases
                OleDbCommandBuilder dbBLD = new OleDbCommandBuilder(dbDA);
                dbDA.InsertCommand = dbBLD.GetInsertCommand();
                dbDA.UpdateCommand = dbBLD.GetUpdateCommand();
                dbDA.DeleteCommand = dbBLD.GetDeleteCommand();

                //Subscribe to the OleDbRowUpdateEventHandler
                dbDA.RowUpdated += new OleDbRowUpdatedEventHandler(dbDA_RowUpdated);

                //Update the database using the 'Update' method of the data adapter object
                if (_dbConn.State == ConnectionState.Closed) _dbConn.Open();

                dbDA.Update(pDataSet, pStrTableName);
                //Close the connection
                _dbConn.Close();
                //Refresh the dataset
                pDataSet.Tables[pStrTableName].AcceptChanges();
            }
            catch
            {
                _dbConn.Close();
                //MessageBox.Show(e.ToString());
            }
        }

 DbDataAdapter IDataProviderDatabase.CreateAdapter(IDbConnection connection, Microsoft.Matrix.Packages.DBAdmin.DBEngine.Table table, bool readOnly)
 {
     OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [" + table.Name.Replace("]", "]]") + "]", (OleDbConnection) connection);
     if (!readOnly)
     {
         OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
         builder.QuotePrefix = "[";
         builder.QuoteSuffix = "]";
         OleDbCommand insertCommand = builder.GetInsertCommand();
         OleDbCommand deleteCommand = builder.GetDeleteCommand();
         OleDbCommand updateCommand = builder.GetUpdateCommand();
         foreach (Microsoft.Matrix.Packages.DBAdmin.DBEngine.Column column in table.Columns)
         {
             if (column.IsIdentity)
             {
                 insertCommand.CommandText = insertCommand.CommandText + ";SELECT @[email protected]@IDENTITY";
                 insertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;
                 OleDbParameter parameter = new OleDbParameter();
                 parameter.ParameterName = "@retId";
                 parameter.DbType = column.DbType;
                 parameter.Size = column.Size;
                 parameter.Direction = ParameterDirection.Output;
                 parameter.IsNullable = column.AllowNulls;
                 parameter.Precision = (byte) column.NumericPrecision;
                 parameter.Scale = (byte) column.NumericScale;
                 parameter.SourceColumn = column.Name;
                 parameter.SourceVersion = DataRowVersion.Current;
                 parameter.Value = DBNull.Value;
                 insertCommand.Parameters.Add(parameter);
                 break;
             }
         }
         adapter.DeleteCommand = deleteCommand;
         adapter.UpdateCommand = updateCommand;
         adapter.InsertCommand = insertCommand;
     }
     return adapter;
 }

        public void UpdateTableData(string tableName, DataTable datatable)
        {
            string query = $"Select* From {tableName}";
            OleDbConnection conn = new OleDbConnection();
            conn.ConnectionString = [email protected]"Provider=Microsoft.Jet.OLEDB.4.0; Data Source= {path}";

            DataTable compareTable = RetrieveTableData(tableName).Tables[0];
            try
            {
                foreach (DataRow row in compareTable.Rows)
                {
                    if (row["List_Name"].ToString().Equals( datatable.Rows[0]["List_Name"].ToString() )){
                        compareTable.Rows[compareTable.Rows.IndexOf(row)].Delete();
                    }
                }
                compareTable.Rows.Add(datatable.Rows[0].ItemArray);

            }
            catch
            {
                compareTable = datatable;
            }

            OleDbCommand command = new OleDbCommand(query, conn);

            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(command);

            OleDbCommandBuilder oledbCommandBuilder = new OleDbCommandBuilder(dataAdapter);

            dataAdapter.DeleteCommand = oledbCommandBuilder.GetDeleteCommand();
            dataAdapter.InsertCommand = oledbCommandBuilder.GetInsertCommand();
            dataAdapter.UpdateCommand = oledbCommandBuilder.GetUpdateCommand();

            dataAdapter.Update(compareTable);

            conn.Close();
        }

        public static void CleanTable(string tbl, string cnnStr)
        {
            try
            {
                string sqlStr = "SELECT * FROM " + tbl;

                OleDbConnection cnn = new OleDbConnection(cnnStr);
                OleDbDataAdapter oda = new OleDbDataAdapter(sqlStr, cnn);
                OleDbCommandBuilder ocb = new OleDbCommandBuilder(oda);

                cnn.Open();

                DataSet ds = new DataSet();
                DataTable dt = new DataTable();

                oda.Fill(ds, tbl);
                dt = ds.Tables[tbl];

                foreach (DataRow dr in dt.Rows)
                    dr.Delete();

                oda.DeleteCommand = ocb.GetDeleteCommand();

                if (oda.Update(ds, tbl) == 1)
                    ds.AcceptChanges();
                else
                    ds.RejectChanges();

                cnn.Close();

                dt.Dispose();
                ds.Dispose();
                ocb.Dispose();
                oda.Dispose();
                cnn.Dispose();

                dt = null;
                ds = null;
                ocb = null;
                oda = null;
                cnn = null;

                sqlStr = null;
            }
            catch
            {
            }
            finally
            {
            }
        }