使用 LINQ 和 C# 查询 Microsoft Access MDB 数据库

Question

提问by Matthew Ruston

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.

我有一个 *.MDB 数据库文件，我想知道是否可以或建议使用 C# 中的 LINQ 来处理它。我也想知道一些简单的例子会是什么样子。

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.

我对 LINQ 了解不多，但我对这项任务的要求非常简单（我相信）。用户将向我传递 Microsoft Access MDB 数据库的文件路径，我想使用 LINQ 将行添加到数据库中的一个表中。

Answer 1

采纳答案by FlySwat

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

您需要的是 LINQ to ODBC 提供程序，或 LINQ to JET/OLEDB 提供程序。

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

开箱即用，MS 不生产。可能会有第三者这样做。

Answer 2

回答by GeekyMonkey

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.

LINQ to SQL 仅适用于 SQL Server 数据库。您需要的是 Microsoft 实体框架。这使得面向对象的访问您的 mdb。从此您可以运行 LINQ 查询。

http://msdn.microsoft.com/en-us/library/aa697427(vs.80).aspx

Answer 3

回答by David

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.

实际上，我最近（今天）发现您可以使用 LinqToSql 访问 Access 数据库。它必须是 2002 年或更新的格式，您将无法将表拖放到您的数据上下文中，因此您可以在 dbml 中手动创建对象，或者您可以使用 SQL Server Migration for Access 将其移动到 sql server 和然后拖放所有你想要的。当您想要实际创建上下文时，将它传递给 OleDbConnection。在 OleDbConnection 上使用标准的 Jet.OLEDB.4.0 连接字符串，一切顺利。不确定这可能会产生什么限制。我只是做了一个快速的样本，并没有问题地做了一个 OrderBy。

Answer 4

回答by Quinten Miller

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 :)

您可以使用数据集。有 linq 扩展可以让您使用我们已经习惯的所有 LINQ 优点来查询数据:)

eICATDataSet.ICSWSbuDataTable tbl = new eICATDataSet.ICSWSbuDataTable();

ICSWSbuTableAdapter ta = new ICSWSbuTableAdapter();
ta.Fill(tbl);

var res = tbl.Select(x => x.ProcedureDate.Year == 2010);

Answer 5

回答by jocull

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.

我写了一个小示例程序来测试大卫的答案。您需要创建一个访问数据库并为 Linq-to-SQL 手动创建 DBML，因为您无法拖放它们。

Inserts fail, citing Missing semicolon (;) at end of SQL statement.but queries seem to work alright.

插入失败，引用Missing semicolon (;) at end of SQL statement.但查询似乎工作正常。

Access database tables for Program

程序的 Access 数据库表

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();
            }
        }
    }
}

Answer 6

回答by Hannington Mambo

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:

LinQ 不是为 Access 设计的。但是，许多查询都适用于 Access，包括删除过程。因此，据我所知，使用 Access 时只有两个关键缺陷，它们是：

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

无法保存数据。
无法将对象拖放到 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.

插入将失败并显示错误“缺少分号 (;)”。这是因为 LinQ 保存程序是为了保存数据和检索保存的记录的主键 ID 一次。我们知道您不能在 Access 中执行多个 SQL 语句，所以这就是失败的原因。

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.

更新将失败并显示错误“未找到记录”。更新过程会查找要更新的记录，然后更新它。当查找记录的正常 LinQ 查询工作正常时，我不知道为什么它找不到它。

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):

因为使用 LinQ 有很多好处，所以我想出了如何解决不足之处，同时在整个应用程序中享受其他好处。这是如何（注意：我的代码在 VB.net 中，但您可以根据需要进行转换）：

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:

创建 LinQ to SQL (.dbml) 类以根据访问数据库管理您的 LinQ，以及管理您的保存过程的方法。以下是我创建的完整程序，我现在使用 LinQ to Access 没有任何问题：

Add a DataGridViewon a form. Add buttons for Add, Edit & Delete

DataGridView在表单上添加一个。添加用于添加、编辑和删除的按钮

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

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

私有子 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

LinQToSQL类

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.

您必须手动将数据对象添加到 dbml，因为在使用 Access 时无法拖放。另请注意，您必须在属性窗口中正确设置字段的所有属性。添加字段时未设置多个属性。

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

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()

            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())

        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.

上面代码的最后一部分是让您获得所保存记录的 ID。就我个人而言，我通常将其作为一个选项，因为在大多数情况下我不需要它，所以我不需要添加每次保存记录时取回数据的开销，我很高兴知道一个记录已保存。

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.

这是添加到 LinQ 的开销，它导致 Insert 与 Access 失败。真的有必要拥有吗？我不这么认为。

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

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 Access 了！快乐编码:)

使用 LINQ 和 C# 查询 Microsoft Access MDB 数据库

提问by Matthew Ruston

采纳答案by FlySwat

回答by GeekyMonkey

回答by David

回答by Quinten Miller

回答by jocull

回答by Hannington Mambo

相关推荐

最近更新

标签

使用 LINQ 和 C# 查询 Microsoft Access MDB 数据库

提问by Matthew Ruston

采纳答案by FlySwat

回答by GeekyMonkey

回答by David

回答by Quinten Miller

回答by jocull

回答by Hannington Mambo

相关推荐

在 C#/.NET 中将参数标记为不可为空？

C# 反射索引属性

在 C# 中遍历 XML 树

C# DatagridView 不显示错误图标或错误文本？

相关推荐

最近更新

标签