C#對Access停止增刪改查的完全示例。本站提示廣大學習愛好者:(C#對Access停止增刪改查的完全示例)文章只能為提供參考,不一定能成為您想要的結果。以下是C#對Access停止增刪改查的完全示例正文
這篇文章整頓了C#對Access數據庫的查詢、添加記載、刪除記載和更新數據等一系列的操作示例,有須要的可以參考進修。
起首是AccessHelper.cs,網上有下載,上面附送一份;
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Data.OleDb; using System.Data; using System.Windows.Forms; namespace yxdain { public class AccessHelper { private string conn_str = null; private OleDbConnection ole_connection = null; private OleDbCommand ole_command = null; private OleDbDataReader ole_reader = null; private DataTable dt = null; /// <summary> /// 結構函數 /// </summary> public AccessHelper() { //conn_str = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + Environment.CurrentDirectory + "\\yxdain.accdb'"; conn_str = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + Environment.CurrentDirectory + "\\yxdain.accdb'"; InitDB(); } private void InitDB() { ole_connection =new OleDbConnection(conn_str);//創立實例 ole_command =new OleDbCommand(); } /// <summary> /// 結構函數 /// </summary> ///<param name="db_path">數據庫途徑 public AccessHelper(string db_path) { //conn_str ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source='"+ db_path + "'"; conn_str = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + db_path + "'"; InitDB(); } /// <summary> /// 轉換數據格局 /// </summary> ///<param name="reader">數據源 /// <returns>數據列表</returns> private DataTable ConvertOleDbReaderToDataTable(ref OleDbDataReader reader) { DataTable dt_tmp =null; DataRow dr =null; int data_column_count = 0; int i = 0; data_column_count = reader.FieldCount; dt_tmp = BuildAndInitDataTable(data_column_count); if(dt_tmp == null) { return null; } while(reader.Read()) { dr = dt_tmp.NewRow(); for(i = 0; i < data_column_count; ++i) { dr[i] = reader[i]; } dt_tmp.Rows.Add(dr); } return dt_tmp; } /// <summary> /// 創立並初始化數據列表 /// </summary> ///<param name="Field_Count">列的個數 /// <returns>數據列表</returns> private DataTable BuildAndInitDataTable(int Field_Count) { DataTable dt_tmp =null; DataColumn dc =null; int i = 0; if(Field_Count <= 0) { return null; } dt_tmp =new DataTable(); for(i = 0; i < Field_Count; ++i) { dc =new DataColumn(i.ToString()); dt_tmp.Columns.Add(dc); } return dt_tmp; } /// <summary> /// 從數據庫外面獲得數據 /// </summary> ///<param name="strSql">查詢語句 /// <returns>數據列表</returns> public DataTable GetDataTableFromDB(string strSql) { if(conn_str == null) { return null; } try { ole_connection.Open();//翻開銜接 if(ole_connection.State == ConnectionState.Closed) { return null; } ole_command.CommandText = strSql; ole_command.Connection = ole_connection; ole_reader = ole_command.ExecuteReader(CommandBehavior.Default); dt = ConvertOleDbReaderToDataTable(ref ole_reader); ole_reader.Close(); ole_reader.Dispose(); } catch(System.Exception e) { //Console.WriteLine(e.ToString()); MessageBox.Show(e.Message); } finally { if(ole_connection.State != ConnectionState.Closed) { ole_connection.Close(); } } return dt; } /// <summary> /// 履行sql語句 /// </summary> ///<param name="strSql">sql語句 /// <returns>前往成果</returns> public int ExcuteSql(string strSql) { int nResult = 0; try { ole_connection.Open();//翻開數據庫銜接 if(ole_connection.State == ConnectionState.Closed) { return nResult; } ole_command.Connection = ole_connection; ole_command.CommandText = strSql; nResult = ole_command.ExecuteNonQuery(); } catch(System.Exception e) { //Console.WriteLine(e.ToString()); MessageBox.Show(e.Message); return nResult; } finally { if(ole_connection.State != ConnectionState.Closed) { ole_connection.Close(); } } return nResult; } } }
界說變量,設置列題目;
private AccessHelper achelp; ...... private void Form1_Load(object sender, EventArgs e) { achelp = new AccessHelper(); string sql1 = "select * from ycyx"; databind1(sql1); dataGridView1.Columns[0].Visible = false; dataGridView1.Columns[1].HeaderCell.Value = "辦事號碼"; dataGridView1.Columns[2].HeaderCell.Value = "客戶稱號"; dataGridView1.Columns[3].HeaderCell.Value = "歸屬地域"; dataGridView1.Columns[4].HeaderCell.Value = "以後品牌"; dataGridView1.Columns[5].HeaderCell.Value = "以後套餐"; dataGridView1.Columns[6].HeaderCell.Value = "以後狀況"; }
顯示數據表全體內容;
private void databind1(string sqlstr) { DataTable dt = new DataTable(); dt = achelp.GetDataTableFromDB(sqlstr); dataGridView1.DataSource = dt; }
讀取要更新記載到更新窗體控件;
private void button3_Click(object sender, EventArgs e) { if (dataGridView1.SelectedRows.Count < 1 || dataGridView1.SelectedRows[0].Cells[1].Value == null) { MessageBox.Show("沒有選中行。", "M營銷"); return; } //f3.Owner = this; DataTable dt = new DataTable(); object oid = dataGridView1.SelectedRows[0].Cells[0].Value; string sql = "select * from ycyx where ID=" + oid; dt = achelp.GetDataTableFromDB(sql); f3 = new Form3(); f3.id = int.Parse(oid.ToString()); //f3.id = 2; f3.Text1 = dt.Rows[0][1].ToString(); f3.Text2 = dt.Rows[0][2].ToString(); f3.Text3 = dt.Rows[0][3].ToString(); f3.Text4 = dt.Rows[0][4].ToString(); f3.Text5 = dt.Rows[0][5].ToString(); f3.Text6 = dt.Rows[0][6].ToString(); f3.ShowDialog(); }
添加記載;
private void button4_Click(object sender, EventArgs e) { if (textBox1.Text == "" && textBox2.Text == "" && textBox3.Text == "" && textBox4.Text == "" && textBox5.Text == "" && textBox6.Text == "") { MessageBox.Show("沒有要添加的內容", "M營銷添加"); return; } else { string sql = "insert into ycyx (fwhm,khmc,gsdq,dqpp,dqtc,dqzt) values ('" + textBox1.Text + "','" + textBox2.Text + "','"+ textBox3.Text + "','"+ textBox4.Text + "','"+ textBox5.Text + "','"+ textBox6.Text + "')"; int ret = achelp.ExcuteSql(sql); string sql1 = "select * from ycyx"; databind1(sql1); textBox1.Text = ""; textBox2.Text = ""; textBox3.Text = ""; textBox4.Text = ""; textBox5.Text = ""; textBox6.Text = ""; } }
刪除記載;
private void button2_Click(object sender, EventArgs e) { if (dataGridView1.SelectedRows.Count < 1 || dataGridView1.SelectedRows[0].Cells[1].Value == null) { MessageBox.Show("沒有選中行。", "M營銷"); } else { object oid = dataGridView1.SelectedRows[0].Cells[0].Value; if (DialogResult.No == MessageBox.Show("將刪除第 " + (dataGridView1.CurrentCell.RowIndex + 1).ToString() + " 行,肯定?", "M營銷", MessageBoxButtons.YesNo)) { return; } else { string sql = "delete from ycyx where ID=" + oid; int ret = achelp.ExcuteSql(sql); } string sql1 = "select * from ycyx"; databind1(sql1); } }
查詢;
private void button13_Click(object sender, EventArgs e) { if (textBox23.Text == "") { MessageBox.Show("請輸出要查詢確當前品牌", "M營銷"); return; } else { string sql = "select * from ycyx where dqpp='" + textBox23.Text + "'"; DataTable dt = new System.Data.DataTable(); dt = achelp.GetDataTableFromDB(sql); dataGridView1.DataSource = dt; } }
用戶肯定顯示或不顯示哪些數據列;
private void button15_Click(object sender, EventArgs e) { if (checkBox1.Checked == true) { dataGridView1.Columns[1].Visible = true; } else { dataGridView1.Columns[1].Visible = false; } if (checkBox2.Checked == true) { dataGridView1.Columns[2].Visible = true; } else { dataGridView1.Columns[2].Visible = false; } if (checkBox3.Checked == true) { dataGridView1.Columns[3].Visible = true; } else { dataGridView1.Columns[3].Visible = false; } if (checkBox4.Checked == true) { dataGridView1.Columns[4].Visible = true; } else { dataGridView1.Columns[4].Visible = false; } if (checkBox5.Checked == true) { dataGridView1.Columns[5].Visible = true; } else { dataGridView1.Columns[5].Visible = false; } if (checkBox6.Checked == true) { dataGridView1.Columns[6].Visible = true; } else { dataGridView1.Columns[6].Visible = false; } }
更新數據;
public partial class Form3 : Form { private AccessHelper achelp; private int iid; public Form3() { InitializeComponent(); achelp = new AccessHelper(); iid = 0; } // 更新 private void button1_Click(object sender, EventArgs e) { try { //UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing'WHERE LastName = 'Wilson' string sql = "update ycyx set fwhm='"+textBox1.Text+"',khmc='"+textBox2.Text+"',gsdq='"+textBox3.Text+"',dqpp='"+textBox4.Text+ "',dqtc='"+textBox5.Text+"',dqzt='"+textBox6.Text+"' where ID="+iid; int ret = achelp.ExcuteSql(sql); if (ret > -1) { this.Hide(); MessageBox.Show("更新勝利", "M營銷"); } } catch (Exception ex) { MessageBox.Show(ex.Message); } } private void Form3_Load(object sender, EventArgs e) { } public int id { get { return this.iid; } set { this.iid = value; } } public string Text1 { get { return this.textBox1.Text; } set { this.textBox1.Text = value; } } public string Text2 { get { return this.textBox2.Text; } set { this.textBox2.Text = value; } } public string Text3 { get { return this.textBox3.Text; } set { this.textBox3.Text = value; } } public string Text4 { get { return this.textBox4.Text; } set { this.textBox4.Text = value; } } public string Text5 { get { return this.textBox5.Text; } set { this.textBox5.Text = value; } } public string Text6 { get { return this.textBox6.Text; } set { this.textBox6.Text = value; } } //撤消 private void button2_Click(object sender, EventArgs e) { this.Hide(); } } }
留意此處有一個技能;C# Winform,在窗體之間傳值,或在一個窗體中設置另外一個窗體的控件的值時,有多種方法;最好方法是如上代碼所示;應用.net的get
、set
屬性;
控件是一個窗體的公有變量,不克不及在另外一個窗體中直接拜訪;為了在a窗體中設置b窗體的控件的值,對b窗體的控件都添加一個帶get
、set
的公共屬性,便可在a中設置b中控件的值,詳細看代碼;
以上就是C#對Access停止增刪改查的完全示例代碼,願望對年夜家進修C#能有所贊助。