As someone who has learned a lot from SQLServerCentral, I have been thinking of ways I could contribute to the SSC community. As a developer I have noticed there are not as many introductory articles in SSC's archives from the point of view of the developer. So I thought I would try some short articles about working with SQL Server data in a .Net application, to explain how to connect with SQL Server data, what is possible, and how to work with the issues that can come up. For the first article I decided to focus on the Connection object which is part of the ADO.Net Framework.
The ADO.Net Framework is a collection of objects that provides a consistent way to access data using any .Net language, regardless of the original format of the data. For example ADO.Net objects can work with text files, XML, and SQL Server and Oracle databases as data sources.
The ADO.Net Connection object works with the other ADO.Net objects to configure the connection to the data, the source of the data and what kind of protocols to use. The SQLConnection is the instance of the Connection object specifically for configuring a connection to SQL Server. It is designed to take advantage of the flexibility and various features of SQL Server databases such as Replication, Availability Groups, encryption etc.
This is an introduction to the SQLConnection object and how to connect to your SQL Server data from .Net code. Then I will go further into some of the other features of the SQLConnection object and how configure the connection for your specific SQL Server environment.
Try This at Home?
I created a simple console application to try out the options in the SQLConnection. The .zip file is included below in the Resources section. I added a class called DataConnect and a method called ConnectionStringDemo to test the code. The namespace comes from the name I gave to the project.
using System;
using System.Data.SqlClient;
namespace ConnectDemo1
{
class DataConnect
{
public void ConnectionStringDemo()
{
// Simple demonstration of the connection string and some of the options.
// ... (insert the sample code here)
}
}
}To run the test code I added code to the Main() method to create an instance of my class and run the test.
static void Main(string[] args)
{
DataConnect conn = new DataConnect();
conn.ConnectionStringDemo();
}The sample code can be pasted into the ConnectionStringDemo method to run the test.
It's Not String Theory
The details of the connection are specified with a connection string. The connection string can be provided in various ways to be able to request a specific data connection for each situation. The connection string consists of a list with the elements in the list specifying the different properties of the connection. A link to the BOL article on the connection string is here.
The connection string is a list of name/value pairs in the format key=value separated by semicolons (;). The elements in the list specify the source of the data and how to connect to the data. With the most basic form of the connection string you specify the server, default database and SQL Server login information.
This code example shows how to create and open a SQL connection with a basic connection string.
string connectString = "Data Source=(local);Initial Catalog=tempdb;Integrated Security=True";
SqlConnection connection = new SqlConnection(connectString);
connection.Open();
The elements specified in the sample connection string are:
- Data Source=(local) specifies that the server is on the local computer. In this field you can supply a server name or server\instance name. As an alternative to (local) you can also use localhost or a period (.). Server= is a synonym for Data Source=. If you need to specify the network protocol or provide a port number you can provide the format with a colon (:) before the server name, for example as tcp:hostnme or tcp:hostname, portnumber for TCP. For Named pipes the format is Data Source=np:\\hostname\pipe\pipename. The protocols and ports available for connection are configured through the SQL Server Configuration Manager.
- Initial Catalog=tempdb specifies that tempdb is the default database on the data source. Any database name can be specified as the default.
- Integrated Security=True means that it will connect to SQL Server with Windows integrated security. Integrated Security=SSPI has the same meaning as True for this element. If you set Integrated Security=False the connection will try to log in to SQL Server with a SQL Server login so you need to provide the SQL Server database login and password in the conection string by including the UserID=usr;Password=pwd; elements.
There is information about the other elements in the connection string later in this article.
After opening the connection you can use code like this to display the status of the connection and one of the properties available in the SQLConnection object, the version of the server it is connected to. If the connection is open, the State property returns "Open". The other values for the connection status returned by Connection.State are in the ConnectionState Enumeration as documented in this BOL article.
Console.WriteLine("Connection Status is {0}", connection.State);
Console.WriteLine("Server Version is {0}", connection.ServerVersion);
connection.Close();
Console.Write("Press Enter to Continue:");
Console.ReadLine();After displaying the status and version, this code closes the connection and waits for you to press enter so you can read the output before the window is closed.
After you are done with what you are doing with the connection you should close it. The recommendation is to open the connection, do what you need to do, and close the connection. This releases the resources for other connections to the server.
When I ran my test (on a computer with SQL Server 2008 R2) this was the output:
Connection Status is Open Server Version is 10.50.2550 Press Enter to Continue:
Connection String Builder
The SqlConnectionStringBuilder object can be used to build the connection string at runtime. For example, the user could select which server they want to connect to, or access could be restricted by limiting the resources assigned to the user.
When you create the string builder object it is like a template that includes every possible element in a SQL Server connection string. You can set the values of the elements in the connection string as properties of the string builder object to configure your connection to SQL Server. Using the string builder, we could build our sample connection string and use it to create the connection object with this code:
// Data Source=(local);Initial Catalog=tempdb;Integrated Security=True
SqlConnectionStringBuilder sbldr = new SqlConnectionStringBuilder();
sbldr.DataSource = "(local)";
sbldr.InitialCatalog = "tempdb";
sbldr.IntegratedSecurity = true;
SqlConnection connection = new SqlConnection(sbldr.ConnectionString);This code creates a string builder object and sets the properties of the string builder that correspond to the elements in our basic connection string. Then the last line creates a SQLConnection object, using the ConnectionString property of the string builder to return the proper string with the values we specified.
This sample shows a method that can be used to configure the connection string at runtime. The method replaces values in a default connection string to specify different server name and default database, or to specify a SQL Server user/password for login.
public string BuildConnectionString(string server, string dbName, string sqlUser, string sqlPwd)
{
// Modify the basic connection string to allow the user to specify a different server and default database
// or to use a SQL Server login instead of Windows integrated security if values are supplied supplied.
// Start with the basic string as a default.
string connectString = "Data Source=(local);Initial Catalog=tempdb;Integrated Security=True";
SqlConnectionStringBuilder bldr = new SqlConnectionStringBuilder(connectString);
// Replace the values if provided or keep the default values.
if (server != "")
bldr.DataSource = server;
if (dbName != "")
bldr.InitialCatalog = dbName;
if (SQLuser != "")
{
bldr.IntegratedSecurity = false;
bldr.UserID = SQLuser;
bldr.Password = SQLpwd;
}
return bldr.ConnectionString;
}If you want to try this method in the test code, replace the line that creates the connection string with a reference to the method like this:
string connectString = BuildConnectionString("server", "defaultDB", "SQLuser", "SQLpwd");To get a list of all the possible keys in a SQL Server connection string and their current values in the string builder object you can list the Keys collection of the string builder object with this code.
foreach (string key in sbldr.Keys)
Console.WriteLine("{0}={1}", key, sbldr);Here is the result I got (using Visual Studio 2013 and .Net framework 4.5):
Data Source=(local)
Failover Partner=
AttachDbFilename=
Initial Catalog=tempdb
Integrated Security=True
Persist Security Info=False
User ID=
Password=
Enlist=True
Pooling=True
Min Pool Size=0
Max Pool Size=100
PoolBlockingPeriod=Auto
Asynchronous Processing=False
Connection Reset=True
MultipleActiveResultSets=False
Replication=False
Connect Timeout=15
Encrypt=False
TrustServerCertificate=False
Load Balance Timeout=0
Network Library=
Packet Size=8000
Type System Version=Latest
Authentication=NotSpecified
Application Name=DataConnect
Current Language=
Workstation ID=
User Instance=False
Context Connection=False
Transaction Binding=Implicit Unbind
ApplicationIntent=ReadWrite
MultiSubnetFailover=False
TransparentNetworkIPResolution=True
ConnectRetryCount=1
ConnectRetryInterval=10
Column Encryption Setting=Disabled
More information about the different options available is in the BOL article about the SqlConnectionStringBuilder Class.
Other Options for the Connection String
Some of the other elements in the connection string can be used to configure the connection for other SQL Server datatbase options, including
- Replication
- AlwaysOn Availability Groups
- Always Encrypted
- Distributed transactions
- Current language
Some of the elements are for configuring connection pooling, which is enabled by default. You can configure the maximum and minimum number of connections, or switch it off.
f your connection is slow you can use the Connection Timeout element to increase your connection's time limit. The default is 15 seconds.
The Application Name element is useful to identify the connection in SQL Server and other applications. For example the application name value shows in the ProgramName columnwhen you execute th sp_who2 stored procedure. APP is a synonym for Application Name.
Because you don't want to have to recompile your code to change the connection, the connection string is usually saved in the configuration file, app.config or web.config. This allows assignment to a specific data connection for each deployment. To do this, you add a section to the configuration file for connection strings, and use <add> to add the string as a key/value pair like this:.
<connectionStrings>
<add name="simpleConnection" connectionString="Data Source=(local);Initial Catalog=tempdb;Integrated Security=True" />
</connectionStrings>Then in your data access method you use a ConfigurationManager object to retrieve the value:
ConnectionStringsSection connectionStringsSection = (ConnectionStringsSection)ConfigurationManager.GetSection("connectionStrings");
string connectString = connectionStringsSection.ConnectionStrings["simpleConnection"].ConnectionString;You will need to add System.Configuration namespace to the using directives at the top of the class to access the ConfigurationManager class:
using System.Configuration;
There is more information about maintaining the connection string in the configuration file in this BOL article.
Handling Exceptions and, Error Conditions
Because there can always be problems with communication to SQL Server, you can use exception handling to respond to any problems with the process of creating the connection. If you try to create the new SqlConnectionString object and the connection string has invalid keys or syntax errors, you may get an ArgumentException. If the Open is not successful the method can raise the SqlException, InvalidOperationException or ConfigurationErrorsException exceptions.
This code sample shows how you can catch these execptions when creating and opening the connection.
SqlConnection connection;
try
{
connection = new SqlConnection(connectString);
}
catch (ArgumentException ax) // there was something wrong in the connection string.
{
Console.WriteLine("Error creating the connection: {0}", ax.Message);
return;
}
try
{
connection.Open();
}
// Catch errors specific to the Open method
catch (SqlException ex)
{
Console.WriteLine("Error opening the connection: {0}", ex.Message);
return;
}
catch (InvalidOperationException ix)
{
Console.WriteLine("Invalid Operation error: {0}", ix.Message);
return;
}
catch (ConfigurationErrorsException cx)
{
Console.WriteLine("Configuration error: {0}", cx.Message);
return;
}The Message property will have further information about why the operation failed. There is more information about the SQLException object here.
After you attempt to connect you can also check the status of the connection object as in this code sample that checks whether the connection is open:
if (connection.State != System.Data.ConnectionState.Open)
{
Console.WriteLine("Connection did not open properly.");
Console.WriteLine("The connection status is: {0}", connection.State.ToString());
connection.Close();
return;
}The State property of the connection is compared to the member of the ConnectionState enumeration. The ConnectionState Enumeration is documented in this BOL article.
If you need to perform complex operations, or operations that involve updates to several tables, you can use the Connection object to create a transaction to enclose all the operations in one unit. Either it succeeds and everything is committed, or it doesn’t and everything is rolled back. Here is a short code sample that illustrates how to create a transaction and commit it or roll it back if there are exceptions.
SqlTransaction trans = connection.BeginTransaction();
try
{
// ... Do your operations here
trans.Commit(); // And commit the transaction if everything succeeds
}
catch (SqlException ex) // If there are any problems, roll back
{
trans.Rollback();
} The BeginTransaction() method of the Connection object returns a SqlTransaction object that represents the transaction in your C# code. The SqlTransaction object is then used to process the transaction with various methods such as Save (savepoint), Rollback or Commit. The BOL article on the SqlTransaction Class is here.
By using different versions of BeginTransaction you can also give the transaction a name or specify the isolation level.
Where to Go Next
This has been a brief introduction to the Connection object, making a basic connection, configuring the connection with the different options in the connection string, and reacting to problems and status. The next step is to actually get the data into the .Net environment. The Connection object is passed to the other data access components user to define where the data is and how to access it.
There are different kinds of components in the ADO.Net framework for different kinds of data access options. The DataSet is an in-memory representation of the data and its relationshipsfrom SQL Server as a collection of objects in .Net. The DataAdapter (SqlDataAdapter) is used to fill, update and manage the connection between the data in the DataSet and the data in SQL Server. The Command (SqlCommand) object offers various options for executing T-SQL statements and stored procedures with support for parameters.
Thanks for reading. Please provide feedback in the discussion about what you think or if you have any other ideas for topics. I will provide my list of possible topics in the discussion. Please provide feedback in the discussion about what you think or if you have any other ideas for topics. I will provide my list of possible topics in the discussion.
