ASP.NET

How to connect database in ASP.NET?

asp.net-db

Connection Strings

Database providers require some form of connection string to connect to the database. Sometimes this connection string contains sensitive information that needs to be protected. Connection string needs to change when application moves from development environments to production environment, such as development, testing, and production.

ASP.NET Core

In ASP.NET Core the configuration system is very flexible, and the connection string could be stored in appsettings.json, an environment variable, the user secret store, or another configuration source. The following example shows the connection string stored in appsettings.json.

JSONCopy
{
  “ConnectionStrings”: {
  “EFSourceDatabase”: “Server= (localdb)\\mssqllocaldb;Database=EFSource;Trusted_Connection=True;”
  },
}

The context is typically configured in Startup.cs with the connection string being read from configuration. Note the GetConnectionString () method looks for a configuration value whose key is ConnectionStrings :< connection string name>. You need to import the Microsoft.Extensions.Configuration namespace to use this extension method.

C#Copy
public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<EFSourceContext>(options =>         options.UseSqlServer(Configuration.GetConnectionString(“EFSourceDatabase”)));
}

WinForms & WPF Applications

WinForms, WPF, and ASP.NET 4 applications have a tried and tested connection string pattern. The connection string should be added to your application’s App.config file (Web.config if you are using ASP.NET). If your connection string contains sensitive information, such as username and password, you can protect the contents of the configuration file using the Secret Manager tool.

XMLCopy
<?xml version=”1.0″ encoding=”utf-8″?>
<configuration>
  <connectionStrings>
    <add name=”EFSourceDatabase”  connectionString=”Server=(localdb)\mssqllocaldb;Database=EFSource;Trusted_Connection=True;” />
  </connectionStrings>
</configuration>

The providerName setting is not required on EntityFramework (EF) Core connection strings stored in App.config because the database provider is configured via code.

Read the connection string using the ConfigurationManager API in your context’s OnConfiguring method. You may need to add a reference to the System.Configuration framework assembly to be able to use this API.

C#Copy
public class EFSourceContext : DbContext
{
    public DbSet<User> User { get; set; }
    public DbSet<Post> Posts { get; set; }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {     
   optionsBuilder.UseSqlServer (ConfigurationManager.ConnectionStrings[“EFSourceDatabase”].ConnectionString);
    }
}

Universal Windows Platform (UWP)

Connection strings in a UWP application are typically a SQLite connection that just specifies a local filename. They typically do not contain sensitive information, and do not need to be changed as an application is deployed. These connection strings are usually fine to be left in code, as shown below. If you wish to move them out of code then UWP supports the concept of settings, see the App Settings section of the UWP documentation for details.

C#Copy
public class EFSourceContext : DbContext
{
    public DbSet<User> User { get; set; }
    public DbSet<Post> Posts { get; set; }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
       optionsBuilder.UseSqlite(“Data Source=EFSource.db”);
    }
}