Технология Клиент-Сервер 2001'2

Home

Поиск

Издания

Контакты

k-press.ru

Продолжается подписка на наши издания! Вы не забыли подписаться?

Использование ADO.NET в приложениях ASP.NET**

Д.Руденко

ADO.NET (ActiveX Data Object.NET) – набор классов, используемый для доступа к источникам данных в платформе .NET. Название ADO.NET означает, что данный набор классов – это логическое развитие предыдущей объектной модели доступа к данным – ADO. Но ADO.NET не просто следующая версия ADO. ADO.NET представляет собой новую объектную модель, которая использует стандарт XML для передачи данных. ADO.Net развивает идею использования отсоединенных массивов данных, переводя ее из разряда дополнительных возможностей в разряд реально используемых, более того – основных способов работы. По сравнению с ADO, ADO.NET обещает более легкое программирование, лучшую производительность и масштабирование, меньшую зависимость от особенностей источников данных и большую способность взаимодействовать с другими платформами.

В этой статье будет рассмотрено применение ADO.NET для доступа к данным из сценариев ASP.NET. Сначала будет рассмотрена объектная модель ADO.NET, затем наиболее часто употребляемые объекты DataSet и DataReader и поставщики данных, которые применяются для связи с источниками данных. И в конце статьи будут приведены несколько интересных примеров применения ADO.NET в ASP.NET-приложениях.

Объектная модель ADO.NET

Примерную объектную модель ADO.NET можно представить следующей диаграммой:

Рисунок 1

В предыдущих версиях ADO основным объектом для работы с данными был объект Recordset, хранящий результат выполнения запроса – набор строк и колонок. Хотя ADO и позволял создавать отсоединенные наборы данных, это не было его основной задачей. Он был предназначен для работы с данными, использующей постоянное соединение с источником данных.

В ADO.NET все изменилось. Теперь ключевой объект, который хранит данные в ADO.NET – DataSet. Это экземпляр CLR-класса System.Data.DataSet, представляющий моментальный снимок части базы данных, размещенный в памяти.

В ADO объект Recordset использует другие объекты ADO для соединения с источником данных. DataSet – независимый от источника данных объект, который не имеет собственных средств для работы с источниками данных. Связующую роль между DataSet и источником данных в ADO.NET выполняют управляемые (managed) провайдеры. Каждый управляемый провайдер представляет набор объектов, с помощью которых можно подключиться к источнику данных, считать данные и заполнить ими DataSet. Это позволяет DataSet не знать ничего про соединения с источниками данных.

Преимущества ADO.NET

ADO.NET создан для использования в управляемых (managed) проектах. Старый ADO основан на технологии COM и при использовании из управляемых приложений требует дополнительных затрат на выполнение прокси-кода. К тому же ADO имеет меньшие возможности при работе с отключенными наборами данных и XML. Например, в ADO было непросто сохранить изменения, произведенные в отключенном курсоре. Вот некоторые преимущества ADO.NET в сравнении с ADO:

Масштабируемость

При использовании DataSet работа происходит с отсоединенными наборами данных. Это означает, что вы используете соединение с источником данных очень короткое время. Во многих системах количество подключений к базам данных является самым узким местом в плане масштабируемости. И для этих систем ADO.NET является очень хорошим решением, резко повышающим их масштабируемость. Отключенный набор данных может использоваться несколькими частями программы (или пользователями) одновременно.

Независимость от источника данных

В ADO возможности объекта Recordset сильно зависели от используемого источника данных. Хотя теоретически ADO обеспечивал доступ к данным независимо от источника данных, на практике всегда необходимо было иметь хорошее представление о возможностях провайдера. В ADO.NET DataSet действительно независим от источника данных, и изменение провайдера, с помощью которого заполняется DataSet, не влияет на функциональность DataSet-а. А то, что данные читаются целиком или последовательно, снижает планку требований, предъявляемых к провайдеру. Так что в большинстве случаев изменение кода будет состоять в изменении используемого управляемого провайдера данных и строки подключения.

Способность к взаимодействию

Так как ADO.NET использует XML как стандартный формат передачи данных, программа, которой необходимо получить данные из компонента ADO.NET, не обязана сама быть компонентом ADO.NET. В общем случае она вообще может не быть Windows-программой. Единственное требование – эта программа должна понимать XML. И это позволяет ADO.NET-компонентам при использовании других компонентов и сервисов, входящих в VS.Net, легко взаимодействовать с любой программой на любой платформе.

Последние версии ADO также могли представлять данные в виде XML, но это представление было основано на специально разработанной схеме данных, применяемой только для представления объектов Recordset. В ADO.NET поддержка XML более гибка.

Типизированные поля

В ADO из-за того, что нужно было обеспечить работу и в скриптовых языках, работа со значениями ячейки шла через тип Variant. И это вызывало некоторое количество проблем – компилятор не имел возможности выполнения проверки на совместимость типов, переменная типа Variant занимала лишнюю память и медленнее обрабатывалась. В последних версиях ADO был добавлен интерфейс IADORecordBinding, позволявший решить эту проблему, но доступен он был только для C++-разработчиков. ADO.NET снимает это ограничение. Теперь данные колонки можно без конвертаций считать в переменную необходимого типа. Стало возможным также создавать классы, производные от DataSet. Такие классы реализуют свойства, сходные с колонками DataSet-а и позволяют осуществлять строго типизированный доступ к полям DataSet-а. При этом можно пользоваться такими функциями среды, как CompleteWord. Генерацию строго типизированных DataSet-ов можно возложить на IDE.

Брандмауэры

Так как ADO.NET передает данные с помощью XML – есть возможность передавать эти данные через основную массу брандмауэров. При работе с ADO брандмауэры являлись большой проблемой, так как основная их масса не настроена на пропуск COM пакетов.

Таблица 1.

Название свойства	Описание свойства	Тип элемента коллекции	Описание свойства
Rows	Возвращает коллекцию строк таблицы	DataRow	Содержит все значения одной строки таблицы
Columns	Возвращает коллекцию колонок таблицы	DataColumn	Содержит информацию о столбце таблицы (имя, тип данных, и т.д.)
Constraints	Возвращает коллекцию описаний ограничений таблицы	Constraint	Содержит информацию об ограничении таблицы
ParentRelations	Возвращает коллекцию связей таблицы	DataRelation	Содержит информацию о связи с другой таблицей
ChildRelations	Возвращает коллекцию связей таблицы	DataRelation	Содержит информацию о связи с другой таблицей

Объекты для работы с данными

DataSet

DataSet хранит в себе набор таблиц (считанных полностью или частично из БД) с дополнительной информацией об их структуре и отношениях между ними. Такой подход позволяет более компактно представлять выбранные из источника данные. DataSet предоставляет объектную модель, которая позволяет получить доступ к его внутренним таблицам, их строкам, ячейкам и отношениям между таблицами. С помощью объектов ADO.Net, относящихся к провайдеру, можно выполнять запросы к БД и заполнять таблицы объекта DataSet.

Основную нагрузку по хранению и обработке данных в DataSet несет на себе коллекция DataTableCollection Tables, содержащая объекты типа DataTable. В объекте DataTable хранятся данные одной таблицы DataSet-а. DataTable в свою очередь содержит несколько коллекций, описывающих хранимую таблицу. В таблице 1 приведено описание свойств, позволяющих получить доступ к этим коллекциям.

Управляемые провайдеры (managed providers)

Раньше, до появления ADO.NET, ADO был надстройкой над OLE DB. Это означало, что при подключении к источнику данных с помощью ADO на самом деле вся работа выполнялась OLE DB. COM-природа OLE DB приводит к тому, что управляемое приложение вынуждено использовать прокси для взаимодействия с провайдером. По всей видимости, чтобы избежать неоправданных накладных расходов, разработчики Microsoft решили кроме поддержки OLE DB-провайдеров ввести прямые, так называемые (managed) управляемые провайдеры. Управляемый провайдер может использовать более низкоуровневые API, нежели OLE DB, специфичные для конкретного источника данных. Это позволяет производить необходимую обработку данных сразу в управляемом коде, не тратя времени на прокси-вызовы.

Управляемый провайдер данных – это набор объектов ADO.NET, разработанных для соединения с определенным источником данных. Все провайдеры обеспечивают одинаковый набор базовых методов и свойств, скрывая в своей реализации всю работу с API доступа к источнику данных. Необходимо только знать, какой управляемый провайдер должен использоваться в конкретном случае.

Текущая доступная версия .NET Framework (beta 2) представляет два управляемых провайдера:

Управляемый провайдер SQL для доступа к данным, расположенным на серверах баз данных Microsoft SQL Server 7 и выше (SqlConnection)
Управляемый провайдер для доступа к данным через OLE DB (OleDbConnection). Его можно использовать для доступа к базам данных Access и Oracle, Active Directory, каталогам LDAP и другим источникам, поддерживающим OLE DB или ODBC.

При использовании SQL провайдера используются объекты SqlConnection, SqlCommand, SqlDataReader и SqlDataAdapter (эти объекты находятся в пространстве имен System.Data.SqlClient). Если же вы работаете с провайдером для OLE DB, то соответственно необходимо использовать объекты OleDbConnection, OleDbCommand, OleDbDataReader и OleDbDataAdapter (пространство имен System.Data.OleDb).

Почему Microsoft вообще не отказалась от использования OLE DB? Все очень просто – провайдеры тяжелы в реализации, а для OLE DB и ODBC создано много провайдеров и драйверов.

DataReader

Использование DataSet оправдано, если необходимо передать данные в формате XML на другой компьютер, например, для их отображения и редактирования или дополнительной обработки. Использование DataSet также оправдано, если необходимо вывести данные в гриде или подключить к полям формы. Если же необходимые данные используются непосредственно там, где они запрашивались, и нужно всего лишь перебрать их по очереди, лучше воспользоваться объектом типа DataReader (SqlDataReader или OleDbDataReader).

Объект DataReader очень похож на forward-only read-only Recordset в ADO. Данный объект считывает одну запись из источника данных (с помощью метода Read()) за одно обращение и позволяет получить доступ к содержимому записи. Его имеет смысл применять в случае, если надо просто прочитать данные из источника данных и выполнить с ними какие-либо действия (например, вывести на ASP-страницу).

Примеры работы с ADO.NET

Теперь посмотрим на применение ADO.NET на практике. Мы рассмотрим примеры нескольких способов чтения данных из базы данных и их изменения. В качестве базы данных будет использоваться Pubs – демонстрационная база данных, входящая в состав поставки MS SQL. В примерах будут использоваться управляемый провайдер SQL. Полученный результат будет отображаться с помощью элемента управления asp:DataGrid. Новая версия ASP – ASP.Net позволяет работать с элементами Web-страницы как с элементами пользовательского интерфейса, размещенными на форме VB. asp:DataGrid – это один из новых компонентов, позволяющих упростить разработку Web-страниц.

Чтение данных в DataSet

Итак, первое ADO.NET приложение. В первом примере мы выведем на страницу содержимое таблицы Authors. Код страницы, выполняющий данное действие, приведен ниже:

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<script language="C#" runat="server">
  protected void Page_Load(Object sender, EventArgs e) 
  {
    SqlConnection myConn = 
      new SqlConnection("server=(local);database=pubs;Trusted_Connection=yes");
    SqlDataAdapter myCmd = new SqlDataAdapter("select * from authors", myConn);

    DataSet ds = new DataSet();
    myCmd.Fill(ds, "Authors");

    MyDataGrid.DataSource = ds.Tables["Authors"].DefaultView;
    MyDataGrid.DataBind();
  }
</script>

<body>
  <h3><font face="Verdana">Вывод данных с помощью DataGrid</font></h3>
  <ASP:DataGrid id="MyDataGrid" runat="server"
        BorderColor="black"
        BorderWidth="1"
        GridLines="Both"
        CellPadding="3"
        CellSpacing="0"
        Font-Name="Verdana"
        Font-Size="8pt"
        HeaderStyle-BackColor="#336600"
        HeaderStyle-ForeColor="#ffcc00"
  />
</body>
</html>

Если запустить эту страницу под отладчиком или открыть в броузере, мы получим результат, подобный изображенному на рисунке 2.

Обратите внимание на функцию Page_Load, в которой и выполняется весь наш код. В ней создается экземпляр объекта SqlConnection, представляющий собой подключение к источнику данных. В конструкторе этого объекта передается строка подключения к нужной базе данных. Затем создается экземпляр объекта SqlDataAdapter, играющий роль связующего звена между провайдером источника данных и объектом DataSet. В конструкторе этого объекта передаются SQL-запрос к базе данных и ранее созданный объект SqlConnection. Затем создается экземпляр DataSet, и с помощью метода Fill объекта SqldataAdapter заполняется необходимыми данными. Полученный DataSet связывается с элементом управления DataGrid. На этом создание простейшей страницы для вывода данных с помощью объекта DataSet закончено. Первая ADO.NET-страница готова. (Работа с DataGrid будет рассмотрена далее в этой статье в разделе «Связывание данных с элементами web-формы»).

Рисунок 2

Чтение данных. DataReader.

Теперь рассмотрим вопрос о доступе к данным с помощью объекта DataReader. Как уже говорилось ранее, DataReader создает forward-only read-only курсор, хорошо знакомый всем программистам, когда-либо использовавшим ADO. DataReader читает одну запись из источника данных за раз.

В примере будет показан способ чтения данных из таблицы jobs из базы Pubs и вывода прочитанных данных в ListBox. Код страницы, выполняющей это действие, приведен ниже:

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<html>
<script language="C#" runat="server">

protected void Page_Load(Object sender, EventArgs e) 
{
  if (!IsPostBack)
  {
    SqlConnection myConn = 
      new SqlConnection("server=(local);database=pubs;Trusted_Connection=yes");
    SqlCommand myCmd = new SqlCommand("select * from jobs", myConn);

    myConn.Open();
    SqlDataReader myReader = myCmd.ExecuteReader();

      while (myReader.Read())
      {
        ListBox1.Items.Add(new ListItem(myReader["job_desc"].ToString(), 
                                        myReader["job_id"].ToString()));
      }
      myConn.Close();
    }
  }

void SubmitBtn_Click(Object sender, EventArgs e)
{
  Label1.Text = "Текст: " + ListBox1.SelectedItem.Text + "<br> Значение: " + 
                ListBox1.SelectedItem.Value;
}

</script>
<body>

  <h3><font face="Arial">
      Вывод результатов запроса с помощью DataReader
  </font></h3>

  <form runat=server>

    <asp:ListBox id=ListBox1 Height="150px" runat="server"></asp:ListBox><p>
    <asp:button Text="Submit" OnClick="SubmitBtn_Click" runat="server" /><p>
    <asp:Label id=Label1 font-name="Verdana" font-size="10pt" runat="server"/>

  </form>

</body>
</html>

Примерный результат выполнения этой страницы вы можете увидеть здесь:

Рисунок 3. Вывод результатов запроса с помощью DataReader.

Весь код опять-таки выполняется в методе Page_Load. Как и в предыдущем примере, создается экземпляр объекта SqlConnection со строкой подключения к базе. Затем создается экземпляр объекта SqlCommand (а не SqlDataSetCommand, как в прошлом примере). Открывается соединение с базой данных (myConn.Open()) и вызывается метод ExecuteReader объекта SqlCommand, который возвращает экземпляр объекта SqlDataReader. После этого в цикле осуществляется проход по полученному myReader, и в ListBox добавляются значения, полученные из базы данных. В конце соединение с базой данных закрывается.

Код для отображения текста/значения выбранного элемента ListBox добавлен только для проверки.

Этот код приведен только для примера применения объекта DataReader. Намного эффективнее было бы, конечно же, заполнить DataSet и связать его с ListBox. Как уже упоминалось ранее, вопрос связывания данных с элементами управления на web-странице мы рассмотрим позже.

Чтение данных. Получение результатов выполнения хранимых процедур

В предыдущих примерах было описано получение результатов запросов select и их вывод на web-страницу. Но это не единственный способ получения каких-либо результатов от источника данных. Сейчас мы обратимся к работе с хранимыми процедурами – вызову хранимых процедур, передаче и возврату параметров в хранимых процедурах, а также возврату наборов записей из хранимых процедур с помощью ADO.NET.

Для начала рассмотрим вызов хранимой процедуры с передачей в нее параметров, и получение значения, возвращаемого хранимой процедурой с помощью оператора return. Для этого в базу pubs необходимо добавить следующую хранимую процедуру:

CREATE PROCEDURE getContractsCnt 
  @state char(2)
AS
begin
  declare @cnt int
  select @cnt = count(*) from authors where contract = 1 and state = @state
  return @cnt
end

Эта процедура возвращает количество авторов с активным контрактом, проживающих в указанном штате.

Теперь напишем ASP.NET-страницу, которая позволит нам вызвать эту процедуру. Ее код приведен ниже:

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<html>
<script language="C#" runat="server">

protected void Page_Load(Object sender, EventArgs e) 
{
}

void SubmitBtn_Click(Object sender, EventArgs e)
{
  SqlConnection myConn = 
     new SqlConnection("server=(local);database=pubs;Trusted_Connection=yes");
  SqlCommand myCmd = new SqlCommand("getContractsCnt", myConn);
  myCmd.CommandType = CommandType.StoredProcedure;

  myCmd.Parameters.Add(new SqlParameter("@state", SqlDbType.Char, 2));
  myCmd.Parameters["@state"].Value = tbState.Text;

  myCmd.Parameters.Add(new SqlParameter("RETURN_VALUE", SqlDbType.Int));
  myCmd.Parameters["RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;

  myConn.Open();
  myCmd.ExecuteNonQuery();
  lblRes.Text = "Количество контрактов: " + 
                myCmd.Parameters["RETURN_VALUE"].Value.ToString();
  myConn.Close();
}

</script>

<body>

<h3>Вызов хранимой процедуры с помощью SqlCommand</h3>
<form runat=server> 
<table cellspacing="5" cellpadding="0">
  <tr> 
    <td>Штат</td>
    <td> 
      <asp:textbox id="tbState" AutopostBack="false" text="" runat="server"/>
    </td>
  </tr>
  <tr> 
    <td> </td>
    <td> 
      <asp:button id="btnSubmit" onclick="SubmitBtn_Click" text="Submit" 
                  runat="server"/>
    </td>
  </tr>
  <tr> 
    <td colspan="2"> 
      <asp:label id="lblRes" text="" runat="server"/>
    </td>
  </tr>
</table>
</form>

</body>
</html>

Результат выполнения хранимой процедуры при введенном в поле «Штат» значении “CA” (без кавычек) вы можете увидеть здесь (Рисунок 4):

Рисунок 4

Весь полезный код выполняется в методе SubmitBtn_Click. В нем вызывается хранимая процедура, ей передаются параметры, и возвращаемое значение выводится на страницу. Итак, как обычно создается экземпляр объекта SqlConnection для подключения к базе данных. Затем создается экземпляр объекта SqlCommand, и ему передается имя вызываемой хранимой процедуры. Для передачи параметров используется свойство Parameters объекта SqlCommand, представляющее собой коллекцию объектов SqlParameter. Эта коллекция предназначена для определения всех параметров хранимой процедуры (входных, выходных и возвращаемого значения). С помощью метода Add этой коллекции в хранимую процедуру добавляются 2 параметра – @state (входной параметр) и RETURN_VALUE (параметр, через который будет возвращаться результат выполнения хранимой процедуры). Для параметра @state устанавливается значение, введенное в поле ввода, а для RETURN_VALUE устанавливается Direction в ParameterDirection.ReturnValue. Также необходимо установить CommandType у объекта SqlCommand в CommandType.StoredProcedure. Затем для реального вызова хранимой процедуры открывается соединение с источником данных, исполняется запрос (с помощью метода ExecuteNonQuery()), вернувшееся значение записывается в Label и соединение закрывается. Создание страницы для вызова хранимой процедуры завершено.

Чтение данных. Получение набора записей из хранимой процедуры

Последним примером, связанным с получением данных из источника, будет вызов хранимой процедуры, возвращающей набор данных. Для этого будет вызываться хранимая процедура reptg4 – немного измененный вариант хранимой процедуры reptg3 из базы pubs. Для отображения данных будет использоваться уже знакомая связка объектов SqlDataAdapter-DataSet.

Страница из примера будет искать книги с ценой, лежащей в пределах MinPrice – MaxPrice и входящих в категорию Type или в категорию, название которой содержит в себе слово “cook".

Вначале представлен текст хранимой процедуры, которую необходимо добавить в базу pubs:

CREATE PROCEDURE reptq4 @lolimit money, @hilimit money,
  @type char(12)
AS
  select pub_id, type, title_id, price
    from titles
    where    price >@lolimit AND price <@hilimit AND type = @type 
          OR type LIKE '%cook%'
    order by pub_id, type
Теперь код страницы:
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<html>
<script language="C#" runat="server">

protected void Page_Load(Object sender, EventArgs e) 
{
}

void SubmitBtn_Click(Object sender, EventArgs e)
{
  SqlConnection myConn = 
      new SqlConnection("server=(local);database=pubs;Trusted_Connection=yes");
  SqlDataAdapter myCmd = new SqlDataAdapter("reptq4", myConn);

  myCmd.SelectCommand.CommandType = CommandType.StoredProcedure;
  myCmd.SelectCommand.Parameters.Add(
          new SqlParameter("@lolimit", SqlDbType.Int)
  );
  myCmd.SelectCommand.Parameters["@lolimit"].Value = 
                                Int32.Parse(tbMinPrice.Text);

  myCmd.SelectCommand.Parameters.Add(
          new SqlParameter("@hilimit", SqlDbType.Int)
  );
  myCmd.SelectCommand.Parameters["@hilimit"].Value = 
                               Int32.Parse(tbMaxPrice.Text);

  myCmd.SelectCommand.Parameters.Add(
           new SqlParameter("@type", SqlDbType.Char, 12)
  );
  myCmd.SelectCommand.Parameters["@type"].Value = ddType.SelectedItem.Value;

  DataSet ds = new DataSet();
  myCmd.Fill(ds, "titles");

  MyDataGrid.DataSource = ds.Tables["titles"].DefaultView;
  MyDataGrid.DataBind();
}

</script>

<body>

  <h3><font face="Arial">
     Вывод результатов вызова хранимой процедуры с помощью DataSet
  </font></h3>
  <form runat=server>
    <table cellspacing="5" cellpadding="0">
      <tr> 
        <td>Мин. цена</td>
        <td>Макс. цена</td>
        <td>Тип книги</td>
      </tr>
      <tr> 
        <td> 
          <asp:textbox id="tbMinPrice" runat="server"/>
        </td>
        <td> 
          <asp:textbox id="tbMaxPrice" runat="server"/>
        </td>
        <td> 
           <asp:DropDownList id=ddType runat="server">
             <asp:ListItem>business</asp:ListItem> 
             <asp:ListItem>mod_cook</asp:ListItem>
             <asp:ListItem>popular_comp</asp:ListItem>
             <asp:ListItem>psychology</asp:ListItem>
             <asp:ListItem>trad_cook</asp:ListItem> 
           </asp:DropDownList> </td>
    </tr>
    <tr> 
      <td colspan="3">
        <asp:button id="btnSubmit" onclick="SubmitBtn_Click" text="Submit" 
                    runat="server"/>
      </td>
    </tr>
  </table>
  <p>
  <ASP:DataGrid id="MyDataGrid" runat="server"
     BorderColor="black"
     BorderWidth="1"
     GridLines="Both"
     CellPadding="3"
     CellSpacing="0"
     Font-Name="Verdana"
     Font-Size="8pt"
     HeaderStyle-BackColor="#336600"
     HeaderStyle-ForeColor="#ffcc00"
  /> 
</form>
</body>
</html>

И результаты:

Рисунок 5

Рассмотрим нашу страницу. Во-первых, мы должны были обеспечить возможность передачи параметров в хранимую процедуру. Для этой цели мы разместили на странице текстовые поля tbMinPrice и tbMaxPrice и выпадающий список ccType, предназначенные соответственно для ввода значений минимальной и максимальной цены, и выбора типа книги. Мы также поместили элемент DataGrid для вывода полученных результатов.

Весь код, выполняющий вызов хранимой процедуры и отображающий результат вызова на странице, расположен в методе SubmitBtn_Click. Он отличается от кода, приведенного в первом примере, только тем, что в данном случае данные получаются не с помощью команды SQL select, а с помощью вызова хранимой процедуры. Остановимся на этом моменте более подробно.

Объект SqlDataAdapter имеет свойства SelectCommand, InsertCommand, UpdateCommand и DeleteCommand, имеющие тип SqlCommand. Пример работы с объектом SqlCommand уже был рассмотрен ранее. Более подробно работу с этими свойствами мы рассмотрим немного позднее, а сейчас только посмотрим, каким образом мы можем вызвать хранимую процедуру и передать ей параметры.

Итак, после создания объекта SqlDataAdapter необходимо изменить тип SelectCommand на CommandType.StoredProcedure и потом, как и в предыдущем примере, заполнить коллекцию Parameters этого объекта для передачи параметров в хранимую процедуру. После передачи всех параметров необходимо (как и в первом примере с использованием DataSet) вызвать метод Fill для заполнения DataSet результатами выполнения хранимой процедуры. Связывание DataSet с DataGrid выводит полученный результат на страницу.

Изменение данных. Простые примеры

Мы рассмотрели вопрос вызова SELECT запросов с помощью объекта SqlCommand. Очень часто вы будете применять этот объект и для изменения данных – для выполнения одиночных SQL команд INSERT, UPDATE или DELETE, или вызова хранимых процедур, производящих изменения в базе данных. Далее будет кратко рассмотрен пример страницы, производящей такие изменения в таблице jobs базы pubs. Вначале в таблицу будет добавлена новая запись, потом эта запись будет изменена и затем удалена. Добавление будет сделано с помощью конструируемого запроса INSERT, обновление - с помощью параметризированного запроса, а удалена запись будет вызовом хранимой процедуры без параметров. После каждой операции в DataGrid на страницу будут выводиться текущие данные таблицы jobs.

Код процедуры удаления:

CREATE PROCEDURE delJobs 
AS
  delete from jobs where job_id > 14
Код страницы:
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<html>
<script language="C#" runat="server">

protected void Page_Load(Object sender, EventArgs e) 
{
  SqlConnection myConn = 
      new SqlConnection("server=(local);database=pubs;Trusted_Connection=yes");
  if (!IsPostBack)
  {
    SqlCommand myCmd = new SqlCommand("delJobs", myConn);
    myCmd.CommandType = CommandType.StoredProcedure;
    myConn.Open();
    myCmd.ExecuteNonQuery();
    myConn.Close();
    ViewState["currStep"] = 0;
  }

  switch((int) ViewState["currStep"])
  {
  case 0:
    cmdBtn.Text = Добавить";
    ViewState["currStep"] = 1;
    tb_desc.Text = "Новая работа";
    tb_min.Text = "10";
    tb_max.Text = "50";
    break;
  case 1:
    SqlCommand insCmd = new SqlCommand("insert into jobs values('" + 
       tb_desc.Text + "', " + tb_min.Text + ", " + tb_max.Text + ")", myConn
    );
    myConn.Open();
    insCmd.ExecuteNonQuery();
    myConn.Close();
    ViewState["currStep"] = 2;
    cmdBtn.Text = "Изменить";
    tb_desc.Text = "Хорошая работа";
    tb_min.Text = "100";
    tb_max.Text = "200";
    break; 
  case 2:
    SqlCommand updCmd = new SqlCommand(@"update jobs set job_desc = @job_desc, 
         min_lvl = @min_lvl, max_lvl = @max_lvl 
          where job_id = (select max(job_id) from jobs)", myConn
    );
    updCmd.Parameters.Add(new SqlParameter("@job_desc", SqlDbType.VarChar,50));
    updCmd.Parameters["@job_desc"].Value = tb_desc.Text;
    updCmd.Parameters.Add(new SqlParameter("@min_lvl", SqlDbType.TinyInt));
    updCmd.Parameters["@min_lvl"].Value = Int32.Parse(tb_min.Text);
    updCmd.Parameters.Add(new SqlParameter("@max_lvl", SqlDbType.TinyInt));
    updCmd.Parameters["@max_lvl"].Value = Int32.Parse(tb_max.Text);
    myConn.Open();
    updCmd.ExecuteNonQuery();
    myConn.Close();
    ViewState["currStep"] = 3;
    cmdBtn.Text = "Удалить";
    tb_desc.Visible = false;
    tb_min.Visible = false;
    tb_max.Visible = false;
    break;
  case 3:            
    SqlCommand delCmd = new SqlCommand("delJobs", myConn);
    delCmd.CommandType = CommandType.StoredProcedure;
    myConn.Open();
    delCmd.ExecuteNonQuery();
    myConn.Close();
    ViewState["currStep"] = 0;
    tb_desc.Visible = true;
    tb_min.Visible = true;
    tb_max.Visible = true;
    goto case 0;
}

  SqlDataAdapter myAdapter = new SqlDataAdapter("select * from jobs", myConn);
  DataSet ds = new DataSet();
  myAdapter.Fill(ds, "jobs");
  MyDataGrid.DataSource = ds.Tables["jobs"].DefaultView;
  MyDataGrid.DataBind();
}
</script>
<body>
  <h3><font face="Arial">
     Добавление/изменение/удаление данных с помощью SqlCommand
  </font></h3>

  <form runat=server>
    <table cellspacing="5" cellpadding="0">
      <tr> 
        <td>job_desc</td>
        <td>min_lvl</td>
        <td>max_lvl</td>
      </tr>
      <tr> 
        <td> 
          <asp:textbox id="tb_desc" runat="server"/>
        </td>
        <td>
          <asp:textbox id="tb_min" runat="server"/>
        </td>
        <td>
          <asp:textbox id="tb_max" runat="server"/>
        </td>
      </tr>
      <tr> 
        <td colspan="3"> 
          <asp:button id="cmdBtn" Text="Submit" runat="server" />
        </td>
      </tr>
    </table>
  </form>
  <ASP:DataGrid id="MyDataGrid" runat="server"
    BorderColor="black"
    BorderWidth="1"
    GridLines="Both"
    CellPadding="3"
    CellSpacing="0"
    Font-Name="Verdana"
    Font-Size="8pt"
    HeaderStyle-BackColor="#336600"
    HeaderStyle-ForeColor="#ffcc00"
  />
</body>
</html>

Весь код, выполняющий обработку данных, находится в методе Page_OnLoad. При первом запуске данной страницы выполняется вызов хранимой процедуры delJobs, которая удаляет все ранее введенные нами jobs. Затем, в зависимости от значения ViewState["currStep”] (переменной, в которой сохраняется текущий шаг страницы – добавление, изменение или удаление) выполняются соответствующие действия. Рассмотрим их более подробно.

Добавление данных. Для добавления данных используется команда INSERT, текст которой создается с помощью введенных значений.

Обновление данных. Для обновления используется команда UPDATE с параметрами. Затем, как и при работе с хранимыми процедурами, требующими параметров, заполняется коллекция Parameters объекта SqlCommand и выполняется запрос.

Удаление данных. Для удаления записей просто вызывается приведенная выше хранимая процедура.

Изменение данных. Работа связки DataSet + DataAdapter.

.NET Framework предоставляет очень мощную возможность редактирования данных с помощью объектов DataSet и SqlDataAdapter (AdoDataAdapter). После заполнения DataSet с помощью DataAdapter вы можете без проблем редактировать данные в DataSet. А когда придет время сохранить изменения в данных, это легко можно будет сделать с помощью метода Update объекта DataAdapter.

Рассмотрим работу данного алгоритма на примере. Заполним DataSet данными из таблицы jobs. Затем изменим данные в одной строке и добавим еще одну строку, после чего запишем данные обратно в базу и заново их прочтем для того, чтобы вывести на страницу результат и проконтролировать выполнение операций изменения и добавления.

Код страницы:

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<html>
<script language="C#" runat="server">

protected void Page_Load(Object sender, EventArgs e) 
{
  SqlConnection myConn = 
     new SqlConnection("server=(local);database=pubs;Trusted_Connection=yes");
  if (!IsPostBack)
  {
    SqlCommand myCmd = new SqlCommand("delete from jobs where job_id > 14",
                                      myConn);
    myConn.Open();
    myCmd.ExecuteNonQuery();
    myCmd.CommandText = 
        "update jobs set job_desc='Designer' where job_id = 14";
    myCmd.ExecuteNonQuery();
  }
  else
  {
    SqlDataAdapter myAdapter = 
           new SqlDataAdapter("select * from jobs", myConn);
    DataSet ds = new DataSet();
    myAdapter.Fill(ds, "jobs");

    ds.Tables["jobs"].Rows[13]["job_desc"]="Web Designer";
    DataRow dr = ds.Tables["jobs"].NewRow();
    dr["job_desc"] = "Новая работа";
    dr["min_lvl"] = 15;
    dr["max_lvl"] = 25;
    ds.Tables["jobs"].Rows.Add(dr);

    SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter);
    myAdapter.Update(ds, "jobs");
    cmdBtn.Visible = false;
  }
  BindData();
}

protected void BindData()
{
  SqlConnection myConn = 
     new SqlConnection("server=(local);database=pubs;Trusted_Connection=yes");
  SqlDataAdapter myAdapter = new SqlDataAdapter("select * from jobs", myConn);
  DataSet ds = new DataSet();
  myAdapter.Fill(ds, "jobs");
  MyDataGrid.DataSource = ds.Tables["jobs"].DefaultView;
  MyDataGrid.DataBind();
}

</script>

<body>

  <h3><font face="Arial">
      Редактирование набора данных с помощью DataSet и SqlDataAdapter
   </font></h3>

<form runat=server>
  <ASP:DataGrid id="MyDataGrid" runat="server"
    BorderColor="black"
    BorderWidth="1"
    GridLines="Both"
    CellPadding="3"
    CellSpacing="0"
    Font-Name="Verdana"
    Font-Size="8pt"
    HeaderStyle-BackColor="#336600"
    HeaderStyle-ForeColor="#ffcc00"
  />
  <asp:button id="cmdBtn" Text="Изменить DataSet" runat="server" />
</form>
</body>
</html>

Первоначальный вид страницы:

Рисунок 6

Страница после нажатия кнопки (изменена строка с job_id = 14 и добавлена еще одна строка):

Рисунок 7

Возможность использования связки DataSet + DataAdapter состоит в том, что объект SqlDataAdapter кроме свойства SelectCommand (команда, с помощью которой происходит выборка данных из источника данных) использует также команды для изменения данных (DeleteCommand, InsertCommand и UpdateCommand). Вся сложность при этом заключается в правильном присвоении значений (команд SQL) этим свойствам для корректного обновления данных в базе данных. Большую помощь в этом оказывает объект SqlCommandBuilder, предназначенный для автоматической генерации команд, использующихся для обновления данных. Ну а дальше все просто. Создается объект SqlDataAdapter, заполняется DataSet, и производятся манипуляции с данными. Затем в нужный момент с помощью SqlCommandBuilder генерируются команды для обновления данных, и вызывается метод SqlDataAdapter.Update для записи изменений данных в базу...

<...>

Заключение

В данной статье мы только начали разговор о работе с новой версией ADO – ADO.NET. В следующем номере журнала мы продолжим рассказ как о работе с ней из приложений ASP.NET, так и о работе с ADO.NET из Windows-приложений и построении многоуровневых приложений.