Community Learn Tools Library Leisure
search
English
Home > Database > Mysql Tutorial > 让ADO.NET Entity Framework 支持ACCESS数据库

让ADO.NET Entity Framework 支持ACCESS数据库

WBOY
Release： 2016-06-07 15:42:53
Original
1025 people have browsed it

如写的不好请见谅，本人水平有限。 个人简历及水平：。 http://www.cnblogs.com/hackdragon/p/3662599.html 接到一个程序和网页交互的项目，用ADO.NET Entity Framework （以下简称EF）很快就搞完了，但是对方的空间提供的MSSQL数据库比较昂贵，所以就采用AC

如写的不好请见谅，本人水平有限。

个人简历及水平：。 http://www.cnblogs.com/hackdragon/p/3662599.html

接到一个程序和网页交互的项目，用ADO.NET Entity Framework （以下简称EF）很快就搞完了，但是对方的空间提供的MSSQL数据库比较昂贵，所以就采用ACCESS数据库，但是我查了资料发现 EF不支持Access数据库，（以前觉得LINQ TO SQL 不支持 这个应该支持），写完的代码不想用OLEDB在写了，于是网上一顿查，试验了ALINQ和其他很多的，总是不能符合项目的需要。不是更新不行就算插入失败，要不就是经常查询错误。最后没办法，我自己决定写一个实体支持ACCESS数据库，我觉得懒人应该有需要这个的，当然大侠们估计有更好的办法来更懒一些。

懒人第一步：

因为VS的实体生成器不支持ACCESS数据库，所以无法生成代码，但是想快速开发项目，那么你可以用SQL数据库先来设计，然后导出数据库到ACCESS数据库，这样做的目的是让生成器可以生成我们所需要的代码。注意设计数据库字段的时候要考虑他们的兼容性。

勤快第二步：

开始写代码了，原始的上下文代码如下：

<span>#region</span> 上下文
    
    <span>///</span> <span><summary></summary></span>
    <span>///</span><span> 没有元数据文档可用。
    </span><span>///</span> <span></span>
    <span>public</span> <span>partial</span> <span>class</span><span> SqlDoorEntities1 : ObjectContext
    {
        </span><span>#region</span> 构造函数
    
        <span>///</span> <span><summary></summary></span>
        <span>///</span><span> 请使用应用程序配置文件的“SqlDoorEntities1”部分中的连接字符串初始化新 SqlDoorEntities1 对象。
        </span><span>///</span> <span></span>
        <span>public</span> SqlDoorEntities1() : <span>base</span>(<span>"</span><span>name=SqlDoorEntities1</span><span>"</span>, <span>"</span><span>SqlDoorEntities1</span><span>"</span><span>)
        {
            OnContextCreated();
        }
    
        </span><span>///</span> <span><summary></summary></span>
        <span>///</span><span> 初始化新的 SqlDoorEntities1 对象。
        </span><span>///</span> <span></span>
        <span>public</span> SqlDoorEntities1(<span>string</span> connectionString) : <span>base</span>(connectionString, <span>"</span><span>SqlDoorEntities1</span><span>"</span><span>)
        {
            OnContextCreated();
        }
    
        </span><span>///</span> <span><summary></summary></span>
        <span>///</span><span> 初始化新的 SqlDoorEntities1 对象。
        </span><span>///</span> <span></span>
        <span>public</span> SqlDoorEntities1(EntityConnection connection) : <span>base</span>(connection, <span>"</span><span>SqlDoorEntities1</span><span>"</span><span>)
        {
            OnContextCreated();
        }
    
        </span><span>#endregion</span>
    
        <span>#region</span> 分部方法
    
        <span>partial</span> <span>void</span><span> OnContextCreated();
    
        </span><span>#endregion</span>
    
        <span>#region</span> ObjectSet 属性
    
        <span>///</span> <span><summary></summary></span>
        <span>///</span><span> 没有元数据文档可用。
        </span><span>///</span> <span></span>
        <span>public</span> ObjectSet<cmdmsg><span> CmdMsg
        {
            </span><span>get</span><span>
            {
                </span><span>if</span> ((_CmdMsg == <span>null</span><span>))
                {
                    _CmdMsg </span>= <span>base</span>.CreateObjectSet<cmdmsg>(<span>"</span><span>CmdMsg</span><span>"</span><span>);
                }
                </span><span>return</span><span> _CmdMsg;
            }
        }
        </span><span>private</span> ObjectSet<cmdmsg><span> _CmdMsg;
    
        </span><span>///</span> <span><summary></summary></span>
        <span>///</span><span> 没有元数据文档可用。
        </span><span>///</span> <span></span>
        <span>public</span> ObjectSet<door><span> Door
        {
            </span><span>get</span><span>
            {
                </span><span>if</span> ((_Door == <span>null</span><span>))
                {
                    _Door </span>= <span>base</span>.CreateObjectSet<door>(<span>"</span><span>Door</span><span>"</span><span>);
                }
                </span><span>return</span><span> _Door;
            }
        }
        </span><span>private</span> ObjectSet<door><span> _Door;
    
        </span><span>///</span> <span><summary></summary></span>
        <span>///</span><span> 没有元数据文档可用。
        </span><span>///</span> <span></span>
        <span>public</span> ObjectSet<manager><span> Manager
        {
            </span><span>get</span><span>
            {
                </span><span>if</span> ((_Manager == <span>null</span><span>))
                {
                    _Manager </span>= <span>base</span>.CreateObjectSet<manager>(<span>"</span><span>Manager</span><span>"</span><span>);
                }
                </span><span>return</span><span> _Manager;
            }
        }
        </span><span>private</span> ObjectSet<manager><span> _Manager;
    
        </span><span>///</span> <span><summary></summary></span>
        <span>///</span><span> 没有元数据文档可用。
        </span><span>///</span> <span></span>
        <span>public</span> ObjectSet<users><span> Users
        {
            </span><span>get</span><span>
            {
                </span><span>if</span> ((_Users == <span>null</span><span>))
                {
                    _Users </span>= <span>base</span>.CreateObjectSet<users>(<span>"</span><span>Users</span><span>"</span><span>);
                }
                </span><span>return</span><span> _Users;
            }
        }
        </span><span>private</span> ObjectSet<users><span> _Users;

        </span><span>#endregion</span>

        <span>#region</span> AddTo 方法
    
        <span>///</span> <span><summary></summary></span>
        <span>///</span><span> 用于向 CmdMsg EntitySet 添加新对象的方法，已弃用。请考虑改用关联的 ObjectSet<T> 属性的 .Add 方法。
        </span><span>///</span> <span></span>
        <span>public</span> <span>void</span><span> AddToCmdMsg(CmdMsg cmdMsg)
        {
            </span><span>base</span>.AddObject(<span>"</span><span>CmdMsg</span><span>"</span><span>, cmdMsg);
        }
    
        </span><span>///</span> <span><summary></summary></span>
        <span>///</span><span> 用于向 Door EntitySet 添加新对象的方法，已弃用。请考虑改用关联的 ObjectSet<T> 属性的 .Add 方法。
        </span><span>///</span> <span></span>
        <span>public</span> <span>void</span><span> AddToDoor(Door door)
        {
            </span><span>base</span>.AddObject(<span>"</span><span>Door</span><span>"</span><span>, door);
        }
    
        </span><span>///</span> <span><summary></summary></span>
        <span>///</span><span> 用于向 Manager EntitySet 添加新对象的方法，已弃用。请考虑改用关联的 ObjectSet<T> 属性的 .Add 方法。
        </span><span>///</span> <span></span>
        <span>public</span> <span>void</span><span> AddToManager(Manager manager)
        {
            </span><span>base</span>.AddObject(<span>"</span><span>Manager</span><span>"</span><span>, manager);
        }
    
        </span><span>///</span> <span><summary></summary></span>
        <span>///</span><span> 用于向 Users EntitySet 添加新对象的方法，已弃用。请考虑改用关联的 ObjectSet<T> 属性的 .Add 方法。
        </span><span>///</span> <span></span>
        <span>public</span> <span>void</span><span> AddToUsers(Users users)
        {
            </span><span>base</span>.AddObject(<span>"</span><span>Users</span><span>"</span><span>, users);
        }

        </span><span>#endregion</span><span>

    }

    </span><span>#endregion</span></users></users></users></manager></manager></manager></door></door></door></cmdmsg></cmdmsg></cmdmsg>
Copy after login

ObjectContext 继承于IDisposable 那么我写一个自己的 ObjectContext 这样的类 我给他起个名字叫EFToAccess 那么多 构造方法 我们就需要2个一个 是 给定的连接字符串 一个是默认从webconfig中读取的链接字符串就可以了。本人偷懒，直接读取指定的路径了。数据库的简单读写可能都依赖一个where查询，那么怎么实现自己的where查询就很关键，于是我看资料研究了2天Lambda Expression 表达式。最后还是看了 博客园的一篇 扩展LINQ to SQL：使用Lambda Expression批量删除数据才会用，现在也不是很明白，懒人就是拿来主义，不怎么消化，我现在也没多少时间消化知识，估计这样的人也不少吧。下面是我自己用的的方法，利用VS生成的代码 2个替换1个删除搞定 （ObjectContext替换“你自己的类名我的是SqlDoorEntities”，ObjectSet替换成IEnumerable，删除无用的构造函数）

<span>public</span> <span>class</span><span> SqlDoorEntities : EFToAccess
    {
        </span><span>public</span><span> SqlDoorEntities()</span>:base("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" <br>+<em>AppDomain.CurrentDomain.BaseDirectory + "bin\\DataDoor.mdb")</em>
Copy after login
<span>
        {
        }
        </span><span>#region</span> IEnumerable 属性
        <span>///</span> <span><summary></summary></span>
        <span>///</span><span> 没有元数据文档可用。
        </span><span>///</span> <span></span>

        <span>public</span> IEnumerable<cmdmsg><span> CmdMsg
        {
            </span><span>get</span><span>
            {
                </span><span>if</span> ((_CmdMsg == <span>null</span><span>))
                {
                    _CmdMsg </span>= <span>base</span>.CreateObjectSet<cmdmsg>(<span>"</span><span>CmdMsg</span><span>"</span><span>);
                }
                </span><span>return</span><span> _CmdMsg;
            }
        }
        </span><span>private</span> IEnumerable<cmdmsg><span> _CmdMsg;

        </span><span>///</span> <span><summary></summary></span>
        <span>///</span><span> 没有元数据文档可用。
        </span><span>///</span> <span></span>
        <span>public</span> IEnumerable<door><span> Door
        {
            </span><span>get</span><span>
            {
                </span><span>if</span> ((_Door == <span>null</span><span>))
                {
                    _Door </span>= <span>base</span>.CreateObjectSet<door>(<span>"</span><span>Door</span><span>"</span><span>);
                }
                </span><span>return</span><span> _Door;
            }
        }
        </span><span>private</span> IEnumerable<door><span> _Door;

        </span><span>///</span> <span><summary></summary></span>
        <span>///</span><span> 没有元数据文档可用。
        </span><span>///</span> <span></span>
        <span>public</span> IEnumerable<manager><span> Manager
        {
            </span><span>get</span><span>
            {
                </span><span>if</span> ((_Manager == <span>null</span><span>))
                {
                    _Manager </span>= <span>base</span>.CreateObjectSet<manager>(<span>"</span><span>Manager</span><span>"</span><span>);
                }
                </span><span>return</span><span> _Manager;
            }
        }
        </span><span>private</span> IEnumerable<manager><span> _Manager;

        </span><span>///</span> <span><summary></summary></span>
        <span>///</span><span> 没有元数据文档可用。
        </span><span>///</span> <span></span>
        <span>public</span> IEnumerable<users><span> Users
        {
            </span><span>get</span><span>
            {
                </span><span>if</span> ((_Users == <span>null</span><span>))
                {
                    _Users </span>= <span>base</span>.CreateObjectSet<users>(<span>"</span><span>Users</span><span>"</span><span>);
                }
                </span><span>return</span><span> _Users;
            }
        }
        </span><span>private</span> IEnumerable<users><span> _Users;

        </span><span>#endregion</span>
        <span>#region</span> AddTo 方法

        <span>///</span> <span><summary></summary></span>
        <span>///</span><span> 用于向 CmdMsg EntitySet 添加新对象的方法，已弃用。请考虑改用关联的 IEnumerable<T> 属性的 .Add 方法。
        </span><span>///</span> <span></span>
        <span>public</span> <span>void</span><span> AddToCmdMsg(CmdMsg cmdMsg)
        {
            </span><span>base</span>.AddObject(<span>"</span><span>CmdMsg</span><span>"</span><span>, cmdMsg);
        }

        </span><span>///</span> <span><summary></summary></span>
        <span>///</span><span> 用于向 Door EntitySet 添加新对象的方法，已弃用。请考虑改用关联的 IEnumerable<T> 属性的 .Add 方法。
        </span><span>///</span> <span></span>
        <span>public</span> <span>void</span><span> AddToDoor(Door door)
        {
            </span><span>base</span>.AddObject(<span>"</span><span>Door</span><span>"</span><span>, door);
        }

        </span><span>///</span> <span><summary></summary></span>
        <span>///</span><span> 用于向 Manager EntitySet 添加新对象的方法，已弃用。请考虑改用关联的 IEnumerable<T> 属性的 .Add 方法。
        </span><span>///</span> <span></span>
        <span>public</span> <span>void</span><span> AddToManager(Manager manager)
        {
            </span><span>base</span>.AddObject(<span>"</span><span>Manager</span><span>"</span><span>, manager);
        }

        </span><span>///</span> <span><summary></summary></span>
        <span>///</span><span> 用于向 Users EntitySet 添加新对象的方法，已弃用。请考虑改用关联的 IEnumerable<T> 属性的 .Add 方法。
        </span><span>///</span> <span></span>
        <span>public</span> <span>void</span><span> AddToUsers(Users users)
        {
            </span><span>base</span>.AddObject(<span>"</span><span>Users</span><span>"</span><span>, users);
        }

        </span><span>#endregion</span><span>
    }</span></users></users></users></manager></manager></manager></door></door></door></cmdmsg></cmdmsg></cmdmsg>
Copy after login

懒人第三步：

为了让代码和EF使用方法基本一致，所以不得不做一些工作让我写的类基本满足项目需要。首先实现一个让Lambda Expression 表达式变成字符串的函数

<span>string</span><span> GetWhereString(Expression Func)
        {
            ConditionBuilder conditionBuilder </span>= <span>new</span><span> ConditionBuilder();
            conditionBuilder.Build(Func);
            </span><span>for</span> (<span>int</span> i = <span>0</span>; i )
            {
                <span>object</span> ce =<span> conditionBuilder.Arguments[i];
                </span><span>if</span> (ce == <span>null</span><span>)
                    conditionBuilder.Arguments[i] </span>= <span>"</span><span>null</span><span>"</span><span>;
                </span><span>else</span> <span>if</span> (ce <span>is</span><span> ValueType)
                    conditionBuilder.Arguments[i] </span>=<span> ce.ToString();
                </span><span>else</span> <span>if</span> (ce <span>is</span> <span>string</span> || ce <span>is</span> <span>char</span><span>)
                    conditionBuilder.Arguments[i] </span>= <span>string</span>.Format(<span>"</span><span>'{0}'</span><span>"</span><span>, ce.ToString());
                </span><span>else</span> <span>if</span> (ce <span>is</span><span> DateTime)
                    conditionBuilder.Arguments[i] </span>= <span>string</span>.Format(<span>"</span><span>#{0}#</span><span>"</span><span>, ce.ToString());

            }
            </span><span>return</span> <span>string</span><span>.Format(conditionBuilder.Condition, conditionBuilder.Arguments);
        }</span>
Copy after login

上面的ConditionBuilder类代码我就不贴出了。大家参考我提到的那篇文章，如果实际应用当中，有死循环的地方那么应该在该调用基类的地方加入base.XXX比如base.Visit

实现我们自己的where 这里如果我们自己生成类 那么我们的实体类类可以直接有一个where 方法，但是我为了能让我们懒人使用VS生成的实体类只能这么实现了。为了代码利用率，我们还需要另外2个函数。

取得所有记录

IEnumerable<tentity> SelectAll<tentity>() <span>where</span> TEntity : <span>new</span><span>()
        {
            TEntity TDefault </span>= <span>new</span><span> TEntity();
            </span><span>string</span> entitySetName =<span> TDefault.GetType().Name;
            </span><span>string</span> strSqlQuery = <span>string</span>.Format(<span>"</span><span>SELECT * FROM {0}</span><span>"</span><span>, entitySetName);
            m_LastSqlCommand </span>=<span> strSqlQuery;
            </span><span>return</span> SelectWhere<tentity><span>(strSqlQuery);
        }</span></tentity></tentity></tentity>
Copy after login

经常的条件查询

<span>public</span> IEnumerable<tentity> Where<tentity>(Expression<func>bool>> Func) <span>where</span> TEntity : <span>new</span><span>()
        {
            TEntity TDefault </span>= <span>new</span><span> TEntity();
            </span><span>string</span> entitySetName =<span> TDefault.GetType().Name;
            </span><span>string</span> strWhere = GetWhereString(Func).Replace(<span>"</span><span>Where</span><span>"</span><span>, entitySetName);
            </span><span>string</span> strSqlQuery = <span>string</span>.Format(<span>"</span><span>SELECT * FROM {0} WHERE {1} </span><span>"</span><span>, entitySetName, strWhere);
            m_LastSqlCommand </span>=<span> strSqlQuery;
            </span><span>return</span> SelectWhere<tentity><span>(strSqlQuery);
        }</span></tentity></func></tentity></tentity>
Copy after login

最后的where

IEnumerable<tentity> SelectWhere<tentity>(<span>string</span> strSqlQuery) <span>where</span> TEntity : <span>new</span><span>()
        {
            TEntity TDefault </span>= <span>new</span><span> TEntity();
            </span><span>//</span><span>确认基础类型是否是 EntityObject类型</span>
            Type TBase =<span> TDefault.GetType();
            </span><span>while</span> ((TBase.BaseType) != <span>null</span><span>)
            {
                </span><span>if</span> (TBase.Name == <span>"</span><span>EntityObject</span><span>"</span>) <span>break</span><span>;
                TBase </span>=<span> TBase.BaseType;
            }
            </span><span>bool</span> IsPCEH = <span>false</span><span>;
            </span><span>if</span> (TBase != <span>null</span> && TBase.Name == <span>"</span><span>EntityObject</span><span>"</span>)  IsPCEH = <span>true</span><span>;
            PropertyInfo[] properties </span>= TDefault.GetType().GetProperties(BindingFlags.Public |<span> BindingFlags.Instance);
            List</span><tentity> Records = <span>new</span> List<tentity><span>();
            </span><span>string</span> entitySetName =<span> TDefault.GetType().Name;
            </span><span>try</span><span>
            {
                OleDbCommand Cmd </span>= <span>new</span><span> OleDbCommand(strSqlQuery, AccessConn);
                OleDbDataReader sqlReader </span>=<span> Cmd.ExecuteReader();
                </span><span>#region</span> 数据库查询开始
                <span>while</span><span> (sqlReader.Read())
                {
                    TEntity TValue </span>= <span>new</span><span> TEntity();
                    </span><span>//</span><span>输入是EntityObject类型，那么加入属性改变事件</span>
                    <span>if</span><span> (IsPCEH)
                    {
                        EntityObject EO </span>= TValue <span>as</span><span> EntityObject;
                        EO.PropertyChanged </span>+=<span> PropertyChangedEH;
                    }
                    </span><span>for</span> (<span>int</span> i = <span>0</span>; i )
                    {
                        <span>string</span> strField =<span> sqlReader.GetName(i);
                        </span><span>//</span><span>根据字段名 反射 类的属性</span>
                        PropertyInfo p = properties.Where(P => <span>string</span>.Compare(P.Name, strField, <span>true</span>) == <span>0</span><span>).First();
                        </span><span>#region</span> 数据转换
                        <span>switch</span><span> (p.PropertyType.Name.ToString().ToLower())
                        {
                            </span><span>case</span> <span>"</span><span>int16</span><span>"</span><span>:
                                </span><span>if</span> (!sqlReader.IsDBNull(i)) p.SetValue(TValue, sqlReader.GetInt16(i), <span>null</span><span>);
                                </span><span>break</span><span>;
                            </span><span>case</span> <span>"</span><span>int32</span><span>"</span><span>:
                                </span><span>if</span> (!sqlReader.IsDBNull(i)) p.SetValue(TValue, sqlReader.GetInt32(i), <span>null</span><span>);
                                </span><span>break</span><span>;
                            </span><span>case</span> <span>"</span><span>int64</span><span>"</span><span>:
                                </span><span>if</span> (!sqlReader.IsDBNull(i)) p.SetValue(TValue, sqlReader.GetInt64(i), <span>null</span><span>);
                                </span><span>break</span><span>;
                            </span><span>case</span> <span>"</span><span>string</span><span>"</span><span>:
                                </span><span>if</span> (!sqlReader.IsDBNull(i)) p.SetValue(TValue, sqlReader.GetString(i), <span>null</span><span>);
                                </span><span>break</span><span>;
                            </span><span>case</span> <span>"</span><span>double</span><span>"</span><span>:
                                </span><span>if</span> (!sqlReader.IsDBNull(i)) p.SetValue(TValue, sqlReader.GetDouble(i), <span>null</span><span>);
                                </span><span>break</span><span>;
                            </span><span>case</span> <span>"</span><span>float</span><span>"</span><span>:
                                </span><span>if</span> (!sqlReader.IsDBNull(i)) p.SetValue(TValue, sqlReader.GetFloat(i), <span>null</span><span>);
                                </span><span>break</span><span>;
                            </span><span>case</span> <span>"</span><span>decimal</span><span>"</span><span>:
                                </span><span>if</span> (!sqlReader.IsDBNull(i)) p.SetValue(TValue, sqlReader.GetDecimal(i), <span>null</span><span>);
                                </span><span>break</span><span>;
                            </span><span>case</span> <span>"</span><span>datetime</span><span>"</span><span>:
                                </span><span>if</span> (!sqlReader.IsDBNull(i)) p.SetValue(TValue, sqlReader.GetDateTime(i), <span>null</span><span>);
                                </span><span>break</span><span>;
                            </span><span>default</span><span>:
                                </span><span>if</span> (!sqlReader.IsDBNull(i)) p.SetValue(TValue, sqlReader.GetValue(i), <span>null</span><span>);
                                </span><span>break</span><span>;
                        }
                        </span><span>#endregion</span><span>

                    }
                    Records.Add(TValue);
                }
                </span><span>#endregion</span><span>
                Cmd.Clone();
                m_IsDetectionChange </span>= <span>true</span><span>;
            }
            </span><span>catch</span><span> (Exception)
            {
                </span><span>throw</span><span>;
            }
            </span><span>return</span><span> Records;
        }</span></tentity></tentity></tentity></tentity>
Copy after login

属性的改变（也就是数据库记录的字段值）我们要知道才能实现EF的SaveChanges()函数那么我们需要简历一个数组变量，直接上代码 

<span>//</span><span>表名            </span><span>//</span><span>主键              </span><span>//</span><span>属性    值1 值2 主键类型名字</span>
        Dictionarystring, Dictionarystring, Dictionarystring, <span>object</span>[]>>> m_ArrDetection = <span>new</span> Dictionarystring, Dictionarystring, Dictionarystring, <span>object</span>[]>>><span>();
</span>
Copy after login

<span>void</span> PropertyChangedEH(<span>object</span><span> sender, PropertyChangedEventArgs e)
        {
            </span><span>//</span><span>没有开启返回</span>
            <span>if</span> (!m_IsDetectionChange) <span>return</span><span>;
            </span><span>//</span><span>反射所有属性</span>
            PropertyInfo[] properties = sender.GetType().GetProperties(BindingFlags.Public |<span> BindingFlags.Instance);
            </span><span>//</span><span>查询主键</span>
            PropertyInfo EntityKey =<span> GetEntityKeyFormAllProperty(properties);
            </span><span>//</span><span>没有主键的返回（表必须要有主键）</span>
            <span>if</span> (EntityKey == <span>null</span>) <span>return</span><span>;
            </span><span>//</span><span>表名字</span>
            <span>string</span> Table =<span> sender.GetType().Name;
            </span><span>//</span><span>主键值</span>
            <span>string</span> MainKey = EntityKey.GetValue(sender, <span>null</span><span>).ToString();
            </span><span>if</span> (MainKey == <span>null</span> || MainKey == <span>""</span>) <span>return</span><span>;
            </span><span>//</span><span>没有表 就添加</span>
            <span>if</span> (!m_ArrDetection.ContainsKey(Table)) m_ArrDetection[Table] = <span>new</span> Dictionarystring, Dictionarystring, <span>object</span>[]>><span>();
            </span><span>//</span><span>没有主键 就添加</span>
            <span>if</span> (!m_ArrDetection[Table].ContainsKey(MainKey)) m_ArrDetection[Table][MainKey] = <span>new</span> Dictionarystring, <span>object</span>[]><span>();
            </span><span>//</span><span>主键是不用更新的（一般数据库主键都是自动增长的吧，尤其快速开发的项目）</span>
            <span>if</span> (e.PropertyName == MainKey) <span>return</span><span>;
            PropertyInfo p </span>= properties.Where(P => <span>string</span>.Compare(P.Name, e.PropertyName, <span>true</span>) == <span>0</span><span>).First();
            </span><span>//</span><span>赋值</span>
            m_ArrDetection[Table][MainKey][e.PropertyName] = <span>new</span> <span>object</span>[<span>2</span>] { p.GetValue(sender, <span>null</span><span>), EntityKey.Name };
        }</span>
Copy after login

查找主键的函数

<span>private</span> <span>bool</span><span> IsEntityKeyProperty(PropertyInfo Info)
        {
            </span><span>foreach</span> (Attribute attr <span>in</span><span> Attribute.GetCustomAttributes(Info))
            {
                </span><span>if</span> (attr <span>is</span><span> System.Data.Objects.DataClasses.EdmScalarPropertyAttribute)
                {
                    System.Data.Objects.DataClasses.EdmScalarPropertyAttribute Key </span>=<span> (System.Data.Objects.DataClasses.EdmScalarPropertyAttribute)attr;
                    </span><span>if</span> (Key.EntityKeyProperty == <span>true</span><span>)
                    {
                        </span><span>return</span> <span>true</span><span>;
                    }
                }
            }
            </span><span>return</span> <span>false</span><span>;
        }
        </span><span>private</span><span> PropertyInfo GetEntityKeyFormAllProperty(PropertyInfo[] properties)
        {
            </span><span>foreach</span> (PropertyInfo Info <span>in</span><span> properties)
            {
                </span><span>if</span> (IsEntityKeyProperty(Info)) <span>return</span><span> Info;
            }
            </span><span>return</span> <span>null</span><span>;
        }</span>
Copy after login

在实际的调用过程中，我们使用基本可以和EF使用一样 

<span>public</span><span> ActionResult Index()
        {
            </span><span>int</span> UID =<span> GetUerID();
            List</span><door> Arr = <span>new</span> List<door><span>();
            </span><span>try</span><span>
            {
                </span><span>//</span><span> TODO: Add insert logic here</span>
                <span>using</span> (SqlDoorEntities Database = <span>new</span><span> SqlDoorEntities())
                {
                    </span><span>//</span><span>EF写法 
                    </span><span>//</span><span>IEnumerable<door> Records = Database.Door.Where(R => R.U_ID == UID);</door></span>
                    IEnumerable<door> Records = Database.Where<door>(R => R.U_ID ==<span> UID);
                    </span><span>foreach</span> (Door Record <span>in</span><span> Records) Arr.Add(Record);
                }

            }
            </span><span>catch</span><span> (Exception E)
            {
                </span><span>return</span><span> Content(E.Message);
            }
            </span><span>return</span><span> View(Arr);
        }</span></door></door></door></door>
Copy after login

当然你可以直接用EF的方式 ，缺点就是直接把整个表的数据都读取过来了。下面我依次说说 数据库的 增加 删除 修改；

增加

实现CreateObjectSet，懒人嘛，要不还得去修改。

<span>public</span> IEnumerable<tentity> CreateObjectSet<tentity>(<span>string</span> entitySetName) <span>where</span> TEntity : <span>new</span><span>()
        {
            </span><span>return</span>  SelectAll<tentity><span>();
        }</span></tentity></tentity></tentity>
Copy after login

 

实现AddObject 直接一个简单把对象插入到数组中。实现的时候可以让SaveChanges()在处理

Listobject> m_ArrAdd = <span>new</span> Listobject><span>();
</span><span>public</span> <span>void</span> AddObject(<span>string</span> strName, <span>object</span><span> o)
        {
            m_ArrAdd.Add(o);
        }</span>
Copy after login

总说SaveChanges()那么先把他贴出来

<span>public</span> <span>int</span><span> SaveChanges()
        {
            </span><span>if</span> (m_ArrDel.Count > <span>0</span><span>)
            {
                DeleteAll();
                m_ArrDel.Clear();
            }
            </span><span>if</span> (m_ArrAdd.Count > <span>0</span><span>)
            {
                AddAll();
                m_ArrAdd.Clear();
            }
            </span><span>if</span> (m_ArrDetection.Count > <span>0</span><span>)
            {
                AutoUpdate();
                m_ArrDetection.Clear();
            }
            m_IsDetectionChange </span>= <span>false</span><span>;
            </span><span>return</span> <span>0</span><span>;
        }</span>
Copy after login

其实也没什么，就是看看数组中哪个有了需要增删改的 就处理下，接着写添加所有的函数。

<span>int</span><span> AddAll()
        {
            </span><span>foreach</span> (<span>object</span> O <span>in</span><span> m_ArrAdd)
            {
                AddNew(O);
            }
            </span><span>return</span> <span>0</span><span>;
        }</span>
Copy after login

下面该实现我们的insert into 了 直接使用也是可以的 就不用使用纠结的SaveChanges()了。

<span>public</span> <span>int</span> AddNew<tentity>(TEntity TDefault) <span>where</span> TEntity : <span>class</span><span>
        {
            PropertyInfo[] properties </span>= TDefault.GetType().GetProperties(BindingFlags.Public |<span> BindingFlags.Instance);
            PropertyInfo EntityKey </span>=<span> GetEntityKeyFormAllProperty(properties);
            </span><span>if</span> (EntityKey == <span>null</span>) <span>throw</span> <span>new</span> Exception(<span>"</span><span>未设置主键，无法使用本函数请使用其他函数!</span><span>"</span><span>);
            </span><span>string</span> TabName =<span> TDefault.GetType().Name;
            </span><span>string</span> EntityValue = <span>""</span><span>;
            </span><span>string</span> strRows = <span>""</span><span>;
            </span><span>string</span> strValues = <span>""</span><span>;
            </span><span>#region</span> Rows Values
            <span>foreach</span> (PropertyInfo Info <span>in</span><span> properties)
            {
                </span><span>object</span> ce = Info.GetValue(TDefault, <span>null</span><span>);
                </span><span>string</span> strLeft =<span> Info.Name;
                </span><span>string</span> strRight = <span>""</span><span>;
                </span><span>if</span> (ce == <span>null</span><span>)
                    </span><span>continue</span><span>;
                </span><span>else</span> <span>if</span> (ce <span>is</span><span> DateTime)
                    strRight </span>= <span>string</span>.Format(<span>"</span><span>#{0}#</span><span>"</span><span>, ce.ToString());
                </span><span>else</span> <span>if</span> (ce <span>is</span><span> ValueType)
                    strRight </span>=<span> ce.ToString();
                </span><span>else</span> <span>if</span> (ce <span>is</span><span> ValueType)
                    strRight </span>=<span> ce.ToString();
                </span><span>else</span> <span>if</span> (ce <span>is</span> <span>string</span> || ce <span>is</span> <span>char</span><span>)
                    strRight </span>= <span>string</span>.Format(<span>"</span><span>'{0}'</span><span>"</span><span>, ce.ToString());
                </span><span>if</span> (strLeft ==<span> EntityKey.Name)
                {
                    EntityValue </span>=<span> strRight;
                    </span><span>continue</span><span>;
                }
                </span><span>if</span> (strRight.Length == <span>0</span>) <span>continue</span><span>;
                </span><span>if</span> (strLeft == <span>"</span><span>EntityKey</span><span>"</span> || strLeft == <span>"</span><span>EntityState</span><span>"</span>) <span>continue</span><span>;
                strRows </span>= strRows + strLeft + <span>"</span><span>,</span><span>"</span><span>;
                strValues </span>= strValues + strRight + <span>"</span><span>,</span><span>"</span><span>;
            }
            </span><span>#endregion</span>
            <span>if</span> (strRows.Length 2 || strValues.Length 2) <span>throw</span> <span>new</span> Exception(<span>"</span><span>SQL语句错误</span><span>"</span><span>);
            strRows </span>= strRows.Remove(strRows.Length - <span>1</span><span>);
            strValues </span>= strValues.Remove(strValues.Length - <span>1</span><span>);
            </span><span>string</span> strSqlQuery = <span>string</span>.Format(<span>"</span><span>INSERT INTO {0} ({1}) VALUES ({2})</span><span>"</span><span>, TabName, strRows, strValues);
            m_LastSqlCommand </span>=<span> strSqlQuery;
            </span><span>try</span><span>
            {
                OleDbCommand Cmd </span>= <span>new</span><span> OleDbCommand(strSqlQuery, AccessConn);
                Cmd.ExecuteNonQuery();
            }
            </span><span>catch</span><span> (Exception)
            {
                </span><span>throw</span><span>;
            }
            </span><span>return</span> <span>0</span><span>;
        }</span></tentity>
Copy after login

函数中也没什么，就是注意一下不要生成SQL语句的时候，把主键信息也生成进去，一般情况主键大多是个自动增长的数字吧。还有就是不要把EntityObject的属性的特有主键信息写入到数据库中。根据反射写入数据库。

删除

还是先现实EF的删除方法DeleteObject

<span>public</span> <span>void</span> DeleteObject(<span>object</span><span> TDefault)
        {
            PropertyInfo[] properties </span>= TDefault.GetType().GetProperties(BindingFlags.Public |<span> BindingFlags.Instance);
            PropertyInfo EntityKey </span>=<span> GetEntityKeyFormAllProperty(properties);
            </span><span>if</span> (EntityKey == <span>null</span>) <span>throw</span> <span>new</span> Exception(<span>"</span><span>未设置主键，无法使用本函数请使用其他函数!</span><span>"</span><span>);
            </span><span>string</span> EntityValue = EntityKey.GetValue(TDefault, <span>null</span><span>).ToString();
            </span><span>if</span> (EntityValue == <span>null</span> || EntityValue == <span>""</span>) <span>throw</span> <span>new</span> Exception(<span>"</span><span>反射取值失败!</span><span>"</span><span>);
            </span><span>string</span> entitySetName =<span> TDefault.GetType().Name;
            </span><span>string</span> KeyName =<span> TDefault.GetType().Name;
            </span><span>if</span> (!m_ArrDel.ContainsKey(KeyName)) m_ArrDel.Add(KeyName,<span>new</span> Liststring><span>());
            m_ArrDel[KeyName].Add(</span><span>string</span>.Format(<span>"</span><span>(({0})={1})</span><span>"</span><span>, EntityKey.Name, EntityValue));
        }</span>
Copy after login

然后我们需要建立我们自己的列表

Dictionarystring, Liststring>> m_ArrDel = <span>new</span> Dictionarystring, Liststring>>();
Copy after login

 

实现删除函数

<span>public</span> <span>int</span> Delete<tentity><span>(TEntity TDefault)
        {
            PropertyInfo[] properties </span>= TDefault.GetType().GetProperties(BindingFlags.Public |<span> BindingFlags.Instance);
            PropertyInfo EntityKey </span>=<span> GetEntityKeyFormAllProperty(properties);
            </span><span>if</span> (EntityKey == <span>null</span>) <span>throw</span> <span>new</span> Exception(<span>"</span><span>未设置主键，无法使用本函数请使用其他函数!</span><span>"</span><span>);
            </span><span>string</span> EntityValue = EntityKey.GetValue(TDefault, <span>null</span><span>).ToString();
            </span><span>if</span> (EntityValue == <span>null</span> || EntityValue == <span>""</span>) <span>throw</span> <span>new</span> Exception(<span>"</span><span>反射取值失败!</span><span>"</span><span>);
            </span><span>string</span> entitySetName =<span> TDefault.GetType().Name;
            </span><span>string</span> strSqlQuery = <span>string</span>.Format(<span>"</span><span>DELETE FROM {0} WHERE {1}={2} </span><span>"</span><span>, entitySetName, EntityKey, EntityValue);
            m_LastSqlCommand </span>=<span> strSqlQuery;
            </span><span>try</span><span>
            {
                OleDbCommand Cmd </span>= <span>new</span><span> OleDbCommand(strSqlQuery, AccessConn);
                Cmd.ExecuteNonQuery();
            }
            </span><span>catch</span><span> (Exception)
            {
                </span><span>throw</span><span>;
            }
            </span><span>return</span> <span>0</span><span>;
        }</span></tentity>
Copy after login

更新

这里是根据主键更新的，没有实现update …where…，因为往往都是查询到记录，然后根据这个记录更新的，所以还需要更多代码的实现Select等，为了这个小的项目进度没有写完。上面代码已经告诉了，可以侦测到查询到的属性的变更所以SaveChanges()保存更改时，我们就直接根据数组进行更改了。

<span>public</span> <span>int</span><span> AutoUpdate()
        {
            List</span>string> ArrSqlText = <span>new</span> Liststring><span>();
            </span><span>foreach</span> (KeyValuePairstring, Dictionarystring, Dictionarystring, <span>object</span>[]>>> TabKVP <span>in</span><span> m_ArrDetection)
            {
                </span><span>//</span><span>遍历表名</span>
                <span>string</span> TabName =<span> TabKVP.Key;
                </span><span>foreach</span> (KeyValuePairstring, Dictionarystring, <span>object</span>[]>> KeyKVP <span>in</span><span> TabKVP.Value)
                {
                    </span><span>string</span> strSet = <span>""</span><span>;
                    </span><span>string</span> strMainKeyName = <span>""</span><span>;
                    </span><span>#region</span> 把数据列出来例如： a=1,c="2"
                    <span>foreach</span> (KeyValuePairstring, <span>object</span>[]> ValueKVP <span>in</span><span> KeyKVP.Value)
                    {
                        </span><span>if</span> (strMainKeyName.Length == <span>0</span>) strMainKeyName = ValueKVP.Value[<span>1</span><span>].ToString();
                        </span><span>object</span> Va = ValueKVP.Value[<span>0</span><span>];
                        </span><span>string</span> strLeft =<span> ValueKVP.Key;
                        </span><span>string</span> strRight = <span>""</span><span>;
                        </span><span>#region</span> 根据值确认是否添加引号
                        <span>if</span> (ValueKVP.Value == <span>null</span><span>)
                            </span><span>continue</span><span>;
                        </span><span>else</span> <span>if</span> (Va <span>is</span><span> DateTime)
                            strRight </span>= <span>string</span>.Format(<span>"</span><span>#{0}#</span><span>"</span><span>, Va.ToString());
                        </span><span>else</span> <span>if</span> (Va <span>is</span><span> ValueType)
                            strRight </span>=<span> Va.ToString();
                        </span><span>else</span> <span>if</span> (Va <span>is</span> <span>string</span> || Va <span>is</span> <span>char</span><span>)
                            strRight </span>= <span>string</span>.Format(<span>"</span><span>'{0}'</span><span>"</span><span>, Va.ToString());
                        </span><span>#endregion</span>
                        <span>if</span> (strRight.Length == <span>0</span>) <span>continue</span><span>;
                        </span><span>if</span> (strLeft == <span>"</span><span>EntityKey</span><span>"</span> || strLeft == <span>"</span><span>EntityState</span><span>"</span>) <span>continue</span><span>;
                        strSet </span>+= strLeft + <span>"</span><span>=</span><span>"</span> + strRight + <span>"</span><span>,</span><span>"</span><span>;

                    }
                    </span><span>#endregion</span>
                    <span>if</span> (strSet.Length 2) <span>continue</span><span>;
                    strSet </span>= strSet.Remove(strSet.Length - <span>1</span><span>);
                    </span><span>//</span><span>根据当前的主键[ID] 生成一个SQL语句</span>
                    <span>string</span> strSqlQuery = <span>string</span>.Format(<span>"</span><span>UPDATE {0} SET {1} WHERE {2}={3} </span><span>"</span><span>, TabName, strSet, strMainKeyName, KeyKVP.Key);
                    ArrSqlText.Add(strSqlQuery);
                }
            }
            </span><span>foreach</span> (<span>string</span> strSqlQuery <span>in</span><span> ArrSqlText)
            {
                m_LastSqlCommand </span>=<span> strSqlQuery;
                </span><span>try</span><span>
                {
                    OleDbCommand Cmd </span>= <span>new</span><span> OleDbCommand(strSqlQuery, AccessConn);
                    Cmd.ExecuteNonQuery();
                }
                </span><span>catch</span><span>
                {
                }

            }
            </span><span>return</span> <span>0</span><span>;
        }</span>
Copy after login

当然我们还有直接把对象直接赋值拷贝的时候（ a = b），这时候是侦测不到属性的变化的，所以我们要另外一个函数来支持更新，就是让他实现侦测到属性的变化。

<span>public</span> <span>void</span> CopyPropertiesFrom(<span>object</span> destObject, <span>object</span><span> sourceObject)
        {
            </span><span>if</span> (destObject.GetType().Name != destObject.GetType().Name) <span>throw</span> <span>new</span> Exception(<span>"</span><span>类型不同</span><span>"</span><span>);
            PropertyInfo[] destProperties </span>= destObject.GetType().GetProperties(BindingFlags.Public |<span> BindingFlags.Instance);
            PropertyInfo[] sourceProperties </span>= sourceObject.GetType().GetProperties(BindingFlags.Public |<span> BindingFlags.Instance);
            PropertyInfo EntityKey </span>=<span> GetEntityKeyFormAllProperty(destProperties);
            </span><span>if</span> (EntityKey == <span>null</span>) <span>throw</span> <span>new</span> Exception(<span>"</span><span>未设置主键，无法使用本函数请使用其他函数!</span><span>"</span><span>);
            
            </span><span>for</span> (<span>int</span> i = <span>0</span>; i )
            {
                <span>if</span> (destProperties[i]==<span>null</span>|| destProperties[i].Name == EntityKey.Name) <span>continue</span><span>;
                </span><span>if</span> (destProperties[i].Name == <span>"</span><span>EntityKey</span><span>"</span> || destProperties[i].Name == <span>"</span><span>EntityState</span><span>"</span>) <span>continue</span><span>;
                </span><span>object</span> DstV = destProperties[i].GetValue(destObject,<span>null</span><span>);
                </span><span>object</span> SrcV = sourceProperties[i].GetValue(sourceObject, <span>null</span><span>);
                </span><span>if</span> (SrcV == <span>null</span>) <span>continue</span>;<span>//</span><span>源 是NULL 不拷贝</span>
                <span>if</span> (DstV.ToString() == SrcV.ToString()) <span>continue</span><span>;
                destProperties[i].SetValue(destObject, SrcV,</span><span>null</span><span>);
                </span><span>//</span><span>Action<object object> LmdSetProp = LmdSet(destObject.GetType(), destProperties[i].Name);
                </object></span><span>//</span><span>LmdSetProp(destObject, SrcV);</span>
<span>            }

        }</span>
Copy after login

显示-添加-删除-修改的例子代码

<span>#region</span> 显示用户<span>
        [Authorize(Roles </span>= <span>"</span><span>manager</span><span>"</span><span>)]
        </span><span>public</span><span> ActionResult Index()
        {
            List</span><users> Users = <span>new</span> List<users><span>();
            Users u </span>= <span>new</span><span> Users();
            </span><span>try</span><span>
            {
                </span><span>using</span> (SqlDoorEntities Database = <span>new</span><span> SqlDoorEntities())
                {
                    IEnumerable</span><users> Records =<span> Database.Users;
                    </span><span>if</span> (Records.Count() > <span>0</span><span>)
                    {
                        </span><span>foreach</span> (Users U <span>in</span><span> Records) Users.Add(U);
                    }
                }
            }
            </span><span>catch</span><span> { }
            </span><span>return</span><span> View(Users);
        }
        </span><span>#endregion</span>

        <span>#region</span> 创建用户<span>
        [Authorize(Roles </span>= <span>"</span><span>manager</span><span>"</span><span>)]
        [HttpPost]
        </span><span>public</span><span> ActionResult CreateUser(Users collection)
        {
            </span><span>try</span><span>
            {
                </span><span>//</span><span> TODO: Add insert logic here</span>
                <span>using</span> (SqlDoorEntities Database = <span>new</span><span> SqlDoorEntities())
                {
                    IEnumerable</span><users> Records = Database.Where<users>(R => R.U_Number ==<span> collection.U_Number);
                    </span><span>if</span> (Records.Count() > <span>0</span><span>)
                    {
                        ModelState.AddModelError(</span><span>""</span>, <span>"</span><span>已經有了記錄了!</span><span>"</span><span>);
                        </span><span>return</span> RedirectToAction(<span>"</span><span>Index</span><span>"</span><span>);
                    }
                    Database.AddToUsers(collection);
                    Database.SaveChanges();
                    </span><span>//</span><span>collection.U_LastIP = GetWebClientIp();</span>
<span>                }
                </span><span>return</span> RedirectToAction(<span>"</span><span>Index</span><span>"</span><span>);
            }
            </span><span>catch</span><span> (Exception E)
            {
                ModelState.AddModelError(</span><span>""</span>, <span>"</span><span>数据库错误!</span><span>"</span> +<span> E.Message);
            }
            </span><span>return</span><span> View();
        }
        [Authorize(Roles </span>= <span>"</span><span>manager</span><span>"</span><span>)]
        </span><span>public</span><span> ActionResult CreateUser()
        {
            </span><span>return</span><span> View();
        }
        </span><span>#endregion</span>

        <span>#region</span> 编辑用户<span>
        [Authorize(Roles </span>= <span>"</span><span>manager</span><span>"</span><span>)]
        [HttpPost]
        </span><span>public</span> ActionResult EditUser(<span>int</span><span> id, Users collection)
        {
            </span><span>try</span><span>
            {
                </span><span>//</span><span> TODO: Add insert logic here</span>
                
                <span>using</span> (SqlDoorEntities Database = <span>new</span><span> SqlDoorEntities())
                {
                    Users Record </span>= Database.Where<users>(R => R.U_ID ==<span> id).FirstOrDefault();
                    </span><span>//</span><span>Database.Update<users>(Record);</users></span>
<span>                    Database.CopyPropertiesFrom(Record, collection);
                    Database.SaveChanges();
                }
                </span><span>return</span> Content(<span>"</span><span>OK</span><span>"</span><span>);
            }
            </span><span>catch</span><span> (Exception E)
            {
                </span><span>return</span><span> Content(E.Message);
            }
        }
        </span><span>#endregion</span>

        <span>#region</span> 删除用户<span>
        [Authorize(Roles </span>= <span>"</span><span>manager</span><span>"</span><span>)]
        </span><span>public</span> ActionResult DeleteUser(<span>int</span><span> id)
        {
            </span><span>try</span><span>
            {
                </span><span>//</span><span> TODO: Add insert logic here</span>

                <span>using</span> (SqlDoorEntities Database = <span>new</span><span> SqlDoorEntities())
                {
                    Users Record </span>= Database.Where<users>(R => R.U_ID ==<span> id).FirstOrDefault();
                    </span><span>if</span> (User != <span>null</span><span>)
                    {
                        Database.DeleteObject(Record);
                        Database.SaveChanges();
                    }
                }
            }
            </span><span>catch</span><span>
            {

            }
            </span><span>return</span> RedirectToAction(<span>"</span><span>Index</span><span>"</span><span>);
        }
        </span><span>#endregion</span></users></users></users></users></users></users></users>
Copy after login

最后

算是写完了，也算是我cnblog的第一篇技术类文章吧。写的不对的地方欢迎指正啊。本人QQ78486367。下面是用到的源文件。

http://files.cnblogs.com/hackdragon/EFToAccess.zip

Related labels：
ac framework support
source：php.cn
Previous article：MyReport 2.6.0.0新功能 Next article：STL vector总结(四) Element access（37）
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Latest Issues
function_exists() cannot determine the custom function Function test () {return true;} if (function_exists ('test')) {echo "test is function...
From 2024-04-29 11:01:01
0
3
2219
How to display the mobile version of Google Chrome Hello teacher, how can I change Google Chrome into a mobile version?
From 2024-04-23 00:22:19
0
11
2363
The child window operates the parent window, but the output does not respond. The first two sentences are executable, but the last sentence cannot be implemented.
From 2024-04-19 15:37:47
0
1
1975
There is no output in the parent window document.onclick = function(){ window.opener.document.write('I am the output of the child ...
From 2024-04-18 23:52:34
0
1
1862
Where is the courseware about CSS mind mapping? Courseware
From 2024-04-16 10:10:18
0
0
1931
Related Topics
More>
Popular Recommendations
Popular Tutorials
More>
Related Tutorials
Popular Recommendations
Latest courses
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template