Учебник. Проектирование реляционной базы данных в службе "База данных SQL Azure" на языке C# с помощью ADO.NET
Область применения:
База данных SQL Azure
База данных SQL Azure — это реляционная база данных как услуга (DBaaS) в Microsoft Cloud (Azure). Из этого руководства вы узнаете, как с помощью портала Azure и ADO.NET с Visual Studio выполнить следующие действия:
- создать базу данных с помощью портала Azure;
- настроить правила брандмауэра на уровне сервера с помощью портала Azure;
- подключиться к базе данных с помощью ADO.NET и Visual Studio;
- создать таблицы с помощью ADO.NET;
- вставить, обновить и удалить данные с помощью ADO.NET;
- выполнить запрос данных с помощью ADO.NET.
\* Если у вас еще нет подписки Azure, создайте бесплатную учетную запись Azure, прежде чем начинать работу.
Совет
В этом бесплатном модуле Learn показано, как разработать и настроить приложение ASP.NET, которое запрашивает базу данных Azure SQL, включая создание простой базы данных.
Предварительные требования
Установленная среда Visual Studio 2019 или более поздней версии.
Создание пустой базы данных в службе "База данных SQL Azure"
База данных в службе "База данных SQL Azure" создается с определенным набором вычислительных ресурсов и ресурсов хранения. База данных создается в группе ресурсов Azure и управляется с помощью логического сервера SQL Server.
Чтобы создать пустую базу данных, выполните приведенные ниже действия.
Щелкните Создать ресурс в верхнем левом углу окна портала Azure.
На странице Создать выберите Базы данных в разделе Azure Marketplace, а затем выберите База данных SQL в разделе Рекомендуемые.
Заполните форму База данных SQL, указав следующую информацию, как показано на предыдущем рисунке.
Параметр Рекомендуемое значение Описание Имя базы данных yourDatabase Допустимые имена баз данных см. в статье Идентификаторы баз данных. Подписка yourSubscription Дополнительные сведения о подписках см. здесь. Группа ресурсов yourResourceGroup Допустимые имена групп ресурсов см. в статье о правилах и ограничениях именования. Выбрать источник Пустая база данных Указывает, что должна быть создана пустая база данных. Выберите Сервер , чтобы использовать существующий сервер или создать и настроить новый сервер. Выберите существующий сервер или выберите Создать новый сервер и заполните форму Новый сервер следующими сведениями:
Параметр Рекомендуемое значение Описание Имя сервера Любое глобально уникальное имя Допустимые имена серверов см. в статье о правилах и ограничениях именования. Имя для входа администратора сервера Любое допустимое имя Сведения о допустимых именах для входа см. в статье Идентификаторы базы данных. Пароль Любой допустимый пароль Длина пароля должна составлять минимум восемь символов. В пароле должны использоваться символы трех категорий из перечисленных: прописные буквы, строчные буквы, цифры и специальные символы. Расположение Любое допустимое расположение Дополнительные сведения о регионах Azure см. здесь. 
Щелкните Выбрать.
Выберите Ценовая категория , чтобы указать уровень служб, количество DTU или виртуальных ядер, а также объем хранилища. Вы можете изучить доступные ресурсы для каждого уровня служб (число DTU или виртуальных ядер и объем хранилища).
Выбрав уровень служб, количество DTU или виртуальных ядер, а также объем хранилища, нажмите кнопку Применить.
Заполните поле Параметры сортировки для пустой базы данных. В этом руководстве используйте значение по умолчанию. Дополнительные сведения о параметрах сортировки см. в этой статье.
Теперь, когда вы заполните форму База данных SQL, выберите Создать, чтобы подготовить базу данных. Этот шаг может занять несколько минут.
На панели инструментов щелкните значок Уведомления, чтобы отслеживать процесс развертывания.
Создание правила брандмауэра для IP-адресов на уровне сервера
Служба "База данных SQL" создает брандмауэр IP-адресов на уровне сервера. Он не позволяет внешним приложениям и средствам подключаться к серверу и к любой базе данных на сервере, если не создано правило брандмауэра, позволяющее пропускать их IP-адреса через брандмауэр. Чтобы разрешить внешние подключения к базе данных, необходимо сначала добавить правило брандмауэра IP-адресов, указав в нем свой IP-адрес (или диапазон IP-адресов). Выполните следующие действия, чтобы создать правило брандмауэра IP-адресов на уровне сервера.
Важно!
База данных SQL обменивается данными через порт 1433. Если вы пытаетесь подключиться к этой службе из корпоративной сети, исходящий трафик через порт 1433 может быть запрещен сетевым брандмауэром. В таком случае вы не сможете подключиться к базе данных, пока ваш администратор не откроет порт 1433.
После завершения развертывания выберите Базы данных SQL в меню слева, а затем выберите базу данных на странице Базы данных SQL . После этого откроется страница обзора базы данных, где будет указано полное имя сервера (например, yourserver.database.windows.net) и будут предоставлены параметры для дальнейшей настройки.
Скопируйте полное имя сервера. Оно понадобится вам для подключения к серверу и связанным базам данных из SQL Server Management Studio.
В разделе Параметров выберите Сеть. Перейдите на вкладку Общий доступ , а затем выберите Выбранные сети в разделе Доступ к общедоступной сети , чтобы открыть раздел Правила брандмауэра .
Выберите Добавить IP-адрес клиента на панели инструментов, чтобы добавить текущий IP-адрес в новое правило брандмауэра для IP-адресов. С использованием правила брандмауэра IP-адресов можно открыть порт 1433 для одного IP-адреса или диапазона IP-адресов.
Щелкните Сохранить. Для текущего IP-адреса будет создано правило брандмауэра уровня сервера, с помощью которого можно открыть порт 1433 сервера.
Нажмите кнопку ОК и закройте страницу параметров брандмауэра .
Теперь IP-адрес может проходить через брандмауэр IP-адресов. Теперь можно подключиться к базе данных с помощью SQL Server Management Studio или другого средства по своему усмотрению. Обязательно используйте созданную ранее учетную запись администратора сервера.
Важно!
По умолчанию доступ через брандмауэр IP-адресов Базы данных SQL включен для всех служб Azure. Выберите выкл. на этой странице, чтобы отключить доступ для всех служб Azure.
Пример программы C#
В следующих разделах этой статьи представлена программа C#, в которой используется ADO.NET для отправки инструкций Transact-SQL (T-SQL) в Базу данных SQL. В программе C# демонстрируются следующие действия:
- Подключение к Базе данных SQL с помощью ADO.NET
- Методы, возвращающие инструкции T-SQL
- Создание таблиц
- Заполнение таблиц данными
- Обновление, удаление и выбор данных
- Отправка T-SQL в базе данных
Схема отношения элементов (ERD)
Инструкции CREATE TABLE включают ключевое слово REFERENCES для создания отношения внешнего ключа (FK) между двумя таблицами. При использовании tempdb закомментируйте ключевое слово --REFERENCES с помощью пары тире в начале.
На схеме ERD представлено отношение между двумя таблицами. Значения в дочернем столбце tabEmployee.DepartmentCode ограничены значениями родительского столбца tabDepartment.DepartmentCode.
Примечание
Вы можете отредактировать T-SQL, чтобы добавить начальные # к именам таблиц, чтобы они создавались как временные таблицы в tempdb. Это удобно при демонстрации, когда тестовая база данных недоступна. Все ссылки на внешние ключи не применяются во время их использования, а временные таблицы автоматически удаляются при закрытии подключения после завершения работы программы.
Компиляция и запуск
Программа C# логически является одним CS-файлом. Она физически разделена на несколько блоков кода для удобства изучения каждого блока. Чтобы скомпилировать и запустить программу, выполните шаги ниже.
Создайте проект C# в Visual Studio. Необходимо выбрать тип проекта Консоль. Это можно сделать в разделе Шаблоны>Visual C#>Классическое приложение Windows>Консольное приложение (.NET Framework) .
В файле Program.cs замените соответствующие начальные строки кода, сделав следующее:
Скопируйте и вставьте следующие блоки кода в той же последовательности, в которой они представлены (дополнительные сведения см. в разделах, посвященных подключению к базе данных, созданию T-SQL и отправке в базу данных).
Измените следующие значения в методе
Main:- cb.DataSource
- cb.UserID
- cb.Password
- cb.InitialCatalog
Убедитесь, что на сборку System.Data.dll есть ссылка. Для этого разверните узел Ссылки на панели обозревателя решений.
Чтобы создать программу в Visual Studio и запустить ее, щелкните Начало. Выходные данные отчета отображаются в окне программы. Значения GUID могут отличаться для разных тестовых запусков.
================================= T-SQL to 2 - Create-Tables... -1 = rows affected. ================================= T-SQL to 3 - Inserts... 8 = rows affected. ================================= T-SQL to 4 - Update-Join... 2 = rows affected. ================================= T-SQL to 5 - Delete-Join... 2 = rows affected. ================================= Now, SelectEmployees (6)... 8ddeb8f5-9584-4afe-b7ef-d6bdca02bd35 , Alison , 20 , acct , Accounting 9ce11981-e674-42f7-928b-6cc004079b03 , Barbara , 17 , hres , Human Resources 315f5230-ec94-4edd-9b1c-dd45fbb61ee7 , Carol , 22 , acct , Accounting fcf4840a-8be3-43f7-a319-52304bf0f48d , Elle , 15 , NULL , NULL View the report output here, then press any key to end the program...
Подключение к Базе данных SQL с помощью ADO.NET
using System;
using System.Data.SqlClient; // System.Data.dll
//using System.Data; // For: SqlDbType , ParameterDirection
namespace csharp_db_test
{
class Program
{
static void Main(string[] args)
{
try
{
var cb = new SqlConnectionStringBuilder();
cb.DataSource = "your_server.database.windows.net";
cb.UserID = "your_user";
cb.Password = "your_password";
cb.InitialCatalog = "your_database";
using (var connection = new SqlConnection(cb.ConnectionString))
{
connection.Open();
Submit_Tsql_NonQuery(connection, "2 - Create-Tables", Build_2_Tsql_CreateTables());
Submit_Tsql_NonQuery(connection, "3 - Inserts", Build_3_Tsql_Inserts());
Submit_Tsql_NonQuery(connection, "4 - Update-Join", Build_4_Tsql_UpdateJoin(),
"@csharpParmDepartmentName", "Accounting");
Submit_Tsql_NonQuery(connection, "5 - Delete-Join", Build_5_Tsql_DeleteJoin(),
"@csharpParmDepartmentName", "Legal");
Submit_6_Tsql_SelectEmployees(connection);
}
}
catch (SqlException e)
{
Console.WriteLine(e.ToString());
}
Console.WriteLine("View the report output here, then press any key to end the program...");
Console.ReadKey();
}
Методы, возвращающие инструкции T-SQL
static string Build_2_Tsql_CreateTables()
{
return @"
DROP TABLE IF EXISTS tabEmployee;
DROP TABLE IF EXISTS tabDepartment; -- Drop parent table last.
CREATE TABLE tabDepartment
(
DepartmentCode nchar(4) not null PRIMARY KEY,
DepartmentName nvarchar(128) not null
);
CREATE TABLE tabEmployee
(
EmployeeGuid uniqueIdentifier not null default NewId() PRIMARY KEY,
EmployeeName nvarchar(128) not null,
EmployeeLevel int not null,
DepartmentCode nchar(4) null
REFERENCES tabDepartment (DepartmentCode) -- (REFERENCES would be disallowed on temporary tables.)
);
";
}
static string Build_3_Tsql_Inserts()
{
return @"
-- The company has these departments.
INSERT INTO tabDepartment (DepartmentCode, DepartmentName)
VALUES
('acct', 'Accounting'),
('hres', 'Human Resources'),
('legl', 'Legal');
-- The company has these employees, each in one department.
INSERT INTO tabEmployee (EmployeeName, EmployeeLevel, DepartmentCode)
VALUES
('Alison' , 19, 'acct'),
('Barbara' , 17, 'hres'),
('Carol' , 21, 'acct'),
('Deborah' , 24, 'legl'),
('Elle' , 15, null);
";
}
static string Build_4_Tsql_UpdateJoin()
{
return @"
DECLARE @DName1 nvarchar(128) = @csharpParmDepartmentName; --'Accounting';
-- Promote everyone in one department (see @parm...).
UPDATE empl
SET
empl.EmployeeLevel += 1
FROM
tabEmployee as empl
INNER JOIN
tabDepartment as dept ON dept.DepartmentCode = empl.DepartmentCode
WHERE
dept.DepartmentName = @DName1;
";
}
static string Build_5_Tsql_DeleteJoin()
{
return @"
DECLARE @DName2 nvarchar(128);
SET @DName2 = @csharpParmDepartmentName; --'Legal';
-- Right size the Legal department.
DELETE empl
FROM
tabEmployee as empl
INNER JOIN
tabDepartment as dept ON dept.DepartmentCode = empl.DepartmentCode
WHERE
dept.DepartmentName = @DName2
-- Disband the Legal department.
DELETE tabDepartment
WHERE DepartmentName = @DName2;
";
}
static string Build_6_Tsql_SelectEmployees()
{
return @"
-- Look at all the final Employees.
SELECT
empl.EmployeeGuid,
empl.EmployeeName,
empl.EmployeeLevel,
empl.DepartmentCode,
dept.DepartmentName
FROM
tabEmployee as empl
LEFT OUTER JOIN
tabDepartment as dept ON dept.DepartmentCode = empl.DepartmentCode
ORDER BY
EmployeeName;
";
}
Отправка T-SQL в базе данных
static void Submit_6_Tsql_SelectEmployees(SqlConnection connection)
{
Console.WriteLine();
Console.WriteLine("=================================");
Console.WriteLine("Now, SelectEmployees (6)...");
string tsql = Build_6_Tsql_SelectEmployees();
using (var command = new SqlCommand(tsql, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine("{0} , {1} , {2} , {3} , {4}",
reader.GetGuid(0),
reader.GetString(1),
reader.GetInt32(2),
(reader.IsDBNull(3)) ? "NULL" : reader.GetString(3),
(reader.IsDBNull(4)) ? "NULL" : reader.GetString(4));
}
}
}
}
static void Submit_Tsql_NonQuery(
SqlConnection connection,
string tsqlPurpose,
string tsqlSourceCode,
string parameterName = null,
string parameterValue = null
)
{
Console.WriteLine();
Console.WriteLine("=================================");
Console.WriteLine("T-SQL to {0}...", tsqlPurpose);
using (var command = new SqlCommand(tsqlSourceCode, connection))
{
if (parameterName != null)
{
command.Parameters.AddWithValue( // Or, use SqlParameter class.
parameterName,
parameterValue);
}
int rowsAffected = command.ExecuteNonQuery();
Console.WriteLine(rowsAffected + " = rows affected.");
}
}
} // EndOfClass
}
Дальнейшие действия
Из этого руководства вы узнали об основных задачах базы данных, таких как создание базы данных и таблиц, подключение к базе данных, загрузка данных и выполнение запросов. Вы ознакомились с выполнением следующих задач:
- создать базу данных с помощью портала Azure;
- настроить правила брандмауэра на уровне сервера с помощью портала Azure;
- подключиться к базе данных с помощью ADO.NET и Visual Studio;
- создать таблицы с помощью ADO.NET;
- вставить, обновить и удалить данные с помощью ADO.NET;
- выполнить запрос данных с помощью ADO.NET.
Перейдите к следующему руководству, чтобы узнать о переносе данных.