Created
June 13, 2013 07:31
-
-
Save ShaneGowland/5771850 to your computer and use it in GitHub Desktop.
Abstraction class for accessing Microsoft Access (.accdb) files. Instantiate the class by passing the path to the database file to the constructor. Use the instance.Query() method to perform SQL queries. Returns a string array.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
using System; | |
using System.Collections.Generic; | |
using System.Linq; | |
using System.Text; | |
using System.Data.OleDb; | |
public class DatabaseConnection | |
{ | |
//Property that stores the path to the open file | |
public string FilePath {get; set;} | |
public DatabaseConnection(string path) | |
{ | |
//Ensure the active database actually exists | |
if (System.IO.File.Exists(path)) { | |
FilePath = path.Replace("\\","\\\\"); | |
} else { | |
throw new Exception(); | |
} | |
} | |
public string[] Query(String queryString) | |
{ | |
//Create a list to store each value returned | |
List<string> resultsList = new List<string>(); | |
//The connection string: this shouldn't change. | |
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;data source=" + FilePath; | |
//Get all the elements | |
using (OleDbConnection connection = new OleDbConnection(connectionString)) | |
{ | |
// Create the Command and Parameter objects. | |
OleDbCommand command = new OleDbCommand(queryString, connection); | |
try | |
{ | |
connection.Open(); | |
OleDbDataReader reader = command.ExecuteReader(); | |
int iRecNbr = 1; | |
while (reader.Read()) | |
{ | |
String sRecord = string.Empty; | |
for (int i = 0; i < reader.FieldCount; i++) | |
{ | |
sRecord += string.Format("{0}|", reader[i].ToString()); | |
} | |
//Add this record to the list | |
char[] charsToTrim = { ',', '-', ' ' }; | |
resultsList.Add(sRecord.TrimEnd(charsToTrim)); | |
iRecNbr++; | |
} | |
//Close the reader; because the compiler isn't smart enough to do this automagically. | |
reader.Close(); | |
//Return the results list, as an array. | |
string[] resultsArray = new string[resultsList.Count]; | |
resultsArray = resultsList.ToArray(); | |
return resultsArray; | |
} | |
catch (Exception ex) | |
{ | |
Console.WriteLine(ex.Message); | |
return new string[1]; | |
} | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment