Visual Studio database projects are good to support software development. I have successfully used database projects for years and I think it’s time to share my experiences also to global developers audience. In this posting I will introduce you how to effectively use database projects so developers are working with up-to-date schema and test data all the time.
Standardizing development environments
To make things easier I will always use standardized development environments. It makes also life easier for developers because all problems that appear in environments are similar and after some time there are known solutions for all environment specific problems.
One important aspect in standardization is virtualization. We (or at least those with stronger survival instinct or more experiences) use virtual machines for development because they are easy to restore and when VM is crashed then it doesn’t affect host system where usually office and other work supporting software is running. Plus you can increase or decrease the amount of resources that VM uses.
For databases we usually agree in couple of things to make development easier:
- developers use local SQL Server (usually developer edition),
- names of databases are the same in all development machines,
- credentials to access databases are same in all development machines.
Yes, it is not always possible this way and there are exceptional cases but points given here work for most of projects.
Initializing database project
Some of your team who is responsible for developing database is the one who will be also manager of database project in Visual Studio solution. Writing database objects on Visual Studio, then deploying database to local machine just to test changes is non-sense. Management Studio and other WYSIWYG database management tools are way more productive and usually databases are built using these tools. I don’t want to force people to more work manually and I ‘m happy when they are moving on as fast as possible.
Suppose that database guy has already started building database. There are some tables and maybe views. Maybe even some stored procedures. Let’s also suppose there is empty database project also added to solution.
1. Set target database platform
Here is Windows Azure SQL Database selected as target so Visual Studio will check that modifications done to schema project doesn’t conflict with SQL Azure.
2. Create schema comparison
To get changes made to database to schema project we create new database schema comparison. Schema comparison allows us to compare two different schemas. Right click on database project and select Schema Compare…
Empty schema compare window is opened.
Now you have to specify source and target schema:
- source schema will be your local database (you can make new connection and use Windows authentication in this case because you don’t run schema comparison under application pool or other limited account),
- target schema will be the database schema project in your solution.
Here are my sample settings for source and target dialogs:
When settings are done then click Compare button in schema compare window.
You can see now what objects are in database and what is action to do with object on target. If I don’t have good reason I don’t add roles and user accounts to schema project as sometimes their deployment is problematic. Take a look at object definitions window – you can see diff between source and target there.
3. Update schema project
Now click Update button to get database objects to your schema project and save schema to file in database project folder. I usually call this file as LocalDbToSchema.scmp.
Under your schema project you should see now some new folders and database objects. On sample image on right you can see some database tables that were imported from database.
Now include saved database compare file to your database project. This way this comparison is always available for you and you don’t have to manually configure comparison again and again when you want to compare development database with schema project.
This is the schema compare that is used mostly by database developer. Of course, after making update to schema project you have to try to build it to see that there are no errors in schema project.
Adding schema project to local database comparison
As now you have way to get schema changes from database to schema project you are ready to make another comparison that is mostly used by developers who don’t develop database. They need to compare their local databases against schema project and apply changes or recreate their databases.
Steps are almost same as before:
- Right click on database project and select Schema Compare…
- For source schema select your schema project in solution
- For target schema select your local database
- Save schema as file to database project folder (SchemaToLocalDb.scmp)
- Include schema compare file to database project
This is it. As all virtual machines have same database settings then all developers can use the same schema comparison to update or recreate their schemas.
Adding test data
If you made breaking changes that cannot be deployed to existing database with data then developers need to create their databases again. Can you imagine how painful it is when new database is empty and you have to insert all data again to make even elementary things work again? The guy who manages database project will usually also manage data that is deployed to database after it is recreated.
1. Create new post-deployment script
Right click on database project, select Add…, select New Item… and select Post-Deployment Script.
Name it as Script.PostDeployment.sql by example. I’m using often only one script and that’s usually enough for me. Click Add and new post-deployment script will be added to database project. Here you can see the empty window of post-deployment script:
To this script you must add all test data and keep it up to date so developers have only minimum delays in work when something happens to database. Yes, it is additional work to keep data up to date but as database developer you should know very well how to produce test data and how to get data from database to clipboard.
Publish profile
You need two database publish profiles. Schema comparison doesn’t work for you when developer has to delete database and create it again. Yes, schemas are compared but data doesn’t appear to new database automatically. Of course, it is possible to take data with copy and paste from post-deployment script but why so much manual work when we have tools that work nice for us?
Right click on database project, select Publish… You are asked for target database connection string. Add connection to your local development database and then click on button Advanced…
Set publishing options like I have (should work for most projects you have) and then click OK.
Now save publishing settings to file called PublishToLocal.publish.xml and make sure this file is included to database project. When some developer explodes database then it is easy to recreate it again now.
Updating test and production databases
You have also test databases that are used in test environments where users and testers test the system. Also you have production databases. It would be nice if all changes get there automatically but life has shown that for these systems you have to deal mostly manually. You must be sure that you don’t break something and you don’t delete any important data by mistake.
You can still use database comparisons to compare current schema to test or production database but I recommend you to use database accounts that have no permissions to modify data or database objects. Schema comparisons will show you what changes are needed for target databases but you have to do these changes manually and not trust automatics. Even if you have publishing options set correctly you cannot always be sure that automatic modifications are done the way you expected. Make on mistake and face worst problems you have ever seen.
Until you just compare two schemas to find out differences and you use “read-only” accounts you are in safer waters.
How it works in practice
So far, so good. My teams have saved remarkable amount of time to fix mysterious database issues and setting up databases again.
- All developers know how to use database projects regardless their actual role in projects.
- Database and environment related issues are usually easy to find because development environments are standardized and unknown issues appear only when doing something “unusual”.
- Crashed or screwed up database gets back to life with about 15 minutes at maximum.
- Mismatches between database and code are quickly detected and fixed.
You need well disciplined developers to use things like this because guys who don’t really care about quality will just screw up all thing by out-dating schema project and post-deployment scripts. For good developers database projects save time and help them keep work quality high.
Conclusion
Database projects in Visual Studio are powerful tools to use. This posting gave you overview about how to use database projects when developing systems where databases with regular size and structure are used. For more complex and advanced scenarios also rules change and different problems like big amounts of data or impossibility to use local databases may need workarounds. Until it’s regular development you can easily use database projects like described here.
It has been quite here for some month as I was very busy on some critical projects and I also got used to be a father to one wonderful baby. Now it’s time to get back to community stuff and this time I will dig to fancy JavaScript library called Knockout. This was one of interesting journeys I have had together with fellow ASP.NET MVP Hajan Selmani.
How I got to Knockout
Knockout was decision that was to be made in one project where UI is making heavy use of AJAX. Although there were other ways how to go we got some technical decisions that were not adequate and that left not much room for us.
As we had business logic implemented in database level we built WCF service so UI can communicate with database and we can also solve some issues in server side code so we don’t affect UI with every change we make.
This is how the communication between layers was organized in big picture. We had to find something to make communication between UI and server as painless as the choice was knockout. Actually Hajan suggested it at first place and it take me a little time to get to this idea but now I like the idea, of course.
What is Knockout?
Knockout is JavaScript library that allows to implement declarative model binding and use observable variables and collections. This is like client side implementation of MVVM but the engine that it is running on is JavaScript. I hope this explanation is simple enough to get the idea.
Coding with Knockout is as follows:
- write code to get data from server,
- write templates for repeated data,
- add data binding attributes to HTML elements,
- bind data to page.
Here is fragment on page that uses Knockout to show data. data-bind attribute is the one that Knockout checks. In this attribute you tell to Knockout all it has to do with element. On this example you can see foreach, click, text and clickBubble attributes.
<ul data-bind="foreach: $root.Products()">
<li class="ui-state-default"
data-bind="click: function(data){$root.SetCurrentProduct(data)">
<span data-bind="text: ProductName"></span>
<div>
<span class="moveUp"
data-bind="click: function(data){MoveProductUp(data)},
clickBubble: false"></span>
<span class="moveDown"
data-bind="click: function(data){MoveProductDown(data)},
clickBubble: false"></span>
</div>
</li>
</ul>
Syntax of attributes may feel weird at first but still it’s better to have one HTML-attribute to avoid possible clashes with other libraries.
Getting data from server
If you are building rich user interface you probably have enough calls for data to server that you need some service classes.
function ProductService() {
}
ProductService.prototype.GetProductsForUser = function (categoryId, callback) {
$.ajax({
type: "POST",
contentType: "application/json; charset=utf-8",
dataType: "json",
url: "ProductService.svc/GetProductsForUser",
data: { "categoryId": categoryId },
success: function (response) {
if (typeof callback === "function") callback(response);
},
error: function () {
if (typeof callback === "function") callback("error");
}
});
};
Classes like these help us divide service calls to special service classes on UI side so our code remains organized and clean.
View models
As a next thing we need view models for our pages. View models provide data and operations to HTML page and bindings are controlled by Knockout. If we are using collections that users may change through UI code then we can use observable collections that reflect changes through bindings automatically back to HTML DOM.
Using of view models like this is optional – you can organize your code the way you like. As we had some view specific logics we started using view models so we have view model for each HTML page and we can go on with object-oriented code that we love usually most.
function ProductViewModel() {
var self = this;
self.Products = ko.observableArray();
}
ProductViewModel.prototype.Initialize = function () {
ko.applyBindings(this, $('#ProductForm')[0]);
}
ProductViewModel.prototype.LoadProducts = function () {
this.Products.splice(0, this.Products().length);
productService.LoadProducts(null, function (data) {
if (data == 'error') {
console.error('error loading products');
return;
}
for (var i = 0; i < data.LoadProductsResult.length; i++) {
var product = data.LoadProductsResult[i];
product.Categories = ko.observable(product.Categories);
productModel.Products.push(product);
}
});
}
When view model is initialized then Knockout is binding view model to products form element. After that products are loaded from server and products collection is filled with products. As products collection is array observed by Knockout then products list on screen is filled automatically. We can use templates for single products and keep this way our forms simpler to read.
What were main wins using Knockout?
As I am not big JavaScript guru and we had most forms not very simple then my biggest wins are these:
- no need for long-long code to control the UI,
- no need for long code to bind data to form elements,
- HTML got way cleaner and shorter when using Knockout,
- Knockout worked also in more complex situations where more than one view model per page was needed.
Practically Knockout solved almost all nightmares I was afraid of to happen.
How to get started?
I got started using Knockout home page. They have pretty good tutorial and also good examples:
I started from these pages and got on my feet with Knockout quickly.
Conclusion
Knockout is easy to learn although it takes some time to find out all powerful features it has and find answers to questions when working with more complex scenarios. Although materials at Knockout site are very good they don’t explain all things but using search engines I was able to solve all problems I faced. I also had some problems when Knockout got confused but mostly these were simple issues. After week or two with Knockout I found it to be extremely valuable library to use even in more complex scenarios.
Our next stop after Varna was Sofia where DevReach happens. DevReach is one of my favorite conferences in Europe because of sensible prices and strong speakers line-up. Also they have VIP-party after conference and this is good event to meet people you don’t see every day, have some discussion with speakers and find new friends.
Our trip from Varna to Sofia took about 6.5 hours on bus. As I was tired from last evening it wasn’t problem for me as I slept half the trip. After smoking pause in Velike Tarnovo I watched movies from bus TV. We had supper later in city center Happy’s – place with good meat dishes and nice service. And next day it begun…. :)
DevReach 2012
DevReach is held usually in Arena Mladost. It’s near airport and Telerik office. The event is organized by local MVP Martin Kulov together with Telerik. Two days of sessions with strong speakers is good reason enough for me to go to visit some event. Some topics covered by sessions:
- Windows 8 development
- web development
- SharePoint
- Windows Azure
- Windows Phone
- architecture
- Visual Studio
Practically everybody can find some interesting session in every time slot. As the Arena is not huge it is very easy to go from one sessions to another if selected session for time slot is not what you expected.
On the second floor of Arena there are many places where you can eat. There are simple chunk-food places like Burger King and also some restaurants. If you are hungry you will find something for your taste for sure. Also you can buy beer if it is too hot outside :)
Weather was very good for October – practically Estonian summer – 25C and over.
Sessions I visited
Here is the list of sessions I visited at DevReach 2012:
- DevReach 2012 Opening & Welcome Messsage with Martin Kulov and Stephen Forte
- Principled N-Tier Solution Design with Steve Smith
- Data Patterns for the Cloud with Brian Randell
- .NET Garbage Collection Performance Tips with Sasha Goldshtein
- Building Secured, Scalable, Low-latency Web Applications with the Windows Azure Platform with Ido Flatow
- It’s a Knockout! MVVM Style Web Applications with Charles Nurse
- Web Application Architecture – Lessons Learned from Adobe Brackets with Brian Rinaldi
- Demystifying Visual Studio 2012 Performance Tools with Martin Kulov
- SPvNext – A Look At All the Exciting And New Features In SharePoint with Sahil Malik
- Portable Libraries – Why You Should Care with Lino Tadros
I missed some sessions because of some death march projects that are going and that I have to coordinate but it was not big loss as I had time to walk around in session venue neighborhood and see Sofia Business Park.
Next year again!
I will be there again next year and hopefully more guys from Estonia will join me. I think it’s good idea to take short vacation for DevReach time and do things like we did this time – Bucharest, Varna, Sofia. It’s only good idea to plan some more free time so we are not very much in hurry and also we have no work stuff to do on the trip. This far this trip has been one of best trips I have organized and I will go and meet all those guys in this region again! :)
Second stop in my DevReach 2012 trip was at Varna. We had not much time to hang around there but this problem will get fixed next year if not before. But still we had sessions there with Dimitar Georgijev and I had also chance to meet local techies. Next time we will have more tech and beers for sure!
We started in the morning from Bucharest and travelled through Ruse, Razgrad and Shumen to Varna. It’s about 275km. We used cab, local bus and Dimitar father’s car. We had one food stop in Ruse and after that we went directly to Varna. Here is our route on map.
Varna is Bulgarian city that locates on western coast of Black Sea. I have been there once before this trip and it’s good place to have vacation under sun. Also autumn is there milder than here in Estonia (third day of snow is going on). Bulgaria has some good beers, my favorite mankind killer called rakia and very good national cuisine. Food is made of fresh stuff and it is damn good experience. Here are some arbitrarily selected images (you can click on these to view at original size):
 |  |  |
Old bus “monument”
in Razgrad | Stuffed peppers, Bulgarian
national cuisine | Infra-red community having good time and beers |
We made our sessions at one study class of Varna technical university. It’s a little bit old style university but everything we needed was there and we had no problems with machinery. Sessions were same as in Bucharest.
The user group in Varna is brand new and hopefully it will be something bigger one good day. At least I try to make my commits so they get on their feet quicker. As we had not much time to announce the event there was about 15 guys listening to us and I’m happy that it was not too much hyped event because still I was getting my first experiences with foreign audiences. After sessions we took our stuff to hotel and went to hang around with local techies. We had some good time there and made some new friends.
Next time when I go to Varna I go back as more experienced speaker and I plan to do there one tougher and highly challenging session. Maybe somebody from Estonian community will join me and then it will be well planned surprise-attack to Varna :)
When preparing my Sinergija12 code examples I ran into interesting problem. Windows Azure local development storage hangs for about minute and gives StorageServerException stating that there was problem in web server. A little surf in search engines gave me solution.
For me this error happened with default settings of development storage. Everything started working normally when I created storage database to my local SQL Server. MSDN library has good article about steps to take: How to Configure SQL Server for the Storage Emulator:
- Click Start, point to All Programs, and then click Windows Azure SDK.
- Right-click Windows Azure SDK Command Prompt, and then click Run as administrator.
- In the Windows Azure SDK Command Prompt window, type the following command:
DSInit /sqlInstance:<SQLServerInstance>
Where <SQLServerInstance> is the name of the SQL Server instance.
Before running these commands shut down Storage Emulator. You can run it when you are done on Azure command-line.
Some years ago I published list of Microsoft training kits for developers. Now it’s time to make a little refresh and list current training kits available. Feel free to refer additional training kits in comments.
Next week I will be speaker at Sinergija12, the biggest Microsoft conference held in Serbia. The first time I visited Sinergija it was clear to me that this is the event where I should go back. Why? Because technical level of sessions was very well in place and actually sessions I visited were pretty hardcore. Now, two years later, I will be back there but this time I’m there as speaker.
My session at Sinergija12
Here are my three almost finished sessions for Sinergija12.
- ASP.NET MVC 4 Overview
Session focuses on new features of ASP.NET MVC 4 and gives the audience good overview about what is coming. Demos cover all important new features - agent based output, new application templates, Web API and Single Page Applications. This session is for everybody who plans to move to ASP.NET MVC 4 or who plans to start building modern web sites.
- Building SharePoint Online applications using Napa Office 365
Next version of Office365 allows you to build SharePoint applications using browser based IDE hosted in cloud. This session introduces new tools and shows through practical examples how to build online applications for SharePoint 2013.
- Cloud-enabling ASP.NET MVC applications
Cloud era is here and over next years more and more web applications will be hosted on cloud environments. Also some of our current web applications will be moved to cloud. This session shows to audience how to change the architecture of ASP.NET web application so it runs on shared hosting and Windows Azure with same code base. Also the audience will see how to debug and deploy web applications to Windows Azure.
All developers who are coming to Sinergija12 are welcome to my sessions. See you there! :)
In one of my projects I needed to draw radial indicators for processes measured in percent. Simple images like the one shown on right. I solved the problem by creating images in C# and saving them on server hard disc so if image is once generated then it is returned from disc next time. I am not master of graphics or geometrics but here is the code I wrote.
Drawing radial indicator
To get things done quick’n’easy way – later may some of younger developers be the one who may need to changes things – I divided my indicator drawing process to four steps shown below.
 |  |  |  |
| 1. Fill pie | 2. Draw circles | 3. Fill inner circle | 4. Draw text |
Drawing image
Here is the code to draw indicators.
private static void SaveRadialIndicator(int percent, string filePath)
{
using (Bitmap bitmap = new Bitmap(100, 100))
using (Graphics objGraphics = Graphics.FromImage(bitmap))
{
// Initialize graphics
objGraphics.Clear(Color.White);
objGraphics.SmoothingMode = SmoothingMode.AntiAlias;
objGraphics.TextRenderingHint = TextRenderingHint.ClearTypeGridFit;
// Fill pie
// Degrees are taken clockwise, 0 is parallel with x
// For sweep angle we must convert percent to degrees (90/25 = 18/5)
float startAngle = -90.0F;
float sweepAngle = (18.0F / 5) * percent;
Rectangle rectangle = new Rectangle(5, 5, 90, 90);
objGraphics.FillPie(Brushes.Orange, rectangle, startAngle, sweepAngle);
// Draw circles
rectangle = new Rectangle(5, 5, 90, 90);
objGraphics.DrawEllipse(Pens.LightGray, rectangle);
rectangle = new Rectangle(20, 20, 60, 60);
objGraphics.DrawEllipse(Pens.LightGray, rectangle);
// Fill inner circle with white
rectangle = new Rectangle(21, 21, 58, 58);
objGraphics.FillEllipse(Brushes.White, rectangle);
// Draw text on image
// Use rectangle for text and align text to center of rectangle
var font = new Font("Arial", 13, FontStyle.Bold);
StringFormat stringFormat = new StringFormat();
stringFormat.Alignment = StringAlignment.Center;
stringFormat.LineAlignment = StringAlignment.Center;
rectangle = new Rectangle(20, 40, 62, 20);
objGraphics.DrawString(percent + "%", font, Brushes.DarkGray, rectangle, stringFormat);
// Save indicator to file
objGraphics.Flush();
if (File.Exists(filePath))
File.Delete(filePath);
bitmap.Save(filePath, ImageFormat.Png);
}
}
Using indicators on web page
To show indicators on your web page you can use the following code on page that outputs indicator images:
protected void Page_Load(object sender, EventArgs e)
{
var percentString = Request.QueryString["percent"];
var percent = 0;
if(!int.TryParse(percentString, out percent))
return;
if(percent < 0 || percent > 100)
return;
var file = Server.MapPath("~/images/percent/" + percent + ".png");
if(!File.Exists(file))
SaveImage(percent, file);
Response.Clear();
Response.ContentType = "image/png";
Response.WriteFile(file);
Response.End();
}
Om your pages where you need indicator you can set image source to Indicator.aspx (if you named your indicator handling file like this) and add percent as query string:
<img src="Indicator.aspx?percent=30" />
That’s it! If somebody knows simpler way how to generate indicators like this I am interested in your feedback.
My second trip was to DevReach with two stops. My first stop was at Bucharest where I met with my friend Dimitar Georgiev who is one of authors of Gym Realm service. Romanian MVP Andrei Ignat was our host there and organized meeting with local community guys. With me – it was first time in my life – was one more guy from Estonia visiting DevReach and he made the whole trip with me.
Bucharest
We arrived to Bucharest 29.09 at night. We stayed at Hotel Michelangelo. It’s small hotel with nice rooms, free WiFi and very good service. Although my room was on the first floor there was no street noise. We visited one restaurant that offers national cuisine and it was really great.
Next day we went out with local guys and had some beers in “old town”. Bucharest “old town” is nice and cozy. There are many bars open and I am sure everybody will find there some very okay place. After supper we visited one warm karaoke bar where we had beers with local guys.
 |  |
| Andrei Ignat – karaoke star | Agu Suur and Andrei Ion Rinea enjoying
karaoke and tequila |
Community event
Next day we had community event. I made my session about ASP.NET Web API and Dimitar told about how to port ASP.NET web applications to cloud environment. Sessions were held at study class of one local company.
Dimitar Georgiev speaking about porting web apps to Windows Azure.
As it was usual community evening and not some bigger event there were about 12 guys attending from Bucharest. There were both IT-PROs and developers and one nice thing about Bucharest community is that they are listening to you very well and they ask questions if something is unclear or if you slide over from topic they are interested in. Okay, we tried to keep up good tempo so people stay awake and I think we succeeded.
After sessions we went all together to local Piranha pub that was near event venue. We had some beers with local guys and talked with them on different technology topics. It was another good and interesting evening at Bucharest.
I want to go back there for sure. As it was my first trip to Bucharest and mostly I gathered experiences I think my next community trip there will be way stronger. I take it as a challenge. Plus – I have there some new friends and I want to meet them too – be it community event or not. :)
My first self organized trip this autumn was visit to SharePoint User Group Finland community evening. As active community leaders who make things like these possible they are worth mentioning and on spug.fi side there was Jussi Roine the one who invited me. Here is my short review about my trip to Helsinki.
User group meeting
As Helsinki is near Tallinn I went there using ship. It was easy to get from sea port to venue and I had also some minutes of time to visit academic book store. Community evening was held on the ground floor of one city center hotel and room was conveniently located near hotel bar and restaurant.
Here is the meeting schedule:
- Welcome (Jussi Roine)
- OpenText application archiving and governance for SharePoint
(Bernd Hennicke, OpenText) - Using advanced C# features in SharePoint development
(Alexey Sadomov, NED Consulting) - Optimizing public-facing internet sites for SharePoint (Gunnar Peipman)
After meeting, of course, local dudes doesn’t walk away but continue with some beers and discussion.
Sessions
After welcome words by Jussi there was session by Bernd Hennicke who spoke about OpenText. His session covered OpenText history and current moment. After this introduction he spoke about OpenText products for SharePoint and gave the audience good overview about where their SharePoint extensions fit in big picture. I usually don’t like those vendors sessions but this one was good. I mean vendor dudes were not aggressively selling something. They were way different – kind people who introduced their stuff and later answered questions. They acted like good guests.
Second speaker was Alexey Sadomov who is working on SharePoint development projects. He introduced some ways how to get over some limitations of SharePoint. I don’t go here deeply with his session but it’s worth to mention that this session was strong one. It is not rear case when developers have to make nasty hacks to SharePoint. I mean really nasty hacks. Often these hacks are long blocks of code that uses terrible techniques to achieve the result. Alexey introduced some very much civilized ways about how to apply hacks.
Alex Sadomov, SharePoint MVP, speaking about SharePoint
coding tips and tricks on C#
I spoke about how I optimized caching of Estonian Microsoft community portal that runs on SharePoint Server and that uses publishing infrastructure. I made no actual demos on SharePoint because I wanted to focus on optimizing process and share some experiences about how to get caches optimized and how to measure caches.
Networking
After official part there was time to talk and discuss with people. Finns are cool – they have beers and they are glad. It was not big community event but people were like one good family. Developers there work often for big companies and it was very interesting to me to hear about their experiences with SharePoint. One thing was a little bit surprising for me – SharePoint guys in Finland are talking actively also about Office 365 and online SharePoint. It doesn’t happen often here in Estonia.
I had to leave a little bit 21:00 to get to my ship back to Tallinn. I am sure spug.fi dudes continued nice evening and they had at least same good time as I did. Do I want to go back to Finland and meet these guys again? Yes, sure, let’s do it again! :)
More Posts
« Previous page -
Next page »