Linq 直接连接Access数据库

Linq 应用比较多的是直接连接SqlServer 数据库，然后建立*.dbml 文件，调用DataContext 来增删改查。

但其实Linq 其实也可以连接OleDbConnection

Linq 连接Access数据库，还需借助OleDbConnection ，下面是提供的DataContext 访问类

public class LinqConextClass:IDisposable
    {
        private OleDbConnection oleConnection;
        private bool flagOpen=false;
        private DataContext context;

        public LinqConextClass()
        {
            oleConnection = new OleDbConnection(PubConstant.ConnectionString);
        }

        /// <summary>
        /// 获取执行的上下文
        /// </summary>
        public DataContext Context
        {
            get
            {
                if (oleConnection != null && oleConnection.State == ConnectionState.Open && flagOpen)
                {
                    context = new DataContext(oleConnection);
                    return context;
                }
                else
                {
                    throw new Exception("打开数据库连接失败！");
                    return null;
                }
            }
        }

        public void  Open()
        {
            if (oleConnection != null)
            {
                oleConnection.Open();
                flagOpen = true;
            }
        }

        public void  Close()
        {
            if (oleConnection != null)
            {
                oleConnection.Close();
                flagOpen = false;
            }
        }

        #region IDisposable 成员

        public void Dispose()
        {
            if (oleConnection != null)
            {
                oleConnection.Close();
                oleConnection.Dispose();
            }
            if (context != null)
            {
                context.Connection.Close();
                context.Dispose();
            }
        }

        #endregion
    }

 

 

DataContext对象可以用OleDbConnection对象来初始化

但是OleDbConnection对象必须处于打开状态。

接下来是建立数据库表的模型

/// <summary>
    /// 实体类Department 。(属性说明自动提取数据库字段的描述信息)
    /// </summary>
    [Serializable]
    public class Department
    {
        public Department()
        {}
        #region Model
        private int _departmentid;
        private string _name;
        private int _budget;
        private DateTime? _startdate;
        private int _administrator;
        /// <summary>
        /// 
        /// </summary>
        public int DepartmentID
        {
            set{ _departmentid=value;}
            get{return _departmentid;}
        }
        /// <summary>
        /// 
        /// </summary>
        public string Name
        {
            set{ _name=value;}
            get{return _name;}
        }
        /// <summary>
        /// 
        /// </summary>
        public int Budget
        {
            set{ _budget=value;}
            get{return _budget;}
        }
        /// <summary>
        /// 
        /// </summary>
        public DateTime? StartDate
        {
            set { _startdate = value; }
            get{return _startdate;}
        }
        /// <summary>
        /// 
        /// </summary>
        public int Administrator
        {
            set{ _administrator=value;}
            get{return _administrator;}
        }
        #endregion Model

    }

 

 

 写Db访问的Service类

关键是执行sql语句，处理参数的问题

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model;
using System.Collections;
using BAL.LinqContext;
using System.Data.Linq;

namespace BAL.Service
{
    public class ContextDepartmentService
    {
        public void Save(Department dept)
        {
            //INSERT INTO Department ( 
            //[DepartmentID] ,
            //[Name] ,
            //[Budget] ,
            //[StartDate] ,
            //[Administrator] ) VALUES (123,'test1',456,'2010-4-12 0:00:00',456)

            StringBuilder strSql = new StringBuilder();
         
            strSql.Append("INSERT INTO Department (");
            strSql.Append("[DepartmentID],[Name],[Budget],[StartDate],[Administrator] )");
            strSql.Append(" values (");
            string dateTime = string.Empty;
            if (dept.StartDate == null)
            {
                dateTime = string.Empty;
            }
            else
            {
                DateTime dateTimeTemp=(DateTime)dept.StartDate;
                dateTime = dateTimeTemp.ToString("yyyy-MM-dd HH:mm:ss");
            }
            strSql.Append(string.Format("{0},'{1}',{2},'{3}',{4}", 
                dept.DepartmentID, dept.Name, dept.Budget,dateTime, dept.Administrator)) ;
            strSql.Append(")");

            LinqConextClass context = new LinqConextClass();
            try
            {
                context.Open();
                DataContext dataContext = context.Context;
                int x = dataContext.ExecuteCommand(strSql.ToString());
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                context.Close();
            }
        }

        public void Update()
        { }

        public void Delte()
        { }

        public int GetMaxId()
        {
            int maxId=0;
            StringBuilder strSql = new StringBuilder();
            //select Max([DepartmentID]) from Department

            strSql.Append("SELECT MAX([DepartmentID]) FROM Department");
            LinqConextClass context = new LinqConextClass();
            try
            {
                context.Open();
                DataContext dataContext = context.Context;
                IEnumerable collection = dataContext.ExecuteQuery((new int()).GetType(), strSql.ToString());
                foreach (int item in collection)
                {
                    maxId = item;
                }
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                context.Close();
            }
            return maxId;
        }

        public List<Department> GetList(string where)
        {
            List<Department> deplist = new List<Department>();
            StringBuilder strSql = new StringBuilder();
            //select [DepartmentID],[Name],[Budget],[StartDate],[Administrator] from Department
            strSql.Append("SELECT [DepartmentID],[Name],[Budget],[StartDate],[Administrator] FROM Department ");
            strSql.Append(where);
            LinqConextClass context = new LinqConextClass();
            try
            {
                context.Open();
                DataContext dataContext = context.Context;
                IEnumerable collections = dataContext.ExecuteQuery((new Department()).GetType(), strSql.ToString());
                foreach (var item in collections)
                {
                    Department temp = item as Department;
                    deplist.Add(temp);
                }
            }
            catch (Exception exp)
            {
                throw exp;
            }
            finally
            {
                context.Close();
            }
            return deplist;
        }
    }
}

 

 后边把源代码附上

代码：

 /Files/csharponworking/LinqFromAccess.rar