VistaDB .Net Database Blog

Programming is more and more about how to find information

js_vistadb — Sat, 07 Nov 2009 14:50:00 -0400

Do you have the complete .Net framework memorized and know every class from the back of your hand? (Wow, impressive if you do) Most people cannot remember that much information. Back when all we had was the paltry C runtime library I knew guys who reveled in their esoteric knowledge of functions and overloads. Yes, this was back in the days before we had the fancy Intellisense in our IDEs… I am sure many of you remember.

Never memorize a process or procedure

I learned this the hard way when I was in the military. You should never memorize a process or procedure – the documentation on it could have changed since the last time you did that operation. You always had to go get the book and follow the steps. In this case the books were a hefty series of volumes on UH-1H (Huey) maintenance. The books totaled about twenty 5 inch binders, that each weighed about 15 lbs. No one wanted to go look up the process each time – took too long and the darn books were too heavy.

I was a young 19 year old (yea, it’s been a while) that ran off one day to do a test. I had to pull a transmission filter suspected of metal contaminants' and replace it. Then turn in the old one for inspection. Easy stuff, I was thinking. I had done this task a dozen times before in training sessions – I didn’t NEED the book. Got my tools, ran out to the flight line and did the procedure in about ten minutes time.

Got back to the TI (Technical Inspector) and turned in my paperwork. He immediately asked me what the last revision was on the manual I used for the procedure (each book has a revision number in the front to tell you when it was last updated and what the minor changes were). I rattled off the number I had seen yesterday on the book. He looked at me for a long time (first clue you really screwed up), then sent me off to the lead instructor. I then ran about 5 miles around the flight line while wondering what I had messed up.

Did you read the manual?

When I returned from my little “thinking time” run the Chief Warrant Office asked me, “Did you read the manual?” No point lying at this point, “No Sir!”.

If I had read the manual (that day) I would have known that the procedure had changed last night. A new manual “patch” had come in the previous day and been applied that night. The process had been changed to include a new step for swapping a gear while you had it all apart to get further insight into the gear wear pattern. By skipping that step I had just invalidated the entire process, all my results on the metal analysis would be wrong. You don’t fail a military test like this though.

I got to think about it, and then present a series of “remedial” lectures to my fellow soldiers about the virtues of always reading the book. And about the dangers of thinking you know the answer before you start a task.

Needless to say the lesson did stick in my head

To this day when I go to assemble something I read the instructions first. I go through them to make sure there is nothing unexpected. When I get a new piece of software I always look at the release notes to see if something I was expecting might have changed.

And I never assume that an object is going to do something without actually reading the current information to ensure it still does what I am expecting. This is a lot easier today with Intellisense, you can get the quick information on the function directly while writing code. Make sure what you think it does is actually still there.

Don’t memorize, but know how to find the information

In a recent job interview with an intern I asked him some arcane information that most people will not have memorized. He told me that he didn’t know the answer, but he knew how to look it up and find out. That is what I was looking for, a man not afraid to say he doesn’t know; but does know how to find it.

Make yourself a better programmer

Every programmer can make themselves better by learning how to read auto gen style documentation (frequently called the MSDN style docs). No, it does not give you pretty code snippets you can copy and paste all the time. That is not the point.

The point is that it gives you the objects, hierarchy, methods, etc that you need to see how to use the object. If you don’t understand how the object works a code snippet is worse than useless – it misleads you into thinking that the snippet is the correct way to use the object.

There have been so many documented cases of MSDN docs having worst practices, or snippets that don’t show every possible use. I don’t think they need to, programmers need to learn to read to MSDN docs, explore through the object and it’s parents, etc. That is how you learn more deeply the ways things work. But don’t attempt to memorize it all, just learn how to look it back up again when you need it.

Look at your objects from the outside

Another very useful way to learn is to inspect your own objects from the outside. Don’t look at the code. Does the method name imply what it actually does? Do the parameters make sense without knowing exactly how they will be used? Does the return value cover the logic states you expect coming out of the object?

ADO.NET Data Services with VistaDB for quick client server applications

js_vistadb — Tue, 03 Nov 2009 16:08:31 -0400

We get questions from users asking how they can build client server solutions using VistaDB, or more often just how they can avoid having to share drive letters to share a database. My traditional answer in the past has been to build a simple data access layer (DAL) and expose it over SOAP or WCF. Now with ADO.NET Data Services and our Entity Framework (EF) provider you have another choice.

ADO.NET Data Services (Astoria)

Not familiar with the codename? “Project Astoria” was released with .Net 3.5 SP1 under the moniker ADO.NET Data Services. This services on the surface appears to be about putting generic objects in front of your data and letting it handle protocols for you. The fact that you can take any EF model (VistaDB included) and put an ADO.Net Data Service around it in a controlled manner is fantastic. No more need needing to handle the shared drive scenario. You connect to a service hub, and that hub then applies all the changes and takes care of the ugly handling of edge cases. This is similar to a feature that allowed direct from within SQL Server, but I think too many DBA’s didn’t want to expose their actual SQL Server. Push all that exposure out to IIS and you have the same basic functions.

When is it available?

ADO.NET Data Services is .Net 3.5 SP1 technology and is considered production code. Today you can easily create a service that runs on one local machine and all clients point to it for their data. This single server allows all others to make queries against it without concern for direct access to the database.

What if the service is unavailable?

ADO.NET Data Services requires online access to the service. If the service goes down, the clients can’t do much. There is an offline version of Astoria in the works from Microsoft to allow a local client proxy to build up and sync to the service endpoint. I have watched a few PDC demos on it and it looks impressive, but it is still in the Alpha stages at the time of this writing. I tried to play around with it a little and it was almost all still hard coded to SQL Server and SQL CE.

Even without the offline version of Astoria I think you could build a highly reliable system using just the ADO.NET Data Services. Your client would have to be intelligent enough to know what to do in a failure to connect scenario, but this is no different than a client server database application today. The client still has to handle what happens when the server goes away.

Take an intranet site and add a client component

A sample scenario we just recently deployed on the intranet could expand to work over the internet. We could easily build a client timesheet app (copy local or click once) that knows how to get data through the service rather than direct from the database, and perform the data presentation within that client.

Sample Scenario

We have an existing website that users use to input their time spent daily, and weekly submit the timesheet. At the end of time period someone needs to collect the timesheets, approve and pay the correct employees.

ASP.NET Intranet Site

We already have a system that does this all through ASP.Net for the intranet users. But what if one user needed to work remotely? We could enable the intranet to be accessed remotely, or we could just expose the service and implement security to prevent unauthorized access. Using a click once application for the user approving the timesheets would enable her to work in the office or at home when approving and paying the timesheets without her having to know the difference.

Multiple Ways to get at your data

For this particular scenario we decided to leave the intranet alone for the employees. It works and they know how to use it. But the person approving the timesheets right now has to come into the office in order to approve them. She is not very technical and setting up a VPN would be serious overkill for this simple application.

Works with VistaDB today!

Yes, ADO.NET Data Services work with VistaDB 4 today through the EF provider. We don’t have any samples built specifically around this technology though (hence why this post).

Good sample?

Would this be a sample you would be interested in seeing built? Would you be interested in seeing how to build and deploy an ADO.NET Data Service using VistaDB?

We have the current Timesheet sample that is a simplified version of our timesheet system. I thought if we were going to build this local and remote application for our accountant that others might like to see how it is done as well. I think it makes a very easy scenario to get access to VistaDB database remotely without a lot of custom programming on the developers end. There is not a lot to know about the communications layer, but it could surely be customized for many deployment scenarios.

VistaDB 4 Build 9 with Visual Studio 2010 Beta 2 support

js_vistadb — Sat, 31 Oct 2009 17:13:11 -0400

Build #9

Visual Studio 2010 Beta 2

Fixed issues in the installer if Visual Studio 2010 Beta 2 was installed on the machine it would prevent installation of the Visual Studio 2008 designer. One of the Visual Studio 2008 interfaces is being promoted to .Net 4 automatically in beta 2 for some reason. We have worked around the issue for now. We also had to work around other issues in Beta 2 for DDEX providers that were not present in Beta 1.

Visual Studio 2010 Beta 2 support should be considered, um... Beta. We have it installing, and have done some initial tests. VS 2010 B2 just came out, if you are using it then you hopefully understand that it is not a final release of Visual Studio.

Same bugs still in VS 2010

We already have verified many of the exact same bugs in VS 2008 are still in VS 2010 (like the checkbox for updating an auto increment value after insert still only works for Microsoft providers, but doesn't gray out for third parties). We will start opening tickets with Microsoft on these for VS 2010 (like we did for VS 2008), but I don't hold out a lot of hope of Microsoft fixing these issues since they have been around since the Visual Studio since 2005 days.

Customer Experience Improvement Program

There is an optional download for Professional subscribers to get a CEIP (Customer Experience Improvement Program) enabled version of VistaDB. This is a separate download, and at install time you have to check the box for the Customer Experience application to be installed. If you install the CEIP version of the engine and enable the tray app logs about your Data Builder, Visual Studio Designer, License Manager, etc will be sent to VistaDB. This does not include anything in the actual runtime engine. There is a special VistaDB.Logged.4.dll that you have to reference in your application if you want logs from the engine. Shutting down the tray app will also prevent any logs from being transmitted to us.

This is something we are experimenting with to provide better support, trapping errors on your machine rather than you having to send us a sample, and to understand better the customer usage of features and components.

I have a separate (much longer and verbose) blog post about the CEIP program, how we are building it, why, and more. Look for that in a few days.

New Sample – RSS Aggregator

Added the RSS Aggregator sample project. This project loads feeds from RSS sources and stores them in a VistaDB database. All database activity is done through the ADO.NET provider factories. The tray app also alerts the user when new activity has occurred.

Some of the other samples have continued to have work done on them and have been updated.

Contains Fixes

The Contains SQL parse had a few bugs in the code from an experimental test that were not intended to go live. This was causing some words with keywords (and, or, not) within the word to run part of code for phrase matching that is not currently in the production code. This has been corrected. Yes, we do still intend to bring phrase matches and word combinations to FTS, they are under development.

Cast and Convert changes

Added more logic to the automatic conversion of chars or nchars to double and decimal to handle special cases from SQL Server.

You do things like this in SQL Server:

select cast(' ' as float); 
select cast('' as float); 
select cast('2.0077E1  ' as float);

But the following does not work:

select cast(' ' as decimal); 
select cast('' as decimal); 
select cast('2.0077E1  ' as decimal);

You can do those conversions for float, but not for decimal. We have changed these cases to match SQL Server rules.

Pack and Repair Operations

Packing or repairing a database from ADO.NET functions only work if the database was not originally opened in DDA. If the database is open in DDA then the DDA operations must be used instead. A new exception has been added to these routines when an attempt is made in these circumstances.

Clustered Index and Alter Index

Clustered indexes are not support as we do not store rows on disk the same as SQL Server. We do not reorder the tables on disk for the index to be a "natural" order on disk. Clustering an index in SQL Server reorders the entire table and new insertions are sorted based upon that index as well (which can lead to fragmentation of the index, and the pages on disk as each new insert can cascade into multiple updates to disk). We do not support this concept, but we were parsing the clustered index create syntax from the sql parser. This will now generate an exception as it is not supported.

Alter index is another command that the sql parser was allowing, even though it did not perform as expected. You cannot alter an index, or rebuild an index (packing automatically rebuilds indexes). The only way to rename an index is to drop it and build a new one. Attempting the syntax will now throw an exception rather than silently failing.

VistaDB 4 Entity Framework Quick Start Video

js_vistadb — Sun, 25 Oct 2009 01:59:25 -0400

The Entity Framework makes it really easy to generate models within Visual Studio 2008. You must have Visual Studio 2008 SP1 with .Net 3.5 SP1 installed. You also need VistaDB 4 Build 8 or higher for this video.

I just posted a quick video showing VistaDB 4.0 Build 8 generating an Entity Framework model and adding the model to a console application.

Generate the model

Generating the model is the main point of this video, and it is really quite easy. Once the model has been generated you need to use it, that is a little more complicated.

Entity Framework Video Tutorial

You can watch the video here, or jump over to YouTube if you want to watch it in HD. The Entity Framework quick start video is available from the VistaDB Training page on YouTube.

License your Application

After the model is built I show how to add a licenses.licx file to the application (this same step works for any type of application).

Using the Model

Using the model is outside the scope of this quick start, but I show how to use the new TicketEntities in an application, get back a query and loop through some results.

I will add more videos later showing how to use the model in more detail.

VistaDB 4.0 Build 8

js_vistadb — Sat, 24 Oct 2009 00:40:29 -0400

Build #8

Setup changes

The Data Migration Wizard initial release is included in the setup now.

New samples included and linked through samples folders in the start menu. These include links to the solutions, readme files, and databases used.

Help Search FTS - The HelpSearch sample was created to demonstrate a small search application for help files and forum solutions using ASP.NET and Full Text Search with a VistaDB4 Database.

NTier TODO Sample - Example system using NTier communications through a WCF service that exposes a Data Access Layer (DAL). Includes WPF, Silverlight, and asp.net presentation layers.

Ticket System Sample - Sample ticket system for a small business to respond to customer trouble tickets.

Employee Timesheet Sample - Example website for tracking employee time spent on projects.

Preparing for the Customer Experience changes coming soon. The setup now has the ability to optionally install the Customer Experience tray app for users and set it for startup. This is not enabled in this build, but the work has been done and it is ready.

General Notes

Lots of changes to the help file around User Defined Functions, SQL Server compatibility, group by, aggregates, order by, new examples, etc. The online help has also been updated with the current topics.

Corrected issue in triggers that would case a semi-recursive trigger to fail. New tests are needed for CLR Triggers.

Entity Framework Updates

Lots of changes under the hood to how we generate the ESQL, and the procs used by EF to pull their data. We were using TSQL Procs to pull the data, but the overhead is really high. We now have written CLR Functions (embedded in the EF Provider) to do the work, and only use the TSQL to select the rows from the CLR Functions. This results in a lot faster gen time and less overhead.

This should result in a better model gen experience for developers.

This version DOES correctly generate relational models (as long as you have packed your database recently so you don't have duplicate names). There are a few known issues still, but we are working through them as quickly as possible.

Right now VIEWS do not generate correctly in some circumstances. And in one instance just having a broken view in the database caused a model not to gen correctly (make sure your views are correct if you get this error).

New Error Messages

Added new error messages for attempting to implicitly convert between types or when attempting automatic conversion on behalf of the user.

As an example, attempting the following SQL would result in an unable to convert from BigInt to DateTime without a callstack.

select DATEADD(dd, 0, DATEDIFF(dd, 0, '2009-01-01')) as SaleYear

Now the error message tells you that SaleYear was the attempted assignment column, and the line number in the sql. This makes it much easier to diagnose casting issues in large sql scripts.

VistaDB.Diagnostic.Errors.sql_NumericParameterRequired
Error 661: A Numeric data type is required in function: [FUNCTION]
The following call would result in an input error, but you would not have known the function or the line of code.

SQL: SELECT ROUND('abc', 2); -- NOT LEGAL MUST BE NUMERIC INPUT

Result: 'Input string was not in a correct format.'

VistaDB.Diagnostic.Errors.sql_UnableToImplicitCastType
Error 662: Unable to perform the requested implicit cast operation. Try CAST() to the desired type instead. Output Target: [COLUMN]

VistaDB.Diagnostic.Errors.sql_IncorrectTypeProvided
Error 663: Data Conversion failed, unable to convert input to correct datatype.

In some situations VistaDB does attempt to convert on behalf of the user. As an example you may pass in a string that contains a number to a function, and we will attempt to convert it to an integer or whatever the correct type is for the function. If the Convert.ToInt() (for example) fails we will now return the Error 663 message above. We tried, but the underlying .Net routines didn't know how to convert the type for you.

Spelling correction in the error dda_DatatypeConverion to dda_DatatypeConversion. No change was made to the throw, or the errorid, just the spelling.

Visual Studio 2010 Beta 2

js_vistadb — Tue, 20 Oct 2009 19:03:11 -0400

Yep, BETA 2 is now up. Read the full post from ScottGU’s blog post about VS 2010 and .Net 4.

Download VS 2010 Beta 2

Visit the site to download – MSDN available Oct 21

Ultimate, Professional, etc

Yep, they are now going to ship Visual Studio with naming like the Operating System.

Visual Studio Express: Free Express SKUs for Web, VB, C#, and C++

Visual Studio 2010 Professional with MSDN: Professional development tools as you are used to today with the addition of source control integration, bug tracking, build automation, and more. It also includes 50 hours/month of Azure cloud computing.

Visual Studio 2010 Premium with MSDN: Premium has everything in Professional plus advanced development tools (including richer profiling and debugging, code coverage, code analysis and testing prioritization), advanced database support, UI testing, and more. Rather than buying multiple “Team” SKUs like you would with VS 2008, you can now get this combination of features in one box with VS 2010. It also includes 100 hours/month of Azure cloud computing.

Visual Studio 2010 Ultimate with MSDN: Ultimate has everything in Premium plus additional advanced features for developers, testers, and architects including features like Intellitrace (formerly Historical Debugging), the new architecture tools (UML, discovery), test lab management, etc. It also includes 250 hours/month of Azure cloud computing.

Side by Side Supported

This beta now includes side by side support with VS 2008 (makes our life a LOT easier).

Go live license!?!

You CAN, but should you? You can now ship .Net 4 and VS 2010 Beta 2 projects are production code… But should you really be putting a BETA of the .Net framework on a user machine? I don’t think I would do that. Maybe for a situation where you are writing developer tools and they already have to be installed (like us – the VS 2010 plugin would require you already have VS 2010 installed).

VistaDB 4 Data Migration Wizard initial release

js_vistadb — Sat, 17 Oct 2009 01:49:00 -0400

BETA Level Release

This initial release has some warts and problems that we wanted to address before releasing a DMW for 4, but I am putting this out there so people can convert basic databases to VistaDB 4 in the meantime. We plan to make several changes to the tool before it is declared complete.

This is a port of the 3.x DMW with almost no changes just to get something up quick. It does not know anything about UAC (you had better run it as Admin), it does not know anything about the Windows 7 Libraries of VistaDB 4, etc. Basic port to get new users up and running.

Data Migration Wizard 4

This is a beta level release. It is posted on the public downloads area of our account manager website. You can’t install or use it unless you have a valid VistaDB license on your machine.

Start Menu Shortcut

Launch the tool from the VistaDB 4 Data Migration Wizard folder in your start menu and you will see the familiar starting screen.

Yes, there is a typo where it still says VistaDB 3 on the initial dialog. It does not work with 3.x at all.

Databases Supported

You can convert SQL Server 2005/2008 databases using SQLClient, SQL CE 3.5 SP1, and Access MDB and ACCDB database formats. Although Access conversion is rough, and does not validate things like views at all (don’t convert them).

Converting a Database from SQL Server

Fill out the connection information like normal, but make sure the VistaDB 4 destination database is a writable location (UAC counts here).

Choose the tables and other information

Select the tables, indexes, foreign keys, etc that you want to bring over to VistaDB.

Convert Away!

Then hit next and watch the window scroll by. Each table schema is created one at a time, including any keys, indexes, etc and then the data is filled in as well.

Saving the scripts

After you complete the migration you can save the scripts to re-run it at anytime in the future. The database should now be loaded and packed. You can also play with the pagesize to see what the minimum is for your database. The DMW just sets it to the max allowed right now.

Click the Open Database to launch Data Builder with the database.

Source Version

The source for the DMW is included with the Professional and higher editions of VistaDB. You can customize and build your own special version from the source as needed.

Lite and Personal users will be able to purchase the Data Migration Wizard and source from the online store for an additional fee.

Getting a DATETIME without the time

js_vistadb — Sat, 17 Oct 2009 00:24:16 -0400

We get this question so often in tech support, that I wanted to post a quick tip that hopefully people will find useful.

Remove the time part

To remove the time part from a datetime you can do it the following ways in both SQL Server and VistaDB.

   1:  -- SET SOME DATE WITHOUT A TIME (doesn't matter what it is)

   2:  declare @somedateonly datetime;

   3:  set @somedateonly = '2000-01-01 00:00:00';

   4:  SELECT DATEADD(dd, DATEDIFF(dd, @somedateonly, GETDATE() ), @somedateonly );

You could also do it in a single statement like this. The date doesn’t matter, just that it does not have a TIME component.

   1:  SELECT DATEADD(dd, DATEDIFF(dd, '2000-01-01 00:00:00', GETDATE() ), '2000-01-01 00:00:00' );

How does this work?

The calls are really subtracting the number of days from the GETDATE() call and the entered date. Then it adds the difference to the entered date (which contained no time). The end result is that you have the date without a time being set.

.NET does not cast 0 to a DateTime

In SQL Server you could just pass 0 as the datetime. In .Net a 0 does not cast to a DateTime. So why can’t you use the same syntax in VistaDB? We are 100% managed code, that means we use the .Net 2 runtime engine. There is no way to cast a 0 to a DateTime in .Net.

   1:  DateTime targetdateonly = (DateTime)0; // Does not work

   2:  DateTime targetdateonly = Convert.ToDateTime(0); // Does not work

That means you can’t just pass in a 0 to the datetime part of the DATEADD function in VistaDB. We don’t have a way to convert it to a DateTime in .Net. And remember that the minimum date in SQL Server is WAY different than .Net anyway.

Entity Framework Code Only (not)

js_vistadb — Thu, 15 Oct 2009 13:00:00 -0400

Microsoft’s Entity Framework (EF) team have released a new blog updating their Code Only mode (see original blog article as well). For those not familiar with this concept, the Entity Framework is currently built to work only from a Model (conceptual representation of your database).

Without a database?

Yes, sometimes you need to be able to build an EF model without a database. Maybe you are working from a logical concept that the database doesn’t map to, or maybe you are just working from the model and want to derive the database later.

One thing you cannot do with the current Entity Framework is add facets (think of them like properties of the model) at runtime very easily. Something like adding a new property and then saying it is an identity. Those things pretty much have to be done at model generation today, but what if you don’t know the exact database yet? This is a common scenario for us in our Data Builder tool, we can’t pre-generate a model of your databases. Every one is different.

Vaporware 2.0

I find it discouraging that Microsoft continues to pump out blog posts of things like this; things that will one day be cool. But not today, or even the initial release, maybe sometime. Can you imagine if we here at VistaDB wrote our posts on things we want to one day do?

WOW, we could do anything, because we wouldn’t have to actually write the code!

Just write about what you want to have happen, and see what type of reaction you get in the community.

People used to call this type of application Vaporware. I think this is Vaporware 2.0. You not only don’t talk about what you are shipping today, you don’t write about the next release either. You get to write about the mysterious “patch” release that will happen post the next release.

Never Mind, please ignore this post

Hmm, the biggest part of this blog from Microsoft (to me) is that the code only changes will not be in .Net 4. They are going to roll it out after 4 is released. This is starting to sound familiar… Oh yea, sort of how EF didn’t make it into 3.5, but was a “service pack” to 3.5 SP1.

I fail to see how something this large can be skipped from .Net 4 and then later applied as a service pack. There are provider writer changes, DDL changes, lots of interface work, a service pack? Come on!

Me, Harsh?

I can already see the email in my inbox from some users accusing me of being too harsh. All I can say is that I call them like I see ‘em. Don’t talk about things 18+ months away as coming soon, especially when the next two releases come before this alleged feature.

This post was picked up on Visual Studio magazines weekly email blast as a new feature in EF. Not so, not even the next major release.

VistaDB 4 Lite Edition Released

js_vistadb — Wed, 14 Oct 2009 21:46:00 -0400

The first release of VistaDB 4 Lite (Build 7) is now available on the account manager site!

VistaDB Lite is a very low cost embedded SQL database engine that includes many of the features only found in large server systems like Microsoft SQL Server. VistaDB Lite users can utilize the power of CLR Procedures and Triggers to expand the functionality of the default database engine.

The VistaDB Lite edition is intended for hobbyists, students, teachers, anyone who needs a small footprint xcopy deployable database for a very low cost.

Overview of Editions

Visit the VistaDB Editions Overview page for information about the differences between VistaDB Lite and other editions.

Lite editions are limited to a 1Kb pagesize, which does limit the number of columns, indexes, etc available on a database. But we feel that the Lite edition has the ability to cover most learning and hobbyist scenarios without breaking the bank. ASP.NET support is included with the Lite Edition, so even small data driven websites are possible.

Use CLR Procedures

T-SQL Procs are not supported in Lite, but CLR Procedures are included. We want to encourage the adoption of CLR Procedures with developers. We feel that in most cases T-SQL Procs should only be used for legacy applications, new applications should embrace CLR Procs and unleash their database code!

Not free?

Sorry, we had to discontinue the free edition of VistaDB due to the support demands, and the number of companies who were using it for commercial purposes. We have to put food on the table and feed our families just like anyone else, this was the free as in beer problem (it cost us a lot to build and support, but people used it to make money and not pay us).

We have tried as hard as possible to make VistaDB Lite affordable for hobbyists and students (it is ~$50 right now).

Commercial Use is Ok

Another change from VistaDB Express to VistaDB Lite is that now it is OK to use it in commercial applications. If you are building a small desktop utility, something for shareware / freeware, you can use VistaDB Lite for that purpose. The runtime is royalty free.

What is not present?

Entity Framework and Visual Studio plugins are not supported. You will have to write your Data Access Layer (DAL) using either ADO.NET or Direct Data Access (DDA) methods. Both are supported.

The SQL Server natural order query myth

js_vistadb — Fri, 09 Oct 2009 20:48:00 -0400

There is this myth among developers and DBAs that SQL Server has a natural order that is logical and predictable (and can therefore be relied on). Not so! I wanted to take a few moment to explain and debunk the myth, explain how VistaDB is different, and share something I learned along the way.

The Myth(s)

SQL Server stores entries in a natural order based upon the first column.
SQL Server always appends inserted rows at the end of the current table.
Unindexed data is always available in the order it was inserted.

Have you heard those as well? In most databases an insert is always appended to the end of the current table, this is for ease of insertion and for multi threading where you want to lazy write to the database after the insert (of course servers can get away with that, but we can’t because we don’t know when your application will go away and kill us).

You can’t actually count on ANY of those from SQL Server.

Select * always returns the same order

Many times developers pull a SELECT * from the database in SQL Server and think the order will not change – IT CAN CHANGE. If you do not explicitly put an order by clause on your select you have no guarantee of ordering in SQL Server.

EDIT

You can read the full explanation why this happens over at the SQL Serverpedia blog entry on natural order. I am not sure if this was the original or a repost from Brad's blog, but I wanted to give credit to where I originally read it. The article at SQL Serverpedia was emailed to me from a user asking about how VistaDB works in this same situation. I then got the same article from 2 other users in the same week... Sounded like a good time to do a blog about it.

As was pointed out in the comments below, the original article came from another blog. Brad Schulz did the original article on this topic and you can read his blog for the complete details. Trust me if you love SQL technology you will love that article. It is very well laid out and explains everything in depth that only a true SQL geek can love (like me!).

Take a look at this SQL:

create table ordertest 
(
  col1 varchar(900) not null
 ,col2 char(1) not null
);

-- STEP 1 insert some data
insert ordertest values ('B'+replicate('.',899),'N');
insert ordertest values ('R'+replicate('.',899),'A');
insert ordertest values ('A'+replicate('.',899),'T');
insert ordertest values ('D'+replicate('.',899),'U');
insert ordertest values ('S'+replicate('.',899),'R');
insert ordertest values ('C'+replicate('.',899),'A');
insert ordertest values ('H'+replicate('.',899),'L');
insert ordertest values ('U'+replicate('.',899),'O');
insert ordertest values ('L'+replicate('.',899),'R');
insert ordertest values ('Z'+replicate('.',899),'D');
select * from ordertest;

-- STEP 2 insert two more values
insert ordertest values ('X','X');
insert ordertest values ('Y','Y');
select * from ordertest;

What do you think the first and second select statements look like? The first is probably what you expected, the second probably is not!

First Select statement:

   1:  col1      col2

   2:  --------- ----

   3:  B........ N

   4:  R........ A

   5:  A........ T

   6:  D........ U

   7:  S........ R

   8:  C........ A

   9:  H........ L

  10:  U........ O

  11:  L........ R

  12:  Z........ D

Second select statement:

   1:  col1      col2

   2:  --------- ----

   3:  B........ N

   4:  R........ A

   5:  A........ T

   6:  D........ U

   7:  S........ R

   8:  C........ A

   9:  H........ L

  10:  U........ O

  11:  X         X

  12:  Y         Y

  13:  L........ R

  14:  Z........ D

Did you see that one coming? Records 11 and 12 got inserted apparently out of order in SQL Server. But really there is a perfectly logical reason why. SQL Server uses 8 Kb pages, this results in 8060 bytes available in the page for row data. Since the 901 bytes per record took up 7208 bytes, there is 852 bytes left in the first page. Not enough for the next entry, but enough for the two smaller entries.

The col1 + col2 overflowed what could be fit on the page after the 10th record, so the next two inserts went to the next page. When the second set of inserts was run they first looked at the first page of the database and saw there was free space left, so they used it. This makes SQL Server inserts non deterministic (unless you happen to know how many pages are in use and how much free space is available). You can read the full SQL Server books online entry here.

And if you really want to understand the internals of SQL Server there is a fantastic MS Press book on Microsoft SQL Server 2008 Internals. We have multiple copies of it and I have read my copy cover to cover more than once (probably only person on planet to have done so).

VistaDB is deterministic for inserts

Now what about in VistaDB?

   1:  col1      col2

   2:  --------- ----

   3:  B........ N

   4:  R........ A

   5:  A........ T

   6:  D........ U

   7:  S........ R

   8:  C........ A

   9:  H........ L

  10:  U........ O

  11:  L........ R

  12:  Z........ D

  13:  X         X

  14:  Y         Y

So how does VistaDB insert rows? VistaDB does always append rows at the end of the table. This is for a couple of reasons. The major reason is because we want to leave a little extra space per page, because of the way we update rows. In order to be ACID compliant we don’t edit the actual row, we create a new row and then unlink the old row. So we do attempt to do edits in row, but if they don’t fit off they go to the end of the natural order.

Clustered Indexes to the (SQL Server) rescue

Now the normal “fix” in SQL Server is to add a clustered index to the table. This will ensure that the inserts are actually stored on disk in the order of the clustered index, and therefore your select *’s will always be deterministic in ordering. The reality is that building the clustered index actually reorders the data on disk, VistaDB does not support clustered indexes for this reason.

We cannot reorder the data at a per insert basis. It would be terrible performance to have to split pages every insert to ensure they are all kept in order. SQL Server does this through a lazy write and reorder ability that we simply do not have being in process with your application.

I learned something today (a good thing)

I thought that creating a primary key index made it the default for all natural order select statements (I think SQL Server uses this behavior if the PK is clustered; which it is by default). VistaDB does not use the primary key index by default, I think this may be a behavior that needs to be changed.

You can create an index (and make it a primary key if you want) on the rows, and we will then keep the index in the order you desire, but there is no natural use of that index through SQL. In DDA you could set the active index to the newly created index and you would in effect walk the records in that index order, but this is because DDA is really a scrollable cursor that ADO.NET does not support. You have to apply an order by clause in the select in order to use the created index rather than the natural order.

This actually leads to me to believe that some of the optimizer code may be less efficient than I originally thought. I think some of the assumptions in the optimizer are that a Primary Key being present means we don’t have to explicitly use the index, it is the natural one. But this is clearly not the case in my testing today. I will work on some usage scenarios for it over the weekend and see what I can come up with for the usage.

Using the Primary Key index as the natural order when present

This may or may not turn out to be a good idea. I need to think through the ramifications of doing so, as it is subtle in the changes it will result in through things like joins. At the minimum I know we need to make that select * with a Primary Key order by applied faster, it has to be fast because so many operations rely on the primary key.

Learn .Net Programming Resources

js_vistadb — Fri, 09 Oct 2009 13:31:00 -0400

There are a lot of areas to learn in .Net programming. I don’t know any programmer who actually knows all of them, the namespace is just too big and complex. I know programmers who specialize only in one small area of the .Net framework and still tell me they learn new things all the time.

Now imagine the range of questions we get here at VistaDB. How do I attach x to a y using z namespace? Quite often we have no idea. We know how Ado.net works inside and out, we understand a lot of things, but that does not mean we know how to do exactly what you are attempting. And as much as I really enjoy teaching and learning we don’t have the time to go do research for people and give them a one on one training session for their specific need.

I wanted to list a few resources that I find invaluable in my day to day programming, and a few sites for those just getting started or just moving to a new area of .Net.

Just Getting Started, or want to dig deeper?

ASP.Net

For Asp.net developers take a look at the official Microsoft site dedicated to Asp.net. There is a whole section on Asp.net Tutorials that includes excellent video segments, walk throughs, and much more.

Winforms and Windows Presentation Foundation (WPF)

Desktop application typically use Winforms for their user interface, but the newer WPF is catching on with developers. Microsoft has a WindowsClient.net site dedicated to all things Winform. There is again an entire Winform help and tutorials area with videos and walk throughs.

Silverlight

Even though you cannot load VistaDB into a Silverlight client side, you can certainly use VistaDB on the server side and communicate to it using Silverlight (we even have a Todo sample showing how to do that). Microsoft again provides a great Silverlight website with a Learning Silverlight area.

Need more advanced help?

StackOverflow is your friend

Seriously, since StackOverflow was introduced I rarely bother asking questions anywhere else. If I have a programming problem I usually go here first looking for someone else who has already asked the question.

Stackoverflow is unique in that anyone can ask questions, and anyone can respond (you get to give back to others). This is a great practice and I often spend a few hours a week trying to help others (yea, in my copious amounts of free time). You can also vote on questions and responses, so there is a very social aspect to the site as well.

LearnVisualStudio.Net

LearnVisualStudio.Net is a great resource for learning about specific technologies. The format is entirely video based, but includes fantastic in depth tutorials on specific technologies. When I was first learning Visual Studio this was the best site for me. It does cost a small amount per year to maintain your subscription, but I have always found it to be well worth the small investment.

VistaDB Community Forums

Our own VistaDB Community Forums also is a great resource for asking questions of fellow programmers. Many of the development staff also read and respond here regularly. Many of the community have been using VistaDB for several years and are extremely helpful in SQL and programming tips. It is a fantastic resource for everyone who uses VistaDB.

Looking specifically for Windows 7 features?

MSDev.com has a great series of videos and tutorials that help managed programmers take advantage of new features in Windows 7. This is obviously a little more than just getting started with .Net development, but Windows 7 may be a big deal for your application.

Just the beginning…

This list is literally the tip of the iceberg for learning things in Visual Studio, but hopefully it will help get you started or to continue your learning. Every programmer gets stuck and needs help from time to time. Hopefully these resources will help!

VistaDB 4 Trial now Available

js_vistadb — Wed, 07 Oct 2009 00:59:00 -0400

The first VistaDB 4 trial is now available. You have to provide a valid email address in order to receive the trial.

Request Trial Email

Signup for the trial online

Professional Limits on all Trials

All trials are of the VistaDB Professional license. Once you download the trial you enter the same email address you used for the trial signup in order to get your temporary license. If your email bounces or is from a temp email service the trial will be blocked.

Also keep in mind that the limits of the Professional are not the same as all other licenses. See the online comparison page for pagesize limits for more information. If you plan on using VistaDB Lite, don’t use the Visual Studio tools or build with pagesizes larger than 1kb.

Unlimited Installs for single trial email

You may install the trial on as many machines as you need from a single email address. Keep in mind this is not how it works with a full license, each developer machine must be licensed to compile applications that use VistaDB.

30 day trial limits

The 30 day trial will stop running 30 days after the initial installation. All applications built with the trial will also stop running at the same time. These limits are not present in normal full licenses.

Your VistaDB applications do not ever expire when built with a full license. The only expiration is on support and maintenance updates. You are free to continue using whatever versions you have on your current machine if your support contract expires. There is no such thing as your application expiring at 3 am, unless you are running the trial.

The number of trial days remaining is displayed in the Data Builder about box, and the main screen when it first loads. In addition the Visual Studio tools also display a dialog once per day reminding you about trial status.

Getting to know VistaDB 4

js_vistadb — Mon, 05 Oct 2009 20:23:32 -0400

VistaDB 4 includes a number of improvements to SQL syntax, engine enhancements, and many API changes.

The VistaDB Edition Differences page should help explain most of the changes in the versions of VistaDB that are available.

Data Builder 4 includes syntax highlighting

Data Builder now uses the fantastic Actipro Software ActiPro SyntaxEditor control for syntax and colorhighting in the SQL Editor dialog (and some others).

This change provides a much more user friendly SQL Editor with support for collapsible regions, find and replace, goto line, block comment and uncomment, and many more features.

Our customization of the syntax editor includes colors to match closely what users are already familiar with in SQL Management Studio, and includes new hot key support for things like ctrl-e for execution.

Works with Windows 7

VistaDB 4 has passed all the Windows 7 compatibility tests from Microsoft! SQL Scripts are now kept beside the target database by default (same directory), but you can override this directory as well. The default for all scripts now uses the Public Document Library in Windows 7 to avoid UAC issues for users.

We currently run Windows 7 64 bit internally on most developer workstations, and test the 32 bit version in Virtual Machines. We have found that Windows 7 performs very well with VistaDB 4.

AssemblyVersion vs FileVersion

VistaDB 4 uses a new schema for versioning the numbers of the assemblies similar to how Microsoft versions the system assemblies for .Net. The AssemblyVersion attribute will stay 4.0.0.0 for all builds that implement the interfaces to be compatible with 4.0. The individual builds have a FileVersion attribute set that includes their specific build (4.0.6.1).

The list of VistaDB Assemblies in the GAC shows that the 3.x line included the build number. This would often cause problems with applications built against one version, but that now need to run under another (ORM and other third party tools).

Looking in the GAC you will find an entry for VistaDB.4 that shows 4.0.0.0. That is to avoid the whole binding redirect problem of the 3.x line. This also means you can only have a single 4.0 assembly in your GAC at a time (avoiding build binding problems). If you want to see the specific FileVersion of the assembly you can right click the entry, select properties and then view the Version tab. It should look something like the entry shown.

Entity Framework Supported

VistaDB 4 introduces support for the Entity Framework (EF). The Entity Framework can greatly simplify writing Data Access Layers (DALs) for your managed applications.

By using the EF you also gain the ability to use LINQ to Entities to query your VistaDB database. The LINQ syntax is an incredibly powerful way to write queries against a database in your .Net language of choice.

The initial release of our EF provider is definitely nascent technology – it is still under development. We plan to continue to improve the support and features of the EF provider over the lifetime of the VistaDB 4 product.

SQL Server Compatibility Changes for CLR Procs and Functions

We have added a new VistaDB.Compatibility class to make it easier to transition SQL Server CLR Procs to VistaDB (and back again). We have renamed the enumeration members and contexts to match SQL Server naming more closely.

Now through only changing the using statements at the top of your CLR Proc you should be able to run in both database engines.

ClrProcedures now use the [SqlProcedure] and [SqlFunction] attributes exactly like SQL Server. The one change that many will need to make is to use the System types in their clr procs rather than the SQL Data types. Using the system types (string, int, etc) ensure that the code is more portable.

Better Medium Trust support

VIstaDB 4 includes a number of enhancements for websites running under Medium Trust. Medium Trust compliance is a very difficult objective for something like a relational database. We have managed to allow all of our features (including temp tables, Sql procs, and Clr Procs) to run under Medium Trust. Users can even pack their database directly from Medium Trust in order to perform database maintenance without first needing to download the database.

New Installer based on Tarma

We have updated our installer to use the Tarma Installer. The installer is very easy to use, can be automated from command options, and provides us with great customization abilities for running things like the EF install system.

Tarma has been great to work with from a company standpoint as well. I have opened a few tickets during our migration from the previous installer and they have always been very responsive. Updates from Tarma are also often and painless to install. The product tells you that an upgrade is available and you can choose to download it.

Breaking Changes from 3.x

We have a revision philosophy that should minimize breaking changes within customer applications as much as possible between revisions.

VistaDB 4 is a major release because we had to make a number of breaking changes to the interfaces. Anytime you revise an interface it should be a major change in your product version.

We strive very hard to make sure minor versions are small changes in behavior or features. Any product built against a 4.0 should expect to run on all of the builds for that major.minor. When VistaDB 4.1 comes out you should expect some minor breaking changes, perhaps additional interfaces or overloads. We should not break any of the interfaces though until we release a 5.0.

This allows you to be confident that any build for 4.0 should be compatible within your application.

Upgrading from VistaDB 3

First, remember that you can run 3 and 4 in side by side mode. There is no reason to remove 3 in order to install 4. You can use the Visual Studio Designers for both without conflict. Visual Studio 2008 and higher are the only ones supported in 4.x, you can load the assemblies in Visual Studio 2005 if you don’t need any design time support and are working on a .Net 2 SP1 project.

Upgrading from VistaDB 3 to 4 should be considered a complete retest and revalidation of your product. It is, after all, a major change. Some of the interfaces have changed behaviors. The largest changes are in Direct Data Access (DDA) interfaces. We have encapsulated more of these interfaces to ensure that users cannot manipulate the internals of the objects. There are users who were forward casting the interfaces to their underlying types and modifying their contents, this should always result in an exception as it was not the intended design of the class. If you think you need access to the internals of an object there should always be a way to get that information through other means, not manipulating the object directly. In some cases this would lead to bad application behavior, and in others it would lead to database corruption (since the objects were not intended to be used that way).

License Changes

Make sure your application has a licenses. licx in the executable. The entire license system has changed in 4, so you may want to read through the help section on “VistaDB Licensing” to see how your project is effected. Every developer who compiles any part of the application must have a valid developer license on their machine. This was not enforced in 3.x, but it is now in 4. There is no such edition where only the “database guy” needed a license. The license is built into the executable on the machine it was built, so machines without licenses cannot build.

We will be offering build machine licenses for things like MSBUILD, or continuous integration machines at a reduced price. But they will not be able to be used on full developer machines – they are intended for automated compiles only.

ADO.NET is easiest transition

ADO.NET code should be the easiest to migrate from 3 to 4 as the interfaces are well defined. We have added new overloads in some cases, and updated some behaviors, but the majority of code should run unchanged.

Pagesize limits

See the blog post on upgrading VDB3 files for detailed information on migrating a VDB3 database to VDB4. The database may need to be changed as a result of the pagesize enforcement changes in VistaDB 4. You can view a list of the pagesize limits on the website.

Applications were being submitted to us all the time with complex requirements, but their pagesize set to 1. This led to a lot of wasted tech support time trying to discover problems that were due to incorrect structure of the data. In VistaDB 4 we have implemented these hard limits to help avoid those designs being implemented before the problems arise at runtime.

If you are attempting to upgrade a database and it fails, the most likely cause is that your pagesize is 1 and you have more entries than the limits allow. The other most likely cause is that you have invalid defaults for columns (DATE is a datatype now, not a function to get the current date – GETDATE() is that function in SQL Server).

DDA Interfaces changed

Many of the DDA interfaces had a property to let you get a list of the tables, indexes, etc. Those were not the actual tables, but a copy of the table list to be enumerated (not modified). Many of the interfaces were returning classes that could be forward cast to concrete implementations that were intended for internal use only.

As an example the list of default values are always contained in a IVistaDBDefaultValueInformation object that only exposes a few properties (ColumnName, Expression, UseInUpdate). Each of these are now read only in order to ensure the user knows that it is a read only collection of the values. Enumerating the list of default values is done from the IVistaDBDefaultValueCollection (which contains read only IVistaDBDefaultValueInformation objects).

You will see this pattern many times in the DDA interfaces, a collection of some objects that are information objects. This allows you to enumerate over the collections for each object, but many times you cannot modify them at that location. Look to the parent of the object. If you are modifying constraints, the table is probably the level that holds the routines to modify them. If you are adding columns, the TableSchema is the location to look.

Most of the changes in 3.x code should be fairly minor, unless your code was relying on bad behavior (like forward casting).

Read the changes through the blog

The complete history of the changes can be read by following through the BUILDS category on the blog.

Online Help files Updated

The VistaDB 4 Online Help Files have been updated to include the final shipping version of VistaDB 4. The entire VistaDB.net website has been updated to include the information for VistaDB 4.

VistaDB 4.0 Ships with Build 6

js_vistadb — Mon, 05 Oct 2009 19:47:00 -0400

Build #6

VistaDB 4.0 shipped October 2, 2009 as Build 6 with only minor changes from the previous release candidate.

See the detailed Getting to know VistaDB 4 post for more information on what has changed and is new in VistaDB 4.

New TODO Sample

Added TODO Sample N-TIER Application. The sample includes a simple TODO list application, with the presentations being totally abstracted from the actual data storage. The purpose of this sample is to demonstrate a well structured 3 tier application that uses a range of presentation technologies to the same underlying foundation using VistaDB as an embedded database.

Inside the sample you will find a WCF service, Business Logic Layer (BLL), Data Access Layer (DAL), and 3 different presentation layers (ASP.NET, WPF, and Silverlight 3). This sample is a much larger and more complex sample than we have presented previously. It demonstrates writing a well structured 3 tier application with VistaDB as the common data storage across multiple types of .Net applications.

ASP.NET, WPF, and Silverlight 3 are used as presentation layers (WPF and Silverlight require additional downloads as noted in the TODO Help file). Each of the presentation layers is represented in a separate solution with the other projects linked in from the common directory path.

Medium Trust

Fixed an issue with Web Applications, medium trust, and VistaDB running from the GAC.

Pack Database overload

Fixed an issue with pack called on a VistaDBConnection that was wrapped around a database opened via DDA. This is now an error since the VistaDBConnection does not know enough about the underlying database to pack. Pack should be called from DDA when the open was performed from DDA.

Trial Version will follow soon

The VistaDB 4 trial version is almost ready to ship, we are internally testing it right now. We also have to update the trial signup pages and followup emails to discuss the 4 version of the product.