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 =

            "http://localhost/SchoolLinqToEntities.svc";

        private static string schoolLinqToSQLUri =

            "http://localhost/SchoolLinqToSQL.svc";

        private static string AIOUri =

      "http://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 =

             "http://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:

http://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:

http://localhost/SchoolLinqToSQL.svc/Persons(PersonID)/CourseGrades.

By CourseGrade entity, we can get the Course object via it CourseID.  The URL can be:

http://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:

http://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:

http://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!