Опубликован: 07.05.2010 | Доступ: свободный | Студентов: 1678 / 62 | Оценка: 4.56 / 4.06 | Длительность: 34:11:00
Лекция 13:

Привязка данных ADO.NET

Передача параметра источнику данных через элемент управления

Применение параметризованных команд выполняется в тех случаях, если необходимо работать с двумя и более связанными таблицами. Для каждой таблицы нужно создать свой источник данных. Нужно связать эти источники с элементами отображения так, чтобы при выборе данного в одном элементе происходила выборка в другой элемент всех данных из второй таблицы с требуемым значением ключа.

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

  • Создайте страницу с объединенным кодом и именем SqlDataSourceParameters.aspx. Назначьте ее стартовой
  • Удалите контейнер сценария <script runat="server">...</script> как ненужный для данного примера
  • Из вкладки Data панели Toolbox добавьте на страницу первый источник данных SqlDataSource, который будет поставлять список всех городов из таблицы Employees. Назовите его sourceEmployeeCities
  • Из вкладки Standard панели Toolbox добавьте на страницу элемент управления DropDownList, который будет отображать все города, выбранные источником sourceEmployeeCities из таблицы Employees. Назовите его lstCities
  • Из вкладки Data панели Toolbox добавьте на страницу второй источник данных, который будет выбирать всех сотрудников из таблицы Employees, проживающих в переданном ему из раскрывающегося списка lstCities через параметр городе. Назовите его sourceEmployees
  • Из вкладки Data панели Toolbox добавьте на страницу элемент управления GridView с именем GridView1, который будет отображать список сотрудников, проживающих в выбранном городе и полученных из источника sourceEmployees
  • Настройте объекты страницы так, как показано ниже.

При добавлении параметров пользуйтесь удобным редактором оболочки, позволяющим создавать команды и определять параметры. Редактор вызывается при выделенном элементе второго источника sourceEmployees щелчком на кнопке ( ...) свойства SelectQuery в панели Properties

Обратите внимание, что обратную отсылку мы выполняем за счет включенного свойства AutoPostBack раскрывающегося списка lstCities. Обратите также внимание, что правильный перенос строковой константы в свойстве SelectCommand второго источника не влияет на возвращаемые SQL-командой результаты.

<%@ Page Language="C#" %>
    
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <h2>Выберите город</h2>
        <asp:SqlDataSource ID="sourceEmployeeCities" runat="server" 
            ConnectionString="<%$ connectionStrings:Northwind %>" 
            ProviderName="System.Data.SqlClient" 
            SelectCommand="SELECT DISTINCT City FROM Employees" />
        <asp:DropDownList ID="lstCities" runat="server" 
            AutoPostBack="True" DataSourceID="sourceEmployeeCities" 
            DataTextField="City" />
            
        <h2>Проживающие сотрудники</h2>
        <asp:SqlDataSource ID="sourceEmployees" runat="server" 
            ConnectionString="<%$ connectionStrings:Northwind %>" 
            ProviderName="System.Data.SqlClient" 
            SelectCommand="SELECT EmployeeID, FirstName, LastName, 
                Title, City FROM Employees WHERE City=@City">
            <SelectParameters>
                <asp:ControlParameter Name="City" ControlID="lstCities" 
                    PropertyName="SelectedValue" />
            </SelectParameters>
        </asp:SqlDataSource>
        <asp:GridView ID="GridView1" runat="server" 
            DataSourceID="sourceEmployees" />
    </div>
    </form>
</body>
</html>
  • Исполните страницу SqlDataSourceParameters.aspx для получения следующего результата

Выберите город


Проживающие сотрудники

EmployeeID FirstName LastName Title City
5 Steven Buchanan Sales Manager London
6 Michael Suyama Sales Representative London
7 Robert King Sales Representative London
9 Anne Dodsworth Sales Representative London

Здесь мы всю привязку данных источников данных к элементам управления выполнили на этапе проектирования без всякого кода.

Вернемся к редактору команд и параметров, представленному ранее окном

После набора команды в области SELECT command щелчок на кнопке Refresh Parameters сканирует введенную команду и заполняет другие области и раскрывающиеся списки редактора нужной информацией, из которой можно потом быстро выбрать требуемое значение. В списке Parameter source перечислены типы элементов, которые могут передавать значения параметров в источник данных:

  • Cookie
  • Control
  • Form
  • Profile
  • QueryString
  • Session

В нашем случае используется элемент управления DropDownList, поэтому мы установили тип параметра Control.

Передача параметра источнику данных через строку запроса

Предыдущий пример можно разбить на две страницы, чтобы продемонстрировать способ передачи параметра через строку запроса QueryString. На первой странице мы определим списковый элемент управления, который отобразит все города, находящиеся в таблице Employees учебной базы данных Northwind. На второй странице будем отображать список сотрудников, проживающих в выбранном городе. Информацию о городе будем перенаправлять в источник на второй странице через строку запроса. Там же и отобразим результаты, полученные источником второй страницы.

  • Создайте новую страницу с объединенным кодом и именем QueryParameter1.aspx. Назначьте ее стартовой
  • Поместите на страницу источник данных SqlDataSource, списочный элемент ListBox и кнопку Button
  • Настройте свойства элементов так, как показано ниже, включая обработчики, полученные двойным щелчком на свободном месте страницы ( Page_Load ) и на кнопке ( cmdGo_Click )
    <%@ Page Language="C#" %>
        
    <script runat="server">
        
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
                lstCities.SelectedIndex = 0;
        }
        
        protected void cmdGo_Click(object sender, EventArgs e)
        {
            Response.Redirect("QueryParameter2.aspx?selectCity="
                + lstCities.SelectedValue);
        }
    </script>
        
    <html xmlns="http://www.w3.org/1999/xhtml" >
    <head runat="server">
        <title>Страница выбора города</title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <asp:SqlDataSource ID="sourceEmployeeCities" runat="server"
                ProviderName="System.Data.SqlClient"
                ConnectionString="<%$ ConnectionStrings:Northwind %>"
                SelectCommand="SELECT DISTINCT City FROM Employees">
            </asp:SqlDataSource>
            
            <h2>
                Выберите город</h2>
            <asp:ListBox ID="lstCities" runat="server"
                DataSourceID="sourceEmployeeCities"
                DataTextField="City">
            </asp:ListBox>
            <br />
            <br />
            <asp:Button ID="cmdGo" runat="server" Text="Применить" 
                OnClick="cmdGo_Click" />
        </div>
        </form>
    </body>
    </html>
  • Создайте новую страницу с объединенным кодом и именем QueryParameter2.aspx
  • Поместите на страницу источник данных SqlDataSource и элемент GridView
  • Настройте свойства элементов так, как показано ниже
    <%@ Page Language="C#" %>
        
    <html xmlns="http://www.w3.org/1999/xhtml" >
    <head runat="server">
        <title>Страница проживающих сотрудников</title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <asp:SqlDataSource ID="sourceEmployees" runat="server" 
                ConnectionString="<%$ ConnectionStrings:Northwind %>"
                ProviderName="System.Data.SqlClient" 
                SelectCommand="SELECT EmployeeID, FirstName, LastName, Title, 
                    City FROM Employees WHERE City=@City">
                <SelectParameters>
                    <asp:QueryStringParameter Name="City" QueryStringField="selectCity" />
                </SelectParameters>
            </asp:SqlDataSource>
            
            <h2>
                Проживающие сотрудники</h2>
            <asp:GridView ID="GridView1" runat="server" DataSourceID="sourceEmployees" >
            </asp:GridView>
        </div>
        </form>
    </body>
    </html>
  • Исполните страницу QueryParameter1.aspx и убедитесь, что запрос, сформированный на ней и направленный странице QueryParameter2.aspx на обработку, дает тот же самый результат, что и в предыдущем примере

Передача параметра источнику данных в хранимых процедурах

Мы уже говорили о достоинствах хранимых процедур, которые представляют собой именованные SQL-команды, хранимые прямо в базе данных. Они более надежны, поскольку заранее проверены и отлажены. Они более безопасны, поскольку недоступны изменениям со стороны кода страницы, а значит недоступны злоумышленнику. Они упрощают часть нашей .aspx -страницы за счет переноса действительного SQl-запроса в базу данных, который может достигать на странице внушительных размеров. Хранимые процедуры позволяют передавать параметры, в том числе в источники данных.

Прежде всего, создадим и запишем в базу данных хранимую процедуру программным способом на этапе выполнения. Для этого создадим страницу.

  • Скопируйте командой Add Existing Item из приложения WebSite8 разработанную нами ранее страницу SaveStoredProcedure.aspx и переименуйте ее в SaveStoredProcedure1.aspx
  • Откройте на редактирование страницу SaveStoredProcedure1.aspx и откорректируйте ее так
    <%@ Page Language="C#" %>
        
    <script runat="server">
        
        // Объявления хранимых процедур
        // Выборка городов
        string sql1 =
            "CREATE PROCEDURE GetCityes "
          + "AS "
          + "SELECT DISTINCT City FROM Employees";
        
        // Выборка сотрудников для выбранного города
        string sql2 =
            "CREATE PROCEDURE GetEmployeesByCity "
          + "@paramCity		varchar(15) "
          + "AS "
          + "SELECT EmployeeID, FirstName, LastName, Title, City "
          + "FROM Employees WHERE City=@paramCity";
        
        protected void Page_Load(object sender, EventArgs e)
        {
            // Извлекли строку соединения из web.config
            string connectString = System.Web.Configuration.WebConfigurationManager.
                ConnectionStrings["Northwind"].ConnectionString;
        
            // Создали объект соединения
            System.Data.SqlClient.SqlConnection con =
                new System.Data.SqlClient.SqlConnection(connectString);
        
            // Создали объект Command
            System.Data.SqlClient.SqlCommand cmd =
                new System.Data.SqlClient.SqlCommand();
        
            // Настроили объект Command
            cmd.Connection = con;
            cmd.CommandType = System.Data.CommandType.Text;
        
            lblInfo.Text = "";
        
            // Открываем соединение в безопасном режиме
            try
            {
                con.Open();
            }
            catch
            {
                con.Close();
                return;
            }
        
            // Выполняем команды в безопасном режиме
            // Удаляем процедуры на случай повторного запуска этой страницы
            try
            {
                cmd.CommandText = "DROP PROCEDURE GetCityes";
                cmd.ExecuteNonQuery();
                lblInfo.Text += "Прошли \"DROP PROCEDURE GetCityes\"<br />";
            }
            catch { }
            try
            {
                cmd.CommandText = "DROP PROCEDURE GetEmployeesByCity";
                cmd.ExecuteNonQuery();
                lblInfo.Text += "Прошли \"DROP PROC GetEmployeesByCity\"<br />";
            }
            catch { }
        
            // Выполняем команды в безопасном режиме
            // Добавляем в базу данных хранимые процедуры
            try
            {
                cmd.CommandText = sql1;
                cmd.ExecuteNonQuery();
                lblInfo.Text += "Прошли \"CREATE PROCEDURE GetCityes\"<br />";
            }
            catch { }
            try
            {
                cmd.CommandText = sql2;
                cmd.ExecuteNonQuery();
                lblInfo.Text += "Прошли \"CREATE PROCEDURE GetEmployeesByCity\"<br />";
            }
            finally
            {
                con.Close();
            }
        }
    </script>
        
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head id="Head1" runat="server">
        <title>Untitled Page</title>
    </head>
    <body>
        <form id="form1" runat="server">
            <asp:Label ID="lblInfo" runat="server" />
        </form>
    </body>
    </html>
  • Исполните страницу SaveStoredProcedure1.aspx, чтобы добавить в базу данных Northwind две хранимых процедуры, одна из которых содержит параметр

Теперь осталось воспользоваться этими хранимыми процедурами в источниках данных. Не мудрствуя лукаво, мы просто заменим в двух предыдущих примерах строку SQL-запроса источников на имя хранимой процедуры, а также изменим тип команды.

  • Скопируйте страницу SqlDataSourceParameters.aspx и присвойте ей имя StoredProcedureParam.aspx. Назначьте страницу StoredProcedureParam.aspx стартовой
  • Внесите в страницу StoredProcedureParam.aspx небольшие изменения, чтобы она стала такой
    <%@ Page Language="C#" %>
        
    <html xmlns="http://www.w3.org/1999/xhtml" >
    <head runat="server">
        <title>Untitled Page</title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <h2>Выберите город</h2>
            <asp:SqlDataSource ID="sourceEmployeeCities" runat="server" 
                ConnectionString="<%$ connectionStrings:Northwind %>"
                ProviderName="System.Data.SqlClient" 
                SelectCommand="GetCityes" 
                SelectCommandType="StoredProcedure">
            </asp:SqlDataSource>
            <asp:DropDownList ID="lstCities" runat="server" 
                AutoPostBack="True" DataSourceID="sourceEmployeeCities" 
                DataTextField="City" />
                
            <h2>Проживающие сотрудники</h2>
            <asp:SqlDataSource ID="sourceEmployees" runat="server" 
                ConnectionString="<%$ connectionStrings:Northwind %>"
                ProviderName="System.Data.SqlClient" 
                SelectCommand="GetEmployeesByCity" 
                SelectCommandType="StoredProcedure">
                <SelectParameters>
                    <asp:ControlParameter ControlID="lstCities" 
                        Name="paramCity" PropertyName="SelectedValue" />
                </SelectParameters>
            </asp:SqlDataSource>
            <asp:GridView ID="GridView1" runat="server" 
                DataSourceID="sourceEmployees" />
        </div>
        </form>
    </body>
    </html>
  • Исполните страницу StoredProcedureParam.aspx и убедитесь, что она выдает точно такие же результаты, как и страница SqlDataSourceParameters.aspx

Выберите город


Проживающие сотрудники

EmployeeID FirstName LastName Title City
5 Steven Buchanan Sales Manager London
6 Michael Suyama Sales Representative London
7 Robert King Sales Representative London
9 Anne Dodsworth Sales Representative London

Теперь попробуем проверить работу хранимых процедур при передаче параметра через строку запроса из одной страницы в другую.

  • Выделите в панели Solution Explorer сразу две страницы QueryParameter1.aspx и QueryParameter2.aspx и создайте их копии с именами QueryStoredProcParam1.aspx и QueryStoredProcParam2.aspx. Назначьте страницу QueryStoredProcParam1.aspx стартовой
  • Заполните обе новых страницы следующими поправками
    <%@ Page Language="C#" %>
        
    <script runat="server">
        
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
                lstCities.SelectedIndex = 0;
        }
        
        protected void cmdGo_Click(object sender, EventArgs e)
        {
            Response.Redirect("QueryStoredProcParam2.aspx?selectCity="
                + lstCities.SelectedValue);
        }
    </script>
        
    <html xmlns="http://www.w3.org/1999/xhtml" >
    <head runat="server">
        <title>Страница выбора города</title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <asp:SqlDataSource ID="sourceEmployeeCities" runat="server" 
                ConnectionString="<%$ ConnectionStrings:Northwind %>"
                ProviderName="System.Data.SqlClient" 
                SelectCommand="GetCityes" 
                SelectCommandType="StoredProcedure">
            </asp:SqlDataSource>
            
            <h2>
                Выберите город</h2>
            <asp:ListBox ID="lstCities" runat="server"
                DataSourceID="sourceEmployeeCities"
                DataTextField="City">
            </asp:ListBox>
            <br />
            <br />
            <asp:Button ID="cmdGo" runat="server" Text="Применить" 
                OnClick="cmdGo_Click" />
        </div>
        </form>
    </body>
    </html>
    <%@ Page Language="C#" %>
        
    <html xmlns="http://www.w3.org/1999/xhtml" >
    <head runat="server">
        <title>Страница проживающих сотрудников</title>
    </head>
    <body>
        <form id="form1" runat="server">
        <div>
            <asp:SqlDataSource ID="sourceEmployees" runat="server" 
                ConnectionString="<%$ ConnectionStrings:Northwind %>"
                ProviderName="System.Data.SqlClient" 
                SelectCommand="GetEmployeesByCity" 
                SelectCommandType="StoredProcedure">
                <SelectParameters>
                    <asp:QueryStringParameter Name="paramCity" QueryStringField="selectCity" />
                </SelectParameters>
            </asp:SqlDataSource>
            
            <h2>
                Проживающие сотрудники</h2>
            <asp:GridView ID="GridView1" runat="server" DataSourceID="sourceEmployees" >
            </asp:GridView>
        </div>
        </form>
    </body>
    </html>
  • Исполните страницу QueryStoredProcParam1.aspx и убедитесь, что получился тот же результат, что и в соответствующем примере без использования хранимых процедур, но код вышел более удобным (все переходим на хранимые процедуры!)