C#笔记: 项目通过EF框架操作SQLCE数据库
(转载请注明来源:cnblogs coder-fang)
1. 项目添加package:
2. 创建好sqlce数据库并设计好关系表,在项目中添加ado.net 实体数据模型,并连接到此数据 库。
3. 如果不想在app.config暴露数据库敏感字段(如密码)时, 在创建实体模型时,进行如下选择:
4. 修改自动生成的Context.cs:
5. 在此文件中,增加一个构造函数 :
6.创建sqlcceprovider,填写连接字串:
public abstract class SqlceProvider { protected const string DBUtilVersion = "1.0"; protected EntityConnectionStringBuilder ecb; protected EntityConnectionStringBuilder ecbReadOnly; public SqlceProvider(string dataSource) { ecb = new EntityConnectionStringBuilder() { Metadata = "res://*/DAO.SQLCEStorage.csdl|res://*/DAO.SQLCEStorage.ssdl|res://*/DAO.SQLCEStorage.msl", Provider = "System.Data.SqlServerCe.4.0", ProviderConnectionString = "Data Source=" + dataSource +";"+ "Default Lock Timeout=2000;Persist Security Info=false;Max Database Size=4091;Password=123456;" }; } protected String FindExceptMsg(Exception e) { return e.InnerException == null ? e.Message : FindExceptMsg(e.InnerException); } }
7. 创建子类,并实现业务数据的增删改查:
public partial class SqlceStorageProvider : SqlceProvider, IStorage, IDisposable { static Object objCreate = new Object(); Entities db; public SqlceStorageProvider(string dataSource = "./Store.db") : base(dataSource) { db = new Entities(ecb.ConnectionString); try { Config v = db.Config.Find("DBVersion"); if (!v.value.Equals(DBUtilVersion)) { throw new Exception("Database version is not compatible"); } } catch (DbEntityValidationException e) { throw new DBException(e.HResult, (e.EntityValidationErrors.Count() > 0) ? e.EntityValidationErrors.First().ValidationErrors.First().ErrorMessage : e.Message); } catch (Exception e) { throw new DBException(e.HResult, FindExceptMsg(e)); } } public List<Patient> FindPatient(int page, int size, out int total, out DBException err, Patient search = null) { List<Patient> ret = null; total = 0; err = new DBException(); lock (this) { try { var query = from p in db.Patient select p; if (search != null) { if (search.FamilyName != null) { query = query.Where(p => p.FamilyName.StartsWith(search.FamilyName)); } } total = query.Count(); return query.OrderByDescending(p => p.id).Skip((page - 1) * size).Take(size).ToList(); } catch (DbEntityValidationException e) { err.Code = e.HResult; err.Msg = (e.EntityValidationErrors.Count() > 0) ? e.EntityValidationErrors.First().ValidationErrors.First().ErrorMessage : e.Message; } catch (Exception e) { err.Code = e.HResult; err.Msg = FindExceptMsg(e); } return ret; } } public int SaveOrChangePatient(Patient p, out DBException err) { int ret = -1; err = new DBException(); lock (this) { try { if (p.id > 0) { db.Entry(p).State = EntityState.Modified; } else { db.Patient.Add(p); } db.SaveChanges(); return p.id; } catch (DbEntityValidationException e) { err.Code = e.HResult; err.Msg = (e.EntityValidationErrors.Count() > 0) ? e.EntityValidationErrors.First().ValidationErrors.First().ErrorMessage : e.Message; } catch (Exception e) { err.Code = e.HResult; err.Msg = FindExceptMsg(e); } return ret; } } public Boolean RemovePatient(Patient p, out DBException err) { bool ret = false; err = new DBException(); lock (this) { try { db.Entry(p).State = EntityState.Deleted; db.SaveChanges(); return true; } catch (DbEntityValidationException e) { err.Code = e.HResult; err.Msg = (e.EntityValidationErrors.Count() > 0) ? e.EntityValidationErrors.First().ValidationErrors.First().ErrorMessage : e.Message; } catch (Exception e) { err.Code = e.HResult; err.Msg = FindExceptMsg(e); } return ret; } } }
8. 级联查询代码示例:
var query = from s in db.Study.Include("Patient") select s; if (search.Patname != "") { query = query.Where(s => s.Patient.FamilyName.StartsWith(search.Patname)); }
9.通过EF实现SQLCE操作已完成,直接创建DB实例,即可对数据库进行操作。