none
Which keywords and values of SqlConnection.ConnectionString are recommended to use when they have alternatives? RRS feed

  • Question

  • I'm trying to build a connection string for SQL Server.  Some of the keywords have alternatives.  I want to know which alternative is recommended and why.  For examples,

     

    Example #1:

    the keyword Initial Catalog is listed as an alternative to the keyword Database.   Should I use Initial Catalog or Database and why?

    Example #2:

    Should I use the name or network address of the instance of SQL Server and why?

     

    Also, some keywords can accept true, false, yes, and no.  I don't think there is any difference in using true or yes.  But I wonder whether there is any recommneded standard.

     

     

    In MSDN Library, the value sspi for the keyword Intergrated Security or Trusted_Connection is stated as strongly recommended.  I remember reading a thread somewhere using the value true does not work in a case but using the value sspi will work.  What is the difference between sspi and true?

     

    Keywords that I'm interested to know which alternative is recommended and why

    Connect Timeout

    Connection Timeout

    ----------------------------------------

    Data Source

    Server

    Address

    Addr

    Network Address

    ----------------------------------------

    Initial Catalog

    Database

    ----------------------------------------

    Integrated Security

    Trusted_Connection

    ----------------------------------------

    Network Library

    Net

    ----------------------------------------

    Password

    Pwd

    ----------------------------------------

     

     

     

     

     

    Wednesday, January 23, 2008 7:31 PM

Answers

All replies

  • use the SqlCommandBuilder class
    Wednesday, January 23, 2008 11:39 PM
  • Hi Sheng,

     

    I think you have misunderstood my question.  I'm trying to build the connection string not command.  Also, I'm using ConnectionStringBuilder class to build the connection string.  Please reread my post.

     

    Thursday, January 24, 2008 12:53 AM
  • Sorry, I misunderstood. You can use SqlConnectionStringBuilder to access the SQL server specific properties. To make some properties required, you can write your own validate procedure and validate before accepting changes.
    Thursday, January 24, 2008 1:03 AM
  •  

    Hi Sheng,

     

    I have no problem in using SqlConnectionStringBuilder class or the one from DbProvider.   I'm asking about the keyword/value of the connection string.   Please reread my original post for detail.

    Thursday, January 24, 2008 1:24 AM
  • It is version specific, for the keywords used by SQL Server 2005, see Using Connection String Keywords with SQL Native Client in SQL Server 2005 Books Online

    Thursday, January 24, 2008 1:30 AM
  •  

    Hi Sheng,

     

    I'm confused now.  I'm using ADO.NET in VS2008.    I believe the link you gave me is not for ado.net.  

     

    I'm looking at this http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx.  You will notice that some keywords have few equivalents.

    Thursday, January 24, 2008 2:32 AM
  • See Connection String Syntax (ADO.NET) for keywords supported by the .Net Frameword data providers.

    Thursday, January 24, 2008 2:49 AM
  •  

    Hi Sheng,

     

    The link that I gave you is a link from that page....I'm using SqlClient....

    Thursday, January 24, 2008 2:58 AM
  • Ultimately the connection string is sent to the SQL Server. Look at the SQL Server's documentation. There are two sets of keywords.

    Applications using IDataInitialize::GetDataSource can also use the keywords used by IDBInitialize::Initialize but only for

    properties that do not have a default value. If an application uses both the IDataInitialize::GetDataSource keyword and the IDBInitialize::Initialize keyword in the initialization string, the IDataInitialize::GetDataSource keyword setting is used.

    Because both Integrated Security and Trusted_Connection are mapped to DBPROP_AUTH_INTEGRATED. So they are indentical. Addr, Address and Network Address map to the same SSPROP_INIT_NETWORKADDRESS setting but Data Source and Server maps to DBPROP_INIT_DATASOURCE

    Thursday, January 24, 2008 3:47 AM
  • Hi Sheng,

     

    Does your reply applicable to .NET Framework Data Provider for SQL Server (

    System.Data.SqlClient)?  I think that reply is for .NET Framework Data Provider for OLE DB (

    System.Data.OleDb).

     

     

     

     

    Thursday, January 24, 2008 7:11 PM
  • Ultimately someone needs to parse the string and initialize the database connection, and SQL Server native client is the one. Other data providers should map their parameters to native parameters. Take a look at the ,net source code if you have Visual Studio 2008.
    Thursday, January 24, 2008 10:00 PM
  •  

    Hi Sheng,

     

    Now, I think I start to understand what you're talking about.   Even though I'm using .NET Framework Data Provider for SQL Server in ADO.NET, Sql Native Client is what being used behind the scene.  Which component of Sql Native Client (OLE DB provider or ODBC driver) is used by .NET Framework Data Provider for SQL Server?

     

    I'm using VS2008 but I have not downloaded the .net source code yet.  I think the source code is just available recently.

    Thursday, January 24, 2008 10:58 PM