Avatar of luca345
luca345
 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 ??
Visual Basic Classic

Avatar of undefined
Last Comment
luca345

8/22/2022 - Mon
Chris Raisin

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

Cheers
Chris
Chris Raisin

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?
Chris Raisin

Also, are you using Jet or ODBC in your VB6 Databases?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
luca345

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
Chris Raisin

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
Chris Raisin

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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Chris Raisin

Sorry I meant "OleDbConnection"
luca345

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
Chris Raisin

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 Const directive to the Pre-Processor
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Chris Raisin

Oops! Forget what I just said!

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

Stand By.....
luca345

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
Chris Raisin

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

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
luca345

ASKER
Dear Chris ,

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

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

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).   :-)
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
luca345

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
Chris Raisin

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
luca345

ASKER
Dear Chris ,

Here the error.

Marco
errror.jpg
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Chris Raisin

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
Chris Raisin

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.....
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Chris Raisin

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.
Chris Raisin

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

Chris Raisin

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".
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
luca345

ASKER
Dear Chris ,

I send you the entere test project (ModuloDatabase).

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

Marco



luca345

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
luca345

ASKER
After rename some error.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Chris Raisin

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....
Chris Raisin

Marco,

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

Its OK...I dont need it.

Cheers
Chris
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Chris Raisin

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

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
Chris Raisin

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

Your help has saved me hundreds of hours of internet surfing.
fblack61
luca345

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
Chris Raisin

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
luca345

ASKER
Dear Chris ,

OK, we see tomorrow.

Good night.

Marco
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Chris Raisin

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

Chris Raisin

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

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Chris Raisin

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


luca345

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
Chris Raisin

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

 
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Chris Raisin

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

Chris Raisin

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

luca345

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



I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
luca345

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
luca345

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
Chris Raisin

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
luca345

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
Chris Raisin

Chris Raisin

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
luca345

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
Chris Raisin

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
luca345

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



Chris Raisin

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
luca345

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
Chris Raisin

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

Stand By....

Chris
luca345

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
luca345

ASKER
Very good !!
luca345

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
Chris Raisin

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
luca345

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
Chris Raisin

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  :-)

luca345

ASKER
What problem you have ?? Fever ?

Marco
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Chris Raisin

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
luca345

ASKER
I feel sorry for this.

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

I will post today or tomorrow the next question.

Marco
Chris Raisin

Thx Marco

Cheers
Chris
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
luca345

ASKER
Dear Chris ,

This is the next question:

https://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_27260524.html

As in the past is not easy ...

Marco