WCF Data Services Toolkit to talk to any database using GetAll, GetOne, Save, Remove methods

I did a primer on using WCF Data Services in my last post. One of the things I’m seeing about the posts regarding WCF Data Services using OData is that they used Entity Framework to do the DAL work. So a lot of underlying work gets hidden by using EF. Here is a post that allows you to connect to any database using the traditional ADO.NET way.

WCF Data Services Toolkit – This is the library that makes it happen. Using the ODataContext class, we can write simple methods to perform CRUD operations on your data. We’ll see how.

I will be using SQL Server 2008 database as my back-end, although you can use any database supported by ADO.NET. The schema (and the data) for the Director and Movie tables look like this.

image

Accordingly, my model looks like below. Just as an FYI, the DataServiceKey that resides in the System.Data.Services.Common namespace, denotes the key property / properties of an entity (required for all data contracts)

   1: using System.Data.Services.Common;
   2:  
   3: namespace MovieModel
   4: {
   5:     [DataServiceKey("DirectorId")]
   6:     public class Director
   7:     {
   8:         public int DirectorId { get; set; }
   9:         public string FirstName { get; set; }
  10:         public string LastName { get; set; }
  11:     }
  12:  
  13:     [DataServiceKey("MovieId")]
  14:     public class Movie
  15:     {
  16:         public int MovieId { get; set; }
  17:         public string Name { get; set; }
  18:         public int YearReleased { get; set; }
  19:         public int DirectorId { get; set; }
  20:         public Director Director { get; set; }
  21:     }
  22: }

The WCF Data Service (MovieDataService.svc) takes in a MovieContext object. As you see below, for this example, I’ve given all permissions to all my entities and service operations.

   1: [System.ServiceModel.ServiceBehavior(IncludeExceptionDetailInFaults = true)]
   2: public class MovieDataService : DataService<MovieContext>
   3: {
   4:     // This method is called only once to initialize service-wide policies.
   5:     public static void InitializeService(DataServiceConfiguration config)
   6:     {
   7:         // Examples:
   8:         // config.SetEntitySetAccessRule("MyEntityset", EntitySetRights.AllRead);
   9:         // config.SetServiceOperationAccessRule("MyServiceOperation", ServiceOperationRights.All);
  10:  
  11:         // AllWrite does not allow read; for that set it to All
  12:         config.SetEntitySetAccessRule("*", EntitySetRights.All);
  13:         config.SetServiceOperationAccessRule("*", ServiceOperationRights.All);
  14:         config.DataServiceBehavior.MaxProtocolVersion = DataServiceProtocolVersion.V2;
  15:         config.UseVerboseErrors = true;
  16:     }
  17:  
  18:     protected override void HandleException(HandleExceptionArgs args)
  19:     {
  20:         try
  21:         {
  22:             args.UseVerboseErrors = true;
  23:         }
  24:         catch (Exception ex)
  25:         {
  26:             Console.WriteLine(ex.Message);
  27:         }
  28:     }
  29: }

You can also override the HandleException method to perform custom logic in there. The MovieContext class inherits from the ODataContext. This class gives quite a bit of information in its implementation.

   1: public class MovieContext : ODataContext
   2: {
   3:     public IQueryable<Movie> Movies 
   4:     {
   5:         get { return CreateQuery<Movie>(); }
   6:     }
   7:  
   8:     public IQueryable<Director> Directors
   9:     {
  10:         get { return CreateQuery<Director>(); }
  11:     }
  12:  
  13:     public override object RepositoryFor(string fullTypeName)
  14:     {
  15:         if (fullTypeName == typeof(Movie).FullName)
  16:         {
  17:             return new MovieRepository();
  18:         }
  19:         return fullTypeName == typeof(Director).FullName ? new DirectorRepository() : null;
  20:     }
  21: }

The CreateQuery returns an instance of DataServiceQuery<T> which is an IQueryable<T>. The RepositoryFor method takes in a type name and returns the repository object to handle that type. For our case we have two repositories – DirectorRepository and MovieRepository.

To build the repositories, we need to implement the (not so) famous four methods – GetAll(), GetOne(), Save() and Remove(). Let’s just take them one at a time!

GetAll() – This is the method that gets called when you do something like – http://mysite.com/MyDataService.svc/Directors/. The return type of this method should be an IQueryable<Director> type. So my DirectorRepository has the below code to return a list of all the directors.

   1: using System;
   2: using System.Collections.Generic;
   3: using System.Data;
   4: using System.Data.SqlClient;
   5: using System.Linq;
   6: using Microsoft.Data.Services.Toolkit.QueryModel;
   7: using MovieModel;
   8:  
   9: namespace UserInterface.Repositories
  10: {
  11:     // the BaseRepository only sets the ConnectionString property;
  12:     // the same gets used in the MovieRepository class as well;
  13:     public class DirectorRepository : BaseRepository
  14:     {
  15:         // The ODataQueryOperation param maps the query string elements like 
  16:         // $skip, $take, $select
  17:         // so you read them in your code-behind and mimic the action
  18:         public IQueryable<Director> GetAll(ODataQueryOperation oDataQueryOperation)
  19:         {
  20:             List<Director> directors = new List<Director>();
  21:             Director director;
  22:  
  23:             using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
  24:             {
  25:                 sqlConnection.Open();
  26:  
  27:                 SqlCommand sqlCommand = new SqlCommand("SelectAllDirectors", sqlConnection)
  28:                 {
  29:                     CommandType = CommandType.StoredProcedure
  30:                 };
  31:  
  32:                 SqlDataReader reader = sqlCommand.ExecuteReader();
  33:                 while (reader.Read())
  34:                 {
  35:                     director = new Director
  36:                                    {
  37:                                        DirectorId = int.Parse(reader["DirectorId"].ToString()),
  38:                                        FirstName = reader["FirstName"].ToString(),
  39:                                        LastName = reader["LastName"].ToString()
  40:                                    };
  41:  
  42:                     directors.Add(director);
  43:                 }
  44:             }
  45:             if (oDataQueryOperation != null && oDataQueryOperation.SkipCount > 0 && oDataQueryOperation.TopCount > 0)
  46:             {
  47:                 directors.Skip(oDataQueryOperation.SkipCount).Take(oDataQueryOperation.TopCount);
  48:             }
  49:  
  50:             if (directors.Count == 0)
  51:             {
  52:                 director = new Director();
  53:                 directors.Add(director);
  54:             }
  55:  
  56:             return directors.AsQueryable();
  57:         }
  58:     }
  59: }

I won’t go in to the details of the ADO.NET itself, but there are a couple of things to note here. We pass the type ODataQueryOperation (from the WCF Data Services Toolkit) to the GetAll method so we can capture any of the query string (like) parameters that were typed in the browser’s address bar. In the snippet above, (lines 45-48) we do some paging. All projections, sorting and filtering that were typed in the address bar also get mapped into this instance to be used in the code-behind.

The second thing is about the BaseRepository class. This is not some magic class or anything like that. All that this class does is to read the connection string from the config file and store it in the ConnectionString property to be used by both the repositories (avoiding code duplication).

GetOne() – So now what happens if the user typed: http://mysite.com/MyDataService.svc/Directors(1)/ – where 1 is the primary key of the Director entity? This is when the GetOne() method gets called. It takes in a string parameter to represent the primary key, even though in our case the primary key is an integer.

   1: public Director GetOne(string directorId)
   2: {
   3:     int intDirectorId;
   4:     int.TryParse(directorId, out intDirectorId);
   5:  
   6:     Director director = new Director { DirectorId = intDirectorId};
   7:     using (SqlConnection conn = new SqlConnection(ConnectionString))
   8:     {
   9:         conn.Open();
  10:  
  11:         SqlCommand sqlCommand = new SqlCommand("SelectDirectorByDirectorId", conn)
  12:         {
  13:             CommandType = CommandType.StoredProcedure
  14:         };
  15:  
  16:         // input parameter
  17:         SqlParameter directorIdParameter = sqlCommand.Parameters.Add("@directorId", SqlDbType.Int);
  18:         directorIdParameter.Value = intDirectorId;
  19:  
  20:         SqlDataReader reader = sqlCommand.ExecuteReader();
  21:         while (reader.Read())
  22:         {
  23:             director.DirectorId = int.Parse(reader["DirectorId"].ToString());
  24:             director.FirstName = reader["FirstName"].ToString();
  25:             director.LastName = reader["LastName"].ToString();
  26:  
  27:             break;
  28:         }
  29:     }
  30:  
  31:     return director;
  32: }

Save() – called for inserts and updates. I have taken care of whether it is an insert or an update inside the code by setting the key property to –1.

Remove() – called for deletes

   1: public void Save(Director director)
   2: {
   3:     using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
   4:     {
   5:         sqlConnection.Open();
   6:  
   7:         SqlCommand sqlCommand = new SqlCommand("InsertUpdateDirector", sqlConnection)
   8:         {
   9:             CommandType = CommandType.StoredProcedure
  10:         };
  11:  
  12:         SqlParameter directorIdParam = sqlCommand.Parameters.Add("@directorId", SqlDbType.Int);
  13:         directorIdParam.Value = director.DirectorId;
  14:  
  15:         SqlParameter lastNameParam = sqlCommand.Parameters.Add("@LastName", SqlDbType.VarChar, 50);
  16:         lastNameParam.Value = director.LastName;
  17:  
  18:         SqlParameter firstNameParam = sqlCommand.Parameters.Add("@FirstName", SqlDbType.VarChar, 50);
  19:         firstNameParam.Value = director.FirstName;
  20:  
  21:         // map the id of the director in case of inserts
  22:         // for updates, this is a little redundant as the id remains unchanged
  23:         director.DirectorId = (int)sqlCommand.ExecuteScalar();
  24:     }
  25: }
  26:  
  27: public void Remove(Director director)
  28: {
  29:     using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
  30:     {
  31:         sqlConnection.Open();
  32:  
  33:         SqlCommand sqlCommand = new SqlCommand("DeleteDirector", sqlConnection)
  34:         {
  35:             CommandType = CommandType.StoredProcedure
  36:         };
  37:  
  38:         SqlParameter directorIdParam = sqlCommand.Parameters.Add("@directorId", SqlDbType.Int);
  39:         directorIdParam.Value = director.DirectorId;
  40:  
  41:         sqlCommand.ExecuteScalar();
  42:     }
  43: }

I have a similar setup for my MovieRepository as well.

   1: public class MovieRepository : BaseRepository
   2: {
   3:     public Movie GetOne(string movieId)
   4:     {
   5:         int intMovieId;
   6:         int.TryParse(movieId, out intMovieId);
   7:  
   8:         Director director = new Director();
   9:         Movie movie = new Movie { Director = director };
  10:         using (SqlConnection conn = new SqlConnection(ConnectionString))
  11:         {
  12:             conn.Open();
  13:  
  14:             SqlCommand sqlCommand = new SqlCommand("SelectMovieDirectorByMovieId", conn)
  15:                                     {
  16:                                         CommandType = CommandType.StoredProcedure
  17:                                     };
  18:  
  19:             // input parameter
  20:             SqlParameter movieIdParameter = sqlCommand.Parameters.Add("@movieId", SqlDbType.Int);
  21:             movieIdParameter.Value = intMovieId;
  22:  
  23:             SqlDataReader reader = sqlCommand.ExecuteReader();
  24:             while (reader.Read())
  25:             {
  26:                 director.DirectorId = int.Parse(reader["DirectorId"].ToString());
  27:                 director.FirstName = reader["FirstName"].ToString();
  28:                 director.LastName = reader["LastName"].ToString();
  29:                 movie.DirectorId = int.Parse(reader["DirectorId"].ToString());
  30:                 movie.MovieId = intMovieId;
  31:                 movie.Name = reader["Name"].ToString();
  32:                 movie.YearReleased = int.Parse(reader["YearReleased"].ToString());
  33:                 break;
  34:             }
  35:         }
  36:  
  37:         return movie;
  38:     }
  39:  
  40:     public IQueryable<Movie> GetAll(ODataQueryOperation oDataQueryOperation)
  41:     {
  42:         List<Movie> movies = new List<Movie>();
  43:         Movie movie;
  44:  
  45:         using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
  46:         {
  47:             sqlConnection.Open();
  48:  
  49:             SqlCommand sqlCommand = new SqlCommand("SelectAllMovieDirector", sqlConnection)
  50:                                         {
  51:                                             CommandType = CommandType.StoredProcedure
  52:                                         };
  53:  
  54:             SqlDataReader reader = sqlCommand.ExecuteReader();
  55:             while (reader.Read())
  56:             {
  57:                 Director director = new Director
  58:                                         {
  59:                                             DirectorId = int.Parse(reader["DirectorId"].ToString()),
  60:                                             FirstName = reader["FirstName"].ToString(),
  61:                                             LastName = reader["LastName"].ToString(),
  62:                                         };
  63:                 movie = new Movie
  64:                             {
  65:                                 MovieId = int.Parse(reader["MovieId"].ToString()),
  66:                                 Name = reader["Name"].ToString(),
  67:                                 YearReleased = int.Parse(reader["YearReleased"].ToString()),
  68:                                 DirectorId = director.DirectorId,
  69:                                 Director = director,
  70:                             };
  71:                 movies.Add(movie);
  72:             }
  73:         }
  74:  
  75:         if (oDataQueryOperation != null && oDataQueryOperation.SkipCount > 0 && oDataQueryOperation.TopCount > 0)
  76:         {
  77:             movies.Skip(oDataQueryOperation.SkipCount).Take(oDataQueryOperation.TopCount);
  78:         }
  79:  
  80:         return movies.AsQueryable();
  81:     }
  82:  
  83:     public void Save(Movie movie)
  84:     {
  85:         using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
  86:         {
  87:             sqlConnection.Open();
  88:  
  89:             SqlCommand sqlCommand = new SqlCommand("InsertUpdateMovie", sqlConnection)
  90:             {
  91:                 CommandType = CommandType.StoredProcedure
  92:             };
  93:                 
  94:             SqlParameter movieIdParam = sqlCommand.Parameters.Add("@movieId", SqlDbType.Int);
  95:             movieIdParam.Value = movie.MovieId;
  96:  
  97:             SqlParameter directorIdParam = sqlCommand.Parameters.Add("@directorId", SqlDbType.Int);
  98:             directorIdParam.Value = movie.DirectorId;
  99:  
 100:             SqlParameter nameParam = sqlCommand.Parameters.Add("@Name", SqlDbType.VarChar, 100);
 101:             nameParam.Value = movie.Name;
 102:  
 103:             SqlParameter yearReleasedParam = sqlCommand.Parameters.Add("@YearReleased", SqlDbType.Int);
 104:             yearReleasedParam.Value = movie.YearReleased;
 105:  
 106:             movie.MovieId = (int)sqlCommand.ExecuteScalar();
 107:         }
 108:     }
 109:  
 110:     public void Remove(Movie movie)
 111:     {
 112:         using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
 113:         {
 114:             sqlConnection.Open();
 115:  
 116:             SqlCommand sqlCommand = new SqlCommand("DeleteMovie", sqlConnection)
 117:             {
 118:                 CommandType = CommandType.StoredProcedure
 119:             };
 120:  
 121:             SqlParameter movieIdParam = sqlCommand.Parameters.Add("@movieId", SqlDbType.Int);
 122:             movieIdParam.Value = movie.MovieId;
 123:  
 124:             sqlCommand.ExecuteScalar();
 125:         }
 126:     }
 127: }

And that completes your code for the creating the service to talk to any .net supported database.

Let’s go ahead and consume this service in a console application. I have a few helper methods to render the output on the console.

   1: private static void PrintAllDirectors(MovieContext movieContext)
   2: {
   3:     List<Director> directors = (from director in movieContext.Directors
   4:                                 select director).ToList();
   5:  
   6:     foreach (Director director in directors)
   7:     {
   8:         PrintDirector(director);
   9:     }
  10: }
  11:  
  12: private static void PrintAllMovies(MovieContext movieContext)
  13: {
  14:     List<Movie> movies = (from movie in movieContext.Movies.Expand("Director")
  15:                             select movie).ToList();
  16:  
  17:     foreach (Movie movie in movies)
  18:     {
  19:         PrintMovie(movie);
  20:     }
  21: }
  22:  
  23: private static void PrintDirector(Director director)
  24: {
  25:     Console.WriteLine("- Director details");
  26:     Console.WriteLine("--- Director Id: {0}", director.DirectorId);
  27:     Console.WriteLine("--- First Name: {0}", director.FirstName);
  28:     Console.WriteLine("--- Last Name: {0}", director.LastName);
  29:  
  30: }
  31:  
  32: private static void PrintMovie(Movie movie)
  33: {
  34:     Console.WriteLine("- Movie details");
  35:     Console.WriteLine("--- Movie Id: {0}", movie.MovieId);
  36:     Console.WriteLine("--- Name: {0}", movie.Name);
  37:     Console.WriteLine("--- Year Released: {0}", movie.YearReleased);
  38:     PrintDirector(movie.Director);
  39:     
  40: }

Here’s an outline of what happens in the Main() method:

  • Add a new director (with say, incorrect first name
  • Update the newly added director with correct name
  • Add a movie (with say, incorrect name and year)
  • Update the newly added movie
  • Delete the newly added movie
  • Delete the newly added director
  • and lastly give a big sigh that it all worked out without any hiccups!

I’m also calling the PrintAllDirectors() and PrintAllMovies() between these steps to show the changes in the database.

   1: static void Main()
   2: {
   3:     Uri serviceUri = new Uri("http://localhost:27115/MovieDataService.svc/");
   4:     MovieContext movieContext = new MovieContext(serviceUri);
   5:  
   6:     Console.WriteLine("All directors");
   7:     PrintAllDirectors(movieContext);
   8:  
   9:     Console.WriteLine("Add a new director");
  10:     #region Add New Director
  11:     Director newDirector = new Director
  12:                                 {
  13:                                     DirectorId = -1,
  14:                                     FirstName = "adsf",
  15:                                     LastName = "Bay",
  16:                                 };
  17:     movieContext.AddToDirectors(newDirector);
  18:     DataServiceResponse dsr = movieContext.SaveChanges();
  19:     #endregion
  20:  
  21:     PrintAllDirectors(movieContext);
  22:  
  23:     Console.WriteLine("Update newly added director");
  24:     #region Update Director
  25:     Director directorToBeUpdated = newDirector;
  26:     directorToBeUpdated.FirstName = "Michael";
  27:     movieContext.UpdateObject(directorToBeUpdated);
  28:     dsr = movieContext.SaveChanges();
  29:     PrintAllDirectors(movieContext);
  30:     #endregion
  31:  
  32:     Console.WriteLine("All movies");
  33:     PrintAllMovies(movieContext);
  34:  
  35:     Console.WriteLine("Add a new movie");
  36:     #region Add New Movie
  37:     Movie newMovie = new Movie
  38:                             {
  39:                                 MovieId = -1,
  40:                                 DirectorId = directorToBeUpdated.DirectorId,
  41:                                 Director = directorToBeUpdated,
  42:                                 Name = "Transformer",
  43:                                 YearReleased = 1999,
  44:                             };
  45:     movieContext.AddToMovies(newMovie);
  46:     movieContext.SaveChanges();
  47:     #endregion
  48:  
  49:     PrintAllMovies(movieContext);
  50:  
  51:     Console.WriteLine("Update newly added movie");
  52:     #region Update Movie
  53:     Movie movieToBeUpdated = newMovie;
  54:     movieToBeUpdated.Name = "Transformers";
  55:     movieToBeUpdated.YearReleased = 2007;
  56:     movieToBeUpdated.Director = directorToBeUpdated;
  57:     movieContext.UpdateObject(movieToBeUpdated);
  58:     dsr = movieContext.SaveChanges();
  59:     #endregion
  60:  
  61:     PrintAllMovies(movieContext);
  62:  
  63:     Console.WriteLine("Delete newly added movie");
  64:     #region Delete Movie
  65:     Movie movieToBeDeleted = movieToBeUpdated;
  66:     movieContext.DeleteObject(movieToBeDeleted);
  67:     dsr = movieContext.SaveChanges();
  68:     #endregion
  69:  
  70:     PrintAllMovies(movieContext);
  71:  
  72:     Console.WriteLine("Delete newly added director");
  73:     #region Delete Director
  74:     Director directorToBeDeleted = directorToBeUpdated;
  75:     movieContext.DeleteObject(directorToBeDeleted);
  76:     dsr = movieContext.SaveChanges();
  77:     #endregion
  78:  
  79:     PrintAllDirectors(movieContext);
  80:  
  81:     Console.ReadLine();
  82: }

I have the following output.

   1: All directors
   2: - Director details
   3: --- Director Id: 1
   4: --- First Name: Duncan
   5: --- Last Name: Jones
   6: - Director details
   7: --- Director Id: 2
   8: --- First Name: Quentin
   9: --- Last Name: Tarantino
  10: - Director details
  11: --- Director Id: 3
  12: --- First Name: Steven
  13: --- Last Name: Spielberg
  14: Add a new director
  15: - Director details
  16: --- Director Id: 1
  17: --- First Name: Duncan
  18: --- Last Name: Jones
  19: - Director details
  20: --- Director Id: 2
  21: --- First Name: Quentin
  22: --- Last Name: Tarantino
  23: - Director details
  24: --- Director Id: 3
  25: --- First Name: Steven
  26: --- Last Name: Spielberg
  27: - Director details
  28: --- Director Id: 37
  29: --- First Name: adsf
  30: --- Last Name: Bay
  31: Update newly added director
  32: - Director details
  33: --- Director Id: 1
  34: --- First Name: Duncan
  35: --- Last Name: Jones
  36: - Director details
  37: --- Director Id: 2
  38: --- First Name: Quentin
  39: --- Last Name: Tarantino
  40: - Director details
  41: --- Director Id: 3
  42: --- First Name: Steven
  43: --- Last Name: Spielberg
  44: - Director details
  45: --- Director Id: 37
  46: --- First Name: Michael
  47: --- Last Name: Bay
  48: - Movie details
  49: --- Movie Id: 1
  50: --- Name: A.I. Artificial Intelligence 
  51: --- Year Released: 2001
  52: - Director details
  53: --- Director Id: 3
  54: --- First Name: Steven
  55: --- Last Name: Spielberg
  56: - Movie details
  57: --- Movie Id: 2
  58: --- Name: Schindler's List
  59: --- Year Released: 1993
  60: - Director details
  61: --- Director Id: 3
  62: --- First Name: Steven
  63: --- Last Name: Spielberg
  64: - Movie details
  65: --- Movie Id: 3
  66: --- Name: Inglourious Basterds
  67: --- Year Released: 2009
  68: - Director details
  69: --- Director Id: 2
  70: --- First Name: Quentin
  71: --- Last Name: Tarantino
  72: - Movie details
  73: --- Movie Id: 4
  74: --- Name: Moon
  75: --- Year Released: 2009
  76: - Director details
  77: --- Director Id: 1
  78: --- First Name: Duncan
  79: --- Last Name: Jones
  80: Add a new movie
  81: - Movie details
  82: --- Movie Id: 1
  83: --- Name: A.I. Artificial Intelligence 
  84: --- Year Released: 2001
  85: - Director details
  86: --- Director Id: 3
  87: --- First Name: Steven
  88: --- Last Name: Spielberg
  89: - Movie details
  90: --- Movie Id: 2
  91: --- Name: Schindler's List
  92: --- Year Released: 1993
  93: - Director details
  94: --- Director Id: 3
  95: --- First Name: Steven
  96: --- Last Name: Spielberg
  97: - Movie details
  98: --- Movie Id: 3
  99: --- Name: Inglourious Basterds
 100: --- Year Released: 2009
 101: - Director details
 102: --- Director Id: 2
 103: --- First Name: Quentin
 104: --- Last Name: Tarantino
 105: - Movie details
 106: --- Movie Id: 4
 107: --- Name: Moon
 108: --- Year Released: 2009
 109: - Director details
 110: --- Director Id: 1
 111: --- First Name: Duncan
 112: --- Last Name: Jones
 113: - Movie details
 114: --- Movie Id: 13
 115: --- Name: Transformer
 116: --- Year Released: 1999
 117: - Director details
 118: --- Director Id: 37
 119: --- First Name: Michael
 120: --- Last Name: Bay
 121:  
 122: Update newly added movie
 123: - Movie details
 124: --- Movie Id: 1
 125: --- Name: A.I. Artificial Intelligence 
 126: --- Year Released: 2001
 127: - Director details
 128: --- Director Id: 3
 129: --- First Name: Steven
 130: --- Last Name: Spielberg
 131: - Movie details
 132: --- Movie Id: 2
 133: --- Name: Schindler's List
 134: --- Year Released: 1993
 135: - Director details
 136: --- Director Id: 3
 137: --- First Name: Steven
 138: --- Last Name: Spielberg
 139: - Movie details
 140: --- Movie Id: 3
 141: --- Name: Inglourious Basterds
 142: --- Year Released: 2009
 143: - Director details
 144: --- Director Id: 2
 145: --- First Name: Quentin
 146: --- Last Name: Tarantino
 147: - Movie details
 148: --- Movie Id: 4
 149: --- Name: Moon
 150: --- Year Released: 2009
 151: - Director details
 152: --- Director Id: 1
 153: --- First Name: Duncan
 154: --- Last Name: Jones
 155: - Movie details
 156: --- Movie Id: 13
 157: --- Name: Transformers
 158: --- Year Released: 2007
 159: - Director details
 160: --- Director Id: 37
 161: --- First Name: Michael
 162: --- Last Name: Bay
 163:  
 164: Delete newly added movie
 165: - Movie details
 166: --- Movie Id: 1
 167: --- Name: A.I. Artificial Intelligence 
 168: --- Year Released: 2001
 169: - Director details
 170: --- Director Id: 3
 171: --- First Name: Steven
 172: --- Last Name: Spielberg
 173: - Movie details
 174: --- Movie Id: 2
 175: --- Name: Schindler's List
 176: --- Year Released: 1993
 177: - Director details
 178: --- Director Id: 3
 179: --- First Name: Steven
 180: --- Last Name: Spielberg
 181: - Movie details
 182: --- Movie Id: 3
 183: --- Name: Inglourious Basterds
 184: --- Year Released: 2009
 185: - Director details
 186: --- Director Id: 2
 187: --- First Name: Quentin
 188: --- Last Name: Tarantino
 189: - Movie details
 190: --- Movie Id: 4
 191: --- Name: Moon
 192: --- Year Released: 2009
 193: - Director details
 194: --- Director Id: 1
 195: --- First Name: Duncan
 196: --- Last Name: Jones
 197:  
 198: Delete newly added director
 199: - Director details
 200: --- Director Id: 1
 201: --- First Name: Duncan
 202: --- Last Name: Jones
 203: - Director details
 204: --- Director Id: 2
 205: --- First Name: Quentin
 206: --- Last Name: Tarantino
 207: - Director details
 208: --- Director Id: 3
 209: --- First Name: Steven
 210: --- Last Name: Spielberg

And as promised… SIGH! (as it all went well).

The code, along with database scripts for table schemas and the stored procedures can be downloaded here.

In this post we saw how you can create a WCF Data Service to connect with any .net supported database with the help of the WCF Data Service Toolkit.

Update: fixed the Movie.GetOne() code as per Michael Herman's suggestion.

Published Monday, May 23, 2011 12:38 PM by nmarun
Filed under: , , , , , , , ,

Comments

# re: WCF Data Services Toolkit to talk to any database using GetAll, GetOne, Save, Remove methods

Monday, May 23, 2011 2:15 AM by Lohith

Kewl man....nice post.

# re: WCF Data Services Toolkit to talk to any database using GetAll, GetOne, Save, Remove methods

Friday, June 03, 2011 4:09 PM by Chuck

Hi.  I have implemented this solution on a table in my db.  When I call the service from the browser it works fine and returns rows but the data is rendered in HTML and I have to view source to see all the properties.  Is there a way for me to specify output or am I doing something wrong

Thanks,

Chuck

# re: WCF Data Services Toolkit to talk to any database using GetAll, GetOne, Save, Remove methods

Saturday, June 04, 2011 6:11 AM by nmarun

@@Chuck,

I have the same issue with Firefox v4.01. Do it in IE 8/9 or Chrome and you should see the results. And just so you know, it's not HTML that is getting returned. It's RSS format (basically an XML).

Arun

# WCF is Service kit

Wednesday, July 20, 2011 6:02 PM by WCF is Service kit

Pingback from  WCF is Service kit

# re: WCF Data Services Toolkit to talk to any database using GetAll, GetOne, Save, Remove methods

Wednesday, September 14, 2011 10:22 PM by David Thompson

Just what I was looking for to get me started. Thanks

# re: WCF Data Services Toolkit to talk to any database using GetAll, GetOne, Save, Remove methods

Thursday, September 15, 2011 12:54 AM by nmarun

Glad you liked it Dave.

Arun

# re: WCF Data Services Toolkit to talk to any database using GetAll, GetOne, Save, Remove methods

Sunday, September 18, 2011 4:50 PM by Michael Herman (Toronto)

Do you have any sample data to prepopulate the Movie and Director tables with?  mwherman at parallelspace dot net

# re: WCF Data Services Toolkit to talk to any database using GetAll, GetOne, Save, Remove methods

Sunday, September 18, 2011 5:44 PM by Michael Herman (Toronto)

Here's a bug in MovieRepository.GetOne().  The Movie.Director is not being set.  Here's the fix:

               SqlDataReader reader = sqlCommand.ExecuteReader();

               while (reader.Read())

               {

                   //director.DirectorId = int.Parse(reader["DirectorId"].ToString());

                   //director.FirstName = reader["FirstName"].ToString();

                   //director.LastName = reader["LastName"].ToString();

                   //movie.DirectorId = int.Parse(reader["DirectorId"].ToString());

                   //movie.MovieId = intMovieId;

                   //movie.Name = reader["Name"].ToString();

                   //movie.YearReleased = int.Parse(reader["YearReleased"].ToString());

                   director = new Director

                   {

                       DirectorId = int.Parse(reader["DirectorId"].ToString()),

                       FirstName = reader["FirstName"].ToString(),

                       LastName = reader["LastName"].ToString(),

                   };

                   movie = new Movie

                   {

                       MovieId = int.Parse(reader["MovieId"].ToString()),

                       Name = reader["Name"].ToString(),

                       YearReleased = int.Parse(reader["YearReleased"].ToString()),

                       DirectorId = int.Parse(reader["DirectorId"].ToString()),

                       Director = director,

                   };

                   break;

               }

# re: WCF Data Services Toolkit to talk to any database using GetAll, GetOne, Save, Remove methods

Sunday, September 18, 2011 5:48 PM by Michael Herman (Toronto)

Here's some Directors (copied from the employees table in the old MS pubs database):

insert [Director] values ('Philip', 'Cramer')

insert [Director] values ('Ann', 'Devon')

insert [Director] values ('Francisco', 'Chang')

insert [Director] values ('Laurence', 'Lebihan')

insert [Director] values ('Paul', 'Henriot')

insert [Director] values ('Sven', 'Ottlieb')

insert [Director] values ('Rita', 'Muller')

insert [Director] values ('Maria', 'Pontes')

insert [Director] values ('Janine', 'Labrune')

insert [Director] values ('Carlos', 'Hernadez')

insert [Director] values ('Victoria', 'Ashworth')

insert [Director] values ('Lesley', 'Brown')

insert [Director] values ('Anabela', 'Domingues')

insert [Director] values ('Martine', 'Rance')

insert [Director] values ('Peter', 'Franken')

insert [Director] values ('Daniel', 'Tonini')

insert [Director] values ('Helen', 'Bennett')

insert [Director] values ('Paolo', 'Accorti')

insert [Director] values ('Elizabeth', 'Lincoln')

GO

insert [Director] values ('Matti', 'Karttunen')

insert [Director] values ('Palle', 'Ibsen')

insert [Director] values ('Mary', 'Saveley')

insert [Director] values ('Gary', 'Thomas')

insert [Director] values ('Martin', 'Sommer')

insert [Director] values ('Roland', 'Mendel')

insert [Director] values ('Howard', 'Snyder')

insert [Director] values ('Timothy', 'O''Rourke')

insert [Director] values ('Karin', 'Josephs')

insert [Director] values ('Diego', 'Roel')

insert [Director] values ('Maria', 'Larsson')

insert [Director] values ('Paula', 'Parente')

insert [Director] values ('Margaret', 'Smith')

insert [Director] values ('Aria', 'Cruz')

insert [Director] values ('Miguel', 'Paolino')

insert [Director] values ('Yoshi', 'Latimer')

insert [Director] values ('Carine', 'Schmitt')

insert [Director] values ('Pedro', 'Afonso')

insert [Director] values ('Annette', 'Roulet')

insert [Director] values ('Helvetius', 'Nagy')

insert [Director] values ('Manuel', 'Pereira')

insert [Director] values ('Karla', 'Jablonski')

insert [Director] values ('Pirkko', 'Koskitalo')

insert [Director] values ('Patricia', 'McKenna')

GO

# re: WCF Data Services Toolkit to talk to any database using GetAll, GetOne, Save, Remove methods

Sunday, September 18, 2011 5:49 PM by Michael Herman (Toronto)

..and some movies (from the titles table in the old MS pubs database):

insert [Movie] values('The Busy Executives Database Guide', 2001, 1)

insert [Movie] values('Cooking with Computers: Surreptitious Balance Sheets', 2001, 2)

insert [Movie] values('You Can Combat Computer Stress!', 2003, 2)

insert [Movie] values('Straight Talk About Computers' ,2004, 3)

insert [Movie] values('Silicon Valley Gastronomic Treats', 2005, 3)

insert [Movie] values('The Gourmet Microwave', 2006, 3)

insert [Movie] values('The Psychology of Computer Cooking', 2001, 4)

insert [Movie] values('But Is It User Friendly?', 2003, 4)

insert [Movie] values('Secrets of Silicon Valley', 2004, 4)

insert [Movie] values('Net Etiquette', 2005, 4)

insert [Movie] values('Computer Phobic AND Non-Phobic Individuals: Behavior Variations', 2006, 4)

insert [Movie] values('Is Anger the Enemy?', 2002, 5)

insert [Movie] values('Life Without Fear', 2004, 5)

insert [Movie] values('Prolonged Data Deprivation: Four Case Studies', 2006, 5)

insert [Movie] values('Emotional Security: A New Algorithm', 2008, 5)

insert [Movie] values('Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean', 2010, 5)

insert [Movie] values('Fifty Years in Buckingham Palace Kitchens', 2001, 6)

insert [Movie] values('Sushi, Anyone?', 2002, 6)

go

# re: WCF Data Services Toolkit to talk to any database using GetAll, GetOne, Save, Remove methods

Monday, September 19, 2011 1:17 AM by nmarun

Hi Mike,

Thanks for the fix and for the movie / director listings.

Arun

# OData | Baski&#039;s Blog

Thursday, February 16, 2012 8:39 PM by OData | Baski's Blog

Pingback from  OData | Baski&#039;s Blog

Leave a Comment

(required) 
(required) 
(optional)
(required)