I am no longer a beginner like I was back in 2002 when I wrote A Beginner's Guide to VB.NET and Database Programming - The Recipe Application for DevCity.NET. I wrote back then that "I am a beginner. I wanted to share some experiences with other beginners" and now I want to share with you what I have learned since then. I have made several applications and actually got paid to make two of them. In the process of developing, I graduated from the simple books I started with to more advanced material and even started to experiment on my own with the concepts I learned.
Eventually my goal will be to create a small database application that is a working functional piece of software. It will be more complex than The Recipe Application was and will make use of a local Access database. I will walk you through the concepts first and then in the end I will enclose a copy of the source code. Yes, this will be a long process that will cover everything from database classes to GUI creation and concepts.
The first item I want to cover is the generic database class. Beginners books show you how to make a DataConnection, DataAdapter and DataSet using the IDE; while that works I find it a poor way to do things. I prefer to understand a little more and excercise a little more control.
The Connection:
The first thing to do in a database class is to make a connection to the database. Go ahead and right click on your project in the solution explorer and select add class. You can name it dbConnection.
|
public class database
{
public static OleDbConnection GetConnection()
{
// TODO: must get application install path
string connection =
@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Documents and Settings\UserName\Data\thedb.mdb";
return new OleDbConnection(connection);
}
// see below for more
} |
I hard coded the path above, but the best procedure would be to get the application install path or have an application settings file that stored the location of the database. In this case the application is intended to be profile specific for the data so the data is stored in each user's documents and settings folders. This function creates a connection to the database and can be called from any other part of your code. I prefer to extend my database class to include creating DataSets and DataViews and pass those to my pages (asp.net) or forms (windows). So let's take a look at that...
static DataSet ds(string select, string table)
{
OleDbConnection objConnection = GetConnection();
OleDbCommand objCommand =
new OleDbCommand(select, objConnection);
OleDbDataAdapter da = new OleDbDataAdapter(objCommand);
DataSet ds = new DataSet();
objConnection.Open();
try
{
da.Fill(ds, table);
}
catch (OleDbException e)
{
MessageBox.Show(e.ToString());
}
objConnection.Close();
return ds;
} |
Here we have a function that returns a DataSet public static DataSet ds(string select, string table) that requires the SELECT string for selecting fields and the name of the table. This allows you to reduce the amount of code you will need to write to get a DataSet to any form or page in the application you are creating or to build a DataSet so that you can create a DataView.
Well, that is it for this short example of how to make a generic Database Class. In the next part I will show you how to use the class above to make more specific classes for building menus, drop down boxes, or even filling datagrids. I wll show you how to create a DataView and give you my thoughts on when to use them.
Until then keep coding.