How to make an ADO.NET Data Service client (console application) to CRUD ADO.NET Entity Data Model, LINQ to SQL Classes, and non-relational data
My last blog article introduced a step-by-step guide to make ADO.NET Data Services for ADO.NET Entity Data Model, LINQ to SQL Classes, and non-relation data. Today you will see how to make an ADO.NET Data Service client (console application) to CRUD (Create, Read, Update, and Delete) these different data sources in Visual C# (or VB.NET). Also, you will see how to use ADO.NET Data Service query options, parse DataServiceClientExceptions, and use service operations that we created in the last blog article.
Here we go.
A. Creating an ordinary Visual C# Console Application and adding the ADO.NET Data Services references.
1. In the same solution of the ADO.NET Data Services project, add a new Visual C# / Windows / Console Application project named CSADONETDataServiceClient.
2. Right click the References in the Solution Explorer, select Add Service References… .
3. In the Add Service Reference dialog, click Discover, add the three ADO.NET Data Services AIO.svc, SchoolLinqToEntities.svc, and SchoolLinqToSQL.svc, rename the Namespace of the services to AIOService, SchoolLinqToEntities, and SchoolLinqToSQLService respectively.
B. Creating static ADO.NET Data Services URL fields, local entity classes, and helper methods.
1. Define static fields for the ADO.NET Data Services URL. You need to modify the URL to specify the port settings of the localhost.
// The URLs need to be modified according to ADO.NET Data Services
// settings
private static string schoolLinqToEntitiesUri =
"https://localhost/SchoolLinqToEntities.svc";
private static string schoolLinqToSQLUri =
"https://localhost/SchoolLinqToSQL.svc";
private static string AIOUri =
"https://localhost/AIO.svc";
2. Create a local entity class named TempCourse which only have CourseID and Title properties and misses other properties than the Course entity class in the ADO.NET Data Service references.
// The local course entity class that misses some properties
// It only contains CourseID and Title properties.
public class TempCourse
{
public int CourseID { get; set; }
public string Title { get; set; }
}
3. Create a local entity class named InnerDataServiceException to hold the inner exception information of the DataServiceException.
// The local InnerDataServiceException to hold the DataServiceException
// data from the server side
public class InnerDataServiceException
{
public int Code { get; set; }
public string Message { get; set; }
}
4. Create a helper method ParseDataServiceClientException to parse the DataServiceClientException via LINQ to XML methods, and return a local entity class object InnerDataServiceException holding the inner exception information.
/// <summary>
/// Parse the DataServiceClientException to get the error code
/// and message
/// </summary>
/// <param name="exception">The DataServiceClientException message
/// </param>
/// <returns>The local InnerDataServiceException class</returns>
private static InnerDataServiceException
ParseDataServiceClientException(string exception)
{
try
{
// The DataServiceClientException XML namespace
XNamespace ns =
"https://schemas.microsoft.com/ado/2007/08/dataservices/metadata";
// Load the DataServiceClientException by XDocument
XDocument doc = XDocument.Parse(exception);
// Return the error code and message
return new InnerDataServiceException
{
Code = String.IsNullOrEmpty(
doc.Root.Element(ns + "code").Value) ? 400 :
int.Parse(doc.Root.Element(ns + "code").Value),
Message = doc.Root.Element(ns + "message").Value
};
}
catch (Exception ex)
{
Console.WriteLine(
"Exceptions when parsing the DataServiceClientException: " + ex.Message);
return null;
}
}
5. Create a helper method UpdateData to save the pending modifications of the DataServiceContext. If all the DataServiceResponse.StatusCode start with 2, since the status code 2** means success. For detail, please see List of HTTP status codes. If there are any exception occurs and the InnerException is in type of DataServiceClientException, use the helper method ParseDataServiceClientException to parse the exception information.
/// <summary>
/// Update the changes to the data at the server side
/// </summary>
/// <param name="context">The DataServiceContext to be updated.
/// </param>
/// <returns>Whether the update is successful.</returns>
private static bool UpdateData(DataServiceContext context)
{
try
{
// Get the response from the DataServiceContext update
// operation
DataServiceResponse response = context.SaveChanges();
bool isSucess = false;
foreach (var r in response)
{
// If response status code shows the update fails
// return false
if (!r.StatusCode.ToString().StartsWith("2"))
return false;
else
isSucess = true;
}
// The update is successful
return isSucess;
}
catch (Exception ex)
{
// Retrieve the exception information if there is some
// DataServiceException is thrown at the server side
if (ex.InnerException is DataServiceClientException)
{
// Parse the DataServieClientException
InnerDataServiceException innerException =
ParseDataServiceClientException(ex.InnerException.
Message);
// Display the DataServiceClientException message
if (innerException != null)
Console.WriteLine("DataServiceException Message: " +
innerException.Message);
}
else
{
Console.WriteLine("The update operation throws the error: " + ex.Message);
}
return false;
}
}
C. Updating and querying database via calling ADO.NET Data Service for the ADO.NET Entity Data Model.
1. Initialize the DataService object for the ADO.NET Entity Data Model.
/////////////////////////////////////////////////////////////////
// Initialize the DataService object for ADO.NET Entity Data
// Model
//
SchoolLinqToEntitiesService.SQLServer2005DBEntities svc = new
SchoolLinqToEntitiesService.SQLServer2005DBEntities(new Uri(
schoolLinqToEntitiesUri));
2. Create an invalid entity class, add it into the DataService context, and then try to save the pending modification. Through the helper methods UpdateData and ParseDataServiceClientException, we will see the exception information from the server side: The valid value of PersonCategory is 1 (for students) or 2 (for instructors) .
// Create a wrong instructor
SchoolLinqToEntitiesService.Person wrongInstructor = new
SchoolLinqToEntitiesService.Person()
{
......
// Set invalid value for the PersonCategory
PersonCategory = 3
};
// Add the incorrect person object into the context
svc.AddToPerson(wrongInstructor);
// Update the changes to the database
if (UpdateData(svc))
Console.WriteLine("Successfully!");
else
{
Console.WriteLine("Failed!");
// Detach the incorrect Person object
svc.Detach(wrongInstructor);
}
3. Create some valid entity classes and some relationships among them, then save the pending modification. Call AddTo** to add entity class and call AddLink to add the relationships.
// Add the newly-created instructor into context
svc.AddToPerson(newInstructor);
// Add the newly-created courses into context
svc.AddToCourse(newCourse1);
svc.AddToCourse(newCourse2);
// Add relationships to the newly-created instructor and courses
svc.AddLink(newCourse1, "Person", newInstructor);
svc.AddLink(newCourse2, "Person", newInstructor);
4. Perform a LINQ query which contains where, orderby, Skip, and Take operators or methods. These four LINQ operators or methods have their corresponding supported ADO.NET Data Service query options (filter, orderby, skip, top). How to use the query options will be introduced in the following steps.
// Get the third and fourth newly-enrolled students
// LINQ operator and ADO.NET Data Service query option comparison
// 'where'(LINQ) <==> 'filter'(Query Option)
// 'orderby' (LINQ) <==> 'orderby' (Query Option)
// 'Skip' (LINQ) <=> 'skip' (Query Option)
// 'Take' (LINQ) <==> 'top' (Query Option)
var students = (from p in svc.Person
where p.PersonCategory == 1
orderby p.EnrollmentDate descending
select p).Skip(2).Take(2);
5. Call the service operation CourseByPerson to get the certain person’s courses based on the person table primary key PersonID. The URL for the service operation CourseByPersonID can be:
https://localhost/SchoolLinqToEntities.svc/CoursesByPersonID?ID=PersonID.
// Call the service operation CoursesByPersonID to get the
// certain person's courses based on primary key PersonID
Uri uri = new Uri(String.Format("/CoursesByPersonID?ID={0}",
i.PersonID), UriKind.Relative);
// Exceute the URL to the retrieve the course list
var courses = svc.Execute<SchoolLinqToEntitiesService.Course>(uri);
D. Updating and querying database via ADO.NET Data Service for LINQ to SQL Classes.
1. Initialize the DataService object for the LINQ to SQL Classes.
/////////////////////////////////////////////////////////////////
// Initialize the DataService object for LINQ to SQL Data Class
//
SchoolLinqToSQLService.SchoolLinqToSQLDataContext svc = new
SchoolLinqToSQLService.SchoolLinqToSQLDataContext(new Uri(
schoolLinqToSQLUri));
2. Create some valid entity classes and some relationship entity classes, then save the pending modification. Since LINQ to SQL does not implement the many-to-many relationships, we need to add the relationship entity as well instead of calling AddLink to create the relationships. First we add the entity classes, after saving the entity classes, then we add the relationship entity class and save the data.
// Add the newly-created instructor into context
svc.AddToPersons(newStudent);
// Add the newly-created courses into context
svc.AddToCourses(newCourse1);
svc.AddToCourses(newCourse2);
// Update the changes to the database
if (UpdateData(svc))
Console.WriteLine("Successfully!");
else
Console.WriteLine("Failed!");
// Add the newly-created CourseGrade objects into context
svc.AddToCourseGrades(newCourseGrade1);
svc.AddToCourseGrades(newCourseGrade2);
3. Query the many-to-many relationships through the relationship entity. First we get all CourseGrade entities of one Person based on the PersonID. The URL can be:
https://localhost/SchoolLinqToSQL.svc/Persons(PersonID)/CourseGrades.
By CourseGrade entity, we can get the Course object via it CourseID. The URL can be:
https://localhost/SchoolLinqToSQL.svc/Courses(CourseID).
// Get the students whose name is 'Lingzhi Sun'
var studentsWithCourses = from p in svc.Persons
where p.LastName == "Sun" &&
p.FirstName == "Lingzhi"
select p;
// Get all the courses that' Lingzhi Sun' learns
foreach (var s in studentsWithCourses)
{
// Create URL to get the certain person's CourseGrade list
// based on primary key PersonID
Uri uri = new Uri(String.Format("/Persons({0})/CourseGrades",
s.PersonID), UriKind.Relative);
// Exceute the URL to the retrieve the CourseGarde list
var courseGrades = svc.Execute<SchoolLinqToSQLService.
CourseGrade>(uri);
Console.WriteLine("The student {0}'s courses:", s.FirstName
+ " " + s.LastName);
foreach (var cg in courseGrades)
{
// Create URL to get the certain course list based on
// primary key CourseID
uri = new Uri(String.Format("/Courses({0})", cg.CourseID),
UriKind.Relative);
// Exceute the URL to the retrieve the course list
var course = svc.Execute<SchoolLinqToSQLService.Course>
(uri).First();
// Display the query results
Console.WriteLine("Course Title: {0}, Credits: {1}",
course.Title, course.Credits);
}
}
E. Querying the database via ADO.NET query options and custom service operations.
1. Initialize the DataService object. Here we use the ADO.NET Data Service for ADO.NET Entity Data Model for testing.
/////////////////////////////////////////////////////////////////
// Initialize the DataService object
//
DataServiceContext context = new DataServiceContext(new Uri(
schoolLinqToEntitiesUri));
2. Perform the data source using query options (filter, orderby, skip, top) directly. The query URL can be:
https://localhost/SchoolLinqToEntities.svc/Person?$filter=PersonCategory eq 1&$orderby=EnrollmentDate desc&$skip=2&$top=2.
// Get the third and fourth newly-enrolled students
// LINQ operator and ADO.NET Data Service query option comparison
// 'where'(LINQ) <==> 'filter'(Query Option)
// 'orderby' (LINQ) <==> 'orderby' (Query Option)
// 'Skip' (LINQ) <=> 'skip' (Query Option)
// 'Take' (LINQ) <==> 'top' (Query Option)
var students = context.Execute<SchoolLinqToEntitiesService.Person>
(new Uri("/Person?$filter=PersonCategory eq 1&$" +
"orderby=EnrollmentDate desc&$skip=2&$top=2",
UriKind.Relative));
3. Initialize another DataService object and set the IgnoreMissingProperties property as true, so that the returned type should be mapped to the client side type. Here we use the ADO.NET Data Service for LINQ to SQL Classes for testing.
// Initialize a new DataServiceContext
DataServiceContext contextSQL = new DataServiceContext(new Uri(
schoolLinqToSQLUri));
// Set tje IgnoreMissingProperties to ture to retrieve entities
// that miss properties
contextSQL.IgnoreMissingProperties = true;
4. Call the service operation SearchCourses and use local entity class LocalCourse to retrieve the returned course information. The query URL can be:
https://localhost/SchoolLinqToSQL.svc/SearchCourses?searchText=’SELECT * FROM [Course] AS [c] WHERE [c].[Credits] = 4’.
// SQL search command
string searchText = "SELECT * FROM [Course] AS [c] WHERE " +
"[c].[Credits] = 4";
// Query the SQL commands at the server side
var courses = contextSQL.Execute<TempCourse>(new Uri(
string.Format("/SearchCourses?searchText='{0}'", searchText),
UriKind.Relative));
F. Updating and querying non-relational data via ADO.NET Data Service for non-relational data.
1. Initialize the DataService object for non-relational data.
/////////////////////////////////////////////////////////////////
// Initialize the DataService object for non-relational data
//
DataServiceContext svc = new DataServiceContext(new Uri(
AIOUri));
2. Insert a new entity object into the non-relational data collection.
Category newCategory = new Category()
{
CategoryName = "Silverlight",
};
// Add the newly created Category object into Categories collection
svc.AddObject("Categories", newCategory);
// Update the changes to the non-relational data
if (UpdateData(svc))
Console.WriteLine("Successfully!");
else
Console.WriteLine("Failed!");
3. Query the non-relational data based on DataServiceKey value and object links and group the data.
// Get all the project objects to the local memory
var projects = svc.Execute<Project>(new Uri("/Projects",
UriKind.Relative)).ToList();
foreach (var p in projects)
{
// Create URL to get the project category information based
// on primary key ProjectName
Uri uri = new Uri(String.Format("/Projects('{0}')/" +
"ProjectCategory", p.ProjectName), UriKind.Relative);
// Set the ProjectCategory property
Category category = svc.Execute<Category>(uri).First();
p.ProjectCategory = category;
}
// Group the projects by project category
var projectsGroups = from p in projects
group p by p.ProjectCategory;
Console.WriteLine("Group the AIO projects by category name:");
// Display the query results
foreach (var g in projectsGroups)
{
Console.WriteLine("Projects in Category: {0}",
g.Key.CategoryName);
foreach (var p in g)
{
Console.WriteLine("{0} owned by {1}", p.ProjectName,
p.Owner);
}
Console.WriteLine();
}
How to get the samples
The latest version of CSADONETDataServiceClient and VBADONETDataServiceClient 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 CSADONETDataServiceClient and VBADONETDataServiceClient samples, please feel free to post it to the discussion board or directly sent it to us. Thank you very much!