Connecting a Database II

In the last edition I showed how to define a connection string. i.e. How to tell the application the type and location of said database. Here I shall detail how to use said string in the coding. Please feel free to comment on my posts with your doubts and suggestions or mail me at nardz07@gmail.com.




An application may have more that one modules, and so a need to connect to a database multiple times at varying instances. So one has to connect to a database multiple times in an application. For every database connection, Microsoft provides certain packages or namespaces with methods and drivers for a certain database type within them. Hence in any form where we intend to connect to a database, this namespace must be included.

Here as you can see the namespace System.Data.OleDb has been included. This is because I wish to include a Microsoft access database.

When we use a object database that may or may not include sql queries we use:

using System.Data.OleDb;

When we are specifically using SQL Server database:

using System.Data.SqlClient;

When we are using an platform independent open source database:

using System.Data.Odbc;

When we are specifically using Oracle database:

using System.Data.OracleClient;

Now that we have declared the packages we will require for said program, we must define the connection, i.e. Tell the application what driver to use to read a database, where it is located, what security measures it holds, and how to get past said measures.

So, inside the namespace and the partial class, we define

OleDbConnection conn = new OleDbConnection(@"provider = Microsoft.Jet.OLEDB.4.0; Data Source=E:\Project11\learning curve\Windows apps\addressbook\addressbook.mdb ");

As we already know that OleDbConnection is a class object as provided by the namespace we defined above: OleDb, here a new connection object of namespace OleDb has been defined with the name conn.

As you can see that we have defined all the parameters of said database inside the brackets, preceded by @ to inform us that these are the features and details of said database.

You can also notice, that this string is the exactly what we get when we connect a database visually to an application, as I had detailed in Connecting a Database I. So just copy paste the entire code here, and voila, a database connection has been defined.

Once the connection has been defined, we must open and close the connection in the required event ONLY. This is because the longer a connection is open, the more it is vulnerable to hacking. So, once you are into the event in question, and all the validations have been taken care of, then open the connection using the connection object:

conn.Open();

In the above event, data is being inserted into a table in connected database. The command is taken in by the OleDbCommand object cmd in reference to the connection object conn.

Once cmd is defined it is executed

cmd.ExecuteNonQuery();

and then the connection is closed immediately, even before raising a dialog to depict success:

conn.Close();

In this event, we have defined conn in a different way. This I shall explain in a bit, but let us concentrate on the OleDbCommand comm here. Once we have defined a select query inside it in reference to Connection object conn, we must provide a reader to read the values that are generated upon execution.

So we define OleDbDataReader object dr and call it to take in the values from the ExecuteReader() method of comm.

What this does, is read one value at a time. So, we provide a while loop for the reader dr to keep reading as long as it gets values. Inside the while loop we tell what must be done with the data that is read. These coding will change with the type of data we are dealing with, and the type of presentation we are providing it.

Then we close the connection.

NOTE:

If only one value is to be generated, then we use the ExecuteScalar() method of object comm.

If lots of values are to be generated at once, and we have a presentation capability to hold the entirety without issue, we use the OleDbDataAdapter and OleDbDataSet classes under the OleDb namespace.

For those people who wish to know parallel commands for other database namespaces:

OleDbConnection for OLEDB is SqlConnection for SQL Server, OracleConnection for Oracle, and OdbcConnection for ODBC.

OleDbCommand for OLEDB is similarly SqlCommand, OracleCommand, & OdbcCommand.

This combination is similarly followed with only minor changes in the methods within these classes. To understand them more, please feel free to study about OleDb, SqlClient, Odbc, OracleClient.

Notice, above in the dgw_Load() event, I have called the connection instead of defining it. This is because I have used inheritance to define the connection. This is a very useful technique that I will explain in much detail in my next post. Till then, please enjoy these. Contact me freely at aforementioned email id anytime with any suggestions or doubts. And also please ask questions so I can look into those I do not know and broaden my horizons. Well, so long.