Complete example of C adding deleting and modifying Access
- 2021-11-01 04:23:50
- OfStack
This article collates C # to Access database query, add records, delete records and update data and other 1 series of operational examples, there is a need for reference to learn.
The first is AccessHelper. cs, which is downloaded online, and one copy is attached below;
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>
/// Constructor
/// </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);// Create an instance
ole_command =new OleDbCommand();
}
/// <summary>
/// Constructor
/// </summary>
///<param name="db_path"> Database 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>
/// Convert data format
/// </summary>
///<param name="reader"> Data source
/// <returns> Data list </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>
/// Create and initialize a data list
/// </summary>
///<param name="Field_Count"> Number of columns
/// <returns> Data list </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>
/// Get data from the database
/// </summary>
///<param name="strSql"> Query statement
/// <returns> Data list </returns>
public DataTable GetDataTableFromDB(string strSql)
{
if(conn_str == null)
{
return null;
}
try
{
ole_connection.Open();// Open a connection
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>
/// Execute sql Statement
/// </summary>
///<param name="strSql">sql Statement
/// <returns> Return results </returns>
public int ExcuteSql(string strSql)
{
int nResult = 0;
try
{
ole_connection.Open();// Open a database connection
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;
}
}
}Define variables and set column headers;
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 = " Service number ";
dataGridView1.Columns[2].HeaderCell.Value = " Customer name ";
dataGridView1.Columns[3].HeaderCell.Value = " Belonging area ";
dataGridView1.Columns[4].HeaderCell.Value = " Current brand ";
dataGridView1.Columns[5].HeaderCell.Value = " Current package ";
dataGridView1.Columns[6].HeaderCell.Value = " Current status ";
}Display all the contents of the data table;
private void databind1(string sqlstr)
{
DataTable dt = new DataTable();
dt = achelp.GetDataTableFromDB(sqlstr);
dataGridView1.DataSource = dt;
}Read the record to be updated to the update form control;
private void button3_Click(object sender, EventArgs e)
{
if (dataGridView1.SelectedRows.Count < 1 || dataGridView1.SelectedRows[0].Cells[1].Value == null)
{
MessageBox.Show(" No rows are selected. ", "M Marketing ");
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();
}Add records;
private void button4_Click(object sender, EventArgs e)
{
if (textBox1.Text == "" && textBox2.Text == "" && textBox3.Text == "" && textBox4.Text == "" && textBox5.Text == "" && textBox6.Text == "")
{
MessageBox.Show(" There is nothing to add ", "M Marketing addition ");
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 = "";
}
}Delete records;
private void button2_Click(object sender, EventArgs e)
{
if (dataGridView1.SelectedRows.Count < 1 || dataGridView1.SelectedRows[0].Cells[1].Value == null)
{
MessageBox.Show(" No rows are selected. ", "M Marketing ");
}
else
{
object oid = dataGridView1.SelectedRows[0].Cells[0].Value;
if (DialogResult.No == MessageBox.Show(" Will delete the " + (dataGridView1.CurrentCell.RowIndex + 1).ToString() + " OK, sure? ", "M Marketing ", MessageBoxButtons.YesNo))
{
return;
}
else
{
string sql = "delete from ycyx where ID=" + oid;
int ret = achelp.ExcuteSql(sql);
}
string sql1 = "select * from ycyx";
databind1(sql1);
}
}Inquiry;
private void button13_Click(object sender, EventArgs e)
{
if (textBox23.Text == "")
{
MessageBox.Show(" Please enter the current brand to query ", "M Marketing ");
return;
}
else
{
string sql = "select * from ycyx where dqpp='" + textBox23.Text + "'";
DataTable dt = new System.Data.DataTable();
dt = achelp.GetDataTableFromDB(sql);
dataGridView1.DataSource = dt;
}
}The user determines which data columns are displayed or not displayed;
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;
}
}Update data;
public partial class Form3 : Form
{
private AccessHelper achelp;
private int iid;
public Form3()
{
InitializeComponent();
achelp = new AccessHelper();
iid = 0;
}
// Update
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(" Update succeeded ", "M Marketing ");
}
}
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; }
}
// Cancel
private void button2_Click(object sender, EventArgs e)
{
this.Hide();
}
}
}
Note that there is one trick here; C # Winform, there are many ways to pass values between forms, or to set the values of controls of one form in another form; The best way is as shown in the above code; Object using. net
get
,
set
Attribute;
Control is a private variable of 1 form and cannot be accessed directly in another form; In order to set the value of the control of the b form in the a form, add 1 band to the control of the b form
get
,
set
Public attribute, you can set the value of b control in a, see the code specifically;
The above is the complete example code of C # to add, delete and change Access, hoping to help everyone learn C #.