Database Project in Visual Studio 2012 (SSDT)

Yesterday I was wondering, what is the best practice to manage database changes in TFS. In the past we have been using CVS to do version controls. The way to manage database changes was just having a folder to store all the scripts from the day the project starts. So when it comes to TFS, the same idea can be applied – create a folder of database scripts in the solution.

However, we can actually go one step further with the help of Database project in Visual Studio. It has been there forever but I never noticed it. The Database project stores all types of database objects that you can think of such as Table, View, Stored Procedures, you name it. The best part of it is that you can directly develop the database from inside of the database project in Visual Studio with the real-time compilation to check errors and have it deployed to your real database server from VS!

Here are the screenshots everyone wants:

Create a Database Project in VS2012. Notice that in 2012 they have get rid of “Server Project”. Part of that is because in VS 2012 you don’t need to have a Server project to create server level objects such as Login object. Server objects can be directly added to Database project in VS 2012 via Add -> New Item.. option. More about this can be found later in this post.

4-19-2013 10-43-04 AM

Now we have the project created.  We can start adding objects to the project.

4-19-2013 11-16-39 AM

If you have a database already. There are 2 ways that scripts of an existing database can be imported to the project:

1. Import.

2. Compare Schema and update your project with only the selected objects. (Source database is the existing database and Target database is the project.)

4-19-2013 10-49-09 AM

Here is how the Schema Compare looks:

4-19-2013 10-52-01 AM

Troubleshooting:

1. Unresolved reference

If you have objects from other database, right click on the project and select “Add Database Reference”, OR

Add the referenced objects to the project. Login object is an example. It would not be imported because it is at server level but you can manually create it.

4-19-2013 10-57-38 AM

2. All I see are errors.

Sometimes after importing a huge database developed outside of the VS, one may experience a lot of errors and warnings. A simple workaround is to not importing the database, and use the project to just manage new scripts. This is a more traditional approach to me.

There is just one thing to keep in mind. When adding new scripts, select Script (Not in Build). Otherwise it will check for errors in the scripts, and because the rest of the database objects are not imported, there will always be errors.

4-19-2013 11-05-12 AM

4-19-2013 10-57-38 AM

Technical Recluse from Eloquent JavaScript

I have been reading a book named Eloquent JavaScript  (license). The author is Marijn Haverbeke. The book is great as an introduction to JavaScript as well as an introduction to programming. Besides, the author included a number of humorous stories and analogies to describe some concepts and they worked pretty well.

Below is an excerpt about a recluse who wants to write a technical book from chapter 6. Marijn has even provided several paragraphs of the book the recluse wrote. I have to say there are some philosophies in the book! (Partly because he cited some stories from programmer Fu-Tzu who does not even use 286-computer or MS DOS anymore.)


There once was, living in the deep mountain forests of Transylvania, a recluse. Most of the time, he just wandered around his mountain, talking to trees and laughing with birds. But now and then, when the pouring rain trapped him in his little hut, and the howling wind made him feel unbearably small, the recluse felt an urge to write something, wanted to pour some thoughts out onto paper, where they could maybe grow bigger than he himself was.

After failing miserably at poetry, fiction, and philosophy, the recluse finally decided to write a technical book. In his youth, he had done some computer programming, and he figured that if he could just write a good book about that, fame and recognition would surely follow.

So he wrote. At first he used fragments of tree bark, but that turned out not to be very practical. He went down to the nearest village and bought himself a laptop computer. After a few chapters, he realized he wanted to put the book in HTML format, in order to put it on his web-page…

(Here goes some introductions to basic HTML. The Marijn talked about encoding <, >, and &  into &lt;, &gt;, and &amp;)

So, picking up the story again, the recluse wanted to have his book in HTML format. At first he just wrote all the tags directly into his manuscript, but typing all those less-than and greater-than signs made his fingers hurt, and he constantly forgot to write &amp; when he needed an &. This gave him a headache. Next, he tried to write the book in Microsoft Word, and then save it as HTML. But the HTML that came out of that was fifteen times bigger and more complicated than it had to be. And besides, Microsoft Word gave him a headache.

The solution that he eventually came up with was this: He would write the book as plain text, following some simple rules about the way paragraphs were separated and the way headings looked. Then, he would write a program to convert this text into precisely the HTML that he wanted.

The rules are this:

  1. Paragraphs are separated by blank lines.
  2. A paragraph that starts with a ‘%’ symbol is a header. The more ‘%’ symbols, the smaller the header.
  3. Inside paragraphs, pieces of text can be emphasized by putting them between asterisks.
  4. Footnotes are written between braces.

After he had struggled painfully with his book for six months, the recluse had still only finished a few paragraphs. At this point, his hut was struck by lightning, killing him, and forever putting his writing ambitions to rest. From the charred remains of his laptop, I could recover the following file:

% The Book of Programming

%% The Two Aspects

Below the surface of the machine, the program moves. Without effort,
it expands and contracts. In great harmony, electrons scatter and
regroup. The forms on the monitor are but ripples on the water. The
essence stays invisibly below.

When the creators built the machine, they put in the processor and the
memory. From these arise the two aspects of the program.

The aspect of the processor is the active substance. It is called
Control. The aspect of the memory is the passive substance. It is
called Data.

Data is made of merely bits, yet it takes complex forms. Control
consists only of simple instructions, yet it performs difficult
tasks. From the small and trivial, the large and complex arise.

The program source is Data. Control arises from it. The Control
proceeds to create new Data. The one is born from the other, the
other is useless without the one. This is the harmonious cycle of
Data and Control.

Of themselves, Data and Control are without structure. The programmers
of old moulded their programs out of this raw substance. Over time,
the amorphous Data has crystallized into data types, and the chaotic
Control was restricted into control structures and functions.

%% Short Sayings

When a student asked Fu-Tzu about the nature of the cycle of Data and
Control, Fu-Tzu replied 'Think of a compiler, compiling itself.'

A student asked 'The programmers of old used only simple machines and
no programming languages, yet they made beautiful programs. Why do we
use complicated machines and programming languages?'. Fu-Tzu replied
'The builders of old used only sticks and clay, yet they made
beautiful huts.'

A hermit spent ten years writing a program. 'My program can compute
the motion of the stars on a 286-computer running MS DOS', he proudly
announced. 'Nobody owns a 286-computer or uses MS DOS anymore.',
Fu-Tzu responded.

Fu-Tzu had written a small program that was full of global state and
dubious shortcuts. Reading it, a student asked 'You warned us against
these techniques, yet I find them in your program. How can this be?'
Fu-Tzu said 'There is no need to fetch a water hose when the house is
not on fire.'{This is not to be read as an encouragement of sloppy
programming, but rather as a warning against neurotic adherence to
rules of thumb.}

%% Wisdom

A student was complaining about digital numbers. 'When I take the root
of two and then square it again, the result is already inaccurate!'.
Overhearing him, Fu-Tzu laughed. 'Here is a sheet of paper. Write down
the precise value of the square root of two for me.'

Fu-Tzu said 'When you cut against the grain of the wood, much strength
is needed. When you program against the grain of a problem, much code
is needed.'

Tzu-li and Tzu-ssu were boasting about the size of their latest
programs. 'Two-hundred thousand lines', said Tzu-li, 'not counting
comments!'. 'Psah', said Tzu-ssu, 'mine is almost a *million* lines
already.' Fu-Tzu said 'My best program has five hundred lines.'
Hearing this, Tzu-li and Tzu-ssu were enlightened.

A student had been sitting motionless behind his computer for hours,
frowning darkly. He was trying to write a beautiful solution to a
difficult problem, but could not find the right approach. Fu-Tzu hit
him on the back of his head and shouted '*Type something!*' The student
started writing an ugly solution. After he had finished, he suddenly
understood the beautiful solution.

%% Progression

A beginning programmer writes his programs like an ant builds her
hill, one piece at a time, without thought for the bigger structure.
His programs will be like loose sand. They may stand for a while, but
growing too big they fall apart{Referring to the danger of internal
inconsistency and duplicated structure in unorganized code.}.

Realizing this problem, the programmer will start to spend a lot of
time thinking about structure. His programs will be rigidly
structured, like rock sculptures. They are solid, but when they must
change, violence must be done to them{Referring to the fact that
structure tends to put restrictions on the evolution of a program.}.

The master programmer knows when to apply structure and when to leave
things in their simple form. His programs are like clay, solid yet
malleable.

%% Language

When a programming language is created, it is given syntax and
semantics. The syntax describes the form of the program, the semantics
describe the function. When the syntax is beautiful and the semantics
are clear, the program will be like a stately tree. When the syntax is
clumsy and the semantics confusing, the program will be like a bramble
bush.

Tzu-ssu was asked to write a program in the language called Java,
which takes a very primitive approach to functions. Every morning, as
he sat down in front of his computer, he started complaining. All day
he cursed, blaming the language for all that went wrong. Fu-Tzu
listened for a while, and then reproached him, saying 'Every language
has its own way. Follow its form, do not try to program as if you
were using another language.'