简体中文（ZH-CN）English（EN）繁体中文（ZH-TW）日本語（JA）한국어（KO）Melayu（MS）Français（FR）Deutsch（DE）
Home
Article
Q&A
Course
Topic
Download
Game
Dictionary
Recent Updates

Home  >  Article  >  Database  >  让ADO.NET Entity Framework 支持ACCESS数据库

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

WBOY
WBOYOriginal
2016-06-07 15:42:53956browse

如写的不好请见谅，本人水平有限。 个人简历及水平：。 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>

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

懒人第三步：

为了让代码和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>

上面的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>

经常的条件查询

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

最后的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>

属性的改变（也就是数据库记录的字段值）我们要知道才能实现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>

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

查找主键的函数

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

在实际的调用过程中，我们使用基本可以和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>

当然你可以直接用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>

 

实现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>

总说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>

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

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

下面该实现我们的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>

函数中也没什么，就是注意一下不要生成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>

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

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

 

实现删除函数

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

更新

这里是根据主键更新的，没有实现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>

当然我们还有直接把对象直接赋值拷贝的时候（ 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>

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

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

最后

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

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

Statement：
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
Previous article：MyReport 2.6.0.0新功能Next article：STL vector总结(四) Element access（37）

Related articles

See more