How to create SQL Server connection strings in Visual Studio

Untangle SQL Server connection strings

Visual Studio connection strings
Credit: Fluzwup (Public Domain)

Today's README file explains how to create connection strings to SQL Server databases using Visual Studio's built-in tools.

To provide an example using real source, I created a simple Winforms application named ITworld, written in C#.

Although my example app is written in C#, the techniques and methodologies used in this piece will work for applications written in other languages such as VB.Net.  

So let's get started -- click Project, application Properties.

Application properties Stephen Glasskeys

Select the Settings tab, and create a new setting entry. For my simple app, I added a new setting named ITworldConn, changed the Type to (Connection String), and Scope to Application.

To finish your new connection setting, click the small ellipsis button. You'll need to do this to specify: server, login, database, and other connection properties.

Settings, create Connection String Stephen Glasskeys

The Connection Properties dialog opens. For my sample ITworld application, I changed the data source to Microsoft SQL Server (SQLClient), and entered the necessary Server, Log On, and database values. 

Connection Properties window Stephen Glasskeys

To validate your connection, use the Test Connection feature, and click OK if you receive a "test connection succeeded" notification. 

But...you may get an error message, which means you'll probably need to double-check the server name, log on credentials, or authentication entries. If you click Test Connection again and receive another error message, you may wish to read my piece on How to fix common SQL Server Management Studio 'Connect to Server' errors.

However if everything works as it should, don't forget to click the Save toolbar icon to keep your new connection string setting. When the project is saved, Visual Studio updates setting information in the app.config XML file.

Next, open app.config to examine its contents. Look for values inside the connectionStrings tag.
Notice the name="ITWorldApp.Properties.Settings.ITWorldConn" entry in the example image below.
 

Connection saved in App.config file Stephen Glasskeys

This is the connection string name, and we can use this to create a connection object in source:

using System.Configuration;

//Retrieve Connection String By Name
string sConn = "ITWorldApp.Properties.Settings.ITworldConn";
ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings[sConn];

SqlConnection conn = new SqlConnection(settings.ConnectionString);
Using ConnectionStringSettings Stephen Glasskeys

Alternatively, you can access the same setting value, reading the Application's properties:

string sConnection = Properties.Settings.Default.ITWorldConn; 
A second way to retrieve connection string settings Stephen Glasskeys

And of course, the connection can be used to create SqlCommand or other data objects like a SqlDataReader.

This source creates a SQLcommand using the conn connection object created above.

//Command and Data Reader
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM Person.Person";
SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.Default);

Creating a command object and data reader Stephen GlasskeysC

Finally, close and dispose of the objects when finished...    

sdr.Close();
conn.Dispose();

In the near future, README file will cover connections to other kinds of servers and databases.

This article is published as part of the IDG Contributor Network. Want to Join?

ITWorld DealPost: The best in tech deals and discounts.
Shop Tech Products at Amazon