VB icon

Database Connection Class

Email
Submitted on: 1/1/2015 5:43:00 AM
By: Mandrake Dragonbait (from psc cd)  
Level: Intermediate
User Rating: By 6 Users
Compatibility: C#
Views: 1033
 
     This code serves as a starting point for database connections in C#. Adding greater functionality should not be too difficult. This example contains functionality for data navigation, database connection, recordset connection, Bookmarking, AbsolutePosition and RecordCount counters, and of course: Returning data from the table.
 
code:
Can't Copy and Paste this?
Click here for a copy-and-paste friendly version of this code!
				
//**************************************
// Name: Database Connection Class
// Description:This code serves as a starting point for database connections in C#. Adding greater functionality should not be too difficult.
This example contains functionality for data navigation, database connection, recordset connection, Bookmarking, AbsolutePosition and RecordCount counters, and of course: Returning data from the table.
// By: Mandrake Dragonbait (from psc cd)
//
// Assumes:In order to use this code you will need to set a reference to the Microsoft ActiveX Data Objects library 2.7.
A quick example of how to use this code:
//Declare an object of type DBConnect class:
private DBConnect DBCN = new DBConnect();
//Because the connection is static (so you
//can have one connection but several recordsets)
//You connect to the database using the class name
//and not the object you declared:
DBConnect.Connect("\\Database\\ExampleDB.mdb");
//To open a recordset:
DBCN.RSConnect("tblTableName");
//To navigate records:
DBCN.Navigate("first");
DBCN.Navigate("next");
DBCN.Navigate("previous");
DBCN.Navigate("Last");
//To set a bookmark:
DBCN.Bookmark = DBCN.AbsPosition;
//To go to the bookmarked record:
DBCN.GotoBookmark();
//To return data from a field on
//the current record (0 is the number
//for the field):
DBCN.Value(0)
//
// Side Effects:If you do not have the .NET framework installed on your machine, this code has no hope of working.
//**************************************

using System;
using System.Windows.Forms; //Used for message boxes during testing
namespace Nova
{
	public class DBConnect
	{
		#region Declares
		private static ADODB.Connection cn = new ADODB.ConnectionClass();
		private ADODB.Recordset rs = new ADODB.RecordsetClass();
		private long RecordCount = 0;
		private long AbsolutePosition = 0;
		private long BMark = 0;
		#endregion
		#region Property get & let...
		public long RSCount
		{
			get{return RecordCount;}
		}
		public long AbsPosition
		{
			get{return AbsolutePosition;}
		}
		public bool EOF
		{
			get{return rs.EOF;}
		}
		public bool BOF
		{
			get{return rs.BOF;}
		}
		public long Bookmark
		{
			get{return BMark;}
			set{BMark = value;}
		}
		#endregion
		#region Methods/Functions/Procedures
		public DBConnect()
		{}//End DBConnect()
		public static void Connect(string DBName)
		{
			//Connect to the specified database. This database will
			//need to be stored in the same directory as the .exe file.
			//During design time this directory will most likely be in
			//the bin folder. So don't be confused if it doesn't find it.
			//You can also specify a longer string of subfolders for
			//DBName
			try
			{
				string DBProvider = "Provider=Microsoft.Jet.OLEDB.4.0;";
				string DBSource = "Data Source=" + Application.StartupPath.ToString() + DBName + ";";
				string ConStr = DBProvider + DBSource;
				cn.Open(ConStr, "", "", 0);
			}
			catch(Exception e)
			{
				MessageBox.Show(e.ToString());
			}
		}//End Connect()
		
		public void RSConnect(string TableName)
		{
			try
			{
				//Open a connection to the table
				rs.Open(TableName, cn, ADODB.CursorTypeEnum.adOpenDynamic,ADODB.LockTypeEnum.adLockOptimistic, 0);
				//Set AbsPosition and RecordCount
				if(rs.EOF == true && rs.BOF == true)
				{
					RecordCount = 0;
					AbsolutePosition = 0;
				}
				else
				{
					rs.MoveFirst();
					//Loop through records to count them
					do
					{
						RecordCount++;
						rs.MoveNext();
					} while(rs.EOF == false);
					rs.MoveFirst();
				}
			}
			catch
			{
				MessageBox.Show("Cannot connect to table specified.");
			}
		}//End RSConnect()
		public string Value(int FieldNum)
		{
			//Return a value from the database
			return(rs.Fields[FieldNum].Value.ToString());
		}//End Value()
		public void Navigate(string Direction)
		{
			//This switch statement handles the data navigation
			switch(Direction.ToUpper())
			{
				case "FIRST":
					rs.MoveFirst();
					AbsolutePosition = 1;
					break;
				case "PREVIOUS":
				case "PREV":
					if(rs.BOF == false)
					{
						rs.MovePrevious();
						AbsolutePosition--;
					}
					break;
				case "NEXT":
					if(rs.EOF == false)
					{
						rs.MoveNext();
						AbsolutePosition++;
					}
					break;
				case "LAST":
					rs.MoveLast();
					AbsolutePosition = RecordCount;
					break;
				default:
					MessageBox.Show(Direction + " is not a valid argument.");
					break;
			}//End Switch
		}//End MoveNext()
		public void GotoBookmark()
		{
			//Move to the first record
			rs.MoveFirst();
			AbsolutePosition = 1;
			//Loop through the records until the bookmark is reached
			for(long i = 1; i < BMark; i++)
			{
				rs.MoveNext();
				AbsolutePosition++;
			}
		}//End GotoBookmark
		#endregion
	}
}


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this code (in the Intermediate category)?
(The code with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)
 

Other User Comments

1/20/2016 8:37:06 PMStacey

Transitioning from VB.Net to C# and want to use ADO and want to manipulate recordsets directly think your code will help alot thanks.
(If this comment was disrespectful, please report it.)

 

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular code, please click here instead.)
 

To post feedback, first please login.