MS Access OleDbHelper 클래스 사용한 DB샘플

https://steven-england.info/2014/10/28/c-helper-class-database-access-various-providers/

A C# helper class for database access for various providers - Steven England's Weblog

MS Access(OleDB) + C# 을 이용한 데이타베이스 샘플입니다.

개발툴은 SharpDevelopPortable로 만들었습니다.

등록,수정,삭제,검색 간단한 기능만 테스트

ExecuteReader 부분은 조금 더 봐야 될듯 싶습니다. (Connection관련부분)

 

 

개발툴

SharpDevelopPortable.zip

9.54MB

SharpDevelopPortable.z01

10.00MB

소스

DBSample.zip

0.33MB

 

 

/*
 * SharpDevelop으로 작성되었습니다.
 * 사용자: 지오시스템
 * 날짜: 2019-12-26
 * 시간: 오전 10:38
 * 
 * 이 템플리트를 변경하려면 [도구->옵션->코드 작성->표준 헤더 편집]을 이용하십시오.
 */
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Windows.Forms;

// 추가 클래스
using System.Data;
using System.Data.Common;
using System.Data.OleDb;

namespace DBSample
{
	/// <summary>
	/// Description of MainForm.
	/// </summary>
	public partial class MainForm : Form
	{
		private OleDbHelper _oleDbHelper;
		public MainForm()
		{
			//
			// The InitializeComponent() call is required for Windows Forms designer support.
			//
			InitializeComponent();
			
			//
			// TODO: Add constructor code after the InitializeComponent() call.
			//
			// 초기설정
			init();
			
			// 데이타베이스 바인딩
			bind();
		}
		
		/// <summary>
		/// 초기설정
		/// 그리드 디자인 구성
		/// 클래스 정의
		/// </summary>
		private void init()
		{
				// sql helper 클래스 생성
				_oleDbHelper = new OleDbHelper();
				_oleDbHelper.CreateDBObjects(Constants.CONNECTION_ACCESS_DB, OleDbHelper.Providers.OleDB);
				
				// 그리드 설정
				gridMaster.AllowUserToAddRows = false;
				gridMaster.AutoGenerateColumns = false;
				gridMaster.ColumnCount = 7;
				gridMaster.RowHeadersVisible = false;
				gridMaster.EditMode = DataGridViewEditMode.EditProgrammatically;
				gridMaster.SelectionMode = DataGridViewSelectionMode.FullRowSelect; //row선택시 전체row선택
				gridMaster.MultiSelect = false; // row 다중선택 off
				gridMaster.RowTemplate.Height = 23;
				gridMaster.AlternatingRowsDefaultCellStyle.BackColor = Color.AliceBlue;   // 홀수row배경색 지정
				
				gridMaster.ScrollBars = ScrollBars.Vertical;                              // 스크롤바 vertical만 지정
				gridMaster.DefaultCellStyle.Font = new Font("돋움", 9);                   // 전체 
				
				gridMaster.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.None;
				gridMaster.AllowUserToResizeRows = false;                                 // row조정고정(false)

				gridMaster.Columns[0].HeaderText = "IDX";
				gridMaster.Columns[0].DataPropertyName = "IDX";
				gridMaster.Columns[0].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
				gridMaster.Columns[0].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
				gridMaster.Columns[0].Width = 30;
				gridMaster.Columns[0].Visible = true;
				
				gridMaster.Columns[1].HeaderText = "Type1";
				gridMaster.Columns[1].DefaultCellStyle.Font = new Font("Tahoma", 8);
				gridMaster.Columns[1].DataPropertyName = "Type1";
				gridMaster.Columns[1].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
				gridMaster.Columns[1].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
				gridMaster.Columns[1].Width = 50;
				gridMaster.Columns[1].SortMode = DataGridViewColumnSortMode.NotSortable;
				
				gridMaster.Columns[2].HeaderText = "Type2";
				gridMaster.Columns[2].DefaultCellStyle.Font = new Font("Tahoma", 8);
				gridMaster.Columns[2].DataPropertyName = "Type2";
				gridMaster.Columns[2].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
				gridMaster.Columns[2].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
				gridMaster.Columns[2].Width = 80;
				gridMaster.Columns[2].SortMode = DataGridViewColumnSortMode.NotSortable;
				
				
				gridMaster.Columns[3].HeaderText = "등록일자";
				gridMaster.Columns[3].DataPropertyName = "Date1";
				gridMaster.Columns[3].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
				gridMaster.Columns[3].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
				gridMaster.Columns[3].Width = 90;
				
				gridMaster.Columns[4].HeaderText = "관리자";
				gridMaster.Columns[4].DataPropertyName = "Owner";
				gridMaster.Columns[4].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
				gridMaster.Columns[4].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
				gridMaster.Columns[4].Width = 90;
				
				gridMaster.Columns[5].HeaderText = "건수";
				gridMaster.Columns[5].DataPropertyName = "Count";
				gridMaster.Columns[5].HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
				gridMaster.Columns[5].DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;
				gridMaster.Columns[5].Width = 60;
				gridMaster.Columns[5].SortMode = DataGridViewColumnSortMode.NotSortable;
				
				gridMaster.Columns[6].HeaderText = "내용";
				gridMaster.Columns[6].DataPropertyName = "Msg";
				gridMaster.Columns[6].Width = 208;				
				

		}
		

		/// <summary>
		/// 검색버튼
		/// </summary>
		/// <param name="sender"></param>
		/// <param name="e"></param>
		void BtnSearchClick(object sender, EventArgs e)
		{
			string _search = string.Empty;
			_search = txtSearch.Text.Trim();
			bind(_search);
		}
		
		/// <summary>
		/// 바인딩 버튼
		/// </summary>
		/// <param name="sender"></param>
		/// <param name="e"></param>
		void BtnBindClick(object sender, EventArgs e)
		{
			bind();
		}

		/// <summary>
		/// 등록 버튼
		/// </summary>
		/// <param name="sender"></param>
		/// <param name="e"></param>
		void BtnInsertClick(object sender, EventArgs e)
		{
			Random rnd = new Random();
			string[] aryType1 = new string[]{"일반", "희귀", "전승", "전설"};
			string[] aryType2 = new string[]{"거래가능", "거래불가"};
			string[] aryOnwer = new string[]{"NC소프트", "넷마블", "지오스톰", "스마일게이트", "넥슨", "컴투스", "네오위즈"};
			string[] aryMsg = new string[]{"[어드벤처 게임]", "[아케이드 게임]", "[스포츠 게임]", "[MMORPG]", "[롤플레잉 게임]", "[시뮬레이션 게임]", "[퍼즐 게임]",  "[액션 게임]"};
			
			string queryString = @"INSERT INTO [test] ([Type1], [Type2], [Date1], [Owner], [Count], [Msg])
									VALUES(@isType1, @isType2, @isDate1, @isOwner, @isCount, @isMsg)";
			
			_oleDbHelper.AddParameter("@isType1", aryType1[rnd.Next(0,4)]);
			_oleDbHelper.AddParameter("@isType2", aryType2[rnd.Next(0,2)]);
			_oleDbHelper.AddParameter("@isDate1", DateTime.Now, DbType.Date);
			_oleDbHelper.AddParameter("@isOwner", aryOnwer[rnd.Next(0,7)]);
			_oleDbHelper.AddParameter("@isCount", rnd.Next(9,100));
			_oleDbHelper.AddParameter("@isMsg", aryMsg[rnd.Next(0,4)]);
			
			_oleDbHelper.ExecuteNonQuery(queryString, CommandType.Text, ConnectionState.Closed);
			bind();
		}
		
		/// <summary>
		///  전체 삭제 버튼
		/// </summary>
		/// <param name="sender"></param>
		/// <param name="e"></param>
		void BtnDeleteClick(object sender, EventArgs e)
		{
			try{
				_oleDbHelper.ExecuteNonQuery("DELETE FROM [test]", CommandType.Text, ConnectionState.Closed);
			}
			catch(Exception ex)
			{
				MessageBox.Show(string.Format("삭제오류 {0}", ex));
			}
			finally{
				MessageBox.Show("성공적으로 삭제했습니다.");
				bind();
			}
			
		}
		
		
		/// <summary>
		/// 선택 삭제
		/// </summary>
		/// <param name="sender"></param>
		/// <param name="e"></param>
		void BtnDelete2Click(object sender, EventArgs e)
		{
			int _idx = -1;
			_idx = Convert.ToInt32(txtIDX.Text);
			
			string queryString = @"DELETE FROM [test] WHERE [IDX] = @isIDX";
			_oleDbHelper.AddParameter("@isIDX", _idx);
			_oleDbHelper.ExecuteNonQuery(queryString, CommandType.Text, ConnectionState.Closed);
			
			// 수정 텍스트 박스 내용 초기화
			txtIDX.Text = string.Empty;
			txtType1.Text = string.Empty;
			txtType2.Text = string.Empty;
			txtDate.Text = string.Empty;
			txtOwner.Text = string.Empty;
			txtCount.Text = string.Empty;
			txtMsg.Text = string.Empty;
			
			// 바인딩
			bind();
		}
				
		/// <summary>
		/// 업데이트 버튼
		/// </summary>
		/// <param name="sender"></param>
		/// <param name="e"></param>
		void BtnUpdateClick(object sender, EventArgs e)
		{
			int _idx = -1;
			string _type1 = string.Empty;
			string _type2 = string.Empty;
			string _date = string.Empty;
			string _owner = string.Empty;
			int _count = -1;
			string _msg = string.Empty;
			
			_idx = Convert.ToInt32(txtIDX.Text);
			_type1 = txtType1.Text.Trim();
			_type2 = txtType2.Text.Trim();
			_date = txtDate.Text.Trim();
			_owner = txtOwner.Text.Trim();
			_count = Convert.ToInt32(txtCount.Text);
			_msg = txtMsg.Text.Trim();
			

			string queryString = @"UPDATE [test] SET 
								    [Type1]=@isType1, [Type2]=@isType2, [Date1]=@isDate1, [Owner]=@isOwner, [Count]=@isCount, [Msg]=@isMsg
								     WHERE [IDX] = @isIDX";
			_oleDbHelper.AddParameter("@isType1", _type1);
			_oleDbHelper.AddParameter("@isType2", _type2);
			_oleDbHelper.AddParameter("@isDate1", _date);
			_oleDbHelper.AddParameter("@isOwner", _owner);
			_oleDbHelper.AddParameter("@isCount", _count);
			_oleDbHelper.AddParameter("@isMsg", _msg);
			_oleDbHelper.AddParameter("@isIDX", _idx);
			
			_oleDbHelper.ExecuteNonQuery(queryString, CommandType.Text, ConnectionState.Closed);
			
			// 수정 텍스트 박스 내용 초기화
			txtIDX.Text = string.Empty;
			txtType1.Text = string.Empty;
			txtType2.Text = string.Empty;
			txtDate.Text = string.Empty;
			txtOwner.Text = string.Empty;
			txtCount.Text = string.Empty;
			txtMsg.Text = string.Empty;
			
			// 바인딩
			bind();
			
		}
		
		/// <summary>
		/// 그리드 선택이 바뀔때 이벤트
         	/// 마우스,키보드 이벤트 
		/// </summary>
		/// <param name="sender"></param>
		/// <param name="e"></param>
		void GridMasterSelectionChanged(object sender, EventArgs e)
		{
			//MessageBox.Show(string.Format("{0}", gridMaster[0, gridMaster.CurrentCell.RowIndex].Value));
			if (gridMaster.CurrentCell != null){
				getROW(gridMaster[0, gridMaster.CurrentCell.RowIndex].Value);
			}
		}
		
		/// <summary>
		/// 마우스 그리스 셀 선택 이벤트
		/// 마우스 이벤트
		/// </summary>
		/// <param name="sender"></param>
		/// <param name="e"></param>
		void GridMasterCellClick(object sender, DataGridViewCellEventArgs e)
		{
			

			
		}
		
		
		#region 데이타베이스 핸들링
					/// <summary>
		/// 테이블 바인딩 (검색어 X)
		/// </summary>
		private void bind()
		{
			try
			{
				string queryString = "SELECT IDX, Type1, Type2, Date1, Owner, Count, Msg FROM test ORDER BY IDX DESC";
				DataSet ds = new DataSet();
				ds = _oleDbHelper.GetDataSet(queryString, CommandType.Text, ConnectionState.Closed);
				gridMaster.DataSource = ds.Tables[0];				
				ds.Dispose();
				
				// 리스트를 새로 바인딩 하기 때문에 수정사항이 없습니다.
				// 그래서 수정 관련된  텍스트 박스틑 초기화 시킴
				txtIDX.Text = string.Empty;
				txtType1.Text = string.Empty;
				txtType2.Text = string.Empty;
				txtDate.Text = string.Empty;
				txtOwner.Text = string.Empty;
				txtCount.Text = string.Empty;
				txtMsg.Text = string.Empty;
			
			}
			catch(Exception ex)
			{
				MessageBox.Show(ex.Message);
			}
		}
		
		/// <summary>
		/// 테이블 바인딩 (검색어 O)
		/// </summary>
		/// <param name="_search"></param>
		private void bind(string _search)
		{
			try
			{
				// 첫번째 검색 문자가 대괄호 "[" 인 경우 "[[]" 이렇게 문자열로 인식시켜야 sql문에 적용됩니다.
	                if( _search.Substring(0,1).Equals("["))
	                {
	                	_search = string.Format("%[[]{0}%",_search.Substring(1, _search.Length-1));
	                }
	                else
	                {
					_search = string.Format("%{0}%", _search);
	                }
	                
				string queryString = "SELECT IDX, Type1, Type2, Date1, Owner, Count, Msg FROM [test] WHERE Msg LIKE @isSearch ORDER BY IDX DESC";
				_oleDbHelper.AddParameter("@isSearch", _search);
				
				DataSet ds = new DataSet();
				ds = _oleDbHelper.GetDataSet(queryString, CommandType.Text, ConnectionState.Closed);
				gridMaster.DataSource = ds.Tables[0];				
				ds.Dispose();
				
				// 리스트를 새로 바인딩 하기 때문에 수정사항이 없습니다.
				// 그래서 수정 관련된  텍스트 박스틑 초기화 시킴
				txtIDX.Text = string.Empty;
				txtType1.Text = string.Empty;
				txtType2.Text = string.Empty;
				txtDate.Text = string.Empty;
				txtOwner.Text = string.Empty;
				txtCount.Text = string.Empty;
				txtMsg.Text = string.Empty;
			
			}
			catch(Exception ex)
			{
				MessageBox.Show(ex.Message);
			}
		}
		
		/// <summary>
		/// 선택 로우데이터 뷰
		/// </summary>
		/// <param name="_idx"></param>
		private void getROW(object _idx)
		{
			DbDataReader _reader;
			try{
				
				string queryString = "SELECT IDX, Type1, Type2, Date1, Owner, Count, Msg FROM [test] WHERE [IDX] = @isIDX";
				_oleDbHelper.AddParameter("@isIDX", _idx);
				
				_reader = _oleDbHelper.ExecuteReader(queryString, CommandType.Text, ConnectionState.Open);
				if(_reader.HasRows)
				{
					_reader.Read();
					txtIDX.Text = _idx.ToString();
					txtType1.Text = _reader["Type1"].ToString();
					txtType2.Text = _reader["Type2"].ToString();
					txtDate.Text = string.Format("{0:yyyy-MM-dd}", _reader["Date1"]);
					txtOwner.Text = _reader["Owner"].ToString();
					txtCount.Text = _reader["Count"].ToString();
					txtMsg.Text = _reader["Msg"].ToString();
				}
				_reader.Close();
				_reader.Dispose();
			}
			catch(Exception ex)
			{
				MessageBox.Show(string.Format("catch {0}, {1}",_idx, ex.Message));
			}
			finally{
				
			}
			
		}
		
		#endregion
	} // class(e)
} //namespace(e)

 

 

 

/*
 * SharpDevelop으로 작성되었습니다.
 * 사용자: 지오시스템
 * 날짜: 2019-12-26
 * 시간: 오전 10:50
 * 
 * 상수를 정의 해주는 클래스
 */
using System;

namespace DBSample
{
	/// <summary>
	/// 상수값 정의 클래스
	/// </summary>
	public static class Constants
	{
		/// <summary>
		/// 수학 PI값 ( summary 테스트용)
		/// </summary>
		public const double Pi = 3.14159;
		
		/// <summary>
		/// Access Database파일 Connection String
		/// </summary>
		public const string CONNECTION_ACCESS_DB = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source = ./data/tqdata.accdb;";
		
		
	}
}

 

 

/*
 Copyright  Code4Forever 2012.  All rights reserved.
 Visit code4forever.blogspot.com for more information about us.
 https://steven-england.info/2014/10/28/c-helper-class-database-access-various-providers/
 */

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.Odbc;
using System.Data.OleDb;

namespace DBSample
{
        /// 

        /// dbHelper is a helper class that takes the common data classes and allows you
        /// to specify the provider to use, execute commands, add parameters, and return datasets.
        /// See examples for usage.
        /// 
        public class OleDbHelper
        {
            #region private members
            private string _connectionstring = "";
            private DbConnection _connection;
            private DbCommand _command;
            private DbProviderFactory _factory = null;
            #endregion

            #region properties

            /// 

            /// Gets or Sets the connection string for the database
            /// 
            public string connectionstring
            {
                get
                {
                    return _connectionstring;
                }
                set
                {
                    if (value != "")
                    {
                        _connectionstring = value;
                    }
                }
            }

            /// 

            /// Gets the connection object for the database
            /// 
            public DbConnection connection
            {
                get
                {
                    return _connection;
                }
            }

            /// 

            /// Gets the command object for the database
            /// 
            public DbCommand command
            {
                get
                {
                    return _command;
                }
            }

            #endregion

            # region methods

            /// 

            /// Determines the correct provider to use and sets up the connection and command
            /// objects for use in other methods
            /// 
            /// The full connection string to the database
            /// The enum value of providers from dbutilities.Providers
            public void CreateDBObjects(string connectString, Providers providerList)
            {
                //CreateDBObjects(connectString, providerList, null);
                switch (providerList)
                {
                    case Providers.SqlServer:
                        _factory = SqlClientFactory.Instance;
                        break;
                    case Providers.OleDB:
                        _factory = OleDbFactory.Instance;
                        break;
                    case Providers.ODBC:
                        _factory = OdbcFactory.Instance;
                        break;
                }

                _connection = _factory.CreateConnection();
                _command = _factory.CreateCommand();

                _connection.ConnectionString = connectString;
                _command.Connection = connection;
            }

            #region parameters

            /// <summary>
            /// 데이타 타입 정의
            /// 날자 데이타 넣을때 필요
            /// </summary>
            /// <param name="name"></param>
            /// <param name="value"></param>
            /// <param name="type"></param>
            /// <returns></returns>
            public int AddParameter(string name, object value, DbType type)
            {
                DbParameter parm = _factory.CreateParameter();                
                parm.ParameterName = name;
                parm.DbType = type;
                parm.Value = value;
                return command.Parameters.Add(parm);
            }

            /// Creates a parameter and adds it to the command object
            /// 
            /// The parameter name
            /// The paremeter value
            /// 
            public int AddParameter(string name, object value)
            {
                DbParameter parm = _factory.CreateParameter();                
                parm.ParameterName = name;
                parm.Value = value;
                return command.Parameters.Add(parm);
            }

            /// 

            /// Creates a parameter and adds it to the command object
            /// 
            /// A parameter object
            /// 
            public int AddParameter(DbParameter parameter)
            {
                return command.Parameters.Add(parameter);
            }

            #endregion

            #region transactions

            /// 

            /// Starts a transaction for the command object
            /// 
            private void BeginTransaction()
            {
                if (connection.State == System.Data.ConnectionState.Closed)
                {
                    connection.Open();
                }
                command.Transaction = connection.BeginTransaction();
            }

            /// 

            /// Commits a transaction for the command object
            /// 
            private void CommitTransaction()
            {
                command.Transaction.Commit();
                connection.Close();
            }

            /// 

            /// Rolls back the transaction for the command object
            /// 
            private void RollbackTransaction()
            {
                command.Transaction.Rollback();
                connection.Close();
            }

            #endregion

            #region execute database functions

            /// 

            /// Executes a statement that does not return a result set, such as an INSERT, UPDATE, DELETE, or a data definition statement
            /// 
            /// The query, either SQL or Procedures
            /// The command type, text, storedprocedure, or tabledirect
            /// The connection state
            /// An integer value
            public int ExecuteNonQuery(string query, CommandType commandtype, ConnectionState connectionstate)
            {
                command.CommandText = query;
                command.CommandType = commandtype;
                int i = -1;
                try
                {
                    if (connection.State == System.Data.ConnectionState.Closed)
                    {
                        connection.Open();
                    }

                    BeginTransaction();

                    i = command.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    RollbackTransaction();
                    throw (ex);
                }
                finally
                {
                    CommitTransaction();
                    command.Parameters.Clear();

                    if (connection.State == System.Data.ConnectionState.Open)
                    {
                        connection.Close();
                        connection.Dispose();
                        //command.Dispose();
                    }
                }

                return i;
            }

            /// 

            /// Executes a statement that returns a single value. 
            /// If this method is called on a query that returns multiple rows and columns, only the first column of the first row is returned.
            /// 
            /// The query, either SQL or Procedures
            /// The command type, text, storedprocedure, or tabledirect
            /// The connection state
            /// An object that holds the return value(s) from the query
            public object ExecuteScaler(string query, CommandType commandtype, ConnectionState connectionstate)
            {
                command.CommandText = query;
                command.CommandType = commandtype;
                object obj = null;
                try
                {
                    if (connection.State == System.Data.ConnectionState.Closed)
                    {
                        connection.Open();
                    }

                    BeginTransaction();
                    obj = command.ExecuteScalar();
                }
                catch (Exception ex)
                {
                    RollbackTransaction();
                    throw (ex);
                }
                finally
                {
                    CommitTransaction();
                    command.Parameters.Clear();

                    if (connection.State == System.Data.ConnectionState.Open)
                    {
                        connection.Close();
                        connection.Dispose();
                        command.Dispose();
                    }
                }

                return obj;
            }

            /// 

            /// Executes a SQL statement that returns a result set.
            /// 
            /// The query, either SQL or Procedures
            /// The command type, text, storedprocedure, or tabledirect
            /// The connection state
            /// A datareader object
            public DbDataReader ExecuteReader(string query, CommandType commandtype, ConnectionState connectionstate)
            {
                command.CommandText = query;
                command.CommandType = commandtype;
                DbDataReader reader = null;
                try
                {
                    if (connection.State == System.Data.ConnectionState.Closed)
                    {
                        connection.Open();
                    }
                    if (connectionstate == System.Data.ConnectionState.Open)
                    {
                        reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                    }
                    else
                    {
                        reader = command.ExecuteReader();
                    }
                }
                catch (Exception ex)
                {
                    throw (ex);
                }
                finally
                {
                    command.Parameters.Clear();
                }

                return reader;
            }

            /// 

            /// Generates a dataset
            /// 
            /// The query, either SQL or Procedures
            /// The command type, text, storedprocedure, or tabledirect
            /// The connection state
            /// A dataset containing data from the database
            public DataSet GetDataSet(string query, CommandType commandtype, ConnectionState connectionstate)
            {
                DbDataAdapter adapter = _factory.CreateDataAdapter();
                command.CommandText = query;
                command.CommandType = commandtype;
                adapter.SelectCommand = command;
                DataSet ds = new DataSet();
                try
                {
                    adapter.Fill(ds);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    command.Parameters.Clear();

                    if (connection.State == System.Data.ConnectionState.Open)
                    {
                        connection.Close();
                        connection.Dispose();
                        command.Dispose();
                    }
                }
                return ds;
            }

            #endregion

            #endregion

            #region enums

            /// 

            /// A list of data providers
            /// 
            public enum Providers
            {
                SqlServer,
                OleDB,
                ODBC,
                Oracle,
            }

            #endregion
        }
}