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.
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.