If you have
not read yet the first parts, please found them here: Part
1 , Part
2 and Part
3
Now we have
the Access database on the C:\Book\BookData – and the dll to load data is on
the C:\Book\BookObjects.
We will
create a Windows Forms project to see the data that is in the MDB file.
Right click
on the solution -and choose: Add => New Project => and choose Windows
Application. Put the name BookWin. A new form is created for you by
default.
Change the name from Form1 to frmPublisherList.cs, double
click on the file, press F4 to bring Properties window up front and change the
Text from Form1 to List of Publishers.
Now we must tell to the windows project to use the objects
project that we created earlier. Right click BookWin project in the Solution
Explorer, click „Add reference” and go to the Projects tab on the next window.
Double Click the BookObjects project.Compile(CTRL+SHIFT+B).
I must
found a way to copy the MDB in the same location as the executable, in order
that the path can function easily even the application will be deployed in
other paths. (Not every user will easily agree to deploy the application on the
same path as we do, C:\Book – maybe he wants to install in other places).
The
solution to be always in sync is to perform the copy automatically and
precisely when the build is complete.
Right click
on BookWin project from Solution Explorer, select properties (or find in menu
Project => Book Project Properties)
On the
“build events“tab you have a pre and a post build event command line
We will
wrote this code in the post build
copy
$(ProjectDir)..\BookData\*.mdb $(TargetDir)
That means
copy all MDB files from the following dir C:\Book\BookData (obtained as
following from the project dir (C:\Book\BookObjects\), the up (C:\Book\), the
to BookData (C:\Book\BookData)) to TargetDir (does no matter if debug or release)
Save and compile
(CTRL + SHIFT + B)
Now in the
C:\Book\BookObjects\bin\Release or in the C:\Book\BookObjects\bin\Debug must be
another copy of the MDB file.
Very good *
let’s write now the connection to the MDB. Right Click on BookWin in the Solution
Explorer – click Add => New Item => and choose “Application Configuration
File” ( default name :App.config – do not change this!) and put the following
lines:
<?xml version=“1.0“ encoding=“utf-8“ ?>
<configuration>
<appSettings>
<add
key=“DatabaseUsed“ value=“MDB“/>
<!– possible values : MDB, SQLServer–>
</appSettings>
<connectionStrings>
<add
name=“MDB“ connectionString=“Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=|DataDirectory|\book.mdb;User Id=admin;Password=;“/>
<!– TODO : add for asp.net application the connection string with SQL Server–>
</connectionStrings>
</configuration>
Now it’s
the moment to load the records. We put code in the settings.cs file to be able
to switch connection at run-time:
public static DbConnection TheConnection
{
get
{
switch
(TheDatabase)
{
case
DatabaseUsed.MDB:
OleDbConnection oc = new OleDbConnection(ConnectionStringMDB);
return oc;
case
DatabaseUsed.SQLServer:
SqlConnection sc=new SqlConnection(ConnectionStringSQLServer);
return sc;
default:
// Maybe throw an error that config file has not been initialized with
// the database type ?
return null;
}
}
}
And to load Records:
public static IDataReader
Load(string CommandLine, DbConnection dbcon)
{
if
(!(dbcon.State == ConnectionState.Open))
dbcon.Open();
DbCommand
dc = null;
switch
(TheDatabase)
{
case
DatabaseUsed.MDB:
dc = new OleDbCommand(CommandLine);
break;
case
DatabaseUsed.SQLServer:
dc = new SqlCommand(CommandLine);
break;
default:
//TODO
: throw specific error that database type does not properly have been initialized
break;
}
dc.CommandType = CommandType.Text;
dc.Connection = dbcon;
return
dc.ExecuteReader();
return
null;
}
As you see,
we return a DbConnection no matter we use the OleDbConnection
or SqlConnection .
Now let’s fill
in the Load code in the ColPublisher file:
using System;
using
System.Collections.Generic;
using
System.Text;
using
System.Data;
using
System.Data.Common;
namespace
BookObjects
{
public class ColPublisher :
System.Collections.ObjectModel.KeyedCollection<string,Publisher>
{
protected
override string
GetKeyForItem(Publisher item)
{
return
“K” + item.IDPublisher;
}
public void Load()
{
DbConnection
db = Settings.TheConnection;
using
(db)
{
db.Open();
IDataReader
ir = Settings.Load(“select
IDPublisher, NamePublisher, SitePublisher from Publisher”, db);
while
(ir.Read())
{
Publisher
p = new Publisher();
p.FillObject(ir);
//TODO : add p into the
collection
}
}
}
}
}
We realize
now that the ColPublisher is not a collection where to add the new
publisher p. Fortunately, the .NET collection is enough big to contain many
collection.
Just look
into the System.Collections namespace, then into System.Collections.ObjectModel and into System.Collections.Specialized. You will find many more on the net
( for example, an implementation of a Set collection : http://www.codeproject.com/csharp/sets.asp)
We will
derive the ColPublisher from the System.Collections.ObjectModel.KeyedCollection<string,Publisher> .We must wrote how to generate the key for a specific Publisher -and what Key is better than the ID ?
protected override string
GetKeyForItem(Publisher item)
{
return
“K” + item.IDPublisher;
}
Now we can
wrote the Load method
properly :
using System;
using
System.Collections.Generic;
using
System.Text;
using
System.Data;
using
System.Data.Common;
namespace
BookObjects
{
public class ColPublisher :
System.Collections.ObjectModel.KeyedCollection<string,Publisher>
{
protected
override string
GetKeyForItem(Publisher item)
{
return
“K” + item.IDPublisher;
}
public void Load()
{
DbConnection
db = Settings.TheConnection;
using
(db)
{
db.Open();
IDataReader
ir = Settings.Load(“select
IDPublisher, NamePublisher, SitePublisher from Publisher”, db);
while
(ir.Read())
{
Publisher
p = new Publisher();
p.FillObject(ir);
this.Add(p);
}
}
}
}
}
And use it
from the Form :
Double
click on the frmPublisherList.cs and drag a DataGridView to the form
Now configure
the data source:
Click on “(none)”
and select “add new data source”
In the next
dialog, choose “Object”
and press
next.
Now expand the
BookObjects node and choose “ColPublisher”. Press Next and then Finish.
To the form
is a new control added: colPublisherBindingSource – and the Grid has already the columns defined.
Now wrote
the data to load the data from the database:
Double click
on form, and you will find yourself in the Form_Load event :
private void frmPublisherList_Load(object
sender, EventArgs e)
{
BookObjects.ColPublisher publishers = new
BookObjects.ColPublisher();
publishers.Load();
colPublisherBindingSource.DataSource = publishers;
}
Now the
results – right click on BookWin – and select “Set as startup project”
Now press F5
* and wait to see the results. If the best case, you will see a form with no
data at all – and this is very correct – because of the fact that are no items
in the Book.mdb
In the next
lesson we will wrote code to do insert of a new Publisher and viewing him on
the form ( finally!)
Recommended
readings:
CSLA : http://www.lhotka.net/cslanet/ – a good framework to handle all the security,
scalability , binding and other issues ( and, most of all, free).