How to Connect Sql Server Using Sql Developer

Table of Contents

• Introduction
• Creating connections
  • Writing out code for connections.
• Dealing with secure connections
• Changing server and or initial catalog at runtime
  • User interface consideration
  • Changing server and or initial catalog in code
    • TableAdapter example
    • User interface example
• Helper methods
• Container classes
• Implementing in a Visual Studio solution
• Alternate method to change connections at runtime
• Summary
• See also
• Related resources
• Source code

Introduction

When working with SQL-Server databases in Windows Forms projects using conventional data providers like System.Data. SqlClient to access tables a connection object is required with a well-formed connection string which may be stored in a string variable or a configuration file.

Depending on the developer, connection strings can be written directly in code, create a connection string using SqlConnectionStringBuilder (there is an example in the accompanying source code) or create temporary project, use the Data wizard to generated a classes access via a TableAdapter or using Entity Framework 6 code first with an existing database.

This setup works well for personal use applications while distributing an application to friends, co-workers or customers or changing from a development environment to a production environment (and in many cases a test environment) quickly causes using string variables useless and with configuration files when changes to the server name requires updating from the developer's server name to another server name in the wild it's best to have a method to change a connection string without user intervention.

In this article these issues along with other common challenges with creating connections to server-based databases will be covered along with dynamically changing a connection string stored in an application configuration file where connections are in Project.Settings.

Creating connections

Writing out code for connections.

When a developer first starts out writing code to access database data they usually will write the connection directly in a form as follows.

private void button5_Click( object sender, EventArgs e)

{

SqlConnection cn = new SqlConnection();

cn.ConnectionString = "Data Source=KARENS-PC;" +

"Initial Catalog=NorthWind;" +

"Integrated Security=True" ;

cn.Open();

}

In the code snippet above disregarding that all data operations should reside in a class when the Data Source (the name or network address of the instance of SQL Server to connect to) and Initial Catalog (The name of the database associated with the connection) exists and the user at runtime has permissions to access the data there are no true issues.

Although the above connection works as a project grows in size with more operations needed to access data many developers tire of creating a connection each time it's needed so this evolves into either a private connection object in a form or another class and is generally scoped as globe. This means each time the connection is needed an assertion is required to see if the connection object state is open e.g.

if (_connection.State != ConnectionState.Open)

{

_connection.Open();

}

Simply executing Open or OpenAsync method may not resolve why the connection is not open along with in general leading to more code than needed. The next step is to work with a using statement as per below which will dispose of the connection once done with working data.

using (var cn = new SqlConnection())

{

cn.ConnectionString = "Data Source=KARENS-PC;" +

"Initial Catalog=NorthWind;" +

"Integrated Security=True" ;

cn.Open();

}

A simple example for proper connection object which is in a button click event.

private void button5_Click( object sender, EventArgs e)

{

using (var cn = new SqlConnection())

{

cn.ConnectionString = "Data Source=KARENS-PC;" +

"Initial Catalog=NorthWind;" +

"Integrated Security=True" ;

var selectStatement = "SELECT FirstName, LastName " +

"FROM Customers " +

"WHERE ID = @Identifier" ;

using (var cmd = new SqlCommand() {Connection = cn, CommandText = selectStatement})

{

cmd.Parameters.AddWithValue( "@Identifier" , 100);

cn.Open();

var reader = cmd.ExecuteReader();

if (reader.HasRows)

{

reader.Read();

string firstName = reader.GetString(0);

string lastName = reader.GetString(1);

}

}

}

}

Unfortunately the code is only good for the event it resides in, a better idea is to place the code into a public class with a public method. First, since two values are needs let's create a class named Customer.

public class Customer

{

public int Identifier { get ; set ; }

public string FirstName { get ; set ; }

public string LastName { get ; set ; }

public string FullName => $ "{FirstName} {LastName}" ;

public override string ToString()

{

return $ "{Identifier}" ;

}

}

Note there is a property to provide first and last name together and ToString is is overridden so when in a debug session the primary key will be displayed by default.

Below code was removed from the button click event and refactored into a method.

using System.Data.SqlClient;

namespace WindowsFormsApp1

{

public class CustomerOperations

{

public Customer GetCustomerByPrimaryKey( int identifier)

{

var customer = new Customer();

using (var cn = new SqlConnection())

{

cn.ConnectionString = "Data Source=KARENS-PC;" +

"Initial Catalog=NorthWind;" +

"Integrated Security=True" ;

var selectStatement = "SELECT FirstName, LastName " +

"FROM Customers " +

"WHERE ID = @Identifier" ;

using (var cmd = new SqlCommand() { Connection = cn })

{

cmd.CommandText = selectStatement;

cmd.Parameters.AddWithValue( "@Identifier" , identifier);

cn.Open();

var reader = cmd.ExecuteReader();

if (reader.HasRows)

{

reader.Read();

customer.Identifier = identifier;

customer.FirstName = reader.GetString(0);

customer.LastName = reader.GetString(1);

}

}

}

return customer;

}

}

}

Keeping with what has been covered, the connection string may also be stored in the project's application configuration file which is done under project settings, settings, add a new setting as type ConnectionString. In the following example the connection string name is NorthWind.

using (var cn = new SqlConnection())

{

cn.ConnectionString = Properties.Settings.Default.NorthWind;

cn.Open();

}

When working with TableAdapter approach to accessing data the wizard which generated data classes also generated a connection string within the current project's configuration file which means the Framework knows how to connection to the underlying data. In the designer file for the dataset is where the connection string is set by referencing a connection string set under project properties.

This is an example where a typed dataset gets it's connection string.

[global::System.Diagnostics.DebuggerNonUserCodeAttribute()]

[global::System.CodeDom.Compiler.GeneratedCodeAttribute(

"System.Data.Design.TypedDataSetGenerator" , "15.0.0.0" )]

private void InitConnection() {

this ._connection = new global::System.Data.SqlClient.SqlConnection();

this ._connection.ConnectionString = global::ConnectionStrings2.

Properties.Settings.Default.NorthWindAzureConnectionString;

}

Both in typed data sets and when using the first examples shown this is how a connection string is stored in app.config.

<? xml version = "1.0" encoding = "utf-8" ?>

< configuration >

< configSections >

</ configSections >

< connectionStrings >

< add name = "ConnectionStrings2.Properties.Settings.NorthWindAzureConnectionString"

connectionString = "Data Source=KARENS-PC;Initial Catalog=NorthWindAzure1;Integrated Security=True;MultipleActiveResultSets=True;Application Name=EntityFramework"

providerName = "System.Data.SqlClient" />

</ connectionStrings >

< startup >

< supportedRuntime version = "v4.0" sku = ".NETFramework,Version=v4.7.2" />

</ startup >

</ configuration >

Normally an application requires one database but in more complex applications several connections may be needed and are stored as shown below.

<? xml version = "1.0" encoding = "utf-8" ?>

< configuration >

< configSections >

</ configSections >

< connectionStrings >

< add name = "ConnectionStrings1.Properties.Settings.North1" connectionString = "Data Source=KARENS-PC;Initial Catalog=NorthWindAzure;Integrated Security=True" />

< add name = "ConnectionStrings1.Properties.Settings.SomeConnection"

connectionString = "Data Source=KARENS-PC;Initial Catalog=NorthWindAzureForInserts;Integrated Security=True"

providerName = "System.Data.SqlClient" />

</ connectionStrings >

</ configuration >

Accessing North connection.

Properties.Settings.Default.North1;

Accessing SomeConnection.

Properties.Settings.Default.SomeConnection;

Dealing with secure connections

In the examples above connections are made with windows authentication, no user name or user password is needed, in this case encrypting the connection string within the configuration file is needed as per the following Microsoft documentation. Moving up a level, configure an instance of SqlCredential class to the connection which requires a fair amount of code which should be done when a user has been created in SQL-Server with appropriate permissions assigned to the user to access data.  For a easy to following code sample see the following TechNet article with full source code. Note, when using SqlCredential class it's not possible to stored connection properties in the application configuration file when a user password expires as the algorithm used to encrypt the connection section of the application configuration file will break, also it's better to prompt users for name and password.

Changing server and or initial catalog at runtime

There are several reasons for changing connection strings stored in the application configuration file after the application has been installed.

The main reason, the customer has data for the application in a server with a different server name then the developer server or that a server is renamed is another reason and the user has no technical ability to change the configuration file.

The following works with Entity Framework 6 Code First, database exists when the connection string is stored in the application configuration file.

User interface consideration

Customers with some technical ability should still be shielded from making changes that may break an application. One option is to create a interface to shield them and the program from making incorrect changes.

An interface (and included in the article's source code) is a basic template.

Dialog to change, in this case the server name.

To ensure a server exists from the server name entered methods shown below can assist to

• Ensure SQL-Server is currently running.
• The server name is available.

public class Helpers : BaseExceptionProperties

{

/// <summary>

/// Determine if a specific SQL-Server is available

/// </summary>

/// <param name="pServerName"></param>

/// <returns></returns>

public async Task< bool > SqlServerIsAvailable( string pServerName)

{

mHasException = false ;

bool success = false ;

try

{

await Task.Run(() =>

{

var sqlDataSourceEnumeratorInstance = SqlDataSourceEnumerator.Instance;

DataTable dt = sqlDataSourceEnumeratorInstance.GetDataSources();

// ReSharper disable once ConditionIsAlwaysTrueOrFalse

// ReSharper disable once InvertIf

if (dt != null )

{

if (dt.Rows.Count > 0)

{

var row = dt.AsEnumerable().FirstOrDefault(

dataRow =>

dataRow.Field< string >( "ServerName" ) == pServerName.ToUpper());

success = row != null ;

}

else

{

success = false ;

}

}

});

}

catch (Exception ex)

{

mHasException = true ;

mLastException = ex;

}

return success;

}

public async Task<List< string >> ServerNames()

{

mHasException = false ;

var serverNames = new List< string >();

try

{

await Task.Run(() =>

{

var sqlDataSourceEnumeratorInstance = SqlDataSourceEnumerator.Instance;

DataTable dt = sqlDataSourceEnumeratorInstance.GetDataSources();

// ReSharper disable once ConditionIsAlwaysTrueOrFalse

// ReSharper disable once InvertIf

if (dt != null )

{

if (dt.Rows.Count > 0)

{

foreach (DataRow row in dt.Rows)

{

serverNames.Add(row.Field< string >( "ServerName" ));

}

}

}

});

}

catch (Exception ex)

{

mHasException = true ;

mLastException = ex;

}

return serverNames;

}

/// <summary>

/// Determine if a specific service is running e.g.

/// SQL-Server: MSSQLServer

/// MSSQLSERVER

/// SQL Server Agent: SQLServerAgent

/// SQL Server Analysis Services: MSSQLServerOLAPService

/// SQL Server Browser: SQLBrowser

/// </summary>

/// <param name="serviceName">Service name to find</param>

/// <returns>True if found, false if not</returns>

public static bool IsWindowsServiceRunning( string serviceName)

{

var isRunning = false ;

var services = ServiceController.GetServices().Where(sc => sc.ServiceName.Contains( "SQL" )).ToList();

foreach (var service in services)

{

if (service.ServiceName == serviceName)

{

if (service.Status == ServiceControllerStatus.Running)

{

isRunning = true ;

}

}

}

return isRunning;

}

}

Example of calling SqlServerisAvailable from a button click event.

private async void ServerIsAvailableButton_Click( object sender, EventArgs e)

{

var builder = new SqlConnectionStringBuilder

{

ConnectionString = Properties.Settings.Default.NorthWind

};

var ops = new Helpers();

var result = await ops.SqlServerIsAvailable(builder.DataSource).ConfigureAwait( true );

}

Changing server and or initial catalog in code

The following uses several classes provided in the accompanying source code which resides in class project which by adding these class projects to a Visual Studio solution make it easy to change connection strings stored in the application file.

TableAdapter example

In this example a single connection string is stored in the application configuration file.

<? xml version = "1.0" encoding = "utf-8" ?>

< configuration >

< configSections >

</ configSections >

< connectionStrings >

< add name = "ConnectionStrings2.Properties.Settings.NorthWindAzureConnectionString"

connectionString = "Data Source=KARENS-PC;Initial Catalog=NorthWindAzure1;Integrated Security=True;MultipleActiveResultSets=True;Application Name=EntityFramework"

providerName = "System.Data.SqlClient" />

</ connectionStrings >

< startup >

< supportedRuntime version = "v4.0" sku = ".NETFramework,Version=v4.7.2" />

</ startup >

</ configuration >

Step 1 is to get a list of connections in the configuration file using the following method.

public List<ProjectConnection> Connections => ConnectionStrings.Cast<ConnectionStringSettings>().Select((item) => new ProjectConnection

{

Name = item.Name.Replace($ "{this.GetType().Namespace}.Properties.Settings." , "" ),

Provider = item.ProviderName,

ConnectionString = item.ConnectionString

}).ToList();

ProjectConnection class will store details about each connection in the list along with a method to assist populating a ListView control.

Connections method is called in form load event of the form accessing data.

var ops = new ConnectionHelper();

_projectConnections = ops.Connections;

/*

* Get connection name

*/

var connectionName = DefaultConnectionPath(CurrentNamespace(), _projectConnections[1].RealName());

Since TableAdapter components create strong typed classes when changing the initial catalog both databases must have the same table with the same column definitions. One may be for test while the other development environment. In the following button click event the code will toggle between two databases, display the current database, restart the app and load data from the selected database table.

private void ToggleCatalogButton_Click( object sender, EventArgs e)

{

var ops = new ConnectionHelper();

_projectConnections = ops.Connections;

var connectionName = DefaultConnectionPath(CurrentNamespace(), _projectConnections[1].RealName());

/*

* Get connection properties for the current connection string in connectionName

*/

var properties = ops.Properties(connectionName);

/*

* Toggle between two databases - both must match for the TableAdapter classes

*/

ops.ChangeInitialCatalog(connectionName, properties.InitialCatalog == "NorthWindAzure1" ?

"NorthWindAzure3" :

"NorthWindAzure1" );

connectionName = DefaultConnectionPath(CurrentNamespace(), _projectConnections[1].RealName());

InitialCatalogLabel.Text = $ "Current catalog '{ops.Properties(connectionName).InitialCatalog}'" ;

// restart app to use no catalog

Application.Restart();

}

User interface example

This example uses the example shown above with a listview with buttons which open a secondary form to obtain a server name. Step 1 is to create an instance of the class ConnectionHelper. Next call ChangeServer method with the connection string for the first parameter and the server name for the second parameter.

If both server name and initial catalog need to be changed, call the overload of ChangeServer method. First parameter is the connection name, second parameter is the server name while the last parameter is the catalog name.

Helper methods

When working with connection strings stored in a Windows Form project and the modifications to that project's configuration file are performed in a class project with a different namespace then the Windows Form project's namespace conventional methods to access and modify the connection string must keep this in consideration by using method which can get the Window Form project namespace and open it's application configuration file rather than when using conventional methods which would attempt to access connection strings in the class project configuration file.

The following method which resides in the class project gets the namespace of the calling Window Form project's namespace.

[MethodImpl(MethodImplOptions.NoInlining)]

public static string CurrentNamespace()

{

return Assembly.GetCallingAssembly().EntryPoint.DeclaringType.Namespace;

}

The following method creates a string path to the connection string in the Windows Form project configuration file.

/// <summary>

/// Create path to specific setting in caller's configuration file

/// </summary>

/// <param name="projectNamespace">Namespace where configuration file resides</param>

/// <param name="sender"></param>

/// <returns></returns>

public static string DefaultConnectionPath( string projectNamespace, string sender)

{

return $ "{projectNamespace}.Properties.Settings.{sender}" ;

}

The following method asserts if a specific connection string exists in the Windows Form project from the class project.

/// <summary>

/// Determine if a connection string exists

/// </summary>

/// <param name="value"></param>

/// <param name="key">ConnectionString name</param>

/// <returns>true if connection string exists, false if not found</returns>

/// <remarks>

/// Throws an exception if not found, we ignore this.

/// </remarks>

public static bool HasConnectionString( this ConnectionStringSettingsCollection value, string key)

{

try

{

var location = Assembly.GetEntryAssembly().Location;

return value[$ "{Path.GetFileNameWithoutExtension(location)}.Properties.Settings.{key}" ].ConnectionString.Length > 0;

}

catch

{

return false ;

}

}

Container classes

The following class is responsible to store information for a connection string read from a application configuration file used in all examples provided.

using System.Data.SqlClient;

namespace DataConnectionLibrary

{

public class ConnectionProperties

{

/// <summary>

/// Server name

/// </summary>

public string DataSource { get ; set ; }

/// <summary>

/// Database to access

/// </summary>

public string InitialCatalog { get ; set ; }

/// <summary>

/// User id (not used)

/// </summary>

public string UserId { get ; set ; }

/// <summary>

/// User password (not used)

/// </summary>

public string Password { get ; set ; }

/// <summary>

/// Name of local database such as one stored for a database

/// stored in the application folder.

/// </summary>

public string AttachDbFilename { get ; set ; }

/// <summary>

/// True if the database is an attached database, false if not

/// </summary>

public bool IsAttached => ! string .IsNullOrWhiteSpace(AttachDbFilename);

/// <summary>

/// Describes the different SQL authentication methods that can be used

/// by a client connecting to Azure SQL Database.

/// </summary>

/// <remarks>

/// </remarks>

public SqlAuthenticationMethod Authentication { get ; set ; }

/// <summary>

/// True if integrated security is used, false if not

/// </summary>

public bool IntegratedSecurity { get ; set ; }

/// <summary>

/// Indicates if the object is valid

/// </summary>

public bool IsValid { get ; set ; }

}

}

Implementing in a Visual Studio solution

Add the class project DataConnectionLibrary to a Visual Studio solution followed by checking that the .NET Framework uses in projects which will use code in the DataConnectionLibrary class project match.

Add the following NuGet package BaseConnectionLibrary to the solution when working with the SqlServerUtilityLibrary where this library is optional

Alternate method to change connections at runtime

When creating a TableAdapter in Visual Studio the following dialog appears when a new connection is required. While there is no dialog in the Visual Studio toolbox the following will explain how to implement in a Windows Form project.

Important note: All code in the class projects are copyrighted to Microsoft while the form example project is not copyrighted to Microsoft.

For experience developers who are interested in modifying the code this is possible as the source code is available in the following GitHub repository. In the following example a context menu has been added to insert a default server name.

The following method creates the dialog with a parameter passed in which will be the connection string to work with.

public bool GetConnection( ref string DataSource, bool SaveConfiguration = false )

{

var success = false ;

var dcd = new DataConnectionDialog();

var dcs = new DataConnectionConfiguration( null );

dcs.LoadConfiguration(dcd);

if (DataConnectionDialog.Show(dcd) == DialogResult.OK)

{

DbProviderFactory factory = DbProviderFactories.GetFactory(dcd.SelectedDataProvider.Name);

using (var connection = factory.CreateConnection())

{

connection.ConnectionString = dcd.ConnectionString;

DataSource = connection.DataSource;

connection.Open();

var cmd = connection.CreateCommand();

cmd.CommandType = CommandType.Text;

cmd.CommandText = "SELECT * FROM INFORMATION_SCHEMA.TABLES" ;

var dt = new DataTable();

dt.Load(cmd.ExecuteReader());

TableNames = dt.AsEnumerable()

.Select(row => row.Field< string >( "table_name" ))

.OrderBy(field => field)

.ToList();

}

var builder = new SqlConnectionStringBuilder() { ConnectionString = dcd.ConnectionString };

ServerName = builder.DataSource;

InitialCatalog = builder.InitialCatalog;

if (SaveConfiguration)

{

dcs.SaveConfiguration(dcd);

}

if (TableNames.Count > 0)

{

success = true ;

}

}

return success;

}

When the method returns true the following properties will contain the server name and default catalog to create a connection string.

/// <summary>

/// SQL-Server name returned in GetConnection method

/// </summary>

public string ServerName { get ; set ; }

/// <summary>

/// SQL-Server database returned in GetConnection method

/// </summary>

public string InitialCatalog { get ; set ; }

Summary

In this article basics of setting up connection strings in code both using hard coded connections string and connection strings stored in an application configuration file. How to change connection strings stored in an application configuration file at runtime using classes provided in the accompanying source code and finally the Microsoft Connection dialog to use in your projects.

TableAdapter component should only be used to get up and running for simple projects and should be avoided in general unless time is taken to completely learn the component and ramifications when working with a relation database.

See also

Related resources

Source code

How to Connect Sql Server Using Sql Developer

Source: https://social.technet.microsoft.com/wiki/contents/articles/53379.c-working-with-sql-server-connection.aspx

Share :