C# OleDbCommand.ExecuteNonQuery方法代码示例

        public static int SaveRecord(string sql)
        {
            const int rv = 0;
            try
            {
                string connectionString = ConfigurationManager.ConnectionStrings["LA3Access"].ConnectionString;
                using (var conn = new OleDbConnection(connectionString))
                {
                    conn.Open();
                    var cmGetID = new OleDbCommand("SELECT @@IDENTITY", conn);
                    var comm = new OleDbCommand(sql, conn) { CommandType = CommandType.Text };
                    comm.ExecuteNonQuery();

                    var ds = new DataSet();
                    var adapt = new OleDbDataAdapter(cmGetID);
                    adapt.Fill(ds);
                    adapt.Dispose();
                    cmGetID.Dispose();

                    return int.Parse(ds.Tables[0].Rows[0][0].ToString());

                }
            }
            catch (Exception)
            {
            }
            return rv;
        }

        public static void updatePosition(string swimlane, List<string> swimlaneBacklog, List<string> swimlaneBacklogPos)
        {
            OleDbConnection conn = new OleDbConnection();
            OleDbCommand myCommand = new OleDbCommand();
            conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|LanbanDatabase.mdb;";
            conn.Open();
            myCommand.Connection = conn;
            int statusID = 1;
            if (swimlane == "5")
                statusID = 3;
            if (swimlane == "4")
                statusID = 2;
            for (int i = 0; i < swimlaneBacklog.Count; i++)
            {

                if (swimlane == "5")
                {
                    myCommand.CommandText = "UPDATE Backlogs SET BacklogPosition = " + swimlaneBacklogPos[i]
                                        + ", swimlaneID = " + swimlane + ", BacklogStatusID = " + statusID + ", BacklogCompletedDate = '" + DateTime.Today
                                        + "' WHERE backlogID = " + swimlaneBacklog[i];
                    myCommand.ExecuteNonQuery();
                    myCommand.CommandText = "UPDATE Tasks SET TaskStatusID = " + statusID + ", TaskCompletedDate = '" + DateTime.Today
                                        + "' WHERE backlogID = " + swimlaneBacklog[i] + " AND TaskStatusID <> 3";
                    myCommand.ExecuteNonQuery();
                }
                else
                {
                    myCommand.CommandText = "UPDATE Backlogs SET BacklogPosition = " + swimlaneBacklogPos[i]
                                        + ", swimlaneID = " + swimlane + ", BacklogStatusID = " + statusID + ", BacklogCompletedDate = null"
                                        + " WHERE backlogID = " + swimlaneBacklog[i];
                    myCommand.ExecuteNonQuery();
                }
            }
            conn.Close();
        }

        private void ENT_Click()
        {
            //项目名称修改
            if (TbFileName.Text == "")
            {
                MessageBox.Show("文件项目名不能为空！", "提示");
            }
            else
            {
                //更新数据库
                //Window_Project11.CurrentItemName;
                OleDbConnection conn = new OleDbConnection(odbcConnStr);
                string sql = "select * from ItemInfor where ItemName='" + TbFileName.Text.Trim() + "'";
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                conn.Open();
                if (cmd.ExecuteNonQuery() > 0)
                {
                    MessageBox.Show("该项目已经存在！", "提示");
                }
                else
                {
                    // sql = "update tbCourseInfo set filedCourseName='" + tbCourseName.Text.Trim() + "' where filedCourseID=" + courseID;
                    sql = "update ItemInfor set ItemName='" + TbFileName.Text.Trim() + "' where ItemID=" + Window_Project11.CurrentItemID;
                    MessageBox.Show(sql);
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("项目修改成功！", "提示");
                }
            }

        }

 protected void BtnAdd_Click(object sender, EventArgs e)
 {
     OleDbConnection Con = ET_Lib.E_trainingConnection;
     string ApoloReason = "NULL";
     if (TxtReason.Text.Trim().Length != 0)
     {
         ApoloReason = TxtReason.Text;
     }
     OleDbCommand Cmd = new OleDbCommand("", Con);
     try
     {
         Con.Open();
         Cmd.CommandText = "UPDATE DowraApology SET ApologyDate = #" + DateTime.Today.ToShortDateString().ToString() + "#, ApologyReson = '" + ApoloReason + "' WHERE (DowraApology.TRshehId = " + TRshehId(ET_Lib.Empid, Request.QueryString["Courseid"].ToString()) + ")";
         if (Cmd.ExecuteNonQuery() == 0)
         {
             Cmd.CommandText = "INSERT INTO DowraApology (TRshehId, ApologyDate, ApologyReson) VALUES (" + TRshehId(ET_Lib.Empid, Request.QueryString["Courseid"].ToString()) + ", #" + DateTime.Today.ToShortDateString().ToString() + "#, '" + ApoloReason + "')";
             Cmd.ExecuteNonQuery();
         }
         Response.Redirect("~/TrainerCourses.aspx");
     }
     catch (Exception ex)
     {
         LblState.Text = ex.Message;
         LblState.Visible = true;
     }
     Con.Close();
 }

 protected void BtnSaveComment_Click(object sender, EventArgs e)
 {
     Button Btn = (Button)sender;
     DataListItem DLI = (DataListItem)Btn.Parent;
     DataList DL = (DataList)DLI.Parent;
     DataTable EvalTextItem = (DataTable)ViewState["EvalTextItem"];
     string EvalItemTextId = string.Empty;
     EvalItemTextId = EvalTextItem.Rows[DLI.ItemIndex]["EvalItemTextId"].ToString();
     TextBox txt = (TextBox)DLI.FindControl("TxtComment");
     Label Lbl = (Label)DLI.FindControl("LblNotify");
     Lbl.Text = string.Empty;
     int Idx = (int)DLI.ItemIndex;
     OleDbConnection Con = ET_Lib.E_trainingConnection;
     OleDbCommand Cmd = new OleDbCommand("UPDATE DowraEvalioutionTrainerText SET dateEval = #" + DateTime.Today.ToShortDateString() + "#, EvalText = '" + txt.Text + "' WHERE (DowraEvalioutionTrainerText.TRshehId = " + GetTRshehId() + ") AND (DowraEvalioutionTrainerText.EvalItemTextId = " + EvalItemTextId + ")", Con);
     try
     {
         Con.Open();
         if (Cmd.ExecuteNonQuery() == 0)
         {
             Cmd.CommandText = "INSERT INTO DowraEvalioutionTrainerText (TRshehId, EvalItemTextId, dateEval, EvalText) VALUES (" + GetTRshehId() + ", " + EvalItemTextId + ", #" + DateTime.Today.ToShortDateString() + "#, '" + txt.Text + "')";
             Cmd.ExecuteNonQuery();
         }
         Lbl.Text = "تم الاضـــــــافه";
         Lbl.ForeColor = System.Drawing.Color.Green;
     }
     catch (Exception ex)
     {
         Lbl.Text = ex.Message;
         Lbl.ForeColor = System.Drawing.Color.Red;
     }
     Con.Close();
 }

    protected void BtnSaveResearch_Click(object sender, EventArgs e)
    {
        if (TxtResearchSubject.Text == string.Empty)
        {
            LblResearchError.Text = "من فضلك املاء البيانات";
            LblResearchError.Visible = true;
            return;
        }
        try
        {
            string search_code = string.Empty;
            OleDbConnection Con = MySchool.LibraryConnection;
            OleDbCommand CMD = new OleDbCommand("Select TOP 1 MAX(search_code) From search1", Con);
            OleDbDataReader DR;
            string SavingFileName = string.Empty;

            if (FileUploadResearch.HasFile)
            {
                SavingFileName = DateTime.Today.Year.ToString() + DateTime.Today.Month.ToString() + DateTime.Today.Day.ToString() + DateTime.Today.Second.ToString() + DateTime.Today.Second.ToString() + DateTime.Today.Second.ToString() + FileUploadResearch.FileName.ToString();
                FileUploadResearch.SaveAs(HttpContext.Current.Server.MapPath("ResearchsFiles\\") + SavingFileName);
                SavingFileName = "~/ResearchsFiles/" + SavingFileName;
            }
            Con.Open();
            DR = CMD.ExecuteReader();
            if (DR.Read())
            {
                search_code = DR.GetValue(0).ToString();
                DR.Close();
                search_code = (Convert.ToInt16(search_code) + 1).ToString();
                CMD.CommandText = "Insert Into search (search_code,SPOS,asase_code,[date],subject,aim_search,Show,RelatedFile) VALUES (" + search_code + "," + SPOS + "," + asase_code + ",#" + TxtResearchDate.Text + "#,'" + TxtResearchSubject.Text + "','" + TxtAim.Text + "',0,'" + SavingFileName + "')";
                CMD.ExecuteNonQuery();
                LblResearchError.Text = "تم اضافة البحث";
                LblResearchError.ForeColor = System.Drawing.Color.Green;
                LblResearchError.Visible = true;
            }
            else
            {
                search_code = "0";
                CMD.CommandText = "Insert Into search (search_code,SPOS,asase_code,[date],subject,aim_search,Show,RelatedFile) VALUES (1," + SPOS + "," + asase_code + ",#" + TxtResearchDate.Text + "#,'" + TxtResearchSubject.Text + "','" + TxtAim.Text + "',0,'" + SavingFileName + "')";
                CMD.ExecuteNonQuery();
                LblResearchError.Text = "تم اضافة البحث";
                LblResearchError.ForeColor = System.Drawing.Color.Green;
                LblResearchError.Visible = true;
            }
            CMD.CommandText = "Insert Into search1 (search_code, code_name) VALUES (" + search_code + ", " + StuID + ")";
            CMD.ExecuteNonQuery();
            Con.Close();
        }
        catch (Exception ex)
        {
            LblResearchError.Text = ex.Message.ToString();
            LblResearchError.ForeColor = System.Drawing.Color.Red;
            LblResearchError.Visible = true;
        }
    }

        private void addbtn_Click(object sender, EventArgs e)
        {
            if (nameTxt.Text.Length < 1 || lastNameTxt.Text.Length < 1 || addressTxt.Text.Length < 1 || phoneTxt.Text.Length < 1)
            {
                MessageBox.Show("All fields must be entered!", "Invalid Values", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            if (birthdayPicker.Value.AddYears(18) > DateTime.Today)
            {
                MessageBox.Show("Your employee must be atleast 18 years old!", "Invalid Birthday", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            Connection connection = new Connection();

            connection.openConnection();

            OleDbCommand employeeCommand = new OleDbCommand();
            employeeCommand.Connection = connection.connection;

            string name = nameTxt.Text;
            string lastName = lastNameTxt.Text;
            string address = addressTxt.Text;
            string phone = phoneTxt.Text;
            string birthday = birthdayPicker.Value.ToShortDateString();
            string employment = DateTime.Now.ToString();

            if (edit)
            {
                employeeCommand.CommandText = "UPDATE Employees SET First_Name = '" + name + "', Last_Name = '" + lastName + "', Address = '" + address + "', Phone = '" + phone + "', Birthday = #" + birthday + "# WHERE ID = " + employee.id;
                employeeCommand.ExecuteNonQuery();

                employee.name = name;
                employee.lastName = lastName;
                employee.address = address;
                employee.phone = phone;
                employee.birthday = birthdayPicker.Value.Date;
            }
            else
            {
                employeeCommand.CommandText = "INSERT INTO Employees (First_Name,Last_Name,Address,Phone,Birthday,Employmend,Fired) VALUES ('" + name + "', '" + lastName + "', '" + address + "', '" + phone + "', #" + birthday + "#, #" + employment + "#, false)";
                employeeCommand.ExecuteNonQuery();

                employeeCommand.CommandText = "Select @@Identity";
                string idEmployee = employeeCommand.ExecuteScalar().ToString();

                employee = new CEmployee(idEmployee, name, lastName, address, phone, birthdayPicker.Value.Date, DateTime.Now, false);
            }

            connection.closeConnection();

            DialogResult = System.Windows.Forms.DialogResult.OK;
            Close();
        }

        private void Guncelle()
        {
            using (OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=hesap.mdb"))
            {
                conn.Open();
                OleDbCommand komut = new OleDbCommand();
                komut.Connection = conn;
                komut.CommandText = "Select * from hesap"; // sorgu / komut cumlemi yazıyorum.
                komut.ExecuteNonQuery(); // insert , updateiçin gerekli satir sayisi donduruyoruz.
                OleDbDataReader dr = komut.ExecuteReader(); // datareader olusturup komut sorgulayıp veritabaninda okuma işlemini tanıtıyoruz
                while (dr.Read()) // datareader ile okuyoruz.
                {
                    string hadi = dr["hadi"].ToString();
                    string kadi = dr["kadi"].ToString();
                    string q;
                    using (WebClient asd = new WebClient())
                    {
                        asd.Encoding = Encoding.UTF8;
                        q = asd.DownloadString("http://gdata.youtube.com/feeds/api/users/" + kadi + "/uploads?v=2&alt=jsonc&max-results=0");
                    }
                    string[] adet1 = q.Split(new string[] { "totalItems\":" }, StringSplitOptions.None);
                    string[] adet2 = adet1[1].Split(',');
                    listView1.Items.Add(new ListViewItem(new string[] { hadi, "Adet: "+adet2[0] }));
                }
                dr.Close();
                komut.ExecuteNonQuery(); // insert , updateiçin gerekli satir sayisi donduruyoruz.
                dr = komut.ExecuteReader(); // datareader olusturup komut sorgulayıp veritabaninda okuma işlemini tanıtıyoruz
                while (dr.Read()) // datareader ile okuyoruz.
                {
                    string kadi = dr["hadi"].ToString(); // veritabanimdaki "kadi" alanımdaki veriyi alip kadi değişkenine atıyorum(yukarıda string olusturmustum)
                    string sifre = dr["hsifresi"].ToString(); // aynı durum söz konusu
                    string devkey = dr["devkey"].ToString();
                    Random a = new Random();
                    string id = a.Next(100000, 999999).ToString();
                    YouTubeRequestSettings settings = new YouTubeRequestSettings(id, devkey, kadi,sifre);
                    YouTubeRequest request = new YouTubeRequest(settings);

                    string feedUrl = "https://gdata.youtube.com/feeds/api/users/default/uploads";

                    Feed<Video> videoFeed = request.Get<Video>(new Uri(feedUrl));
                    foreach (Video entry in videoFeed.Entries)
                    {
                        string vid_thumb ="http://img.youtube.com/vi/"+entry.VideoId+"/0.jpg";
                        int izlenme = entry.ViewCount;
                        if(izlenme == -1)
                            izlenme = 0;
                        listView1.Items.Add(new ListViewItem(new string[] { kadi,entry.YouTubeEntry.Title.Text,izlenme.ToString()  }));
                    }
                }
            }
        }

 private void btnReset_Click(object sender, EventArgs e)
 {
     if (connection.State.Equals(ConnectionState.Closed))
     {
         connection = new OleDbConnection();
         connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\DB\\UC.mdb;Persist Security Info=False";
         connection.Open();
     }
     OleDbCommand cmd = new OleDbCommand();
     cmd.CommandText = "DELETE * FROM InputTable";
     cmd.Connection = connection;
     int a = cmd.ExecuteNonQuery();
     cmd.CommandText = "DELETE * FROM OutputTable";
     a = cmd.ExecuteNonQuery();
 }

        //清除用户
        private void Btn_Delete_User_Click(object sender, EventArgs e)
        {
            OleDbConnection oleDB = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=dac.accdb");
            oleDB.Open();
            OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = oleDB;
            cmd.CommandText = "delete  from  [主体信息表] where 主体名称=" + "'" + this.ComboBox_UserName.Text.ToLower() + "'";
            cmd.ExecuteNonQuery();
            cmd.CommandText = "delete  from  [用户-角色指派信息表] where 用户名称=" + "'" + this.ComboBox_UserName.Text.ToLower() + "'";
            cmd.ExecuteNonQuery();
            oleDB.Close();
            MessageBox.Show(this, "删除用户成功", "成功", MessageBoxButtons.OK, MessageBoxIcon.Information);

               Refresh_Users_Roles(0);
        }

        private void addbtn_Click(object sender, EventArgs e)
        {
            if (nameTxt.Text.Length < 1 || lastNameTxt.Text.Length < 1 || addressTxt.Text.Length < 1 || phoneTxt.Text.Length < 1)
            {
                MessageBox.Show("All fields must be entered!", "Invalid Values", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }

            Connection connection = new Connection();

            connection.openConnection();

            OleDbCommand customerCommand = new OleDbCommand();
            customerCommand.Connection = connection.connection;

            string name = nameTxt.Text;
            string lastName = lastNameTxt.Text;
            string address = addressTxt.Text;
            string phone = phoneTxt.Text;
            string birthday = birthdayPicker.Value.ToShortDateString();

            if (edit)
            {
                customerCommand.CommandText = "UPDATE Customers SET First_Name = '" + name + "', Last_Name = '" + lastName + "', Address = '" + address + "', Phone = '" + phone + "', Birthday = #" + birthday + "# WHERE ID = " + customer.id;
                customerCommand.ExecuteNonQuery();

                customer.name = name;
                customer.lastName = lastName;
                customer.address = address;
                customer.phone = phone;
                customer.birthday = birthdayPicker.Value.Date;
            }
            else
            {
                customerCommand.CommandText = "INSERT INTO Customers (First_Name,Last_Name,Address,Phone,Birthday) VALUES ('" + name + "', '" + lastName + "', '" + address + "', '" + phone + "', #" + birthday + "#)";
                customerCommand.ExecuteNonQuery();

                customerCommand.CommandText = "Select @@Identity";
                string idCustomer = customerCommand.ExecuteScalar().ToString();

                customer = new CCustomer(idCustomer, name, lastName, address, phone, birthdayPicker.Value.Date);
            }

            connection.closeConnection();

            DialogResult = System.Windows.Forms.DialogResult.OK;
            Close();
        }

        public bool DeleteSurplusRows()
        {
            if (m_pOleDbConnection != null)
            {
                try
                {
                    //删除反向次数为偶数的记录记录
                    string strCommand = "Delete * From " + TableName_TempTable + " Where " + FieldName_LineNodeID
                        + " in ( Select " + FieldName_LineNodeID + " From ("
                        + " Select " + FieldName_LineNodeID + ",Count(*) as RowCount From " + TableName_TempTable + " Group by "+FieldName_LineNodeID
                        +" ) Where RowCount Mod 2=0 )";

                    OleDbCommand oleDbCommand = new OleDbCommand(strCommand, m_pOleDbConnection);
                    oleDbCommand.ExecuteNonQuery();

                    //删除重复记录（只保留一个）
                    strCommand = "Delete * From " + TableName_TempTable + " Where " + FieldName_IndexID
                        + " in( Select a." + FieldName_IndexID + " From " + TableName_TempTable + " As a," + TableName_TempTable + " As b Where a." + FieldName_LineNodeID + "=b." + FieldName_LineNodeID + " And a." + FieldName_IndexID + "<b." + FieldName_IndexID + ")";

                    oleDbCommand = new OleDbCommand(strCommand, m_pOleDbConnection);
                    oleDbCommand.ExecuteNonQuery();

                    return true;
                }
                catch (Exception ex)
                {
                    LogAPI.WriteErrorLog(ex);
                }
            }
            return false;
        }

        public override bool CreateTable()
        {
            if (m_pOleDbConnection != null)
            {
                try
                {
                    base.CreateTable();

                    string strCommand = "Create Table " + TableName_TempTable + "("
                        + FieldName_IndexID + " int primary key,"
                        + FieldName_LineNodeID + " int)";

                    OleDbCommand oleDbCommand = new OleDbCommand(strCommand, m_pOleDbConnection);
                    oleDbCommand.ExecuteNonQuery();

                    nIndex = 0;
                    return true;
                }
                catch (Exception ex)
                {
                    LogAPI.WriteErrorLog(ex);
                }
            }
            return false;
        }

        static void Main()
        {
            var excelConn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=..\\..\\TestBook.xlsx; " +
                "Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"");
            excelConn.Open();
            using (excelConn)
            {
                // Task 6
                Console.WriteLine("\n\nName and Score Cells:");
                var cmdScores = new OleDbCommand("SELECT * FROM [Scores$]", excelConn);
                var reader = cmdScores.ExecuteReader();
                using (reader)
                {
                    while (reader.Read())
                    {
                        string name = reader["Name"].ToString();
                        string score = reader["Score"].ToString();
                        Console.WriteLine("{0,-15} - {1}", name, score);
                    }
                }

                // Task 7
                Console.WriteLine("\n\nInserting new row into the excel sheet Scores...");
                string scName = "Some Student";
                double scScore = 21;
                var cmdInsertProduct = new OleDbCommand("INSERT INTO [Scores$]([Name],[Score]) VALUES (@scName, @scScore)", excelConn);
                cmdInsertProduct.Parameters.AddWithValue("@scName", scName);
                cmdInsertProduct.Parameters.AddWithValue("@scScore", scScore);
                cmdInsertProduct.ExecuteNonQuery();
                Console.WriteLine("Name: {0} - Score: {1}", scName, scScore);
            }
        }

 protected void Page_Load(object sender, EventArgs e)
 {
     if (!this.IsPostBack)
     {
         string name = Request["name"].ToString();
         OleDbConnection con = DB.createDB();
         con.Open();
         OleDbCommand cmd = new OleDbCommand();//声明一个OleDbCommand的 cmd对象，并将其实例化
         cmd.Connection = con;
         cmd.CommandText = "select * from tb_qianfei where name='" + name + "'";
         cmd.ExecuteNonQuery();
         OleDbDataReader sdr = cmd.ExecuteReader();
         sdr.Read();
         if (name!= "")
         {
             this.lblqfname.Text = sdr.GetString(0).ToString();
             this.lblzz.Text = sdr.GetString(1);
             this.lbllb.Text = sdr.GetString(2);
             this.lblrqzs.Text = sdr.GetDateTime(3).ToShortDateString();
             this.lblzzsj.Text = sdr.GetDateTime(4).ToShortDateString();
             this.lblje.Text = sdr.GetString(5).ToString();
             this.Lblzt.Text = sdr.GetString(6).ToString();
             this.lbljsr.Text = sdr.GetString(7).ToString();
         }
         else
         {
             Response.Write("暂无主题,不能显示");
             Response.Redirect("Default.aspx");//将该页跳转到指定的页面中
         }
     }
 }