I have a need in a new
program I am creating to talk to an Access DB.
I am programming in C# using Visual Studio Pro 2012.
I am using InstallShield
LE for my installation process. If you
are having problems getting InstallShield LE working on Visual Studio I did a
little write up on it here http://www.whiteboardcoder.com/2013/02/visual-studio-pro-2012-install-shield-le.html
First a few Gotchas
Before I get into the
programming I want to list a few gotchas that I had to deal with. I hope this saves you some programming time
before you start.
My first gotcha is a
dump one, I don't create databases all that often so I foolishly named my table
"Names" which is an SQL reserved word. I knew this in the back of my head but it's
been so long since I made an example simple database I had forgotten….
My second gotcha is at
least a little more impressive…
I have a 64 bit version
Win 7 running on my Box. I have Office
2010 installed and running as 32-bit programs.
If you want to check if your office suite is 32-bit or 64-bit check this
guide http://www.howtogeek.com/howto/24259/beginner-discover-if-youre-running-the-32-or-64-bit-version-of-office-2010/ [1]
By Default my Visual
Studio 2012 C# program is set to compile to Any CPU (I am guessing 32-bit or 64-bit). As a result if I try to compile my program
and connect to a database I get this error.
The error "The
'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine."
Is caused by the program trying to find a 64 bit connection and not finding it,
since in my case there is only a 32-bit one.
There seems to be two
ways to fix this.
The first way is to go
to http://www.codeproject.com/Tips/370922/Microsoft-Access-Database-Engine-2010-Redistributa [2] download and install this program that will
then be able to handle 64-bit connection and 32-bit connections.
The second way involves
fixing Platform target of your program.
From Visual Studio 2012
select Build--> Configuration Manager
Change "Any
CPU" to x86 and click Close. This forces the issue and uses the 32-bit
connection type.
This is the solution I
chose, as for my client it's one more thing they need to install before they
can use the software.
Programmatically Accessing the Database
I am starting with a
basic "Hello World" Program I made before. (if you need help with getting that working
here is the details http://www.whiteboardcoder.com/2013/02/visual-studio-pro-2012-install-shield-le.html
I am editing a button
click. So that when the button is
clicked it will read from a table called "Employees" in my database
that is located on my Desktop.
Here is the code.
|
private void button1_Click(object sender, EventArgs e)
{
String connectionString =
@"Provider=Microsoft.ACE.OLEDB.12.0;Data"
+ @" Source=C:\Users\patman\Desktop\Test_DB.accdb";
String tableName = "Employees";
String query = String.Format(
"select *
from [{0}]", tableName);
DataSet ds = new DataSet();
OleDbConnection conn =
new OleDbConnection(connectionString);
try
{
//Open Database
Connection
conn.Open();
OleDbDataAdapter da =
new OleDbDataAdapter(query, conn);
//Fill the DataSet
da.Fill(ds, tableName);
conn.Close();
MessageBox.Show(" Hello World
"
+ ds.Tables["Employees"].Rows.Count
+ " "
+
ds.Tables["Employees"].Rows[0]["id"]
+ " "
+
ds.Tables["Employees"].Rows[0]["fname"]
+ " "
+
ds.Tables["Employees"].Rows[0]["lname"]);
}
catch (OleDbException exp)
{
MessageBox.Show("Database
Error:"
+
exp.Message.ToString());
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
|
|
|
First the connection
string to my database is
Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=C:\Users\patman\Desktop\Test_DB.accdb
I query it to get all
the data from tableName. It then fills
the DataSet ds with the all the data from this table. Then it create a MessageBox do display the
first row of the query results the column called fname.
Looking at Employees
Table in my test database you can see that the first record in the fname column
is "Fred"
Now if you run the
program and click the "Hello World Button" you will see that it does grab the data from
the database and display it.
In fact I can update the
database while the program is still running, changing Fred to Lisa, and because
in this case it always queries the database every time the button is pressed
the results will update.
And the results are
updated the next time you click on HelloWorld.
Updating ACCESS Database
Now that we can successfully
read from an ACCESS database how about adding records to the database in C#
The first step is to update the
DataSet. Here is my code that updates
the information in the first Row of the Dataset. I also updated the code here to explicitly
close the connection to the database.
|
using System;
using
System.Collections.Generic;
using
System.ComponentModel;
using
System.Configuration;
using System.Data;
using System.Data.OleDb;
using
System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using
System.Windows.Forms;
namespace HelloWorld
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
String connectionString =
@"Provider=Microsoft.ACE.OLEDB.12.0;Data"
+ @" Source=C:\Users\patman\Desktop\Test_DB.accdb";
String tableName = "Employees";
String query = String.Format(
"select *
from [{0}]", tableName);
DataSet ds = new DataSet();
OleDbConnection conn =
new OleDbConnection(connectionString);
try
{
//Open Database
Connection
conn.Open();
OleDbDataAdapter da =
new OleDbDataAdapter(query, conn);
OleDbCommandBuilder cmdB =
new OleDbCommandBuilder(da);
da.MissingSchemaAction =
MissingSchemaAction.AddWithKey;
//Fill the DataSet
da.Fill(ds, tableName);
//-------- Update the first Row ---
ds.Tables["Employees"].Rows[0]["fname"] = "Steven";
ds.Tables["Employees"].Rows[0]["lname"] = "Bailey";
da.Update(ds, "Employees");
//-------- Update
the first Row ---
//Close the Database
Connection
conn.Close();
MessageBox.Show(" Hello World
"
+ ds.Tables["Employees"].Rows.Count
+ " "
+
ds.Tables["Employees"].Rows[0]["id"]
+ " "
+
ds.Tables["Employees"].Rows[0]["fname"]
+ " "
+
ds.Tables["Employees"].Rows[0]["lname"]);
}
catch (OleDbException exp)
{
MessageBox.Show("Database
Error:" + exp.Message.ToString());
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
}
}
}
|
|
|
|
|
The first record of my Database
contains Jefferey Jones, but If I run this program
I can see that the DataSet was
updated with the new fname and lname.
This also updated the database
If you look at this part you
can see the DataSet (ds) being updated with the new information. However this does not get applied to the
Database until you use da.Update(ds, "Employees")
For the update to have worked
you need to have a OleDbCommandBuilder applied to the OleDbDataAdapter as show
here.
Inserting into a Database (adding Rows)
|
DataRow row = ds.Tables["Employees"].NewRow();
row["fname"] = "Patrick";
row["lname"] = "Bailey";
ds.Tables["Employees"].Rows.Add(row);
da.Update(ds, "Employees");
|
|
|
|
|
Use this bit of Code to add a
row to the DataSet then update the DataSet back to the Database.
Deleting Rows from the Database
|
//------ Delete a row ----
ds.Tables["Employees"].Rows[0].Delete();
da.Update(ds, "Employees");
//------ Delete a row ----
|
|
|
|
|
Use this bit of Code to delete
a row from the DataSet then delete that row from the Database.
That concludes this tutorial on
Connecting to an Access DB, updating,
inserting, and deleting rows.
References
References
[1] How Do I Know if I’m Running
32-bit or 64-bit Office 2010?
Accessed
02/2013
[2] Microsoft Access Database
Engine 2010 Redistributable
Accessed
02/2013
there is also an article with demo app here
ReplyDeletehttp://geeksprogrammings.blogspot.com/2013/10/connect-access-database-with-c.html
Hi I'm using VS 2012 & Office 2013 64 bits, and i changed the target platform to x86, but I still got this weard error "The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine"..
ReplyDelete