Can ADODB connection, in VB.Net, create function, procedure, View, etc.?

Question

I have just downloaded a new version of SQL Server: my previous version was 2008; the new one is 2014, which I did because 2014 offers more functionality than my wildly outdated 2008 version. Copying the tables from the 2008 Server to the 2014 Server was easy enough. I simply went to Tasks and performed an import from the 2008 version of the database to the 2014, all of which occurred without a hitch.

The next stage was to copy the programmable objects (Views, Procedures, Functions, etc.), and this, too, is a fairly simple procedure. I went to the 2008 version, and used the "Script As..." / "Create To...". These Create features I then saved all of these in a folder (for simplicity's sake, I've called it "C:\ObjectFolder\", and then ran some VB.Net routines I wrote: ReturnFilesInFolder() lists the sequence of files, and ReturnFileContents() returns the contents of a specific file as a string. The purpose is to have an ADODB connection executing the text that produces the object in question, but in the 2014 version of my server. This leverages the work I'd already started and gives my 2014 server the same information I already built in 2008. The technique did not work, however, and I was getting conflicting error messages.

To solve the problem at hand, I ran the files I'd created on my 2014 server, and the process faithfully executed. I would now like to know what is considered a best practice around transferring programmatic objects from one server to another. If not using a ADODB connection, then what is the technique for automating a wholesale transfer? Thank you.

Sub Build_SQLObjects_2014()
    Const strObjectFolder As String = "C:\ObjectFolder\"

    Dim Conn_2014 As ADODB.Connection = Return_SQLServer_2014Connection()

    Dim strFiles() As String = ReturnFilesInFolder(strObjectFolder)

    Dim iCount As Integer

    For iCount = 0 To UBound(strFiles)
        Conn_2014.Execute(ReturnFileContents(strObjectFolder & strFiles(iCount)))
    Next
End Sub

Answer 1

A few things:

To transer from the older systm to the new one? Just create a backup on the older system, and restore it on the new one. That would transfer everything - indexes, views, store procs, everything. Not only is restoring a backup (.bak file) very fast, it ensures that everything from the older database is moved, and moved in one simple operation.

Next up: You can use ADO, but I would avoid doing so if you can. The main reason is those libraries are not only very old, but you miss out on using all of the newer .net objects.

In .net, you can/have to choose a provder. The 3 most common are:

oleDB provider - this works with say Access, and you can use sql server.  

ODBC provider     This again works with Access, and you can use sql server

SQL Provider.     most common and best for use with sql server

I would certainly choose the sql provider if possible.

Once you choose a provider, then the code that "follows" the connect and command objects is the SAME for all 3 providers.

Now, of course the issue here is that if you have a existing working application, and no doubt truckloads of ado code exist. And that being the case, the application may well have started out from VB6.

So, I will FULL admit that suggesting to change all that existing code, and known working code is obviously not such a great idea. If the code base is small, then I would dump ADO. However, if the code base is large, then only you can make the call if upgrading is worth it. I would if a small code base dump ADO if possible (it not managed code, and will be problematic over time - and it also past end of life anyway).

And the choice between the 3 providers? I would adopt the sql provider. This also tends to work a lot better if you do some asp.net + vb.net coding. If future plans exist to work with different servers? I actually suggest the ODBC providers!

The issue with ADO is you using and introducing a non managed dependency, and one that is not all that great to work with compared to the newer .net objects. This can cost you stability, but also box you in if you want to use a lot of the built in .net features (data grids etc.). Perhaps someone can comment, but do un-managed ADO objects even automatic cast to .net objects like combo boxes, data grids etc? I suspect they might due to migrating code from VB6 - but that was a long time ago.

And you also lose the ability to use the data-set designer. (or now the newer fancy version called "entity framework").

So the new "ado.net" objects really have ZERO to do with ADO, and you can choose any 3 of the above providers and THEN work with ado objects that work the same regardless of the provider you choose.

And this comes into play even larger for if you want to lift some code etc. for use with asp.net (web applications). You will lose the ability to use some of the .net controls by using ADO.

And keep in mind that ADO was depreciated quite a few years ago. In fact, the latest versions of SQL server state that they don't officially support oleDB connections. (they work, but are on life support - and ADO based on that technology).

You can certainly create views etc. using the ADO object model just like you did in the past. It just that you are using a non .net library but a windows un-managed library. Its not so much that it is "older", but it not really designed around the .net objects and models.

As noted, this may not be practical for you if you have lots of existing code. But if you have little or are considering to introduce and use ADO? Don't!

You find the learning curve to use sql provider really short - and you hardly miss a beat. You also are somewhat forced to do things the .net way and you get more disconnected objects as compared to ADO with less effort.

So ado.net is not really related to ADO. And you can use any provider with the built in ado .net objects. Calling it ado.net probably was not the best choice, but then again when .net appeared on the scene, a friendly name to not scare away developers was thus a good or ok choice.

I do strong recommend you avoid ADO as a choice here. Use the sql provider - it really kind of built in. And while I might get some flack? My next choice after sqlprovider is ODBC providers - it a open standard, not limited to windows, and has wide support - even on non windows systems.