Ok, VistaDB 4.1 has been released as Build 16.  We had posted a Build 15 last night for testing, but it was never intended to go live.  So we had to bump the build number today to the final build number of 16.

There are a number of changes to this release, this post is about release notes.  Upgrade paths will be in a separate post (in general login to your account and look at the Upgrades panel to see what your options are).

Release Notes

License system has been removed from the current codebase.  There is no need for licenses.licx files.  But the assembly has been bumped to 4.1 as a result of the change in the interfaces, you must rebind your application in most cases to use 4.1.

The .Net Provider name now reads "VistaDB 4 ADO.NET Provider for .Net".  This is to cover all VistaDB 4.x versions.  The binding is now 4.1.0.0, so make sure you update any provider factory versions in your applications.

Provider Name Change

The VistaDB Provider was named VistaDB.NET20 to indicate it was the .Net 2 version of the provider (back when we had a .Net 1.1 version).  But to better match the framework we have now changed it to System.Data.VistaDB. 

If you have strongly typed datasets you need to edit the xml using a text editor (the wizards do not allow this) to change the name in the Connections section from VistaDB.NET20 to System.Data.VistaDB.

Visual Studio 2010

Entire product is now built in Visual Studio 2010.  We still produce .Net 2 assemblies, but we are using VS 2010 as our main development IDE.

Obfuscation Change

We are no longer using the Obfuscation engine.  There were problems using the assembly under Mono, and with sporadic bugs in the product we could not get addressed by the vendor.  The obfuscation is no longer enabled by default.  We may use another one at some point in the future to compact the assembly, but for now it is full size.   This means slightly larger DLL and more RAM usage due to the namespaces.

Data Builder Changes

Data Builder is built against .Net 3.5 SP1 now because we have added LINQ query ability to the main form.  We have also removed the pulling of the RSS feed onto the main window (trying to remove all external dependencies in the code).

The load screen has changed to a list of the most recently used databases (double click to choose one).  You can change the number of recent item from the Tools | Options dialog.

LINQ Queries

There is a new item in the database tree view named LINQ.  These are for writing dynamic EF queries against your database.  Once you click the LINQ item you will see a new panel that shows a query partially constructed for you.  You must hit the Generate Model to build an EF model out of the current database.  Then you can write LINQ queries against it and run them in Data Builder. 

The idea here is to allow for rapid writing of LINQ queries against a database for quick tests.  You can write them in Visual Studio (to get intellisense) and then paste them in here as long as you name your data context object "context".

There is no intellisense support, but the resulting code can pretty much be copied and pasted into your C# code (there is no VB support, sorry).  The queries must be written against the temporary data context object named context.  If you use some other naming in your code then you must obviously update the name once you paste it into your code.

SQL Script Export

A lightweight SQL Script Export has been added to Data Builder to output schema to a SQL file.  It does not handle data, or stored procs, or anything else.  Just schema for the entire database.  It is not for use as an API, it is only for use internal to Data Builder.

Customer Experience Removed

The CEIP program has been removed.  It may be added back at a future date, but I wanted to make sure everything that talks externally is out of this version just in case.

More...

There is a new pane in the Data Builder for VistaDB 4.1.  The LINQ query pane is a very handy way to build and test LINQ queries against a simple Entity Framework model.  First understand that this pane is a work in progress.  It was designed to be an easy way to write LINQ queries (similar to LINQPad) for your VistaDB database.  The feature is of limited usefulness since the model cannot be customized at generation, or loaded from your own assembly.  But this is where we were with the feature when we had to ship, so here it is.  We have been using it a lot to help write LINQ queries, we hope you find it useful too.

The current implementation only supports C# (sorry VB guys, it was on the list).  You can right click on the Entity Model tree and Script the main query window with all your basic CRUD operations.

By default the dialog is grayed out until you generate a model.  The code shown will still be present once the model is generated.

More...

For anyone coming from a background using T-Sql as their primary query language, Linq to Entities can be quite daunting. I, for one, am a huge fan of the Entity Framework.  I consider it another step towards keeping data driven application developers inside the .Net framework. And LINQ is a perfect way for programmers to think about data queries.  Sql is another language for most developers to learn, and each database has slightly different syntax for operations.  LINQ and Entity Framework make it much easier for programmers to think in their native programming language while writing queries.

In this article I am going to be showcasing a few common Sql query patterns, and explaining their Linq to Entities (EF) equivalents.  This will hopefully show some of the power of LINQ to developers who have yet to start learning the LINQ syntax.

Single Column T-Sql query

First we are going to take a look at a simple T-Sql query that selects one column from a table named Employees. This query is used in a DataAdapter to fill a DataTable then bound to a WinForms GridView.

select Age from employees

The column “Age” is of type Int, so the query will return a list of all Age’s(int’s) from the employees table. This simple query can be edited in several ways to return different results to the naming of the Age context.

select AGE from employees
select employees.Age as NewAge from employees

More...

LINQ is fantastic for the ability to write queries that express intent much more clearly than the same SQL, or structured code.  One problem that I have run into though is handling NULL database values that are part of a group by statement. 

Grouping by ProductSKU

Grouping in LINQ allows you to return sets of data from a collection for a given key value.  The group by clause is what the key ends up being in the result set.  Lets take a grouping of the Products by the SKU.

from p in Products
    group p by p.ProductSKU

Enumerable IGrouping collection

This results from the group by are enumerable groups ( IGrouping<String, Product> ) with the String being the Key for the groups (the ProductSKU field from the table).  The typical way you walk through this result is a nested for loop.

var groups = from p in Products
    group p by p.ProductSKU;
    
foreach( var groupentry in groups )
{
    Console.WriteLine( "Group: {0}", groupentry.Key );
    
    foreach( var groupitem in groupentry )
    {
        Console.WriteLine("Product: {0}", groupitem.ProductSKU);
    }
}
    

I end up with a list that looks something like this:

Group: VDB4DBA
Product: VDB4DBA
Group: VDB4DMW
Product: VDB4DMW
Group: VDB4PARTNER
Product: VDB4PARTNER

This works, but not what I really wanted.  In this case the first four characters of the SKU are the same per product family (VDB4 for all VistaDB 4 SKUs).  I would like to be able to group by only those first four characters instead of the complete ProductSKU.  You can do this with the following code.

from p in Products
    group p by p.ProductSKU.Substring(0, 4)

What if there are NULL entries?

But what happens if there is a NULL entry in the ProductSKU?  You get a ConstraintException: The property cannot be set to a null value.

More...

In this article I will continue to explain how to build a dynamic LINQ to Entities compiler for any database provider that supports the Ado.net Entity Framework.  See part 1 of building a dynamic linq to entities compiler for background information. This part of the series will cover using the .Net CodeDom Compiler to dynamically execute LINQ queries against an EF model.

We are working on a dynamic LINQ query mechanism for the next major release of VistaDB.  Our goal is to provide a dynamic LINQ execution panel (like LinqPad does for Linq to Sql) in Data Builder.  Users will be able to write LINQ to Entities queries against the database without having to first build an EF model.  We include a default data context object that can be used to write the queries the same way they will appear in your code.

Allow users to write a LINQ query

The first step needed in the process of compiling the query is to allow the user to supply me with the database and query they wish to execute. In this example the user must return their result set to a var named query and use the name “context” for the EF model context.

More...

In this article I will explain how to build a dynamic LINQ to Entities compiler for any database provider that supports the Ado.net Entity Framework. Due to the wide range of technologies used this article, it will be broken up into two parts as listed below.

We are working on a dynamic linq query mechanism for the next major release of VistaDB.  Our goal is to provide a LinqPad type of environment in Data Builder for users to write LINQ queries against the database without having to first build an EF model.

Blog Article Sections

• Part I. How to use edmgen command line tool to generate an EF model.
• Part II. How to use CodeDom to dynamically compile a LINQ query. See blog post

Technologies Used

• Ado.net Entity Framework (EF) – EF is an Object Relational Mapping (ORM) technology from Microsoft that is built into the .Net framework 3.5 SP1 and higher.
• VistaDB 4 – Commercial embedded SQL database that supports EF
• edmgen Tool (.Net Framework) – Included in the .Net framework, this tool is used to generate the models used by the EF runtime.
• CodeDom.Compiler (.Net Framework) – CodeDom is also built into the .Net Framework and provides the way to dynamically compile code
• LINQ to Entities (.Net Framework) – This is the query mechanism against the EF runtime, it is how you ask questions of the EF model.

Part I. How to use the edmgen command line tool

There are several steps needed in the process of dynamically testing a LINQ to entities query, first of which being the EDMX model itself. Visual Studio has a great set of wizards built in to handle generating an ADO.NET data model.  These wizards handle creating the necessary files for the EF model, and adding the connection strings to the app.config.

These wizards are not available at runtime, and the model generation becomes slightly more complex. There is no API available to generate an EDMX but Microsoft does include a command line tool called edmgen which can be used to generate an EDMX from any database provider that supports Entity Framework. You can find the edmgen tool under the 3.5 and 4.0 .net framework folders (C:\Windows\Microsoft.NET\Framework\).

More...

There are some new operations in .Net 4 Entity Framework, this a quick example of each working with VistaDB 4 and Visual Studio 2010.  These changes were mostly made to bring LINQ to Entities inline with the other LINQ providers in .Net.  Of the list below I think that Single() was the one that most people were confused about because if you used it you would get weird errors that didn’t make a lot of sense.

For a complete list of LINQ to Entities operators visit the Supported and Unsupported LINQ Methods on MSDN.

Entity Framework in .Net 4

The operations I want to demonstrate are the new Contains(), Single(), SingleOrDefault(), and DefaultIfEmpty().

These operations are all new in .Net 4, and yes they work with VistaDB 4.  I started with a simple one table database called Feedback.  The only table has 3 columns:

• int FeedbackID
• NText FeedbackText
• DateTime FeedbackDate

I added a few small text entries, including “I like grapes'” to search against with the Contains() operator.

More...

Have you tried to use LINQ to query a database using Visual Studio?  It can be a frustrating experience of things that compile fail at runtime, and that edit / compile / test cycle can quickly lead to hours of lost time trying to get a single complex query to work correctly.

LINQPad is an editor for LINQ?

LINQPad is sort of like a Notepad, you can write and edit .linq files using it.  But that is where the Notepad similarity stops. You can execute your LINQ queries and see the results without having to run your application.  It is truly something you have to watch in order to believe how much more productive it can make your writing of LINQ.

How I found LINQPad

When I was first working on the VistaDB product store and account manager writing LINQ queries against my Entity Framework objects was incredibly frustrating.  Most of the documentation and samples I found was for Linq2Sql, which is similar syntax… But not the same. And worse, most of the syntax compiles fine, but blows up at runtime with cryptic error messages.

My typical dev and test cycle was around 5 minutes to compile the DAL / Site, login to the account, navigate to the correct page, and visit the yellow screen of death from asp.net.  It was not fun, so I started working in a stand alone tester app I built just for this purpose.  Write the query, compile, debug, step, step, read exception and try to decipher it.

I was reading a post on Stack Overflow about one of those cryptic errors when someone suggested to the poster they use LINQPad to test their queries first before putting them into their apps.  Wow, what a great idea!  Where was this tool?  (You can download it for free from Linqpad.net )

LINQPad to the rescue

LINQPad allows you to execute single LINQ commands against an existing EF model, or even to write dot net code in the editor and execute it like a little dynamic dot net environment.  The main application is free, but there is an auto-complete feature to the editor that you must pay in order to activate. Believe me it is worth it to pay for the license, you also support the author and show him the application is worth money.  The license is very inexpensive and well worth the price in order to get intellisense like behavior on your LINQ queries.

More...