您现在访问的是微软AZURE全球版技术文档网站,若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站,请访问 https://docs.azure.cn.
使用 C 和 C++ 连接到 SQL 数据库Connect to SQL Database using C and C++
12/12/2018
本文内容
适用于:
Azure SQL 数据库
本文面向尝试连接到 Azure SQL 数据库的 C 和 C++ 开发人员。This post is aimed at C and C++ developers trying to connect to Azure SQL Database. 它分为多个部分,方便大家选择最感兴趣的部分进行查看。It is broken down into sections so you can jump to the section that best captures your interest.
C/C++ 教程的先决条件Prerequisites for the C/C++ tutorial
确保具有以下内容:Make sure you have the following items:
有效的 Azure 帐户。An active Azure account. If you don't have one, you can sign up for a Free Azure Trial.
必须安装 C++ 语言组件才能生成和运行此示例。You must install the C++ language components to build and run this sample.
如果基于 Linux 开发,还必须安装 Visual Studio Linux 扩展。If you are developing on Linux, you must also install the Visual Studio Linux extension.
Azure SQL 数据库和虚拟机上的 SQL ServerAzure SQL Database and SQL Server on virtual machines
Azure SQL 数据库基于 Microsoft SQL Server 构建,旨在提供高可用性、高性能和可缩放的服务。Azure SQL Database is built on Microsoft SQL Server and is designed to provide a high-availability, performant, and scalable service. 在本地运行的专有数据库中使用 Azure SQL 有很多好处。There are many benefits to using Azure SQL over your proprietary database running on premises. 使用 Azure SQL 时,需要安装、设置、维护或管理的不是数据库,而是数据库的内容和结构。With Azure SQL you don't have to install, set up, maintain, or manage your database but only the content and the structure of your database. 我们通常担心的数据库容错和冗余等功能全都内置其中。Typical things that we worry about with databases like fault tolerance and redundancy are all built in.
Azure 目前有两个选项可用于托管 SQL Server 工作负载:Azure SQL 数据库(数据库即服务)和虚拟机 (VM) 上的 SQL Server。Azure currently has two options for hosting SQL server workloads: Azure SQL Database, database as a service and SQL server on Virtual Machines (VM). 我们不会详细介绍两者之间的差异,但需要了解的是,如果想要利用云服务提供的成本节省和性能优化,那么 Azure SQL 数据库是使用新的基于云的应用程序的最佳选择。We will not get into detail about the differences between these two except that Azure SQL Database is your best bet for new cloud-based applications to take advantage of the cost savings and performance optimization that cloud services provide. 如果正在考虑将本地应用程序迁移或扩展到云中,Azure 虚拟机上的 SQL Server 可能更适合。If you are considering migrating or extending your on-premises applications to the cloud, SQL server on Azure virtual machine might work out better for you. 为了使本文简单明了,让我们创建一个 Azure SQL 数据库。To keep things simple for this article, let's create an Azure SQL Database.
数据访问技术:ODBC 和 OLE DBData access technologies: ODBC and OLE DB
连接到 Azure SQL 数据库没有任何差别,目前可以通过两种方法连接到数据库:ODBC(开放式数据库连接)和 OLE DB(对象链接与嵌入数据库)。Connecting to Azure SQL Database is no different and currently there are two ways to connect to databases: ODBC (Open Database connectivity) and OLE DB (Object Linking and Embedding database). 最近几年,Microsoft 已在使用 ODBC 进行本地关系数据访问。In recent years, Microsoft has aligned with ODBC for native relational data access. ODBC 相对简单,并且比 OLE DB 快得多。ODBC is relatively simple, and also much faster than OLE DB. 唯一需要说明的是,ODBC 使用的是旧的 C 样式 API。The only caveat here is that ODBC does use an old C-style API.
步骤 1:创建 Azure SQL 数据库Step 1: Creating your Azure SQL Database
请参阅入门页,以了解如何创建示例数据库。See the getting started page to learn how to create a sample database. 或者,你可以遵循这 一短两分钟的视频 ,通过 Azure 门户创建 Azure SQL 数据库。Alternatively, you can follow this short two-minute video to create an Azure SQL Database using the Azure portal.
步骤 2:获取连接字符串Step 2: Get connection string
预配 Azure SQL 数据库后,需要执行以下步骤,确定连接信息及添加用于防火墙访问的客户端 IP。After your Azure SQL Database has been provisioned, you need to carry out the following steps to determine connection information and add your client IP for firewall access.
在 Azure 门户中,使用“显示数据库连接字符串”(包含在数据库概述部分中)转到 Azure SQL 数据库 ODBC 连接字符串:In Azure portal, go to your Azure SQL Database ODBC connection string by using the Show database connection strings listed as a part of the overview section for your database:
复制 ODBC (包括 Node.js) [SQL 身份验证] 字符串的内容。Copy the contents of the ODBC (Includes Node.js) [SQL authentication] string. 稍后,我们使用此字符串从 C++ ODBC 命令行解释程序进行连接。We use this string later to connect from our C++ ODBC command-line interpreter. 此字符串提供驱动程序、服务器和其他数据库连接参数等详细信息。This string provides details such as the driver, server, and other database connection parameters.
步骤 3:将 IP 添加到防火墙Step 3: Add your IP to the firewall
转到服务器的防火墙部分,并使用以下步骤将客户端 IP 添加到防火墙,以确保我们可以建立成功的连接:Go to the firewall section for your server and add your client IP to the firewall using these steps to make sure we can establish a successful connection:
此时,已配置好 Azure SQL 数据库,并已准备好通过 C++ 代码连接。At this point, you have configured your Azure SQL Database and are ready to connect from your C++ code.
步骤 4:从 Windows C/C++ 应用程序连接Step 4: Connecting from a Windows C/C++ application
You can easily connect to your Azure SQL Database using ODBC on Windows using this sample that builds with Visual Studio. 该示例实现可用于连接到 Azure SQL 数据库的 ODBC 命令行解释器。The sample implements an ODBC command-line interpreter that can be used to connect to our Azure SQL Database. 此示例将数据库源名称文件 (DSN) 文件作为命令行参数,或我们先前从 Azure 门户复制的详细的连接字符串。This sample takes either a Database source name file (DSN) file as a command-line argument or the verbose connection string that we copied earlier from the Azure portal. 打开此项目的属性页,并将连接字符串作为命令行参数粘贴,如下所示:Bring up the property page for this project and paste the connection string as a command argument as shown here:
确保在该数据库连接字符串中为数据库提供正确的身份验证详细信息。Make sure you provide the right authentication details for your database as a part of that database connection string.
启动用于生成的应用程序。Launch the application to build it. 应看到如下所示确认成功连接的窗口。You should see the following window validating a successful connection. 甚至可以运行一些基本的 SQL 命令(例如 create table )来验证数据库连接:You can even run some basic SQL commands like create table to validate your database connectivity:
或者,可以使用未提供命令行参数时启动的向导创建 DSN 文件。Alternatively, you could create a DSN file using the wizard that is launched when no command arguments are provided. 我们也建议尝试此选项。We recommend that you try this option as well. 可以使用此 DSN 文件进行自动化以及保护身份验证设置:You can use this DSN file for automation and protecting your authentication settings:
祝贺!Congratulations! 现在已成功在 Windows 上使用 C++和 ODBC 连接到 Azure SQL。You have now successfully connected to Azure SQL using C++ and ODBC on Windows. 可以继续阅读如何为 Linux 平台执行相同操作的内容。You can continue reading to do the same for Linux platform as well.
步骤 5:从 Linux C/C++ 应用程序连接Step 5: Connecting from a Linux C/C++ application
也许你还不知道,Visual Studio 现在也允许开发 C++ Linux 应用程序。In case you haven't heard the news yet, Visual Studio now allows you to develop C++ Linux application as well. 可以在 Visual C++ for Linux Development (用于 Linux 开发的 Visual C++)博客中阅读关于此新方案的信息。You can read about this new scenario in the Visual C++ for Linux Development blog. 若要为 Linux 生成,需要运行 Linux 分发的远程计算机。To build for Linux, you need a remote machine where your Linux distro is running. 如果没有可用的远程计算机,可以使用 Linux Azure 虚拟机快速设置。If you don't have one available, you can set one up quickly using Linux Azure Virtual machines.
对于本教程,我们假设已设置好 Ubuntu 16.04 Linux 分发。For this tutorial, let us assume that you have an Ubuntu 16.04 Linux distribution set up. 此处的步骤还适用于 Ubuntu 15.10、Red Hat 6 和 Red Hat 7。The steps here should also apply to Ubuntu 15.10, Red Hat 6, and Red Hat 7.
按照以下步骤安装发行版 SQL 和 ODBC 所需的库:The following steps install the libraries needed for SQL and ODBC for your distro:
sudo su
sh -c 'echo "deb [arch=amd64] https://apt-mo.trafficmanager.net/repos/mssql-ubuntu-test/ xenial main" > /etc/apt/sources.list.d/mssqlpreview.list'
sudo apt-key adv --keyserver apt-mo.trafficmanager.net --recv-keys 417A0893
apt-get update
apt-get install msodbcsql
apt-get install unixodbc-dev-utf16 #this step is optional but recommended*
启动 Visual Studio。Launch Visual Studio. 在“工具”->“选项”->“跨平台”->“连接管理器” 下,添加到 Linux 框的连接:Under Tools -> Options -> Cross Platform -> Connection Manager, add a connection to your Linux box:
建立通过 SSH 的连接后,创建一个空项目 (Linux) 模板:After connection over SSH is established, create an Empty project (Linux) template:
使用 ODBC API SQLAllocHandle、SQLSetConnectAttr 和 SQLDriverConnect 时,应能够初始化并建立与数据库的连接。Using the ODBC APIs SQLAllocHandle, SQLSetConnectAttr, and SQLDriverConnect, you should be able to initialize and establish a connection to your database.
和 Windows ODBC 示例一样,需要使用数据库连接字符串参数的详细信息(之前从 Azure 门户复制)替换 SQLDriverConnect 调用。Like with the Windows ODBC sample, you need to replace the SQLDriverConnect call with the details from your database connection string parameters copied from the Azure portal previously.
retcode = SQLDriverConnect(
hdbc, NULL, "Driver=ODBC Driver 13 for SQL"
"Server;Server=;Uid=;Pwd=
"yourpassword>;database=",
SQL_NTS, outstr, sizeof(outstr), &outstrlen, SQL_DRIVER_NOPROMPT);
编译前需要完成的最后一步是将 odbc 作为库依赖项添加:The last thing to do before compiling is to add odbc as a library dependency:
若要启动应用程序,请从“调试” 菜单打开 Linux 控制台:To launch your application, bring up the Linux Console from the Debug menu:
如果已成功连接,现在应看到 Linux 控制台中显示当前数据库名称:If your connection was successful, you should now see the current database name printed in the Linux Console:
祝贺!Congratulations! 你已成功完成本教程,现在可以在 Windows 和 Linux 平台上通过 C++ 连接到 Azure SQL 数据库。You have successfully completed the tutorial and can now connect to your Azure SQL Database from C++ on Windows and Linux platforms.
获取完整的 C/C++ 教程解决方案Get the complete C/C++ tutorial solution
可以在 GitHub 中查找包括本文所有示例的 GetStarted 解决方案:You can find the GetStarted solution that contains all the samples in this article at GitHub:
ODBC C++ Windows 示例,下载 Windows C++ ODBC 示例,连接到 Azure SQLODBC C++ Windows sample, Download the Windows C++ ODBC Sample to connect to Azure SQL
ODBC C++ Linux 示例,下载 Linux C++ ODBC 示例,连接到 Azure SQLODBC C++ Linux sample, Download the Linux C++ ODBC Sample to connect to Azure SQL
后续步骤Next steps
其他资源Additional resources