Start Free Trial

asked on

Ado.net with VB.net

Hi ,

I need to migrate from VB6 Ado Recordset to VB.NET Ado.net.

I see that in Ado.net there are two types:

1) Oledb
2) SqlClient

Now I must convert differents large projects and I like to use the some modules.

In short I need a easy way to change on fly with only one set (if possible) these declares.

I have thinking 2 teorically ways to do it:

1) -- The best Solution ---

//
Use "Oledb"

In all project if I declare for example:

Dim CNN as new Connection (The compiler see 'new Connection' = 'new Oledb.Connection')

Use "SqlClient"

In all project if I declare for example:

Dim CNN as new Connection (The compiler see 'new Connection' = 'new SqlClient.Connection')

//

I hope is possible do it

1) -- The compromise Solution ---

//

Use "Oledb"

In all project if I declare for example:

Dim CNN as new My_Custom_Connection (The compiler see 'new My_Custom_Connection' = 'new Oledb.Connection')

Use "SqlClient"

In all project if I declare for example:

Dim CNN as new My_Custom_Connection (The compiler see 'new My_Custom_Connection' = 'new SqlClient.Connection')

//

I use all types of database Access , SQL Server , My SQL.

For this reason for me is very important switch easy these two types without 'replace all' that is not professional.

Can you help me ??

OK...I am looking into it, Marco.

Cheers
Chris

You only use SQLClient classes if your database is Microsoft SQL Server 2000.
Is that the case?

It is actually better to go with the classes contained in the namespace System.Data.OleDb since it was designed to work with any data source.

Which one do you want to use?

Also, are you using Jet or ODBC in your VB6 Databases?

ASKER

Dear Chris ,

Please foget VB6 for this question. Consider only VB.NET , ADO.NET and framework not else.

I need to use differents database (SQL server, Access , MYSQL) but I don't want replace in all routine:

Oledb to SqlClient or viceversa.

If for example I need change from Oledb to SqlClient, I like to do this with ONLY ONE LINE CODE if possible like change the value of the constant.

I don't want replace all declare to change it. Only one or few lines codes.

I' don't think is easy do it ... ---------> BUT I BELIVE IN YOU <----------

Marco

At the top of any class in which you are placing code put the following statement:

Imports System.Data.OldDb

The when you define a connection anywhere in that class module, make the definition:

Dim CNN As New OleDbConnection

See image below.

Cheers
Chris
OleDB.jpg

Aha...I think what you mean is you want to type in "My_Custom_Connection" but the compiler sees it as "P;dDbConnection", am I right?

Sorry I meant "OleDbConnection"

ASKER

Dear Cris ,

//
At the top of any class in which you are placing code put the following statement:
Imports System.Data.OldDb
//

My typical project have minimum 50/60 forms and 10/20 modules.

Teorically I must modify 60 forms and 20 modules, it is hard ...

I need I way that can be do this with much less modifications ... 2 / 3 lines for example.

Marco

If that is what you meant, use the #Const" pre-processor directive at the top of any class/module in which you are using the term "My_Custom_Connection"

#Const My_Custom_Connection = "New OleDbConnection"

The pre-processor will translate each occurrence of that coding in your class/module into "New OldDbConnection" before it starts compiling.

See this explanation and examples from the Help System for VB.Net

User generated image

Oops! Forget what I just said!

You can't use that to change the words in a Dim statement.

Stand By.....

ASKER

Dear Chis ,

This seem very interested ....

If you have time can you please send me an VB.net project example with 2 modules that do it ??

only 3/4 declare is sufficient.

Marco

I don't think we are in luck.

The Pre-Processor does not have "macros" like the "C#" compiler has.
Macros are where you can say sometjhing like:

#define My_Custom_Connection = "New OleDbConnection"

and the compiler will substitute the words in all modules it processes before the compiler runs.

Microsoft apparently made a concious effort NOT to use macros with the VB.Net compiler for some reason (just to make life hard for us, it seems).

I will keep investigating, but I don't like your chances... :-)

Stand By.....

ASKER

Dear Chris ,

My questions are generally not simply ... but where is the fun if is all easy ???

I think it will involve creating a class based on an Abstract Class, but it is tricky.....
It might take a while....stand by

It turns out that this MIGHT be what you are after (I am not sure).

Create a MODULE (NOT a class module) [e.g. Module1.vb] and in it place the following code:

Public MustInherit Class My_Custom_Connection
Inherits Data.Common.DbConnection

then press Enter.

Visual Studio will complete the module for you.

Than wherever you place code like the following "DIM" statement in the project everything should work as you want it. I do not have any code to test, but the intellisense worked and so I asume everything works OK.

Dim CNN as My_Customer_Connection
........
........

Ids this what you were after?

Cheers
Chris

(Another sleepless night....11.20am now so I am going to be to get LAST nights sleep). :-)

ASKER

Dear Chris ,

You must sleep .... this is very important !!!

Your code seem work in theory but the compiler show a warning and when I try to open the module the compiler show a fullscreen window about a problem like you can use a class inside module.

However you have understand perfectly what I need.

Marco

Cab you please send me the error message? I think the warning may be associated with the designer only (not the runtime program).
There is a problem with the designer trying to create an object from an Abstract Class.

I have a workaround for that (I think) if that is the problem.

Please submit some of your code plus a capture of the screen message that is affeting you.

Cheers
Chris

ASKER

Dear Chris ,

Here the error.

Marco
errror.jpg

Hi
http://msdn.microsoft.com/en-us/library/dw70f090(v=vs.80).aspx
http://msdn.microsoft.com/en-us/library/haa3afyz(v=vs.80).aspx
http://msdn.microsoft.com/en-us/library/ms971485.aspx
http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/3fc7f0c5-fcce-471a-86c6-708241ceb9cb/
http://support.microsoft.com/kb/310985/EN-US/

By the way, you can view the code in the module by right clicking on it in the explorer window (i.e. right-click Module1.vb) then select "View Code". That will take you straight there without the interface performing any actions on the code (obviously a double-click executes the code!)

As I said, I think this is a VS designer problem, and if we create an abstract class which inherits from Data.Common.DbConnection and then a "My_Customer_Connection" class which inherits from the Abstract Class, that may fix that problem. (They make it complicated, don't they?)

Anyway, I will just check first to see if that is indeed what troubles you at the moment.

Cheers
Chris

Interesting references (and handy, excuse the pun) a_hnd, but I am not sure that is what we are after here.

Thanks for the details, Marco, stand by.....

Yes, that is the error I mentioned.

You should still be able to proceed with your coding though. Check out the Intellisense and see if all works OK.

I will try to get the fix to stop that warning message from the Designer since it is annoying (although it should not stop you from proceeding with your coding.)

Can you send me a bit of your VB.Net code that includes the DIM message defining CNN:
e.g. Dim CNN as My_Customer_Connection

I am trying another approach at the moment,also.

I must have been half asleep when I wrote that code last night!

Let's try again:

Create a CLASS module [e.g. Class1.vb] and in it place the following code:

Public MustInherit Class My_Custom_Connection
Inherits Data.Common.DbConnection
(or if you already have the code written, place in it your exhisting code and alter the "Inherits" bit if required)

then press Enter.

Visual Studio will complete the class definition for you, if required.

Than wherever you place code like the following "DIM" statement in the project everything should work as you want it. Intellisense works and so I asume everything works OK.

Dim CNN as My_Customer_Connection
........
........

This time then code is "more correct" since the class is written in a class module (as it should be) although it does the same thing (and the designed error is being activated during design time).

Any warninga stem from the unfilled-in code or "unused" tokens in the "My_Customer_Connection" class.

Let me know how you are going while I try to get around this designer problem.

Cheers
Chris

By the way, after your have created "Class1.vb" rename it to "My_Customer_Connection.vb" by right-clicking in the Explorer and selecting "rename".

ASKER

Dear Chris ,

I send you the entere test project (ModuloDatabase).

www.pro-software.it/testVB.net.zip

Marco

ASKER

Dear Chris ,

Excuse me .. wrong read your message I have send you my code becouse I' am confuse ....

I try to rename with explorer.

Marco

ASKER

After rename some error.

As a matter of interest the following link details the fix for the "designer error" message we are getting.

http://www.startvbdotnet.com/oop/abstract.aspx

It is just a matter of me defining the Abstract class correctly.

Thanks for the code...

Stand By....

Marco,

You have an encryption password set on the files you just supplied, so I cannot unzip them. Any idea what the password is?

Its OK...I dont need it.

Cheers
Chris

Is that a solution for VB.Net or for VB6?

ASKER

Dear Chris ,

The project that I have sent you is small part of re-write VB6 code converted to VB.net.

You find only VB.net code.

I must convet many modules and form.

But before this , I need to find a solution to exchange easy SqlClient and OleDb and viceversa.

Marco

OK, I think I have it.

The code shows below.

The problem is a well-known one in the designer of Visual Studio, but Microsoft have not seen fit to fix this problem (there are MANY posts on the internet about it, manly in the C# or C++ arean, and thus little code I could work on).

Create a Class Module called "My_Custom_Connection.vb" and post the following code in it. Intellisense works when you define "Dim CNN as My_Custom_Connection" and should you not change any of the code in
"My_Cutom_Connection" it will inherit from the defined methods and properties etc. which are all "abstracted" from common.dbConnection.

Should you want to change anything that is handled by comman.dbConnection, by pressing the "Enter" key after typing in the line "inherits AbstractClass" the editor will layout all the definitions for you to "fill in the blanks". If nothing is to be altered, simply delete the added code (if you can understand what I mean).

Give that a go.....

Public MustInherit Class AbstractClass
    Protected MustOverride Function beinDBTransaction() As System.Data.Common.DbTransaction
    Public MustOverride Sub ChangeDatabase(databaseName As String)
    Public MustOverride Sub close()
    Public MustOverride Property Connectionstring As String
    Protected MustOverride Function CreateDbCommand() As System.Data.Common.DbCommand
    Public MustOverride ReadOnly Property Database As String
    Public MustOverride ReadOnly Property DataSource As String
    Public MustOverride Sub Open()
    Public MustOverride ReadOnly Property ServerVersion As String
    Public MustOverride ReadOnly Property State As System.Data.ConnectionState
End Class
Public MustInherit Class My_Custom_Connection
    Inherits AbstractClass
End Class

Open in new window

ASKER

Dear Chris ,

Please excuse me , I have poor experience about VB:net, I have some questions:

1) What is the difference beetween a 'normal' class and this 'AbstractClass'.

2) What type of modification I must do to switch from SqlClient to OleDB or viceversa ?

You have my VB.net source code and you known what need to do it, can please give me more precise instructions about my particular code ??

Do you suggest convert my 'ModuloDatabase.vb' to 'ClassDatabase.vb' ?

Please let me known.

Marco

Have a look at the article:
http://www.startvbdotnet.com/oop/abstract.aspx

It tells you a bit about abstract classes.

Abstract classes are really only an "extract" of the definitions of each function/attribute in a class. The code on how to perform actions or store/get data is in the actual class itself, not the abstract class. It is a little like the definitions in the "Interfaces", it is just DEFINING what parts of a class exist and what types data need to be passed to the class (or recived back from it).

It is late again so I will go to bed tonight early (2.30am). I am teaching in the mornming but will have a look at it tomorrow afternoon.

Cheers
Chris

ASKER

Dear Chris ,

OK, we see tomorrow.

Good night.

Marco

To switch from OLDDb to SqlClient would all be done in the "My_Custom_Connection" Class.

As I mentioned earlier, the VS interface will automatically expand the "My_Custom_Connection" class to include "stems" for each of the items in the "Abstract" class after you type in "inherits AbstractClass". This is (in effect) all the publicly available methods/properties of the Base class from which the Abstract is derived (i.e. System.Data.Common.DbConnection)

So the class will then look like that showing below.

You will need to change only the items:
ChangeDatabase
Connectionstring
CreateDbCommand (if required)

Perhaps the best plan of attack is to have a conditional preprocessor
command #if when you write your code.

e.g.
CONST SQL= True

# if SQL then
......(code)
......(code)
# else
......(code)
.....(code)
# endif

You can also then also compile from the command line:

e.g.
Use the /d switch to enter conditional compilation constants, as in the following example:
vbc MyProj.vb /d:SQL=–1 (-1 means True)

The neatest way of course is to place the value of TRUE or FALSE in the constant SQL via "View/Project Properties/Compile Tab/Advanced Compile Options"

This is what happens with C#, Marcus, and I am assuming the same for VB.Net, so you will have to experiment with this.

I do not have SQL server on my machine (I don't think) so it is hard for me to test but I will have a go at confitional compilation when I get a chance (I am not very well at the moment...must be the lack of sleep :-(

I will do some more work on this ASAP.....keep me posted with how things are at your end.

Cheers
Chris

In these ways you can change the value of "SQL" from TRUE to FALSE to produce
different versions of your runtime program.

You can also do this from
(I agther the useres will not be wanting to change from one to the other during runtime).

Public MustInherit Class My_Custom_Connection
    Inherits AbstractClass

    Protected Overrides Function beinDBTransaction() As System.Data.Common.DbTransaction

    End Function

    Public Overrides Sub ChangeDatabase(databaseName As String)

    End Sub

    Public Overrides Sub close()

    End Sub

    Public Overrides Property Connectionstring As String
        Get

        End Get
        Set(value As String)

        End Set
    End Property

    Protected Overrides Function CreateDbCommand() As System.Data.Common.DbCommand

    End Function

    Public Overrides ReadOnly Property Database As String
        Get

        End Get
    End Property

    Public Overrides ReadOnly Property DataSource As String
        Get

        End Get
    End Property

    Public Overrides Sub Open()

    End Sub

    Public Overrides ReadOnly Property ServerVersion As String
        Get

        End Get
    End Property

    Public Overrides ReadOnly Property State As System.Data.ConnectionState
        Get

        End Get
    End Property
End Class

Open in new window

Please ignore the comments after "Cheers Chris" in the last comment.
I was inserting a comment line and forgot I had some typing below
where I was typing....I then finished off the comment prematurely...sorry.

ASKER

Dear Chris ,

Please confirm if I have undestand:

In this sample you re-write the calls like

SqlClient.SqlConnection - OledDb.OleDbConnection
SqlClient.SqlDataReader - OledB.OledbDataReader
SqlClient.SqlDataAdapter - OledB.OledbDataAdapter

In short you use in your example:

dim CNN as Connection ( connection can be OleDbConnection or SqlConnection)
dim Reader as DataReader (DataReader can be SqlDataReader or OledbDataReader)
dim DataAdapt as DataAdapter (SqlDataAdapter or OledbDataAdapte)
....

and with only one set you switch from SqlClient to Oledb and viceversa and the new type can be visible in all the project ?

If it is true this is what I need. When you finish can you please send me a complete sample for only

'Connection' and 'DataReader' ?? if work I can complete my self the others calls.

Marco

You would have to place your conditional #if....#else....#endif coding in the
"My_Custom_Connection" Class coding. That is where you would code the
three lones you mentioned above. Of course you can even ADD functionality to the "My_Custom_Connection" class if you wish by writing your own functions/subs/properties.

As I said before, though, this works I think in C#.Net coding but I have yet to see if it works in VB.Net coding (I can't see why it wouldn't).

I will try a bit of testing code as soon as I get a chance and then confirm to you.
Meanwhile you could TRY putting conditional coding in the "My_Cust_Connection" Class code into the methods/properties:

#if SQL then
.........
.........
#else
.........
.........
#endif

Cheers
Chris

ASKER

Dear Chirs ,

I have try intellisense: #If the compiler full support this.

Probably becouse now VB.NET is more similar to C++/ C then in the past.

Your idea seem possible ...

Please follow this way ... seem the right choice.

Best Regards

Marco

It actually struck me that there is no need to use the #if structure.

Why not just use the normal IF...THE...ELSE test?

This would work just the same!

Public CONST DBTYPE = "SQL" 'Place this at top of first module (e.g. "Main.vb")

Then in Class "My_Custom_Connection" you could say:

Select Case DBTYPE
Case "SQL"
{some code}
Case "OLEDB_READER"
{different code}
Case "OLEDB_ADAPTER"
{again different code}
Case "OLEDB_CONNECTION"
{some more different code}
case else
{show an error message}
End Select

The differemce between the two is that, with this latter opetion
(i.e. NOT using #If....#else,,,,,,,#endif) ALL the code would be included in the final executable, whereas using the pre-processot option means that the code is smaller but you must produce different versions of your executable (One for SQL, one for OleDb, etc.)

With this second cjhoice, too, you could even have a "Setting" which can be changed in the Main menu of your application to change from one database type to another. The user would then select the type of database to be used.
(Of course you should include error trapping code in your "My_Custom_Connection" module in case they pick a data type connection not available on their machine/network).

Which method do you prefer?

Cheers
Chris

Pursuing the second option (NOT using the pre-processor), try this method of attack:

Your main module starts with something like this:

Module Main
Public ConnType As String
Private Sub Main()
ConnType = "SQL" '(change as required to OleDb/SQL)
'{MORE CODE}
End Sub
End Module

Then your class "My_Custom_Connection" is set as as showing below.

You would then simply state in your code:

Dim CNN as New My_Custom_Connection

and CNN would be a "common.dbConnection" object initialized via the
"New()" code called when you place "NEW" after the word "as" when
defining CNN.

Does that sound feasible?

Cheers
Chris

'This code goes in a class module named "My_Custom_Connection"

Public MustInherit Class AbstractClass
    Protected MustOverride Function beinDBTransaction() As System.Data.Common.DbTransaction
    Public MustOverride Sub ChangeDatabase(databaseName As String)
    Public MustOverride Sub close()
    Public MustOverride Property Connectionstring As String
    Protected MustOverride Function CreateDbCommand() As System.Data.Common.DbCommand
    Public MustOverride ReadOnly Property Database As String
    Public MustOverride ReadOnly Property DataSource As String
    Public MustOverride Sub Open()
    Public MustOverride ReadOnly Property ServerVersion As String
    Public MustOverride ReadOnly Property State As ystem.Data.ConnectionState
End Class
Public MustInherit Class My_Custom_Connection
    Inherits AbstractClass

    Public Sub New()
        Select Case ConnType    'the project's public variable "ConnType"
            Case "SQL"
                'put SQL connection code here 
                'associated with common.dbConnection
            Case "OleDb"
                'put OleDb connection code here
                'associated with common.dbConnection
            Case Else
                '{show an error message}
        End Select
    End Sub
End Class

Open in new window

Well!

It seems that what I said last would work fine if the function "New" in the "My_Custom_Connection" class ran when an instance is instantiated, but infortunately it does not. (Only the "New" in the Base Class runs).

And I can't use "Overrides" with the "New" function either (not allowed) nor can we say "Dim CNN as New My_Custom_Connection" since "New" cannot be used when calling an Abstract Class (or a class which inherits from an Abstract Class).

VERY frustrating.

Our problem is to get the code I currently have in the Class "My_Custom_Connection" under "New" to run every time you say:
"Dim CNN as My_New_Connection"

So lets forget all about the Abstract Class (sicne it is causing us problems).

Keep the Public "ConnType" at the top of the main module, but revert to only creating your class module "My_Custom_Connection" with the following code
syntax, and see if that helps in any way:

Dim CNN as New My_Custom_Connection

See if anything can be done using this approach.......If this fails I am not sure we can do it except via the pre-processor method (maybe!)

Cheers
Chris

Public Class My_Custom_Connection
    Public Sub New()
        Dim oConn As Common.DbConnection
        Select Case ConnType
            Case "SQL"
                '{You can remove the MessageBox code here since it
                'is used for testing purposes only). 
                MessageBox.Show("Connecting to SQL")
                '{put SQL connection code here using oConn Object} 
            Case "OleDb"
                '{You can remove the MessageBox code here since it
                'is used for testing purposes only). 
                MessageBox.Show("Connecting to OLEDB")
                '{put OleDb connection code here}using oConn Object)
            Case Else
                '{show an error message}
        End Select
    End Sub
End Class

Open in new window

ASKER

Dear Chris ,

Please excuse me for delay:

what is this : 'Dim oConn As Common.DbConnection'

How can I switch from OleDb to SqlClient ? Is possible ??

Best Regards

Marco

ASKER

Dear Chris ,

what is the difference beetween

//
Common.DbConnection
SQLClient.SqlConnection
OledB.OleDb.Connection
//

Common.DbConnection can allow me to switch from SQLClient.SqlConnection and OledB.OleDb.Connection ?

If this true can be interested ....

Marco

ASKER

Dear Chris ,

Probably the problem not exist , please confirm:

http://msdn.microsoft.com/en-us/library/system.data.common.dbconnection.aspx

Seem that with with: "Common.DbConnection"

The compiler switch automatically the best way to menage the database:

If for example I use SqlServer the compiler automatically choose 'SQLClient'

If I use Accees the compiler choose 'OleDb.Connection'

I Use Oracle the compiler choose 'OracleClient'.

Do you confirm it ???

If it is true I don't need any IF or similar I must only replace all with 'Common.DbConnection'

In this case is the connection string that choose that type of provider I need to use and the performance for SqlServer , Access or MySQL will not decrese.

Please let me known if I have understand

Marco

I am sorry, I cannot understand your question.

You wiull have to experiment to find out your answer. I think you are saying that is you use Common.dbConnection then you do not need the "IF" test.

I would not think thast was the case, but I could be wrong.

The problem I have is that I do not have Oracle. Basically all I have is Access
(AND maybe SQLClient, I am not sure).

I would not think that the computer would decide which one to use unless
something associated with "Common" and any database interatcion with it was already in place.

It is a pretty good guess that the "Connection string" would be the one that lets the computer know which connection to use. Try different connection scttrings and then try "CNN.OpenDatabase" on first (say) an SQL database then change the connection strong and try "CNN.Opendatabase" on an Access database.

If both work successfully, then I weould think the only code you need to place in the "IF" test on connType is the changes in the ConnectionStrings.

The "oConn" is the connection object you work wioth internally withinin "My_Custom_Connection" to perform actions on the database. It may NOT be necessary, I don't know. It was just an idea, since I am not aware of what code you are using in your "My_Custom_Connection" class.

Could you advise the code you are now using in your "My_Custom_Connection"
class ?

Many thanks
Chris

ASKER

Dear Chris ,

Probably is better that I tell you what are the reasons why I need this.

I think OleDb, SqlClient work good with all database types but these is difference of performance and supported properties.

Teorically I can do a test with this code:

//
Public Class My_Custom_Connection
Public Sub New()
Dim oConn As Common.DbConnection
Select Case ConnType
Case "SQL"
'{You can remove the MessageBox code here since it
'is used for testing purposes only).
MessageBox.Show("Connecting to SQL")
'{put SQL connection code here using oConn Object}
Case "OleDb"
'{You can remove the MessageBox code here since it
'is used for testing purposes only).
MessageBox.Show("Connecting to OLEDB")
'{put OleDb connection code here}using oConn Object)
Case Else
'{show an error message}
End Select
End Sub
End Class
//

But if for example this code work , how can I do to undestand if :

Common.DbConnection offer the some performance of SqlClient.SqlConnection with SQL Server ??

Many forums affirm that SqlClient offer better performance then OleDb but for Access is better use OleDb.

Now before migrate all my VB6 projects to .NET I want find a UNIQUE solution that offer the best actual performance and I don't want do a step back ....

I have no doubt that 'Common.DbConnection' work fine with all database but what are the performance with Sql Server and Access ??

With VB6 AdoDB this problem don't exsist becouse is the connection string that chooses.

Now with Ado.Net the situation is more complicated becouse there are too many classes that for my opinion, cause only confusion .....

If I choose one way: Common.DbConnection , Custom class or other , I need to be sure 100% that it is the best way to operate becouse if now I choose the wrong solution I must replace all future projects ....

I hope you have undestand my point of view ...

Please let me known

Marco

marco,

I am not really an expert on performance issues between databases.
As it is I really only have Access (and MAYBE SQL-server) on my PC.
From what I read Oracle databases seem to be best performance.

All I can do is give you references which may help you decide:

Details of SQL-Server:
http://en.wikipedia.org/wiki/Microsoft_SQL_Server
http://www.daniweb.com/software-development/vbnet/threads/84306

Details of ADODB:
http://en.wikipedia.org/wiki/ADOdb#Supported_Database_Systems

MSAccess:
http://www.startvbdotnet.com/ado/msaccess.aspx

SQLServer:
http://www.startvbdotnet.com/ado/msaccess.aspx
http://www.daniweb.com/software-development/vbnet/threads/84306

Discussions:
https://www.experts-exchange.com/questions/20985045/ODBC-Vs-OLEDB-Vs-ADO-Vs-ADO-NET.html
http://stackoverflow.com/questions/1005509/what-database-should-i-use-in-this-vb-net-app
https://www.experts-exchange.com/questions/22896384/Best-Database-for-Vb-Net-Application.html

I suggest you set the connection string for each of the different databases, then select one as your default (the one you think is the best performer) and assign it to the public Variable: "ConnType"

Cheers
Chris

ASKER

Dear Chris ,

//
I suggest you set the connection string for each of the different databases, then select one as your default (the one you think is the best performer) and assign it to the public Variable: "ConnType"
//

The problem is not only the 'connection' Object but in VB.NET there are many others classes that change:

Here only same samples:

//
- OleDb.OleDbDataAdapter <----> SqlClient.SqlDataAdaptor
- OleDb.OleDbDataReader <----> SqlClient.SqlDbDataReader
- OleDb.OleDbCommand <----> SqlClient.SqlDbCommand
//

If you multiply all these database class the modifications are not few ...

Now if is possible create new types (Connection,DataAdaptor, DataReader,Command) like:

//
Dim CNN as Connection (can be OleDbConnection or SqlConnection)
Dim Adaptor as DataAdaptor (can be SqlDataAdaptor or OleDbDataAdapter )
Dim Reader as DataReader (can be OleDbDataReader or SqlDbDataReader)
Dim Comm as Command (can be OleDbCommand or SqlDbCommand )
//

The problem is solved .... for your opinion is possible do it ??

Best Regards

Marco

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Dear Chris ,

//
If you use the approach with "My_Custom_Connection"
"My_Custom_Adaptor"
"My_Custom_Reader"
"My_Custom_Command"
//

To do it I must create 4 files (class) ??

Apart this

//
'{You can remove the MessageBox code here since it
'is used for testing purposes only).
MessageBox.Show("Connecting to SQL")
//

What code I must add here ? I must copy the code of my functions and duplicate it ?

Or this is only a simply assign like A = B ?

Frankly I don't understand the solution that you suggest.

Marco

You must place the code from your functions that connect to the database, datareader, run commands etc. They would then REPLACE your existing functions.

My solution is to allow for someone to change from one database type to another without re-writing the code.

If you do not intend to change databases and only wish to select the most efficient database and then code for it, you really only simply need to run your exisitng code from within the "My_Custom_Connection" (again replacing your existing code.

Do I have your exisitng VB.Net code to operate on the database (e.g. assign the connection string, open the database, close the database, etc.)?

I will be away for a day, but should be back on board in 24 hours.

Cheers
Chris

ASKER

Dear Chris ,

//
Do I have your exisitng VB.Net code to operate on the database (e.g. assign the connection string, open the database, close the database, etc.)?
//

You have already downloaded my VB.net code I think. If no I can re-send you.

Frankly I searching for a solution that need to replace in my actual code only the name of type example:

SqlClient.SqlDataAdaptor <---- change to -----> MyDataAdaptor without 'select case'
........

the 'select case' eventually I like to insert only in to new class/module created only for this purpose.

I have read one week ago somethink like 'alias' type that seem interested.

http://pietschsoft.com/post/2007/11/26/NET-How-to-Alias-Namespaces-and-Data-Types.aspx

A possible way to follow eventually can be 'abstract class' + 'alias'.

Please let me known if this is can be a solution or not ...

As you known I have very poor experience in VB.net ... only VB6.

Marco

I am very ill at the moment (flu I think) but I am looking into this avenue .

Stand By....

Chris

ASKER

Dear Chris,

I have find another better solution:

I have created a 'closed' class that don't return any object but only values.

I this mode I can forget DataAdapter , DataReader , Transaction , etc...

I will create a DLL of this class.

If for example in the future micosoft will create a new Ado (Ado.Enterprise) with new techology I must only create a new DLL and replace the old ... and the project still work and this is THE MOST IMPORTANT THING.

I think this solution is the best possible ....

Howver I will 'accept as solution' to get you 500 points.

I have a question:

Marco

ASKER

Very good !!

ASKER

I have a question:

I must converted from VB6 to VB.net the printer object.

All my projects use a 'A4' picture for print preview and 'printer' ojbect to print.

Do you have experience about this ??

I will post another question ....

Marco

Your solution will work fine and is probably the neatest. However, the ALIASES idea was a very good one.

You just needed to ay at the top of each module:

includes My_Custom_Connection = My_Custom_SQL_Connection

or

includes My_Custom_Connection = My_Custom_ADO_Connection

etc. then write each seperate Connection class.

That way changing the "includes" statement would change the type of database used in the solution. You can use a global "Replace" then each time you want to produce a new soltion for a different database type
( e.g. Replace all "My_Custom_ADO_Connection"
with "My_Custom_SQL_Connection")

Have you posted the other question yet?

Either way weorks

ASKER

Dear Chris,

I don't have posted yet the new question becouse first I like to undestand if you have experience about the 'printer object' in VB6 and relative way to convert it in VB.net.

I see there are many differences about this point in VB.net.

Have you already covered this topic before ??

Marco

I can help you with the printer object in VB.Net.

Just post your question and I will answer it.

Cheers
Chris

P.S. I went to the doctor today and am on antibiotics - feeling a bit better :-)

ASKER

What problem you have ?? Fever ?

Marco

Yes. Vomiting and terrible cough...all my ribs are aching and head cold, fever then chills.

I have taken the first course of antibiotics and feel better already!

Anyway.

I have a solution for your printer question so just please post it.

Cheers
Chris

ASKER

I feel sorry for this.

I hope that the cure take effect soon ....

I will post today or tomorrow the next question.

Marco

Thx Marco

Cheers
Chris

ASKER

Dear Chris ,

This is the next question:

https://www.experts-exchange.com/questions/27260524/VB6-Printer-object-conversion-to-VB-NET.html

As in the past is not easy ...

Marco