添加引用

using System.Data.OleDb;

数据库连接字符串：

tandard security

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;Persist Security Info=False;

With database password

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;Jet OLEDB:Database Password=MyDbPassword;

DataDirectory functionality

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\myAccessFile.accdb;Persist Security Info=False;

Network Location

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\server\share\folder\myAccessFile.accdb;

如连接数据库出现问题：

“未在本地计算机上注册“Microsoft.ACE.OLEDB.12.0”提供程序”

安装需要X64数据引擎, 连接字符串的问题。采用Microsoft.Jet.OleDb.4.0，可以读取excel以前的版本，在客户机上不需要部署office，采用Microsoft.Ace.OleDb.12.0的时候，需要安装引擎。

安装OK后设置程序的平台为X64

插入语句 Insert

查询的SQL语句的字段必须是中括号，如 [UserName]

例1：

“INSERT INTO bookRated [title], [rating], [review], [frnISBN], [frnUserName]) VALUES(@title, @rating, @review, @isbn, @username)”;

// add named parameters

cmd.Parameters.AddRange(new OleDbParameter[]

{

new OleDbParameter(“@title”, title),

new OleDbParameter(“@rating”, rating),

…

});

// execute

cmd.ExecuteNonQuery();

或者cmd.Parameters.AddWithValue(“@fn”, txtFirstName.Text);

例2：

“Insert Into Contacts (FirstName, LastName) Values (?,?)”;

mandType = CommandType.Text;

cmd.Parameters.AddWithValue(? txtFirstName.Text);

cmd.Parameters.AddWithValue(?, txtLastName.Text);

conn.Open();

cmd.ExecuteNonQuery();

例3：

mandText = “INSERT INTO EmployeeData([FirstName],[UserName],[PassWord],[LastName],[DOB],[Counrty],[Phone],[Pay]) VALUES(?,?,?,?,?,?,?,?)”;

cmd.Parameters.Add(new OleDbParameter(“?”, OleDbType.VarChar, 20) { Value = txtFirstName.Text });

…

cmd.Parameters.Add(new OleDbParameter(“?”, OleDbType.Decimal, 20) { Value = txtPay.Text });

/en-us/dotnet/api/system.data.oledb.oledbcommand.parameters?redirectedfrom=MSDN&view=dotnet-plat-ext-6.0#System_Data_OleDb_OleDbCommand_Parameters

OleDbCommand command = new OleDbCommand(queryString, connection);mandText ="SELECT CustomerID, CompanyName FROM Customers WHERE Country = ? AND City = ?";command.Parameters.Add(parameters);for (int j=0; j<parameters.Length; j++){command.Parameters.Add(parameters\[j\]) ;}

更新语句Update

cnn.Open();string sql = "UPDATE EmployeeData SET \[FirstName\]=@fn, \[UserName\]=@un,\[PassWord\]=@ps,\[LastName\]=@ln, \[DOB\]=@dob, \[Counrty\]=@ct,\[Phone\]=@ph,\[Pay\]=@pay WHERE \[ID\]=@id";OleDbCommand cmd = new OleDbCommand();mandType = mandType.Text;cmd.Connection = cnn;mandText = sql;cmd.Parameters.AddWithValue("@fn", txtFirstName.Text);cmd.Parameters.AddWithValue("@un", txtUserName.Text);cmd.Parameters.AddWithValue("@ps", txtPassword.Text);cmd.Parameters.AddWithValue("@ln", txtLastName.Text);cmd.Parameters.AddWithValue("@dob", dtBirthday.SelectedDate);cmd.Parameters.AddWithValue("@ct", txtCountry.Text);cmd.Parameters.AddWithValue("@ph", txtPhone.Text);cmd.Parameters.AddWithValue("@pay", txtPay.Text);cmd.Parameters.AddWithValue("@id", txtOperateID.Text);int r = cmd.ExecuteNonQuery();cnn.Close();

数据查询 Select

if (txtOperateID.Text == ""){sql = "Select \* from EmployeeData";}else{sql = "Select \* from EmployeeData Where ID =" + txtOperateID.Text;}DataSet ds = new DataSet();OleDbDataAdapter adapter = new OleDbDataAdapter(sql, cnn);adapter.Fill(ds);dg.ItemsSource = ds.Tables\[0\].DefaultView;

删除数据Delete

cnn.Open();string sql = "Delete from EmployeeData Where ID =" + txtOperateID.Text;OleDbCommand cmd = new OleDbCommand(sql, cnn);int r= cmd.ExecuteNonQuery();

如果觉得《C# 操作Access 数据库》对你有帮助，请点赞、收藏，并留下你的观点哦！