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.
SQL Server Connection string in .NET Core AppSettings
We will need to use below JSON in appsettings.json to add connection string in .NET Core
{
"ConnectionStrings": {
"DefaultConnection": "Server=tcp:xxUAT.domain.com;Initial Catalog=IdentityDB;MultipleActiveResultSets=true;User ID=xx;Password=xx"
}
}OR
{
"ConnectionStrings": {
"WindowsAuth": "Data Source=Desktop-11\\SQL2017;Initial Catalog=SampleDB; Integrated Security=true",
"DefaultConnection": "Data Source=Desktop-11\\SQL2017;Initial Catalog=SampleDB; User ID=sa;Password=pass1234"
}
}
To read above connection string in C# .NET Core Controller, you can have C# code in controller as below
public class TestController : Controller
{
private IConfiguration Configuration;
public TestController(IConfiguration _configuration)
{
Configuration = _configuration;
}
public IActionResult Index()
{
string connString = this.Configuration.GetConnectionString("DefaultConnection");
return View();
}
}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="Data Source=ServerName;Integrated Security=False;User Id=userid;Password=password;MultipleActiveResultSets=True"" />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.dllwhich 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(); } } } }?
That's it, if we have missed any Database connection string in SQL Server, let us know in comments sections.
You may also like to read:
Query in Sql server to list all stored procedures in all databases
How to backup SQL Server database ? (Various Ways explained)
Check database size in Sql server ( Various Ways explained)
Download and Install SQL Server (Step by Step procedure)
Download and Install SQL Server Management Studio (Step by Step)
Buy us a coffee
Become a Patron