SqlConnection (.NET)Standard Security: “Data Source=Aron1;Initial Catalog=pubs;User Id=sa;Password=asdasd;” – or – “Server=Aron1;Database=pubs;User ID=sa;Password=asdasd;Trusted_Connection=False” (both connection strings produces the same result) Trusted Connection: “Data Source=Aron1;Initial Catalog=pubs;Integrated Security=SSPI;” – or – “Server=Aron1;Database=pubs;Trusted_Connection=True;” (both connection strings produces the same result) (use serverName\instanceName as Data Source to use an specifik SQLServer instance) Connect via an IP address: “Data Source=190.190.200.100,1433; Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=sa;Password=asdasd;” (DBMSSOCN=TCP/IP instead of Named Pipes, at the end of the Data Source is the port to use (1433 is the default)) Enabling MARS (multiple active result sets): “Server=Aron1;Database=pubs; Trusted_Connection=True;MultipleActiveResultSets=true” Note! Use ADO.NET 2.0 for MARS functionality. MARS is not supported in ADO.NET 1.0 nor ADO.NET 1.1 Streamline your Data Connections by Moving to MARS, by Laurence Moroney, DevX.com >>Attach a database file on connect to a local SQL Server Express instance: “Server=.\SQLExpress; AttachDbFilename=c:\asd\qwe\mydbfile.mdf; Database=dbname; Trusted_Connection=Yes;” – or – “Server=.\SQLExpress; AttachDbFilename=DataDirectorymydbfile.mdf; Database=dbname; Trusted_Connection=Yes;” (use DataDirectory when your database file resides in the data directory) Why is the “Database” parameter needed? Answer: If the database was previously attached, SQL Server does not reattach it ( it uses the attached database as the default for the connection). Using “User Instance” on a local SQL Server Express instance: “Data Source=.\SQLExpress;integrated security=true; attachdbfilename=DataDirectory\mydb.mdf;user instance=true;” The “User Instance” functionality creates a new SQL Server instance on the fly during connect. This works only on a local SQL Server 2005 instance and only when connecting using windows authentication over local named pipes. The purpose is to be able to create a full rights SQL Server instance to a user with limited administrative rights on the computer. To enable the functionality: sp_configure ‘user instances enabled’,’1′ (0 to disable) Using SQL Server 2005 Express? Don’t miss the server name syntax: SERVERNAME\SQLEXPRESS (Substitute “SERVERNAME” with the name of the computer)

Context Connection – connecting to “self” from within your CLR stored prodedure/function C#: using(SqlConnection connection = new SqlConnection(“context connection=true”)) { connection.Open(); // Use the connection } Visual Basic: Using connection as new SqlConnection(“context connection=true”) connection.Open() ‘ Use the connection End Using The context connection lets you execute Transact-SQL statements in the same context (connection) that your code was invoked in the first place.

OracleConnection (.NET)Standard: “Data Source=MyOracleDB;Integrated Security=yes;” This one works only with Oracle 8i release 3 or later Specifying username and password: “Data Source=MyOracleDB;User Id=username;Password=passwd;Integrated Security=no;” This one works only with Oracle 8i release 3 or later

Adaptive Server Enterprise (ASE): “Provider=Sybase.ASEOLEDBProvider;Srvr=myASEserver,5000; Catalog=myDBname;User Id=username;Password=password” – some reports on problem using the above one, try the following as an alternative – “Provider=Sybase.ASEOLEDBProvider; Server Name=myASEserver,5000;Initial Catalog=myDBname; User Id=username;Password=password” This one works only from Open Client 12.5 where the server port number feature works, allowing fully qualified connection strings to be used without defining any .IDS Data Source files.

ODBCDSN-less: “Provider=MSDASQL.1;DRIVER=Ingres;SRVR=xxxxx; DB=xxxxx;Persist Security Info=False;uid=xxxx; pwd=xxxxx;SELECTLOOPS=N; Extended Properties=””SERVER=xxxxx; DATABASE=xxxxx;SERVERTYPE=INGRES””” Important note! The two double quota (“”) in the string are escaped quotas (VB syntax), you may have to change this to your language specific escape syntax (ex. \”) or maybe single quota (‘).

OLE DB Standard: “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties=””Excel 8.0;HDR=Yes;IMEX=1″”” “HDR=Yes;” indicates that the first row contains columnnames, not data “IMEX=1;” tells the driver to always read “intermixed” data columns as text. Note that this option might affect excel sheet write access negative. TIP! SQL syntax: “SELECT * FROM [sheet1$]” – i.e. worksheet name followed by a “$” and wrapped in “[” “]” brackets. TIP! Check out the [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel] located registry REG_DWORD “TypeGuessRows”. That’s the key to n ot letting Excel use only the first 8 rows to guess the columns data type. Set this value to 0 to scan all rows. This might hurt performance. Important note! The two double quota (“”) in the string are escaped quotas (VB syntax), you may have to change this to your language specific escape syntax (ex. \”) or maybe single quota (‘).

OLE DB Standard: “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\;Extended Properties=””text;HDR=Yes;FMT=Delimited””” “HDR=Yes;” indicates that the first row contains columnnames, not data Important note! The two double quota (“”) in the string are escaped quotas (VB syntax), you may have to change this to your language specific escape syntax (ex. \”) or maybe single quota (‘).

OLE DB, OleDbConnection (.NET) IBM Client Access OLE DB provider: “PROVIDER=IBMDA400; DATA SOURCE=MY_SYSTEM_NAME; USER ID=myUserName;PASSWORD=myPwd” Where MY_SYSTEM_NAME is the name given to the system connection in OperationsNavigator IBM Client Access OLE DB provider: “PROVIDER=IBMDA400; DATA SOURCE=MY_SYSTEM_NAME;USER ID=myUserName;PASSWORD=myPwd;DEFAULT COLLECTION=MY_LIBRARY;” Where MY_SYSTEM_NAME is the name given to the System Connection, and MY_LIBRARY is the name given to the library in iSeries Navigator.