ASP .NET Connection Strings in Web.config File

ASP .NET offers an elegant way to store data source connection creditentials. Connection strings should be stored in a .config file, commonly and by default the Web.config file.

Firstly, what are connection strings? Connection strings are strings of text that contain information about a data source used in the code for data access. Information contained in a connection string includes the data source name, data source address, security mode, login creditentials, and the data source type.

There are a couple reasons why the Web.config file is a good place to store connection. One, ASP .NET is automatically configured to never display a file with a .config extension. So you can assume a level of security when storing login information in the Web.config. In addition, ASP .NET can be configured to encrypt the Web.config file, which would add more security. Nonetheless, you can be assured that any web request to a .config file would result in an error. Secondly, using Web.config allows a central way to manage information without requiring digging into the code nor code modification.

Below are examples of how connection strings would be used:

<?xml version="1.0"?>
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
 <connectionStrings>
  <add name="connectionName" connectionString="Data Source=serverName;
  Initial Catalog=databaseName;User Id=username;Password=password;"
  providerName="System.Data.SqlClient"/>
</connectionStrings>
...
</configuration>

To retrieve the above connection string in C# code:

string connStr = ConfigurationManager.ConnectionStrings["connectionName"];

To use the connection string directly in an ASP .NET page:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"
  ConnectionString="<%$ ConnectionStrings:connectionName %>"
  SelectCommand="SELECT * FROM [Employees]" />

Reading/Writing Data with ADO .NET in C#

In .NET coding there are two main methods of handling database data using ADO.NET. The two methods are distinguished by the classes used to access and manipulate the data. The classes are DataReader and DataSet. I will briefly go over each class and major differences between the two in addition to some examples.

DataReader

The DataReader is the core class for retrieving data from a database in ADO.NET. When using ADO.NET to read data from a database, a DataReader is always used although sometimes implicitly. Even in the case of using a DataSet, the DataAdapter that is used to populate the DataSet uses a DataReader internally.
Now a few things about the DataReader. A DataReader object can merely provide read-only access to a database and only in a forward direction. The DataReader is connection based, meaning the connection to database is maintained while data is read by the DataReader. The DataReader class is efficient and has a relatively small memory fingerprint. If no data writing is required, a DataReader is usually the best choice.

Example

using (SqlConnection conn = new SqlConnection(connectionString))
{
   SqlCommand command = new SqlCommand("SELECT * FROM Categories");
   command.Connection = conn;
   conn.Open();
   SqlDataReader dr = command.ExecuteReader();
}

DataSet

As we have seen above, the DataSet aproach also uses a DataReader. However, the difference is that the DataReader is used to read the data into a DataSet object which retains the data in memory. Once in memory, the data can be manipulated as the programmer wishes. After the data is modified inside the DataSet, it can be written back to a database.
The DataSet is a connectionless object. Once the data is read into the DataSet, the connection to the database is halted. Clearly, using a DataSet to hold records has a memory cost. While in memory, the data can be modified multiple times and when finalized written once to the database. If data memory retention or modification is needed, a DataSet is usually the solution.

Example

using (SqlConnection conn = new SqlConnection(connectionString))
{
   SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Categories",conn);
   DataSet ds = new DataSet();
   ad.Fill(ds);
}