Examples for SQL Server Connection String in C#

In this article, I have provided various samples of using SQL server connection string in C#, to connect asp.net web applications, console or using SQL server connection string using integrated security.

MS SQL server connection string using C#

.NET DataProvider – Standard Connection

using System.Data.SqlClient;

var conn = new SqlDbConnection();
conn.ConnectionString = 
"Data Source=YourServerName;" + 
"Initial Catalog=YourDataBaseName;" + 
"User id=YourDBUserName;" + 
"Password=YourDBSecret;"; 
conn.Open();

.NET DataProvider — Trusted Connection

using System.Data.SqlClient;

var conn = new SqlConnection();
conn.ConnectionString = 
"Data Source=YourServerName;" + 
"Initial Catalog=YourDBName;" + 
"Integrated Security=SSPI;"; 
conn.Open();

.NET DataProvider – via IP Address

using System.Data.SqlClient;

var conn = new SqlConnection();
conn.ConnectionString = 
"Network Library=DBMSSOCN;" + 
"Data Source=xxx.xxx.xxx.xxx,1433;" + 
"Initial Catalog=YourDBName;" + 
"User Id=YourUserName;" + 
"Password=YourPassword;"; 
conn.Open();

SQL server express connection string

.NET Data Provider – Default Relative Path – Standard Connection

using System.Data.SqlClient; // add reference in C# file

var conn = new SqlConnection();
conn.ConnectionString = 
"Data Source=.\SQLExpress;" + 
"User Instance=true;" + 
"User Id=UserName;" + //replace "UserName" with your DB Username
"Password=Secret;" + //replace "Secret" with your DB password
"AttachDbFilename=|DataDirectory|DataBaseName.mdf;"
conn.Open();

.NET Data Provider – Default Relative Path – Trusted Connection

using System.Data.SqlClient;

var conn = new SqlConnection();
conn.ConnectionString = 
"Data Source=.\SQLExpress;" + 
"User Instance=true;" + 
"Integrated Security=true;" + 
"AttachDbFilename=|DataDirectory|DataBaseName.mdf;"
conn.Open();

.NET Data Provider – Custom Relative Path – Standard Connection

using System.Data.SqlClient;

AppDomain.CurrentDomain.SetData(
"DataDirectory", "C:\MyPath\");
var conn = new SqlConnection();
conn.ConnectionString = 
"Data Source=.\SQLExpress;" + 
"User Instance=true;" + 
"User Id=UserName;" + 
"Password=Secret;" + 
"AttachDbFilename=|DataDirectory|DataBaseName.mdf;"
conn.Open();

.NET Data Provider – Absolute Path – Standard Connection

using System.Data.SqlClient;

var conn = new SqlConnection();
conn.ConnectionString = 
"Data Source=.\SQLExpress;" + 
"User Instance=true;" + 
"User Id=UserName;" + //replace "UserName" with your DB Username
"Password=Secret;" + //replace "Secret" with your DB password
"AttachDbFilename=C:\MyPath\DataBaseName.mdf;"
conn.Open();

.NET Data Provider – Absolute Path – Trusted Connection

using System.Data.SqlClient;

var conn = new SqlConnection();
conn.ConnectionString = 
"Data Source=.\SQLExpress;" + 
"User Instance=true;" + 
"Integrated Security=true;" + 
"AttachDbFilename=C:\MyPath\DataBaseName.mdf;"
conn.Open();

To convert one of SQL server express connection strings to LocalDB, make the following changes:

• Change “Data Source=.\SQLEXPRESS” to “Data Source=(LocalDB\v11.0)”.
• This change assumes that you installed LocalDB with the default instance name.
• Remove “User Instance=True” if it is present. Also, remove the preceding or following semicolon (;).

MySQL connection string examples in C#

ODBC DSN

using System.Data.Odbc;

var conn = new OdbcConnection();
conn.ConnectionString = 
"Dsn=DsnName;" + //replace DsnName with your local db server name
"Uid=UserName;" + //replace UserNamewith your local db user name
"Pwd=Secret;"; //replace Secret with your local db password name
conn.Open();

Using Connector/NET instead of ODBC

using MySql.Data.MySqlClient;

string connStr = "server=server;user=user;database=db;password=yourpassword;";
MySqlConnection conn = new MySqlConnection(connStr);
conn.Open();

ODBC – MyODBC Driver – remote database

using System.Data.Odbc;

var conn = new OdbcConnection();
conn.ConnectionString = 
"Driver={MySql};" + 
"Server=db.domain.com;" + 
"Option=131072;" + 
"Port=3306;" + 
"Stmt=;" + 
"DataBase=YourDatabaseName;" + 
"Uid=YourDBUserName;" + 
"Pwd=YourDBPassword;" 
conn.Open();

ODBC – MyODBC Driver – local database

using System.Data.Odbc;

var conn = new OdbcConnection();
conn.ConnectionString = 
"Driver={MySql};" + 
"Server=localhost;" + 
"Option=16834;" + 
"DataBase=DataBaseName;" 
conn.Open();

Using OLEDB

using System.Data.OleDb;

var conn = new OleDbConnection();
conn.ConnectionString = 
"Provider=MySqlProviderName;" + 
"Data Source=YourServerName;" + 
"User id=YourDBUserName;" + 
"Password=YourDBPassword;" 
conn.Open();

SQL Server connection string in web.Config File

The following example is for a SQL Server database using SQL Server security (log on to the server by using user credentials in the connection string). The example assumes that you are connecting to the default SQL Server instance on the server.

<add name="ConnectionStringName"
providerName="System.Data.SqlClient"
connectionString="Data Source=ServerName;Initial Catalog=DatabaseName;Integrated Security=False;User Id=userid;Password=password;MultipleActiveResultSets=True" />

The following example is for a SQL Server database using integrated security (log on to the server using the credentials of the Windows user account). The example specifies a named instance of SQL Server.

<add name="ConnectionStringName"
providerName="System.Data.SqlClient"
connectionString="Data Source=ServerName\InstanceName;Initial Catalog=DatabaseName;Integrated Security=True;MultipleActiveResultSets=True" />

Entity Framework Database First or Model First Connection String Example

<add name="ConnectionStringName"
providerName="System.Data.EntityClient"
connectionString="metadata=res://*/ ContextClass.csdl|res://*/ ContextClass.ssdl|res://*/ ContextClass.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=ServerName;Integrated Security=False;User Id=userid;Password=password;MultipleActiveResultSets=True&quot;" />

The part before the first " symbol specifies the conceptual model, data schema, and mapping information that is stored in the .edmx file. The part between the two " symbols is the database connection string. In this example, the database connection string is the same as the example for SQL Server using SQL Server security.

ContextClass in this example represents the fully qualified context class name (for example, namespace.classname).

SQL Server Compact Connection String Example

The following example is for a SQL Server Compact database located in the App_Data folder.

<add name="ConnectionStringName"
providerName="System.Data.SqlServerCe.4.0"
connectionString="Data Source=|DataDirectory|\DatabaseFileName.sdf" />

Local DB connection string in XML format

<add name="ConnectionStringName"
providerName="System.Data.SqlClient"
connectionString="Data Source=(LocalDB)\v11.0;AttachDbFileName=|DataDirectory|\DatabaseFileName.mdf;InitialCatalog=DatabaseName;Integrated Security=True;MultipleActiveResultSets=True" />

MySQL connection string in XML format (ASP.NET)

<connectionstrings>
<add name="AppNameCString" connectionstring="Data Source=mysql#.gear.host;Initial Catalog=DBName;User Id=DBUser;Password=myPassword" providername="MySql.Data.MySqlClient" />
</connectionstrings>

To connect to MySQL in ASP.NET, you need to follow these steps:

• Download MySQL from https://dev.mysql.com/downloads/connector/net/ and install it. ( You may need to login/register to download it)
• Once you have downloaded and installed MySQL, naviagte to Windows Explorer and look for the MySql installation in the Program Files folder of your Windows drive.
• You will find a folder for MySQL Connector and inside that you will find the MySql.Data.dll which you need to copy inside the BIN folder of your project.
• That’s it, now you can use MySQL to store and retrieve data.
• C# Code example for MySQL, considering “AppNameC” = database connection string name in web.config

 string constr = ConfigurationManager.ConnectionStrings["AppNameC"].ConnectionString;
           using (MySqlConnection con = new MySqlConnection(constr))
           {
                   using (MySqlCommand cmd = new MySqlCommand("SELECT * FROM Students"))
                   {
                           using (MySqlDataAdapter sda = new MySqlDataAdapter())
                          {
                                  cmd.Connection = con;
                                  sda.SelectCommand = cmd;
                                  using (DataTable dt = new DataTable())
                                  {
                                          sda.Fill(dt);
                                         //fill data in gridview in asp .net
                                          GridView1.DataSource = dt;
                                          GridView1.DataBind();
                                  }
                          }
                   }
          }?

Conclusion

That’s all. There are few examples using using SQL server connection string in C#. If this article helpful, you can always share the article. Thank you and we will share other interesting tutorial.