Beth's Chinese blog
As I mentioned before, I’ll be speaking this weekend at Silicon Valley Code Camp (http://siliconvalley-codecamp.com/). This is a FREE event at at Foothill College and it’s not just Microsoft technologies. A lot of great speakers will be there too.
The schedule is up so check it out and mark the ones you plan on attending. I’ll be presenting two sessions on Saturday:
Also on Sunday there will be a Cloud Camp Unconference where early adopters of cloud computing technologies can exchange ideas and give insight on how to plan for and take advantage of cloud computing.
There are already over 1300 people registered so it’s sure to be a great event. Register today!
See you this weekend!
Yesterday I posted about how we could create a letter generator (mail merge) that took data from a database to create a Word 2007 document using the System.IO.Packaging class. I showed how to take data from Northwind using a single LINQ query to create XDocument objects representing letters to customers and then create a single document of all the data using XML Literals.
Today I want to show how we can embed images into the documents as well as text. Since we’re using Northwind for this example, we’ll use the Employee.Photo field from that database.
The Letter Template with Photo
The first thing to do is create a letter template with our field placeholders as well as an image placeholder. We’ll also need to insert a page break at the end because we’re going to merge all the letters into a single document for easy printing:
The next step is to grab the document.xml from this docx file. Because it’s an Open XML package, as I showed in yesterday’s post, we can just rename this file with a .zip extension temporarily and drill into the word\document.xml. However, there’s an easier way to work with these packages by installing the Open XML package editor Visual Studio add-in from the VSTO Power Tools download.
Once you install the VSTO Power tools, save this template in your project. Then you can just double-click on the .docx file in the Solution Explorer and it will open the package editor (it also works with .pptx and .xslx files). Now you can drill into the document.xml directly in Visual Studio and inspect all the parts of the package easily.
The difference between this template and the template we used yesterday is there is an image imbedded into the document. So you will see an image embedded into the package in the media folder. This is referred to as an “image part”. This part is linked to the “main document part” which is the word\document.xml file. The Open XML package editor shows the links (relationships) to all the parts. When you look at the relationship properties you’ll see that it has a unique ID. This is the same ID that you will see in the main document part at the point where we embedded the image, in our case it’s set to rId4. (I’ve omitted all the WordProccessingML for clarity):
<?xml version="1.0" encoding="utf-8" standalone="yes"?> <w:document ... <w:body> ...
<w:drawing> ...
<a:graphic xmlns:a="http://schemas.openxmlformats.org/drawingml/2006/main"> <a:graphicData uri="http://schemas.openxmlformats.org/drawingml/2006/picture"> <pic:pic xmlns:pic="http://schemas.openxmlformats.org/drawingml/2006/picture"> <pic:nvPicPr> <pic:cNvPr id="0" name="image1.png"/> <pic:cNvPicPr/> </pic:nvPicPr> <pic:blipFill> <a:blip r:embed="rId4" cstate="print"/> <a:stretch> <a:fillRect/> </a:stretch> </pic:blipFill> <pic:spPr> <a:xfrm> <a:off x="0" y="0"/> <a:ext cx="1829055" cy="2124372"/> </a:xfrm> <a:prstGeom prst="rect"> <a:avLst/> </a:prstGeom> </pic:spPr> </pic:pic> </a:graphicData> </a:graphic> ... </w:drawing> ...
<w:p w:rsidR="00622A50" w:rsidRDefault="00622A50"> <w:r> <w:br w:type="page"/> </w:r> </w:p> ...
</w:body> </w:document>
Installing the Open XML SDK
As I mentioned yesterday, manipulating the package directly with the System.IO.Packaging classes gets a little bit tricky when you have to start adding embedded images and objects and linking them together. Instead, it’s easier to use the Open XML SDK to do this because it will handle linking new image parts we add to the package automatically for us. The SDK provides a strongly-typed way of accessing the parts in a package so it cuts down on the amount of code you have to write yourself. Version 1.0 requires only .NET 2.0, Version 2 requires .NET 3.5 and uses LINQ to provide more functionality when querying the parts. This version is currently a CTP. For what we’re doing you can use either version but I have Version 2 installed.
The assembly that is installed in the GAC when you install the SDK is called DocumentFormat.OpenXml.dll and you’ll need to add a project reference to this. When you distribute your application you can simply xcopy the assembly with your application for easy deployment. (After you add the reference, select it in the Server Explorer and then in the Properties window set Copy Local to True in order to have it outputted to the \bin folder when you build your application.)
Now we can add the namespace imports we’ll be working with at the top of our program, including the DocumentFormat.OpenXml.Packaging namespace:
Imports <xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"> Imports System.Xml Imports System.IO Imports DocumentFormat.OpenXml.Packaging
Modifying the Letter Class and LINQ Query to Get the Photo
In order to create letters for our customers in the database, we want to end up with a collection of XDocuments with our data merged into them. I created a Letter class that has two properties, CustomerID As String and Document As XDocument. We’re also going to add a new property for the photo byte array called EmployeePhoto As Byte() and OrderID as Integer. Next we need to modify the query so that it selects the photo as well as add the above WordProcessingML into our XML Literal to specify where the image should appear in the document. Because the relationship IDs must be unique, I’m going to use the OrderID as the suffix for the relationship ID for each of the images we want to embed. Like before, we can use embedded expressions to merge the data into the right places in the XDocument.
(I’ve omitted all the XML with the embedded expressions for clarity, take a loot at the code sample for the whole listing.) The bolded areas show the modified sections of the query:
Dim letters = _ From Order In db.Orders _ Join Employee In db.Employees _ On Order.EmployeeID Equals Employee.EmployeeID _ Where Order.OrderDate IsNot Nothing AndAlso _ Order.ShippedDate IsNot Nothing AndAlso _ Order.ShippedDate.Value.Date >= #1/1/1998# _ Let DateOrder = Order.OrderDate.Value.ToShortDateString _ Let DateShip = Order.ShippedDate.Value.ToShortDateString _ Select New Letter With { _ .CustomerID = Order.CustomerID, _ .OrderID = Order.OrderID, _ .EmployeePhoto = If(Employee.Photo IsNot Nothing, Employee.Photo.ToArray, Nothing), _ .Document = <?xml version="1.0" encoding="utf-8" standalone="yes"?> <w:document ... <w:body> ... <w:p w:rsidR="00A9498E" w:rsidRDefault="00A9498E" w:rsidP="00AA2EC6"> <w:r> <w:t xml:space="preserve">If you have any issues please contact </w:t> </w:r> <w:proofErr w:type="spellStart"/> <w:r> <w:t><%= Employee.FirstName & " " & Employee.LastName %></w:t> </w:r> <w:proofErr w:type="spellEnd"/> <w:r> <w:t xml:space="preserve"> at</w:t> </w:r> <w:r w:rsidR="005F35D6"> <w:t xml:space="preserve"> ext.</w:t> </w:r> <w:r> <w:t xml:space="preserve"></w:t> </w:r> <w:r w:rsidR="005F35D6"> <w:t><%= Employee.Extension %></w:t> </w:r> </w:p> ... <w:drawing> ... <pic:blipFill> ... <a:blip r:embed=<%= "rId" & Order.OrderID %>/> ... </w:drawing> ... <w:p w:rsidR="00622A50" w:rsidRDefault="00622A50"> <w:r> <w:br w:type="page"/> </w:r> </w:p> ... </w:body> </w:document>}
The query returns an IEnumerable(Of Letter) objects with the Document property set to the document.xml data for that customer and the EmployeePhoto set to the photo byte array. If we wanted to create separate documents (which is exactly what I demonstrated in my DevProConnections article) it’s actually pretty simple because all we need to do is copy the template and then replace the document.xml and the image data, we don’t have to mess with the relationship ID at all. However if we want to create a single document with all our letters we need to delete the placeholder image part and add new unique image parts for the Letter.EmployeePhoto.
Adding and Linking the Images into a Single Word Document
Because relationship IDs must be unique in a document I used the OrderID as the embedded expression for the r:embed attribute in the XML Literal above. You can choose to use any unique ID you want but it must start with an alpha character, that’s why I prefixed it with “rId”. When you use the Open XML SDK to add the new image parts we have to specify the relationship ID to use. To update the code we wrote yesterday to work with the Open XML SDK and also work with images we first need to add a call to open the Word document template, grab the main document part and then delete the image part by calling DeletePart:
'Place the letters in a separate directory Dim sourceFile = CurDir() & "\LetterTemplate.docx" Dim letterDir = CurDir() & "\Letters\" My.Computer.FileSystem.CreateDirectory(letterDir) Dim mergeFile = letterDir & "AllLetters.docx" My.Computer.FileSystem.CopyFile(sourceFile, mergeFile, True) 'Open the Document template using the Open XML SDK Using wordDoc = WordprocessingDocument.Open(mergeFile, True) 'get the main document part (document.xml) Dim mainPart = wordDoc.MainDocumentPart() 'Delete the placeholder image from the template so we can replace it with the real photos mainPart.DeletePart("rId4")
This opens the Word document as read/write and then deletes the placeholder image. You should also notice at this point that the code is a bit simpler to open the package than yesterday because we don’t have to declare the relative URI’s or content types. The Open XML SDK helps us here. Next we need to load the document.xml into an XElement object and grab the body:
Dim mainDocumentXML As XElement Using sr As New StreamReader(mainPart.GetStream) mainDocumentXML = XElement.Load(sr) End Using Dim mainBody = mainDocumentXML...<w:body>.First() Dim i = 0
Now we can loop through all the letters, grab the body of those XDocuments and then add an image part. We need to specify the content type and the relationship ID that must match what we embedded in the XML Literal in our query. Then we can either replace the main document body with the first letter (overwrite our placeholder data) otherwise we append the rest of the letter contents into the document:
For Each letter In letters Dim nextBody = letter.Document...<w:body>.First() 'Add the new imagePart. The SDK takes care of linking this properly. Dim imagePart = mainPart.AddImagePart("image/png", "rId" & letter.OrderID) If i = 0 Then 'Replace the first body contents in the template mainBody.ReplaceNodes(nextBody.Elements()) Else 'Otherwise append the next letter contents mainBody.Add(nextBody.Elements()) End If
Next we need to feed the byte array data into the ImagePart. Note that since I’m using Northwind for this example there is an OLE header on those images that we must strip off that is 78 bytes. If you are using your own images stored in SQL Server you won’t have to do that:
'Now feed the byte array into the ImagePart If letter.EmployeePhoto IsNot Nothing Then 'Using ms As New System.IO.MemoryStream(letter.EmployeePhoto.ToArray) 'NOTE: Northwind Photos have an OLE header on them that we must strip off. ' You don't have to do this if you are using your own SQL-server stored images Using ms As New System.IO.MemoryStream(letter.EmployeePhoto, 78, _ letter.EmployeePhoto.Length - 78) imagePart.FeedData(ms) End Using End If i += 1 Next
Now that we’ve built up the document XML, added our image parts and linked them properly, the final step is to replace the document part with our XML just like before:
'Replace the document part with our XML Using sw As New StreamWriter(mainPart.GetStream(FileMode.Create)) mainDocumentXML.Save(sw) End Using End Using
Now when we run the application you will see a single document with all the letters, including photos, that’s much easier to print than multiple files.
There’s Always Room for Improvement
LINQ & Northwind savvy folks may notice that I’m actually creating duplicates of the employee photos. Since I’m flattening out the data in my query to have 1 photo per order there could be an unnecessary amount of duplicate images. This can cause the document to get bloated. To be more efficient we should reuse the relationship ID’s to the images if they are the same on multiple orders. One approach would be to modify the LINQ query and Letter object to select the EmployeePhoto and then have a collection of order & document data under that. I’ll leave that experiment up to you :-)
Take a look at the full code listing for this example as well as the complete project with other Open XML examples here on Code Gallery.
Enjoy!
With the release of Microsoft Office 2007 we can work with a much simpler, standard, XML format called Open XML which opens the door for many types of applications that cannot work via COM. What if you needed to build a scalable web service that processes many documents in high volume? What if you wanted to quickly read or write to these formats from a client application but wanted to have minimal dependencies on other applications? These types of programs do not want to require Microsoft Office be installed to run. The cool thing is you have the tools already with Visual Basic 2008. XML Literals are an easy way to manipulate any kind of XML, including Open XML.
I’ve written before on how to use XML Literals to manipulate Word documents, if you missed them:
If you aren’t familiar with XML Literals or Open XML then I’d suggest reading the article in the first bullet above first. It’s a great intro into XML Literals, Open XML and it also demonstrates a couple practical ways of reading and writing to Word document formats directly.
Recently I got a great question on how to change the program outlined in the last bullet above to merge letters we were creating on the fly from a database into a single document instead of multiple documents like we’re doing. This would make it much easier for an end user to print them. That’s such an obvious thing I can’t believe I didn’t think of that! For those of you that aren’t familiar with what we did I’ll give a quick recap of that application.
Creating Word Documents with XML Literals
What I wanted to do is take all the Customers in the Northwind database who had some orders shipping today and send them thank you letters. To get started I created a new Word 2007 document with the letter text and some placeholder field names to indicate where I want the data:
Save the document (I named it Doc1.docx) and then rename the .docx extension to .zip and you can drill into the package and see a bunch of XML documents inside it. If we drill down through the zip file we'll see that the text we just typed is located in the \word\document.xml file. Copy this xml and paste it into the Visual Basic editor, it will infer it as an XDocument object, and then you can use embedded expressions to replace the placeholders. (If I’ve lost you, read this article which explains it step-by-step.)
In order to create letters for our customers in the database, we want to end up with a collection of XDocuments with our data merged into them. I created a simple class called Letter that has two properties, CustomerID As String and Document As XDocument. Then I wrote a LINQ query to select the data and embed it into the document (I’ve omitted all the WordProcessingML for clarity, take a loot at the code sample for the whole listing.)
Dim letters = _ From Order In db.Orders _ Join Employee In db.Employees _ On Order.EmployeeID Equals Employee.EmployeeID _ Where Order.OrderDate IsNot Nothing AndAlso _ Order.ShippedDate IsNot Nothing AndAlso _ Order.ShippedDate.Value.Date >= #1/1/2007# _ Let DateOrder = Order.OrderDate.Value.ToShortDateString _ Let DateShip = Order.ShippedDate.Value.ToShortDateString _ Select New Letter With { _ .CustomerID = Order.Customer.CustomerID, _ .Document = <?xml version="1.0" encoding="utf-8" standalone="yes"?> <w:document ... <w:p w:rsidR="00705CFF" w:rsidRDefault="00112228" w:rsidP="00AA2EC6"> <w:r> <w:br/> </w:r> <w:r w:rsidR="007A5236"> <w:t xml:space="preserve">Dear </w:t> </w:r> <w:proofErr w:type="spellStart"/> <w:r> <w:t><%= Order.Customer.ContactName %></w:t> </w:r> <w:proofErr w:type="spellEnd"/> <w:r w:rsidR="00AA2EC6"> <w:t>,</w:t> </w:r> </w:p> <w:p w:rsidR="00E04FB0" w:rsidRDefault="00AA2EC6" w:rsidP="00AA2EC6"> <w:r> <w:t xml:space="preserve">We’d like to inform you that the order you placed on </w:t> </w:r> <w:proofErr w:type="spellStart"/> <w:r w:rsidR="00112228"> <w:t><%= DateOrder %></w:t> </w:r> <w:proofErr w:type="spellEnd"/> <w:r w:rsidR="00806521"> <w:t xml:space="preserve"> has shipped on </w:t> </w:r> <w:proofErr w:type="spellStart"/> <w:r w:rsidR="00112228"> <w:t><%= DateShip %></w:t> </w:r> ... </w:document>}
The query returns an IEnumerable(Of Letter) objects with the Document property set to the document.xml data for that customer. The next step involved taking the template Doc1.docx file, making a copy of it and then simply replacing the document.xml part inside the package with the one here in the Letter class for each letter. However, what I did was create separate letters for each customer. Instead we want to create one .docx file with ALL the letter data merged inside.
Merging into a Single Word Document
The first thing we need to do is insert a page break between all our letters. The easiest thing to do is to open back up your template Doc1.docx file in Word and insert a page break, save it, and then look at the document.xml again in the package. You should see this WordProccessingML element inserted near the end:
<w:p w:rsidR="00622A50" w:rsidRDefault="00622A50"> <w:r> <w:br w:type="page"/> </w:r> </w:p>
Paste this into the query above in the document exactly where you pulled it out of the document.xml.
Next we have a couple options on how to manipulate the package (docx file). I’ve shown how to use the Open XML SDK before to manipulate documents but in this simple case we can use the System.IO.Packaging classes directly. This is because all we’re doing is working with text in the document. If we were working with images or embedded objects it would be easier to use the SDK. In the next post we’ll add photos of the employees on each order into the document, but for this post let’s just focus on how to merge the text.
So the first thing we need to do is Import some namespaces including an XML namespace that we’re going to use:
Imports <xmlns:w="http://schemas.openxmlformats.org/wordprocessingml/2006/main"> Imports System.IO Imports System.IO.Packaging
Next we need to set up some variables and copy the Doc1.docx template to a new file I’m calling AllLetters.docx.
Dim uri As New Uri("/word/document.xml", UriKind.Relative) Dim contentType = "application/vnd.openxmlformats-officedocument.wordprocessingml.document.main+xml" Dim sourceFile = CurDir() & "\Doc1.docx" Dim letterDir = CurDir() & "\Letters\" My.Computer.FileSystem.CreateDirectory(letterDir) Dim mergeFile = letterDir & "AllLetters.docx" My.Computer.FileSystem.CopyFile(sourceFile, mergeFile, True)
Now we can open the template package and load the main document part as an XElement:
Using p As Package = Package.Open(mergeFile) 'get the main document part (document.xml) Dim mainPart = p.GetPart(uri) Dim mainDocumentXML As XElement Using sr As New StreamReader(mainPart.GetStream) mainDocumentXML = XElement.Load(sr) End Using
The next part is where it gets fun. A word document has a top element structure like this:
<w:document> <w:body> ... </w:body> </w:document>
So what we need to do is grab the body of the template, replace it with the first customer’s document body we have and then append the rest of the elements inside the bodies of the rest of the documents. A word document can only have one <w:body> element for it to be legal. The way we get the <w:body> XElement from the document is using the descendants syntax with the three dot notation (…):
Dim mainBody = mainDocumentXML...<w:body>.First() Dim i = 0 For Each letter In letters Dim nextBody = letter.Document...<w:body>.First() If i = 0 Then 'Replace the first body contents in the template mainBody.ReplaceNodes(nextBody.Elements()) Else 'Append the new contents for the rest of the customers mainBody.Add(nextBody.Elements()) End If i += 1 Next
Now that we’ve built up the right body with all our letter data in it, we can replace the main document.xml part in the package and close it.
'Delete the current document.xml file in the template p.DeletePart(uri) 'Replace that part with our new merged XDocument Dim replace As PackagePart = p.CreatePart(uri, contentType) Using sw As New StreamWriter(replace.GetStream()) mainDocumentXML.Save(sw) sw.Close() End Using p.Close() End Using
Now when we run this, all the letters will be mail merged into the same document for easy printing:
As I mentioned if we have embedded images or objects that we need to replace it gets a little trickier. In the DevProConnections Article I have an example of how to create multiple documents with embedded pictures of the employee’s photo. In the next post I’ll show you how we can use the Open XML SDK to create a single document with embedded pictures as well.
Until then, have a look at the complete code I put up on Code Gallery.
[UPDATE: Merging Text & Photos into a Word Document using Open XML SDK]
Yesterday John posted on the Visual Studio Data blog how to Refresh the Primary Key Identity Column during an Insert Operation using SQL Server. In that post he shows how the DataSet designer sets up a batch Insert statement to retrieve identity keys automatically. A while back I wrote about how to insert data into an Access database using TableAdapters and identity keys. Because Access doesn’t support batch statements, the Visual Studio Dataset designer doesn’t generate the code to retrieve the identity on new rows, so you end up having to write a bit of code yourself to do this as I showed in that post.
Since then I’ve had similar questions about how to do the same thing but using a SQL CE (Compact Edition) database so I thought I’d present the updated code to work with this type of database. Let’s take a look at how to do that, but first some background. (If you don’t care, just skip to the end for the code and sample download ;-))
What is SQL-Server Compact Edition (SQL CE)?
SQL CE is a file-based, single-user database that is really handy to use as local storage for your client applications because of its small footprint. And it’s included with Visual Studio 2008. Check out what’s new in SQL CE 3.5 here. One very typical use of SQL CE is as a local data cache to your SQL Server backend data using sync services in order to create an occasionally connected application. You can learn about the sync framework here and how to create a local data cache in Visual Studio 2008 here.
Setting up Parent-Child Tables and Relationships in SQL CE 3.5 Using Visual Studio 2008
To add a local database file to your Visual Studio 2008 project just select Add –> New Item, choose the Data category and then select Local Database. This will add a SQL CE database file with an .sdf extension to your project.
This will trigger the Data Sources wizard to start but first we need to create some tables in our database so cancel the wizard. Next open up the Server Explorer (or Database Explorer if you are using VS Express) and you should see your SQL CE database listed under Data Connections. Expand the database node and then right-click on Tables and select New Table. A dialog will open that allows you to design your table schema.
For this example I’ll create a parent Categories table and a child Products table with just a few fields to illustrate the concepts here. It’s important when you create a primary key that you choose the int data type, set Unique to Yes and then below in the column properties you set Identity to True. This will create an auto-incrementing, unique primary key.
Click OK when you’re done and then repeat the same process to add a new Products table. This time though we need to specify a field for the CategoryID foreign key and I’m going to make this a required field by setting Allow Nulls to No.
Now we need to add a relationship between these tables so that our little database will maintain referential integrity for us. We’re saying that a Product cannot exist without specifying a Category. We want the database to enforce this so that if we try to delete a Category it will prevent us from doing so if there are any Products. SQL CE 3.5 can maintain this kind of referential integrity for us, just go back to the Server Explorer and right-click on the Products table again but this time select Table Properties. Select the Add Relations page.
Type in the name of the relation you want to create and then select the Foreign Key Table Column, in my case I select CategoryID. Notice that you can also set up cascading or set null/default update and delete rules as well, but for this example we want to leave the rules as NO ACTION. Click Add Columns button then Add Relations button then click OK to save and close.
Setting up the Parent-Child DataSet
Now that we have the database set up we can design our DataSet. This is going to be almost exactly the same as how we set up our Access DataSet here so take a look at that post for the screen-shots, they’ll be the same here. To recap, you need to make sure you set up the relationship on the DataSet properly so that the primary key on the parent will cascade to the foreign key on the child. Right click on the relation in the DataSet designer and select "Both Relation and Foreign Key Constraint" and then set the Update and Delete rules to Cascade.
The other important thing you need to do is set the Primary Key fields on both DataTables to ReadOnly False. We need to do this so we can set them in code and have the DataRelation cascade rules work correctly.
The DataSet designer will set all the rest of the properties and statements up correctly so you don’t have to modify anything else, though I do encourage you to take a look through them. One important thing to notice is the AutoIncrement, AutoIncrementSeed and AutoIncrementStep properties here. These are set to True, –1 and –1 respectively. This means that on the DataSet (client side) the referential integrity on new rows is maintained between the products and categories DataTables using temporary primary keys that are negative integers. These do NOT correspond to the keys in the database for new rows. It’s not until we send the updates to the database that we get the real identity keys so keep that in mind. (And these properties are the same regardless if you are using SQL CE, Access, or SQL Server identity keys.)
Okay so now we are ready to design our master-detail form. This should be a familiar process at this point but just in case here’s a recap. Open the Data Sources window (Main Menu –> Data –> Show Data Sources) and you should see the Categories and Products DataTables that are in the DataSet we just created. Design your Master-Detail form like normal. For this example I drag the Categories as details and then select the related Products by expanding the Categories node and dragging the Products table under there. This will set up a relationship on the form as well so that when we select a Category, it will only show those related products. This is also important to get our inserts to work correctly because the temporary identity key (-1, –2, –3, etc) on the CategoryID will automatically cascade to the Product’s CategoryID.
Loading and Editing the Parent-Child DataSet
Now that the DataSet is set to enforce the foreign key relationships, this means that you must have a parent for every child so you have to load the data in parent then child order. Remember, you have to make sure that every row in the child DataTable will have a corresponding parent row in the parent DataTable. This also means that you have to make sure to call EndEdit on any new parent BindingSource before any children can be added. I’m doing this by adding a handler to the grid’s Enter event. So the code-behind for this form is the same as the Access sample I showed before:
Public Class Form1 Private Sub CategoriesBindingNavigatorSaveItem_Click() _ Handles CategoriesBindingNavigatorSaveItem.Click Me.Validate() Me.CategoriesBindingSource.EndEdit() 'Make sure to call EndEdit on all BindingSources before an update Me.ProductsBindingSource.EndEdit() Me.TableAdapterManager.UpdateAll(Me.MyDatabaseDataSet) End Sub Private Sub Form1_Load() Handles MyBase.Load 'Load parent before child because contraints are enabled on the DataSet Me.CategoriesTableAdapter.Fill(Me.MyDatabaseDataSet.Categories) Me.ProductsTableAdapter.Fill(Me.MyDatabaseDataSet.Products) End Sub Private Sub ProductsDataGridView_Enter() Handles ProductsDataGridView.Enter 'You must commit the parent row to the DataTable before adding child rows Me.CategoriesBindingSource.EndEdit() End Sub End Class
Adding Code to Handle Inserts to SQL CE Databases
We need a way to set the primary key on the parent right after the row is inserted into the database and before any children are inserted. Now that we have keys cascading we just need to write code to handle the RowUpdated event on the DataAdapter inside the TableAdapter partial class. TableAdapters are generated classes that Visual Studio creates for us from the DataSet designer. These classes are declared as Partial Classes so that means we can add code to the same class even if it’s in a separate file. Right-click on the TableAdapter class in the DataSet Designer and select View Code and the partial class file that you can edit will be created for you.
Now we can write code to automatically query the database for the identity key because SQL CE supports the @@IDENTITY command just like Access. We need to execute this query after each new row has been inserted into the database, but before any children. If you’re using Visual Studio 2008 then the TableAdapterManager will handle sending parents first then children for insert operations so all we need to do is handle the DataAdapter’s RowUpdated event. Here’s the complete code listing for the DataSet and TableAdapter partial classes which includes code to set default values on the new rows. Notice it’s very similar to the Access code. We’re just working with a different data access client library by importing the System.Data.SqlServerCe instead of System.Data.OleDb.
Imports System.Data.SqlServerCe Public Class SQLCEIDHelper ''' <summary> ''' Retrieves the primary key auto-number identity values from SQL CE ''' </summary> ''' <remarks></remarks> Public Shared Sub SetPrimaryKey(ByVal trans As SqlCeTransaction, _ ByVal e As SqlCeRowUpdatedEventArgs) ' If this is an INSERT operation... If e.Status = UpdateStatus.Continue AndAlso _ e.StatementType = StatementType.Insert Then Dim pk = e.Row.Table.PrimaryKey ' and a primary key PK column exists... If pk IsNot Nothing AndAlso pk.Count = 1 Then 'Set up the post-update query to fetch new @@Identity Dim cmdGetIdentity As New SqlCeCommand("SELECT @@IDENTITY", _ CType(trans.Connection, SqlCeConnection), _ trans) 'Execute the command and set the result identity value to the PK e.Row(pk(0)) = CInt(cmdGetIdentity.ExecuteScalar) e.Row.AcceptChanges() End If End If End Sub End Class Namespace MyDatabaseDataSetTableAdapters Partial Public Class CategoriesTableAdapter Private Sub _adapter_RowUpdated(ByVal sender As Object, _ ByVal e As SqlCeRowUpdatedEventArgs) _ Handles _adapter.RowUpdated SQLCEIDHelper.SetPrimaryKey(Me.Transaction, e) End Sub End Class Partial Public Class ProductsTableAdapter Private Sub _adapter_RowUpdated(ByVal sender As Object, _ ByVal e As SqlCeRowUpdatedEventArgs) _ Handles _adapter.RowUpdated SQLCEIDHelper.SetPrimaryKey(Me.Transaction, e) End Sub End Class End Namespace Partial Class MyDatabaseDataSet Partial Class CategoriesDataTable Private Sub CategoriesDataTable_TableNewRow(ByVal sender As Object, _ ByVal e As System.Data.DataTableNewRowEventArgs) _ Handles Me.TableNewRow 'Set defaults so that constraints don't fail when EndEdit is called Dim cat = CType(e.Row, CategoriesRow) cat.CategoryName = "[new]" End Sub End Class Partial Class ProductsDataTable Private Sub ProductsDataTable_TableNewRow(ByVal sender As Object, _ ByVal e As System.Data.DataTableNewRowEventArgs) _ Handles Me.TableNewRow 'Set defaults so that constraints don't fail when EndEdit is called Dim product = CType(e.Row, ProductsRow) product.ProductName = "[new]" End Sub End Class End Class
Now when we run our form, click the Add button on the ToolStrip to add a new Category and then enter new Products in the DataGridView below. Click Save and you will see the identity keys refresh back into the DataTables from our SQL CE database.
So to recap:
Download the sample application from Code Gallery.
I hope that clears up the confusion on how to work with file-based databases like Access and SQL CE that don’t support batch statements. Once you understand how ADO.NET is working with your DataSets then it’s much easier to understand how to configure things like this. SQL CE is a great FREE database for single-user applications and I encourage you to have a look at it if you’re building these types of client applications.
SQL CE also supports the Entity Framework so that would probably be a good follow-up post to this one… next time! ;-)
I just posted an interview on Channel 9 with Paul Yuknewicz, Lead Program Manager on the Visual Studio team. Paul discusses Visual Basic 6 runtime and IDE support options as well as proven techniques, tools and best practices for interoperability and gradual, phased migration to .NET.
Channel 9 Interview: What is Microsoft's Visual Basic 6 Support Strategy?
Read the Official Support Statement on Windows 7, Windows Vista, and Windows XP
Today we also updated the the Visual Basic 6 Resource Center with better navigation, Visual Basic 6 samples and components, important downloads, as well as migration tools and training mentioned in the interview.
Please visit the Visual Basic 6 Migration page for information on how to plan and execute a gradual, phased migration to .NET.
Tomorrow I'll be speaking at the Central California .NET Users Group located in Fresno, CA. I'll be tag teaming with Tim Huckaby, CEO of Interknowlogy showing off some new features of Visual Studio 2010. I'll talk about the managed language co-evolution strategy and demo all the new language features in VB10 and C#4 -- including those that make Office programming a lot easier. I'll also show off some of the new data tooling features like WPF/Silverlight drag-drop data binding as well. And I think Tim has a special surprise. :-)
I'll pretty much dig into any other tooling areas that people want to see as well, I'm playing with some of them today. I'm hoping that this will be a fun, interactive and educational talk. I've never spoken at this group before so I'm looking forward to meeting new .NET developers in my beautiful home state.
Meeting details are here.
See you tomorrow!
“How Do I” Videos have been an incredibly popular free training for Visual Studio developers for a couple years now. Today MSDN launched a pilot on the Visual Basic Developer Center’s “How Do I” Videos that allows you to rate them and add comments. Sweet!
Click the 1 to 5 star rating above the video player to rate the video (1 is bad, 5 is excellent). You can also make comments about the video directly here instead of having to go to Code Gallery or contacting the authors directly. Comments are displayed at the bottom of the page for each video:
I know a lot of you email me directly on questions about the videos I’ve done and I try to respond to them all, but the sheer volume of questions (video related or otherwise) is way too much for just me to answer individually. The forums are much more responsive than me alone for general programming questions. I promise you that I absolutely do read them all though! And I use your common questions and suggestions to feed into topics I blog about here so please keep that coming. :-)
However, by adding comments on the video pages directly I’m hoping that others in the community will also help each other instead of just relying on me. It also helps focus the conversations better around a specific video topic. Of course, I’ll be monitoring all the comments on the Visual Basic How Do I videos and I’ll try to answer them quickly, but having a more open conversation will not only help me, it will help all of you too. It’s always more helpful to see how other developers find solutions to problems in an open forum like this.
So… let ‘er rip! I’m sure you’ll flood all the pages with a plethora of comments :-). Please note that I’ll be out of the office for the extended Labor Day weekend here in the states but I’ll respond when I get back to the office on Tuesday.
Last week I installed Windows 7 Enterprise on my main laptop – a Lenovo T60p ThinkPad. I was up in Redmond and MSIT made it available via a super-easy network boot that installs Office Enterprise, all the IT-required software like anti-virus, and all the drivers you need automatically. (I can’t say enough great things about MSIT but that’s another post.) It took about an hour and a half and I was cooking with gas.
Like moving into a new apartment, moving into a new operating system can take a little time to see the awesomeness as well as understand the quirks. Now that I’ve been doing my “normal work routine” on it for a week I thought I’d post some of my favorite features of this OS as well as share some tips when I was setting up my system and installing programs like Visual Studio and SQL Server 2008.
3 Favorite Windows 7 Features
My #1 favorite feature, hands down, is being able to boot from a VHD. You can create Windows 7 or Windows 2008 RC Server images via Hyper-V, run sysprep on them, and then copy them to your hard drive. Then you use bcdedit to put an entry in your boot menu and Windows will happily boot right into it like normal. You can even access all your files from the image just like it was another partition. Plus, since my laptop supports 64-bit I can create 32 and 64-bit images and boot from any of them. Now when I am testing or demoing Visual Studio 2010 & Office/SharePoint 2010 it will be almost as fast as if I installed on the metal. Yippie! (I say almost, but I only noticed a bit of sluggishness on startup.)
Here’s the instructions I used to set it up. Many thanks to Aviraj for this information and thanks to my co-worker Sam for pointing me to it :-)
My #2 favorite feature is the window docking. Scott Hanselman showed me this at Denver code camp in the beginning of the year. Take any window and drag it near the left or right of your screen and it will dock there, filling up exactly half the screen. This is super helpful if you don’t have two monitors (like on a laptop) but need to see the contents of two windows at once.
If you drag the window to the top of the screen it will automatically maximize.
My #3 favorite feature is that the Taskbar buttons combine and preview, especially for IE browser windows, but it works for any application. In the picture below (click to enlarge) my mouse is hovering over the IE icon on the taskbar and Windows 7 automatically previews all the open windows and tabs. Here I have three browser windows open and 11 tabs total:
Clicking the taskbar icon also does the same thing if there are multiple previews. When you hover over the previews, the full size window will come into view. It’s a really nice feature when you have a lot of windows open for a particular program. However, especially with IE, sometimes you just want to open any of them so you can navigate somewhere else. If you Ctrl+click then it will open the last window you accessed. If you Shift+click then it will open a new instance of the program.
Taskbar Tips
In the pictures above you may have noticed I have a “Quick Launch Bar” (at least that’s what it was called in Vista) next to the Start menu. In Windows 7 there’s no such thing so you have to create it yourself. Right-click the Taskbar, select Toolbars, New Toolbar… and that will open a dialog that wants you to select a folder. That seemed a bit odd to me but I played along and selected somewhere in my Documents library. Name the folder whatever you want, like MyToolbar. Now there’s some text on your Taskbar called MyToolbar next to your system tray. Clicking on it does nothing. Hmmm.
Well it turns out what you need to do is go back into that folder you created and start adding shortcuts! Right-click on MyToolbar, select Open Folder, and then you can start right-dragging and creating shortcuts to all your favorite programs. Now you will see a little double left arrow next to MyToolbar and clicking it will show all your shortcuts:
In order to get the toolbar to look like the old Quick Launch you need to right-click on the Taskbar and uncheck “Lock the Taskbar”. Then you can right-click on MyToolbar and uncheck “Show Text” and “Show Title”:
Now since the Taskbar is unlocked you can stretch out the toolbar and move it to where you want. If you want it on the left then slide it all the way past the Taskbar icons then slide the Taskbar icons back to where you want them. When you’re done, lock the Taskbar again and you should be a happy camper.
“Show Desktop” on the Left
One thing that annoys me about the Taskbar is that you can’t move the Show Desktop which is now at the end of the Taskbar on the right (the black box). I’ve tried for a week to train myself to go to the right instead of the left (like where it was in XP and Vista) but it’s been too many years of my brain on those OSes so I can’t break the habit.
If you’re like me and want the Show Desktop on the left next to the Start menu then create a toolbar like I showed above. Next, open up Notepad and write the following Explorer commands:
[Shell] Command=2 IconFile=explorer.exe,3 [Taskbar] Command=ToggleDesktop
Then save the file in the the toolbar folder you created and name it “Show Desktop.scf” (use the quotes in Notepad’s Save dialog so that it doesn’t append the txt extension). Now you can move it on your toolbar all the way to the left and all will be right with the world (or at least your Taskbar):
Tips on Installing Visual Studio and SQL Server 2008
There is a known compatibility issue with SQL Server 2008 and Windows 7 that is fixed with Service Pack 1. So if you are installing SQL Server 2008 (any version including Express) then you’ll get a compatibility warning. You can just click past it but then make sure you install Service Pack 1 right away (also available via Windows Update). See this post for details on SQL Server Setup on Windows 7. You’ll also want to do the same thing for Visual Studio 2008, install Service Pack 1. You’ll need to do this because if SQL Server SP1 is installed then Visual Studio will need to also have SP1 installed. Finally, make sure you head to Windows Update to get any other latest fixes.
The Windows API Code Pack
To wrap up this Windows 7 post, I’ll leave you with a link to the Windows API Code Pack that’s been featured on the Visual Basic Developer Center for a few weeks now. This pack provides a source code library that can be used to access Windows features from managed code and includes 20+ samples in Visual Basic and C#. Scott Hanselman digs into it this week. It looks like a pretty easy way to take advantage of Windows 7 features in your .NET applications.
Enjoy Windows 7!