C# Connection String Storing

You could add a config file to the project and store the settings in there. Either as a connectionstring or seperated out like ServerName, DataBase, UserID, PWd

You can also encrypt the data stored.

You can use what Joeisanerd says and encrypt the connection string, thus preventing the user from seeing what value is actually in it.

I am interested in creating the ODBC client on each machine.  I have gone this route before when using Visual Basic.  My problem is that this application is already running using a dataconnection class that returns a sqlConnection.  Is there anyway to incorperate this ODBC connection, replacing my connection string, but still allowing me to return the sqlConnection?

(if that makes any sense?)

thanks

sqlConnection will give enough performance improvement that I would think that it's better to figure a way to store the connection string and use sqlConnection directly, instead of slowing down and using odbc.  It's got more layers.

Are you trying to read the sqlserver name and database from the ODBC connection to created you SqlConnection String? You can do that by reading the registry under
local mahcine, software , ODBC, ODBC.INI, datasourcename

SRigney>  Say... Why is everyone so down on ODBC connections?  It's like a programmers mantra... ODBC connections are Soooo slow compared to OLEDB.  But all of the tests I have run have prooved out to be pretty much inconclusive.

Here's a few pages asking the same question:
http://discuss.fogcreek.com/joelonsoftware/default.asp?cmd=show&ixPost=18686

http://www.4guysfromrolla.com/webtech/070399-1.shtml


Anything less than a dozen concurrent connections there is NO noticable difference between DSN and DSN-less connections.  Even if you have over 60 concurrent connections to your database you will mere 10% improvement.  Hell, I've lost more cpu processes with bad programming structures.  My personal opinion is to add the security and not quibble about milli-seconds.



Having said that... using a SQL data provider for SQL server 200 can be nearly 60% faster than a regular OLEDB connection when using .Net.   .Net was geared specifically to work best with SQL server 2000 (and other MS products).




Now, back to your question.  Can you move transparently from SQLServer provider to an ODBC provider.  The answer is "Maybe".


It depends on how you set up your connection class and how your application uses it.  If you did it correctly (streamlined connections, passing datasets/datatables and not SQLAdapters), then you can change approximately 7 lines of code and have the exact same application.

If you are passing SQLDataADapters around all over... then you have some work to do and you'll have to convert everything to an ODBCDataAdapter

I'm only down on it because I've read and tested it and using SqlConnection against SQL2000 (which is what I use at work) has shown significant advantages.  And some of my databases have as many as 3000, yes that's three thousand, connections on them.

As far as security goes, storing an encrypted string in the config file is as secure as any ODBC connection is.   We already lock the machines down preventing the users from writing to most locations, so a read only file that they can't decipher works great.

Hi, I've actually taken the config file route.  I have now created the file, and am successfully taking in the information.  My question now, is how to encrypt this information.  I have yet to encrypt information, and I was wondering how this was properly done.

Thanks

There is one small bug in his code which may or may not present itself.

To fix it you will need to change
            byte[] bytOut = ms.GetBuffer();
            int i = 0;
            for (i = 0; i < bytOut.Length; i++)
                if (bytOut[i] == 0)
                    break;

to

            byte[] bytOut = ms.GetBuffer();
            int i = 0;
            for (i = 0; i < (int)ms.Length; i++)  <---- This is the only change.
                if (bytOut[i] == 0)
                    break;

And, in your case... I would agree.  3,000 connections (against clustered servers I assume).

Of course, with 3,000 connections I would also just to the assumption that this was a web based application (installing the app on 3,000 computers would not be fun).


At any rate, as you can guess, I am a big proponent for ODBC connections... or at the very least doing the minimum that is required to be secure and functional with good/decent performance.   If you don't HAVE to mess around with encrypting and decrypting text files/registry keys, etc then don't do it.  Let the database do the encryption and decryption for you.  



Ultimately, I feel that if an ODBC connection is good enough for Enterprise level software like PeopleSoft (sold around the world, works on every platform and every database) then I guess its good enough for me.

No, it's a desktop app, in centers located in 8 different cities.  The connections all sit on clusterd COM+ servers and the database resides on a SAN.

We push everything out with ActiveDirectories, so we don't have to actually go to each desktop to deploy.

I haven't used the encryption features of .net yet, I jsut know they are there. I would follow the link given and try that.  Check the msdn website for the System.Security.Cryptography

SRigney>  Insurance company?

I've been working on the example from the given site http://www.codeproject.com/dotnet/encryption_decryption.asp
when i use the encrypt method, is the key that I am passing it just a random hardcoded string?

I have been passing it a string, and depending on the string, I get the error :
An unhandled exception of type 'System.Security.Cryptography.CryptographicException' occurred in mscorlib.dll Length of the data to decrypt is invalid.

when using the decrypt method.

Here is my code:

                  String connection = "Connection string to be encrypted"
                  mobjCryptoService = new RC2CryptoServiceProvider();
                  String output = this.Encrypting(connection, "3523");
                  Console.WriteLine(output);
                  String input = this.Decrypting(output, "3523");
                  Console.WriteLine(input);
                  return output;

This returns the error.  I have however been able to get it to work with other strings.  I also was wondering what type of encryption to use?:

DESCryptoServiceProvider, RC2CryptoServiceProvider or RijndaelManaged.

Thanks for your help

That error sounds like you did not implement the change that I mentioned previously.

The Key is the password key that is used to encrypt the file.  If someone knows the key and the type of encryption they can reproduce the original string, so make it something that's somewhat complex.

I don't know which of the three types of encryption are better, they all end up looking like junk to me.