I'm interested in seeing it, where can I find this application?

This is very much a missing part of the puzzle with Umbraco. I just spend the weekend cutting and pasting from dev to live as I could not find an easy way of doing just this. It would be great if it could be part of the framework or VistaDB should be phased out to use SQLExpress in its stead as MSSQL seems to be the preferred DB on live sites.

Hi,

Can I have a copy of this application. Thank you. You can email it at [email protected]

You can also look here

Thomas

Agree with Peter here, this would be a most welcome tool. I often use VistaDB when I test new ideas or packages on a more or less temprorary local umbraco installations. Every now and then I find that I would like to transfer those sites to MS SQL server if the tests turn out well.

@Peter: Vistadb and SQL Express cannot be compared as vistadb is an embedded db, thus doesn't require anything to be installed on the server/host.

Hi Robert,
would be interested in your tool, as i have the need to migrate from vistadb to sql server.
I there a place for download?

Thank you
/horst

Hi Robert,

I'm also interessted. My email is j.breuer at digibiz dot com.

Thanks

Guys, for some reason I was not notified of your posts here... Didn't realize so many were looking for the tool. I'll post it on my server and provide a download link and instructions.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using VistaDB.Provider;
using System.Data.SqlClient;

namespace VistaDBToSQL
{

   class Program
{

        static string SqlConnectionString = "Data Source=YOURSERVER\\SQLEXPRESS;Initial Catalog=Umbraco;Trusted_Connection=Yes;";

        static string VDBConnectionString =  "Data Source=C:\\inetpub\\wwwroot\\data\\vistadb\\vistadb.vdb3";

        static VistaDBConnection vdbconn = null;

        static SqlConnection sqlconn = null;

        static string sqlUmbracoTables = "SELECT name FROM sys.Tables";      

        static void Main(string[] args)

        {

            try

            {

                vdbconn = new VistaDBConnection(VDBConnectionString);

                vdbconn.Open();

                sqlconn = new SqlConnection(SqlConnectionString);

                sqlconn.Open();

                try

                {

                    SqlDataReader myReader = null;

                    SqlCommand myCommand = new SqlCommand(sqlUmbracoTables, sqlconn);

                    myReader = myCommand.ExecuteReader();

                    while (myReader.Read())

                    {

                        Console.WriteLine(myReader["name"].ToString());

                        CopyData(myReader["name"].ToString());                       

                    }

                }

                catch (Exception e)

                {

                    Console.WriteLine(e.ToString());

                }                

            }

            catch (System.Exception e)

            {

                Console.WriteLine("EXCEPTION: " + e.ToString());

            }

            finally

            {
                if (sqlconn != null)

                {

                    sqlconn.Close();
                    sqlconn.Dispose();
                    sqlconn = null;

                }

                if (vdbconn != null)

                {

                    vdbconn.Close();
                    vdbconn.Dispose();
                    vdbconn = null;

                }

            }

        }

        private static bool CopyData(string tablename)

        {

            using (VistaDBCommand vdbCommand = new VistaDBCommand("Select * from " + tablename, vdbconn))

            {

                using (VistaDBDataReader vdbReader = vdbCommand.ExecuteReader())

                {

                    // SQLBULKCOPY

                    using (SqlBulkCopy bulkCopy =   new SqlBulkCopy(SqlConnectionString, SqlBulkCopyOptions.KeepIdentity))

                    {

                        bulkCopy.DestinationTableName = "dbo." + tablename;
                        bulkCopy.BulkCopyTimeout = 0;

                        try

                        {

                            bulkCopy.WriteToServer(vdbReader);

                            return true;

                        }

                        catch (System.Exception e)

                        {

                            Console.WriteLine("EXCEPTION: " + e.ToString());

                            return false;

                        }

                    }

                }

            }

        }

    }

}

Guys, there's the code. The SQL Server and VistaDB paths are hardcoded. Need to change your database connection string / paths where appropriate, then compile and run this as a console application. One other important note: the Umbraco database you're upgrading to should be an empty Umbraco database, i.e., the Umbraco schema but none of the default data that is normally inserted. You can created such a database by editing the Umbraco SQL install scripts but removing the insert statements.

Okay, here's a zip file with everything you need: the Visual Studio Console Application project file, which includes a SQL script which you must run against an EMPTY database to generate the tables you need. Also, don't forget the connection strings/paths are hard coded in the source code so you have to edit and build the project yourself.

http://www.avant-it.com/images/vistadbtosql.zip

Thank you for the code! I haven't had time to test it yet, but I'm definitely going to. You should make a project of this at http://our.umbraco.org/projects.

I took your console application a few steps further and created a WinForms application.

The VS2008 WinForms source is here: VistaDBtoSQL.zip ... Anyone can an enhance it if they want.. it's just a work in progress.

Let's me try that again... VS2008 WinForms source is here: VistaDBtoSQL.zip

When you run the winforms version of the VistaDBtoSQL..... simply create a new sql server database.. the app will generate the tables.

Please make this a project in the project area so that it doesn't get lost!

I can't get it to work 100% yet. I'm able to create the empty tables, but when I try to copy the data I get the following error: "Login failed for user '' ". I checked the connection string but it's correct (otherwise I also wouldn't be able to copy the empty tables). Does anybody know what I'm doing wrong and have other people succeeded using this tool?

Jeroen,

Do you have VistaDB Express installed on your system? Is there any indication if it's SQL or VistaDB rejecting the login?

Robert

Hi Robert,

I don't have VistaDB Express installed. The error occurs on the following line:

                    // SQLBULKCOPY
                    using (var bulkCopy = new SqlBulkCopy(sqlConn.ConnectionString, SqlBulkCopyOptions.KeepIdentity))
                    {
                        bulkCopy.DestinationTableName = string.Format("dbo.{0}", tablename);
                        bulkCopy.BulkCopyTimeout = 0;
                        try
                        {                            bulkCopy.WriteToServer(vdbReader);
                            this.backgroundWorker1.ReportProgress(progressPercent,
                                                                  "  - Records transferred successfully.");
                            return;
                        }
                        catch (Exception e)
                        {
                            this.backgroundWorker1.ReportProgress(progressPercent,
                                                                  string.Format("  *** Transfer failed: {0}", e.Message));
                            return;
                        }
     } 

So the problem probably happens when vdbReader is being called. Do I need VistaDB Express and which version (isn't the newest version paid?).

Thanks in advance

All problems are fixed.. I now have a project started here: http://our.umbraco.org/projects/vistadb-to-sql-server.

Happy transferring.

RobertJ. Bullock

This is a good LINQ provider for VistaDB.
https://www.kellermansoftware.com/p-47-net-data-access-layer.aspx

is working on a reply...