Open source object relationship ing tool Orm. Net query Table call Stored Procedure added custom code

Lookup Table

In the orm. Net Object Browser, specify the table as a lookup table, as shown in

Lookup indicates that the data in this table is read-only and will not generate any new additions. The delete object method (add [object], new [object], or delete [object]) is used. the attribute of a column does not contain the set attribute. Therefore, the lookup table is used to represent static data that will not change. In this way, the Orm. NET Framework caches its values to improve performance. See the followingCode

 
// Create a new lookup object-a sub-classed datamanagerLookups lookup =NewLookups (config. DSN );// Find a specific course and assign a local object referenceCourse course = lookup. Courses. findbyclassname (& Quot; History 101 & quot");// Display some property information about the course retrieved from the cached lookup objectConsole. writeline ("Course"+ Course. classname +""+ Course. ID );
 

The following example demonstrates the usage of collection, and finds that the course creation date is later than 2002/11/06.

// Assign a local course collectionCoursecollection courses = lookup. courses;// Loop through each record in the Course tableForeach(Course CInCourses) console. writeline ("Course name :"+ C. classname );// Create new course collection with filter applied on the datecreated PropertyCoursecollection oldcourses = courses. filterbydatecreated (datetime. parse ("11/06/2002"));
 

The following code shows how to obtain data from lookup.

 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  

Another function of lookup table is to stick to ASP. NET dropdownlist. Please refer to the sample code and the code is concise.

 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  

If you need to update the lookup value, such as maintaining data management separatelyProgram, You can callLookup. rerefreshlookups ()To refresh the cache and obtain the lookup data again.

 

Call the Stored Procedure Working with Stored Procedures

Use ORM. one of the benefits of net is that it can run on multiple database platforms through a set of code (although Orm.. Net currently only supports SQL Server), so try to use less methods related to specific databases, such as stored procedures. If the dependency on the existing stored procedure cannot be removed during system maintenance, Orm. Net also provides methods to access the stored procedure.

See the following SQL Server Stored Procedure Definition.

 
Alter ProcSpgetcontactdetails @ lastnameVarchar(50), @ CityVarchar(50)Output,@State Varchar(50)OutputAsSelect@ City = city ,@State=StateFromTeacher T, contact CWhereT. fkcontactid = C. IDAndLastname = @ lastname
 

The ORM. NET Framework automatically generates. Net code to encapsulate access to it. Net code is as follows:

Datamanager dm =NewDatamanager (config. DSN );StringCityname ="";// Define and initialize SQL output ParamsStringStatename ="";// Pass input and output parameters to the stored procedureDataset DS = storedprocedures. spgetcontactdetails ("Goldberg",RefCityname,RefStatename); console. writeline ("Display Results :"+ Cityname +""+ Statename );
 

For each stored procedure in the database, a static method is generated and stored in the storedprocedures type. As shown in the above Code.

The source code of this method is as follows. Although it is a simple encapsulation, it greatly simplifies the code for the client to call the stored procedure.

 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 ;}

For example, the stored procedure of the returned dataset returns the total number of students in the city and the city.

Create ProcSpstudentsbycityAsSelectCity,Count(C. ID)As 'Total'FromStudent s, contact CWhereS. fkcontactid = C. IDGroup ByCity
 

C #. Net call code is as follows:

 
Dataset ds1 = storedprocedures. spstudentsbycity ();Foreach(Datarow DrInDs1.tables [0]. Rows) console. writeline ("City :"+ Dr ["City"] +"Total :"+ Dr ["Total"]);
 

If the stored procedure returns multiple datasets, the following code shows:

  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 provides an additional method populateobjectsfromdataset to expand the obtained data.

 dataset DS = storedprocedures. spstudentcontact ( "Johnson"  );  // call the Stored Procedure  string [] arrtables = { "student" ,  "Contact"  };  // create the array with the tables encoded 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); 

The Code shows that the populateobjectsfromdataset method fills the queried dataset into the specified collection.

 

Subclass to add custom code sub-classing and extending generated class files

Orm. Net takes into account application development and modifies the type definition it generates. Please refer to the code

 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 shocould 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 ;}}}

In the generated project code, add a method

 
Public StringFullname (){Return(This. Firstname +""+This. Lastname );}
 

More suitable, it should be added as an attribute

Public Override StringLastname {get {Return(Base. Lastname. tolower ();} set {// Ensure that the lastname is always set// Lower-caseBase. Lastname =Value. Tolower ();}}
 

If the database script or link changes, you need to re-define the code entity, and the code will not be lost.