First we introduce several class types, before we introduce the object themselves: DataView, Connection, Command, DataAdapter, DataReader, and DataSet. These objects provide an abstraction between the database engine and the the data that you're trying to work with. For the examples that I go through I will use the OLE version of these objects (OleDbConnection, OleDbCommand, OleDbDataAdapter, and OleDbDataReader [the DataView and DataSet classes are generic enough to not need specific versions for the a given database interface and do not interact with a database engine directly anyway]), however other versions exist for working with ODBC connected databases, MS Sql Server, and Oracle. To use these other versions simply replace the "OleDb" with "Odbc", "Sql", and "Oracle" respectively.

So that's all nice and everything, but how do I get my data out you may ask. Well there are many ways to use these objects to get your data. We will start with the most basic and easiest way that doesn't take advantage of any of the prebuilt features in most of the .NET components, nor the tools in Visual Studio. Then after you get the hang of that we'll let you glue back on the hair you pulled out and simplify your data access life and use those preexisting tools.

The Simple Hard Way:

As you might expect from the names of the classes the connection class connects us to the database, then the command runs the sql statement on that connection, and finally the datareader takes the result of the command and lets you work with the data.

The connection string is a special string that defines certian properties on how to connect to the database. Important things like what drivers to use, the username and password to get into the database, and most importantly where the database is located. The intricies of the connection string are far beyond the scope of this tutorial however so you're not completely lost in the hopeless sea of ambiguity, for an MS Access database the basic format is:

"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + fileName

...where fileName is a string containing the complete path to your MS Access database. You may notice here that there is no username or password, well that is a good observation...horray for you! (By default MS Access files have only one user: admin and no password for that account. Although you can add a password, other users, and restrict those users...most developers using MS Access don't and thus leave there database files open like giant bullseye targets for hackers).

The command class is also fairly simple in this example, although using some of the command's other properities and associated classes is VERY useful in many cases (i.e. preventing sql injection attacks, etc). We simply provide it with the connection and the sql statement and... viola...out pops a data reader object. The data reader, like most of the .NET classes, has more than one way to use it. Here I simply use it like an array where instead of an index number, I give it an index name. Here you have to be careful about types because it generally just returns a string. You may have to use the Convert object to turn the string into any of the basic types in C#. The read function advances the internal data to the next row and returns true if there is another row, or false if it is at the end of the data.

I call this the simple hard way, but in reality it is the easiest way to get data into a custom class that you've created for whatever purpose, but there are much better alternatives if you simply want to put the data in a dropdownlist, or datagrid, or almost any other object.

Last Updated: 15 April 2005 9:54 AM
Up | Tutorial Main | Home | email author