A good approach for data access in both a custom intra-site and a pluggable module is to encapsulate the data access classes in a separate class library project. While any
standard .NET data access approach can be used, the sample data access project for the Jobs module (Telerik.Samples.Jobs.DataAccess) is implemented using Nolics.net
2005 which is also used by Sitefinity. Nolics.net 2005 is a domain-specific language (DSL) object relational mapping tool.
Nolics.net 2005 automatically generates code and classes that provide the functionality for interfacing with the database, creating instance of the data objects, and
data operations like insert, update, and so on.
Many of the principles necessary for creating a data access project with Nolics.net are discussed in Implementing Data Access with Nolics.NET. This section discusses details on how data access is implemented in the
Telerik.Samples.Jobs.Data data access project as a sample implementation.
Creating the Data Access Project
The first step is to create a separate Telerik.Samples.Jobs.Data project which will contain the persistent data definitions, query
definitions, and behaviors for the Jobs data access.
- Select the File/New/Project menu option in Visual Studio or create a blank solution. Then, add the project by right-clicking on the solution node in the Solution
Explorer. Figure 1 shows how to add a new project context menu selection:
Figure 1
-
In the Add New Project window, enter the project name. Figure 2 shows how to select and add the new data access project using the Nolics.net Database
Class Library project type:
Figure 2
 |
When creating a project for a custom module it is important to use a namespace that will not collide with any other
assemblies in Sitefinity. Therefore, a suggested format is: <CompanyName>.<ModuleName>.Data |
The new Nolics.net Database Class Library project is added to the solution.
Creating the Data Access Classes
Before creating the data access classes, review Implementing Data Access with
Nolics.NET to become familiar with the concepts used in creating data access classes with Nolics.net. Essentially, each data access class represents a business entity such as
a Job or JobType. Figure 3 displays the Telerik.Samples.Jobs.Data data access classes:
Figure 3
The three data access classes (.dbclass) represent the job information, category of job (Sales, Management, and so on), and the type of job (Sales Associate,
Development Manager, and so on). Figure 4 illustrates the schema for the data access classes depicting their relationships:
Figure 4
Job Class
The Job.dbclass defines the details for each job in the job listing. Each Job instance has a link to a related instance of the Job category and Job type
associated with the job.
Persistent Data Definition
| [C#] |
 Copy Code |
|
dbclass Job [TableName="sf_Jobs_Job",WebBinder=true]
{
primary key guid ID[AutoGenGUID=True];
string Title[Length=100];
//This links the Job to a single JobCategory.
link JobCategory;
//This links the Job to a single JobType.
link JobType;
string Description;
string Requirements;
string ExperienceLevel[Length=100];
string EducationLevel[Length=100];
string Location[Length=100];
string ContactPerson[Length=50];
string ContactPhone[Length=20];
bool Active;
date PostUntil;
modified date DatePosted;
}
|
Each property in the class is defined using the Nolics.net domain-specific language syntax which resembles a hybrid of a class property and a database column definition. There
are a few aspects of this definition worth noting:
- The link to JobCategory and JobType establish relationships to the corresponding classes. Nolics.net maintains all the aspects of these relationships automatically.
- Attributes of the Job class, the table name in the database (TableName="sf_Jobs_Job), and the instances of the Job class can be bound to Web controls
(WebBinder=true).
- The modified keyword for the DatePosted property automatically inserts a data/time value when a new instance of the Job class is instantiated.
- AutoGenGUID=True automatically generates a GUID primary key for each job.
Query Definition
The GetJobs query definition defines a static Nolics.net query for retrieving a collection of Job objects with a filter on the Active and
JobCategory properties.
| [C#] |
Copy Code |
|
query GetJobs for Job [ProcedureName = "sf_Job_GetJobs",OrderBy="DatePosted DESC",WebBinder=true]
{
bool Active ?= Active;
long JobCategoryID ?= JobCategory_ID;
}
|
Some useful aspects of this query definition include:
- Attributes of the GetJobs query define the stored procedure name (ProcedureName), order by clause (OrderBy), and the collection of Job objects can be bound to a Web
control.
- The ?= syntax indicates the filter parameter is optional.
Job.cs Partial Class
The Job class definition also has a partial class (Job.cs) that is a companion to the Job.dbclass file which defines behaviors for the Job
object as well as a few additional properties and dynamic queries.
The Job class defines a behavior for the creation of each new Job object by implementing the IOdbEventCreate interface.
| Job.cs |
Copy Code |
|
public partial class Job : IOdbEventCreate
|
And then, the corresponding CreateObject method which sets the values of a few properties when a new instance of the object is created.
| CreateObject() |
Copy Code |
|
public void CreateObject(OdbDataProvider provider)
{
//Default to posting new jobs for one month.
PostUntil = System.DateTime.Now.AddMonths(1);
//New jobs are active by default.
Active = true;
}
|
In addition, the class contains the public JobCategory and JobType properties.
| String JobCategory |
Copy Code |
|
[StringValue(Transient = true)]
public string JobCategory
{
get
{
if (JobCategory_lnk == null)
{
return "(None)";
}
return JobCategory_lnk.Category;
}
}
|
Some useful aspects of the JobCategory property are:
- The StringValue/Transient attribute indicating this property contains a string and it is not cached but retrieved each time the property is accessed.
- The JobCategory_lnk object which is a Nolics.net convention represents a reference to the JobCategory object linked to this Job object.
The Job.cs partial class also contains several dynamic query definitions.
| [C#] |
Copy Code |
|
/// <summary>
/// Dynamic query for filtering job data. Used in conjunction with the GetJobs query.
/// </summary>
public class JobsFilter : Query<Job>
{}
/// <summary>
/// Dynamic query associated with a view for retrieving a customized
/// set of summary data for jobs.
/// </summary>
public class JobListSummary :
Query<Queries.JobListSummary_View> {}
|
The JobFilter class is a Nolics.net dynamic Query consisting of a class that implements IOdbView - in this case a Job object. The
Queries.JobListSummary contained in the Job.dbclass file is also a Nolics.net dynamic Query which consists of a Nolics.net View defined in the Job.dbclass
file.
| JobListSummary_View |
Copy Code |
|
view JobListSummary_View [ViewName="dbo.sf_Jobs_JobListSummary_View",ManualUpgrade=true]
SQLView["AS Select COUNT(ID) AS JobCount
"
"From sf_Jobs_Job "
"Where Active=1"]
{
int JobCount [FieldName = "JobCount"];
}
|
 |
The Query<IDbView> is utilizing the Generics introduced in the .NET Framework 2.0. |
JobCategory Class
The job category class defines the standard categories of jobs that are used to classify each job in the job listing.
Persistent Data Definition
The definition contains a key value and a description for each job category. Note that the AutoGen=True automatically generates a primary key for each job category. This
is similar to the primary key generation for the Job but is an integer value instead of a GUID.
| DB class JobCategory |
Copy Code |
|
dbclass JobCategory [TableName="sf_Jobs_JobCategory",WebBinder=true]
{
primary key long ID[AutoGenID=true];
string Category[Length=100];
}
|
Query Definition
The GetJobCategories static query is an alphabetical list of job categories.
| GetJobCategories |
Copy Code |
|
query GetJobCategories for JobCategory[ProcedureName = "sf_Job_GetJobCategories",OrderBy="Category",WebBinder=true]
{
}
|
JobType Class
The definition contains a key value and a description for each job type.
Persistent Data Definition
| DB class JobType |
Copy Code |
|
dbclass JobType [TableName="sf_Jobs_JobType",WebBinder=true]
{
primary key long ID[AutoGenID=true];
string Type[Length=100];
}
|
Query Definition
The GetJobTypes static query is an alphabetical list of job types.
| GetJobTypes |
Copy Code |
|
query GetJobTypes for JobType[ProcedureName = "sf_Job_GetJobTypes",OrderBy="Type",WebBinder=true]
{
}
|
Creating a Configuration File for Testing
The Nolics.net connection string used by Sitefinity is in the web.config file located in the Sitefinity installation. When a custom module is added to
Sitefinity either as an intra-site (.ascx files) or a pluggable (assembly/.dll) module that references the stand-alone data access class
library, Nolics.net will automatically read the connection string used for Sitefinity.
However, to execute the data access class library independently, that is - unit tests, there needs to be a separate app.config file located
in the project that is executed. To create an app.config file and set the Nolics.net connection string, use the steps described in the previous topic:
Creating a Configuration File for Testing.
|
The Sitefinity SQL Server 2005 database is located in the /App_Data folder of the Sitefinity Web site by default. |
See Also