📄 csharpoledb3.htm
字号:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title></title>
</head>
<body>
<H1 align="center"><U>Access Database Editor in C#</U></H1>
<P>This project presents an Access Database Editor written in C#. The solution
consists of two projects One of which the GenericOLEDB dll provides the access
to the database through using the Ole Db classes available in C#. As the title
suggests the dll code is completely generic and all the main database code that
is specific to Access is contained in the project files that call the code. The
main project and the consumer of the GenericOLEDB project contains two files
the main file takes care of the specific creation and opening of the database
as well as the database operations where there is no further information
required, and the second file presents and editor that allows the editing of a
row of data for insertions or deletions.</P>
<P align="left">This article will have the following format. At first the details
of the code will be presented, starting with the GenericOLEDB project and the
OLE DB code and then move on to the control code for the demo touching on how
the grid and the list are used and how to access the data within them. The
final section will take a look at the abilities and the limitations of the
program.</P>
<P align="left">This project was developed using Developer Studio.NET beta2 and
Developer Studio.NET Professional on win2000 and XP Home </P>
<H2 align="left"><U>The GenericOLEDB Project</U></H2>
<P align="left">The GenericOLEDB project is a dynamic link library that contains a
single C# class. This class is a wrapper class that contains the OLE DB
functionality for the program. The aim of the class is to generically provide a
reusable database interface that can be used whatever database technology
is required. For this reason the class is created by hand and not generated
through the Developer Studio Seven Wizards.</P>
<P align="left">The class is written to follow the same development format as
the wizard would generate if it was used. This means that the access technique
is to use a OleDbDataAdapter as the main bridge to the database
and four OleDbCommand objects that all use the same OleDbConnection
object to talk to the database
</P>
<P align="left"><IMG src="3c5eed29.jpg"></P>
<P></P>
<P>This follows the same set up as the wizard generated code in that the adapter
has a command variable for the update, insert, delete and select commands,
these are set in the code through,</P>
<Pre>
oleDbDataAdapter.DeleteCommand = oleDbDeleteCommand;
oleDbDataAdapter.InsertCommand = oleDbInsertCommand;
oleDbDataAdapter.SelectCommand = oleDbSelectCommand;
oleDbDataAdapter.UpdateCommand = oleDbUpdateCommand;
</Pre>
<P>Once the commands are set on the OleDbDataAdapter the Connection is set on the
commands.
</P>
<pre>
oleDbSelectCommand.Connection = oleDbConnection;
oleDbDeleteCommand.Connection = oleDbConnection;
oleDbUpdateCommand.Connection = oleDbConnection;
oleDbInsertCommand.Connection = oleDbConnection;
</pre>
<p>The main code within the GenericOLEDB dll is the ExecuteCommand function which
executes the currently selected command. The command is selected within the dll
by keeping track of the last set command string which it takes as being the
command that is required to be executed. If the command to be executed is a
select command the function simply returns true as the main part of the command
will be executed when the GetReader function is called which calls the
ExecuteReader function on the select command regardless of which operation
is called.
</p>
<H3><U>Transactions</U></H3>
<P>This brings us to the part of the ExecuteCommand function that requires the
database to be directly updated. This is required for the insert, delete and
the update commands. A transaction is simply a way of locking off a section of
the database so that a write or delete operation can be performed without
corrupting the database integrity. Anything trying to access the section of the
database that is covered by the transaction will be blocked until the
transaction is finished. A transaction is finished in one of two ways, either
by performing a commit which writes the changes to the database or a rollback
which resets the database back into the state it was in before the transaction
started.
</P>
<P>Transactions in C# work in a top down manner first you must get the transaction
from the OleDbConnection which is the top level</P>
<pre> /// get the transaction object from the connection
OleDbTransaction trans = oleDbConnection.BeginTransaction();
</pre>
<p>
This is then the parent transaction that allows us to create a child
transaction for the specific command. This is done by calling the
OleDbTransaction begin function. In this case we'll use the DeleteCommand.
</p>
<pre> case COMMAND.DELETE: oleDbDeleteCommand.Transaction = trans.Begin(); break;
</pre>
<p>As you can see from the above each OleDbCommand Object contains a Transaction
member that must be connected to the same OleDbConnection as the OleDbCommand
Object. If this is not the case an exception will be thrown and database
operations will not work as expected.
</p>
<P>Once the command is executed using the OleDbCommand::ExecuteNonQuery function
which is used to execute commands that do not return a set of records. If the
ExecuteNonQuery function succeeds the transaction can be committed to the
database.
</P>
<pre> case COMMAND.DELETE: oleDbDeleteCommand.Transaction.Commit(); break;
</pre>
<p>If however the ExecuteNonQuery function fails then the transaction will
need to be rolled back to its previous state.
</p>
<pre> case COMMAND.DELETE: oleDbDeleteCommand.Transaction.Rollback(); break;
</pre>
<p>The success or failure of the ExecuteNonQuery function is checked by the value
that it returns. A positive value indicates that it has carried out the
operation on the database successfully, while a negative value or and exception
indicates a failure to carry out the operation on the database. Once the child
transaction has committed or rolled back the operation on the database the same
action must be carried out on the parent or the OleDbConnection object.
</p>
<H2><U>The CSHARPOLEDB3 Project</U></H2>
<P><IMG alt="" src="3c6188f8.jpg"></P>
<P>The main file for the project is the Form1.cs file that controls the data entry
and the responses and binds the project to Access type databases. The original
values for the access database are hard coded in the constructor for the form
class
</P>
<pre> textBox1.Text = "Microsoft.Jet.OLEDB.4.0";
textBox2.Text = "Admin"; textBox3.Text = "";
Mode = "ReadWrite";
</pre>
<H3><U>OpenFileDialog</U></H3>
<P>These are the default settings for an access database with only the path to the
database file needing to be added through the browse button, that initialises
an OpenFileDialog that is entered into the project from the toolbox and then
initialised in the code.
</P>
<pre> openFileDialog1.InitialDirectory = Directory.GetCurrentDirectory();
openFileDialog1.Filter = "Access Files ( *.mdb )| *.mdb";
openFileDialog1.FilterIndex = 1;
if( openFileDialog1.ShowDialog() == DialogResult.OK )
{
DatabaseName = openFileDialog1.FileName;
textBox4.Text = DatabaseName;
}
</pre>
<p>As you can see there are operations to be performed before using the
OpenFileDialog. The InitialDirectory member is set to the current directory by
using the static Directory.GetCurrentDirectory function. Then the filter is set
to access type files. This is done through the use of a string that is divided
into two parts by the "|". The first part is the desription of the file that is
to be opened and the second part is the string that is used to look through the
directory files, in this case "*.mdb". Because an OpenFileDialog does not
actually open the file it can point to as many different file types as are
required, which is why the FilterIndex property needs to be set to the required
filter before the dialog is displayed.
</p>
<P>Once this is done the dialog can be displayed and it controls all the necassary
path finding to the database that will be selected. As long as the user finds
the file and presses the OK button everything will be fine as the
OpenFileDialog will return a value of DialogResult.OK. All that is then left to
do is store the OpenFileDialog FileName property which will return the full
path and name of the file.
</P>
<P>The Select button then brings up a dialog that has two edit fields the first is
the fields that you want to select from the table. This is set by default to
"*" which will return all the fields from the selected table. The second edit
field is the space to type in the name of the required table. This is a normal
edit field and the user must know the name of the one of the database tables in
the field. This is a requirement of the program although one idea for an
improvement would be to get the data from the database parameters and display a
list of the tables that are in the database at this point.
</P>
<P>When the right database has been selected the "Open Database " button is pressed
which creates an gets the parameters from the appropriate boxes before creating
a GenericOLEDB object and calling open with the parameters.</P>
<pre> dbAccess = new GenericOLEDBClass();
dbAccess.Open( Provider, UserID, Password, DatabaseName, Mode );
</pre>
<p>The Select Command is then set up using the table that was entered in to the
select dialog. The Command is the executed by calling
dbAccess.ExecuteCommand(), Although as this is the first command that is a
generated the function will just return true at this point. The select function
isn't called until the dbAccess.ExecuteReader() function is called in the
DisplayList function is called with the dbAccess.Reader variable used as a
parameter.
</p>
<H3><U>Displaying Data in the ListBox</U></H3>
<P>The DisplayList function does exactly what it says on the box, it clears the
listbox of any current data and then sets up a number of columns depending on
the field count that is returned by the reader.</P>
<pre> /// build the headers first<br>
for( int i=0; i<nCount; i++ )
{<br>
ColumnHeader header = new ColumnHeader();<br>
header.Width = 100;<br>
header.Text = reader.GetName( i );<br>
listView1.Columns.Add( header );<br>
/// Store the header names in a collection<br>
stringCol.Add( reader.GetName( i ) );<br>
} <br>
</pre>
<P>When the columns ave been created all that remains for the code to do is add the
data to the list.
</P>
<PRE> /// now add the data
ListViewItem lvItem = new ListViewItem();
while( reader.Read() == true )
{
lvItem = listView1.Items.Add( reader.GetValue( 0 ).ToString() );
for( int i=1; i<nCount; i++ )
{
lvItem.SubItems.Add( reader.GetValue( i ).ToString() );
}
}
</PRE>
<P>The data is entered into the table by creating a new ListViewItem which as the
name suggests controls the data and the display properties for an item that is
displayed within the Listbox. As you can see from the above code though the
actual use of a ListViewItem is a little more complicated in that the
ListViewItem for a row should be thought of as being the first cell of the row,
as well as being the container from which all the other cells of the row are
accessible. This is done through the SubItems member of the ListViewItem class
that is of the type
</P>
<PRE> ListViewItem.ListViewSubItemCollection
</PRE>
<p>We'll look at getting the data back out of a list view next but first we have to
know how to put it into the list. The Initial value in the first cell of the
list is added by calling ListView1.Items.Add with the required parameters. The
ListViewItem.Item member is a ListViewItemCollection that holds all the cells
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -