ADO.NET Access 連線 新增 修改 刪除 查詢

ADO.NET Access 連線 新增 修改 刪除 查詢

ALTER TABLE note1 ADD new_co2 MEMO  MEMO = 備忘類別

注意  cmd.Parameters 的 排序 ，要跟sqlcmd上的順序一樣

 String strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data source= C:\MyNote.mdb";

        private void note_add()
        {
            OleDbConnection conn = new OleDbConnection(strConn);
            string strnow = DateTime.Now.ToString("yyyyMMddHHmmssfff");
            string strdate = dateTimePicker1.Value.ToString("yyyyMMdd");
            string strseq = getNoteSeq(strdate).ToString();
            string strtitle = textBox1.Text;
            string strnote = textBox2.Text;

            try
            {
                //步驟三建立Command物件讀取資料庫的資料
                String strCmd = "INSERT INTO note1 ( note_sid, note_date, note_seq,note_title,note_note) VALUES (@note_sid, @note_date, @note_seq,@note_title,@note_note) ";
                using (OleDbCommand cmd = new OleDbCommand(strCmd, conn))
                {
                    // 要對照sqlcmd的順序排
                    cmd.Parameters.AddWithValue("@note_sid", strnow);
                    cmd.Parameters.AddWithValue("@note_date", strdate);
                    cmd.Parameters.AddWithValue("@ note_seq", strseq);
                    cmd.Parameters.AddWithValue("@note_title", strtitle);
                    cmd.Parameters.AddWithValue("@note_note", strnote);

                    //步驟四建立DataReader物件處理讀出來的資料
                    //透過Command物件所提供的ExecuteReader的方法來產生Command
                    //要先開啟連線
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("新增-完成");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Failed to connect to data source" + ex);
            }
            finally
            {
                conn.Close();
            } 
        }


        private void note_up()
        {
            OleDbConnection conn = new OleDbConnection(strConn);
            string strdate = dateTimePicker1.Value.ToString("yyyyMMdd");
            string strtitle = textBox1.Text;
            string strnote = textBox2.Text;
            string stroid = txt_note_oid.Text;
            try
            {
                //步驟三建立Command物件讀取資料庫的資料
               //UPDATE note1 SET note_date = '1', note_seq = '1' WHERE (note_oid = 2)
                String strCmd = "UPDATE note1 SET note_date=@note_date, note_title=@note_title,note_note=@note_note WHERE note_oid =@note_oid ";
                using (OleDbCommand cmd = new OleDbCommand(strCmd, conn))
                {
                    // 要對照sqlcmd的順序排
                    cmd.Parameters.AddWithValue("@note_date", strdate);
                    cmd.Parameters.AddWithValue("@note_title", strtitle);
                    cmd.Parameters.AddWithValue("@note_note", strnote);
                    cmd.Parameters.AddWithValue("@note_sid", stroid);

                    //步驟四建立DataReader物件處理讀出來的資料
                    //透過Command物件所提供的ExecuteReader的方法來產生Command
                    //要先開啟連線
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("修改-完成");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Failed to connect to data source" + ex);
            }
            finally
            {
                conn.Close();
            }
        }

        private void note_del()
        {
            OleDbConnection conn = new OleDbConnection(strConn);
            string strdate = dateTimePicker1.Value.ToString("yyyyMMdd");
            string strtitle = textBox1.Text;
            string strnote = textBox2.Text;
            string stroid = txt_note_oid.Text;
            try
            {
                //步驟三建立Command物件讀取資料庫的資料
              //  DELETE FROM note1 WHERE  (note1.note_oid = 6)

                String strCmd = "DELETE FROM note1 WHERE note_oid =@note_oid ";
                using (OleDbCommand cmd = new OleDbCommand(strCmd, conn))
                {
                    cmd.Parameters.AddWithValue("@note_oid", stroid);
              
                    //步驟四建立DataReader物件處理讀出來的資料
                    //透過Command物件所提供的ExecuteReader的方法來產生Command
                    //要先開啟連線
                    conn.Open();
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("刪除-完成");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Failed to connect to data source" + ex);
            }
            finally
            {
                conn.Close();
            }
        }

        private void getNoteData(string strtag)
        {

            OleDbConnection conn = new OleDbConnection(strConn);

            try
            {
                //步驟三建立Command物件讀取資料庫的資料
                String strCmd = "Select * from note1 where note_oid=@note_oid ";
                using (OleDbCommand cmd = new OleDbCommand(strCmd, conn))
                {
                    cmd.Parameters.AddWithValue("@note_oid", strtag);

                    //步驟四建立DataReader物件處理讀出來的資料
                    //透過Command物件所提供的ExecuteReader的方法來產生Command
                    //要先開啟連線
                    conn.Open();
                    OleDbDataReader dr = cmd.ExecuteReader();
                    //步驟五在瀏覽器上顯示資料
                    //讀取資料之前要先呼叫read()的方法

                    string[] DateTimeList = { "yyyy/M/d tt hh:mm:ss", "yyy/MM/dd tt hh:mm:ss", "yyyy/MM/dd HH:mm:ss", "yyyy/M/d HH:mm:ss", "yyyy/M/d", "yyyy/MM/dd" };

                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            string str = dr["note_date"].ToString();
                            str = str.Substring(0, 4) + "/" + str.Substring(4, 2) + "/" + str.Substring(6, 2);
                            DateTime dt = DateTime.ParseExact(str, DateTimeList, CultureInfo.InvariantCulture, DateTimeStyles.AllowWhiteSpaces);
                            // DateTime.Parse(dr["note_date"].ToString(), "yyyy/MM/dd");
                            dateTimePicker1.Value = dt;
                            txt_note_oid.Text = strtag;
                            textBox1.Text = dr["note_title"].ToString();
                            textBox2.Text = dr["note_note"].ToString();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Failed to connect to data source" + ex);
            }
            finally
            {
                conn.Close();
            }
        }