4
Components and Database Handling
Exception Handling
Failing to prepare is preparing to fail. Ergo, one is
constantly planning and preparing for the future. We often set a path for
ourselves and try to follow it steadily. But then life intervenes. Uncertainties
in life result in unforeseen situations. These are like exceptions in the
normal course that we set for ourselves. Similarly, when you write programs,
unforeseen problems may arise during its normal path of execution.
These unforeseen problems are nothing but an euphemism for
errors. Just as in life, in the programming world, these errors can be further
classified into Fatal errors and Non-Fatal errors. A Fatal error is an error
that brings the program to a grinding halt. A Non-Fatal error is an error that
allows your program to run but with limited capacity. This can be exemplified
by the following.
Let's assume you have a card that is not of a high
resolution. Accordingly, your browser displays your page in a lower resolution.
Now, technically, that is an error but it is not a Fatal one. However, if you
didn't have a graphics card at all then it would be a Fatal error. Thus, we may
also call an unforeseen problem or error an Exception. In other words,
therefore, the word Exception is used almost synonymously with the word Error.
Earlier, the problem was that we never centralized error
handling. Let's assume you have to open three files. Each time you open a file
you have to check whether an error occurred or not. So you have to conduct that
check for every file. Since there are three files, it would mean repeating the
same error check thrice. That is surely a waste of time. Or you could be
calling two functions and checking for the same error in both the functions.
One reason that programmers don't write error-handling routines is that they
get tired of the mundane task. It is the same thing repeated over and over
again.
Let's consider constructors. Before the constructor gets
called, the object has not yet been created. So you ask the constructor to
create an object, to allocate memory and create a file. Now, if it can't do so,
how will the constructor return to tell you that an error occurred! Today
constructors carry a lot of code within them and if you haven't forgotten,
constructors cannot return values.
Because of the various reasons discussed above, we don't
talk about errors any more; we handle exceptions. Bearing this in mind let's
understand the next program.
a.cs
class zzz
{
public static void Main()
{
yyy a;
a=new yyy();
a.abc();
System.Console.WriteLine("Bye");
}
}
class yyy
{
public void abc()
{
throw new System.Exception();
System.Console.WriteLine("abc");
}
}
Compiler Warning
a.cs(16,1): warning CS0162: Unreachable code detected
Output
Unhandled Exception: System.Exception: Exception of type
System.Exception was thrown
at yyy.abc()
at zzz.Main()
Here, a.abc calls the function abc in class yyy.
System.Console.WriteLine is used to display 'Bye'. It is of significance to
note that when you run this program the System.Console.WriteLine does not get
called. Hence the word 'Bye' is not displayed. Within the abc function we have
a line that says throw new System.Exception(); The word new indicates that we
are creating an object. We are creating an object that looks like System.Exception.
'throw' is a reserved word, that means it is recognized by C#. Exception is a
class in the System namespace. In other words, we are identifying an exception,
creating an object of it, and throwing it. Then we have a WriteLine statement
for printing 'abc'. Note that neither 'Bye' nor 'abc' gets displayed. A Message
Box may appear for debugging the applicaition. Since we are still at the
learning stage, we click on the No button.
The warning says that when you use the 'throw' keyword in
your code, no lines of code get called after that. Since the function abc is
throwing an exception no code after the throw in abc will get executed. The
throw acts like the return statement. Everything comes to a stand still! And we
get an error at runtime and not at the time of running the compiler; indicating
where the exception occurred. Also no code gets called after function abc gets
called as it throws an exception.
Explicitly declaring exceptions tells the compiler that a
particular problem might occur. When the problem does occur, an exception is
thrown; the next step being to catch the exception. Let's see how we can
accomplish this. In our program, the function abc throws an exception. We will
now catch the exception.
a.cs
class zzz
{
public static void Main()
{
yyy a;
a=new yyy();
try
{
a.abc();
System.Console.WriteLine("Bye");
}
catch (System.Exception e)
{
System.Console.WriteLine("In Exception"+
e.ToString());
}
System.Console.WriteLine("After Exception");
}
}
class yyy {
public void abc()
{
throw new System.Exception();
System.Console.WriteLine("abc");
}
}
Output
In ExceptionSystem.Exception: Exception of type
System.Exception was thrown.
at yyy.abc()
at zzz.Main()
After Exception
Catching exceptions is done within 'try-catch' blocks.
Therefore, the code for abc is included within a 'try-catch' block. a.abc - the
function that throws the exception - is included within the try-catch block .
The abc function throws an exception by using the keyword throw. There is no
other way of throwing an exception. At this point all code is skipped in
function abc as well as the in the try block and the control moves to the catch
block. As such, neither 'abc' nor 'bye' gets displayed.
Within the catch we have a parameter 'e' that looks like
System.Exception. The object e has a method called ToString. ToString is a very
handy function. It tells you where exactly the exception occurred and in which
function, function within function. So, System.Console.Writeline will display
the string 'In Exception' along with the exception.
After the code contained in the catch block is executed, the
remaining code after the end of the try - catch block will be executed. Hence,
WriteLine will display 'After Exception'. That means the program will not come
to a stand still, it resumes execution after the catch and not after the
function which threw the exception. If you give a return statement immediately
after the catch block, as we have given in the next program, the program will
stop execution there itself. Hence, in this case, 'After Exception' will not be
displayed as shown below.
a.cs
class zzz
{
public static void Main()
{
yyy a;
a=new yyy();
try
{
a.abc();
System.Console.WriteLine("Bye");
}
catch (System.Exception e)
{
System.Console.WriteLine("In Exception"+
e.ToString());
return;
}
System.Console.WriteLine("After Exception");
}
}
class yyy
{
public void abc()
{
throw new System.Exception();
System.Console.WriteLine("abc");
}
}
Output
In ExceptionSystem.Exception: Exception of type
System.Exception was thrown.
at yyy.abc()
at zzz.Main()
Each time that abc gets called an exception is thrown. But
you may not want that to happen. Hence, exception handling is normally included
in an if statement and if an error condition takes place.
A 'try-catch' block can include a number of functions and
whenever an exception occurs for any one of them, we will catch it. By doing so
we are synchronizing all the code to handle errors at one place. Constructors can also throw exceptions.
Building Components
a.cs
public class zzz {
public void abc() {
System.Console.WriteLine("zzz abc");
}
}
The above program consists of a simple class zzz with one
public function. The class has also been tagged with the modifier public that
makes it accessible to everyone. When we run the command csc a.cs, we are
asking the C# compiler to create an executable file for us even though we did
not explicitly ask it to. An executable file will always be created by the C#
compiler unless you override it by stating an option to the C# compiler. Let us start with the /t option.
csc /t:library a.cs
You could use /t or /target. This option specifies what type
of output file the compiler should create for you. If you do not specify a /t
option on the command line, C# by default writes /t:exe for you thereby
creating an executable file. The long form of /t is /target and depending upon
the time of day, choose the appropriate one. Not all options have a long and a
short form like /t does. The : after the option is mandatory. Then we write the
type of executable output file we want. Library means a dll. When we run the
dir command we see a file named a.dll in the current sub directory. A file with
a .dll or a .exe extension are called Windows PE files. This is the default
file format that Windows uses to create executable files. There is a minor
difference between the internal structures of a dll and a exe file. Which is
that a dll cannot be executed like an exe program can. By convention dlls' are
used to store code even though an exe could have also be used. What we have
done is created a component.
What if we wanted to change the name of the output file. By
default it is the name of our program. Then we must use the /out option as
follows.
csc /t:library
/out:bbb.dll a.cs
This will create a file bbb.dll instead of a.dll as earlier.
Whether we use the /out option or not, C# does it for us. If the C# program was
called a.cs, then C# wrote /out:a.exe on the command line for us. We can use
the /out option to change the name of the output file. /out does not have a
short form /o.
Remember C# uses defaults for command line options to make
life easier for us.
Note that we have created a component in a file bbb.dll
a.cs
class yyy
{
public static void Main()
{
zzz a;
}
}
Compiler error
a.cs(5,1): error CS0246: The type or namespace name 'zzz'
could not be found (are you missing a using directive or an assembly
reference?)
The only reason we create a component is to allow other
programs to call code from it. In the above program, we are saying that a looks
like a class zzz. The C# compiler is telling us in a vague way that it does not
know that zzz is a class. Though we know zzz is a class as we just created it
and it is in a file called bbb.dll, C# is not aware of the same.
a.cs
class yyy {
public static void Main()
{
zzz a;
}
}
>csc a.cs /r:bbb.dll
Compiler Warning
a.cs(5,5): warning CS0168: The variable 'a' is declared but
never used
The /r or reference option tells the C# compiler to look at
bbb.dll; in this case for the code of classes it is not aware of. In our case
the error disappears as the file bbb.dll contains the code for the class
bbb.dll. Thus in future, if the C# compiler ever gives you the above error, do
not panic. All that you need to do is specify which dll contains the code for
the classes. The help available along with C#, tells you every class and the
dll that contains the code of the class.
a.cs
class yyy
{
public static void Main()
{
zzz a = new zzz();
a.abc();
}
}
Output
zzz abc
We have successfully called the function abc in class zzz.
The code of the class zzz resides in bbb.dll.
a.cs
namespace mukhi
{
public class zzz
{
public void abc()
{
System.Console.WriteLine("zzz abc");
}
}
}
>csc /t:library
/out:bbb.dll a.cs
The same class zzz is now enclosed in a namespace mukhi and
the component recreated.
a.cs
class yyy
{
public static void Main()
{
zzz a = new zzz();
a.abc();
}
}
csc a.cs /r:bbb.dll
Compiler Error
a.cs(5,1): error CS0246: The type or namespace name 'zzz'
could not be found (are you missing a using directive or an assembly
reference?)
a.cs(6,1): error CS0246: The type or namespace name 'a'
could not be found (are you missing a using directive or an assembly
reference?)
The error results as the name of the class is not zzz but
mukhi.zzz.
a.cs
using mukhi;
class yyy {
public static void Main()
{
zzz a = new zzz();
a.abc();
}
}
Output
zzz abc
All's well that ends well.
Databases
Databases are centralized stores of data. In a database,
information from several files (also known as tables) is accessed, coordinated
and operated upon as if in a single file. Thus, the database organizes data
independently from the programs that access it. Large volumes of data are
stored in a database.
Computer programs have little meaning when written in
isolation. Therefore, it is of importance to have our programs work with
databases. Databases work under the control of a database management system.
SQL Server is an RDBMS; it is one such database management system.
Before we can write programs that communicate with databases
we need to have a database. One of the simplest databases to use is Microsoft
Access. As a large number of people use it, we have based our examples on it.
However you could use any RDBMS like Oracle, SQL Server as you wish.
First and foremost, before we can access a database and perform
various operations, we need to connect to the database. Assuming you want to
speak to your friend over the phone, you dial your friend's phone number. Its
only when you connect to your friends phone that the both of you can speak to
each other. Similarly, if we want to use a database we first have to connect to
it and only then can we speak to it.
a.cs
class zzz
{
public static void Main()
{
System.Data.OleDb.OleDbConnection s;
}}
Compiler warning
a.cs(5,35): warning CS0168: The variable 's' is declared but
never used
Let's see how we can connect to a database.
a.cs
class zzz
{
public static void Main()
{
try
{
System.Data.OleDb.OleDbConnection s;
s = new System.Data.OleDb.OleDbConnection();
System.Console.WriteLine("hell");
}
catch(System.Exception e)
{
System.Console.WriteLine(e.ToString());
}
}
}
Output
hell
Within the try block we have an object s that looks like
System.Data.OleDb.OleDbConnection. You are already aware that System is a
namespace, but what about Data and OleDb?
Well, System.Data.OleDb is the name of the namespace. We can liken this
to an earlier example where we created a namespace mukhi.vijay. Hierarchy in a
namespace can go to absurd lengths! The help on C# states that
System.Data.OleDb is the name of a namespace and OleDbConnection is a class
within that namespace. By saying s = System.Data.OleDb.OleDbConnection(); we
are creating the object s. When you run this program all that we see is the
word hell, which means that the constructor threw no Exception. If it did then
the catch block would have been executed.
a.cs
class zzz
{
public static void Main()
{
try
{
System.Data.OleDb.OleDbConnection s;
s = new System.Data.OleDb.OleDbConnection();
s.Open();
System.Console.WriteLine("hell");
}
catch(System.Exception e)
{
System.Console.WriteLine(e.ToString());
}
}
}
Output
System.InvalidOperationException: The ConnectionString
property has not been initialized.
at
System.Data.OleDb.OleDbConnection.Open()
at zzz.Main()
The class OleDbConnection has a function called Open which
opens or connects to our database. When we run the above the above program, the
Open function throws an exception. Now, at this point you must catch the
Exception. The ToString within the catch block displays the Exception message.
The error says that the ConnectionString property has not been initialized.
The reason an exception occurred is that we did not provide
certain mandatory information whilst creating the object. We did not indicate
the location of the database server that we want to connect to or the database.
It's like picking up the phone and not dialing a number. How in the world do
you expect to connect then!
We will now provide the constructor with some basic
information.
a.cs
class zzz
{
public static void Main()
{
try
{
System.Data.OleDb.OleDbConnection s;
s = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=c:\\zzz.mdb;");
s.Open();
System.Console.WriteLine("hell");
}
catch(System.Exception e)
{
System.Console.WriteLine(e.ToString());
}
}
}
Output
System.Data.OleDb.OleDbException: Could not find file
'c:\zzz.mdb'. at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr)
at
System.Data.OleDb.OleDbConnection.InitializeProvider()
at
System.Data.OleDb.OleDbConnection.Open()
at zzz.Main()
We first specify the database provider as
Provider=Microsoft.Jet.OLEDB.4.0, which stands for MS-Access and then the path
as Data Source=c:\\zzz.mdb separated by semicolon.
When you run the program, an OleDbException is thrown, which
says could not find file 'c:\zzz.mdb'. This is because we have not created an
MSAccess DataBase called zzz.mdb.
An mdb file stores data from multiple tables. In this file
zzz.mdb, create one table abc with 2 fields f1 and f2 , both of type text. Add
two records to this database. The first record will contain 1,a1 and the second
record will be 2,a2.
Re run the program which now displays hell unlike before
where we generated an Exception.
Output
hell
s.Open opens a connection to the database. The 'Provider'
and 'Data Source' given in the constructor identify the database.
As we have given the constructor
all the mandatory information, s.Open() will open a connection with the Access
database. The program executes smoothly and once the connection is established
WriteLine displays 'hell'.
Now that we have established a connection with the database,
let's see how we can communicate with it. More specifically, let's see how we
can execute an SQL command.
a.cs
using System.Data.OleDb;
class zzz
{
public static void Main()
{
try
{
OleDbConnection s;
s = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=c:\\zzz.mdb");
s.Open();
OleDbCommand c;
c=new OleDbCommand("Create Table a1 ( vno integer , name
char(10))",s);
c.ExecuteNonQuery();
System.Console.WriteLine("hell");
}
catch(System.Exception e)
{
System.Console.WriteLine(e.ToString());
}
}
}
Output
hell
Employing using, we do not have to preface every class with
the namespace System.Data.OleDb. The
purpose of this program is to execute an SQL statement. SQL is a short form for
Structured Query Language. It is not a computer programming language like C#
but rather a language like English. SQL understands certain words like Create,
which create a table for us in our database. SQL has nothing to do with Oracle
or Access, it works with all the RDBMSs
of the world.
Create Table a1 (vno integer, name char(10))
The above SQL statement means that a table called a1 will be
created in our database. It will have 2 fields vno and name. Vno will store
numbers and name will store characters subject to a maximum of 10.
A class in any programming language is made up of a
collection of variables and functions. These put together do something for us
like solving a particular problem or accomplishing a certain task. We live in a
world of specialization. Just like it is difficult to come across one human
being who is a PHD in Nuclear Science as well as Sociology, classes too are
written to focus on one particular task only.
Thus we need one class to handle our connection to a
database and another class which understands SQL. We first create an object
that looks like an OleDbConnection, which understands how to connect to a
database using a string attributes 'Provider' and 'Data Source', which it
passes as a parameter to the constructor. Then we create an object c which
looks like OleDbCommand as this class understands SQL. The constructor gets
called with 2 parameters, the SQL statement and also the connection object.
Thus the object c now comprises the necessary information regarding the command
we want to execute and the connection. Now, c.ExecuteNonQuery() will actually
execute the SQL statement. ExecuteNonQuery() is a function within the
OleDbCommand class that enables us to
execute any SQL command. You will now
see the word 'hell' displayed on your screen.
But did the table get created? Let's find out by starting
Access and then choosing the database zzz. Here we will see the table a1 with 2
fields vno and name but with no data at all. We created our table alright, but
it is empty as it has no data in it! So let's start inserting data/records into
the table. Before doing so, Quit out of Access
a.cs
using System.Data.OleDb;
class zzz
{
public static void Main()
{
try
{
OleDbConnection s;
s = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=c:\\zzz.mdb");
s.Open();
OleDbCommand c;
c=new OleDbCommand("insert into a1
values(1,'hi')",s);
c.ExecuteNonQuery();
System.Console.WriteLine("hell");
}
catch(System.Exception e)
{
System.Console.WriteLine(e.ToString());
}
} }
Output
hell
The only difference between this program and the previous
one is that this one uses the Insert SQL command. Earlier, we created a table
zzz, now we are inserting records in zzz. To the constructor of OleDbCommand we
are giving two things - the insert command "insert into a1
values(1,'hi')" and the connection. Insert into and values are part of the
SQL syntax. A1 is the name of the table in which a fresh record will be added.
After values, we write the data separated by commas. The first field will
contain 1 and the second hi. As the second field has the data type of
character, it has to be enclosed in single quotes. c.ExecuteNonQuery as usual executes the SQL command and as no
exception gets thrown ,the word 'hell' gets displayed..
Let's cross check to see if the data has been inserted into
zzz by running Access. Aha! It displays the record that you just inserted in
the following format-
vno name
1 hi
Now it follows that if we can insert a record then we should
be able to remove it too. The following program uses the delete command to
remove all the records from the table zzz.
a.cs
using System.Data.OleDb;
class zzz
{
public static void Main()
{
try
{
OleDbConnection s;
s = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=c:\\zzz.mdb");
s.Open();
OleDbCommand c;
c=new OleDbCommand("delete from a1",s);
c.ExecuteNonQuery();
System.Console.WriteLine("hell");
}
catch(System.Exception e) {
System.Console.WriteLine(e.ToString());
}
}
}
Output
hell
Now c.ExecuteNonQuery will execute the delete command. This
command deletes all the data from the table. In Access we will see an empty
table. This proves that the delete command was successful!
Apart from inserting and deleting data another operation that
is commonly performed is updating data. However, you must have data in your
table to update it. Our table is empty right now, so let's execute the insert
program once again. You will now have one record in your table zzz. After
inserting a record, change the sql insert command to an update command. This is
shown below.
c= new OleDbCommand("update a1 set vno=3,name='bad'
where vno=1",s);
Here, we are giving an update statement and our connection
to the object is 'c'. The update statement will change the vno and name to the
values specified. It will do so for any record that has vno=1. We have only one
record in our table and it meets this criteria. Now, go back to Access , you
will find that the record has been updated as follows-
vno name
3 bad
a.cs
using System.Data.OleDb;
class zzz
{
public static void Main()
{
try
{
OleDbConnection s;
s = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\\zzz.mdb");
s.Open();
OleDbCommand c;
c=new OleDbCommand("delete from a11",s);
c.ExecuteNonQuery();
System.Console.WriteLine("hell");
}
catch(System.Exception e)
{
System.Console.WriteLine(e.ToString());
}
}
}
Output
System.Data.OleDb.OleDbException: The Microsoft Jet database
engine cannot find the input table or query 'a11'. Make sure it exists and that its name is spelled correctly.
at
System.Data.OleDb.OleDbCommand.ExecuteCommand
TextErrorHandling(Int32 hr)
at
System.Data.OleDb.OleDbCommand.ExecuteCommand
TextForSingleResult(tagDBPARAMS dbParams, Object&
executeResult)
at
System.Data.OleDb.OleDbCommand.
ExecuteCommandText(Object& executeResult)
at
System.Data.OleDb.OleDbCommand.
ExecuteCommand(CommandBehavior behavior, Object&
executeResult)
at
System.Data.OleDb.OleDbCommand.
ExecuteReaderInternal(CommandBehavior behavior, String
method)
at
System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at zzz.Main()
Don't worry, we have simulated this error on purpose. The
table a11 does not exist in our database. Thus an exception got thrown by
ExecuteCommandText and not ExecuteNonQuery as seen above. What it means is that
ExecuteNonQuery calls Execute which calls ExecuteCommand which finally calls
ExecuteCommandText. Also the word hell does not get displayed because of the
exception thrown.
We will now execute a simple select statement.
a.cs
using System.Data.OleDb;
class zzz
{
public static void Main()
{
try
{
OleDbConnection s;
s = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\\zzz.mdb");
s.Open();
OleDbCommand c;
c=new OleDbCommand("select * from abc",s);
OleDbDataReader r;
r = c.ExecuteReader();
System.Console.WriteLine("hell");
}
catch(System.Exception e)
{
System.Console.WriteLine(e.ToString());
}
}
}
Output
hell
We have executed different SQL commands through our earlier
programs. We will now try and execute an SQL select statement. In order to do
so, we have given the following select statement to the constructor along with
the connection- 'Select * from abc'. Abc is a table in the database called zzz
. Remember, c looks like OleDbCommand. 'Select' is part of the SQL syntax like
insert , update . 'from' is also a reserved word . 'abc' is the name of the
table created earlier in Access and populated by data keyed in by us. The '*'
means all fields in the table. We could have also replaced the * with a comma
separating list of field names that we were interested in.
As OleDbConnection has an Open function, OleDbCommand has a
ExecuteReader function. This function returns an object that looks like
OleDbDataReader.
The function ExecuteReader will create an object that looks
like OleDbDataReader and initialize r to it. No exception was thrown, which
meant no errors from our side.
What we need to do now is retrieve data from the abc table.
The abc table may have ten fields and twenty records. So we use what is called
a 'dataset'. In other words, how do we access data contained in multiple rows
and multiple columns through our program? For example, when we run the select
statement it gives us the data in rows and columns. How can we achieve this
through our program? To do that we now need a third object which looks like
OleDbDataReader.
We can almost feel your dismay - Oh no! Another object!
Well, there is a very good reason why you need to have three different objects.
Firstly, you need an object that understands a connection. Then you need
another object that understands an SQL statement. This is because somebody has
to execute that statement. You now need a third object that will let you
read/retrieve data. That is why we have 'r' an object that looks like
OleDbDataReader. Thereafter, we have c.ExecuteReader. Earlier we used a
function call ExecuteNonQuery. This is because in case of an insert, update or
delete we didn't want any data to be retrieved, we didn't want an answer back!!
As a recap, we are using r to store the value return by
ExecuteReader. That means r will be filled up by ExecuteReader. On executing
this program only 'hell' is displayed.
Now that we have the filled up r,
let's see how we can display the data.
a.cs
using System.Data.OleDb;
class zzz
{
public static void Main()
{
try
{
OleDbConnection s;
s = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=c:\\zzz.mdb");
s.Open();
OleDbCommand c;
c=new OleDbCommand("select * from abc",s);
OleDbDataReader r;
r = c.ExecuteReader();
System.Console.WriteLine("{0},{1}",r.GetValue(0),r.GetValue(1));
System.Console.WriteLine("hell");
}
catch(System.Exception e)
{
System.Console.WriteLine(e.ToString());
}
}
}
Output
System.InvalidOperationException: No data exists for the
row/column.
at
System.Data.OleDb.OleDbDataReader.DoValueCheck(Int32 ordinal)
at
System.Data.OleDb.OleDbDataReader.GetValue(Int32 ordinal)
at zzz.Main()
Run this program. Expect the unexpected....an error! The
error says 'No data exists for the row/column' But didn't the function Execute
fill up r? It did, but it did not position the record pointer on the first
record. A record pointer is an abstract concept. To start with it appears just
before the first record. The function GetValue will return the value of the
fields depending upon where the record pointer is. At the start it is just
before the first record. So r can be
also looked at as an array that contains all the fields. We get an error
because we need to read the data into our array first. In order to read data
into the fields we must give r.Read. Without r.Read, the records will not be
read into the array, so also the record pointer will not be moved forward to
the first record.
Now that we know the reason why the error occurred let's
rectify it by doing the needful. Let's add r.Read() to our program.
a.cs
using System.Data.OleDb;
class zzz
{
public static void Main()
{
try
{
OleDbConnection s;
s = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=c:\\zzz.mdb");
s.Open();
OleDbCommand c;
c=new OleDbCommand("select * from abc",s);
OleDbDataReader r;
r = c.ExecuteReader();
System.Console.WriteLine(r.Read());
System.Console.WriteLine("{0},{1}",r.GetValue(0),r.GetValue(1));
}
catch(System.Exception e)
{
System.Console.WriteLine(e.ToString());
}
}
}
Output
True
1,a1
Now that we have added r.Read() the program executes as
advertised. r.Read() returns true.
Thereafter, the GetValue() function which needs the field number as a
parameter retrieves the data associated with the first and second column. Remember in C# we start counting
from zero and hence zero as a parameter to GetValue will retrieve the value of
field one in the table. The datatype of the first field is numeric and of the
second character. However, the function GetValue does not seem to care.
Read returns true if more data is available to read and
false if it is at the last record. Also read positions the record pointer to
the next record.
a.cs
using System.Data.OleDb;
class zzz
{
public static void Main()
{
try
{
OleDbConnection s;
s = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=c:\\zzz.mdb");
s.Open();
OleDbCommand c;
c=new OleDbCommand("select * from abc",s);
OleDbDataReader r;
r = c.ExecuteReader();
while (r.Read())
System.Console.WriteLine("{0},{1}",r.GetValue(0),r.GetValue(1));
}
catch(System.Exception e)
{
System.Console.WriteLine(e.ToString());
}
}
}
Output
1,a1
2,a2
(A display of only the first two fields of all the records
in the table. )
But now we want to display all the data in the fields. So we
are using Read in a loop. while(r.Read()). r.Read() returns true as long as it
can read data. The while loop continues till the condition is true. We are
using our trusted function WriteLine to display the data of the first and
second field. Thus the moment r.Read() cannot read more data it returns false
and the while loop terminates. Now all the data in the two fields is displayed
and as such we have a long list of data displayed. To verify further, add some
more fields and records to abc.
Just as we mind our manners in daily life we must do so in
programming too. It is a good practice to shut the door when leaving a room.
Similarly, in programs we must close what ever we opened.
a.cs
using System.Data.OleDb;
class zzz
{
public static void Main()
{
try
{
OleDbConnection s;
s = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=c:\\zzz.mdb");
s.Open();
OleDbCommand c;
c=new OleDbCommand("select * from abc",s);
OleDbDataReader r;
r = c.ExecuteReader();
while (r.Read())
System.Console.WriteLine("{0},{1}",r.GetValue(0),r.GetValue(1));
r.Close();
s.Close();
System.Console.WriteLine("hell");
}
catch(System.Exception e)
{
System.Console.WriteLine(e.ToString());
}
}
}
Output
1,a1
2,a2
We will mind our manners and give r.Close() and s.Close.
Note that it is not mandatory to do so. First we are closing r and then we are
closing s i.e. the connection that we opened.
a.cs
using System.Data.OleDb;
class zzz
{
public static void Main()
{
try
{
OleDbConnection s;
s = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=c:\\zzz.mdb");
s.Open();
OleDbCommand c;
c=new OleDbCommand("select * from abc",s);
OleDbDataReader r;
r = c.ExecuteReader();
System.Console.WriteLine(r.FieldCount);
for ( int i = 0 ; i < r.FieldCount ; i++)
System.Console.WriteLine(r.GetName(i));
}
catch(System.Exception e)
{
System.Console.WriteLine(e.ToString());
}
}
}
Output
2
f1
f2
An OleDbDataReader has a useful function called FieldCount
which tells you how many fields the SQL statement contained. In our case abc
has two fields. We have one more function GetName which will tell us the name
of the function in the table given the field number. Thus r.GetName(1) will
give us the name of the 2nd field. In the for statement, the variable i starts
at zero. The condition becomes false when the value of i is one less than r.FieldCount which in this case is 2 i.e. i
will take values of 0 and 1. The reason being i starts from zero as GetName
understands zero as the first field. Thus we can now print all the column names
using the for. Now lets print the entire table irrespective of the number of
columns or the number of rows.
a.cs
using System.Data.OleDb;
class zzz
{
public static void Main()
{
try
{
OleDbConnection s;
s = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=c:\\zzz.mdb");
s.Open();
OleDbCommand c;
c=new OleDbCommand("select * from abc",s);
OleDbDataReader r;
r = c.ExecuteReader();
while ( r.Read() )
{
for ( int i = 0 ; i < r.FieldCount ; i++)
System.Console.Write(r.GetValue(i)+" ");
System.Console.WriteLine();
}
}
catch(System.Exception e)
{
System.Console.WriteLine(e.ToString());
}
}
}
Output
1 a1
2 a2
You can modify abc to add more fields and fill in more records. Execute the program and see the change in output. Whenever we do not know better, we use a for or a while i.e. a looping construct. However we do not know two things. One, the numbers of rows in the table and two the number of columns. The first while takes care of the number of rows and the for, the variable number of columns. Thus the above program will work for any number of columns and rows in the table. All that we do is change the select statement. The difference between Write and WriteLine is that Write does not add an enter at the end like WriteLine does. Also + is used to join two strings together. We are adding a space at the end of every field as a separator.