C# OleDbCommandBuilder.GetInsertCommand方法代码示例

        private static void Test_Command_CommandBuilder()
        {
            (new OleDbCommand("drop table if exists t", conn)).ExecuteNonQuery();
            (new OleDbCommand("create table t(id int not null default 1,name string default 'cubrid',PRIMARY KEY (id))", conn)).ExecuteNonQuery();
            (new OleDbCommand("insert into t(id,name) value(1,'cubrid');", conn)).ExecuteNonQuery();

            DataSet dataSet = new DataSet();
            DataTable dataTable = new DataTable();
            string query = "select id,name from t";
            OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, conn);

            OleDbCommandBuilder commandBuilder =new OleDbCommandBuilder(dataAdapter);

            dataAdapter.Fill(dataTable);

            //Without the OleDbCommandBuilder this line would fail.
            foreach (System.Data.DataRow row in dataTable.Rows)
            {
                foreach (System.Data.DataColumn col in dataTable.Columns)
                {
                    Assert.AreEqual(row[col], 1);
                    break;
                }
            }

            OleDbCommand t1 = commandBuilder.GetInsertCommand();
            Assert.AreEqual(t1.CommandText, "INSERT INTO T DEFAULT VALUES");
        }

        /// <summary>
        /// 插入数据通过Datatable
        /// </summary>
        /// <param name="_dt"></param>
        /// <returns>影响记录条数</returns>
        public override int DataTableInsert(DataTable _dt)
        {
            bool flag = false;
                int _nResult = 0;
                if (_dt == null)
                    return _nResult;
                string _sCmdText = string.Format("select * from {0} where 1=2", _dt.TableName);
                OleDbCommand _Command = (OleDbCommand)CreateCommand(_sCmdText, CommandType.Text);
                OleDbDataAdapter _adapter = new OleDbDataAdapter(_Command);
                OleDbDataAdapter _adapter1 = new OleDbDataAdapter(_Command);
                OleDbCommandBuilder _builder = new OleDbCommandBuilder(_adapter1);

                _adapter.InsertCommand = _builder.GetInsertCommand();

                if (_adapter.InsertCommand.Parameters.Count < _dt.Columns.Count)
                {
                    flag = true;//因为表中有自增字段，所以CommandBuild生成的InserttCommand的参数中少了自增字段
                    foreach (DataColumn _dc in _dt.Columns)
                    {
                        if (!_adapter.InsertCommand.Parameters.Contains(_dc.ColumnName))
                        {
                            _adapter.InsertCommand.CommandText =
                                _adapter.InsertCommand.CommandText.Insert(_adapter.InsertCommand.CommandText.IndexOf(") VALUES"), ',' + _dc.ColumnName);

                            _adapter.InsertCommand.CommandText =
                                _adapter.InsertCommand.CommandText.Insert(_adapter.InsertCommand.CommandText.Length - 1, ",?");

                            _adapter.InsertCommand.Parameters.Add("@" + _dc.ColumnName, OleDbType.Decimal, _dc.MaxLength, _dc.ColumnName);

                            if (_adapter.InsertCommand.Parameters.Count >= _dt.Columns.Count)
                                break;
                        }
                    }
                }

                if (flag)
                    this.ExecuteNoQuery(string.Format("SET IDENTITY_INSERT {0} on", _dt.TableName));

                this.BeginTransaction();
                try
                {
                    _adapter.InsertCommand.Transaction = _Command.Transaction;
                    _Command.CommandText = "delete from " + _dt.TableName;
                    _Command.ExecuteNonQuery();
                    _nResult = _adapter.Update(_dt);
                    this.CommitTransaction();
                }
                catch (Exception ex)
                {
                    this.RollbackTransaction();
                    throw ex;
                }
                finally
                {
                    if (flag)
                        this.ExecuteNoQuery(string.Format("SET IDENTITY_INSERT {0} OFF", _dt.TableName));
                }
                return _nResult;
        }

        // 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 void InserOrderDetail(Order order)
    {
        DataSet ds=convertToTable(order.OrderProductds, order.OrderID);

        myCmd = new OleDbCommand("Select * From OrderDetails", myConnection);
        myCmd.Transaction = transaction;

        OleDbDataAdapter adapter = new OleDbDataAdapter(myCmd);
        OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);


        adapter.InsertCommand = builder.GetInsertCommand();

        try
        {
            adapter.Update(ds, "ShoppingBasketDs");

        }
        catch (Exception ex)
        {
            throw ex;

        }   
    }

        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 string CatchImages(string target, byte[][] buffer, string[] ext, bool retFileNames)
    {
        string msg = string.Empty;

        try
        {
            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);

            string[] fileName = { };

            dt = ds.Tables[tbl];

            foreach (string e in ext)
            {
                while (true)
                {
                    string name = NameGen();
                    bool found = false;

                    foreach (string f in fileName)
                    {
                        if (f == name)
                        {
                            found = true;
                            break;
                        }
                    }

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

                            if (dr["id"].ToString() == name)
                            {
                                found = true;
                                break;
                            }
                        }
                    }

                    if (!found)
                    {
                        int len = fileName.Length;
                        Array.Resize(ref fileName, len + 1);
                        fileName[len] = name;
                        break;
                    }
                }
            }

            for (int i = 0; i < ext.Length; i++)
            {
                dr = dt.NewRow();

                dr["id"] = fileName[i];
                dr["ext"] = ext[i].ToLower().Trim();
                dr["data"] = Convert.ToBase64String(buffer[i]);
                dr["location"] = target.Trim();

                dt.Rows.Add(dr);

                oda.InsertCommand = ocb.GetInsertCommand();

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

            if (msg == "Created" && retFileNames)
            {
                Array.Resize(ref pgImages, 0);

                pgImages = fileName;

                //msg = "Created";
//.........这里部分代码省略.........

    public string ContactListCatchChanges(string tbl, DataTable dtList, string legal)
    {
        string msg = string.Empty;

        if (tLegal == legal.Trim() && IsDaysLeft())
        {
            try
            {
                string sqlStr = "SELECT * FROM " + tbl;

                if (!CleanTable(tbl))
                {
                    return "Can't Clean Table";
                }

                DataRow drList;

                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;

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

                if (dtList.Rows.Count > 0)
                {
                    for (int i = 0; i < dtList.Rows.Count; i++)
                    {
                        dr = dt.NewRow();
                        drList = dtList.Rows[i];

                        dr["mailbox"] = EncDec.Encrypt(drList[0].ToString().Trim(), Base.hashKey);
                        dr["rname"] = EncDec.Encrypt(drList[1].ToString().Trim(), Base.hashKey);

                        dt.Rows.Add(dr);

                        oda.InsertCommand = ocb.GetInsertCommand();

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

                cnn.Close();

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

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

                tbl = null;
                sqlStr = null;
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
            finally
            {
            }
        }
        else
            msg = "illegal";

        return msg;
    }

        /// <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();
            }
        }

        //public bool WriteLine(IEnumerable<object> values)
        //{
        //    return WriteLine((IEnumerable<string>)values);
        //}
        /// <summary>
        /// Make sure you have a value for every column!
        /// This is really intentionally not thread safe, do not share this class across threads.
        /// </summary>
        public bool WriteLine(IEnumerable<string> values)
        {
            if (!IsOpen())
                return false;

            if (_insertCmd == null)
            {
                DbCommand cmd = _conn.CreateCommand();
                cmd.CommandText = "SELECT * FROM " + _tablename;

                DbDataAdapter dda = new System.Data.OleDb.OleDbDataAdapter((OleDbCommand)cmd);
                System.Data.OleDb.OleDbCommandBuilder odcb = new System.Data.OleDb.OleDbCommandBuilder((OleDbDataAdapter)dda);

                _insertCmd = odcb.GetInsertCommand();
            }

            //double temp = Double.NaN;

            int i = 0;
            foreach (string val in values)
            {
                //if (Double.TryParse(val, out temp))
                //{
                //    //try to parse out integers, float vars (lat/lon), ids, anything numeric
                //    _insertCmd.Parameters[i].Value = temp;
                //}
                //else if (_insertCmd.Parameters[i].SourceColumn.ToLower().EndsWith("id"))
                //{
                //    //if the value is empty, and it HAS to be a number...
                //    _insertCmd.Parameters[i].Value = 0;
                //}
                //else
                //{
                    _insertCmd.Parameters[i].Value = val;
                //}

                i++;
            }

            int rows = _insertCmd.ExecuteNonQuery();
            return (rows == 1);
        }

        public override bool InsertBulkRows(string pSelectSQL, System.Data.Common.DbDataReader pDataReader,  SetGadgetStatusHandler pStatusDelegate = null, CheckForCancellationHandler pCancellationDelegate = null)
        {
            bool result = false;

            System.Data.OleDb.OleDbConnection ConnOle = null;
            System.Data.OleDb.OleDbDataAdapter AdapterOle = null;
            System.Data.OleDb.OleDbCommandBuilder builderOLE = null;
            System.Data.Common.DbCommand cmdOle = null;

            DataSet dataSet = new DataSet();
            DataTable Temp = new DataTable();

            try
            {
                StringBuilder InsertSQL;
                StringBuilder ValueSQL;

                ConnOle = new System.Data.OleDb.OleDbConnection(ConnectionString.Replace(";IMEX=1", ""));
                AdapterOle = new System.Data.OleDb.OleDbDataAdapter(pSelectSQL, ConnOle);
                AdapterOle.FillSchema(dataSet, SchemaType.Source);
                AdapterOle.Fill(Temp);
                builderOLE = new System.Data.OleDb.OleDbCommandBuilder();
                builderOLE.DataAdapter = AdapterOle;

                ConnOle.Open();
                cmdOle = ConnOle.CreateCommand();

                cmdOle.CommandTimeout = 1500;

                int rowCount = 0;
                int skippedRows = 0;
                int totalRows = 0;
                int truncatedCellCount = 0;
                bool numericFieldOverflow = false;

                while (pDataReader.Read())
                {
                    cmdOle = builderOLE.GetInsertCommand();
                    InsertSQL = new StringBuilder();
                    ValueSQL = new StringBuilder();

                    InsertSQL.Append("Insert Into ");
                    InsertSQL.Append(pSelectSQL.Replace("Select * From ", ""));
                    InsertSQL.Append(" (");
                    ValueSQL.Append(" values (");
                    int CheckLength = 0;
                    List<OleDbParameter> ParameterList = new List<OleDbParameter>();
                    foreach (System.Data.OleDb.OleDbParameter param in cmdOle.Parameters)
                    {
                        string FieldName = param.SourceColumn;

                        InsertSQL.Append("[");
                        InsertSQL.Append(FieldName);
                        InsertSQL.Append("],");

                        ValueSQL.Append(param.ParameterName);
                        ValueSQL.Append(",");

                        try
                        {
                            param.Value = pDataReader[FieldName];
                        }
                        catch (Exception ex)
                        {
                            param.Value = DBNull.Value;
                        }
                        ParameterList.Add(param);

                    }
                    InsertSQL.Length = InsertSQL.Length - 1;
                    ValueSQL.Length = ValueSQL.Length - 1;
                    InsertSQL.Append(")");
                    ValueSQL.Append(")");
                    InsertSQL.Append(ValueSQL);

                    cmdOle = null;
                    cmdOle = ConnOle.CreateCommand();
                    cmdOle.CommandText = InsertSQL.ToString();

                    foreach (OleDbParameter param in ParameterList)
                    {
                        DbParameter p2 = cmdOle.CreateParameter();
                        p2.DbType = param.DbType;
                        try
                        {
                            p2.Value = pDataReader[param.SourceColumn];
                            CheckLength = p2.Value.ToString().Length;
                            if (CheckLength > 255)
                            {
                                p2.Value = p2.Value.ToString().Substring(0, 255);
                                truncatedCellCount++;
                            }
                        }
                        catch (Exception ex)
                        {
                            p2.Value = DBNull.Value;
                        }
                        p2.ParameterName = param.ParameterName;

                        cmdOle.Parameters.Add(p2);
//.........这里部分代码省略.........

    public string NodesAdd(string node, string parent, string fullPath, int zIndex, string tbl, string legal)
    {
        string msg = string.Empty;

        if (tLegal == legal.Trim() && isDaysLeft)
        {
            node = node.Trim();
            parent = parent.Trim();
            fullPath = fullPath.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();

                OleDbCommand cmd = new OleDbCommand(sqlStr, cnn);
                OleDbDataReader drr = cmd.ExecuteReader();

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

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

                bool found = false;

                while (drr.Read())
                {
                    if (drr["fullpath"].ToString().Trim() == fullPath)
                    {
                        found = true;
                        break;
                    }
                }

                if (!found)
                {
                    dt = ds.Tables[tbl];
                    dr = dt.NewRow();

                    dr["pg"] = node.Trim();
                    dr["parent"] = parent.Trim();
                    dr["fullpath"] = fullPath.Trim();
                    dr["zindex"] = zIndex;
                    dr["body"] = EncDec.Encrypt(" ", Base.hashKey);
                    if (parent != "root")
                        dr["viewcount"] = 0;
                    else
                        dr["viewcount"] = -1;

                    dt.Rows.Add(dr);

                    oda.InsertCommand = ocb.GetInsertCommand();

                    if (oda.Update(ds, tbl) == 1)
                    {
                        ds.AcceptChanges();
                        msg = "Added";
                    }
                    else
                    {
                        ds.RejectChanges();
                        msg = "Rejected";
                    }
                }
                else
                    msg = "Already Exist";

                cnn.Close();
                drr.Close();

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

                ds = null;
                ocb = null;
                oda = null;
                dr = null;
                dt = null;
                cmd = null;
                drr = null;
                cnn = null;
            }
            catch (Exception ex)
            {
                msg = ex.Message;
            }
//.........这里部分代码省略.........

        private void subExportToExcel(string sheetToCreate, List<DataRow> selectedRows, DataTable origDataTable, string tableName)
        {
            char Space = ' ';
            string dest = sheetToCreate;
            int i = 0;
            while (File.Exists(dest))
            {
                dest = Path.GetDirectoryName(sheetToCreate) + "\\" + Path.GetFileName(sheetToCreate) + i + Path.GetExtension(sheetToCreate);
                i += 1;
            }
            sheetToCreate = dest;
            if (tableName == null) tableName = string.Empty;
            tableName = tableName.Trim().Replace(Space, '_');
            if (tableName.Length == 0) tableName = origDataTable.TableName.Replace(Space, '_');
            if (tableName.Length == 0) tableName = "NoTableName";
            if (tableName.Length > 30) tableName = tableName.Substring(0, 30);
            //Excel names are less than 31 chars
            string queryCreateExcelTable = "CREATE TABLE [" + tableName + "] (";
            Dictionary<string, string> colNames = new Dictionary<string, string>();
            foreach (DataColumn dc in origDataTable.Columns)
            {
                //Cause the query to name each of the columns to be created.
                string modifiedcolName = dc.ColumnName.Replace(Space, '_').Replace('.', '#');
                string origColName = dc.ColumnName;
                colNames.Add(modifiedcolName, origColName);
                queryCreateExcelTable += "[" + modifiedcolName + "]" + " text,";
            }
            queryCreateExcelTable = queryCreateExcelTable.TrimEnd(new char[] { Convert.ToChar(",") }) + ")";
            //adds the closing parentheses to the query string
            if (selectedRows.Count > 65000 && sheetToCreate.ToLower().EndsWith(".xls"))
            {
                //use Excel 2007 for large sheets.
                sheetToCreate = sheetToCreate.ToLower().Replace(".xls", string.Empty) + ".xlsx";
            }
            string ext = System.IO.Path.GetExtension(sheetToCreate).ToLower();
            string strConn = defineExcelConnection(ext, sheetToCreate);
            if (strConn.Length <= 0)
                return;

            System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(strConn);
            System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(queryCreateExcelTable, cn);
            cn.Open();
            cmd.ExecuteNonQuery();
            System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + tableName + "]", cn);
            System.Data.OleDb.OleDbCommandBuilder cb = new System.Data.OleDb.OleDbCommandBuilder(da);
            //creates the INSERT INTO command
            cb.QuotePrefix = "[";
            cb.QuoteSuffix = "]";
            cmd = cb.GetInsertCommand();
            //gets a hold of the INSERT INTO command.
            foreach (DataRow row in selectedRows)
            {
                foreach (System.Data.OleDb.OleDbParameter param in cmd.Parameters)
                    param.Value = row[colNames[param.SourceColumn]];
                cmd.ExecuteNonQuery(); //INSERT INTO command.
            }
            cn.Close();
            cn.Dispose();
            da.Dispose();
            GC.Collect();
            GC.WaitForPendingFinalizers();
        }

        /// <summary>
        /// 插入整个DataTable
        /// </summary>
        /// <param name="table">要插入的DataTable</param>
        /// <param name="tableName">插入的表名</param>
        /// <returns></returns>
        public int InsertDataTable(DataTable table, string tableName)
        {
            try
            {
                Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "select * from " + tableName;

                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                OleDbCommandBuilder cmdBuilder = new OleDbCommandBuilder(da);
                da.InsertCommand = cmdBuilder.GetInsertCommand();

                int val = da.Update(table);
                da.Dispose();
                return val;
            }
            catch (OleDbException e)
            {
                this.Close();
                throw e;
            }
        }