Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Robert J. Bullock 386 posts 405 karma points
    Jul 26, 2009 @ 17:48
    Robert J. Bullock
    2

    VistaDB to SQL Server Utility

    I wrote a simple console application that will convert a VistaDB install to SQL Server. If anyone needs any help with it, let me know.

  • Sebastiaan Janssen 5060 posts 15522 karma points MVP admin hq
    Jul 26, 2009 @ 20:17
    Sebastiaan Janssen
    0

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

  • Peter Duncanson 430 posts 1360 karma points c-trib
    Aug 03, 2009 @ 14:50
    Peter Duncanson
    0

    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.

  • curlybub 133 posts 107 karma points
    Aug 13, 2009 @ 14:42
    curlybub
    0

    Hi,

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

  • Thomas Höhler 1237 posts 1709 karma points MVP
    Aug 14, 2009 @ 09:40
    Thomas Höhler
    1

    You can also look here

    Thomas

  • Daniel Lindstrom 454 posts 271 karma points
    Aug 27, 2009 @ 10:06
    Daniel Lindstrom
    0

    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.

     

  • Niels Hartvig 1951 posts 2391 karma points c-trib
    Aug 27, 2009 @ 10:10
    Niels Hartvig
    0

    @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.

  • Horst Sterr 171 posts 133 karma points
    Oct 26, 2009 @ 17:00
    Horst Sterr
    0

    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

  • Jeroen Breuer 4908 posts 12265 karma points MVP 5x admin c-trib
    Dec 14, 2009 @ 09:50
    Jeroen Breuer
    0

    Hi Robert,

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

    Thanks

  • Robert J. Bullock 386 posts 405 karma points
    Jan 04, 2010 @ 18:17
    Robert J. Bullock
    0

    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.

  • Robert J. Bullock 386 posts 405 karma points
    Jan 04, 2010 @ 18:29
    Robert J. Bullock
    1

    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;

                            }

                        }

                    }

                }

            }

        }

    }

  • Robert J. Bullock 386 posts 405 karma points
    Jan 04, 2010 @ 18:31
    Robert J. Bullock
    0

    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.


  • Robert J. Bullock 386 posts 405 karma points
    Jan 04, 2010 @ 19:14
    Robert J. Bullock
    2

    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

  • Jeroen Breuer 4908 posts 12265 karma points MVP 5x admin c-trib
    Jan 05, 2010 @ 15:40
    Jeroen Breuer
    0

    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.

  • Daniel Bardi 927 posts 2562 karma points
    Jan 10, 2010 @ 13:45
    Daniel Bardi
    0

    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.

  • Daniel Bardi 927 posts 2562 karma points
    Jan 10, 2010 @ 13:46
    Daniel Bardi
    1

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

  • Daniel Bardi 927 posts 2562 karma points
    Jan 10, 2010 @ 13:49
    Daniel Bardi
    0

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

  • Daniel Lindstrom 454 posts 271 karma points
    Jan 18, 2010 @ 00:18
    Daniel Lindstrom
    0

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

  • Jeroen Breuer 4908 posts 12265 karma points MVP 5x admin c-trib
    Jan 27, 2010 @ 13:01
    Jeroen Breuer
    0

    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?

  • Robert J. Bullock 386 posts 405 karma points
    Jan 27, 2010 @ 15:07
    Robert J. Bullock
    0

    Jeroen,

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

    Robert

  • Jeroen Breuer 4908 posts 12265 karma points MVP 5x admin c-trib
    Jan 27, 2010 @ 15:47
    Jeroen Breuer
    0

    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

  • Daniel Bardi 927 posts 2562 karma points
    May 08, 2010 @ 20:15
    Daniel Bardi
    0

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

    Happy transferring.

  • asavasamuel 2 posts 22 karma points
    Mar 03, 2013 @ 13:00
    asavasamuel
    0

    RobertJ. Bullock

     

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

Please Sign in or register to post replies

Write your reply to:

Draft