Hi;

Ok, using the Web.Config connection strings I have all of my code for an
insert, using any database, working - except setting the parameters. How do
Ido this part?

ConnectionStringSettings connStr =
WebConfigurationManager.ConnectionStrings["WindwardPortal"];

DbProviderFactory provider =
DbProviderFactories.GetFactory(connStr.ProviderName);
DbConnection conn = provider.CreateConnection();
conn.ConnectionString = connStr.ConnectionString;

DbCommand cmd = provider.CreateCommand();
cmd.Connection = conn;
cmd.CommandText = "insert into Datasource (title, description, providerType,
sqlVendor, sqlServer, sqlDatabase, sqlConnectionString, xmlFilename,
useCredentials, username, password) " +
                "VALUES (@title, @description, @providerType, @sqlVendor, @sqlServer,
@sqlDatabase, @sqlConnectionString, @xmlFilename, @useCredentials, @username,
@password)";

// how do I set these parameters??? Needs to work with any database (Sql
Server, Oracle, MySql, DB2, etc.)

conn.Open();
cmd.ExecuteNonQuery();

David,

Have a look at this sample on our website.

http://www.vb-tips.com/default.aspx?ID=8c3dc2d7-1232-4dd1-817e-22eaaebb2723

I hope this helps,

Cor

Hi;

Correct me if I'm wrong but I think your code assumes SqlClisnt or
OleDbClient. But that lease OracleClient as well as any new clients coming???

Show quoteHide quote

That's a problem with the code shown, but there's a bigger issue, namely
that there's no need to use ConnectionStringSettings if you're not using a
configuration file. Hard coding it defeats the puprose of this approach and
even if you called Save on your configurationManager, this code doesn't read
from those values anyway.

Also, you may want to check out Bob's article, here
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvs05/html/vsgenerics.asp ,
it's excellent.

As far as the Params go, we discussed that in your other post. But with
respect to the ConnectionString...  You can use the Configuration file like
listed below. then you can use the ProviderName property to determine which
type of Connection you have a string for. Then you can use the specific
implementation of DBConnectionStringBuilder to handle each property you
might be concerned with.

Below I'm posting the code for using the ConnectionStrings property of the
ConfigurationManager and a code snippet to call it.  I've used one of each
of the major providers.  Below it is the code used to retrieve it.




<configuration>

  <connectionStrings>

    <clear/>

    <add name="AdventureWorksString"

         providerName="System.Data.SqlClient"

         connectionString="Data Source=localhost;Initial
Catalog=AdventureWorks; Integrated Security=true"/>

      <add name="OdbcConnectionString"

         providerName="System.Data.Odbc"

         connectionString="Driver={Microsoft Access Driver
(*.mdb)};Dbq=C:\adatabase.mdb;Uid=Admin;Pwd=R3m3emberToUseStrongPasswords;"/>

    <add name="AccessConnectionString"

        providerName="System.Data.OleDb"

        connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=\PathOrShare\mydb.mdb;User
Id=admin;Password=Rememb3rStr0ngP4sswords;" />

    <add name="OracleConnectionString"

        providerName="System.Data.OracleClient"

        connectionString="Data Source=MyOracleDB;Integrated Security=yes;"
/>

  </connectionStrings>

</configuration>



---- This code will walk through them for you although you can refer to each
many different ways

   ConnectionStringSettingsCollection MySettings

      = ConfigurationManager.ConnectionStrings;



if (MySettings != null)

{

  StringBuilder sb = new StringBuilder();

  foreach (ConnectionStringSettings individualSettings in MySettings)

  {

    sb.Append("Full Connection String: " +
individualSettings.ConnectionString + "\r\n");

    sb.Append("Provider Name         : " + individualSettings.ProviderName +
"\r\n");

    sb.Append("Section Name          : " + individualSettings.Name +
"\r\n");

  }

  Console.WriteLine(sb.ToString());

}

Show quoteHide quote

Bill,

Is it really needed to criticise Ken and/or me.

I can assure you that all our samples are tested and that there is a reason
why there is not direct a connection string used.

If you think that it is your duty to citicize other MVP's in these
newsgroups feel free. I think that there are more elegant ways to point each
other on failures as that is needed than a sentence as above.

Cor

--I'm not sure how Ken is even remotely involved in this but the fact is I
didn't criticize either one of you. Since I didn't use the names "Ken" or
"Cor" in anything I wrote, I have no idea where you are coming from. If
however Cor as an MVP, you think it's appropriate to put up incorrect and
confusing examples for people that's one thing.  If you think it's
appropriate to put up incorrect and confusing examples and get upset because
someone pointed out there were some flaws in them, that's another. We all
make mistakes Cor. I make them all the time.  And to be honest, not only
don't I mind, but I EXPECT my fellow MVPS to point it out when I do
something wrong.  And in just about every case I respond with "Thank you for
pointing that out" because i don't want to confuse people and I don't want
anyone to learn something that's wrong from me.  I posted that reply to help
David and show him how to handle multiple providers which is what he was
looking for.  It's unfortunate you got offended but I surely didn't insult
you or anyone else and my intent was only to help out David.  And please,
stop with trying to drag Ken into everything, it's plainly obvious this has
nothing to do with him whatsoever.

--I didn't say the samples weren't tested. I said nothing of the sort.
Please Cor, reread my comments before making accusations and ridiculous
statements.  It doesn't matter one bit if you hard code the connection
string in an example or not. But, if you're going to seriously tell me that
there's a reason why you used the ConnectionStringSettings class with a hard
coded value, I'd love to hear it.  And I'd love to hear how it would have
any bearing on what the guy posted considering it's clear that he was using
a Configuration file and not hard coding the values.  How Cor, would you use
a ConfigurationManager to grab a hard coded value outside of the context of
a configuration file?  Technically it'd be possible but only after writing
the ConfigurationSection out first and even then it would make little sense
in the overall context.  If you're going to post an example for the guy Cor,
at least take a second to understand how it works.

Taken Straight From MSDN Online:

"Represents a single, named connection string in the connectionstrings
configuration file section. "  Here's the link for your reference
http://msdn2.microsoft.com/en-us/library(d=robot)/system.configuration.connectionstringsettings.aspx
Notice the last four words of that sentence Cor "connectionstring
configuration file section"

Remarks

A ConnectionStringSettings object represents a single entry in the
connectionStrings configuration file section.


Notice that it DOES NOT say "connectionstring configuration file section or
hard coded string value".  There's a reason for that. The reason is that
it's not the way the Class was meant to be used.  Now look at the code I
posted.  One example of each provider and using the ConfigurationManager to
retrieve the values.  If you want to hard code connection strings, that's
fine.  However using the ConnectionStringSettings to a hard coded value is
useless, and as I said, defeats the entire puprose of how it's meant to be
used.  In addition, it only serves to confuse people new to this material
(and apparently even those who see fit to publish articles about it).
--Where did I criticize anyone?  You must be reading something other than
what I wrote.  If you read my post again, I didn't use a single person's
name nor did I refer to you or anyone else directly or indirectly.  The code
I referred to was incorrect to the extent that it wasn't being used
correctly.  However that's not why I pointed out there was something wrong
with it. It was confusing and classes were being used that weren't necessary
and if you read the poster's question, he was using a different approach
than this in the first place. You may want to look at his code again.  The
ConnectionStrings property of the ConfigurationManager points to a
configuration file section not a hard coded value.  Sending this guy on a
wild goose chase serves no one's interest. And honestly Cor, everyone makes
mistakes.  You shouldn't expect others to have to try to hide or conceal
that something's wrong just because you made a mistake.

<<I think that there are more elegant ways to point each >>
How was I supposed to handle it Cor?  The code is right there in the post
and it's wrong, particularly wrong considering the question at hand.  I
could have emailed you and David privately and pointed out what is wrong
with it and how to address it, but why?  If the shoe was on the other foot,
I would have simply acknowledged you were correct and said Thank you. My
concern in this case is for the person asking the question and while I
regret that you're bothered by it, I did nothing wrong
--Cor, I stated a Fact.  Not an opinion but a fact. I stated if
professionally and politely. I didn't criticize you or anyone else, I
pointed out what was wrong with the code. If I wanted to embarass you, I
would have.  The fact is that we all make mistakes, you , me and everyone
else.  I would much rather have a mistake of mine corrected then let someone
else learn bad habits from a mistake of mine.  If you can't accept that,
it's unfortunate.  As I pointed out, the that code was written, it defeats
the entire purpose of the class that was used.  If anyone is looking at that
code for answers, I think they would much rather know how to do it properly
than learn wrong.  Please reread my words "Hard coding defeats the purpose
of this approach, and even if you called Save on your configuratoinManager,
this code doesn't read from those values anyway".  What is incorrect about
that Cor?

Show quoteHide quote

David,

I have no Oracle database to test and all our samples are tested.

http://msdn2.microsoft.com/en-us/library/system.data.common.dbproviderfactory(VS.80).aspx

It should work as well as ODBC (the class description is not wide, but it is
in it).

Cor

Hi;

Another post here (somewhere) said that the MySqlClient (not using
OleDbClient) does not support the ProviderFactory. If that's true it blows
this whole approach as the big 4 among our customers are Oracle, MySql, Sql
Server, and DB2 (in that order).

I think we need to experiment here with the other three databases and see
what works. I have a bad feeling we are going to have to write our own layer
to create a common API.

Show quoteHide quote

David,

I think you are right, I saw something on Google by the way with MySQL and
DBProviderFactory.

Cor

Show quoteHide quote

So you have 3 out of 4 right there...

Actually the MySql client is open source, if this is the only provider left
you need, you could take in to consideration to implement the
DbProviderFactory yourself... it might not be as fun as to do your own
database abstraction layer, but maybe less effort.

Another approach could be to use the ODBC or OleDB to connect to MySql until
a provider came available.

http://dev.mysql.com/downloads/connector/net/1.0.html

Rune

You simply continue the same way you started:

DbConnection conn = provider.CreateConnection();
[....]
DbCommand cmd = provider.CreateCommand();
[....]
DbParameter param = provider.CreateParameter();
param.DbType = DbType.String;
param.ParameterName = "@title";
param.Value = ......

R-)

I forgot to tell you: The DbProviderFactory is currently not supported by
the MySQL Client (Connector), they probably will sooner or later, but not at
the moment.

R-)

Oh great - this keeps getting worse and worse. In the java world JDBC handles
this so well.

It looks like I have to go to client specific code and client specific
selects for variable substitution.

Show quoteHide quote

How to get DataRow array into a DataView?
get primary key on insert
How to stop TransactionScope from starting distributed transaction
Checking for Nulls in Fields
All or nothing for DataAdapter