I have a *.MDB database file, and I am wondering if it is possible or recommended to work against it using LINQ in C#. I am also wondering what some simple examples would look like.
I don't know a lot about LINQ, but my requirements for this task are pretty simple (I believe). The user will be passing me a file path to Microsoft Access MDB database and I would like to use LINQ to add rows to one of the tables within the database.
Actually I recently (today) discovered that you can access an Access database with LinqToSql. It must be in the 2002 or newer format, you will not be able to drag and drop the tables to your datacontext so either manually create the objects in your dbml or you can use SQL Server Migration for Access to move it to a sql server and then drag and drop all you want. When you want to actually create the context pass it an OleDbConnection. Use your standard Jet.OLEDB.4.0 connection string on the OleDbConnection and you are good to go. Not sure of the limitation this may incurr though. I just did a quick sample and did an OrderBy without issue.
I wrote a small sample program to test this out with David's answer. You'll need to make an access database and manually create the DBML for Linq-to-SQL, as you cannot drag 'n drop them.
Inserts fail, citing Missing semicolon (;) at end of SQL statement. but queries seem to work alright.
using System;
using System.Collections.Generic;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using Linq2Access.Data;
namespace Linq2Access
{
class Program
{
static readonly string AppPath = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location);
static readonly string DbPath = Path.Combine(AppPath, "Data", "database.accdb");
static readonly string DbConnString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + DbPath + "';Persist Security Info=False;";
static void Main(string[] args)
{
if (!File.Exists(DbPath))
throw new Exception("Database file does not exist!");
using (OleDbConnection connection = new OleDbConnection(DbConnString))
using (DataRepositoryDataContext db = new DataRepositoryDataContext(connection))
{
List<dbProject> projects = new List<dbProject>();
for (int i = 1; i <= 10; i++)
{
dbProject p = new dbProject() { Title = "Project #" + i };
for (int j = 1; j <= 10; j++)
{
dbTask t = new dbTask() { Title = "Task #" + (i * j) };
p.dbTasks.Add(t);
}
projects.Add(p);
}
try
{
//This will fail to submit
db.dbProjects.InsertAllOnSubmit(projects);
db.SubmitChanges();
Console.WriteLine("Write succeeded! {0} projects, {1} tasks inserted",
projects.Count,
projects.Sum(x => x.dbTasks.Count));
}
catch(Exception ex)
{
Console.WriteLine("Write FAILED. Details:");
Console.WriteLine(ex);
Console.WriteLine();
}
try
{
//However, if you create the items manually in Access they seem to query fine
var projectsFromDb = db.dbProjects.Where(x => x.Title.Contains("#1"))
.OrderBy(x => x.ProjectID)
.ToList();
Console.WriteLine("Query succeeded! {0} Projects, {1} Tasks",
projectsFromDb.Count,
projectsFromDb.Sum(x => x.dbTasks.Count));
}
catch (Exception ex)
{
Console.WriteLine("Query FAILED. Details:");
Console.WriteLine(ex);
Console.WriteLine();
}
Console.WriteLine();
Console.WriteLine("Press any key to continue...");
Console.ReadKey();
}
}
}
}
You can use a DataSet. There are linq extensions that will allow you to query the data with all that LINQ goodness we have become use to :)
eICATDataSet.ICSWSbuDataTable tbl = new eICATDataSet.ICSWSbuDataTable();
ICSWSbuTableAdapter ta = new ICSWSbuTableAdapter();
ta.Fill(tbl);
var res = tbl.Select(x => x.ProcedureDate.Year == 2010);
I have seen this question a lot and in several fora. I made a go at it and here is a complete answer for those who have been looking at it.
LinQ was not made for Access. However, many of the queries will work with Access, including delete procedure. So, according to me, there are only 2 crucial deficiencies when working with Access, which are:
Insert will fail with the error "missing semicolon (;)". This is because LinQ save procedure was made to save data and retrieve the primary key ID of the record saved in one go. We know that you cannot execute multiple SQL statements in Access, so that is the reason for that failure.
Update will fail with the error "record not found". An update procedure will of cause look for the record to be updated then update it. I cannot tell why it wouldn't find it, when normal LinQ query to find a record works fine.
Because there is so much benefit to use LinQ, I figured out how to work around the deficiency, while enjoy the other benefits throughout my application. This is how (NB: My codes are in VB.net, but you can convert if required):
Create the LinQ to SQL (.dbml) class to manage your LinQ against the access database, and a way to manager your save procedure. Below is the full procedures of what I created and I now work with LinQ to Access without any problems:
Add a DataGridView on a form. Add buttons for Add, Edit & Delete
Code to fill the grid:
Private Sub ResetForm() Try Using db As New AccessDataClassesDataContext(ACCCon) Dim rows = (From row In db.AccountTypes Where row.AccountTypeID > 1 Order By row.AccountTypeID Ascending Select row).ToList() Me.DataGridView1.DataSource = rows End Using Catch ex As Exception MessageBox.Show("Error: " & vbCr & ex.ToString, "Data Error", MessageBoxButtons.OK) End Try End Sub
DetailForm
Code to set control values
Private Sub ResetForm()
Try If _accountTypeID = 0 Then Exit Sub End If Using db As New AccessDataClassesDataContext(ACCCon) 'Dim rows = (From row In db.AccountTypes ' Where row.AccountTypeID = _accountTypeID ' Order By row.AccountTypeID Ascending ' Select row.AccountTypeID, row.AccountType, row.LastUpdated).ToList() Dim rows = (From row In db.AccountTypes Where row.AccountTypeID = _accountTypeID Select row).ToList() For Each s In rows Me.AccountTypeIDTextBox.Text = s.AccountTypeID Me.myGuidTextBox.Text = s.myGuid Me.AccountTypeTextBox.Text = s.AccountType Me.AcHeadIDTextBox.Text = s.AcHeadID Me.DescriptionTextBox.Text = s.Description Me.LastUpdatedDateTimePicker.Value = s.LastUpdated Next End Using Catch ex As Exception End Try End Sub
LinQToSQLClass
You will have to add the data objects to the dbml manually since you cannot drag and drop when using Access. Also note that you will have to set all the properties of the fields correctly in the properties windows. Several properties are not set when you add the fields.
Code to Save
Public Function SaveAccountType(Optional ByVal type As String = "Close") As Boolean
Dim success As Boolean = False Dim row As New AccountType Using db As New AccessDataClassesDataContext(ACCCon) If _accountTypeID > 0 Then row = (From r In db.AccountTypes Where r.AccountTypeID = _accountTypeID).ToList()(0) If String.IsNullOrEmpty(row.AccountTypeID) Then MessageBox.Show("Requested record not found", "Update Customer Error") Return success End If End If Try With row .myGuid = Me.myGuidTextBox.Text .AccountType = Me.AccountTypeTextBox.Text .Description = Me.DescriptionTextBox.Text .AcHeadID = Me.AcHeadIDTextBox.Text .LastUpdated = Date.Parse(Date.Now()) End With If _accountTypeID = 0 Then db.AccountTypes.InsertOnSubmit(row) db.SubmitChanges() success = True Catch ex As Exception MessageBox.Show("Error saving to Customer: " & vbCr & ex.ToString, "Save Data Error") End Try End Using Return success End Function
Now replace these two lines:
If _accountTypeID = 0 Then db.AccountTypes.InsertOnSubmit(row) db.SubmitChanges()
with something like this:
Dim cmd As IDbCommand cmd = Me.Connection.CreateCommand() cmd.Transaction = Me.Transaction cmd.CommandText = query If myGuid.Trim.Length < 36 Then myGuid = UCase(System.Guid.NewGuid.ToString()) cmd.Parameters.Add(New OleDbParameter("myGuid", row.myGuid)) cmd.Parameters.Add(New OleDbParameter("AccountType", row.AccountType)) cmd.Parameters.Add(New OleDbParameter("Description", row.Description)) cmd.Parameters.Add(New OleDbParameter("AcHeadID", row.AcHeadID)) cmd.Parameters.Add(New OleDbParameter("LastUpdated", Date.Now)) If AccountTypeID > 0 Then cmd.Parameters.Add(New OleDbParameter("AccountTypeID", row.AccountTypeID)) If Connection.State = ConnectionState.Closed Then Connection.Open() result = cmd.ExecuteNonQuery() cmd = Me.Connection.CreateCommand() cmd.Transaction = Me.Transaction cmd.CommandText = "SELECT @@IDENTITY" result = Convert.ToInt32(cmd.ExecuteScalar())
The last part of the code above is what gets you the ID of the record saved. Personally, I usually make that an option, because I don't need it in most of the cases, so I don't need to add that overhead of fetching back data every time a record is saved, I am happy just to know a record was saved.
That is the overhead added to LinQ, which causes Insert to fail with Access. Is it really necessary to have it? I don't think so.
You may have noted that I normally put my Update and Insert procedures together, so that saves me time and has address both the Insert & Update procedures in one go.
Code for Delete:
Private Sub DelButton_Click(sender As Object, e As EventArgs) Handles DelButton.Click Using db As New AccessDataClassesDataContext(ACCCon) Dim AccountTypeID As Integer = Me.DataGridView1.CurrentRow.Cells(0).Value Dim row = From r In db.AccountTypes Where r.AccountTypeID = AccountTypeID For Each detail In row db.AccountTypes.DeleteOnSubmit(detail) Next Try db.SubmitChanges() Catch ex As Exception ' Provide for exceptions. MsgBox(ex) End Try End Using End Sub
Now you can enjoy LinQ to Access! Happy coding :)
LINQ to SQL only works for SQL Server databases. What you need is the Microsoft Entity Framework. This makes object oriented access to your mdb. From this you can run LINQ queries.
http://msdn.microsoft.com/en-us/library/aa697427(vs.80).aspx
