Microsoft SQL Server Connection Strings

In this article we will learn about Microsoft SQL Server Connection Strings. Means we discuss here in how many ways we can get the connection string of SQL server in the .NET applications. Please read the previous article on Difference between ExecuteReader ExecuteScalar and ExecuteNonQuery.

SQL Server database is more compatible database with Ado.Net. Since SQL Server and Ado.Net, both are the product of Microsoft. Basically it is not easy to remember different database connection strings in Ado.Net. In Ado.net to make connection to SQL Server we have multiple options. We have different connection string to connect to the SQL Server database. So I am sharing some connection strings to connect to the SQL Server database using different drivers.

For more details please visit SqlConnection.ConnectionString,

Different types of Microsoft SQL Server Connection Strings

Using ODBC

This topic shows you how to connect to an ODBC data source from the Choose a Data Source or Choose a Destination page of the SQL Server Import and Export Wizard.

You may have to download the ODBC driver you need from Microsoft or from a third party.

You may also have to look up the required connection info that you have to provide. This third-party site – The Connection Strings Reference – contains sample connection strings and more info about data providers and the connection info they require.

// ODBC -- Standard Connection
using System.Data.Odbc;
OdbcConnection conn = new OdbcConnection();
conn.ConnectionString = "Driver={SQL Server}; Server=ServerName; DataBase=DataBaseName; Uid=UserName; Pwd=Secret";
conn.Open();
 // ODBC -- Trusted Connection
using System.Data.Odbc;
OdbcConnection conn = new OdbcConnection();
conn.ConnectionString = "Driver={SQL Server}; Server=ServerName; DataBase=DataBaseName; Uid=admin; Pwd=password"; 
conn.Open();
// or
OdbcConnection conn = new OdbcConnection();
conn.ConnectionString = "Driver={SQL Server}; Server=ServerName; DataBase=DataBaseName; Trusted_Connection=Yes;"; 
conn.Open(); 

Using OLEDB

The C# OleDbConnection instance takes Connection String as argument and pass the value to the Constructor statement. An instance of the C# OleDbConnection class is supported the OLEDB Data Provider.

When the connection is established between C# application and the specified Data Source, SQL Commands will execute with the help of the Connection Object and retrieve or manipulate data in the database. Once the Database activities is over Connection should be closed and release from the data source resources .

The Close() method in the OleDbConnection class is used to close the Database Connection. The Close method Rolls Back any pending transactions and releases the Connection from the Database connected by the OLEDB Data Provider.

// OleDb -- Standard Connection

using System.Data.OleDb;

OleDbConnection conn = new OleDbConnection();

conn.ConnectionString = "Driver=SQLOLEDB; Data Source=ServerName; Initial Catalog=DataBaseName; User id=UserName; Password=Secret;"; 

conn.Open();

 // OleDb -- Trusted Connection

using System.Data.OleDb;

OleDbConnection conn = new OleDbConnection();

conn.ConnectionString = "Driver=SQLOLEDB; Data Source=ServerName; Initial Catalog=DataBaseName; Integrated Security=SSPI;"; 

conn.Open(); 

Using .Net Data Provider

A . NET data provider is a software library consisting of classes that provide data access services such as connecting to a data source, executing commands at a data source and fetching data from a data source with support to execute commands within transactions.

// .NET DataProvider -- Standard Connection

using System.Data.SqlClient;

SqlConnection conn = new SqlDbConnection();

conn.ConnectionString ="Data Source=ServerName; Initial Catalog=DataBaseName; User id=UserName; Password=Secret;"; 

conn.Open();

 // .NET DataProvider -- Trusted Connection

using System.Data.SqlClient;

SqlConnection conn = new SqlConnection();

conn.ConnectionString = "Data Source=ServerName; Initial Catalog=DataBaseName; Integrated Security=SSPI;"; 

conn.Open(); 

Conclusion

So far in this article we learnt about Microsoft SQL Server Connection Strings. We discussed here how many different provider are available to use in .NET.