csharpoledb3.htm

C# 使用OLEDB

			for the row. The Add function creates and returns the ListViewItem for the row 
			that in turn holds the subitem collection that holds the rest of the cells in 
			the row. Which means that the actual row is created by first of all adding a 
			ListViewItem to the view and then by adding the rest of the cells for each of 
			the columns to the ListViewItem.SubItem member.
		</p>
		<H3><U>Getting Data from the ListBox</U></H3>
		<P>Once the data is added to the list box it is required that we get it back out 
			again so that the database can be updated. The following code comes from the 
			OnDelete function.</P>
		<PRE>	ListView.SelectedListViewItemCollection col = listView1.SelectedItems;

	IEnumerator colEnum = col.GetEnumerator();

	/// move to the first and only
	colEnum.MoveNext();

	/// get the list view item
	ListViewItem item = ( ListViewItem )colEnum.Current;

	/// get the collection of subitems
	ListViewItem.ListViewSubItemCollection subItemsCol = item.SubItems;

	IEnumerator subEnum = subItemsCol.GetEnumerator();
	StringEnumerator stringEnum = stringCol.GetEnumerator();

	/// build sql code string removed

	bool bFirst = true;

	OleDbDataReader reader = dbAccess.GetReader;
	reader.Read();
	int nColumn = 0;
	string strType;

	while( subEnum.MoveNext() == true &amp;&amp; stringEnum.MoveNext() == true )
	{
		
		ListViewItem.ListViewSubItem subItem = ( ListViewItem.ListViewSubItem )subEnum.Current;
		strType = reader.GetDataTypeName( nColumn++ );
		
		/// build sql string code removed
	}
		</PRE>
		<P>The code starts by getting the selected items in the list view. In this code the 
			ListBox is set for single selection so that there will only ever be one. The 
			selected items are returned to a ListView.SelectedListViewItemCollection which 
			we are going to need to be able to traverse in some form or another. The way to 
			do this is to use the GetEnumerator function that from appearances comes with 
			all instances of a collection class. The GetEnumerator function is inherited 
			from the IEnumerable interface so as long as a collection inherits from this 
			interface the GetEnumerator function will be present.
		</P>
		<P>Whenever, the GetEnumerator function is called it returns an IEnumerator object 
			that is positioned before the start of the whatever type of object it is that 
			is being enumerated. This is why the IEnumerator.MoveNext function must be 
			called before anything can be done with the objects that are being enumerated. 
			The IEnumerator interface has only three functions these are MoveNext which 
			moves to the next object in the enumeration, Reset which sets the enumerator 
			back to just before the first object in the enumeration and Current which 
			returns the object being enumerated. The call to IEnumerator.Current must be 
			cast to the object of the type that is being enumerated.
		</P>
		<PRE>	ListViewItem item = ( ListViewItem )colEnum.Current;
		</PRE>
		<p>Once the ListViewItem has been extracted from the collection we can then get the 
			SubItems from the ListViewItem by getting the 
			ListViewItem.ListViewSubItemCollection from the ListViewItem.SubItem member. 
			Each SubItem in the collection is itself a ListViewSubItem which means that as 
			we cycle through the subitems getting each piece of cell data from the 
			ListViewSubItemCollection.Current member, the SQL statement can be built using 
			the StringEnumerator for the string collection that contains the column 
			headings for the ListBox and the value for each cell can be returned using the 
			ListViewSubItem.Text member variable.
		</p>
		<H2><U>The Edit Box Dialog</U></H2>
		<P>Insert and update commands are accessed through the EditBox Dialog this is a 
			simple box that allows the editing and inserting of a single line of code. The 
			EditBox is called using its constructor that takes four arguments the first is 
			the IWin32Window interface that is used as the parent of the dialogbox once 
			ShowDialog is called. The second parameter is the current OleDbDataReader, The 
			third is the name of the table that the update or insert command will be 
			carried out on and the final parameter is a bool value bInsert which indicates 
			if the EditBox is being used to insert or update a record in the table.</P>
		<H3><U>Displaying Data in a DataGrid</U></H3>
		<P>As with the ListBox the DataGrid is created at runtime, only this time the 
			information is taken directly from the OleDbDataReader that is passed to the 
			constructor as a parameter. In the case of the EditBox being used for the 
			inserting of a new record just the column information is taken from the reader. 
			But in the case of the EditBox being used to edit an existing record the 
			EditBox gets the row information from the reader as well.</P>
		<P>Because we are setting up the DataGrid on the fly we need to create a new 
			DataSet and a new DataTable. The DataSet is effectively a database object that 
			we are creating. It will work as a container that holds the information that 
			will be displayed in the DataGrid. Although in this example we are only using 
			the one DataTable we could include any number of DataTables within the DataSet.
		</P>
		<P>As with displaying data in the ListBox we start by building the set of columns 
			that are required for the table.</P>
		<PRE>	DataColumn dataCol = dataTable.Columns.Add( reader.GetName( i ), reader.GetFieldType( i ) );
	if( reader.GetSchemaTable() != null )
	{
		DataTable tempTable = reader.GetSchemaTable();

		DataColumnCollection colCollect = tempTable.Columns;

		IEnumerator colEnum = colCollect.GetEnumerator();

		colEnum.MoveNext();
		for( int count=0; count&lt;i; count++ )
		{
			colEnum.MoveNext();
		}

		DataColumn tempCol = ( DataColumn )colEnum.Current;

		if( tempCol.Unique == true )
		{
			dataCol.Unique = true;
		}
		/// this is technically incorrect AutoIncrement fields
		/// are being returned with this as false
		if( tempCol.AutoIncrement == true )
		{
			dataCol.ReadOnly = true;
			dataCol.AutoIncrement = true;
		}
	

		dataCol.MaxLength = tempCol.MaxLength;

	}
		</PRE>
		<P>First of all the Column is added to the DataTable object by calling the 
			DataTable.Columns.Add&nbsp;function on the DataTable. This creates the column 
			on the table and returns the DataColumn object so that we specify further 
			options on it. We then check that the reader will return a valid SchemaTable 
			when we call it as if&nbsp;the call to&nbsp;OleDbDataReader.GetSchemaTable 
			returns a null value then we can't to anything with it. Once we've asserted 
			that the Schema Table is valid we call the OleDbDataReader.GetSchemaTable 
			function again and store the result in a DataTable object. From the DataTable 
			object the columns can be extracted from the Columns member and stored into a 
			DataColumnCollection object. The DataColumnCollection is used in the same way 
			as the other collections in that an IEnumerator interace is made equal to the 
			return value of the IEnumerator.GetEnumerator function. The enumerator is then 
			moved through the columns until it is equal with the column number&nbsp;that we 
			require. We then test the column for various properties.
		</P>
		<P>It should be noted here that the original idea for this was that the test for 
			the DataColumn.AutoIncrement doesn't work the way I expected is that it is 
			always false even when the column is actually an autoincrement column. I 
			thought the way around this would be to check the value by which the 
			autoincrement column is updated. This didn't work either as if there is an 
			autoincrement value all columns contain the amount by which the autoincrement 
			column is updated. This is the reason why the demo project specifies that the 
			user manually indicates which fields are to be ignored for an edit or insert as 
			the code isn't able to correctly determine&nbsp;which fields&nbsp;if any should 
			automatically be disqualified from the sql statement.
		</P>
		<P>This gives us a set of columns but so far no data. The way to add data is 
			slightly odd in that it breaks with the get an enumerator and cycle through it 
			routine and returns to a more traditional, at least from a C++ perspective, 
			idea of get an array and fill out the values.</P>
		<PRE>	/// add only one row to the table
	DataRow dataRow = dataTable.NewRow();

	if( bInsert == false )
	{
		object[] itemArray = dataRow.ItemArray;

		for( int i=0; i&lt;nCount; i++ )
		{
			itemArray[ i ] = reader.GetValue( i ).ToString();
		}

		dataRow.ItemArray = itemArray;
	}
		</PRE>
		<p>The above code shows how the EditBox adds data into the row so that it can be 
			displayed in the DataGrid. The row is returned from the DataTable through the 
			DataTable.NewRow function which creates a new row in the table with the correct 
			or the same as the table schema.&nbsp;The information for the 
			row&nbsp;&nbsp;is&nbsp;set&nbsp;by getting the ItemArray from the DataTable and 
			cycling through the returned array filling each item with strings from the 
			reader. The final line makes sure that the DataRow.ItemArray holds the data 
			that we want in it.
		</p>
		<P>The saving of the data once it has been edited is the same no matter if there is 
			an edit or an insert going on and is exactly the reverse in that we drill down 
			to the ItemArray and then copy the data out of the array using the returned 
			values to build up an sql string.
		</P>
		<H2><U>Using The Database Editor</U></H2>
		<P>The Editor has one major flaw when it comes to editing all databases 
			and&nbsp;&nbsp;this is where the database itself, be it Access or any other 
			database maintains a relationship between two or more tables. One example of 
			this is the Microsoft sample database Nwind.mdb. If you open this database and 
			select the Suppliers table the editor will display the data as normal and as 
			long as you exclude the SupplierID field it is possible to edit and insert 
			fields into the table. The problem arises when a delete operation is attempted 
			this will throw an exception that says "The record cannot be deleted or changed 
			because the table 'Products' includes related records." The reason for this 
			error is that the database itself is maintaining the relationship rather than 
			it being a purely logical or relational relationship.</P>
		<P>Another area for improvement is the way in which the editor deals with types at 
			the moment only support for basic types is included and to be honest the sample 
			database uses strings only to represent the data which will mean that if the 
			Editor is used to access database tables which include different data types 
			then there will need to be some code modifications.
		</P>
		<H2><U>Conclusion</U></H2>
		<P>The GenericOLEDB should allow access to any access database using tthe code 
			provided and as it is a seperate dll it will be easy to use it to access other 
			databases. the may require a slight change to the connection string within the 
			GenericOLEDB dll but that should be the end of it. The remaining code shows how 
			to use the dll to deal with Access databases, although most of the provided 
			code should be reusable for any database type as the majority of the code is 
			dealling with the reader and is not concerned with database types.</P>
		<P>&nbsp;</P>
		<P>
			<A HREF="CSHARPOLEDACCESSDB.zip">Download the source code</A> 
		</P>
		<p>
			<a href="TESTBOOKS.ZIP">Download the sample Database</a>
		</p>
	</body>
</html>