Use C# to create a SQL database with the SQL Database Library for .NET

Learn how to use C# to create an Azure SQL database with the Microsoft Azure SQL Management Library for .NET. This article describes how to create a single database with SQL and C#. To create elastic pools, see Create an elastic pool.

The Azure SQL Database Management Library for .NET provides an Azure Resource Manager-based API that wraps the Resource Manager-based SQL Database REST API.

Note

Many new features of SQL Database are only supported when you are using the Azure Resource Manager deployment model, so you should always use the latest Azure SQL Database Management Library for .NET (docs | NuGet Package). The older classic deployment model based libraries are supported for backward compatibility only, so we recommend you use the newer Resource Manager based libraries.

To complete the steps in this article, you need the following:

  • An Azure subscription. If you need an Azure subscription simply click FREE ACCOUNT at the top of this page, and then come back to finish this article.
  • Visual Studio. For a free copy of Visual Studio, see the Visual Studio Downloads page.
Note

This article creates a new, blank SQL database. Modify the CreateOrUpdateDatabase(...) method in the following sample to copy databases, scale databases, create a database in a pool, etc. For more information, see DatabaseCreateMode and DatabaseProperties classes.

Create a console app and install the required libraries

  1. Start Visual Studio.
  2. Click File > New > Project.
  3. Create a C# Console Application and name it: SqlDbConsoleApp

To create a SQL database with C#, load the required management libraries (using the package manager console):

  1. Click Tools > NuGet Package Manager > Package Manager Console.
  2. Type Install-Package Microsoft.Azure.Management.Sql -Pre to install the latest Microsoft Azure SQL Management Library.
  3. Type Install-Package Microsoft.Azure.Management.ResourceManager -Pre to install the Microsoft Azure Resource Manager Library.
  4. Type Install-Package Microsoft.Azure.Common.Authentication -Pre to install the Microsoft Azure Common Authentication Library.
Note

The examples in this article use a synchronous form of each API request and block until completion of the REST call on the underlying service. There are async methods available.

Create a SQL Database server, firewall rule, and SQL database - C# example

The following sample creates a resource group, server, firewall rule, and a SQL database. See, Create a service principal to access resources to get the _subscriptionId, _tenantId, _applicationId, and _applicationSecret variables.

Replace the contents of Program.cs with the following, and update the {variables} with your app values (do not include the {}).

using Microsoft.Azure;
using Microsoft.Azure.Management.ResourceManager;
using Microsoft.Azure.Management.ResourceManager.Models;
using Microsoft.Azure.Management.Sql;
using Microsoft.Azure.Management.Sql.Models;
using Microsoft.IdentityModel.Clients.ActiveDirectory;
using System;

namespace SqlDbConsoleApp
{
class Program
   {
    // For details about these four (4) values, see
    // https://azure.microsoft.com/documentation/articles/resource-group-authenticate-service-principal/
    static string _subscriptionId = "{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}";
    static string _tenantId = "{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}";
    static string _applicationId = "{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}";
    static string _applicationSecret = "{your-password}";

    // Create management clients for the Azure resources your app needs to work with.
    // This app works with Resource Groups, and Azure SQL Database.
    static ResourceManagementClient _resourceMgmtClient;
    static SqlManagementClient _sqlMgmtClient;

    // Authentication token
    static AuthenticationResult _token;

    // Azure resource variables
    static string _resourceGroupName = "{resource-group-name}";
    static string _resourceGrouplocation = "{Azure-region}";

    static string _serverlocation = _resourceGrouplocation;
    static string _serverName = "{server-name}";
    static string _serverAdmin = "{server-admin-login}";
    static string _serverAdminPassword = "{server-admin-password}";

    static string _firewallRuleName = "{firewall-rule-name}";
    static string _startIpAddress = "{0.0.0.0}";
    static string _endIpAddress = "{255.255.255.255}";

    static string _databaseName = "{dbfromcsarticle}";
    static string _databaseEdition = DatabaseEditions.Basic;
    static string _databasePerfLevel = ""; // "S0", "S1", and so on here for other tiers


    static void Main(string[] args)
    {
        // Authenticate:
        _token = GetToken(_tenantId, _applicationId, _applicationSecret);
        Console.WriteLine("Token acquired. Expires on:" + _token.ExpiresOn);

        // Instantiate management clients:
        _resourceMgmtClient = new ResourceManagementClient(new Microsoft.Rest.TokenCredentials(_token.AccessToken));
        _sqlMgmtClient = new SqlManagementClient(new Microsoft.Rest.TokenCredentials(_token.AccessToken)) { SubscriptionId = _subscriptionId };

        Console.WriteLine("Resource group...");
        ResourceGroup rg = CreateOrUpdateResourceGroup(_resourceMgmtClient, _subscriptionId, _resourceGroupName, _resourceGrouplocation);
        Console.WriteLine("Resource group: " + rg.Id);


        Console.WriteLine("Server...");
        Server sgr = CreateOrUpdateServer(_sqlMgmtClient, _resourceGroupName, _serverlocation, _serverName, _serverAdmin, _serverAdminPassword);
        Console.WriteLine("Server: " + sgr.Id);

        Console.WriteLine("Server firewall...");
        FirewallRule fwr = CreateOrUpdateFirewallRule(_sqlMgmtClient, _resourceGroupName, _serverName, _firewallRuleName, _startIpAddress, _endIpAddress);
        Console.WriteLine("Server firewall: " + fwr.Id);

        Console.WriteLine("Database...");
        Database dbr = CreateOrUpdateDatabase(_sqlMgmtClient, _resourceGroupName, _serverName, _databaseName, _databaseEdition, _databasePerfLevel);
        Console.WriteLine("Database: " + dbr.Id);


        Console.WriteLine("Press any key to continue...");
        Console.ReadKey();
    }

    static ResourceGroup CreateOrUpdateResourceGroup(ResourceManagementClient resourceMgmtClient, string subscriptionId, string resourceGroupName, string resourceGroupLocation)
    {
        ResourceGroup resourceGroupParameters = new ResourceGroup()
        {
            Location = resourceGroupLocation,
        };
        resourceMgmtClient.SubscriptionId = subscriptionId;
        ResourceGroup resourceGroupResult = resourceMgmtClient.ResourceGroups.CreateOrUpdate(resourceGroupName, resourceGroupParameters);
        return resourceGroupResult;
    }

    static Server CreateOrUpdateServer(SqlManagementClient sqlMgmtClient, string resourceGroupName, string serverLocation, string serverName, string serverAdmin, string serverAdminPassword)
    {
        Server serverParameters = new Server()
        {
            Location = serverLocation,
            AdministratorLogin = serverAdmin,
            AdministratorLoginPassword = serverAdminPassword,
            Version = "12.0"
        };
        Server serverResult = sqlMgmtClient.Servers.CreateOrUpdate(resourceGroupName, serverName, serverParameters);
        return serverResult;
    }

    static FirewallRule CreateOrUpdateFirewallRule(SqlManagementClient sqlMgmtClient, string resourceGroupName, string serverName, string firewallRuleName, string startIpAddress, string endIpAddress)
    {
        FirewallRule firewallParameters = new FirewallRule()
        {
            StartIpAddress = startIpAddress,
            EndIpAddress = endIpAddress
        };
        FirewallRule firewallResult = sqlMgmtClient.FirewallRules.CreateOrUpdate(resourceGroupName, serverName, firewallRuleName, firewallParameters);
        return firewallResult;
    }

    static Database CreateOrUpdateDatabase(SqlManagementClient sqlMgmtClient, string resourceGroupName, string serverName, string databaseName, string databaseEdition, string databasePerfLevel)
    {
        // Retrieve the server that will host this database
        Server currentServer = sqlMgmtClient.Servers.Get(resourceGroupName, serverName);

        // Create a database: configure create or update parameters and properties explicitly
        Database newDatabaseParameters = new Database()
        {
            Location = currentServer.Location,
            CreateMode = CreateMode.Default,
            Edition = databaseEdition,
            RequestedServiceObjectiveName = databasePerfLevel

        };
        Database dbResponse = sqlMgmtClient.Databases.CreateOrUpdate(resourceGroupName, serverName, databaseName, newDatabaseParameters);
        return dbResponse;
    }



    private static AuthenticationResult GetToken(string tenantId, string applicationId, string applicationSecret)
    {
        AuthenticationContext authContext = new AuthenticationContext("https://login.windows.net/" + tenantId);
        _token = authContext.AcquireToken("https://management.core.windows.net/", new ClientCredential(applicationId, applicationSecret));
        return _token;
    }
  }
}

Create a service principal to access resources

The following PowerShell script creates the Active Directory (AD) application and the service principal that we need to authenticate our C# app. The script outputs values we need for the preceding C# sample. For detailed information, see Use Azure PowerShell to create a service principal to access resources.

# Sign in to Azure.
Add-AzureRmAccount

# If you have multiple subscriptions, uncomment and set to the subscription you want to work with.
#$subscriptionId = "{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}"
#Set-AzureRmContext -SubscriptionId $subscriptionId

# Provide these values for your new AAD app.
# $appName is the display name for your app, must be unique in your directory.
# $uri does not need to be a real uri.
# $secret is a password you create.

$appName = "{app-name}"
$uri = "http://{app-name}"
$secret = "{app-password}"

# Create a AAD app
$azureAdApplication = New-AzureRmADApplication -DisplayName $appName -HomePage $Uri -IdentifierUris $Uri -Password $secret

# Create a Service Principal for the app
$svcprincipal = New-AzureRmADServicePrincipal -ApplicationId $azureAdApplication.ApplicationId

# To avoid a PrincipalNotFound error, I pause here for 15 seconds.
Start-Sleep -s 15

# If you still get a PrincipalNotFound error, then rerun the following until successful. 
$roleassignment = New-AzureRmRoleAssignment -RoleDefinitionName Contributor -ServicePrincipalName $azureAdApplication.ApplicationId.Guid


# Output the values we need for our C# application to successfully authenticate

Write-Output "Copy these values into the C# sample app"

Write-Output "_subscriptionId:" (Get-AzureRmContext).Subscription.SubscriptionId
Write-Output "_tenantId:" (Get-AzureRmContext).Tenant.TenantId
Write-Output "_applicationId:" $azureAdApplication.ApplicationId.Guid
Write-Output "_applicationSecret:" $secret

Next steps

Now that you've tried SQL Database and set up a database with C#, you're ready for the following articles:

Additional Resources