C# OleDb.OleDbCommandBuilder类代码示例

        private void updated_dataview()
        {
            OleDbConnection connection = new OleDbConnection();
            connection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + reference.Getdb();
            OleDbDataAdapter dataAdapter;
            try
            {
                dataAdapter = new OleDbDataAdapter("select * from printer", connection);

                OleDbCommandBuilder command = new OleDbCommandBuilder(dataAdapter);

                // Populate a new data table and bind it to the BindingSource.
                printers = new DataTable();
                printers.Locale = System.Globalization.CultureInfo.InvariantCulture;
                dataAdapter.Fill(printers);

                printers_listbox.DataSource = printers;
                printers_listbox.DisplayMember = "location";
                currentchart = printers.Rows[0].ItemArray.GetValue(0).ToString();
            }
            catch (Exception e3)
            {
                MessageBox.Show(e3.Message);
            }
            finally
            {
                connection.Close();
            }
        }

        public void searcher(string field, string column)
        {
            try
            {

                connectionString = ConfigurationManager.AppSettings["DBConnectionString"] + frmHomeScreen.mUserFile;
                string query = "select ISBN, Title, Author, Publisher, Binding, pub_date as Publication_Date, Pages, Available from book where " + column + " like '%" + escapeHandling(field) + "%' order by Author, Title;";

                OleDbDataAdapter da = new OleDbDataAdapter(query, connectionString);
                OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
                DataTable dt = new DataTable();

                da.Fill(dt);

                BindingSource bs = new BindingSource();
                bs.DataSource = dt;

                dgvBook.DataSource = bs;

                da.Update(dt);

                databaseHandler d = new databaseHandler();
                d.closeDatabaseConnection();
            }
            catch (Exception ee)
            {
                MessageBox.Show(ee.Message);
            }

            dgvBook.ClearSelection();
        }

        private void frmWhatMedia_Load(object sender, EventArgs e)
        {
            try
            {
                string connectionString = ConfigurationManager.AppSettings["DBConnectionString"] + frmHomeScreen.mUserFile;
                string query = "select Title, isbn as Checkout_Code from (select title, isbn from book union select title, upc from movie union select title,upc from game union select album,upc from cd) order by title;";

                OleDbDataAdapter da = new OleDbDataAdapter(query, connectionString);
                OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
                DataTable dt = new DataTable();

                da.Fill(dt);

                BindingSource bs = new BindingSource();
                bs.DataSource = dt;

                dgvMedia.DataSource = bs;

                da.Update(dt);

                databaseHandler d = new databaseHandler();
                d.closeDatabaseConnection();

                DataGridViewColumn c = dgvMedia.Columns[0];
                c.Width = 155;
                dgvMedia.ClearSelection();
            }
            catch (Exception ee)
            {
                MessageBox.Show(ee.Message);
            }
        }

        private void ReserveMenu_Load(object sender, EventArgs e)
        {
            AirAsianDataBase = new OleDbConnection(connectionString);

            AirAsianDataBase.Open();

            string strSelect = "Select * from AirCraft";

            TripGridView.DataSource = null;
            TripGridView.Rows.Clear();
            TripGridView.Refresh();

            OleDbDataAdapter da = new OleDbDataAdapter( strSelect, AirAsianDataBase);
            OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(da);
            DataTable dataTable = new DataTable();
            da.Fill(dataTable);

            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                TripGridView.Rows.Add(
                    dataTable.Rows[i][0],
                    dataTable.Rows[i][1],
                    dataTable.Rows[i][2],
                    dataTable.Rows[i][3],
                    dataTable.Rows[i][4],
                    dataTable.Rows[i][5]);
            }

            AirAsianDataBase.Close();
        }

        public override DataTable GetRecords(string strWhere, string strGroupBy, string strOrderBy)
        {
            if (m_pOleDbConnection != null)
            {
                try
                {
                    m_nCurrentRowIndex = 0;

                    string commandText = "Select * from "
                        + "(Select LineNodeID as LineID,X1 as PX1,Y1 as PY1,X2 as PX2,Y2 as PY2,-1 from LineNodeEx Where EntityID=-1 "
                        + "union Select LineNodeID as LineID,X2 as PX1,Y2 as PY1,X1 as PX2,Y1 as PY2,1 from LineNodeEx Where EntityID=-1) "
                        + "Order By PX1,PY1,PX2,PY2,LineID";

                    m_pOleDbDataAdapter = new OleDbDataAdapter(commandText, m_pOleDbConnection);

                    OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(m_pOleDbDataAdapter);

                    return GetNextRecords();
                }
                catch (Exception ex)
                {
                    Logger.WriteErrorLog(ex);
                }
            }
            return null;
        }

        private void subGetData(string _strSELECT)
        {
            try
            {
                // Specify a connection string. Replace the given value with a
                // valid connection string for a Northwind SQL Server sample
                // database accessible to your system.
                String strConn = clsAppSettings.GetAppSettings().strCTConn;

                // Create a new data adapter based on the specified query.
                daMORNCOAAlerts = new OleDbDataAdapter(_strSELECT, strConn);

                // Create a command builder to generate SQL update, insert, and
                // delete commands based on _strSELECT. These are used to
                // update the database.
                OleDbCommandBuilder cmdMORNCOAAlerts = new OleDbCommandBuilder(daMORNCOAAlerts);

                // Populate a new data table and bind it to the BindingSource.
                DataTable dtMORNCOAAlerts = new DataTable();
                dtMORNCOAAlerts.Locale = System.Globalization.CultureInfo.InvariantCulture;
                daMORNCOAAlerts.Fill(dtMORNCOAAlerts);
                srcMORNCOAAlerts.DataSource = dtMORNCOAAlerts;
            }
            catch (OleDbException)
            {
                MessageBox.Show("To run this example, replace the value of the " +
                    "strConn variable with a connection string that is " +
                    "valid for your system.");
            }
        }

        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");
        }

     private void FrmConfigDatabase_Load(object sender, EventArgs e)
     {
         if (m_cnADONetConnection.State == ConnectionState.Open)
         {
             m_cnADONetConnection.Close();
         }
         m_cnADONetConnection.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.Windows.Forms.Application.StartupPath + @"\ConfigDatabase.mdb";
         
         m_cnADONetConnection.Open();
         m_daDataAdapter = new OleDbDataAdapter("Select * From DatabaseInfo", m_cnADONetConnection);
         OleDbCommandBuilder m_cbCommandBuilder = new OleDbCommandBuilder(m_daDataAdapter);
         m_daDataAdapter.Fill(m_dtDict);
         if(m_dtDict.Rows.Count != 0)
         {
             listBox1.Items.Add("Tên Server : " + m_dtDict.Rows[0]["ServerName"]);
             listBox1.Items.Add("Tên Instance : " + m_dtDict.Rows[0]["InstanceName"]);
             listBox1.Items.Add("Tên Database : " + m_dtDict.Rows[0]["DatabaseName"]);
             if (m_dtDict.Rows[0]["Mode"].ToString() == "AUT")
             {
                 listBox1.Items.Add("Window Authentication");
             }
             else
             {
                 listBox1.Items.Add("UserName : " + m_dtDict.Rows[0]["UserID"]);
                 userId = m_dtDict.Rows[0]["UserID"].ToString();
                 pass = m_dtDict.Rows[0]["Password"].ToString();
             }
             mode = m_dtDict.Rows[0]["Mode"].ToString();
             serverName = m_dtDict.Rows[0]["ServerName"].ToString();
             instanceName = m_dtDict.Rows[0]["InstanceName"].ToString();
             databaseName = m_dtDict.Rows[0]["DatabaseName"].ToString();
             isConfiged = true;
         }
 
     }

        private void button2_Click(object sender, EventArgs e)
        {
            String query = "select * from Admin ;";
            DatabaseManager dbManager = DatabaseManager.dataBaseManager();
            dbManager.BookConn = new OleDbConnection(dbManager.getConnParam());
            OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, dbManager.BookConn);
            OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);
            DataTable dataTable = new DataTable();
            DataSet ds = new DataSet();
            dAdapter.Fill(dataTable);

            DataRow[] dra = dataTable.Select("");
            foreach (DataRow dr in dra)
            {
                if (textBox1.Text.Equals(dr["Sifre"] + ""))
                {
                    AdminEkrani f22 = new AdminEkrani();
                    this.Visible = false;
                    f22.Show();
                }
                else
                {
                    MessageBox.Show("ADMIN SIFRESI HATALI");
                }
            }
        }

        static void Main(string[] args)
        {
            //dateTime.DataSet1TableAdapters.table1TableAdapter table = new dateTime.DataSet1TableAdapters.table1TableAdapter();
            //table.Insert("arif", "khan", "hasan", "1988-01-10", "100.00");
            //Console.ReadLine();

            OleDbConnection con = new OleDbConnection(@"Provider=VFPOLEDB.1;Data Source=D:\Documents and Settings\axkhan2\Desktop\table\table1.dbf");
            con.Open();
            OleDbDataAdapter adapt = new OleDbDataAdapter(@"select * from 'D:\Documents and Settings\axkhan2\Desktop\table\table1.dbf'", con);
            OleDbCommandBuilder build = new OleDbCommandBuilder(adapt);
            DataSet set = new DataSet();
            adapt.Fill(set);
            DataTable table = set.Tables[0];
            DataRow row = table.NewRow();
            row["firstname"] = "arifkhan";
            row["lastname"] = "khan";
            row["middlename"] = "mname";
            row["dob"] = DateTime.Now;
            row["amount"] = 100;
            table.Rows.Add(row);
           
            //adapt.Update(table);
            //adapt.UpdateCommand =  build.GetUpdateCommand();
            adapt.Update(table);
            //cmd.ExecuteNonQuery();
        }

        /// <summary>Delete the specified Cause from the specified Issue.</summary>
        /// <param name="pIssueID">ID of the Issue the cause applies to.</param>
        /// <param name="pCauseID">ID of the Cause to delete.</param>
        /// <returns>ID of the modified/created cause row.</returns>
        public void Delete(int pIssueID, int pCauseID)
        {
            try
            {
                using(OleDbDataAdapter oDA		= new OleDbDataAdapter(String.Format("Select * from Causes where issue_id = {0} and cause_id = {1} order by cause_seq", pIssueID, pCauseID), this.DataObject.ConnectionString))
                using(OleDbCommandBuilder oCB = new OleDbCommandBuilder(oDA))
                using(OleDbConnection oConn		= new OleDbConnection(this.DataObject.ConnectionString))
                {
                    // Get the list of previously saved Causes to the current Issue.
                    oConn.Open();
                    DataSet oDS = new DataSet("Causes");
                    oDA.Fill(oDS);

                    // If we found any...And we're updating an existing Cause...
                    if(oDS.Tables[0].Rows.Count > 0)
                    {
                        DataRow oRow = oDS.Tables[0].Rows[0];
                        oRow.Delete();
                        oDA.Update(oDS);
                    }
                }
            }
            catch(Exception e)
            {
                throw new Exception("Unable to save new Issue Cause!\nError: " + e.Message);
            }
        }

        // ExStart:FillDataSet
        private void DataBindingFeatures_Load(object sender, EventArgs e)
        {
            // The path to the documents directory.
            string dataDir = Utils.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

            // Creating Select query to fetch data from database
            string query = "SELECT * FROM Products ORDER BY ProductID";

            // Creating connection string to connect with database
            string conStr = @"Provider=microsoft.jet.oledb.4.0;Data Source=" + dataDir + "dbDatabase.mdb";

            // Creating OleDbDataAdapter object that will be responsible to open/close connections with database, fetch data and fill DataSet with data
            adapter = new OleDbDataAdapter(query, conStr);

            // Setting MissingSchemaAction to AddWithKey for getting necesssary primary key information of the tables
            adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

            /*
             * Creating OleDbCommandBuilder object to create insert/delete SQL commmands
             * automatically that are used by OleDbDatAdapter object for updating
             * changes to the database
            */
            cb = new OleDbCommandBuilder(adapter);

            // Creating DataSet object
            ds = new DataSet();

            // Filling DataSet with data fetched by OleDbDataAdapter object
            adapter.Fill(ds, "Products");
        }

        public void fillgv(string query)
        {
            OleDbConnection connection = new OleDbConnection();
            connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\sciencedb.accdb";
            OleDbCommand oledbRunCommand = new OleDbCommand();
            oledbRunCommand.CommandText = query;
            oledbRunCommand.CommandType = CommandType.Text;
            oledbRunCommand.Connection = connection;

            //create an OleDbDataAdapter to execute the query
            OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, connection);

            //create a command builder
            OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(dAdapter);

            //create a DataTable to hold the query results

            //fill the DataTable
            dAdapter.Fill(dTable);

            //the DataGridView
            //DataGridView dgView = new DataGridView();
            //BindingSource to sync DataTable and DataGridView
            BindingSource bSource = new BindingSource();

            //set the BindingSource DataSource
            bSource.DataSource = dTable;

            //set the DataGridView DataSource
            locationTableDataGridView.DataSource = bSource;
            connection.Close();
        }

        private void btnSales_Click(object sender, EventArgs e)
        {
            try
            {
                loginConn.Open();

                string strSelect = "SELECT * FROM TopSales";
                dataGridView1.DataSource = null;
                dataGridView1.Rows.Clear();
                dataGridView1.Refresh();

                OleDbDataAdapter da = new OleDbDataAdapter(strSelect, loginConn);
                OleDbCommandBuilder cBuilder = new OleDbCommandBuilder(da);
                DataTable dataTable = new DataTable();
                da.Fill(dataTable);

                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    dataGridView1.Rows.Add(dataTable.Rows[i][0], dataTable.Rows[i][1]);
                    dataGridView1.Sort(dataGridView1.Columns["TotalTransaction"], ListSortDirection.Descending);
                }

                loginConn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                loginConn.Close();
            }
        }

        private void getData(string sqlCommand)
        {
            try
            {
                users = new DataSet();
                dataAdapter = new OleDbDataAdapter(sqlCommand, DbConn.connString);
                dataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

                OleDbCommandBuilder commandBuilder = new OleDbCommandBuilder(dataAdapter);
                commandBuilder.QuotePrefix = "[";
                commandBuilder.QuoteSuffix = "]";

                dataAdapter.Fill(users,"users");

                dataGridView.DataSource = users.Tables["users"];
                dataGridView.AutoGenerateColumns = true;
            //                dataGridView.Columns[0].ReadOnly = true;

            }
            catch (Exception e)
            {

                MessageBox.Show(e.Message);
            }
        }