Руководство для начинающих по использованию SQL Server из C#

Автор: By [MSA] Matt Newman, перевод выполнил kpumuk специально для Realcoding.NET
оригинал http://codeproject.com/cs/database/sql_in_csharp.asp

Введение

В этой статье я хочу показать, как добавлять и читать данные из баз данных SQL Server или MSDE. Этот код должен работать на обоих SQL-серверах. Я использую как 2000, так и MSDE. Кроме того, я использую Visual Studio 2002, но все это должно работать и с Visual Studio 2003, Web Matrix, и SDK из командной строки. Приведенный код должен работать как с приложениями C#, так и с веб-приложениями C# и веб-сервисами. Этот код не компилируется на FreeBSD с помощью Rotor [^].

Предыстория

Часть моего текущего проекта требует от меня сохранять и восстанавливать информацию из базы данных. Я решил использовать C# как свой основной язык с тех пор, как начал читать книгу Inside C# Second Edition [^] от Tom Archer [^], которая, таким образом, является обязательной. Однако я не смог найти примеры того, как красиво и в общем виде использовать SQL-сервер с C#.

Использование кода

Я не включал пример приложения, потому что код, который приводится в статье, может быть использован в реальности и должен работать без проблем. Также во всей статье я буду ссылаться на SQL Server, MSDE - это свободная версия SQL Server, которая не предоставляет некоторых графических инструментов и имеет несколько других ограничений, например, размер базы данных. Этот код должен работать на обоих без проблем.

Устанавливаем любимое соединение

В действительности нет никакой магии вуду для создания соединения с SQL-сервером, допуская, что он был правильно установлен,- это я не буду рассматривать в данной статье. .NET позволяет очень легко работать с SQL. Первым шагом является добавление пространства имен клиента SQL:

using System.Data.SqlClient;

Затем мы создаем SqlConnection и указываем строку подключения.

SqlConnection myConnection = new SqlConnection("user id=username;" + 
                                       "password=password;server=serverurl;" + 
                                       "Trusted_Connection=yes;" + 
                                       "database=database; " + 
                                       "connection timeout=30");

SqlConnection.ConnectionString

Строка подключения - это просто набор опций и их значений, которые указывают как и куда подключаться. Пока я изучал справочные файлы Visual Studio .NET, я обнаружил несколько полей, имеющих много имен, но работающих одинаково, как, например, Password и Pwd взаимозаменяемы. Я не включал всех опций для SqlConnection.ConnectionString в этот раз. Как только я получу шанс проверить и использовать остальные опции, я включу их в статью.

User ID

User ID используется для аутентификации средствами SQL. В своей практике я обычно игнорирую этот параметр, используя Trusted_Connection или аутентификацию средствами Windows. Если с именем пользователя связан пароль, используется Password или Pwd.

"user id=userid;"

Password или Pwd

Поле пароля используется с User ID. Не имеет смысла вход в систему без имени пользователя, а только с паролем. Password и Pwd полностью взаимозаменяемы.

"Password=validpassword;"
-или-
"Pwd=validpassword;"

Data Source, или Server, или Address, или Addr, или Network Address

Просматривая документацию MSDN, я нашел множество путей указать сетевой адрес. В документации утверждается, что нет отличий между ними, и они могут использоваться равнозначно. Адрес - это правильный сетевой адрес, для краткости я использую только адрес localhost в примерах.

"Data Source=localhost;"
-или-
"Server=localhost;"
-или-
"Address=localhost;"
-или-
"Addr=localhost;"
-или-
"Network Address=localhost;"

Integrated Sercurity или Trusted_Connection

Integrated Security и Trusted_Connection используются для указания, является ли соединение безопасным, например, как аутентификация средствами Windows или SSPI. Распознаваемые значение - это true, false и sspi. В соответствии с документацией MSDN, sspi является эквивалентом true. Примечание: Я не знаю, как работает SSPI, или как влияет на соединение.

Connect Timeout или Connection Timeout

Эти опции определяют время ожидания ответа сервера до генерации ошибки в секундах. Значением по умолчанию является 15 (секунд).

"Connect Timeout=10;"
-или-
"Connection Timeout=10;"

Initial Catalog или Database

Initial Catalog и Database - это просто два способа выбора базы данных, связанной с соединением.

"Inital Catalog=main;"
-или-
"Database=main;"

Network Library или Net

Опция Network Library важна, если вы связываетесь с сервером по протоколу, отличному от TCP/IP. Значение по умолчанию для Network Library - это dbmssocn или TCP/IP. Доступны следующие опции: dbnmpntw (Named Pipes, именованные каналы), dbmsrpcn (Multiprotocol), dbmsadsn (Apple Talk), dbmsgnet (VIA), dbmsipcn (Shared Memory, разделяемая память), dbmsspxn (IPX/SPX) и dbmssocn (TCP/IP). И, как и раньше, Network Library и Net могут использоваться взаимозаменяемо. Примечание: Соответствующий сетевой протокол должен быть установленн в системе, к которой вы подключаетесь.

SqlConnection.Open()

Это последняя часть установки соединения, и в простом случае выполняется следующим кодом (не забудьте убедиться, что ваше соединение получило строку подключения ранее):

try
{
    myConnection.Open();
}
catch(Exception e)
{
    Console.WriteLine(e.ToString());
}

SqlConnection.Open() - это void-функция, которая не возвращает ошибок, а выбрасывает исключение, потому не забывайте помещать ее в try/catch блок, чтобы предотвратить взрыв программы на фронте пользователя.

Обзор команд

Команды SQL, вероятно, наиболее сложная часть использования базы данных SQL, но среда .NET предоставляет обертки, которые чудесно все делают, избавляя от большей части работы.

SqlCommand

Есть идеи, для чего используется SqlCommand? Если вы предположили, что он используется для команд SQL, то вы совершенно правы. SqlCommand требует как минимум двух вещей для функционирования. Строка команды и соединение. Для начала рассмотрим требование соединения. Существует два пути указания соединения, оба проиллюстрированы ниже:

SqlCommand  myCommand = new SqlCommand("Command String", myConnection);

// - или -

myCommand.Connection = myConnection;

Строка команды также может быть указана двумя способами через свойство SqlCommand.CommandText. Теперь давайте посмотрим на наш первый SqlCommand. Чтобы оставить пример простым, это будет простая команда INSERT.

SqlCommand myCommand= new SqlCommand("INSERT INTO table (Column1, Column2) " +
                                     "Values ('string', 1)", myConnection);

// - или - 

myCommand.CommandText = "INSERT INTO table (Column1, Column2) " + 
                        "Values ('string', 1)";

Теперь рассмотрим значения. table - это просто таблица в базе данных. Column1 и Column2 - всего лишь названия колонок. В секции значений я показал, как добавлять типы string и int. Значение строки помещается в одинарные кавычки, а целое, как вы видите, передается как есть. Последний шаг - исполнение команды с помощью:

myCommand.ExecuteNonQuery()

SqlDataReader

Вставка данных - это хорошо, но получение их назад так же важно. Вот теперь на сцену выходит SqlDataReader. "Считыватель данных" - это не все, что вам понадобится; кроме него нужен еще и SqlCommand. Следующий код демонстрирует, как настроить и выполнить простой reader:

try
{
    SqlDataReader myReader = null;
    SqlCommand    myCommand = new SqlCommand("select * from table", 
                                             myConnection);
    myReader = myCommand.ExecuteReader();
    while(myReader.Read())
    {
        Console.WriteLine(myReader["Column1"].ToString());
        Console.WriteLine(myReader["Column2"].ToString());
    }
}
catch (Exception e)
{
    Console.WriteLine(e.ToString());
}

Как вы видите, SqlDataReader не получает доступ к базе данных, он просто сохраняет данные и предоставляет простой интерфейс использования данных. SqlCommand довольно прост, table - это таблица, из которой вы читаете данные. Column1 и Column2 - колонки в этой таблице. Так как существует большая вероятность того, что вы будете читать более одной строки, требуется цикл while для получения всех записей. И, как всегда, вы хотите использовать try и catch, чтобы не прерывалось выполнение.

SqlParameter

Существует маленькая проблема при использовании SqlCommand продемонстрированным мной способом - он оставляет большую дыру в безопасности. Например, путем использования ранее продемонстрированного подхода, ваша строка команды получается примерно такой, если вы получаете данные от пользователя:

 SqlCommand myCommand = new SqlCommand(
  "SELECT * FROM table WHERE Column = " + input.Text, myConnection);
            

Все красиво и прекрасно, если пользователь вводит данные в корректном виде, однако, что случится, если пользователь введет value1, DROP table? В лучшем случае сгенерируется исключение (я не ставлю цель проверить, что делает этот пример,- он просто демонстрирует подход), в худшем - вы можете поцеловать вашу таблицу на прощание. Вы можете обработать весь ввод пользователя и вырезать все, что вызывает проблемы, или использовать SqlParameter. Класс SqlParameter довольно большой, я только покажу вам основы использования параметров. По существу, вам нужны три вещи, чтобы создать параметр. Это имя, тип данных и их размер. (обратите внимание, что некоторые типы данных, нужные вам, не требуют указания размера, как Text).

SqlParameter myParam = new SqlParameter("@Param1", SqlDbType.VarChar, 11);
myParam.Value = "Garden Hose";
        
SqlParameter myParam2 = new SqlParameter("@Param2", SqlDbType.Int, 4);
myParam2.Value = 42;

SqlParameter myParam3 = new SqlParameter("@Param3", SqlDbType.Text);
myParam.Value = "Обратите внимание, что я не
      указал размер. " + 
  "Если я сделаю это, текст будет обрезан.";

Существует соглашение по именованию (оно может быть обязательным, я не уверен), называть параметры начиная с символа @. Далее, как вы будете использовать параметр? Это будет довольно просто, как показывает следующий код.

SqlCommand myCommand = new SqlCommand(
  "SELECT * FROM table WHERE Column = @Param2", myConnection);
myCommand.Parameters.Add(myParam2);

Это остановит мошенников от попыток внедриться в вашу строку команды. Это не все, для чего нужны параметры, если вы хотите узнать больше - хорошее место начать отсюда[^].

Не забывайте закрыть, когда вы закончили!

Закрытие соединения так же просто, как и открытие. Просто вызовите SqlConnection.Close(), но не забывайте поместить его в блок try/catch, потому что, как и SqlConnection.Open(), он не возвращает ошибок, а выбрасывает исключение.

try
{
    myConnection.Close();
}
catch(Exception e)
{
    Console.WriteLine(e.ToString());
}

Когда хорошие соединения становятся плохими

Доверенные соединения всегда были загадкой для меня, я никогда не мог понять, почему IIS и SQL-сервер не могли поладить. К счастью Pete (moredip) обратил внимание на полезный раздел документации. Чтобы сделать его более простым, я решил добавить его в эту статью. Я разбил его на две разные секции: IIS 6 и другие версии IIS. Чтобы начать, убедитесь, что osql.exe находится в ваших системных путях, или найдите его вручную. Он должен находится там же, где и ваш SQL Server в каталоге 2000 server/client tools. На моей системе это что-то вроде этого: %Install Directory%80ToolsBINN. Для простоты я буду использовать псевдо-переменные в примерах, потому не смущайтесь. Например, псевдо-переменная будет выглядеть так: %VARIABLE%. Сервер будет указываться как %SERVER%%INSTANCE%. Если вы не используете имена экземпляров, это может быть просто %SERVER%, (local), если сервер является локальной машиной. Если же используете имена экземпляров, это будет что-то вроде ServerNameServerInstance и т.д. Я также буду использовать %DATABASE% для указания названия базы данных.

IIS 6 на Windows 2003 Server

Я знаю, что это будет работать на IIS 6 под Windows 2003 Server, потому что делал это, а также потому, что сейчас это единственная ОС с IIS 6. На IIS 6 процесс ASP.NET выполняется под аккаунтом 'NT AUTHORITYNETWORK SERVICE'.

osql -E -S %SERVER%%INSTANCE% -Q "sp_grantlogin 'NT AUTHORITYNETWORK SERVICE'"

Теперь ваше приложение ASP.NET может логиниться на сервер. Все, что осталось - дать права на базы данных.

osql -E -S %SERVER%%INSTANCE% -d %DATABASE% -Q 
  "sp_grantdbaccess 'NT AUTHORITYNETWORK SERVICE'"
osql -E -S %SERVER%%INSTANCE% -d %DATABASE% -Q 
  "sp_addrolemember 'db_owner', 'NT AUTHORITYNETWORK SERVICE'"

Эти 2 строки дают права на одну из баз данных. Таким образом, если вы хотите дать права на другую базу данных - просто измените %DATABASE% и выполните обе строки.

IIS 5.1

Это должно работать на всех других комбинациях с IIS 5.1 (возможны разные версии). Единственное отличие между IIS 5.1 и IIS 6 - это аккаунт, под которым выполняется процесс ASP.NET. IIS 5.1 работает под %MACHINENAME%ASPNET, где %MACHINENAME% - это название машины.

osql -E -S %SERVER%%INSTANCE% -Q "sp_grantlogin '%MACHINENAME%ASPNET'"

Теперь ваше приложение ASP.NET может логиниться на сервер. Все, что осталось - это дать права на базы данных.

osql -E -S %SERVER%%INSTANCE% -d %DATABASE% 
  -Q "sp_grantdbaccess '%MACHINENAME%ASPNET'"
osql -E -S %SERVER%%INSTANCE% -d %DATABASE% 
  -Q "sp_addrolemember 'db_owner', '%MACHINENAME%ASPNET'"

Эти 2 строки дают права на одну базу данных. Если вы хотите дать права на другую базу, просто измените %DATABASE% и снова выполните обе строки.

Свободные концы

Теперь вы владеете основами, необходимыми для начала использования базы данных SQL в ваших веб или настольных приложениях.

Эта статья не законченная. Я планирую расширить ее и добавить примеры приложений, как только появится время. С информацией о хранимых процедурах, как и с увеличенным разделом опций подключения. Если вы имеете пожелания, оставляйте их на форуме ниже.