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

秒客网

Linq 直接连接Access数据库

相关文章