orm 对象关系映射框架,主要将关系数据库中的数据 ,映射成应用程序的对象。表为类名,列为类的字段。
ADO.NET entity framework 在ADO.NET基础上发展出来的对象关系模型 orm的解决方案。
LINQ,语言集成查询(Language INtegrated Query)是一组用于c#和Visual Basic语言的扩展。它允许编写C#或者Visual Basic代码以查询数据库相同的方式操作内存数据。
在开发过程中,有2种方式可以根据数据库的表生成相应的对象模型。
- OR设计器(直接拖放表,视图,存储过程到or设计器则自动生成 Northwind.dbml (包含Northwind.dbml.layout 和 Northwind.designer.cs)文件 (源代码及属性或者映射文件))
- 另一个是通过 sqlmetal 命令生成 Northwind.cs. 命令代码(将c/Northwind.mdf 生成Northwind.cs):通过 sqlmetal /code:"C:\Users\mikple\Documents\Visual Studio 2008\Projects\WebApplication1\WebApplication1\App_Data\northwind.cs" /language:csharp "C:\Users\mikple\Documents\Visual Studio 2008\Projects\WebApplication1\WebApplication1\App_Data\Northwind.MDF" /sprocs /functions /pluralize
第一种生成的 Northwind.designer.cs 里面存放的是主要的映射代码,和映射的属性。
第二种生成的Northwind.cs 里是根据数据库映射的文件。
区别:第一种通过or设计器生成的代码新建linq to sql类,命名 Northwind.dbml 映射文件会自动加上
public partial class NorthwindDataContext : System.Data.Linq.DataContext
相同: 都继承 System.Data.Linq.DataContext
1.使用OR设计器根据数据库生成映射文件
右击项目-->添加新建项-->选择 LINQ TO SQL类
新建后生成的文件,主要有用的是*.designer.cs 存放的是映射的代码,*.dbml.layout 主要是用来将表,存储过程,视图放到or设计器上,自动生成代码。
开始选定Northwind数据库,将admin表拖放到or设计器上
选定左侧的admin表,然后直接拖放到上图中间部分。
DataClasses1.designer.cs 包含了admin表和列的信息。
拖放后新增的代码如下(数据库itweb,表为admin)
Deigner.cs 新增的admin表和属性信息.#pragma warning disable 1591 //------------------------------------------------------------------------------ // <auto-generated> // 此代码由工具生成。 // 运行时版本:2.0.50727.5420 // // 对此文件的更改可能会导致不正确的行为,并且如果 // 重新生成代码,这些更改将会丢失。 // </auto-generated> //------------------------------------------------------------------------------ namespace WebApplication1 { using System.Data.Linq; using System.Data.Linq.Mapping; using System.Data; using System.Collections.Generic; using System.Reflection; using System.Linq; using System.Linq.Expressions; using System.ComponentModel; using System; [System.Data.Linq.Mapping.DatabaseAttribute(Name="ITweb")] public partial class DataClasses1DataContext : System.Data.Linq.DataContext { private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource(); #region Extensibility Method Definitions partial void OnCreated(); partial void Insertadmin(admin instance); partial void Updateadmin(admin instance); partial void Deleteadmin(admin instance); #endregion public DataClasses1DataContext() : base(global::System.Configuration.ConfigurationManager.ConnectionStrings["ITwebConnectionString"].ConnectionString, mappingSource) { OnCreated(); } public DataClasses1DataContext(string connection) : base(connection, mappingSource) { OnCreated(); } public DataClasses1DataContext(System.Data.IDbConnection connection) : base(connection, mappingSource) { OnCreated(); } public DataClasses1DataContext(string connection, System.Data.Linq.Mapping.MappingSource mappingSource) : base(connection, mappingSource) { OnCreated(); } public DataClasses1DataContext(System.Data.IDbConnection connection, System.Data.Linq.Mapping.MappingSource mappingSource) : base(connection, mappingSource) { OnCreated(); } public System.Data.Linq.Table<admin> admin { get { return this.GetTable<admin>(); } } } [Table(Name="dbo.admin")] public partial class admin : INotifyPropertyChanging, INotifyPropertyChanged { private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty); private int _id; private string _admin1; private string _password; #region Extensibility Method Definitions partial void OnLoaded(); partial void OnValidate(System.Data.Linq.ChangeAction action); partial void OnCreated(); partial void OnidChanging(int value); partial void OnidChanged(); partial void Onadmin1Changing(string value); partial void Onadmin1Changed(); partial void OnpasswordChanging(string value); partial void OnpasswordChanged(); #endregion public admin() { OnCreated(); } [Column(Storage="_id", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)] public int id { get { return this._id; } set { if ((this._id != value)) { this.OnidChanging(value); this.SendPropertyChanging(); this._id = value; this.SendPropertyChanged("id"); this.OnidChanged(); } } } [Column(Name="admin", Storage="_admin1", DbType="VarChar(50)")] public string admin1 { get { return this._admin1; } set { if ((this._admin1 != value)) { this.Onadmin1Changing(value); this.SendPropertyChanging(); this._admin1 = value; this.SendPropertyChanged("admin1"); this.Onadmin1Changed(); } } } [Column(Storage="_password", DbType="VarChar(50)")] public string password { get { return this._password; } set { if ((this._password != value)) { this.OnpasswordChanging(value); this.SendPropertyChanging(); this._password = value; this.SendPropertyChanged("password"); this.OnpasswordChanged(); } } } public event PropertyChangingEventHandler PropertyChanging; public event PropertyChangedEventHandler PropertyChanged; protected virtual void SendPropertyChanging() { if ((this.PropertyChanging != null)) { this.PropertyChanging(this, emptyChangingEventArgs); } } protected virtual void SendPropertyChanged(String propertyName) { if ((this.PropertyChanged != null)) { this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName)); } } } } #pragma warning restore 1591
2.开始使用Linq to sql 查询 admin 表的 所有id 列信息
protected void Page_Load(object sender, EventArgs e)
{
DataClasses1DataContext d1 = new DataClasses1DataContext(); //这个说明连接了数据库。
//从admin表中读取数据
var a = from b in d1.admin
select b.id;
foreach (var item in a)
{
Response.Write(item);
Response.Write("\r\n");
}
}
这个是自动生成的查询语句,可以通过sql profile 查看。
通过linq to sql 插入数据
执行代码
protected void Page_Load(object sender, EventArgs e) { DataClasses1DataContext d1 = new DataClasses1DataContext(); //向admin中插入数据 admin a = new admin(); a.admin1 = "mike"; a.password = "password12345"; d1.admin.InsertOnSubmit(a); //表中插入实体,只是更改了内存 d1.SubmitChanges(); //提交更改 }
update对象时候,直接在此对象上执行更新新值,然后通过 d1.SubmitChanges()操作。
protected void Page_Load(object sender, EventArgs e) { DataClasses1DataContext d1 = new DataClasses1DataContext(); //更改admin表中 admin属性为 mike的改为 jerry var u = (from c in d1.admin where (c.admin1 == "mike") select c).First(); u.admin1 = "jerry"; d1.SubmitChanges(); //提交更改 }
删除数据 调用 DeleteOnSubmit 以将该对象从集合中移除。最后,调用 SubmitChanges 以将删除内容转发至数据库。
例:删除admin表中,admin列为jerry的行数据。
linq to sql总结:
1. 数据库实例连接 DataClasses1DataContext d1 = new DataClasses1DataContext();
2. 查询数据: from xx in d1.表1
增加数据: 先生成实体,接着 insertOnSubmit(),最后 d1.SubmitChanges();
删除数据: 先查询到实体,然后deleteOnSubmit() ,最后 submitChanges();
更新数据: 查询到实体,直接在这实体上更改,最后SubmitChanges();s
下载的 Northwind 2005&&2008 的 *.bak文件,还原数据库。
下载的*.bak 文件放到 C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup
LINQ TO 存储过程(Northwind.mdf 数据库)
1. MSSQL管理器创建存储过程,拖到存储过程到or设计器中,自动生成 *.dbml。
[Function(Name="dbo.[Customers By City]")] //function 代表存储过程 public ISingleResult<Customers_By_City_个结果> Customers_By_City([Parameter(DbType="NVarChar(20)")] string param1) { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), param1); return ((ISingleResult<Customers_By_City_个结果>)(result.ReturnValue)); }
2. linq to 存储过程,调用刚才的存储过程返回集合
protected void Page_Load(object sender, EventArgs e) { DataClasses1DataContext db = new DataClasses1DataContext(); //调用存储过程 var result = db.Customers_By_City("London");//调用存储过程 foreach (var a in result) { Response.Write(a.ContactName +"<br/>"); } }
结果图:
------------------------------------------------------------------------
linq 存储过程
1)调用带输入参数的存储过程返回行集
DataClasses1DataContext db = new DataClasses1DataContext(); //调用存储过程 var result = db.Customers_By_City("London"); //存储过程名字 Customers_By_City foreach (var a in result) { Response.Write(a.ContactName +"<br/>"); }
2)调用带输入,输出参数的存储过程
DataClasses1DataContext db = new DataClasses1DataContext(); //调用存储过程 decimal? total = 0; //ref 必须有初值 var result = db.CustOrderTotal("ALFKI", ref total); //ref不能省略 Response.Write(total.ToString()+"<br/>");
-----------------------------------------
Visual Studio 的开发人员通常会使用对象关系设计器来映射存储过程
或者 自行编写代码。
为多个结果形状映射的存储过程 (LINQ to SQL)
CREATE PROCEDURE VariableResultShapes(@shape int) AS if(@shape = 1) select CustomerID, ContactTitle, CompanyName from customers else if(@shape = 2) select OrderID, ShipName from orders
通过 sqlmetal /code:"C:\Users\mikple\Documents\Visual Studio 2008\Projects\WebApplication1\WebApplication1\App_Data\northwind.cs" /language:csharp "C:\Users\mikple\Documents\Visual Studio 2008\Projects\WebApplication1\WebApplication1\App_Data\Northwind.MDF" /sprocs /functions /pluralize
此处用or设计器的不足就体现出来了,用or设计器只会产生 VariableResultShapesResult1 返回集类型。用sqlmetal命令则可以自动生成2种类型。
自动生成Linq to sql类 Northwind.cs
VariableResultShapesResult1 和 VariableResultShapesResult2public partial class VariableResultShapesResult1 { private string _CustomerID; private string _ContactTitle; private string _CompanyName; public VariableResultShapesResult1() { } [Column(Storage = "_CustomerID", DbType = "NChar(5)")] public string CustomerID { get { return this._CustomerID; } set { if ((this._CustomerID != value)) { this._CustomerID = value; } } } [Column(Storage = "_ContactTitle", DbType = "NVarChar(30)")] public string ContactTitle { get { return this._ContactTitle; } set { if ((this._ContactTitle != value)) { this._ContactTitle = value; } } } [Column(Storage = "_CompanyName", DbType = "NVarChar(40)")] public string CompanyName { get { return this._CompanyName; } set { if ((this._CompanyName != value)) { this._CompanyName = value; } } } } public partial class VariableResultShapesResult2 { private System.Nullable<int> _OrderID; private string _ShipName; public VariableResultShapesResult2() { } [Column(Storage = "_OrderID", DbType = "Int")] public System.Nullable<int> OrderID { get { return this._OrderID; } set { if ((this._OrderID != value)) { this._OrderID = value; } } } [Column(Storage = "_ShipName", DbType = "NVarChar(40)")] public string ShipName { get { return this._ShipName; } set { if ((this._ShipName != value)) { this._ShipName = value; } } } }
生成的dbo.VariableResultShapes存储过程代码 [Function(Name = "dbo.VariableResultShapes")] [ResultType(typeof(VariableResultShapesResult1))] [ResultType(typeof(VariableResultShapesResult2))] public IMultipleResults VariableResultShapes([Parameter(DbType = "Int")] System.Nullable<int> shape) { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), shape); return ((IMultipleResults)(result.ReturnValue)); }
通过sqlmetal生成代码,可以自动感知存储过程可能会返回2种不同的结果集,当然对应的关系实体也不同。则自动生成 VariableResultShapesResult1 和2.
开始使用其代码 GetResult
public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { Northwind db = new Northwind(@"C:\Northwind.MDF"); IMultipleResults result = db.VariableResultShapes(1); foreach (VariableResultShapesResult1 compName in result.GetResult<VariableResultShapesResult1>()) { Response.Write(compName.CompanyName+"<br/>"); } // sqlmetal /code:"C:\Users\mikple\Documents\Visual Studio 2008\Projects\WebApplication1\WebApplication1\App_Data\northwind.cs" /language:csharp "C:\Users\mikple\Documents\Visual Studio 2008\Projects\WebApplication1\WebApplication1\App_Data\Northwind.MDF" /sprocs /functions /pluralize }
*使用为顺序结果形状映射的存储过程 (LINQ to SQL)
CREATE PROCEDURE MultipleResultTypesSequentially AS select * from products select * from customers
新添加存储过程后,可以通过手写代码添加存储过程的映射,也可以通过sqlmetal 命令 自动生成*.cs
使用sqlmetal命令后,Northwind.cs 新增的代码如下。
dbo.MultipleResultTypesSequentially [Function(Name = "dbo.MultipleResultTypesSequentially")] [ResultType(typeof(MultipleResultTypesSequentiallyResult1))] [ResultType(typeof(MultipleResultTypesSequentiallyResult2))] public IMultipleResults MultipleResultTypesSequentially() { IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod()))); return ((IMultipleResults)(result.ReturnValue)); }
返回的2种结果集类型public partial class MultipleResultTypesSequentiallyResult1 { private System.Nullable<int> _ProductID; private string _ProductName; private System.Nullable<int> _SupplierID; private System.Nullable<int> _CategoryID; private string _QuantityPerUnit; private System.Nullable<decimal> _UnitPrice; private System.Nullable<short> _UnitsInStock; private System.Nullable<short> _UnitsOnOrder; private System.Nullable<short> _ReorderLevel; private System.Nullable<bool> _Discontinued; public MultipleResultTypesSequentiallyResult1() { } [Column(Storage = "_ProductID", DbType = "Int")] public System.Nullable<int> ProductID { get { return this._ProductID; } set { if ((this._ProductID != value)) { this._ProductID = value; } } } [Column(Storage = "_ProductName", DbType = "NVarChar(40)")] public string ProductName { get { return this._ProductName; } set { if ((this._ProductName != value)) { this._ProductName = value; } } } [Column(Storage = "_SupplierID", DbType = "Int")] public System.Nullable<int> SupplierID { get { return this._SupplierID; } set { if ((this._SupplierID != value)) { this._SupplierID = value; } } } [Column(Storage = "_CategoryID", DbType = "Int")] public System.Nullable<int> CategoryID { get { return this._CategoryID; } set { if ((this._CategoryID != value)) { this._CategoryID = value; } } } [Column(Storage = "_QuantityPerUnit", DbType = "NVarChar(20)")] public string QuantityPerUnit { get { return this._QuantityPerUnit; } set { if ((this._QuantityPerUnit != value)) { this._QuantityPerUnit = value; } } } [Column(Storage = "_UnitPrice", DbType = "Money")] public System.Nullable<decimal> UnitPrice { get { return this._UnitPrice; } set { if ((this._UnitPrice != value)) { this._UnitPrice = value; } } } [Column(Storage = "_UnitsInStock", DbType = "SmallInt")] public System.Nullable<short> UnitsInStock { get { return this._UnitsInStock; } set { if ((this._UnitsInStock != value)) { this._UnitsInStock = value; } } } [Column(Storage = "_UnitsOnOrder", DbType = "SmallInt")] public System.Nullable<short> UnitsOnOrder { get { return this._UnitsOnOrder; } set { if ((this._UnitsOnOrder != value)) { this._UnitsOnOrder = value; } } } [Column(Storage = "_ReorderLevel", DbType = "SmallInt")] public System.Nullable<short> ReorderLevel { get { return this._ReorderLevel; } set { if ((this._ReorderLevel != value)) { this._ReorderLevel = value; } } } [Column(Storage = "_Discontinued", DbType = "Bit")] public System.Nullable<bool> Discontinued { get { return this._Discontinued; } set { if ((this._Discontinued != value)) { this._Discontinued = value; } } } } public partial class MultipleResultTypesSequentiallyResult2 { private string _CustomerID; private string _CompanyName; private string _ContactName; private string _ContactTitle; private string _Address; private string _City; private string _Region; private string _PostalCode; private string _Country; private string _Phone; private string _Fax; public MultipleResultTypesSequentiallyResult2() { } [Column(Storage = "_CustomerID", DbType = "NChar(5)")] public string CustomerID { get { return this._CustomerID; } set { if ((this._CustomerID != value)) { this._CustomerID = value; } } } [Column(Storage = "_CompanyName", DbType = "NVarChar(40)")] public string CompanyName { get { return this._CompanyName; } set { if ((this._CompanyName != value)) { this._CompanyName = value; } } } [Column(Storage = "_ContactName", DbType = "NVarChar(30)")] public string ContactName { get { return this._ContactName; } set { if ((this._ContactName != value)) { this._ContactName = value; } } } [Column(Storage = "_ContactTitle", DbType = "NVarChar(30)")] public string ContactTitle { get { return this._ContactTitle; } set { if ((this._ContactTitle != value)) { this._ContactTitle = value; } } } [Column(Storage = "_Address", DbType = "NVarChar(60)")] public string Address { get { return this._Address; } set { if ((this._Address != value)) { this._Address = value; } } } [Column(Storage = "_City", DbType = "NVarChar(15)")] public string City { get { return this._City; } set { if ((this._City != value)) { this._City = value; } } } [Column(Storage = "_Region", DbType = "NVarChar(15)")] public string Region { get { return this._Region; } set { if ((this._Region != value)) { this._Region = value; } } } [Column(Storage = "_PostalCode", DbType = "NVarChar(10)")] public string PostalCode { get { return this._PostalCode; } set { if ((this._PostalCode != value)) { this._PostalCode = value; } } } [Column(Storage = "_Country", DbType = "NVarChar(15)")] public string Country { get { return this._Country; } set { if ((this._Country != value)) { this._Country = value; } } } [Column(Storage = "_Phone", DbType = "NVarChar(24)")] public string Phone { get { return this._Phone; } set { if ((this._Phone != value)) { this._Phone = value; } } } [Column(Storage = "_Fax", DbType = "NVarChar(24)")] public string Fax { get { return this._Fax; } set { if ((this._Fax != value)) { this._Fax = value; } } } }
程序中使用刚才的存储过程
Northwnd db = new Northwnd(@"c:\northwnd.mdf");
IMultipleResults sprocResults =
db.MultipleResultTypesSequentially();
// First read products.
foreach (Product prod in sprocResults.GetResult<Product>())
{
Console.WriteLine(prod.ProductID);
}
// Next read customers.
foreach (Customer cust in sprocResults.GetResult<Customer>())
{
Console.WriteLine(cust.CustomerID);
}
使用存储过程来自定义操作 (LINQ to SQL)
将存储过程的方法集合到另外一个类,此类用来供应用程序调用,此类封装了Northwind.cs 里的方法。
using System; using System.Collections.Generic; using System.Linq; using System.Web; public class NorthwindThroughSprocs:Northwind { public NorthwindThroughSprocs(string connection) : base(connection) { } // Override loading of Customer.Orders by using method wrapper. private IEnumerable<Order> LoadOrders(Customer customer) { return this.CustomerOrders(customer.CustomerID); } // Override loading of Order.Customer by using method wrapper. private Customer LoadCustomer(Order order) { return this.CustomersByID(order.CustomerID).Single(); } // Override INSERT operation on Customer by calling the // stored procedure directly. private void InsertCustomer(Customer customer) { // Call the INSERT stored procedure directly. this.ExecuteCommand("exec sp_insert_customer …"); } // The UPDATE override works similarly, that is, by // calling the stored procedure directly. private void UpdateCustomer(Customer original, Customer current) { // Call the UPDATE stored procedure by using current // and original values. this.ExecuteCommand("exec sp_update_customer …"); } // The DELETE override works similarly. private void DeleteCustomer(Customer customer) { // Call the DELETE stored procedure directly. this.ExecuteCommand("exec sp_delete_customer …"); } }
//添加验证实体方法。 On字段名字Changing 方法。 规定插入数据库的值不能早于今天
partial void OnRequiredDateChanging(System.Nullable<System.DateTime> value)
{
if (value < System.DateTime.Today)
{
throw new System.Exception("Required Date cannot be in the past");
}
}