添加引用
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 数据库》对你有帮助,请点赞、收藏,并留下你的观点哦!