Cannot display ODBC login prompt - want to connect without DSN
John Brown
In ADO, I can do this
'VBScript
Dim cnn
Set cnn = CreateObject("ADODB.Connection")
cnn.ConnectionString = "{SQL Server}"
cnn.Properties("Prompt") = 2 'Prompt if needed, I think
cnn.Open
The SQL Server ODBC login box will appear and the user can then fill in
whatever information is needed: Windows or SQL Server authentication, server
name, etc.
I am trying to write a program that will save a connection string for any
ODBC database.
I cannot find the equivalent of 'cnn.Properties("Prompt") = 2' in ADO.Net. I
am using Visual Basic 2008 Express with the .NET Framework 3.5 SP1.
'Visual Basic .NET
Dim cnn as New OdbcConnection
cnn.ConnectionString = "{SQL Server}"
cnn.Open 'fails because connection string is incomplete
Questions:
1) How can I make the ODBC login box appear in an ADO.Net app, other than
falling back to ADO (or horror of horrors, calling the ODBC API directly)?
2) More generally, is there an equivalent to the
ADODB.Connection::Properties collection in ADO.Net?
Regards,
John Brown.
Mark Rae [MVP]
news:5F054111-768C-4FD8...@microsoft.com...
> 2) More generally, is there an equivalent to the
> ADODB.Connection::Properties collection in ADO.Net?
More pertinently, why on earth are you using ODBC...?
--
Mark Rae
ASP.NET MVP
http://www.markrae.net
John Brown
"Mark Rae [MVP]" wrote:
And what, pray tell, should I be using?
Mark Rae [MVP]
>> "John Brown" <John...@discussions.microsoft.com> wrote in message
>> news:5F054111-768C-4FD8...@microsoft.com...
>>
>> > 2) More generally, is there an equivalent to the
>> > ADODB.Connection::Properties collection in ADO.Net?
>>
>> More pertinently, why on earth are you using ODBC...?
>
> And what, pray tell, should I be using?
ODBC was superseded thirteen years ago by OleDb:
http://msdn.microsoft.com/en-us/library/ms810892.aspx
http://database.ittoolbox.com/documents/odbc-vs-oledb-18150
The very first version of the .NET Framework back in 2002 introduced native
.NET data providers to replace OleDb and didn't even include ODBC natively,
which was available as a separate add-on to support very old databases for
which there were no OleDb drivers.
These days, there are native .NET data providers for all major databases,
and these run rings round OleDb in terms of performance and manageability.
Generally speaking, there is no good reason for using ODBC if OleDb is
available, and no good reason for using OleDb if a native .NET data provider
is available.
Especially with SQL Server...
Scott M.
these providers are available in .NET.
Depending on the database you are using, you'll need a specific provider
with a specific connection string to match it. If you want the user to be
able to supply their user ID and password, that would be a simple matter of
providing a logon form and then take the data from that form and make it
part of the connection string.
For SQL Server, you'd have something similar to this:
Dim con As New System.Data.SqlClient.SqlConnection(connectionStringHere)
Try
con.Open
'use connection here
Catch e As Exception
'handle exceptions here
Finally
con.Close()
con.Dispose()
End Try
Various configurations of connection strings can be found here:
-Scott
"John Brown" <John...@discussions.microsoft.com> wrote in message
news:E7322825-2163-4B59...@microsoft.com...
John Brown
"Mark Rae [MVP]" wrote:
> "John Brown" <John...@discussions.microsoft.com> wrote in message
> news:E7322825-2163-4B59...@microsoft.com...
>
> >> "John Brown" <John...@discussions.microsoft.com> wrote in message
> >> news:5F054111-768C-4FD8...@microsoft.com...
> >>
> >> > 2) More generally, is there an equivalent to the
> >> > ADODB.Connection::Properties collection in ADO.Net?
> >>
> >> More pertinently, why on earth are you using ODBC...?
> >
> > And what, pray tell, should I be using?
>
> ODBC was superseded thirteen years ago by OleDb:
> http://msdn.microsoft.com/en-us/library/ms810892.aspx
> http://database.ittoolbox.com/documents/odbc-vs-oledb-18150
>
I knew about OleDB. Wasn't ADO built open OleDB as a way of shielding VB
programmers from the complexity of OleDB? The real reason I have been ODBC
with ADO is that I am lazy. I cannot remember the names of the providers in
which I am interested. I have to look them up each time.
In this particular case, my program will conect to an Oracle database, but I
don't have Oracle here. I'm too lazy to make a trip just so that I can run my
program (to convert an XML file into records in a table) against an Oracle
database. There does not seem to be a .NET provider for MS Access, and even
if there were, I would have to write different code depending on the database
that I was connecting to:
dim cnn as JetConnection 'or something like that
and change it to
dim cnn as OracleClientConnection
You haven't answered the original question. Can you try this one instead:
If I use the ADO.Net provider for OleDB, and I allow the user to select an
OleDB provider from a list of providers installed on his PC , will I be able
to display that provider's login box and let the user connect, and then save
the resulting ConnectionString to be re-used in the future?
<snip>
> These days, there are native .NET data providers for all major databases,
> and these run rings round OleDb in terms of performance and manageability.
>
I would certainly hope so, but to be honest, I have never seen Microsoft
release a new product that was more efficient than the previous one. Anyway,
I will take your word for it.
> Generally speaking, there is no good reason for using ODBC if OleDb is
> available, and no good reason for using OleDb if a native .NET data provider
> is available.
>
> Especially with SQL Server...
>
>
Your point is well taken, but as I said earlier, my app needs to work with 2
databases.
Regards,
John Brown.
Mark Rae [MVP]
> I knew about OleDB. Wasn't ADO built open OleDB as a way of shielding VB
> programmers from the complexity of OleDB?
??? Where on earth did you get that from...
As for the "complexity" of OleDb, you're joking, right...?
> The real reason I have been ODBC with ADO is that I am lazy. I cannot
> remember
> the names of the providers in which I am interested. I have to look them
> up each time.
In which case, I don't think I or anyone else can help you...
> In this particular case, my program will conect to an Oracle database, but
> I
> don't have Oracle here. I'm too lazy to make a trip just so that I can run
> my
> program (to convert an XML file into records in a table) against an Oracle
> database.
As above...
> There does not seem to be a .NET provider for [MS Access] Jet,
Indeed. As mentioned, that's when you use OleDb:
http://www.connectionstrings.com/access
http://www.connectionstrings.com/access-2007
> and even if there were, I would have to write different code depending on
> the database
> that I was connecting to:
Nope - that's what a DAL based on a factory pattern is for:
http://msdn.microsoft.com/en-us/magazine/cc163766.aspx#S5
http://www.microsoft.com/downloads/details.aspx?familyid=1643758B-2986-47F7-B529-3E41584B6CE5&displaylang=en
> You haven't answered the original question. Can you try this one instead:
>
> If I use the ADO.Net provider for OleDB, and I allow the user to select an
> OleDB provider from a list of providers installed on his PC , will I be
> able
> to display that provider's login box and let the user connect, and then
> save
> the resulting ConnectionString to be re-used in the future?
Yes.
> Your point is well taken, but as I said earlier, my app needs to work with
> 2
> databases.
See above...
My DAL works with SQL Server, SqlCe, Oracle, MySql and Jet (which some
people incorrectly refer to as Access), and can be dropped *completely*
unmodified into any WinForms or WebForms project. If I ever need to support
another RDBMS, it would be a simple matter of adding it to the factory
pattern.
John Brown
"Scott M." wrote:
>
> Depending on the database you are using, you'll need a specific provider
> with a specific connection string to match it.
This is what I am trying to avoid. I want to write *one* program that will
work with *two* databases (MS Access and Oracle) with *no* code changes
whatsoever. Even though I know exactly which databases my progam is expected
to work with, so I don't have to support an arbitrary unknown database, I
still want to be able to use the program with a database other than Access or
Oracle. If the OleDB provider has its own login dialog box, I would rather
display that one than make my own form to do the same thing.
With ODBC, this is quite simple. I can present a list of installed ODBC
drivers. When the user selects one, I just need to:
'VBScript
Dim cnn
Set cnn = CreateObject("ADODB.Connection")
cnn.ConnectionString = "Driver={" selectedDriver & "}"
cnn.Properties("Prompt") = 2
cnn.Open
If I call OleDBConnection::Open with an incomplete ConnectionString (just
the provider name) can I make the provider display a login dialog box?
Regards,
John Brown.
Scott M.
>
>
> "Scott M." wrote:
>
>>
>> Depending on the database you are using, you'll need a specific provider
>> with a specific connection string to match it.
>
> This is what I am trying to avoid. I want to write *one* program that will
> work with *two* databases (MS Access and Oracle) with *no* code changes
> whatsoever.
Well, think about what you just said. There will objviously have to be code
set up to accomodate the two possible database types, but there's no reason
you can use the DBProvider factory classes to accomodate either database
being used.
> Even though I know exactly which databases my progam is expected
> to work with, so I don't have to support an arbitrary unknown database, I
> still want to be able to use the program with a database other than Access
> or
> Oracle. If the OleDB provider has its own login dialog box, I would rather
> display that one than make my own form to do the same thing.
There are no "login boxes" for OleDB. As I said, if you want a user to
supply their credentials, you can create your own login form for them to
supply the information to you and you can inject those credentials into the
connection string.
>
> With ODBC, this is quite simple. I can present a list of installed ODBC
> drivers. When the user selects one, I just need to:
>
> 'VBScript
> Dim cnn
> Set cnn = CreateObject("ADODB.Connection")
> cnn.ConnectionString = "Driver={" selectedDriver & "}"
> cnn.Properties("Prompt") = 2
> cnn.Open
>
> If I call OleDBConnection::Open with an incomplete ConnectionString (just
> the provider name) can I make the provider display a login dialog box?
Again, no. the login box which you are talking about was provided to ODBC by
the Windows Operating System via the ODBC Managaer. Taking ODBC out of the
picture (which you should) means that the dialogs are gone too.
-Scott
Scott M.
-Scott
"Scott M." <s-...@nospam.nospam> wrote in message
news:u0I4Ru7P...@TK2MSFTNGP02.phx.gbl...
Scott M.
>
>
> "Mark Rae [MVP]" wrote:
>
>> "John Brown" <John...@discussions.microsoft.com> wrote in message
>> news:E7322825-2163-4B59...@microsoft.com...
>>
>> >> "John Brown" <John...@discussions.microsoft.com> wrote in message
>> >> news:5F054111-768C-4FD8...@microsoft.com...
>> >>
>> >> > 2) More generally, is there an equivalent to the
>> >> > ADODB.Connection::Properties collection in ADO.Net?
>> >>
>> >> More pertinently, why on earth are you using ODBC...?
>> >
>> > And what, pray tell, should I be using?
>>
>> ODBC was superseded thirteen years ago by OleDb:
>> http://msdn.microsoft.com/en-us/library/ms810892.aspx
>> http://database.ittoolbox.com/documents/odbc-vs-oledb-18150
>>
>
> I knew about OleDB. Wasn't ADO built open OleDB as a way of shielding VB
> programmers from the complexity of OleDB? The real reason I have been ODBC
> with ADO is that I am lazy. I cannot remember the names of the providers
> in
> which I am interested. I have to look them up each time.
The ActiveX Data Objects (ADO) are simply API's to the particular OleDB
Provider. They give a developer a common way to work with different
providers.
>
> In this particular case, my program will conect to an Oracle database, but
> I
> don't have Oracle here. I'm too lazy to make a trip just so that I can run
> my
> program (to convert an XML file into records in a table) against an Oracle
> database. There does not seem to be a .NET provider for MS Access, and
> even
> if there were, I would have to write different code depending on the
> database
> that I was connecting to:
>
> dim cnn as JetConnection 'or something like that
> and change it to
> dim cnn as OracleClientConnection
You're not quite correct here. It's much easier than you percieve it to be.
To utilizie the provider for Access just use the types in System.Data.OleDB
namepace (the OleDbConnection, OleDbCommand, etc.). Here's a link to get
the Oracle provider:
http://www.oracle.com/technology/tech/windows/odpnet/index.html.
You can absolutley create one set of code that will handle either database
being used and it's called creating a Database Provider Facotry. I've
posted a link in my other comment about doing it.
> You haven't answered the original question. Can you try this one instead:
>
> If I use the ADO.Net provider for OleDB, and I allow the user to select an
> OleDB provider from a list of providers installed on his PC , will I be
> able
> to display that provider's login box and let the user connect, and then
> save
> the resulting ConnectionString to be re-used in the future?
Again, OleDbProviders do not have a user interface. There is no such thing
as this login box that you are asking about.
What you've got to understand is that Open DataBase Connectivity was a
WINDOWS feature that allowed a developer to create a Data Source Name (DSN)
that was specific to a particular SYSTEM. To aid the developer, Microsoft
created a UI for creating these DSN's, wich was the ODBC Manager, available
via Control Panel. The problem with this approach was twofold...First, the
DSN's are machine-specific and need to be set up identically on each and
every machine that the program was to run on. Second, the whole ODBC
architecture was an extra layer built on top of the database driver that
actually was performing the work. This was like having a middle-man talk to
another middle-man to talk to your database. It made ODBC versitile, but
also slow.
Object Linking & Embedding DataBase (OLEDB) Providers replaced ODBC and
solved both those problems. When using OLEDB, there is no UI to create the
DSN to talk to the database driver. And that means there's no more DSN's
either. And that means you don't have to run around and set this stuff up
on each pc which will run the application. With OLEDB, your programming
code talks directly to the database provider, cutting out the extra middle
man and making the process much more streamlined, which results in superior
performance.
Because OLEDB Providers are "talked" to exclusively through your program's
code, there is no system "login" boxes or any other UI to get in the way.
You simply code your application to use a particular provider and that's
it.
You have mentioned that your application will make use of Access and Oracle
and want a coding solution that will accomodate both. That's not a problem.
DataBase Provider Factories are a relatively straight-forward way to solve
that issue. If you need the end user to supply their credentials, then just
prompt them for that informaiton and use it in your connection strings.
One piece of advice, and don't take this as an insult because it's truly not
meant that way....To work with .NET effectively, you've got to understand
that VBScript and many of the programming paradigms that came before it are
dead, as it relates to .NET. Forget VBScript, it 100% irrelevant to how VB
.NET is written and functions. Forget ODBC, it's archaic and doesn't
perform nearly as well as OleDb.
Good luck!
-Scott
John Brown
"Scott M." wrote:
>
> "John Brown" <John...@discussions.microsoft.com> wrote in message
> news:937278FB-2561-43F4...@microsoft.com...
> >
> >
> > "Scott M." wrote:
> >
> >>
> >> Depending on the database you are using, you'll need a specific provider
> >> with a specific connection string to match it.
> >
> > This is what I am trying to avoid. I want to write *one* program that will
> > work with *two* databases (MS Access and Oracle) with *no* code changes
> > whatsoever.
>
> Well, think about what you just said. There will objviously have to be code
> set up to accomodate the two possible database types,
Not necessarily. My app is not going to create the database or the tables.
It simply loads an XML files into two tables with no columns with exotic data
types. There is exactly *one* Access-specific SQL statement in there, but I
am going to get rid of it.
> but there's no reason
> you can use the DBProvider factory classes to accomodate either database
> being used.
>
> http://www.davidhayden.com/blog/dave/archive/2007/10/08/CreatingDataAccessLayerUsingDbProviderFactoriesDbProviderFactory.aspx
>
I have not read this thoroughly yet. It looks promising, even though it
seems that it still relies on pre-existing knowledge of the full connection
string.
>
<snip>
> There are no "login boxes" for OleDB. As I said, if you want a user to
> supply their credentials, you can create your own login form for them to
> supply the information to you and you can inject those credentials into the
> connection string.
>
So you're saying that if I wanted to write a report writing tool using
OleDB, which is supposed to work with just about any database that exists now
or may be developed in the future, I would have to display a dialog box
asking for the usual suspects (user name, password, database, server) and
another text box for extra connection parameters, because I hav no way to
discover them at run time.
My users, who may not even know what an OleDB connection string is, are now
supposed to look in the programmer's documentation for their database, where
they might never think of looking because they are not programmers, so that
they can use my fantastic reporting tool?
That does not look like progress to me, but maybe the DBProvider business
above solves the problem.
<snip>
> > If I call OleDBConnection::Open with an incomplete ConnectionString (just
> > the provider name) can I make the provider display a login dialog box?
>
> Again, no. the login box which you are talking about was provided to ODBC by
> the Windows Operating System via the ODBC Managaer. Taking ODBC out of the
> picture (which you should) means that the dialogs are gone too.
>
From the description of SQLDriverConnect in the ODBC API reference at
http://msdn.microsoft.com/en-us/library/ms715433%28VS.85%29.aspx
"Based on the value of DriverCompletion, the driver prompts the user for
connection information, such as the user ID and password, and connects to the
data source:
SQL_DRIVER_PROMPT: *** The driver displays a dialog box ***,
============================
using the values from the connection string and system information (if any)
as initial values. When the user exits the dialog box, the driver connects to
the data source. It also constructs a connection string from the value of the
DSN or DRIVER keyword in *InConnectionString and the information returned
from the dialog box. It places this connection string in the
*OutConnectionString buffer."
Even without this documentation, it is obvious that each ODBC driver *must*
export a function to do this so that the ODBC Driver Manager can call it when
required. Otherwise the Driver Manager would have to magically know all the
required and optional values that can be used to make a connection for maybe
dozens or hundreds of drivers, and then construct a dialog box at run time,
complete with tabs, checkboxes and what not.
Regards,
John Brown.
Scott M.
>
>
> "Scott M." wrote:
>
>>
>> "John Brown" <John...@discussions.microsoft.com> wrote in message
>> news:937278FB-2561-43F4...@microsoft.com...
>> >
>> >
>> > "Scott M." wrote:
>> >
>> >>
>> >> Depending on the database you are using, you'll need a specific
>> >> provider
>> >> with a specific connection string to match it.
>> >
>> > This is what I am trying to avoid. I want to write *one* program that
>> > will
>> > work with *two* databases (MS Access and Oracle) with *no* code changes
>> > whatsoever.
>>
>> Well, think about what you just said. There will objviously have to be
>> code
>> set up to accomodate the two possible database types,
>
> Not necessarily. My app is not going to create the database or the tables.
> It simply loads an XML files into two tables with no columns with exotic
> data
> types. There is exactly *one* Access-specific SQL statement in there, but
> I
> am going to get rid of it.
Yes necessarially. If you are going to possibly deal with two different
databases (regardless of what you intend to do with those databases), your
application must be prepared to connect to the two different databases.
There's no getting around that.
>
>> but there's no reason
>> you can use the DBProvider factory classes to accomodate either database
>> being used.
>>
>> http://www.davidhayden.com/blog/dave/archive/2007/10/08/CreatingDataAccessLayerUsingDbProviderFactoriesDbProviderFactory.aspx
>>
>
> I have not read this thoroughly yet. It looks promising, even though it
> seems that it still relies on pre-existing knowledge of the full
> connection
> string.
Well, you need to know the basics of the connection string and can populate
the unknowns at runtime with data from the client. If you want to connect
to Access, you must know how to do that. If you want to connect to Oracle,
you must know how to do that as well. But, we're just talking about the
connection string here. The beauty of the ADO .NET objects is that once
you've made your connection, you use the objects the same way.
I may be wrong, but I'm getting the impression that you belive you can (or
should be able to) code a database application that *may* need to connect to
one of two database types without knowing how to code or having to code that
functionality.
>
>>
>
> <snip>
>
>> There are no "login boxes" for OleDB. As I said, if you want a user to
>> supply their credentials, you can create your own login form for them to
>> supply the information to you and you can inject those credentials into
>> the
>> connection string.
>>
>
> So you're saying that if I wanted to write a report writing tool using
> OleDB, which is supposed to work with just about any database that exists
> now
> or may be developed in the future, I would have to display a dialog box
> asking for the usual suspects (user name, password, database, server) and
> another text box for extra connection parameters, because I hav no way to
> discover them at run time.
Well, if you don't know the details of the connections you need to make at
design-time, then when else would you get them but run-time? And if you
don't have the information and only the end user does, then who else can you
ask to supply the detials? You should only need to ask them for their user
name, password, and database.
>
> My users, who may not even know what an OleDB connection string is, are
> now
> supposed to look in the programmer's documentation for their database,
> where
> they might never think of looking because they are not programmers, so
> that
> they can use my fantastic reporting tool?
Your users don't need to even hear the words "connection string" or "OleDb",
but yes, shouldn't they know where their own data is and their own
credentials to access it?
>
> That does not look like progress to me, but maybe the DBProvider business
> above solves the problem.
The DBProvider Factory will only help you code one pattern that can work for
the two different databases, it's not going to help you figure out your
connection strings. Honestly, (and I may be missing something here), but I
am a bit dumbfounded that you have resistence to the idea that a user would
need to supply credentials to access a secured resource. You seemed to be
ok with the ODBC login dialog popping up to promt the user to fill in the
details, why are you having trouble with having them do the exact same
thing, but with a dialog that you create?
I'm not sure what your point is here. OleDb is NOT ODBC. Comparing the two
architectures for similarities is futile.
-Scott
John Brown
"Mark Rae [MVP]" wrote:
> "John Brown" <John...@discussions.microsoft.com> wrote in message
> news:197C6525-4154-4C0C...@microsoft.com...
>
> > I knew about OleDB. Wasn't ADO built open OleDB as a way of shielding VB
> > programmers from the complexity of OleDB?
>
> ??? Where on earth did you get that from...
From the ActiveX Data Objects Reference:
http://msdn.microsoft.com/en-us/library/ms678262(VS.85).aspx
ADO Introduction
"ADO provides a layer of abstraction between your client or middle-tier
application and the low-level OLE DB interfaces. ADO uses a small set of
Automation objects to provide a simple and efficient interface to OLE DB.
This interface makes ADO a good choice for developers in higher level
languages, such as Visual Basic and VBScript, who want to access data without
having to learn the intricacies of COM and OLE DB
>
There is always the risk of something being lost in translation when you
try to condense a paragraph into a single line, but what is the difference
between this paragraph and what I said?
> As for the "complexity" of OleDb, you're joking, right...?
>
Well, they said "intricacies" and I said "complexity". If you don't like
what they write in MSDN, you may want to take it up with Bill, but I believe
that they are talking about the difficulty of using OleDB in VB, and not
OleDB's feature set. I think that the problem is that OleDB API uses a lot
of pointers, but don't quote me. I don't have a reference for that.
>
<snip>
>
> > There does not seem to be a .NET provider for [MS Access] Jet,
>
> Indeed. As mentioned, that's when you use OleDb:
> http://www.connectionstrings.com/access
> http://www.connectionstrings.com/access-2007
>
>
> > and even if there were, I would have to write different code depending on
> > the database
> > that I was connecting to:
>
> Nope - that's what a DAL based on a factory pattern is for:
> http://msdn.microsoft.com/en-us/magazine/cc163766.aspx#S5
> http://www.microsoft.com/downloads/details.aspx?familyid=1643758B-2986-47F7-B529-3E41584B6CE5&displaylang=en
After a multitude of posts with much information that is completely
irrelevant to my simple and clearly-stated problem, we're finally getting
somewhere. I have not gone through it yet, but it seems to be a more
complicated and less general technique than the DBProvider class mentioned by
the other poster.
>
>
> > You haven't answered the original question. Can you try this one instead:
> >
> > If I use the ADO.Net provider for OleDB, and I allow the user to select an
> > OleDB provider from a list of providers installed on his PC , will I be
> > able
> > to display that provider's login box and let the user connect, and then
> > save
> > the resulting ConnectionString to be re-used in the future?
>
> Yes.
>
The other poster who has replied says that you can't. In particular, he says
that OleDB providers do not have their own login dialog box so I must supply
my own. Of course, I would rather agree with you.
>
> > Your point is well taken, but as I said earlier, my app needs to work with
> > 2
> > databases.
>
> See above...
> My DAL works with SQL Server, SqlCe, Oracle, MySql and Jet (which some
> people incorrectly refer to as Access), and can be dropped *completely*
> unmodified into any WinForms or WebForms project. If I ever need to support
> another RDBMS, it would be a simple matter of adding it to the factory
> pattern.
So you support several databases with the same code, but to add another, you
would have to make a small change. This will work for me, but it is not the
same as my program being able to connect to an arbitrary database without my
knowledge or permission.
Regards,
John Brown.
John Brown
"Scott M." wrote:
>
> "John Brown" <John...@discussions.microsoft.com> wrote in message
> news:C5F5D28B-2A07-45F7...@microsoft.com...
> >
> >
> > "Scott M." wrote:
> >
> >>
> >> "John Brown" <John...@discussions.microsoft.com> wrote in message
> >> news:937278FB-2561-43F4...@microsoft.com...
> >> >
> >> >
> >> > "Scott M." wrote:
> >> >
> >> >>
> >> >> Depending on the database you are using, you'll need a specific
> >> >> provider
> >> >> with a specific connection string to match it.
> >> >
> >> > This is what I am trying to avoid. I want to write *one* program that
> >> > will
> >> > work with *two* databases (MS Access and Oracle) with *no* code changes
> >> > whatsoever.
> >>
> >> Well, think about what you just said. There will objviously have to be
> >> code
> >> set up to accomodate the two possible database types,
> >
> > Not necessarily. My app is not going to create the database or the tables.
> > It simply loads an XML files into two tables with no columns with exotic
> > data
> > types. There is exactly *one* Access-specific SQL statement in there, but
> > I
> > am going to get rid of it.
>
> Yes necessarially.
No, not necessarily.
>If you are going to possibly deal with two different
> databases (regardless of what you intend to do with those databases), your
> application must be prepared to connect to the two different databases.
> There's no getting around that.
>
Please forget the .NET evangelism for a minute and concentrate on what I am
saying. Very early in the piece, I posted ADO code that could connect to any
database without knowing anything beforehand except the driver name. If you
have a generic API that will handle specific objects of different types, it
is wise to have ways of querying the objects to find out their capabilities,
ways to tell them to configure themselves, etc.
What you are telling me is that if I am writing a word processor and my app
wants to print, it should display a standard print dialog box, and if my
users want to use some printer-specific feature that I didn't think of, then
too bad. Fortunately it doesn't worklike that. The printer driver knows how
to configure the printer, so neither my app nor GDI needs to know what the
printer is capable of. However, ny GDI code to print would be exactly the
same, regardless of the printer. I do not consider this to be a bogus concept.
Now I understand. You are assuming that connecting to a database is just a
matter of supplying credentials. (By the way, you left out server). Even
supplying credentials can be tricky. Consider SQL Server, but remember that
my app has no built-in knowledge of SQL Server. I present a list box with the
names of all OleDB providers on your computer. You select the one for SQL
Server. I display my generic box to get your user name, password, database
and server. I Build my ConnectionString: Provider=whatever;Data Source=User
Id=johnbrown;Password=password;I
Problem: Your environment uses Windows Authentication.
Problem: Even if you leave the fields blank, you still cannot connect,
because I need to specify the keyword to turn on Windows authentication.
Problem: the program doesn't know that.
There are more problems, such as:
Problem: How do I specify the database? Sometimes, the keyword is "Initial
Catalog", sometimes it is "Database"; there may be others. There are endless
keywords. Some are necessary, some are optional. For example, maybe system
tables are hidden by default (SQL Anywhere), but now I need to see them. How
would I know how to make system tables visible?
You can connect to all sorts of databases, and things that are not really
databases, such as a SharePoint site, an MS Exchange Server, etc. A generic
dialog box will not work for all data sources.
> > My users, who may not even know what an OleDB connection string is, are
> > now
> > supposed to look in the programmer's documentation for their database,
> > where
> > they might never think of looking because they are not programmers, so
> > that
> > they can use my fantastic reporting tool?
>
> Your users don't need to even hear the words "connection string" or "OleDb",
> but yes, shouldn't they know where their own data is and their own
> credentials to access it?
>
It's not all about credentials. See above.
> >
> > That does not look like progress to me, but maybe the DBProvider business
> > above solves the problem.
>
> The DBProvider Factory will only help you code one pattern that can work for
> the two different databases, it's not going to help you figure out your
> connection strings. Honestly, (and I may be missing something here),
Yes, you certainly are, but I hope that my latest explanation clears things
up.
>but I
> am a bit dumbfounded that you have resistence to the idea that a user would
> need to supply credentials to access a secured resource.
I have not the slightest idea what you are talking about. I am resisting the
idea that with backward, "dead" technology, I could write a program to
connect to a database and execute a few inserts and selects without knowing
or caring (much) about the database,in the same way that a program can print
regardless of the printer, or send emails without knowing which email program
is installed. Now I have to care, because I can't connect unless I specify a
correct connection string.
> You seemed to be
> ok with the ODBC login dialog popping up to promt the user to fill in the
> details, why are you having trouble with having them do the exact same
> thing, but with a dialog that you create?
It's not all about credentials. You are telling me that if I want to write
an app that will work with any database (let's say any database that has a
certain minimum capability), then I would have to write a login dialog box
for each one. This is not practical. Anyway, I have not investigated
DBProvider yet.
I am saying that the ability to connect to a database without knowing the
details is not magic; it was provided by each ODBC driver. If OleDB providers
do not have this capability, then it is a backward step.
Regards,
John Brown.
John Brown
"Scott M." wrote:
<snip>
> Forget ODBC, it's archaic and doesn't
> perform nearly as well as OleDb.
>
> Good luck!
>
Why do you think that they have released a new ODBC version (3.8) for
Windows 7?
Regards,
John Brown.
Mark Rae [MVP]
>> Forget ODBC, it's archaic and doesn't
>> perform nearly as well as OleDb.
>
> Why do you think that they have released a new ODBC version (3.8) for
> Windows 7?
For databases which have neither a native .NET data provider nor an OleDb
provider.
There are still a few, but you aren't using any of them...
I suggest you stick with what you know even though the native .NET data
provider for SQL Server outperforms the ODBC driver by approximately a
factor of 20...
John Brown
"Mark Rae [MVP]" wrote:
> "John Brown" <John...@discussions.microsoft.com> wrote in message
> news:D1045989-6EFE-43B0...@microsoft.com...
>
> >> Forget ODBC, it's archaic and doesn't
> >> perform nearly as well as OleDb.
> >
> > Why do you think that they have released a new ODBC version (3.8) for
> > Windows 7?
>
> For databases which have neither a native .NET data provider nor an OleDb
> provider.
>
<snip>
They did not have to add new features, but they did, so as far as they are
concerned ODBC is not dead just yet.
Scott M.
Because they haven't actually dropped the entire technology, but for all
intents and purposes, there is no reason to use ODBC in new application
development unless no OleDbProvider exists but an ODBC driver does. This is
not the case most of the time.
-Scott
>
> Regards,
> John Brown.
Scott M.
> am
> saying. Very early in the piece, I posted ADO code that could connect to
> any
> database without knowing anything beforehand except the driver name. If
> you
> have a generic API that will handle specific objects of different types,
> it
> is wise to have ways of querying the objects to find out their
> capabilities,
> ways to tell them to configure themselves, etc.
>
> What you are telling me is that if I am writing a word processor and my
> app
> wants to print, it should display a standard print dialog box, and if my
> users want to use some printer-specific feature that I didn't think of,
> then
> too bad. Fortunately it doesn't worklike that. The printer driver knows
> how
> to configure the printer, so neither my app nor GDI needs to know what the
> printer is capable of. However, ny GDI code to print would be exactly the
> same, regardless of the printer. I do not consider this to be a bogus
> concept.
There's no evangelism going on here. I think you are presuming that ADO
.NET and OleDb must somehow work just as ADO and ODBC did, which they do
not. It's actually very simple, in order to connect to any data source, you
need a connection string and different data sources use different connection
strings. You are telling me that you'll need to connect to Access and
Oracle, so that means you need to have two different connection strings.
The best practice is to store connection strings in your app.config or
web.config file, so you'd need to set up the basic shell for both of theses
and fill in the missing pieces later. This isn't a big deal and I'm not
sure why you seem so hung up on it. You'll just have two connection strings
standing by and a simple "if" statement to determine which is necessary at
runtime.
There's nothing about this that is evangelistic or even an opinion. This is
how connections are made in ADO .NET with OleDb - - you need to forget about
ODBC, ADO, and VB Script. They are gone (and a long time ago).
> Now I understand. You are assuming that connecting to a database is just a
> matter of supplying credentials. (By the way, you left out server). Even
> supplying credentials can be tricky. Consider SQL Server, but remember
> that
> my app has no built-in knowledge of SQL Server. I present a list box with
> the
> names of all OleDB providers on your computer. You select the one for SQL
> Server. I display my generic box to get your user name, password, database
> and server. I Build my ConnectionString: Provider=whatever;Data
> Source=User
> Id=johnbrown;Password=password;I
First, I reject the scenario you have started out with. All you need from
the end user is their id, password, and database. You would not present
them with a list of OleDb Providers because they wouldn't understand what
that list represents. Since you've stated that you might need Access or
Oracle, you could just provide them with a radiobutton set with those two
choices.
>
> Problem: Your environment uses Windows Authentication.
This is less a programming issue and more of a configuration issue. But, if
you need windows authentication, you can certainly set up the connection
string to use it.
>
> Problem: Even if you leave the fields blank, you still cannot connect,
> because I need to specify the keyword to turn on Windows authentication.
>
> Problem: the program doesn't know that.
>
This is where I am bit confused. Are you saying that the user gets to tell
your program if Windows Authentication is being used? This is not something
that end users know and understand and hardly something that you'd ask them
to tell you. You should know how your users need to connect and code for
that.
> There are more problems, such as:
>
> Problem: How do I specify the database? Sometimes, the keyword is
> "Initial
> Catalog", sometimes it is "Database"; there may be others. There are
> endless
> keywords. Some are necessary, some are optional. For example, maybe system
> tables are hidden by default (SQL Anywhere), but now I need to see them.
> How
> would I know how to make system tables visible?
Solution, the SqlConnectionStringBuilder class. This is not the problem you
think it is.
>
> You can connect to all sorts of databases, and things that are not really
> databases, such as a SharePoint site, an MS Exchange Server, etc. A
> generic
> dialog box will not work for all data sources.
Your OP was that you need to code against Access or Oracle, now you are
saying it could be anything?
>
>> > My users, who may not even know what an OleDB connection string is, are
>> > now
>> > supposed to look in the programmer's documentation for their database,
>> > where
>> > they might never think of looking because they are not programmers, so
>> > that
>> > they can use my fantastic reporting tool?
>>
>> Your users don't need to even hear the words "connection string" or
>> "OleDb",
>> but yes, shouldn't they know where their own data is and their own
>> credentials to access it?
>>
>
> It's not all about credentials. See above.
It really is. There's a picture that I have obviously not been able to
convey to you, but you only need 3 pieces of info. from your users and it
need not be technical:
What is the Database/Data Source you want to connect to (a simple dropdown
list can handle this - no need to talk about providers).
What is your User Name?
What is your ID?
Based on the answer to question #1, your program uses the correct connection
string and fills in the gaps with #'s 2 and 3.
But, with ODBC you couldn't connect without using the proper DSN. We're
simply replacing your code's selection of a DSN with your code's selection
of a connection string. Just like your ODBC programs supplied a way for the
user to tell you who they are, your OleDb programs would do the same.
>
>
> > You seemed to be
>> ok with the ODBC login dialog popping up to promt the user to fill in the
>> details, why are you having trouble with having them do the exact same
>> thing, but with a dialog that you create?
>
> It's not all about credentials. You are telling me that if I want to write
> an app that will work with any database (let's say any database that has a
> certain minimum capability), then I would have to write a login dialog box
> for each one. This is not practical. Anyway, I have not investigated
> DBProvider yet.
What I am having trouble understanding is that it seems that you want the
user to tell the program (or you think that I'm saying that a user should
tell the program) explicit details about how to connect to the desired data
source. I'm not saying that, but it seems that you are and I don't
understand why.
-Scott
Scott M.
> After a multitude of posts with much information that is completely
> irrelevant to my simple and clearly-stated problem, we're finally getting
> somewhere. I have not gone through it yet, but it seems to be a more
> complicated and less general technique than the DBProvider class mentioned
> by
> the other poster.
>
This is exactly what I've been recommending to you all along. The "factory"
pattern is the same thing as the DBProvider solution I've been suggesting.
>
>>
>>
>> > You haven't answered the original question. Can you try this one
>> > instead:
>> >
>> > If I use the ADO.Net provider for OleDB, and I allow the user to select
>> > an
>> > OleDB provider from a list of providers installed on his PC , will I be
>> > able
>> > to display that provider's login box and let the user connect, and then
>> > save
>> > the resulting ConnectionString to be re-used in the future?
>>
>> Yes.
>>
>
> The other poster who has replied says that you can't. In particular, he
> says
> that OleDB providers do not have their own login dialog box so I must
> supply
> my own. Of course, I would rather agree with you.
As I stated, OleDb does not supply ANY form of UI. What I belive Mark was
saying "yes" to was the "could I save the connection string for later use"
part, which you can.
>
>>
>> > Your point is well taken, but as I said earlier, my app needs to work
>> > with
>> > 2
>> > databases.
>>
>> See above...
>> My DAL works with SQL Server, SqlCe, Oracle, MySql and Jet (which some
>> people incorrectly refer to as Access), and can be dropped *completely*
>> unmodified into any WinForms or WebForms project. If I ever need to
>> support
>> another RDBMS, it would be a simple matter of adding it to the factory
>> pattern.
>
> So you support several databases with the same code, but to add another,
> you
> would have to make a small change. This will work for me, but it is not
> the
> same as my program being able to connect to an arbitrary database without
> my
> knowledge or permission.
John, I really am trying to help you out here, but I've got to say that you
seem to be shooting down suggested courses of action, without really having
any knowledge or experience with what's being suggested. To accomplish what
you want is not at all complicated and does not put the burdens on your
end-user that you keep saying it will. Take it from people who have done
exactly what you are saying you want to do.
I wish you good luck with it, but I can't see a continued debate about the
correct course of action with someone who seems to want to shoot down
exactly the solution that they are seeking. If I haven't explained the
solution correctly, my apologies, but you can get just what you want with
the DB Factory paradigm (implemented via the DBProvider classes) and two
basic connection strings that are completed with user imput using the
ConnectionStringBuilder class.
Please take the time to research these items (links previously provided) and
you'll see that it's what you want. It may not have the built in login
boxes that you used to have, but the performance gains as well as the device
indepenence is well worth doing DSN-less connections, which were introduced
13 years ago.
-Scott
John Brown
"Scott M." wrote:
>
<snip>
>
> This is exactly what I've been recommending to you all along. The "factory"
> pattern is the same thing as the DBProvider solution I've been suggesting.
>
OK.
> >
> >>
> >>
> >> > You haven't answered the original question. Can you try this one
> >> > instead:
> >> >
> >> > If I use the ADO.Net provider for OleDB, and I allow the user to select
> >> > an
> >> > OleDB provider from a list of providers installed on his PC , will I be
> >> > able
> >> > to display that provider's login box and let the user connect, and then
> >> > save
> >> > the resulting ConnectionString to be re-used in the future?
> >>
> >> Yes.
> >>
> >
> > The other poster who has replied says that you can't. In particular, he
> > says
> > that OleDB providers do not have their own login dialog box so I must
> > supply
> > my own. Of course, I would rather agree with you.
>
> As I stated, OleDb does not supply ANY form of UI. What I belive Mark was
> saying "yes" to was the "could I save the connection string for later use"
> part, which you can.
Well, I didn't ask *part* of a question. I asked a whole question, and I am
sure that you cannot deny that I asked it very clearly and concisely, so if
he said "Yes", I can only assume that he must be have been responding to the
whole question.
>
> John, I really am trying to help you out here, but I've got to say that you
> seem to be shooting down suggested courses of action, without really having
> any knowledge or experience with what's being suggested.
I have not shot down anything. I already said that DBProvider looks
promising, but you're right. I won't have anything more to say on this
subject until I try it.
>
> ... you can get just what you want with
> the DB Factory paradigm (implemented via the DBProvider classes) and two
> basic connection strings that are completed with user imput using the
> ConnectionStringBuilder class.
>
The very name of this class fills me with ecstasy. This is of course,
exactly what I want to do.
> Please take the time to research these items (links previously provided) and
> you'll see that it's what you want.
I will.
> It may not have the built in login
> boxes that you used to have, but the performance gains as well as the device
> indepenence is well worth doing DSN-less connections, which were introduced
> 13 years ago.
>
Not to flog a dead horse, but DSN-less connections existed in ODBC 1.0
(Sep.1992, according to Wikipedia.)
Regards,
John Brown.
Scott M.
> Well, I didn't ask *part* of a question. I asked a whole question, and I
> am
> sure that you cannot deny that I asked it very clearly and concisely, so
> if
> he said "Yes", I can only assume that he must be have been responding to
> the
> whole question.
>
I can only assume he was respoding to *part* of the question because I'm
fairly confident that he does know that OleDb has no UI. So, if he knows
that, then his "yes" couldn't possibly be to answer both questions you
asked.
Just my take on it.
-Scott
Scott M.
> Not to flog a dead horse, but DSN-less connections existed in ODBC 1.0
> (Sep.1992, according to Wikipedia.)
>
Well, I wasn't an ODBC expert, but I doubt the accuracy of that statement.
By it's very definition, ODBC required DSN's. The DSN's were the middle-man
created by the ODBC manager. The DSN was the extra layer that is precisely
the thing that causes the performance bottlenecks. It was OleDB that freed
us from ODBC and thus, DSN's. In other words ODBC = DSN.
-Scott
John Brown
"Scott M." wrote:
You can trust me on this. The function in the ODBC API that provides this
capability is SQLDriverConnect. According to the ODBC API reference, this
function was introduced in ODBC 1.0. The only doubt is when ODBC 1.0 was
released, but it was certainly long before OleDB. *1992* I got from
Wikipedia, but the rest is coming straight from the horse's mouth. See:
http://msdn.microsoft.com/en-us/library/ms715433(VS.85).aspx
If you are able to compile C programs, I would be happy to write a small C
program to demonstrate connecting without a DSN using the ODBC API, but that
would be Wednesday.
Regards,
John Brown.
Scott M.
that mean that the DSN-Less connection was available only to SQL databases?
And, for non-SQL users, you still needed a DSN?
-Scott
"John Brown" <John...@discussions.microsoft.com> wrote in message
news:3295BC61-3C11-4454...@microsoft.com...
John Brown
"Scott M." wrote:
> Thanks for the offer, but I take your word for it. Question though: doesn't
> that mean that the DSN-Less connection was available only to SQL databases?
> And, for non-SQL users, you still needed a DSN?
>
> -Scott
>
No. Going back to my subject line, each ODBC driver contains a dialog that
allows you to configure it to make a connection. So all you have to do is
supply a minimum connection string, which can be a DSN or a driver name; a
totally empty connection string would not work.
/* incomplete code fragment */
char szOutConnectionString[256];
SQLSMALLINT lenReturnedString;
rc = SQLDriverConnect(hdbc, NULL, "Driver={Microsoft Access Driver
(*.mdb)}", SQL_NTS,
szOutConnectionString, 255,
&lenReturnedString, SQL_DRIVER_COMPLETE);
In this case, the MS Access driver will display a dialog box with all the
necessary and optional parameters that can be set when connecting to an
Access database. If you supply a connection string with all the required
keywords, the box will not appear at all. You will just connect. If the
string is incomplete, the dialog box will be filled in with whatever
information you supplied, and then you can fill in the rest, or change the
initial values.
I hard-coded the Access driver name, but I could have read a list of driver
names from the Registry. Whichever name I supply, I will get that driver's
login box. It does not matter whether it is Access, SQL Server, Oracle, or
CSV files. When the function returns, I will be connected and I will have the
*complete* connection string in szOutconnectionString.
Regards,
John Brown.
John Brown
method of displaying a dialog box prompt that will let a user connect to any
data source that has an ADO.NET provider. This program will work unmodified
with SuperDB 2012, even though their website is not up yet, and I have no
information about the connection string that I will need.
' sProviderName can be selected from a list box. Of course, we would
' display the friendly names, but use the object names internally
Dim dbpf as DBProviderFactory = GetFactory(sProviderName)
'Get a ConectionStringBuilder that will build a connection string for
sProviderName
Dim csb as DbConnectionStringBuilder = dbpf.ConnectionStringBuilder()
' Use a PropertyGrid contol on your form to display the properties
' of the connection string, which will correspond to the keywords in
' the connection string; user id, password, etc.
PropertyGrid1.SelectedObject = csb
' When you run your form, it will show a property grid that
' corresponds to the conection string for your database.
' After you show the form, you can create a generic connection object
Dim cnn as DbConnection = dbpf.CreateDbConnection()
cnn.ConnectionString = csb.ConnectionString
cnn.Open
' At this point, you will be connected to a database about which you knew
' nothing beforehand. You could have started with an empty
' config file, but you can save the whole string or any part of it in the
'config file.
Now, I will address the points that you raised.
"Scott M." wrote:
<snip>
> You are telling me that you'll need to connect to Access and
> Oracle, so that means you need to have two different connection strings.
This is the sequence of events, which I evidently did not make clear.
- I needed to connect to Access and Oracle with one program.
- The initial suggestion was that I should write:
If Access
DoThis
Else
DoThat
End If
- I was not interested, because I never had to do that before.
- So I asked myself "Does this mean that I cannot write what the ODBC
documentation calls a Generic application, i.e., one that 'must work with a
variety of different DBMSs and that the developer does not know beforehand
what these DBMSs will be.'"?
- The DbProviderFactory methods and generic Dbxxx classes were suggested,
but there was still a gap. In my opinion, such an application needs a way to
present a login box that is appropriate for the database. Yes, the user can
edit my config file, but if they wanted to edit config files, they would use
Linux.
- The PropertyGrid control was the final piece of the puzzle. It allows me
to present a GUI that will help the user to build a connection string.
>
>
> First, I reject the scenario you have started out with. All you need from
> the end user is their id, password, and database.
What you need depends entirely on what the developers of the database say
you need. You sent me to ConnectionStrings.com, so you know this. SuperDB's
connection string is not there, by the way.
> You would not present
> them with a list of OleDb Providers because they wouldn't understand what
> that list represents.
I am assuming that ADO.NET providers have friendly names like "Data Provider
for SuperDB" for display purposes, and a name like Data.SuperDB.Client for
use in programs. Also, we never defined "user". If you use my program, then
you are a user, even if you are an MVP. I also don't care who is using the
program.
<snip>
> >
> > Problem: Your environment uses Windows Authentication.
>
> This is less a programming issue and more of a configuration issue. But, if
> you need windows authentication, you can certainly set up the connection
> string to use it.
>
At this point, I don't know which authentication methods are supported by
SuperDB, and I no longer care, because I now have a way to present the
options to them. They can set up their environment however they like. I don't
need to know. Don't ask, don't tell.
> >
> > Problem: Even if you leave the fields blank, you still cannot connect,
> > because I need to specify the keyword to turn on Windows authentication.
> >
> > Problem: the program doesn't know that.
> >
>
> This is where I am bit confused. Are you saying that the user gets to tell
> your program if Windows Authentication is being used? This is not something
> that end users know and understand and hardly something that you'd ask them
> to tell you. You should know how your users need to connect and code for
> that.
Excel allows you to populate a spreadsheet with data from a database. You
can select a DSN, or you can create one on the fly. If you select the SQL
Server driver, it will present you with a login box with user name, password,
etc., as well as a "Use trusted connection" checkbox, which you can tick or
not. The Excel developers don't know whether your data is in SQL Server or
text files. They don't care whether you use WIndows or SQL Server
authentication. They also expect that Excel will be able to import data from
SuperDB when it is released, and they don't tell anybody that they need to
edit a file to connect to MS Access, SQL Server, or SuperDB.
>
> Solution, the SqlConnectionStringBuilder class. This is not the problem you
> think it is.
>
The solution is the generic ConnectionStringBuilder, along with a
PropertyGrid to allow the user to set the properties. It's useless without
the PropertyGrid. Or more accurately, I woud have to find out how the
PropertyGrid does what it does, but I have no reason to do that unless I was
trying to display a really elegant login box with tabs and what not.
>
> Your OP was that you need to code against Access or Oracle, now you are
> saying it could be anything?
Explained above. I *need* to support Access and Oracle, but I *want* to
support anything.
> >>
> >> The DBProvider Factory will only help you code one pattern that can work
> >> for
> >> the two different databases, it's not going to help you figure out your
> >> connection strings.
With the help of the PropertyGrid control, DbProviderFactory can certainly
help me figure out my connection string.
> > > You seemed to be
> >> ok with the ODBC login dialog popping up to promt the user to fill in the
> >> details, why are you having trouble with having them do the exact same
> >> thing, but with a dialog that you create?
Since SuperDB has not been released yet, I cannot possibly create my own
dialog. I don't know what will be required. You keep insisting that all you
need is a user id, password and database, but it simply is not true. I am
going ask SuperSoft to add a surprise required parameter for connection.
We'll see how far you get with a dialog box with fields just for user name,
password and database.
> What I am having trouble understanding is that it seems that you want the
> user to tell the program (or you think that I'm saying that a user should
> tell the program) explicit details about how to connect to the desired data
> source.
This is exactly what I want. Users of Excel, Access, Crystal Reports and a
host of other applications do this every day. The real mystery is why you
think that it is so strange.
But none of this matters anymore. The problem has been solved. I can do
using ADO.NET what I used to do using ODBC.
Regards,
John Brown.
Scott M.
I'm happy that you were able to get the solution that you desired.
At the risk of beating a dead horse, and really for others who might stumble
across this thread, I wanted to just address one point that was central to
our discussion.
As a best practice, it is not a good idea to assume anything about the user
of your application, or at least to assume a level of knowledge that you can
not reasonably expect the user would have. Given this, when creating
database connected applications, it is not reasonable to expect your end
user to know anything about their data other than their own credentials and
what data they want. I know that in your particular case, you wanted a
scenario where the program wouldn't have to know about the database, because
the user was going to supply all the details, but unless you are building an
application for DBA's, this approach goes against all best practices for
good useability and design. You mentioned several times, when I pointed out
that a user should only enter these 3 pieces of info., that that was
incorrect, and I must finish out this thread by saying that it is correct
and to expect a user to supply/know any more than this is really what's
considered incorrect.
This is very much like two of the core tennants of Object-Oriented Design,
which are the concept of encapsulation and abstraction - - or to think of
your object as a "black box" of code. Only the creator need know how the
box does what it does, but the user of the box, need not (and should not)
know anything at all about the inner workings of the box. They only need
know how to interface with the box. To expect a database user to know (and
input) the details of connection string parameters, not only puts
unreasonable pressure on the end users, but also exposes your application to
a multitude of security concerns as well as potential corruption to the
underlying data store because, in your design (hooking the
ConnectionStringBuilder up to a PropertyGrid for the end user to populate),
you are breaking encapsulation of your application.
The first thing a hacker tries to do when wanting to get into a system, is
try to enter invalid data into input fields to see what error messages are
returned. These error messages (if unfiltered and passed directly to the
UI) in a database application, can contain path information as well as
information about the security model and database schema itself. Armed with
this information, the hacker can hone the attack directly against a specific
resource within the system. Allowing the ConnectionStringBuilder to bind
directly to a PropertyGrid, so that the end user can put whatever they want
in whatever parameters are listed is very hard to justify in a production
environment because you have no input validation layer in the applicaiton.
I do understand your stated requirements: that the program be completely
ignorant of the data source being connected to and that the program be
compatible with any and all other data sources that may come down the line.
And, in a perfect, monitored world, that may work. I don't know enough
about the environment you'll be implementing your application to be able to
predict exactly the success or failure you will have with your chosen
approach. But, as I said, expecting the users to supply all the pertinent
details of the connection, for the sake of the program being able to connect
to anything at any time, is generally considered a poor design choice.
I belive (well, actually I know) that you could accomplish much the same
results by having the basic shell of the potentially needed connection
strings standing by and simply ask the user for the desired data source,
their user name, and their password. Yes, the program will have to know
more details about the potential data sources and how to connect to them and
yes, I understand that this was not what you wanted, but the reality is that
this would generally be the better choice for improved security, control
over scalability, and the end user's experience.
I honestly believe that your design requirements (program doesn't know
anything about the data source, usesr supplies all pertinent connection
info., no custom UI need be developed) are unrealistic for any mainstream
application.
Please understand, I'm not trying to insult you in any way. I just want
anyone else reading this to understand what the best practices are in this
situation and why.
Good luck,
Scott M.
"John Brown" <John...@discussions.microsoft.com> wrote in message
news:44A97E14-E15B-43C3...@microsoft.com...
John Brown
"Scott M." wrote:
<snip>
>
> As a best practice, it is not a good idea to assume anything about the user
> of your application, or at least to assume a level of knowledge that you can
> not reasonably expect the user would have.
I assume nothing. I just put the information out there. If they need help,
they can get it.
> You mentioned several times, when I pointed out
> that a user should only enter these 3 pieces of info., that that was
> incorrect, and I must finish out this thread by saying that it is correct
> and to expect a user to supply/know any more than this is really what's
> considered incorrect.
I misunderstood you. I thought that you meant that those pieces of
information would always be sufficient, not that I should write my app so
that they would always be sufficient. I can save the connection string, minus
the user id password, and database, but of course I have to make the initial
connection first.
<security concerns snipped, but noted />
<snip>
>
> I honestly believe that your design requirements (program doesn't know
> anything about the data source, usesr supplies all pertinent connection
> info., no custom UI need be developed) are unrealistic for any mainstream
> application.
Excel is not a mainstream application? This is what I suggest: convince
Bill. When he tells the Excel team to take this feature out, I also won't
bother with it anymore.
>
> Please understand, I'm not trying to insult you in any way. I just want
> anyone else reading this to understand what the best practices are in this
> situation and why.
>
If you wanted to insult me, you could go right ahead. I don't know you. Why
would I care?
Regards,
John Brown.
Scott M.
>> I honestly believe that your design requirements (program doesn't know
>> anything about the data source, usesr supplies all pertinent connection
>> info., no custom UI need be developed) are unrealistic for any mainstream
>> application.
>
> Excel is not a mainstream application? This is what I suggest: convince
> Bill. When he tells the Excel team to take this feature out, I also won't
> bother with it anymore.
Ah, but the feature, as you describe it, is not in Excel. When you tell
Excel to connect to a data source, it does exactly what I'm advocating and
not what you are suggesting.
It prompts for the type of data source, the log on credentials, and the
particular database data deired. It does not propmt for the connection
timeout, the buffer size, whether Multiple Active Result Sets should be
used, if the connection should be pooled, etc.
Excel does, in fact, need to know something about the available data sources
and how to connect to them. Well, to be more precise, the Windows component
that Excel uses to provide connectivity to various data sources does need to
know certain aspects of how to connect to those resources and it does not
ask the user to supply those data. Also, depending on the data source you
do select as a data source to connect to in Excel, you will get a different
dialog to complete, which makes my point that someone over there at MS had
to program various custom dialogs depending on the data source selected.
In effect, when MS created the connect to data source feature for Excel,
they followed the exact design pattern I've suggested for you:
1. The program does know something about the various types of data sources
you can connect to.
2. The program does put up custom dialogs, based on the data source chosen.
3. The user is not required to supply any connection configuration
parameters beyond:
a. The type of data source to connect to.
b. Windows vs. custom authentication with the user's credentials
c. The specific database/table to connect to.
-Scott
John Brown
"Scott M." wrote:
> > "Scott M." wrote:
> >> I honestly believe that your design requirements (program doesn't know
> >> anything about the data source, usesr supplies all pertinent connection
> >> info., no custom UI need be developed) are unrealistic for any mainstream
> >> application.
> >
> > Excel is not a mainstream application? This is what I suggest: convince
> > Bill. When he tells the Excel team to take this feature out, I also won't
> > bother with it anymore.
>
> Ah, but the feature, as you describe it, is not in Excel. When you tell
> Excel to connect to a data source, it does exactly what I'm advocating and
> not what you are suggesting.
>
> It prompts for the type of data source, the log on credentials, and the
> particular database data deired. It does not propmt for the connection
> timeout, the buffer size, whether Multiple Active Result Sets should be
> used, if the connection should be pooled, etc.
>
You can use Microsoft Query to connect to an ODBC data source, or you can
use their Data Connection Wizard to connect to an OLEDB data source.
If you use Microsoft Query, and select "New Connection" and then select "SQL
Server", you get a small login box with the berver, authentication method,
user name and password . You have to click "Options" to select database, and
language, application name and workstation name are also available.
Connection pooling is implemented by the ODBC Driver Manager, not the
driver, so if Excel is only displaying SQL Server driver properties, you
wouldn't see it here. However, it does seem that properties exposed by the
driver are hidden here. I am not sure that Excel is hiding them. It could be
that ODBC's on-the-fly connection mechanism hides "Advanced" properties. I
could find out by calling SQLDriverConnect, but I will assume for the sake of
this discussion that Excel is the culprit.
When you use OLEDB, you are presented with a list of categories, including
SQL Server and Other. If you choose SQL Server, then only the bare
necessities are presented, but if you choose Other, and then select the
OLEDB Provider for SQL Server from the list of all installed providers, all
the gory details are available. The important stuff is on the first tab, but
there is an "Advanced" tab with "Connection Timeout" and other properties,
and an "All" tab with many properties that are meaningless to your
hypothetical pathetic user.
This list may not be complete; I see an "Extended Properties" property,
which I assume is used to manually enter keywords that are not specified in
the property grid. However, 22 properties are avalable, which goes way beyond
user name, password, and database. Therefore, when Excel knows the data
source, it displays a minimal dialog box, but when it doesn't know, it puts
everything out there, and you can do what you want.
> Excel does, in fact, need to know something about the available data sources
> and how to connect to them.
No, it does not *need* to know, but if it does, it can use that knowledge.
> Well, to be more precise, the Windows component
> that Excel uses to provide connectivity to various data sources does need to
> know certain aspects of how to connect to those resources and it does not
> ask the user to supply those data.
It does not *need* to know, and if it doesn't, it will certainly ask you.
> Also, depending on the data source you
> do select as a data source to connect to in Excel, you will get a different
> dialog to complete, which makes my point that someone over there at MS had
> to program various custom dialogs depending on the data source selected.
>
When you know the data source, certainly it is easier to design a custom
dialog box. However, when the source is unknown, that doesn't stop you.
Obviously, in ADO.NET and OLEDB, there is a way to query the provider at run
time to find out property names and the legal values for those that have a
finite set of legal values. This information can be used to dynamically
create a form. I did it using a PropertyGrid, but it can be done without one.
You would need a lot more code, especially if there is not layout manager
available so that you had to calculate the size and position of your controls
manually. You can use a checkbox for True/False properties, a list box for
properties with a finite set of values, and text boxes for everything else.
In ODBC, you don't have to do all of that, because a login box lives in the
driver. You don't have to use it, but it is there.
> In effect, when MS created the connect to data source feature for Excel,
> they followed the exact design pattern I've suggested for you:
>
> 1. The program does know something about the various types of data sources
> you can connect to.
Yes, it does. The OLEDB connection wizard presents an initial list of
categories on my system:
1 - SQL Server
2 - SQL Server Analysis Services
3 - ODBC DSN
4 - Microsoft Data Access - OLE DB Provider for Oracle
5 - Other/Advanced
> 2. The program does put up custom dialogs, based on the data source chosen.
As I do with any database, known or unknown, even if it is just a property
grid right now.
> 3. The user is not required to supply any connection configuration
> parameters beyond:
> a. The type of data source to connect to.
> b. Windows vs. custom authentication with the user's credentials
> c. The specific database/table to connect to.
>
This is presented up front. In the case of Other/Advanced, other properties
are available in the Advanced and All tabs. You are not "required" to look at
them, but Excel does not hide them either, as you insist that it should.
I insist that Excel can connect to any data source for which a driver is
available, and that it exposes all the properties, or at least many more than
it really needs to, and does not save you from yourself. I can't prove it
until SuperDB is released.
I repeat, take it up with Bill. Maybe Bill might be less stubborn than I am.
I would tell him that if he removed the ability to talk to unknown databases
from Excel, then he can kill SuperDB in the egg. Even if he does not agree
with your philosophy about good and bad designn, he might buy that argument,
and you would get what you want. But a lot of people might want to get their
money back.
Regards,
John Brown.
Scott M.
you extended connection properties, you don't *have* to see them. They are
not provided up front for the user to look at and guess which ones they are
supposed to fill in. This is the key point and the reason that I say that
Excel is more of an example of how I suggest you build a DB program and less
of an example of how you have indicated you'd like to go.
-Scott
"John Brown" <John...@discussions.microsoft.com> wrote in message
news:04C32AF6-6489-4AD0...@microsoft.com...
John Brown
"Scott M." wrote:
> I think the big point you've glossed over is that, while Excel *can* show
> you extended connection properties, you don't *have* to see them.
>They are
> not provided up front for the user to look at and guess which ones they are
> supposed to fill in. This is the key point and the reason that I say that
> Excel is more of an example of how I suggest you build a DB program and less
> of an example of how you have indicated you'd like to go.
>
> -Scott
>
True, this is a refinement that my crude PropertyGrid dialog lacks.
Of course, when SuperDB is released, my users may have to guess (or read the
SuperDB manual as they should), unless the OLEDB and/or ADO.NET specs say
that each property must indicate whether it is absolutely required. They
probably do, because I do not and never will believe that Excel contains a
gigantic select case statement with a branch for every OLEDB provider that
has ever been written.
And don't forget about the ones that have not been written. What do you
think Excel 2007 is likely to do when it is confronted with SuperDB 2012?
Regards,
John Brown.
Regards,
John Brown.
Scott M.
> And don't forget about the ones that have not been written. What do you
> think Excel 2007 is likely to do when it is confronted with SuperDB 2012?
Well, that's exactly my point. The current programming of Excel (or more
accuartely the Windows Data Source Connection component) would prohibit
connections to SuperDB if it could not be connected to using a current
provider/driver. An updated version of the program would have to be written
and released giving Excel those capabilities. This happens all the time
with the Microsoft Data Access Component librry (MDAC). New providers and
drivers are added so that existing software will know how to connect and
communicate with them. But, the software itself is not written in such a
way that it can handle any new data source that comes down the line (SuperDB
2012).
-Scott
John Brown
"Scott M." wrote:
I find that very hard to believe, but since SuperDB isn't out yet, I'll
leave there.
Regards,
John Brown.