Работа с базами данных

Использование объекта Command

Объект Command исполняет запрос SQL, который может быть в форме встроенного текста, процедуры сервера или прямого доступа к таблице. Если это запрос на выборку данных SELECT, то данные обычно помещаются в DataSet или в DataReader. Методы и свойства определены в абстрактном классе DbCommand (через интерфейс IDbCommand ), и их реализуют частные ненаследуемые классы OleDbCommand, SqlCommand, OdbcCommand.

Свойство CommandType может принимать значения из перечисления CommandType. По умолчанию это Text, то есть выполняется непосредственно текст команды SQL, который записан в свойстве Command. TableDirect означает, что в результате выполнения команды будет возвращено все содержание таблицы. StoredProcedure означает, что в Command находится имя процедуры сервера, которая и будет выполняться.

Свойство CommandText хранит текст запроса SQL или имя серверной процедуры.

CommandTimeout задает время ожидания ответа, по умолчанию равное 30 секундам. Если команда не выполнится в течение этого времени, будет выброшено исключение.

Процедуры сервера нуждаются в параметрах. Они хранятся в коллекции Parameters и имеют тип SqlParameter. Текстовые команды также могут получать параметры, перед которыми ставится префикс @. Например:

" SELECT * FROM CUSTOMERS WHERE CITY = @CITY AND CONTACTNAME = 
@CONTACT "

Часто используется метод ExecuteNonQuery. С помощью него можно выполнить любую операцию с базами данных, которая не связана с запросом и получением данных, например, обновление, удаление записей, создание и изменение таблиц, создание процедур сервера. Она возвращает количество измененных записей в том случае, если выполняются команды Select, Update, Delete.

ExecuteScalar возвращает результат запроса в случае, если это одно-единственное значение. Например, нужно узнать количество заказов конкретного покупателя. Запрос выполняется с помощью команды "Select count * where customerid=1". Ее результат — выборка из одной строки и одного столбца. Ее можно выполнить и с помощью метода ExecuteReader, но ExecuteScalar будет выполняться быстрее. Если запрос возвратит большее количество строк или столбцов, они будут проигнорированы.

ExecuteRow возвращает единственную запись.

ExecuteReader выполняется, если нужно получить табличные данные. Результат выполнения — курсор, в котором можно двигаться только от начала до конца.

В результате выполнения метода ExecuteReader объекта Command создается объект DataReader. Всегда закрывайте соединения после использования, иначе оно останется активным и будет занимать ресурсы. Это можно сделать двумя способами. Первый — вызвать перегруженный метод ExecuteReader, который принимает параметр типа CommandBehavior со значением CommandBehavior.CloseConnection. В таком случае необходимо перелистать полученную выборку от начала до конца, и соединение закроется, когда будет достигнут конец. Если вы не хотите прочитать все данные, можете самостоятельно закрыть соединение методом Close:

public void CreateMySqlDataReader(string mySelectQuery, string 
myConnectionString)
  {
    SqlConnection myConnection = new 
SqlConnection(myConnectionString);
    SqlCommand myCommand = new SqlCommand(mySelectQuery, 
myConnection);
    myCommand.CommandType = CommandType.Text;
    myCommand.Connection.Open();
    SqlDataReader myReader = 
myCommand.ExecuteReader(CommandBehavior.CloseConnection);
    while (myReader.Read())
    {
      Response.Write(myReader.GetString(0) + "<br>");
    }
    myReader.Close();
    myConnection.Close();
  }

Развитые СУБД (теперь и MS Access) поддерживают транзакции. Транзакция — это последовательность команд, которая выполняется как одно целое. Например, при переводе денег сумма вычитается с одного счета и добавляется к другому. Если произойдет только одна из этих операций, банк или его клиенты понесут потери, поэтому важно, чтобы произошли обе операции либо ни одна не произошла. Если на одном из этапов транзакции допущена ошибка, происходит откат (Rollback), то есть отменяются все ранее сделанные операции и база возвращается к состоянию до начала транзакции. Если все успешно, транзакция подтверждается операцией Commit.

Для поддержки транзакций введен класс SqlTransaction и ему подобные. У объекта Command есть свойство Transaction. Метод BeginTransaction объекта Connection заставляет базу данных перейти в режим транзакции.

Кроме того, необходимо всегда заключать программный код, работающий с базами данных, в блоки try/catch, так как работа часто идет с удаленными серверами и могут происходить самые разные ошибки как в сети, так и при работе самого сервера.

При этом выбрасывается исключение SqlException или OleDbException:

public void RunTransaction(string[] Queries, string 
myConnectionString)
  {
    SqlConnection conn = null;
    SqlTransaction trans = null;
    try
    {
      conn = new SqlConnection(myConnectionString);
      conn.Open();

      trans = conn.BeginTransaction();

      SqlCommand cmd = new SqlCommand();
      cmd.Connection = conn;
      cmd.Transaction = trans;
      foreach (string Query in Queries)
      {
        cmd.CommandText = Query;
        cmd.ExecuteNonQuery();
      }
      trans.Commit();
    }
    catch (SqlException SqlEx)
    {
      if (trans != null)
      {
        trans.Rollback();
      }

      throw new Exception("An error occurred while transaction", 
SqlEx);
      return;
    }
    finally
    {
      if (conn != null)
      {
        conn.Close();
      }
    }
  }