Rules to Better Connection Strings

• Do you know how to make connection strings among different environment?

  Different data sources provide different connection strings, for example, the connection string for Oracle is totally different from SQL Server's, even the same product but different versions have special requirement, so how do we make it easy?

  The answer is the very useful website called connectionstrings.com, which provides abundant connection strings ranging from different database products to excel files, actually, you can find most of the connection strings of popular products with detail usage.

• Do you use Windows Integrated Authentication connection string in web.config?

  Both SQL Server authentication (standard security) and Windows NT authentication (integrated security) are SQL Server authentication methods that are used to access a SQL Server database from Active Server Pages (ASP).

  We recommend you use the Windows NT authentication by default, because Windows security services operate by default with the Microsoft Active Directory?directory service, it is a derivative best practice to authenticate users against Active Directory. Although you could use other types of identity stores in certain scenarios, for example Active Directory Application Mode (ADAM) or Microsoft SQL Server? these are not recommended in general because they offer less flexibility in how you can perform user authentication.

  If not, then add a comment confirming the reason.

  <connectionStrings>
     <add name="ConnectionString" connectionString="Server=(local);
      Database=NorthWind;uid=sa;pwd=sa;" />
  </connectionStrings>

  ❌ Figure: Figure: Bad example - Not use Windows Integrated Authentication connection string without comment

  <connectionStrings>
      <add name="ConnectionString" connectionString="Server=(local);
       Database=NorthWind;Integrated Security=SSPI;" />
  </connectionStrings>

  ✅ Figure: Figure: Good example - Use Windows Integrated Authentication connection string by default

  <connectionStrings>
      <add name="ConnectionString" connectionString="Server=(local);
       Database=NorthWind;uid=sa;pwd=sa;" />
      <!--It can't use the Windows Integrated because they are using Novell -->
  </connectionStrings>

  ✅ Figure: Figure: Good example - Not use Windows Integrated Authentication connection string with comment

• Do you avoid using duplicate connection string in web.config?

  Since we have many ways to use Connection String in .NET 2.0, it is probably that we are using duplicate connection string in web.config.

  <connectionStrings>
      <add
          name="ConnectionString"
          connectionString="Server=(local);
          Database=NorthWind;"
      />
  </connectionStrings>
  
  <appSettings>
      <add key="ConnectionString" value="Server=(local);Database=NorthWind;"/>
  </appSettings>

  ❌ Figure: Bad example - Using duplicate connection string in web.config

• Do you avoid putting connection strings in Business module?

  We strongly recommend that putting all connection strings together and saving them to one file, it is convenient for management and deployment.

  

  ❌ Figure: Bad example - Putting connection strings in Business module

• Do you avoid using non strongly typed connection strings?

  Using non strongly typed connection strings means that you have to hard code at some point in your code. Once you change the name of your connection strings, you have to change the code that references them too.

  Visual Studio provides a convenient tool, called Application Settings, that allows you to manage all of your connection strings from only one location. You can use its wizard to compose connection strings quickly and correctly. Also, it provides a management class to read and write all of your connection strings.

  var connString = System.Configuration.ConfigurationManager.ConnectionStrings["MyProj.Properties.Settings.ConnectString"].ToString();
  var conn = new SqlConnection(ConnString);
  var cmd = new SqlCommand(strSql, conn);
  conn.Open();
  cmd.ExecuteNonQuery();
  conn.Close();

  ❌ Figure: Bad example - Using non strongly typed connection strings, the highlighted text is hard code actually

  

  ✅ Figure: Good example - Using Application Settings to configure connection strings

• Do you add the Application Name in the SQL Server connection string?

  You should always add the application name to the connection string so that SQL Server will know which application is connecting, and which database is used by that application. This will also allow SQL Profiler to trace individual applications which helps you monitor performance or resolve conflicts.

  <add key="Connection" value="Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Biotrack01;Data Source=sheep;"/>

  ❌ Figure: Bad example - The connection string without Application Name

  <add key="Connection" value="Integrated Security=SSPI;Persist Security
   Info=False;Initial Catalog=Biotrack01;Data Source=sheep;
   Application Name=Biotracker"/> <!-- Good Code - Application Name is added in the connection string. -->

  ✅ Figure: Good example - The connection string with Application Name

• Do you know the best place to place the connection string?

  The best place to put the connection string is in the Web.Config file.That makes the code simple and easy to read. Look into the following code:

  string cnnString = "data source=(local); integrated security=SSPI; persist security info=False; pooling=False; initial catalog=Northwind2";

  ❌ Figure: Bad example - Using magic strings

  and observe the following code which is simple and easy to read:

  string cnnString = LinqToNorthwind.Properties.Settings.Default.NorthwindEFConnectionString;

  ✅ Figure: Good example - Strongly typed connection string

  private void Form1_Load(object sender, System.EventArgs e)
  {
      //string connString = "data source=(local); integrated security=SSPI; persist security info=False; pooling=False; initial catalog=Northwind2";
      string cnnString = LinqToNorthwind.Properties.Settings.Default.NorthwindEFConnectionString;
      cboCity.Items.Add("London");
      cboCity.Items.Add("Madrid");
      cboCity.Items.Add("Sao Paulo");
      db = new NorthwindDataContext(cnnString);
      cboCity.SelectedIndex = 0;
  }

  ✅ Figure: Good example - Using strongly typed connection string