How would I use a Microsoft Access (.accdb
) database in C# (console application, not web asp.net)? From what I've read, I'll need to use ADO.NET
, but I'm really at a loss as to how to do this in a C# console application. In PHP with MySQL
, I'd be looking for mysqli_construct
Could anyone point me towards a tutorial or documentation that would help me with that? I'm trying to use it as a way to store and access data for my non-web, non ASP.NET application, if that changes anything.
Thanks!
You can then add a new C source file and replace it with this content. Using the ODBC APIs SQLAllocHandle, SQLSetConnectAttr, and SQLDriverConnect, you should be able to initialize and establish a connection to your database.
Microsoft SQL Server This database was launched in 1989 and it is also one of the most popular relational database management systems (RDBMS) in the world. It is written in C and C++ and supports structured query language.
Jasmine/C is a C-based database proqramninq language that allows the handling of persistent objects in Jasmine databases. The language is used to write methods for objects and application programs. Both navigational and associative access to objects are supported.
You can code SQL statements in a C or C++ program wherever you can use executable statements. Each SQL statement in a C or C++ program must begin with EXEC SQL and end with a semicolon (;). The EXEC and SQL keywords must appear on one line, but the remainder of the statement can appear on subsequent lines.
See this walkthrough for using ADO.NET to edit an Access database.
Now while that example is using a web application, don't worry, the classes being used are still applicable for a console application as well.
The main classes you will need to use are:
The documentation of the classes above all have code samples that are console app examples.
Below is a code snippet for use in a console app (remember to add the: using System.Data.OleDb;
string connectionString =
@"Provider=Microsoft.Jet.OLEDB.4.0;" +
@"Data Source=C:\path\to\your\database.mdb;" +
@"User Id=;Password=;";
string queryString = "SELECT Foo FROM Bar";
using (OleDbConnection connection = new OleDbConnection(connectionString))
using (OleDbCommand command = new OleDbCommand(queryString, connection))
{
try
{
connection.Open();
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader[0].ToString());
}
reader.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
Also, just noticed you explicitly said an .accdb database. For this grab the Microsoft Access Database Engine 2010 Redistributable. The provider in the connection string would then need to be changed to: Microsoft.ACE.OLEDB.12.0
(see notes at that link for more info).
Just use System.OleDb
. Here is a small class that I use
First of all, place the database fle inside App_Data
web.config
<connectionStrings>
<add name="MyConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\DatabaseName.accdb" providerName="System.Data.OleDb"/>
</connectionStrings>
App_Code/DataAccess.cs
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
public class DataAccess
{
#region "Public Methods"
public static DataTable GetTableFromQuery(string query, Dictionary<string, object> parameters, CommandType commandType)
{
DataTable dataTable = new DataTable();
using (OleDbConnection conn = GetConnection()) {
using (OleDbCommand cmd = new OleDbCommand(query, conn)) {
cmd.CommandType = commandType;
if (parameters != null) {
foreach (KeyValuePair<string, object> parameter in parameters) {
cmd.Parameters.AddWithValue(parameter.Key, parameter.Value);
}
}
using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd)) {
adapter.Fill(dataTable);
}
}
}
return dataTable;
}
public static object GetSingleObjectFromQuery(string query, Dictionary<string, object> parameters, CommandType commandType)
{
object value = null;
using (OleDbConnection conn = GetConnection()) {
using (OleDbCommand cmd = new OleDbCommand(query, conn)) {
cmd.CommandType = commandType;
if (parameters != null) {
foreach (KeyValuePair<string, object> parameter in parameters) {
cmd.Parameters.AddWithValue(parameter.Key, parameter.Value);
}
}
conn.Open();
using (OleDbDataReader reader = cmd.ExecuteReader()) {
while (reader.Read()) {
value = reader.GetValue(0);
}
}
}
}
return value;
}
public static int ExecuteNonQuery(string query, Dictionary<string, object> parameters, CommandType commandType)
{
int value = 1;
using (OleDbConnection conn = GetConnection()) {
using (OleDbCommand cmd = new OleDbCommand(query, conn)) {
cmd.CommandType = commandType;
if (parameters != null) {
foreach (KeyValuePair<string, object> parameter in parameters) {
cmd.Parameters.AddWithValue(parameter.Key, parameter.Value);
}
}
cmd.Connection.Open();
value = cmd.ExecuteNonQuery();
}
}
return value;
}
#endregion
#region "Private Methods"
private static OleDbConnection GetConnection()
{
string ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("MyConnectionString").ConnectionString;
return new OleDbConnection(ConnectionString);
}
#endregion
}
var myTable = DataAccess.GetTableFromQuery("SELECT * FROM TableName", null, CommandType.Text);
Take a look at the OLEDB Namespace there are some simple examples here and here.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With