OLE DB Consumer using basic C#

• Download source files - 34 Kb
• Download Northwind database - 603 Kb

Introduction

 This is the second in a series of articles about using the OLE DB Consumers. I wrote this one using C# because I wanted to,

1. familiarise myself more with C# and
2. I wanted to see what the differences between using C# and C++ would be.

I suspect from writing this one that I will be pleasantly surprised due to the fact that although C# comes across as a simple language, in the background it is actually quite sophisticated. The reason for this being that C# directly accesses all the com objects that Microsoft has been developing for years, indeed a few of the relatively small set of classes I have encountered so far inherit directly from com interfaces which leads me to believe that if the .NET framework is not directly based on COM then it certainly has it's roots firmly embedded in COM principles and techniques.

This code was developed under windows 2000 using Developer Studio 7 beta 2. 

In a previous article OLE DB Consumer Basic C++ I described setting up an ODBC driver and adding a OLE DB Consumer class to the project. I refer you to that if you are unsure oif anything and will state that the only difference in version 7 is that you click on the toolbar and select the Data section. Then drag and drop an OLE DB Consumer Connection and an OLE DB Command on to you're project dialog, this will add

private System.Data.OleDb.OleDbCommand oleDbCommand1; 
private System.Data.OleDb.OleDbConnection oleDbConnection1;

to your C# class. A panel will appear at the bottom of your dialog and the two OLE DB Components will be placed on the panel. In order to set up the connection to the database click on the OLE DB Connection and go to the properties panel and click on the connection string. This will give you the setup dialog that is described in the OLE DB Consumer Basic C++ article.

The Code

Because the code is so short I'll print out the main code here,

oleDbConnection1.Open();

oleDbCommand1 = oleDbConnection1.CreateCommand();
oleDbCommand1.CommandText = "SELECT * FROM Suppliers";
OleDbDataReader oleReader = oleDbCommand1.ExecuteReader();

/// fill the list box
ListViewItem lvItem;

object[] objArray = new object[ 12 ]; 
int nRow = 1; 
int nColumn = 0;
	
while( oleReader.Read() == true )
{ 
	nColumn = 0; 
	oleReader.GetValues( objArray );
	lvItem = listView1.Items.Add( objArray.GetValue( nColumn++ ).ToString() ); 
	for( ; nColumn < 12;  nColumn++ )
	{ 
		lvItem.SubItems.Add( oleReader.GetValue( nColumn ).ToString() );
	} 
} 

This is code for the application apart from the try catch block that it is wrapped in the cs file. The Command object calls open and internally uses the string that you set up when you set up the connection string.

oleDbCommand1.CommandText = "SELECT * FROM Suppliers";
OleDbDataReader oleReader = oleDbCommand1.ExecuteReader();

The command to open the database is set in the CommandText member of the

OleDbCommand

which can be thought of as a Dynamic Accessor when using the C++ template version of OLE DB Consumers in that it will open a Rowset and bind the members dynamically instead of being passed a specific set of parameters to bind to.

The OleDbReader is initialised by calling the OleDbCommand::ExecuteReader function that returns a OleDbDataReader item that by definition has read only access to the database. The information is then put into the list by getting the values from the OleDbDataReader and then calling object::ToString on the returned value.

lvItem.SubItems.Add( oleReader.GetValue( nColumn ).ToString() )

If the type of the object returned from the call to OleDbDataReader::GetValue is unknown for any reason you can call OleDbDataReader::GetFieldType which will return the type of the object allowing you to call one of the more specific get functions included in the OleDbDataReader class.

Although I didn't write this in order to praise C#, The code is obviously shorter and easier to read than the C++ version but at the moment only because the C# implementation hides the details of the OLE DB implementation. But the question remains will C# be able to handle the more complex code that OLE DB Consumers require.

Running The code.

Due to the fact that the code is using the Microsoft provided nwind database you will have to set the code up to point to the database as described above and recompile it.