Skip to content

Latest commit

 

History

History
134 lines (104 loc) · 5.84 KB

sql-database-connect-query-dotnet-visual-studio.md

File metadata and controls

134 lines (104 loc) · 5.84 KB
title description services ms.service ms.subservice ms.custom ms.devlang ms.topic author ms.author ms.reviewer manager ms.date
Use Visual Studio and .NET to query Azure SQL Database | Microsoft Docs
This topic shows you how to use Visual Studio to create a program that connects to an Azure SQL Database and query it using Transact-SQL statements.
sql-database
sql-database
development
dotnet
quickstart
CarlRabeler
carlrab
craigg
11/01/2018

Quickstart: Use .NET (C#) with Visual Studio to connect and query an Azure SQL database

This quickstart demonstrates how to use the .NET framework to create a C# program with Visual Studio to connect to an Azure SQL database and use Transact-SQL statements to query data.

Prerequisites

To complete this quickstart, make sure you have the following:

[!INCLUDE prerequisites-create-db]

SQL server connection information

[!INCLUDE prerequisites-server-connection-info]

For ADO.NET

  1. Continue by clicking Show database connection strings.

  2. Review the complete ADO.NET connection string.

    ADO.NET connection string

Important

You must have a firewall rule in place for the public IP address of the computer on which you perform this tutorial. If you are on a different computer or have a different public IP address, create a server-level firewall rule using the Azure portal.

Create a new Visual Studio project

  1. In Visual Studio, choose File, New, Project.
  2. In the New Project dialog, and expand Visual C#.
  3. Select Console App and enter sqltest for the project name.
  4. Click OK to create and open the new project in Visual Studio
  5. In Solution Explorer, right-click sqltest and click Manage NuGet Packages.
  6. On the Browse tab, search for System.Data.SqlClient and, when found, select it.
  7. In the System.Data.SqlClient page, click Install.
  8. When the install completes, review the changes and then click OK to close the Preview window.
  9. If a License Acceptance window appears, click I Accept.

Insert code to query SQL database

  1. Switch to (or open if necessary) Program.cs

  2. Replace the contents of Program.cs with the following code and add the appropriate values for your server, database, user, and password.

using System;
using System.Data.SqlClient;
using System.Text;

namespace sqltest
{
    class Program
    {
        static void Main(string[] args)
        {
            try 
            { 
                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
                builder.DataSource = "your_server.database.windows.net"; 
                builder.UserID = "your_user";            
                builder.Password = "your_password";     
                builder.InitialCatalog = "your_database";

                using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
                {
                    Console.WriteLine("\nQuery data example:");
                    Console.WriteLine("=========================================\n");
                    
                    connection.Open();       
                    StringBuilder sb = new StringBuilder();
                    sb.Append("SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName ");
                    sb.Append("FROM [SalesLT].[ProductCategory] pc ");
                    sb.Append("JOIN [SalesLT].[Product] p ");
                    sb.Append("ON pc.productcategoryid = p.productcategoryid;");
                    String sql = sb.ToString();

                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                Console.WriteLine("{0} {1}", reader.GetString(0), reader.GetString(1));
                            }
                        }
                    }                    
                }
            }
            catch (SqlException e)
            {
                Console.WriteLine(e.ToString());
            }
            Console.ReadLine();
        }
    }
}

Run the code

  1. Press F5 to run the application.
  2. Verify that the top 20 rows are returned and then close the application window.

Next steps