添加引用

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，可以读取excel2007以前的版本，在客户机上不需要部署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 (?,?)”;

cmd.CommandType = CommandType.Text;

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

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

conn.Open();

cmd.ExecuteNonQuery();

例3：

cmd.CommandText = “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 });

https://docs.microsoft.com/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);

command.CommandText =

        "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();

cmd.CommandType = System.Data.CommandType.Text;

cmd.Connection = cnn;

cmd.CommandText = 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();