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.