The step-by-step guide of making ADO.NET Data Services for ADO.NET Entity Data Model, LINQ to SQL Classes, and non-relational data
My name is Lingzhi Sun and I am a MSDN Community support engineer. In the last year, I mainly supported the Visual C# and VB.NET related MSDN forums. Recently I started to focus on the data platform forums. There are many data platform related samples in All-In-One Code Framework that I want to recommend to you. Today I will introduce two of them about ADO.NET Data Services. Hope you like them!
The ADO.NET Data Services framework consists of a combination of patterns and libraries that enable the creation and consumption of data services for the web. The results in the data service being surfaced to the web as a REST-style resource collection that is addressable with URIs and the agents can interact with using the usual HTTP verbs such as GET, POST or DELETE.
The ADO.NET Data Services is new REST-style web service which can be used for many different data sources. CSADONETDataService and VBADONETDataService samples in All-In-One Code Framework demonstrate how to build ADO.NET Data Services for ADO.NET Entity Data Model, LINQ to SQL Classes, and non-relational data respectively using Visual C# and VB.NET.
Here is the step-by-step creation guide of the sample:
A. Creating an ordinary ASP.NET Web Application
1. In Visual Studio 2008, add a new Visual C#/ Web / ASP.NET Web Application project named CSADONETDataService.
2. Delete the web page Default.aspx and the App_Data folder.
3. Add two new folders named LinqToEntities and LinqToSQL respectively.
B. Creating an ADO.NET Data Service for ADO.NET Entity Data Model.
1. Creating an ADO.NET Entity Data Model as the data source in Visual Studio 2008.
1) In the folder LinqToEntities, add a new Entity Data Model named SchoolLinqToEntities.edmx.
2) In the Entity Data Model Wizard, configure the connection string and database information, set the entity name as SQLServer2005DBEntities, and Data Model name as SQLServer2005DBModel.
(Connect to the SQLServer2005DB database deployed by the SQLServer2005DB database project in All-In-One Code Framework)
3) In Tables tab, select tables Course, CourseGrade, CourseInstructor, and Person.
(For detail, please see How to: Create a New Entity Data Model.)
2. Creating and configuring an ADO.NET Data Service for the ADO.NET Entity Data Model.
1) Add a new ADO.NET Data Service named SchoolLinqToEntities.svc.
2) Set the SchoolLinqToEntities Data Service class targeting to the SQLServer2005DBEntities.
public class SchoolLinqToEntities : DataService<SQLServer2005DBEntities>
3) Create a service operation CoursesByPersonID to retrieve the instructor’s courses list by the primary key PersonID.
/// <summary>
/// A service operation that retrieve the instructor's courses list
/// by primary key PersonID
/// </summary>
/// <param name="ID">The primary key PersonID</param>
/// <returns>An IQueryable collection contains Course objects
/// </returns>
[WebGet]
public IQueryable<Course> CoursesByPersonID(int ID)
{
// Check whether the PersonID is valid and it is instructor's ID
if (this.CurrentDataSource.Person.Any(i => i.PersonID == ID &&
i.PersonCategory == 2))
{
// Retrieve the instructor's course list
var courses = from p in this.CurrentDataSource.Person
where p.PersonID == ID
select p.Course;
// Return the query result
return courses.First().AsQueryable();
}
else
{
// Throw DataServiceException if the PersonID is invalid or
// it is student's ID
throw new DataServiceException(400, "Person ID is incorrect"+
" or the person is not instructor!");
}
}
4) Create a service operation GetPersonUpdateException to retrieve the information of the Person table update exception.
/// <summary>
/// A service operation that retrieve the person update exception
/// information
/// </summary>
/// <returns>An IQueryable collection contains Person objects
/// </returns>
[WebGet]
public IQueryable<Person> GetPersonUpdateException()
{
// Throw the person update exception to the client
throw new DataServiceException(400,
"The valid value of PersonCategory is 1" +
"(for students) or 2(for instructors)."); ;
}
5) Create a service query interceptor to filter the Course collection to only return the Course objects whose CourseID is larger than 4000.
/// <summary>
/// A service query interceptor that filters the course objects to
/// return the course which CourseID is larger than 4000
/// </summary>
/// <returns>LINQ lambda expression to filter the course objects
/// </returns>
[QueryInterceptor("Course")]
public Expression<Func<Course, bool>> QueryCourse()
{
// LINQ lambda expression to filter the course objects
return c => c.CourseID > 4000;
}
6) Create a service change interceptor to check the PersonCategory value of the newly added or modified Person objects.
/// <summary>
/// A service change interceptor that checks the PersonCategory value
/// of the added or changed Person object
/// </summary>
/// <param name="p">The added or changed Person object</param>
/// <param name="operation">The update operation</param>
[ChangeInterceptor("Person")]
public void OnChangePerson(Person p, UpdateOperations operation)
{
// Check whether the update operation is Add or Change
if (operation == UpdateOperations.Add ||
operation == UpdateOperations.Change)
{
// Check invalid value of PersonCategory
if (p.PersonCategory != 1 && p.PersonCategory != 2)
{
// Throw DataServieException
throw new DataServiceException(400,
"The valid value of PersonCategory is 1" +
"(for students) or 2(for instructors).");;
}
}
}
7) Override the HandleException method to throw 400 Bad Request exception to the client side.
/// <summary>
/// Override the HandleException method to throw 400 Bad Request
/// exception to the client side.
/// </summary>
/// <param name="args">The HandleException argument</param>
protected override void HandleException(HandleExceptionArgs args)
{
// Check if the InnerException is null
if (args.Exception.InnerException != null)
{
// Convert the InnerException to DataServiceException
DataServiceException ex = args.Exception.InnerException as
DataServiceException;
// Check if the InnerException is in type of
// DataServiceException and the StatusCode is
// 400(Bad Request)
if (ex != null && ex.StatusCode == 400)
{
// Return the DataServiceException to the client
args.Exception = ex;
}
}
base.HandleException(args);
}
8) Set rules to indicate which entity sets and service operations are visible, updatable, etc.
public static void InitializeService(IDataServiceConfiguration config)
{
// Set rules to indicate which entity sets and service operations
// are visible, updatable, etc.
config.UseVerboseErrors = true;
config.SetEntitySetAccessRule("*", EntitySetRights.All);
config.SetServiceOperationAccessRule("CoursesByPersonID",
ServiceOperationRights.All);
config.SetServiceOperationAccessRule("GetPersonUpdateException",
ServiceOperationRights.All);
}
C. Creating an ADO.NET Data Service for LINQ to SQL Classes.
1. Creating a LINQ to SQL Class as the data source in Visual Studio 2008.
1) In LinqToSQL folder, add a LINQ to SQL Class named SchoolLinqToSQL.dbml.
2) Drag the tables Course, CourseGrade, CourseInstructor, and Person from the Server Explorer into the O/R Designer. For detail, please see Walkthrough: Creating LINQ to SQL Classes (O/R Designer).
(The connection in Server Explorer is connecting to the SQLServer2005DB database deployed by the SQLServer2005DB database project in All-In-One Code Framework)
2. Implementing the IUpdatable interface to enable the insert, update, and delete functionalities of the LINQ to SQL DataContext class.
1) Download the ADO.NET Data Services IUpdatable implementation for LINQ to SQL (It has both Visual C# and VB.NET versions). This implementation realizes most of IUpdatable methods targeting LINQ to SQL via .NET Reflection and LINQ to SQL related APIs, and also leaves some methods for the customization. For detailed information, please see
https://blogs.msdn.com/aconrad/archive/2008/11/05/iupdateable-for-linq-to-sql.aspx
https://blogs.msdn.com/aconrad/archive/2009/03/17/updated-linq-to-sql-iupdatable-implementation.aspx.
2) Add the downloaded Visual C# version IUpdatableLinqToSQL.cs to LinqToSQL folder, and modify the DataContext class name to SchoolLinqToSQLDataContext.
public partial class SchoolLinqToSQLDataContext : IUpdatable
3. Set the data service keys for the LINQ to SQL data classes using DataServiceKey attribute.
// Set data service key of the Course class to 'CourseID'
[DataServiceKey("CourseID")]
public partial class Course
{ }
// Set data service key of the CourseGrade class to 'EnrollmentID'
[DataServiceKey("EnrollmentID")]
public partial class CourseGrade
{ }
// Set data service key of the CourseInstructor class to 'CourseID' and 'PersonID'
[DataServiceKey("CourseID", "PersonID")]
public partial class CourseInstructor
{ }
// Set data service key of the Person class to 'PersonID'
[DataServiceKey("PersonID")]
public partial class Person
{ }
4. Creating and configuring an ADO.NET Data Service for the LINQ to SQL Classes.
1) Add a new ADO.NET Data Service named SchoolLinqToSQL.svc.
2) Set the SchoolLinqToSQL Data Service class targeting to the SchoolLinqToSQL.
public class SchoolLinqToSQL : DataService<SchoolLinqToSQLDataContext>
3) Create a service operation SearchCourses to search the Course objects via T-SQL commands and retrieve an IQueryable collection of certain Course objects.
/// <summary>
/// A service operation that searches the courses via SQL commands
/// and returns an IQueryable collection of Course objects
/// </summary>
/// <param name="searchText">The query SQL commands</param>
/// <returns>An IQueryable collection contains Course objects
/// </returns>
[WebGet]
public IQueryable<Course> SearchCourses(string searchText)
{
// Call DataContext.ExecuteQuery to call the search SQL commands
return this.CurrentDataSource.ExecuteQuery<Course>(searchText).
AsQueryable();
}
4) Set rules to indicate which entity sets and service operations are visible, updatable, etc.
public static void InitializeService(IDataServiceConfiguration config)
{
// Set rules to indicate which entity sets and service operations
// are visible, updatable, etc.
config.UseVerboseErrors = true;
config.SetEntitySetAccessRule("*", EntitySetRights.All);
config.SetServiceOperationAccessRule("SearchCourses",
ServiceOperationRights.All);
}
D. Creating an ADO.NET Data Service for non-relation CLR objects.
1. Add a new ADO.NET Data Service named AIO.svc.
2. Creating the in-memory data classes.
1) Create class Category with property CategoryName to represent the All-in-One Code Framework project category, and set the data service key value as CategoryName.
// All-in-One Code Framework project category entity class with DataServiceKey 'CategoryName'
[DataServiceKey("CategoryName")]
public class Category
{
public string CategoryName { get; set; }
}
2) Create class Project with properties ProjectName, Owner, and ProjectCategory to represent the project, and set the data service key value as ProjectName.
// All-in-One Code Framework project entity class with DataServiceKey 'ProjectName'
[DataServiceKey("ProjectName")]
public class Project
{
public string ProjectName { get; set; }
public string Owner { get; set; }
public Category ProjectCategory { get; set; }
}
3. Creating the ADO.NET Data Service entity class for non-relational CLR objects.
1) Create a class named AIOProjects and set the AIO.svc Data Service targeting to the AIOProjects class.
public class AIO : DataService<AIOprojects>
2) Declare two static members for AIOProjects class, categories in type of List<Category> and projects in type of List<Project>, and a static constructor for AIOProjects class to initialize the two static members.
static List<Category> categories;
static List<Project> projects;
// Static constructor
static AIOProjects()
{
// Initialize the AIO project category list
categories = new List<Category>()
{
new Category { CategoryName = "COM"},
......
};
// Initialize the All-in-One Code Framework project list
projects = new List<Project>()
{
new Project { ProjectName = "CSDllCOMServer",
Owner = "Jialiang Ge", ProjectCategory = categories[0] },
......
};
}
3) Declare two public readonly properties named Categories and Projects to return IQueryable representation of the two static members categories and projects, so that the ADO.NET Data Service client side can retrieve the non-relation data via these two IQuerable properties.
// Public property to get the All-in-One Code Framework projects information from the
// ADO.NET Data Service client side
public IQueryable<Project> Projects
{
get { return projects.AsQueryable(); }
}
// Public property to get the All-in-One Code Framework projects categoryies information
// from the ADO.NET Data Service client side
public IQueryable<Category> Categories
{
get { return categories.AsQueryable(); }
}
4. Implementing the IUpdatable methods to enable the insert functionality of the ADO.NET Data Service entity class AIOProjects.
1) Declare a temp object to hold the temporarily added objects (Category or Project).
// Save the added object temporarily
object tempObj = null;
2) Realize the method GreateResource to create the resource of the given type and belonging to the given container via Activator.CreateInstance method.
/// <summary>
/// Creates the resource of the given type and belonging to the
/// given container
/// </summary>
/// <param name="containerName">container name to which the resource
/// needs to be added</param>
/// <param name="fullTypeName">full type name i.e. Namespace
/// qualified type name of the resource</param>
/// <returns>object representing a resource of given type and
/// belonging to the given container</returns>
public object CreateResource(string containerName, string
fullTypeName)
{
// Get the type of the resource
Type t = Type.GetType(fullTypeName, true);
// Create an instance of the resource type
object resource = Activator.CreateInstance(t);
// Return the resource object
return resource;
}
3) Realize the SetValue method to set the value of the given property on the target object via .NET Reflection.
/// <summary>
/// Sets the value of the given property on the target object
/// </summary>
/// <param name="targetResource">target object which defines the
/// property</param>
/// <param name="propertyName">name of the property whose value needs
/// to be updated</param>
/// <param name="propertyValue">value of the property</param>
public void SetValue(object targetResource, string propertyName,
object propertyValue)
{
// Get the resource object type
Type t = targetResource.GetType();
// Get the property to be updated
PropertyInfo pi = t.GetProperty(propertyName);
if (pi != null)
{
// Set the property value
pi.SetValue(targetResource, propertyValue, null);
}
// Save the target object to temp added object
tempObj = targetResource;
}
4) Realize the ResolveResource method to return the actual instance of the resource represented by the given resource object.
/// <summary>
/// Returns the actual instance of the resource represented by the
/// given resource object
/// </summary>
/// <param name="resource">object representing the resource whose
/// instance needs to be fetched</param>
/// <returns>The actual instance of the resource represented by the
/// given resource object</returns>
public object ResolveResource(object resource)
{
return resource;
}
5) Realize the SaveChanges method to save all the pending changes made till now. This method first checks the temporarily added object’s type (Category or Project) and then adds it into the certain collection (categories or projects).
/// <summary>
/// Saves all the pending changes made till now
/// </summary>
public void SaveChanges()
{
// Add the temp object into the local collection
if (tempObj != null)
{
Type t = tempObj.GetType();
if (t.Name == "Category")
{
AIOProjects.categories.Add((Category)tempObj);
}
else if (t.Name == "Project")
{
AIOProjects.projects.Add((Project)tempObj);
}
}
}
6) Other IUpdatable methods can throw the NotImplementedException if we only want the insert functionality currently. These methods include AddReferenceToCollection, ClearChanges, DeleteResouce, GetResource, GetValue, RemoveReferenceFromCollection, ResetResource, and SetReference. I have implemented some of them for your references in the source code of CSADONETDataService and VBADONETDataService samples.
7) Set rules to indicate which entity sets and service operations are visible, updatable, etc.
public static void InitializeService(IDataServiceConfiguration config)
{
// Set rules to indicate which entity sets and service operations
// are visible, updatable, etc.
config.UseVerboseErrors = true;
config.SetEntitySetAccessRule("*", EntitySetRights.All);
}
How to get the samples
The latest version of CSADONETDataService and VBADONETDataService are available in All-In-One Code Framework . You can find the samples in the releases later than All-In-One Code Framework 2009-8-26.
Feedback
If you have any questions or feedbacks about the CSADONETDataService and VBADONETDataService samples, please feel free to post it to the discussion board or directly sent it to us. Thank you very much!
In the next week, we will have another blog introducing the ADO.NET Data Services client applications CSADONETDataServiceClient and VBADONETDataServiceClient which call the ADO.NET Data Service we built in this article.