May 2008 - Posts
Visual Studio SP1: EntityDataSource - Overview Screencast
I have just posted an overview screencast about EntityDataSource to channel9. The EntityDataSource is the new ASP.Net Data Source that ships as part of the ADO.Net Entity Framework in Service Pack 1 of Visual Studio 2008 and .Net Framework 3.5. In this video you will get a quick overview of the EntityDataSource and see some additional features you may want to explore later.
If you want a step by step walkthrough of using the EntityDataSource, here is where you can find:
You can watch the screencast on channel9. If you'd prefer to download the WMV and watch it offline, you may do so here.
Enjoy!
Visual Studio 2008 SP1: New Assemblies in .Net Framework 3.5 SP1
There are several new assemblies in Service Pack 1 of .Net Framework 3.5 and Visual Studio 2008, I though I should be familiar with:
ADO.Net Entity Framework
- System.Data.Entity.dll - Contains the implementation of the ADO.Net Entity Framework as we've known it from previous Beta versions.
- System.Data.Entity.Design.dll - The Design-Time support for the Entity Data Model Designer and the entities code generation.
- System.Web.Entity.dll - Contains a new Data Source provider for ASP.Net called EntityDataSource.
- System.Web.Entity.Design.dll - The Design-Time support for the EntityDataSource.
ADO.Net Data Services Framework (project "Astoria")
- System.Data.Services.dll (was called Microsoft.Data.Web.dll when it was part of the ASP.Net 3.5 Extensions) - Contains the implementation of ADO.Net Data Services.
- System.Data.Services.Design.dll (previously Microsoft.Data.Web.Design.dll) - Since there is no visual designer for ADO.Net Data Services, this assembly contains the implementation of the datasvcutil.exe tool (previously webdatagen.exe) that creates the client side entities of a data service.
- System.Data.Services.Client.dll (previously Microsoft.Data.WebClient.dll) - Client side support for consuming data services from a .Net application.
ASP.Net Dynamic Data
- System.Web.DynamicData.dll - Contains the implementation of the Dynamic Data Framework for ASP.Net with support for both LINQ to SQL objects and ADO.Net Entity Framework entities.
- System.Web.DynamicData.Design.dll - Contains the Design-Time support for the Dynamic Data Controls.
- System.ComponentModel.DataAnnotations.dll - Metadata and customization support for Dynamic Data Pages and Controls.
ASP.Net MVC Framework / Routing Engine
The ASP.Net MVC Framework is not part of Visual Studio 2008 SP1 Beta1.
- System.Web.Routing.dll - The Routing Engine that was originally part of it, was refactored to be an independent component that can be used regardless of the MVC Framework (For example - the Dynamic Data Framework uses it).
- System.Web.Abstractions.dll - Contains several wrapper classes such as HttpContextBase that are being used by the Routing Engine.
General ASP.Net Extensions Support
- System.Web.Extensions.dll - Contains several extensions to ASP.Net 3.5 that were already been introduced in the ASP.Net Extensions Preview, such as the new ScriptManager for ASP.Net Ajax.
- System.Web.Extensions.Design.dll - Contains the Design-Time support for the new ASP.Net Extensions.
Enjoy!
Visual Studio 2008 SP1: LINQ to SQL with SQL Server 2008 Date Time Types
One of the pillars of Visual Studio 2008 and .Net Framework 3.5 Service Pack 1 is the support for SQL Server 2008 in Visual Studio 2008 tools and the related data access technologies. I decided to check out how LINQ to SQL works with SQL Server 2008 Date and Time types in Visual Studio 2008 and .Net Framework 3.5 Beta.
I created a simple database with a single table called friends, that holds my friends names and birth dates in several formats according the existing and new types.
create table Friends
(
FriendID int identity(1,1) not null primary key,
[Name] nvarchar(50) not null,
BirthDate date,
BirthTime time,
BirthDateTime datetime,
BirthDateTime2 datetime2,
BirthSmallDateTime smalldatetime,
BirthDateTimeOffset datetimeoffset
)
You can download the SQL 2008 Friends Schema.
When I dragged the Friends table from the Server Explorer to the OR/M Designer, I got a visual representation of the Friend class, with types corresponding to the table columns. This is the columns types mapping:
| Column |
Server Data Type |
CLR Type |
| BirthDate |
Date |
System.DateTime |
| BirthTime |
Time |
System.TimeSpan |
| BirthDateTime |
DateTime |
System.DateTime |
| BirthDateTime2 |
DateTime2 |
System.DateTime |
| BirthSmallDateTime |
SmallDateTime |
System.DateTime |
| BirthDateTimeOffset |
DateTimeOffset |
System.DateTimeOffset |
Working with this class was just as usual as with any other type, both for querying and for updating. The result was a GridView full with details of this table:
LINQ to SQL support for Table Valued Patameters
When I tried to drag a stored procedure that takes a table valued parameter and inserts multiple rows from it in a single db access, the designer has created a corresponding method that takes a System.Object type. When I saved the model to work with this method, I got the following compilation error:
DBML1005: Mapping between DbType 'Structured' and Type 'System.Object' in Parameter 'FriendsTable' of Function 'dbo.FriendsInsertMany' is not supported.
I am not sure whether the LINQ to SQL will support Table Valued Patameters in the RTM of the service pack.
Enjoy!
Visual Studio 2008 SP1: LINQ to SQL and FILESTREAM
In Visual Studio 2008 and .Net Framework 3.5 SP1, not only that LINQ to SQL supports the new date and time types of SQL Server 2008, but is also supports forking with FILESTREAMs.
Using the File Management Schema from my SQL Server 2008 FILESTREAM post, I created a simple .net application that uses LINQ to SQL in order to access the file contents.
Just a reminder of how the Files table looks like:
CREATE TABLE [dbo].[Files]
(
FileID uniqueidentifier NOT NULL ROWGUIDCOL PRIMARY KEY,
FileContents varbinary(max) FILESTREAM DEFAULT(0x)
)
and the corresponding LINQ to SQL model and generated code look like:
[Table(Name="dbo.Files")]
public partial class File
{
private System.Guid _FileID;
private System.Data.Linq.Binary _FileContents;
[Column(Storage="_FileID",
DbType="UniqueIdentifier NOT NULL",
IsPrimaryKey=true)]
public System.Guid FileID
{
get { ... }
set { ... }
}
[Column(Storage="_FileContents",
DbType="VarBinary(MAX)",
UpdateCheck=UpdateCheck.Never)]
public System.Data.Linq.Binary FileContents
{
get { ... }
set { ... }
}
}
Notice that the FileContents field is defined as System.Data.Linq.Binary.
Reading a File Content
FileManagementDataContext db = new FileManagementDataContext();
var query = db.Files;
foreach (var file in query)
{
byte[] buffer = file.FileContents.ToArray();
System.IO.File.WriteAllBytes(file.FileID + ".txt", buffer);
}
In the above code block I create a new instance of the data context and query for all the files. Going over the files, I take out the file contents as a byte array from the System.Data.Linq.Binary field and write it to another file in the file system.
Adding a new File
byte[] inputBuffer = System.IO.File.ReadAllBytes("TextFile1.txt");
File newFile = new File();
newFile.FileID = Guid.NewGuid();
newFile.FileContents = new System.Data.Linq.Binary(inputBuffer);
db.Files.InsertOnSubmit(newFile);
db.SubmitChanges();
In the above code I read all the contents of a file and get it as a byte array. Then, I create a new File object, assign a new ID and create a new instance of System.Data.Linq.Binary and pass the contents. Then, I use standard LINQ to SQL methods to add the file into the table and submit the changes to the database.
Conclusion
LINQ to SQL adds the support of using SQL Server 2008 FILESTREAM in Visual Studio 2008 and .Net Framework 3.5 SP1. The FILESTREAM columns is represented as a System.Data.Linq.Binary field that we can access it and get the file contents as a byte array.
Enjoy!
Visual Studio 2008 SP1: Sync Services for ADO.Net with SQL Server 2008 Change Tracking
One of the main pillars of Visual Studio 2008 and .Net Framework 3.5 Service Pack 1 if the better integration with SQL Server 2008. Service Pack 1 now uses the Change Tracking capabilities of SQL Server 2008 in order to identify the incremental changes.
During the process of writing this post, I am building a simple application with both Visual Studio 2008 and Visual Studio 2008 + SP1 in order to spot the changes in Sync Services for ADO.Net in SP1.
The first thing you notice when you add a new Local Database Cache item, is the Use SQL Server change tracking checkbox. The wizard knows that the connection on the server side was a SQL 2008 connection, and then enabled this checkbox.
When this option is enabled, and you click on the add button in order to select the tables you would like to synchronize, the database schema doesn't need to be changed like it used to be in earlier versions besides enabling the change tracking at the level of the database and each table you want to synchronize.
You can notice in the screenshot above that the Script Generation section of this page is missing. This is because those script are not only at the level of each table, but also at the level of the database itself, where you'd want to enable the change tracking.
When you confirm the Configure Data Synchronization Wizard, you will get the following message:
This message replaces the missing script generation section at the table level and when you confirm it, it will generate the scripts for each table and for the database.
But the difference is, that instead of generating a long script that adds several columns and a tombstone table, it only enabled the change tracking at the database level and at the table level.
Summary
Sync Services for ADO.Net in Visual Studio 2008 SP1 now uses SQL Server 2008 Change Tracking and has the tools support for it. When you use a SQL Server 2008 connection, you don't have to change the database schema but to enable change tracking.
Enjoy!
Visual Studio 2008 SP1: SqlFileStream - Managed API for SQL Server 2008 FILESTREAM
In the previous post about SQL 2008 FILESTREAM I created a sample database and added a text file using sql commands. In this post I will build a simple .net client application that adds a file to the database and than reads it. To do that, I will go over a code block that I've written and explain it. You can download the full source code project for this post.
Some related posts about SQL Server 2008:
SQL Server 2008 FILESTREAM - Part 1
SqlFileStream - Managed API for SQL Server 2008 FILESTREAM
LINQ to SQL with SQL Server 2008 Date Time Types
LINQ to SQL and FILESTREAM
SQL Server 2008 IntelliSense
SQL Server 2008 T-SQL: DECLARE and SET in the Same Statement
SQL Server 2008 T-SQL: Insert Multiple Rows
SQL Server 2008 T-SQL: MERGE Statement
Insert a New File
1. Create and open a new connection to the database:
string connectionString = @"Data Source=.;Initial Catalog=FileManagement;Integrated Security=True";
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
2. Begin a new Transaction. Since writing the row to the table and saving the file to the file system should be a single atomic operation, all the following operations should be inside a transaction.
SqlTransaction tx = conn.BeginTransaction();
3. Add a new record to the Files table.
SqlCommand insertFileCommand = conn.CreateCommand();
insertFileCommand.Transaction = tx;
insertFileCommand.CommandText = "INSERT INTO Files (FileID) VALUES (@FileID)";
Guid newFileID = Guid.NewGuid();
insertFileCommand.Parameters.Add("@FileID", SqlDbType.UniqueIdentifier).Value = newFileID;
insertFileCommand.ExecuteNonQuery();
4. After the row has been added to the table, we can select the path name of the newly added file and later write to it. Another thing we have to get is the FileStream Transaction Context (we do that by using the function get_filestream_transaction_context()).
SqlCommand getPathAndTokenCommand = conn.CreateCommand();
getPathAndTokenCommand.Transaction = tx;
getPathAndTokenCommand.CommandText =
"SELECT FileContents.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() " +
"FROM Files " +
"WHERE FileID = @FileID";
getPathAndTokenCommand.Parameters.Add("@FileID", SqlDbType.UniqueIdentifier).Value = newFileID;
SqlDataReader tokenReader = getPathAndTokenCommand.ExecuteReader(CommandBehavior.SingleRow);
tokenReader.Read();
SqlString filePathName = tokenReader.GetSqlString(0);
SqlBinary fileToken = tokenReader.GetSqlBinary(1);
tokenReader.Close();
5. Copy the contents of a file in the file system to the sql file using SqlFileStream class which is new in Service Pack 1 of Visual Studio 2008 and .Net Framework 3.5. The SqlFileStream is found in the System.Data.SqlTypes namespace, and inherits from the Stream class which means that using it is very similar to any other stream.
When constructing a new instance of SqlFileStream, we pass the pass name and FileStream Transaction Context that we got after inserting the new row into the table.
FileStream inputFile = File.OpenRead("TextFile1.txt");
SqlFileStream sqlFile = new SqlFileStream(filePathName.Value, fileToken.Value, System.IO.FileAccess.Write);
byte[] buffer = new byte[512 * 1024]; // 512Kb
int bytesRead = inputFile.Read(buffer, 0, buffer.Length);
while (bytesRead > 0)
{
sqlFile.Write(buffer, 0, bytesRead);
bytesRead = inputFile.Read(buffer, 0, buffer.Length);
}
Writing into an SqlFile is very similar to the way we would use any other stream class, and we do that by using a buffer that we read into it and write from it.
6. Just close all open connections and resources.
sqlFile.Close();
inputFile.Close();
tx.Commit();
conn.Close();
Read File Contents
Reading file's contents is very similar to adding a new file, except from using the Read method instead of the Write method. Just to make things more clear - here's how you do it.
1. Create and open a new connection to the database:
string connectionString = @"Data Source=.;Initial Catalog=FileManagement;Integrated Security=True";
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
2. Begin a new Transaction.
SqlTransaction tx = conn.BeginTransaction();
3. Get the path name and token of the file.
SqlCommand getPathAndTokenCommand = conn.CreateCommand();
getPathAndTokenCommand.Transaction = tx;
getPathAndTokenCommand.CommandText =
"SELECT FileContents.PathName(), GET_FILESTREAM_TRANSACTION_CONTEXT() " +
"FROM Files " +
"WHERE FileID = @FileID";
getPathAndTokenCommand.Parameters.Add("@FileID", SqlDbType.UniqueIdentifier).Value = newFileID;
SqlDataReader tokenReader = getPathAndTokenCommand.ExecuteReader(CommandBehavior.SingleRow);
tokenReader.Read();
SqlString filePathName = tokenReader.GetSqlString(0);
SqlBinary fileToken = tokenReader.GetSqlBinary(1);
tokenReader.Close();
4. Copy the contents of the SqlFile to a new file in the file system.
FileStream outputFile = File.OpenWrite("TextFile2.txt");
SqlFileStream sqlFile = new SqlFileStream(filePathName.Value, fileToken.Value, System.IO.FileAccess.Read);
byte[] buffer = new byte[512 * 1024]; // 512Kb
int bytesRead = sqlFile.Read(buffer, 0, buffer.Length);
while (bytesRead > 0)
{
outputFile.Write(buffer, 0, bytesRead);
bytesRead = sqlFile.Read(buffer, 0, buffer.Length);
}
5. Cleanup.
sqlFile.Close();
outputFile.Close();
tx.Commit();
conn.Close();
Summary
Writing and Reading from a file stored in SQL Server 2008 make easy using the SqlFileStream class in Visual Studio 2008 and .Net Framework 3.5 SP1. After the file row exists in the database, we query for the path name and token, and then use the SqlFileStream just as we would use any other stream.
Enjoy!
Visual Studio 2008 SP1: A Simple ADO.Net Data Service Walkthrough
Back in December, when the last preview of ADO.Net Data Services was released I wrote several blog posts about the topic. Now that Beta 1 of Visual Studio 2008 and .Net Framework 3.5 Service Pack 1 is out, it is a good time to refresh those posts. Additionally, this post is a part of a series of posts Exploring Beta 1 of Visual Studio 2008 and .Net Framework 3.5.
This post is a step by step guide for building a Simple ADO.Net Data Service for the Blog database I have been using though my recent samples.
1. Create the Initial Web Project
Create a new standard ASP.Net Web Application. Notice that there is no special project template for a Web Data Service, and later we will see the new item template. In this guide, I called my web application BlogWebApp.
Visual Studio 2008 created an initial Default.aspx page as part of the web application - it can be removed since we are not exposing any presentation layer as part of this data service.
2. Create an Entity Data Model
Add a new item of ADO.Net Entity Data Model to the application. This is the new item template that is installed as part of Visual Studio 2008 and .Net Framework 3.5 Service Pack In this guide, I called my model Blog.edmx.
In the Entity Data Model Wizard, choose Generate From database option, select the database connection to use, and then select the database objects you want to import to the model. In this sample, I imported all the database objects.
Click Finish. The new model is now created and we can see that by default, all the entities are mapped 1:1 to the database table, besides the PostCategories table that does not appear in the model since it only represents a link table for implementing a n:m releation.
Click Save to save the model and let Visual Studio 2008 generate the entity classes.
3. Create the ADO.Net Data Service
Add a new ADO.Net Data Service item to the project. This is the new item template that is installed as part of Visual Studio 2008 and .Net Framework 3.5 Service Pack. in this guide I called this service BlogDataService.svc.
Creating this data service adds a new item to the project, and opens the service for editing. Additionally, this also adds a reference to System.Data.Services.dll which is a new assembly that ships as part of this service pack (in previous previews of ADO.Net Data Services this assembly was called Microsoft.Data.Web.dll).
public class BlogDataService : DataService< /* TODO: put your data source class name here */ >
{
// This method is called only once to initialize service-wide policies.
public static void InitializeService(IDataServiceConfiguration config)
{
...
}
}
Note: In previous previews of ADO.Net Data Services, the data service base class was called WebDataService<T> and the InitializeService method took an instance of IWebDataServiceConfiguration.
Edit the ADO.Net Data Service. First, replace the template comment in the class definition
/* TODO: put your data source class name here */
with the name of the data provider. In this guide, since we are using ADO.Net Entity Framework, we will use the BlogDataContext class as a provider. This class is one of the classes that Visual Studio has generated when we created the LINQ to SQL model.
public class BlogDataService : DataService< BlogModel.BlogEntities >
{
public static void InitializeService(IDataServiceConfiguration config)
{
...
}
}
Run the project, and enable debugging if the dialog appears. The ASP.Net Web Server starts and the Internet Explorer with it, navigating to the data service: http://localhost:1039/BlogDataService.svc/. Notice that the output returns no results, and the output looks like:
<service xml:base="http://localhost:1039/BlogDataService.svc/"
xmlns:atom="http://www.w3.org/2005/Atom"
xmlns:app="http://www.w3.org/2007/app"
xmlns="http://www.w3.org/2007/app">
<workspace>
<atom:title>Default</atom:title>
</workspace>
</service>
The reason is that as of the December CTP of ADO.Net Data Services, Access Control was integrated into the Data Services, not allowing anyone to view the metadata or the data itself of any resource, without explicitly allowing it. I will not dive into this access control features in this post.
Allow all the resources to be readable. Edit the Data Service, and in the InitializeService method, use the configuration parameter to do this.
public static void InitializeService(IDataServiceConfiguration config)
{
config.SetEntitySetAccessRule("*", EntitySetRights.AllRead);
}
Note: In previous versions the method that set the access rule for entity sets was called SetResourceContainerAccessRule and the Access Rules enumeration was called ResourceContainerRights.
This call gives all the entity sets the ability to be seen via the service.
4. Query the Data Service via Browser
If you now run the service (http://localhost:1039/BlogDataService.svc/) again, you can see the following output:
<service xml:base="http://localhost:1039/BlogDataService.svc/"
xmlns:atom="http://www.w3.org/2005/Atom"
xmlns:app="http://www.w3.org/2007/app"
xmlns="http://www.w3.org/2007/app">
<workspace>
<atom:title>Default</atom:title>
<collection href="Blogs">
<atom:title>Blogs</atom:title>
</collection>
<collection href="Categories">
<atom:title>Categories</atom:title>
</collection>
<collection href="Posts">
<atom:title>Posts</atom:title>
</collection>
</workspace>
</service>
If you cannot see the output in the above way, go to Tools->Internet Options-> Content Tab. Click the Settings button in the Feeds section, an uncheck the Turn on Feed Reading View option.
Notice that some elements contain the href attribute with an additional URL. We can use it to navigate through the data:
<feed xml:base="http://localhost:1039/BlogDataService.svc/"
xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices"
xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"
xmlns="http://www.w3.org/2005/Atom">
<title type="text">Blogs</title>
<id>http://localhost:1039/BlogDataService.svc/Blogs</id>
<updated>2008-05-07T10:26:34Z</updated>
<link rel="self" title="Blogs" href="Blogs" />
<entry m:type="BlogModel.Blogs">
<id>http://localhost:1039/BlogDataService.svc/Blogs('bursteg')</id>
<title type="text" />
<updated>2008-05-07T10:26:34Z</updated>
<author>
<name />
</author>
<link rel="edit" title="Blogs" href="Blogs('bursteg')" />
<link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Posts"
type="application/atom+xml;type=feed" title="Posts" href="Blogs('bursteg')/Posts" />
<content type="application/xml">
<m:properties>
<d:BlogID>bursteg</d:BlogID>
<d:BlogName>Guy Burstein Blog</d:BlogName>
<d:Owner>Guy Burstein</d:Owner>
</m:properties>
</content>
</entry>
</feed>
<entry xml:base="http://localhost:1039/BlogDataService.svc/"
xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices"
xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"
m:type="BlogModel.Blogs" xmlns="http://www.w3.org/2005/Atom">
<id>http://localhost:1039/BlogDataService.svc/Blogs('bursteg')</id>
<title type="text" />
<updated>2008-05-07T10:39:20Z</updated>
<author>
<name />
</author>
<link rel="edit" title="Blogs" href="Blogs('bursteg')" />
<link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Posts"
type="application/atom+xml;type=feed" title="Posts" href="Blogs('bursteg')/Posts" />
<content type="application/xml">
<m:properties>
<d:BlogID>bursteg</d:BlogID>
<d:BlogName>Guy Burstein Blog</d:BlogName>
<d:Owner>Guy Burstein</d:Owner>
</m:properties>
</content>
</entry>
<entry xml:base="http://localhost:1039/BlogDataService.svc/"
xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices"
xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"
m:type="BlogModel.Posts" xmlns="http://www.w3.org/2005/Atom">
<id>http://localhost:1039/BlogDataService.svc/Posts(3)</id>
<title type="text" />
<updated>2008-05-07T10:41:12Z</updated>
<author>
<name />
</author>
<link rel="edit" title="Posts" href="Posts(3)" />
<link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Blogs"
type="application/atom+xml;type=entry" title="Blogs" href="Posts(3)/Blogs" />
<link rel="http://schemas.microsoft.com/ado/2007/08/dataservices/related/Categories"
type="application/atom+xml;type=feed" title="Categories" href="Posts(3)/Categories" />
<content type="application/xml">
<m:properties>
<d:PostID m:type="Edm.Int32">3</d:PostID>
<d:Title>Post 3</d:Title>
<d:Body>This is the body of post 3</d:Body>
<d:PublishDate m:type="Edm.DateTime">2008-05-07T13:21:41.537</d:PublishDate>
</m:properties>
</content>
</entry>
And so on...
You can read more about URL syntax for December CTP of ADO.NET Data Services. Although there are several updates from the December CTP, you can get a very good idea of it.
Enjoy!
Visual Studio 2008 SP1: Querying an ADO.Net Data Service via .Net Client
This post is a part of a series of posts Exploring Beta 1 of Visual Studio 2008 and .Net Framework 3.5, and specifically, the ADO.Net Data Services framework that will ship as part of it.
In part1 I had a step by step guide for building a simple web data service, that exposed Entity Sets of the Entity Data Model I've created. Then, I used the browser to request data from the Data Service. Although it was very nice, it was not useful in real applications. In this post, I will talk about how we can query the Data Service from a .net client application. This post is a step by step guide for creating a client application that queries the Blog Data Service I build in the previous post.
1. Create a .Net Client Application
Create a new Console Application, and add a reference the the client side components for ADO.Net Data Services assembly (System.Data.Services.Client.dll, formerly called Microsoft.Data.WebClient.dll).
2. Generating Client Side Entities with datasvcutil.exe
Open the Blog Data Service from the previous post and browse to the service (http://localhost:1039/BlogDataService.svc), just to make sure we can access the service add pull its metadata. If you want to explore the data service metadata you can add /$metadata to the url (http://localhost:1039/BlogDataService.svc/$metadata)
Open a command windows and navigate to C:\WINDOWS\Microsoft.NET\Framework\v3.5 folder. In order to generate the client classes, we use the datasvcutil.exe tool with the following command:
datasvcutil.exe /uri:http://localhost:1039/BlogDataService.svc /out:BlogEntities.cs
This command queries the metadata of the Data Service from the input uri and generates the entities code in a file called BlogEntities.cs.
Microsoft (R) DataSvcUtil version 3.5.0.0
Copyright (C) 2008 Microsoft Corporation. All rights reserved.
Writing object layer file...
Generation Complete -- 0 errors, 0 warnings
Add the BlogEntities.cs code file to the client project. This adds the generated classes to the project and makes them available for use in the client application.
3. Querying using DataServiceContext and DataServiceQuery<T>
There are several ways in which we can query our data service:
- .
- Using the generated class that inherits from DataServiceContext.
- Using LINQ to ADO.Net Data Services.
I. The untyped approach using DataServiceContext
Create an instance of DataServiceContext, and pass the url of the data service as a parameter.
DataServiceContext svc = new DataServiceContext(new Uri("http://localhost:1039/BlogDataService.svc"));
Now, in order to query for the list of posts, you can create a query.
var query = ctx.CreateQuery<Post>("Posts");
foreach (Post p in query)
{
Console.WriteLine("{0} posted on {1}", p.Title, p.PublishDate);
Console.WriteLine(p.Body);
}
The DataServiceContext.CreateQuery<T> takes the relative path of the Entity Set we want to query, and returns an instance of DataServiceQuery<T> which is IQueryable. This means that only when we first enumerate the results, the query will be translated into a web request and be sent to the Data Service. If we hover the query object while debugging, we can see the URI of the request that will be sent to the service in order to perform the query.
II. Using the generated class that inherits from DataServiceContext
One of the classes that datasvcutil.exe has generated is BlogEntities, which is very similar to the approach taken by the LINQ to SQL DataContext and Entity Framework's ObjectContext.
public partial class BlogEntities : System.Data.Services.Client.
DataServiceContext
{
public BlogEntities(global::System.Uri serviceRoot) : base(serviceRoot) { }
public System.Data.Services.Client.DataServiceQuery<Blogs> Blogs
{
get { return base.CreateQuery<Blogs>("Blogs"); }
}
public System.Data.Services.Client.DataServiceQuery<Categories> Categories
{
get { return base.CreateQuery<Categories>("Categories"); }
}
public System.Data.Services.Client.DataServiceQuery<Posts> Posts
{
get { return base.CreateQuery<Posts>("Posts"); }
}
...
}
It is a partial class that inherits from the "untyped" DataServiceContext and exposes a strongly typed DataServiceQuery<T> objects for the data service entity sets as properties. The constructors takes the base URL of the service as a "connection string".
In order to use the typed Blogentities, we can write:
BlogEntities svc = new BlogEntities(new Uri("http://localhost:1039/BlogDataService.svc"));
var query = svc.Posts;
foreach (Posts p in query)
{
Console.WriteLine("{0} posted on {1}", p.Title, p.PublishDate);
Console.WriteLine(p.Body);
}
We can even take a more sophisticated query. Since the DataServiceQuery<T> is an IQueryable, you can use it's extension methods in order to add operators to the query such as:
var query = svc.Posts.Where(p1=>p1.PostID == 2);
which results in the following URI for querying the data service: http://localhost:1039/BlogDataService.svc/Posts(2)/
var query = svc.Posts.OrderBy(p=>p.PublishDate).Skip(2).Take(2);
which produces: http://localhost:1039/BlogDataService.svc/Posts()?$orderby=PublishDate&$skip=2&$top=2
and you get the idea...
III. LINQ to ADO.Net Data Services
8. Use LINQ to ADO.Net Data Services to query the service. Again, since the DataServiceQuery is an IQueryable, you can use LINQ syntax to query it:
var query = (from p in svc.Posts
where p.PostID > 3
orderby p.PublishDate
select p).Skip(2).Take(2);
Conclusion
ADO.Net Data Services provides a rich mechanism for querying a data service from a .net client application. We start by creating the DataServiceContext, and using DataServiceQuery objects to perform queries. The datasvcutil.exe tool can generate the client side entities for us.
Enjoy!
Visual Studio 2008 SP1: EntityDataSource for ASP.Net
EntityDataSource is a new DataSource control for ASP.Net (just like ObjectDataSource and SQLDataSource and more recent - LinqDataSource) which makes declaratively binding ASP.NET UI controls to Entity Data Models very easy.
In this post I will build a simple web application that uses EntityDataSource in order to demo how it is being used.
1. Create a simple Web Application, and create an initial GridView on it.
<body>
<form id="form1" runat="server">
<h1>
Using the EntityDataSource</h1>
<asp:GridView runat="server" ID="GridView" />
</form>
</body>
2. Create a new ADO.Net Entity Data Model. Add a new ADO.Net Entity Data Model item to the project, and generate a model from an existing database. For this sample I am using the Bank DB Schema, but you can use any other schema as well.
3. If the EntityDataSource is not shown in the Toolbox under the Data category - Add it. Right click the toolbox and select the Choose Items... option.
In the Choose Toolbox Item dialog, set the filter to Entity and check the EntityDataSource component.
4. Add a reference to System.Web.Entity.dll. The EntityDataSource component is part of a new assembly in SP1 called System.Web.Entity.dll, and its designer support components can be found in System.Web.Entity.Design.dll.
5. Bind the grid to the EntityDataSource. Switch to design view and select the gridview to display the smart tag. In the Choose Data Source drop down select the option <new data source...>
This will start the Data Source Configuration Wizard. Select the Entity Data Source and provide a meaningful name.
Select the name of the connection to use and the name of the container.
Select the name of the EntitySet you want to display its entities and the columns you would like to display. If the EntitySet contains a hierarchy of entity types, you can filter the type you want.
When we click the "Finish" button, VS 2008 will declare a <asp:EntityDataSource> within the .aspx page, and update the <asp:gridview> to point to it.
And in the source view it looks like:
<asp:GridView runat="server" ID="GridView" " DataKeyNames="CustomerID"
DataSourceID="EntityDataSource" >
<Columns>
...
</Columns>
</asp:GridView>
<asp:EntityDataSource ID="EntityDataSource" runat="server"
ConnectionString="name=BankEntities" ContextTypeName=""
DefaultContainerName="BankEntities" EntitySetName="Customers">
</asp:EntityDataSource>
6. If we now run this application, we can see the details in the grid.
In this post I had a step by step guide on how to use the EntityDataSource in a very basic way. In the next post I'll show how to filter entities using a where clause.
Enjoy!
Visual Studio 2008 SP1: EntityDataSource Where Clause
This post in one of a series of blog posts about SP1 of Visual Studio 2008 and .Net Framework 3.5.
In the previous post about the EntityDataSource I've showed how to use it in a very basic way, which meant that I didn't customize the EntityDataSource at all. In this post I'll show how to filter the displayed entities by adding a where clause expressed by an Entity SQL statement.
Taking from where I finished in the last post, I now want to filter the customers by their city, according to the city that was selected in a listbox control. To so that:
1. Add a listbox control that contains items for filtering the list of entities. For example, if we want to filter customers by city, we will add a listbox similar to:
<asp:ListBox ID="list" runat="server" AutoPostBack="true">
<asp:ListItem Text="Tel Aviv" Value="Tel Aviv" />
<asp:ListItem Text="Haifa" Value="Haifa" />
</asp:ListBox>
2. Go to the property grid of the EntityDataSource and locate the Where property. In this property, write an Entity SQL statement that may have parameters that start with @, or use the Expression Editor to do this. For example: it.City == @city
3. Using the Add Parameter button in the Expression Editor, add a new parameter with the same name as you used in the where clause, and choose the parameter source. You can choose to take the parameter value from a session value, from another control's value, and some other options. For this sample, choose Parameter Source = Control, and from the ControlID dropdown, select the listbox control ID.
Note: In the Beta of SP1 of Visual Studio 2008 and .Net Framework 3.5 there is a known bug that prevents you from using this Expression Editor and choose the parameter source, which will be fixed when we RTM. For now, after specifying the Where Expression, switch to the source view and add the parameters manually:
<asp:EntityDataSource ID="EntityDataSource" runat="server" ConnectionString="name=BankEntities"
ContextTypeName="" DefaultContainerName="BankEntities" EnableDelete="True" EnableInsert="True"
EnableUpdate="True" EntitySetName="Customers" Where="it.City == @city">
<WhereParameters>
<asp:ControlParameter Name="city" Type="String" ControlID="list" PropertyName="SelectedValue" />
</WhereParameters>
</asp:EntityDataSource>
4. Run and browse to the page, and select one of the values in the listbox. The data shown in the gridview will be refreshed according to the where clause and the selected value of the listbox.
Enjoy!
Visual Studio 2008 SP1: ADO.Net Entity Framework support for FILESTREAM
In Visual Studio 2008 and .Net Framework 3.5 SP1, not only that LINQ to SQL supports SQL Sever 2008 FILESTREAM, but also the ADO.Net Entity Framework supports FILESTREAM as well.
Using the File Management Schema from my SQL Server 2008 FILESTREAM post, I created a simple .net application that uses Entity Framework in order to access the file contents.
Just a reminder of how the Files table looks like:
CREATE TABLE [dbo].[Files]
(
FileID uniqueidentifier NOT NULL ROWGUIDCOL PRIMARY KEY,
FileContents varbinary(max) FILESTREAM DEFAULT(0x)
)
and the corresponding Entity Data Model and generated code look like:
public partial class Files : global::System.Data.Objects.DataClasses.EntityObject
{
[EdmScalarProperty(EntityKeyProperty=true, IsNullable=false)]
[DataMember]
public global::System.Guid FileID
{
get { ... }
set { ... }
}
private Guid _FileID;
[EdmScalarProperty]
[DataMember]
public byte[] FileContents
{
get { ... }
set { ... }
}
private byte[] _FileContents;
}
Notice that the FileContents field is defined as byte[].
Reading a File Content
FileManagementEntities ctx = new FileManagementEntities();
var query = ctx.Files;
foreach (var file in query)
{
System.IO.File.WriteAllBytes(file.FileID + ".txt", file.FileContents);
}
In the above code block I create a new instance of the object context and query for all the files. Going over the files, I take out the file contents (as a byte array) and write it to another file in the file system.
Adding a new File
Files newFile = new Files();
newFile.FileID = Guid.NewGuid();
newFile.FileContents = System.IO.File.ReadAllBytes("TextFile1.txt");
ctx.AddObject("Files", newFile);
ctx.SaveChanges();
In the above code I create a new Files object, assign a new ID and read all the contents of a file ( as a byte array) into the FileContents property. Then, I use standard Entity Framework methods to add the file into the EntitySet and save the changes to the database.
Conclusion
ADO.Net Entity Framework adds the support of using SQL Server 2008 FILESTREAM in Visual Studio 2008 and .Net Framework 3.5 SP1. The FILESTREAM columns is represented as a byte[] field that we can access it and get the file contents.
Enjoy!
Visual Studio 2008 and .Net Framework 3.5 SP1 Beta - A Guided Tour
Over the last month I've been working with an internal build of the Visual Studio 2008 and .Net Framework 3.5 SP1 Beta in order to explore the new features in it. Now that the beta was announced (here, here) I can finally write about it. This post lists all the posts I've written exploring the service pack.
Overview of the Service Pack 1
ADO.Net Entity Framework
ADO.Net Data Services
ADO.Net Sync Services
SQL Server 2008 and Data Access Technologies
Enjoy!
Download and Install Visual Studio 2008 and .Net Framework 3.5 SP1 Beta(KB945140)
Visual Studio 2008 and .Net Framework 3.5 SP1 Beta was announced today after being kept as a secret for some time.
Here is the list of available downloads as part of this release:
Before you install this update, you should remove old previews such as:
- ADO.Net Entity Framework Beta 3 + Tools
- ASP.Net 3.5 Extensions Preview
- Visual Studio 2008 Support for SQL Server 2008 February CTP
After you install the update, you will be prompted for choosing Visual Studio settings.
While you install the Visual Studio 2008 and .Net Framework 3.5 SP1 Beta, you can take the time to take the guided tour of the service pack.
Enjoy!
SQL Server 2008 FILESTREAM - Part 1
SQL Server 2008 has a lot of new cool features that I've talked about before:
One of the great features in SQL 2008 is it's FILESTEAM support. To make things short, we can now not only store the path to the file in our database, but we can now store the whole file inside the SQL Server and than backup, restore and manage it just as we would do with any other data.
In this post series I will build a file management database with SQL 2008 FILESREAM and build a .net client application that works with it.
1. Enable FILESTREAM support
To enable FILESTREAM support, run the following sql command
exec [sp_filestream_configure] @enable_level = 3;
This command enables or disables the support according to the parameter. The value 3 means that FILESTREAM will be enabled for Transact-SQL, local file system access, and remote file system access.
You can find more details about sp_filestream_configure here.
2. Create a database with a File Group that contains FILESTREAM
To create a database instance with a file group that contains FILESTREAM, run the following sql command. This creates the database with 3 file groups, but only one of them contains FILESTREAM as you can see in the command.
CREATE DATABASE FileManagement
ON
PRIMARY (
NAME = FileManagement_Primary,
FILENAME = 'c:\temp\data\FileManagement.mdf'),
FILEGROUP FileStreamGroup CONTAINS FILESTREAM (
NAME = FileManagement_FileGroup,
FILENAME = 'c:\temp\data\FileManagement')
LOG ON ( NAME = FileManagement_Log,
FILENAME = 'c:\temp\data\FileManagementLog.ldf')
GO
This command will create the following directories structure:
In the Data Directory, I could find the files specified above:
and in the FileManagement directory, I could find the following content:
3. Create a Table with FILESTREAM
Note: FILESTREAM is not a type of a column, but it is a property you put on a varbinary(max) column.
So, in order to create a table with a varbinary(max) column that will be used for FILESTREAM, run the following command. Note that I don't have any additional columns to the file itself.
CREATE TABLE [dbo].[Files]
(
FileID uniqueidentifier NOT NULL ROWGUIDCOL PRIMARY KEY,
FileContents varbinary(max) FILESTREAM DEFAULT NULL
)
4. Add Test Data
In order to get the feeling of the experience of working with files, let add an empty file:
insert into Files
values (newid(), null);
If we now query the database to see the files in it:
we will get the following result (the GUID will probably be different...)
FileID FileContents
------------------------------------ ------------------------------------------------------------------
8247CE78-74DF-4BDE-A08D-9760AE0B1555
and If we look at the FileManagement directory, we can see that a new directory has been created
If we add a file with some content:
insert into Files
values (newid(), CAST ('my test file' as varbinary(max)));
we can query for it and see:
FileID FileContents
------------------------------------ ------------------------------------------------------------------
8247CE78-74DF-4BDE-A08D-9760AE0B1555
3C028D84-BA68-4E7B-8E87-B9889900D728 0x6D7920746573742066696C65
Since this hexadecimal content doesn't mean anything to use, and the fact that File is a type in SQL, we can query for some additional data such as path name:
select FileID, FileContents.PathName() as Path from Files
and get the following result:
FileID Path
------------------------------------ ------------------------------------------------------------------
8247CE78-74DF-4BDE-A08D-9760AE0B1555 NULL
3C028D84-BA68-4E7B-8E87-B9889900D728 \\GUYBUR-PC1\SQLEXPRESS\v1\
\FileManagement\dbo\Files\FileContents\
\3C028D84-BA68-4E7B-8E87-B9889900D728
Summary
In this post I showed how to create an SQL Server 2008 database with FILESTREAM support and add some text data into it. In the next post I will show how to write a .net client application that works with this database to add and change files.
Enjoy!
איך להיות בלוגר החודש של MSDN Pulse
מדי 1 לחודש, כחלק מגליון ה- MSDN Pulse נבחר בלוגר החודש. בגליון חודש אפריל 2008 בלוגר החודש היה גדי אלקריף, מהנדס תוכנה במעבדות SAP בישראל, ובגליון מאי 2008 - גיל פינק, מפתח בכיר ויועץ ב- SRL.
איך תוכלו גם אתם להבחר כבלוגר החודש של MSDN Pulse? פשוט ייצרו תוכן איכותי למפתחים.
הנה כמה שיקולים שנלקחים בחשבון כאשר באים לבחור את בלוגר החודש:
- כמות הפוסטים הטכניים למפתחים במהלך החודש
- יחס בין כמות פוסטים טכניים למפתחים לבין פוסטים לא טכניים
- כמות הפוסטים עם חומר מקורי
- כמות סרטונים שפורסמו
יש עוד פרמטרים שכדאי לנו להתייחס אליהם?
חושבים שאתם צריכים להיות בלוגר החודש או רוצים להציע בלוגר אחר? עשו זאת!
תהנו!
More Posts
Next page »