Connection to a Microsoft SQL Server Database System

To be as flexible as possible, ADO.NET allows you to work with various types of databases. This also means that when you create an application, you must certainly first specify the type of database system you would be using. Before performing any database operation, you must first connect to the database server. One way you can do this, on the taskbar, you can click Start -> (All) Programs, and position the mouse on Microsoft SQL Server. You can then click either Query Analyzer or Enterprise Manager:

This would open the SQL Server Enterprise Manager:

You can also establish the connection through the SQL Query Analyzer. To do this, from the task bar, you can click Start -> (All) Programs -> Microsoft SQL Server -> Query Analyzer. This action would open the Connect to SQL Server dialog box:

If the Enterprise Manager was already opened but the server or none of its nodes is selected, on the toolbar of the MMC, you would click Tools -> SQL Query Manager. This also would display the Connect to SQL Server dialog box.

If you are working in Microsoft Visual Studio .NET and need to connect to a server, you have various options. One way is to first display the Server Explorer window. To create a new connection, you can right-click the Data Connections node and click Add Connection...

After clicking Add Connection..., the Data Link Properties would display. The Connection property page should be selected. Otherwise, you can click the Connection tab:

If you are creating a Windows Forms Application (using Microsoft Visual C++ .NET), to support a connection to a database server, the .NET Framework provides the SqlConnection class that is defined in the System::Data::SqlClient namespace. Before using this class, you can first include this namespace in your file:

#pragma once



namespace WinForms1

{

	using namespace System;

	using namespace System::ComponentModel;

	using namespace System::Collections;

	using namespace System::Windows::Forms;

	using namespace System::Data;

	using namespace System::Drawing;

	using namespace System::Data::SqlClient;

To connect to a database, you can first declare a pointer to SqlConnection using one of its two constructors. The default constructor allows you to declare the variable without specifying how the connection would be carried. The second constructor takes as argument a String value. Its syntax is:

public: SqlConnection(String *connectionString);

If you want, you can first create the string that would be used to handle the connection, then pass that string to the second construction. This would be done as follows:

private: System::Void btnConnect_Click(System::Object *  sender, System::EventArgs *  e)

{

	 String *strConnection = S"";

	 SqlConnection *conSQL = new SqlConnection(strConnection);

}

You can also directly create the necessary (but appropriate) string in the second constructor when declaring the variable:

private: System::Void btnConnect_Click(System::Object *  sender, System::EventArgs *  e)

{

	 SqlConnection *conSQL = new SqlConnection(S"");

}

If you prefer to use the default constructor, you can first define a String value. To pass it to the default constructor, the SqlConnection class is equipped with a property called ConnectionString that is a pointer to String. You would use it as follows:

private: System::Void btnConnect_Click(System::Object *  sender, System::EventArgs *  e)

{

	 String *strConnection = S"";

	 SqlConnection *conServer = new SqlConnection();



	 conServer->ConnectionString = strConnection;

}

To use a SqlConnection object, you must provide various pieces of information, packaged as one and made available to the variable. These pieces are joined into a string but are separated from each other with a semi-colon ";". Each piece appears as a Key=Value format. In our lesson, we will refer to each of these pieces (Key=Value) as an attribute of the connection string. When joined, these attributes appear as follows:

Key1=Value1;Key2=Value2;Key_n=Value_n

Anything that part of this string is not case-sensitive (even though you are working on a C++ application). Remember that this whole ensemble is either passed as a string to the second constructor:

SqlConnection *conServer = new SqlConnection(S"Key1=Value1;Key2=Value2;Key_n=Value_n");

or assigned as a string to the SqlConnection::ConnectionString property:

SqlConnection *conServer = new SqlConnection();

String *strConnection = S"Key1=Value1;Key2=Value2;Key_n=Value_n";



conServer->ConnectionString = strConnection;

How you create these attributes depends on the type of computer you are connecting to, whether you are connecting to a database, what level the security you would use (or need), etc. There are various of these attributes, some of them are always required, some of them are usually optional, and some others depend on the circumstances.

Instead of manually declaring a SqlConnection variable, in the Data section of the Toolbox, you can click the SqlConnection button and click the form. When you do this, the studio would declare a global SqlConnection variable for you. To create its connection  string, in the properties window, you can click the arrow of its ConnectionString field. If you have previously created a connection to a server, it would appear in the list. You can select an existing connection or click New Connection to create a new one:

If you select New Connection..., you would be presented with the Data Link Properties dialog box.

When you use the true or SSPI values, the user name (if any) and the password (if any) of the person opening your application would be applied. For example, if the application is being opened on Windows XP Home Edition (that, by default, doesn't require authentication), Windows 2000 Professional that has a default user name and password, or Windows XP Professional that has a default user name and password, the application would be opened fine without checking security.

If you want security to apply, if you are connecting to SQL Query Analyzer using the Connect To SQL Server dialog box, you must click the SQL Server Authentication radio button:

If you are using the Data Link Properties and you want the connection to be authenticated, then you must click (or accept as default selected) the Use A Specific User Name And Password radio button.

If you are programmatically establishing the connection, to apply authentication, you can assign false or no to the security attribute you selected. If you do this, then you must (this becomes a requirement) specify the user name and the password. For example, the following code will produce an error if you execute it:

SqlConnection *conDatabase = new SqlConnection(S"Server=(local);Database='';Integrated Security=no");

So, if you set the security attribute to false or no, then you must provide login credentials. Notice that in some cases, you can provide empty credentials or a blank password.

If you are connecting to SQL Query Analyzer using the Connect To SQL Server dialog box and you want to apply authentication, after selecting the second radio button, this would prompt you for a username.

If you are using the Data Link Properties after clicking the Use A Specific User Name And Password radio button, you can enter a username in the indicated text box.

If you are programmatically creating the connection, to specify the user name, after assigning false or no to the security attribute, you must use the User ID attribute and assign it a valid username. Here is an example:

In some circumstances, you can use an empty password in which case you would assign an empty string to the password attribute.

Microsoft SQL Server (including MSDE) ships with various ready-made databases you can work with. In SQL Server Enterprise Manager, the available databases and those you will create are listed in a node called Databases. To display the list of databases, you can click the Databases node:

If you are not trying to connect to a database, you don't need to locate and click any. If you are attempting to connect to a specific database, in SQL Server Enterprise Manager, you can simply click the desired database. Here is an example:

If you are working in SQL Query Analyzer but you are not trying to connect to a specific database, you can accept the default master selected in the combo box of the toolbar. If you are trying to work on a specific database, to select it, on the toolbar, you can click the arrow of the combo box and select a database from the list:

If you are working from the Data Link Properties and in its Connection property page, if you don't want to open a specific database, there is nothing more to do (the master database would be selected as the default). If you want to establish a connection to a specific database, in the 3. Select The Database On The Server combo box, select the desired database.

If you are working programmatically, to let you specify the database, the connection string includes an attribute named Database. The Database attribute allows you to specify the name of the database you are connecting to, if any. The Database keyword can also be substituted for the Initial Catalog. If you are connecting to an existing database, assign its name to this attribute. If you are not connecting to a database, you can omit this attribute. Alternatively, you can assign nothing to this attribute. Here is an example:

SqlConnection *conDatabase = new SqlConnection(S"Address=(local);Database=; ");

Another alternative is to assign an empty, single-quoted, string to this attribute. Here is an example:

SqlConnection *conDatabase = new SqlConnection(S"Network Address=(local);Initial Catalog=''; ");

As mentioned above, the Database attribute is optional, especially if you are only connecting to the computer and not to a specific database.

There are various other attributes used in the connection string. They include Network Library (also called Net), Application Name, Workstation ID, Encrypt, Connection Timeout, Data Source, Packet Size, AttachDBFilename, Current Language, Persist Security Info.

After creating the connection string, when the application executes, the compiler would "scan" the string to validate each key=value section. If it finds an unknown Key, an unknown value, or an invalid combination of key=value, it would throw an ArgumentException exception and the connection cannot be established.