C# Example
Using Access Database
This exploratory project shows two methods of accessing a
Microsoft Access database. One is from
a newer example from Microsoft, as modified, and one converted from an old, old
Visual Basic example.
They each use a version of the canned Microsoft Northwind
database.
The much older database is Nwind.mdb using the OleDb class
while the second is the Northwnd.mdf database using different access
methods. (Notice that the 'i' was left
out in Microsoft's naming of this database.)
Both databases are accessed from the Microsoft Visual C#
2010 Express that I got from Microsoft years ago and use whatever a recent
version of Microsoft Visual Studio that might be necessary (if anything) to
interface with the Windows 7 of my PC.
In each case the SQL query is a simple one to read the
Customers table of the particular version of the database to return those with
an address in London. The old Visual
Basic code that had a fancier query although I don't know as I ever used it
since the project got reworked to update a different non-Northwind database to
maintain a database of ARINC-661 widgets for a research project for a demo for
an Airbus aircraft.
Besides forming the OleDb query after all this time another
problem arose. I initially bypassed the
OleDb query and copied the code from the Northwnd.mdf example from an internet
search. It had been for a console
application whereas I did a Windows Forms Application. So the example put the query results to a
Console log but I wanted to see them in the Windows form widget of my
project. So I would get the next query
result and copy the text to a textbox widget and when nothing appeared, to a
label widget.
In neither case did anything appear in the textbox or the
label although with the first output to the label's text field the original
text would be shortened to fewer characters.
This was a puzzlement since I had had no trouble with other C#
applications displaying values to such widgets/controls. (Note: Widgets is A661 terminology whereas
Microsoft refers to a control.)
So to get the value displayed I added a MessageBox where I
was going to display the query result – that is, Customer ID and customer
City. But I found all I had to do was
request the MessageBox to display some text and the query results appeared in
the labels and text boxes. So again a
head scratcher.
I later found out that to get the screen (i.e., the form)
updated a second thread was needed (which the activation of the MessageBox was
supplying) or else the use of a Refresh statement for the control – in this
case the form. So I took out the use of
the MessageBox and used Refresh instead.
Although the MessageBox had been helpful since the user got to see the
query result displayed for each customer that matched the requested city.
It then occurred to me that the reason I hadn't had such a
problem before was because I was displaying data or changing the color of
buttons and the like from inside a different event handler. The event handler, of course, runs in a Windows
thread transferring control to the handler when a control is clicked or
characters entered into a text box. In
the current application, everything is being done within the same event handler
so the form wasn't being redisplayed since the event handler isn't being
exited. Therefore, no other event can
be treated.
Without the MessageBox, the query loop runs to completion so
only the last result can be seen unless the loop is stepped thru via the
debugger. (In the Microsoft Northwnd
example, all the results were to be seen in the Console log that it
produced.) Therefore, I've added a one
second delay to allow the user to briefly see the query results as the query
proceeds.
This, of course, is only useful for the example database
with a small number of matches to be found and displayed. For a more normal application, a report
would be produced and if visual results were wanted, a special control could be
used to allow the initial results to be inspected and then continue and produce
the report. Perhaps a text box in which
to enter the number of results to display before dispensing with the
delay. Perhaps, two such boxes with one
for the delay period desired. Both to default
to just produce the report.
Another problem that occurred was that the Microsoft
instructions for using the Northwnd database had the inclusion of the
System.Data.Linq.Mapping class.
However, when filling this into my new C# project, the auto selection
resulted in my using System.Linq and then attempting to add System.Linq.Mapping. This stymied me for quite some time
attempting to get this unit added to my project when it didn't exist. Finally the light dawned and I used the
correct class.
The C# code is as follows without showing the Initialize
code within the Form1.Designer.cs file.
Program.cs
using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Windows.Forms;
namespace WindowsFormsApplication1
{
[Table(Name = "Customers")]
public
class Customer
{
/*In
this step, you accomplish several tasks.
· You
use the ColumnAttribute attribute to designate CustomerID
and City properties on the entity class as
representing columns
in
the database table.
· You
designate the CustomerID property as representing a primary key
column in the database.
· You
designate _CustomerID and _City fields for private storage.
LINQ to SQL can then store and retrieve values directly, instead
of
using public accessors that might include business logic.
To
represent characteristics of two database columns */
private string _CustomerID;
[Column(IsPrimaryKey = true, Storage = "_CustomerID")]
public string CustomerID
{
get
{
return this._CustomerID;
}
set
{
this._CustomerID = value;
}
}
private string _City;
[Column(Storage = "_City")]
public string City
{
get
{
return this._City;
}
set
{
this._City = value;
}
}
}
static
class Program
{
///
<summary>
///
The main entry point for the application.
///
</summary>
[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new Form1());
}
}
}
Form1.cs
using
System;
using
System.Collections.Generic;
using
System.ComponentModel;
using
System.Data;
using
System.Data.OleDb;
using
System.Data.Linq;
using
System.Data.Linq.Mapping;
using
System.Drawing;
using
System.Linq;
using
System.Text;
using
System.Windows.Forms;
namespace
WindowsFormsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object
sender, EventArgs e)
{ // Start button
/* This initial code queries the
Nwind.mdb database of Microsoft
* using OleDb methodology.
*/
string con = "Provider =
Microsoft.Jet.OLEDB.4.0; Data Source = C:\\Source\\VB\\Nwind.mdb";
OleDbConnection connection = new
OleDbConnection(con);
string queryString = "SELECT CustomerID, City FROM
Customers";
queryString += " WHERE City =
'London'";
OleDbCommand command = new
OleDbCommand(queryString,connection);
command.CommandTimeout = 20;
connection.Open();
OleDbDataReader reader =
command.ExecuteReader();
string valueRead;
while (reader.Read())
{
valueRead =
reader[0].ToString();
this.label3.Text = valueRead;
valueRead = reader[1].ToString();
this.label4.Text = valueRead;
this.Refresh();
System.Threading.Thread.Sleep(1000);
}
// reader.Close();
/* This code queries the northwnd.mdf
database of Microsoft.
* A portion of code is in the
Customer class in Program.cs.
*/
// Use a connection string.
DataContext db = new DataContext
(@"c:\linqtest5\northwnd.mdf");
// Get a typed table to run
queries - the Customer class.
Table<Customer> Customers =
db.GetTable<Customer>();
// Attach the log to show
generated SQL.
db.Log = Console.Out;
// Query for customers in London.
IQueryable<Customer>
custQuery =
from cust in Customers
where cust.City ==
"London"
select cust;
foreach (Customer cust in
custQuery)
{
string val1 = cust.CustomerID;
this.textBox1.Text = val1;
this.label3.Text =
cust.CustomerID;
val1 = cust.City;
this.textBox2.Text = val1;
this.label4.Text = cust.City;
this.Refresh();
System.Threading.Thread.Sleep(1000);
}
}
}
}
Note: The query results could have been directly copied to
the label and text box controls. The
use of the extra string values was when I was trying nonsense trying to get the
values to display.
No comments:
Post a Comment