Query Microsoft Access MDB Database using LINQ and C#

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.

What you want is a LINQ to ODBC provider, or a LINQ to JET/OLEDB provider.

Out of the box, MS doesn't make one. There may be a 3rd party who does.

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:

1. not being able to save data.
2. not being able to drag and drop objects onto the dbml

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