开放源码的对象关系映射工具ORM.NET 查询表 调用存储过程 增加自定义代码

Lookup表

在ORM.NET Object Browser中指定表是lookup表，如下图所示

Lookup表明此表的数据是只读的，不会生成任何的新增，删除对象方法(Add[Object], New[Object], or Delete[Object])，列的属性也不包含Set属性。因此,Lookup表是用来表示static的数据，不会发生变化的数据。这样，ORM.NET框架会cache缓存它的值以改善性能。请看下面的代码

// Create a new Lookup object – a sub-classed DataManager
Lookups lookup = new Lookups(Config.Dsn);
// find a specific course and assign a local object reference
Course course = lookup.Courses.FindByClassName("History 101");
// Display some property information about the Course retrieved from the cached Lookup object
Console.WriteLine("Course " + course.ClassName + " " + course.ID);

下面的例子是演示collection的用法，查找创建课程日期大于2002/11/06

// Assign a local Course Collection
CourseCollection courses = lookup.Courses;
// loop through each record in the Course table
foreach(Course c in courses)
  Console.WriteLine("Course Name: " + c.ClassName);
//Create new Course collection with filter applied on the DateCreated property
CourseCollection oldcourses = courses.FilterByDateCreated(DateTime.Parse("11/06/2002"));

下面的代码，演示从Lookup中获取数据

Lookups lookups = new Lookups(Config.Dsn);
Schedule s = dm.NewSchedule();  // Create a new Schedule object
// assign the Schedule object to the desired Course object (Parent)
s.Course = lookups.Courses.FindByClassName("History 101"); 
//.. add the rest of the schedule information
dm.CommitAll(); // create the Schedule object with the Parent Course information

Lookup表的另一个作用是用来棒定到ASP.NET DropDownList，请看代码示例，代码简洁

Lookups lookups = new Lookups(Config.Dsn);
// Courses is set as the DataSource - Courses is returned sorted by ID in ascending order.
DropDownListCourses.DataSource = lookups.Courses.SortByID(SortDirection.Ascending);
DropDownListCourses.DataBind();  // bind the data source

如果需要更新Lookup的值，比如在单独的维护数据管理程序中，可以调用Lookup.ReRefreshLookups()来刷新cache，重新获取lookup数据。

调用存储过程 Working with Stored Procedures

使用ORM.NET的好处之一是，可以通过一套代码，运行在多种数据库平台中(虽然ORM.NET当前只支持SQL Server)，所以尽量少用与特定数据库相关的方式，比如存储过程。如果是在系统维护过程中，暂时无法移除对现有的存储过程的依赖，ORM.NET也提供了方法以访问存储过程。

请看下面的SQL Server存储过程定义

ALTER PROC spGetContactDetails
@LastName varchar(50),
@City varchar(50) OUTPUT,
@State varchar(50) OUTPUT
AS
SELECT @City=City, @State=state 
             FROM Teacher t, Contact c 
             WHERE  t.FKContactId=c.Id     AND   LastName=@LastName

ORM.NET框架会自动生成.NET代码封装对它的访问，.NET代码如下

DataManager dm = new DataManager(Config.Dsn);
string CityName = "";              // define and initialize SQL Output params
string StateName = "";
// Pass Input and Output parameters to the Stored procedure
DataSet ds = StoredProcedures.spGetContactDetails("Goldberg",ref CityName, ref StateName);  
Console.WriteLine("Display results: " + CityName + " " + StateName);

针对数据库中的每个存储过程，会生成一个static的方法，放在StoredProcedures类型中。如上代码所示。

这个方法的源代如下所示，虽然只是个简单的封装，却大大简化了客户端调用存储过程的代码。

public static DataSet spGetContactDetails(  System.String LastName, ref System.String City,   
                                          ref  System.String State )
{
     ArrayList arrayParams = new ArrayList();
     SqlParameter paramLastName = new SqlParameter( "@LastName", LastName);
     paramLastName.SqlDbType = (SqlDbType) Enum.Parse( typeof(SqlDbType), "varchar", true);
     paramLastName.Direction = ParameterDirection.Input;
     arrayParams.Add( paramLastName );

     SqlParameter paramCity = new SqlParameter( "@City", City);
     paramCity.SqlDbType = (SqlDbType) Enum.Parse( typeof(SqlDbType), "varchar", true);
     paramCity.Direction = ParameterDirection.Output;
     arrayParams.Add( paramCity );

     SqlParameter paramState = new SqlParameter( "@State", State); 
     paramState.SqlDbType = (SqlDbType) Enum.Parse( typeof(SqlDbType), "varchar", true);
     paramState.Direction = ParameterDirection.Output;
     arrayParams.Add( paramState );
     DataSet ds = dm.ExecuteProcedure("spGetContactDetails", (SqlParameter[])              
     arrayParams.ToArray(typeof(SqlParameter)));

     City = (System.String) paramCity.Value;
     State = (System.String) paramState.Value;

     return ds;
}

再举例，返回数据集的存储过程，下面的存储过程返回城市和居住在城市里的学生的合计数

CREATE PROC spStudentsByCity
AS
  SELECT City, Count(c.Id) as 'Total'  FROM Student s, Contact c
  WHERE s.FKContactId=c.ID
GROUP BY City

C#.NET调用代码如下所示

DataSet ds1 = StoredProcedures.spStudentsByCity();
foreach (DataRow dr in ds1.Tables[0].Rows)
       Console.WriteLine ("City: " + dr["City"] + " Total: " + dr["Total"]);

如果存储过程返回多个数据集，如下的代码所示

ALTER Proc spStudentContact  @LastName varchar(50)
AS
DECLARE @ContactId int
DECLARE @FKContactId int
SELECT * FROM Student WHERE LastName=@LastName
SELECT @FKContactId=FKContactID FROM Student WHERE LastName=@LastName
SELECT * FROM Contact WHERE ID=@FKContactId

ORM.NET提供了额外的方法PopulateObjectsFromDataSet来展开得到的数据

DataSet ds = StoredProcedures.spStudentContact("Johnson");  // call the stored procedure
String[] arrTables = {"Student","Contact"};   // create the array with the tables included in the DataSet
dm.PopulateObjectsFromDataSet(ds,arrTables);
StudentCollection students = dm.GetStudentCollectionFromDataSet(); 
ContactCollection contacts = dm.GetContactCollectionFromDataSet();
// Work with the objects
foreach(Contact c1 in contacts)
Console.WriteLine("Address: " + c1.Address1);

从代码可以看出，方法PopulateObjectsFromDataSet把查询到的数据集填充到指定的collection中。

子类化，增加自定义代码 Sub-classing and extending Generated Class files

ORM.NET考虑到了应用程序开发中，会修改它生成的类型定义。请看代码

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
namespace SampleAppBiz
{
       /// <summary>
       /// Wraps a row and it's columns/children/parents
       /// This class should be customized.
      /// </summary>
       public class Student : StudentTemplate
       {
              /// <summary>
              /// Constructor must have a row and data context.
              /// </summary>
          internal Student( DataManager dataContext, DataRow ROW) : base( dataContext, ROW)
          {
                     row = ROW;
          }
       }
}

在生成的项目代码中，添加方法

public string FullName()
{
    return (this.FirstName + " " + this.LastName);
} 

更合适的，应该是添加为属性

public override string LastName
           {
                 get
                 {
                       return (base.LastName.ToLower());
                 }
                 set
                 {
                       // ensure that the LastName is always set to 
                       // lower-case
                       base.LastName = value.ToLower();
                 }    
}

如果数据库脚本或是关系发生变化，需要重新代码实体定义，这些代码也不会丢失。