Глава 3. Работа с сервером SQL в Visual Studio 2005 (ASP.NET 2)
Параграф 1. Механизмы доступа к данным, используемые в Visual Studio 2003
Параграф 2. Новые элементы доступа к данным, используемые в Visual Studio 2005
Параграф 3. Методика отображения данных с использованием SqlDataSource
Параграф 4. Возможности редактирования данных в SqlDataSource
Параграф 5. События и методы SqlDataSource и их использование при выборке и редактировании данных
Параграф 6. Создание отчетов master-detai
Параграф 7. Кэширование данных в SqlDataSource
Параграф 8. Возможности фильтрации в SqlDataSource
Параграф 1. Механизмы доступа к данным, используемые в Visual Studio 2003
Visual Studio 2003 имела ряд специализированных контролов, позволяющих обеспечить достаточно простой доступ к данным с использованием технологии ADO.NET. Для различных баз данных был разработан набор специализированных элементов управления (все они представлены также и в виде контролов), которые обеспечивали соединение с базой данных и манипулирование данными. Например, для SQL сервера: SqlConnection, SqlCommand, SqlDataAdapter, SqlDataReader, DataSet, DataTable, DataView. В Visual Studio 2005/2008 данные элементы также представлены, и программисты, без каких либо ограничений, могут пользоваться и привычными контролами и наработанными конструкциями кода. Следующий фрагмент реально работающего кода, наглядно показывает, как выполнить обмен данными с SQL сервером (используются SqlConnection, SqlCommand, SqlDataAdapter, DataSet, DataTable и DataRow).
using System.Data.SqlClient;
......
//На основе строки с connectionstring, создается объект SqlConnection
//Строка connectionstring может выглядеть примерно так:
private string constring="Server=ИМЯ_СЕРВЕРА;database=ИМЯ_БАЗЫ;user id=ИМЯ_ПОЛЬЗОВАТЕЛЯ;
Password="MY_Password"; Trusted_Connection=no;Connection Lifetime=10;
connection timeout=15; Max Pool Size=50;"
//SQL оператор может быть примерно таким
private string sSql="select * from dept";
......
using(SqlConnection sqlConn = new SqlConnection(constring))
{
try
{
//Открываем соединение с БД
sqlConn.Open();
//На основе SQL команды создается объект SqlCommand
//и определяются его параметры
SqlCommand sqlcmd=new SqlCommand(sSql,sqlConn);
sqlcmd.CommandType = CommandType.Text;
//Создается объект DataSet для приема данных
DataSet MyDataSet = new DataSet();
//Создается объект SqlDataAdapter для обращения за данными
//и производится выбор данных из БД
SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
//Данные переносятся в DataSet и соединение разрывается
//несмотря на не закрытый using(SqlConnection ...
da.Fill(MyDataSet);
//Создается объект DataTable для приема данных
DataTable MyDatatable=MyDataSet.Tables[0];
//Далее данные можно было отобразить, например, в DataGrid,
//или создать представление данных и затем их отобразить,
//или (как показано ниже) обращаться к элементам данных
//через обращение к строковому представлению данных DataRow[]
DataRow[] datarows = MyDatatable.Select();
if(datarows.Length > 0)
{
foreach(DataRow datarow in datarows)
{
//Взять из выделенной (очередной строки) необходимые данные
string s=datarow["имя_столбца"].ToString();
...........................
}
}
}
catch(Exception ex)
{
//обработка сбоя
}
}
В следующем фрагменте показано использование SqlDataReader для выбора данных.
using(SqlConnection sqlConn = new SqlConnection(constring))
{
try
{
//Открываем соединение с БД
sqlConn.Open();
//На основе строки SQL команды, например
//string sSql="select * from dept"; создается объект SqlCommand
//и определяются его параметры
SqlCommand sqlcmd=new SqlCommand(sSql,sqlConn);
sqlcmd.CommandType = CommandType.Text;
//Создаем объект SqlDataReader
SqlDataReader myReader =null;
//В отличии от предыдущего примера, когда все данные переносились
//в DataSet и соединение с БД разрывалось
//будем последовательно читать данные их БД
using(myReader =sqlcmd.ExecuteReader())//CommandBehavior.CloseConnection);
{
while(myReader.Read())
{
//Взять из прочитанной строки необходимые данные
string s=myReader["имя_столбца"].ToString();
}
}
}
catch(Exception ex)
{
//обработка сбоя
}
}
Более подробно о использовании рассмотренных механизмов доступа, а также о работе с различными типами данных при обмене данными с серверами SQL и Oracle можно ознакомиться в других главах раздела: Работа с сервером SQL в Visual Studio 2003 и Особенности работы с Oracle.
При всей простоте рассмотренных механизмов доступа к данным и учитывая, то, что программист код пишет один раз, а затем его добросовестно копирует, все же, обилие кода является явным недостатком. Это особенно заметно, когда используются вложенные обращения (к примеру, из БД1 выбираются параметры, по ним выбираются другие параметры из другой базы или таблицы и т.д.). По-видимому, это и подтолкнуло разработчиков Visual Studio 2005 пойти по пути "укрупнения" элементов доступа. Сама технология доступа изменений не претерпела.
Параграф 2. Новые элементы доступа к данным, используемые в Visual Studio 2005
Visual Studio 2005, вместо обилия ранее используемых элементов управления для доступа к данным своей предшественницы, использует всего 5. Это SqlDataSource, AccessDataSource, ObjectDataSource, XmlDataSource и SiteMapDataSource. Первые три используются для работы с табличными источниками данных, остальные с XML данными. Элементов отображения данных стало больше, и они стали более функциональными. Это GridView, DetalisView, FormView, ReportView и знакомые уже нам DataList и Repeater.
Параграф 3. Методика отображения данных с использованием SqlDataSource
3.1. Подключение к источнику данных
Рассмотрим элемент управления SqlDataSource, который, как не трудно предположить, станет наиболее часто используемым в программах. Причина - он предназначен для обмена данными с большинством баз данных (Sql в названии - скорее указание на то, для чего он первоначально разрабатывался). Этот элемент представлен также контролом (вкладка Data).
SqlDataSource может возвращать данные в табличном виде и допускает построчное чтение. Это возможно благодаря использованию DataSet или DataReader, в зависимости от установленных свойств.
Для предметного разговора создадим Web проект пустого сайта (см. раздел "Основы Создание Web сайтов на С# в Visual Studio 2005/2008"). Добавим к проекту контрол SqlDataSource (можно сразу добавить контролы Label и Button). Напомним, что добавлять теперь контролы в решение сайта можно и путем перетаскивания их в Source код aspx файла.
Сразу откроем окно Properties для SQL SqlDataSource и найдем свойство Provider Name (Рис.1). Наличие поддерживаемых провайдеров и обеспечивает многофункциональность элемента (это, так называемая, "фабрика управляемых провайдеров данных").
Рис.1 Контрол SqlDataSource в проекте решения
Выберем ProviderName System.Data.SqlClient.
В меню View выберем пункт Server Explorer. В появившемся слева окне "Server Explorer", в контекстном меню узла Data Connections, выберем пункт Add Connection. В появившемся окне Add Connection, в выпадающем списке Server Name, выбираем для данного провайдера требуемый и доступный по сети сервер. Здесь можно сразу задать и имя базы данных (Рис.2) и другие параметры.
После нажатия кнопки OK имя сервера отобразится в дереве доступных серверов Server Explorer (Рис.2).
Рис.2 Добавление соединения с базой данных
Вернемся к свойствам контрола SqlDataSource и кликнем на свойстве ConnectionString. В выпадающем списке свойства появился указанный нами сервер. Выберем его и, таким образом, создадим ConnectionString.
Data Source=CK1;Initial Catalog=pubs;Integrated Security=True
Обратимся к файлу Default.aspx. Код контрола полностью включил в себя все установки:
<asp:SqlDataSource ID="SqlDataSource1"
runat="server" ProviderName="System.Data.SqlClient"
ConnectionString="Data Source=CK1;
Initial Catalog=pubs;
Integrated Security=True">
</asp:SqlDataSource>
Проделанное выше можно выполнить немного по другому, воспользовавшись мастером настройки SqlDataSource, который вызывается при нажатии мышкой на треугольничке в правом верхнем углу контрола (Рис.3.). Последовательность действий показана на Рис.3. и Рис.4. На шаге 9 мастер предложит создать и сохранить SQL операторы Select, Insert, Delete и Update. Программисты обычно пишут операторы сами, поэтому девятым шагом выберем Cancel. Не забудем в свойствах SqlDataSource выбрать созданную ConnectionString и проверить свойство ProviderName.
Рис.3. Конфигурирование SqlDataSource
Рис.4. Конфигурирование SqlDataSource
Отметим, что в ASP.NET 2 и СonnectionString и ProviderName могут быть заданы в файле web.config в секции connectionStrings.
<connectionStrings>
<add name="conPubsCK1"
connectionString="Data Source=CK1;Initial Catalog=pubs;
Integrated Security=True" providerName="System.Data.SqlClient" />
</connectionStrings>
В этом случае можно будет подключить или заменить СonnectionString динамически в коде программы,
protected void Page_Load(object sender, EventArgs e)
{
SqlDataSource1.ConnectionString=
(string)ConfigurationManager.ConnectionStrings["conPubsCK1"].ConnectionString;
}
или использовать непосредственно запись из секции web.config:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:conPubsCK1 %>" >
</asp:SqlDataSource>
На данном этапе создано подключение и все готово к выбору и отображению данных.
3.2. Отображение данных в контролах
Рассматривая далее свойства SqlDataSource, отметим, что он может работать с командами Insert, Delete, Update и Select, то есть - этот элемент для многофункциональной работы с данными. Для каждого типа команд можно задать и соответствующий CommandType (Text, StoredProcedure) и исполняемый SQL оператор, для формирования которого подключается Query Bilder, который, вызывается из Command and Parameter Editor (вызывается для свойства только в режиме Design). В Command and Parameter Editor можно задать и параметры, которые будут использоваться в SQL команде.
Сформируем для свойства SelectQuery (используя Command and Parameter Editor или записав его в окне свойства) SQL оператор:
SELECT dbo.employee.* FROM dbo.employee
Далее обратимся к свойству DataSourceMode. Свойство DataSourceMode определяет тип возвращаемого набора данных и способ доступа к данным. Возможны только два (знакомые нам по Visual Studio Net 2003) значения: DataSet и DataReader (набор данных или последовательное чтение). Выберем DataSet.
Для отображения данных добавим на страницу контрол GriedView. При помещении его на форму будет предложено выбрать источник данных. Можно сразу выбрать формат таблицы отображения и ряд других параметров (Рис.5).
Рис.5 Добавление контрола GriedView
После выбора источника данных в окне (Рис.5.) отобразится окно Gried View Tasks, в котором целесообразно поставить галочку в чекбоксе Enable Paging (разбиение на страницы). Эти и другие свойства можно задать и в окне Properties (Рис.6.).
Рис.6. Изменение отдельных свойств контрола GriedView
Воспользовавшись свойством Columns можно оставить столько столбцов для отображения, сколько требуется, а свойство Page Size определит число отображаемых на странице строк. Сотни свойств GriedView каждый может отредактировать по своему желанию. Нажимаем F5 и получаем результат (Рис.7.).
Рис.7. Выполнение решения сайта
Абсолютно аналогично можно использовать DetalisView, FormView и DataList.
Как вывод из сказанного выше отметим, что мы отобразили данные таблицы не написав ни одной строчки кода.
3.3. Запросы с параметрами
Для выполнения примеров параграфа добавим на форму нашего проекта решения контрол TextBox.
Для добавления параметров в запрос в Visual Studio 2005 можно использовать Command and Parameter Editor и Quary Builder. Вновь в свойстве контрола Select Quary нажимаем кнопочку с тремя точками. В Command and Parameter Editor нажимаем кнопочку Quary Builder. Сформируем запрос как показано на Рис.8. В значении столбца Filter записываем имя параметра @Par1 (значок @ предопределяет имя параметра в SQL запросах для SQL сервера).
SELECT fname, lname, hire_date FROM dbo.employee WHERE(lname = @Par1)
Рис.8. Формирование SQL запроса
После формирования запроса в Command and Parameter Editor нажимаем кнопку Add Parameter и записываем его имя Par1 (Рис.9.). В выпадающем списке Parameter Source выбираем один из возможных источников для параметров: Cookie, переменная формы (Form), поле контрола (Control), адресная строка вызова страницы (Query String), профиль пользователя (Profile) или сессия (Session).
Пусть мы будем брать данные из контрола TextBox. Тогда значением Parameter Source необходимо задать Control, а Control ID - TextBox1. При задании параметра можно указать DefaultValue - значение по умолчанию, а выбрав ссылку Show advanced properties, дополнительные параметры. Обязательно необходимо указать параметр Type - тип данных (в нашем примере String) и проверить, что параметр входной (Direction - Input).
Рис.9. Формирование SQL запрса с параметрами
Обратим внимание, что все параметры отобразились в коде SqlDataSource:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:conPubsCK1 %>"
ProviderName="System.Data.SqlClient"
SelectCommand="SELECT fname, lname, hire_date
FROM dbo.employee WHERE (lname = @Par1)">
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1"
DefaultValue="Afonso"
Name="Par1"
PropertyName="Text"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
Выполняем решение - в браузере отобразится только одна строка таблицы (Рис.7.), причем, при вводе фамилии в Text и нажатии Enter, будет вызывается новая запись.
В Web достаточно часто для передачи параметров используется адресная строка. В этом случае для извлечения параметра можно добавить элемент QueryStringParameter:
<SelectParameters>
<asp:QueryStringParameter DefaultValue="Afonso"
Name="Par1" QueryStringField="lname"
Type="String" />
</SelectParameters>
У контрола SqlDataSource есть свойство CancelSelectOnNullParameter. Если его значение установить в true, то будет прерываться выполнение запроса в случае если какой-либо из входных параметров равен null. Свойство ConvertEmptyStringToNull позволяет выполнить преобразование пустых значений параметров в null. Для демонстрации действия этого механизма, добавим еще один контрол Button к проекту решения сайта и в обработчиках нажатия кнопок запишем код:
//Режим "Выбрать все"
protected void Button1_Click(object sender, EventArgs e)
{
//Создаем параметр null
TextBox1.Text = "";
SqlDataSource1.CancelSelectOnNullParameter = false;
SqlDataSource1.SelectParameters["Par1"].ConvertEmptyStringToNull = true;
SqlDataSource1.SelectParameters["Par1"].DefaultValue = null;
SqlDataSource1.SelectCommand =
"SELECT fname, lname, hire_date FROM dbo.employee WHERE @Par1 is null";
}
//Режим "Выбрать одного"
protected void Button2_Click(object sender, EventArgs e)
{
SqlDataSource1.CancelSelectOnNullParameter = true;
//Если раскоментировать, то выборки не будет при TextBox1.Text = "";
//SqlDataSource1.SelectParameters["Par1"].ConvertEmptyStringToNull = false;
SqlDataSource1.SelectParameters["Par1"].DefaultValue = "Afonso";
SqlDataSource1.SelectCommand =
"SELECT fname, lname, hire_date FROM dbo.employee WHERE (lname = @Par1)";
}
Рис.10. Выполнение решения в браузере
Такого же результата можно добиться, если установить у SqlDataSource свойство CancelSelectOnNullParameter в False, убрать значение параметра по умолчанию, установить параметр ConvertEmptyStringToNull в true и определить оператор Select в SelectCommand соответствующим образом:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:conPubsCK1 %>"
ProviderName="System.Data.SqlClient"
SelectCommand="SELECT fname, lname, hire_date FROM dbo.employee
WHERE (@Par1 is null or lname = @Par1)"
CancelSelectOnNullParameter="false">
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1"
Name="Par1" PropertyName="Text"
Type="String" ConvertEmptyStringToNull="True" />
</SelectParameters>
</asp:SqlDataSource>
В этом случае при пустом поле TextBox будет отображаться весь список, выбранный по команде Select, а при непустом поле - только одна запись.
3.4. Дифференцированный доступ к данным
Как правило, от программы требуется не просто отобразить данные, а получать конкретные значение данных из нужных столбцов, чтобы в дальнейшем их использовать в каких либо расчетах. Для этого данные столбца должны быть сопоставлены какой-либо переменной. Как и в Visual Studio 2003, для этого используются возможности элементов управления DataSet и DataReader.
SqlDataSource имеет метод Select. Этот метод (как отмечено выше), в зависимости от значения свойства DataSourceMode (DataSet или DataReader), возвращает DataView или IDataReader (соответственно). DataView это элемент близкий к DataTable, но более функциональный (позволяет устанавливать параметры сортировки и фильтрации и т.п.). При выполнении метода Select он, как и DataTable при выполнении команды Fill, заполняется таблицей выбранных данных. IDataReader практический аналог старого доброго DataReader, а их использование полностью идентично.
Для демонстрации доступа к данным, уберем из проекта, используемого нами в предыдущем параграфе, контрол GridView и один из контролов Button. И запишем следующий код обработчика нажатия кнопки:
protected void Button1_Click(object sender, EventArgs e)
{
string sS = string.Empty;
//Возвращаться будет DataView
SqlDataSource1.DataSourceMode = SqlDataSourceMode.DataSet;
SqlDataSource1.SelectCommand = "SELECT dbo.authors.*
FROM dbo.authors order by au_id";
//Производим выборку со сменой режима сортировки
DataView dv =
(DataView)SqlDataSource1.Select(new
DataSourceSelectArguments("au_fname"));
//Обратимся к первой строке и второму столбцу
sS = dv[0][1].ToString();
//Отобразим данные
Label1.Text = "В DataView в первой строке - " + sS;
//Возвращаться будет IDataReader
SqlDataSource1.DataSourceMode = SqlDataSourceMode.DataReader;
//Комманду можно сменить
//SqlDataSource1.SelectCommand = "SELECT dbo.authors.* FROM dbo.authors";
IDataReader dr = (IDataReader)SqlDataSource1.Select(DataSourceSelectArguments.Empty);
Label1.Text += " DataReader прочитал с исходной сортировкой: ";
while (dr.Read())
{
//Получаем данные текущей строки 1го столбца
sS = dr[1].ToString();
Label1.Text += sS+" ";
}
}
Рис.11. Выполнение решения сайта в браузере
3.5. Использование типов SQL Server
Для использования типов данных SQL Server в .NET создано пространство имен System.Data.SqlTypes. При его использовании доступны все типы данных, такие как SqlBinary, SqlBoolean (SqlBoolean.True и SqlBoolean.False), SqlString, SqlByte, SqlInt16, SqlInt32....
using System.Data.SqlClient; using System.Data.SqlTypes;
Параграф 4. Возможности редактирования данных в SqlDataSource
Для редактирования данных в SqlDataSource предусмотрены свойства UpdateCommandType, UpdateQuary, DeleteCommandType, DeleteQuary, InsertCommandType и InsertQuary. Настройка свойств аналогична настройке, выполненной нами для SelectCommand, за исключением того, что при обновлении данных мы должны знать, какую именно строку редактировали. Для этого в Visual Studio 2005 используется специальный механизм задания полей в условии Where - оригинальные параметры (не в смысле оригинальности, а в смысле получения значений строки редактирования при подстановки параметров). В свойстве OriginalValuesParameterFormatString контрола SqlDataSource указывается формат по умолчанию для параметров подстановки (original_{0}). Это означает, что параметры с префиксом original_ и именем столбца таблицы БД заменяются значениями соответствующего поля из строки редактирования. Свойства ConflictDetection контрола SqlDataSource определяет, какие параметры передаются в SQL операторе. Значение CompareAllValues соответствует передаче всех обновляемых полей, OverwriteChanges - только ключевых.
Отметим, что использование команд Insert и Delete в последующих примерах аналогично и проще использования Update и, поэтому, в примерах рассматриваются в основном метод Update.
4.1. Редактирование данных в табличных представлениях
Уберем из нашего проекта решения коды обработчиков событий нажатия кнопок (можно убрать и контролы кнопок и контрол Label). Вновь вернем в наш проект GridView, как мы это делали выше (Рис.5. и 6.). Установим свойство контрола SqlDataSource в значение OverwriteChanges. Сформируем свойство SelectQuery:
SELECT fname, lname, hire_date, emp_id FROM dbo.employee
Сформируем свойство UpdateQuery:
UPDATE dbo.employee SET fname = @Par1 WHERE (emp_id = @original_emp_id)
Здесь мы, как и ранее, используем @Par1 как заполняемый из контрола TextBox1, а для свойства original_emp_id определим только имя и тип (Рис.12):
Рис.12. Создание Update
Для GriedView установим свойство DataKeyNames в знаяение emp_id, определив, таким образом, ключевое поле. Выполним решение (Рис.13.).
Рис.13. Выполнение решения в браузере
В режиме Edit для строк (после нажатия ссылки Edit) значение, введенное в поле TextBox, после нажатия ссылки Update, будет запомнено в базе данных, в чем не сложно убедиться.
Все о чем мы говорили, отображено в файле aspx.
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:conPubsCK1 %>"
ProviderName="System.Data.SqlClient"
CancelSelectOnNullParameter="False"
UpdateCommand="UPDATE dbo.employee SET fname = @Par1
WHERE (emp_id = @original_emp_id)"
SelectCommand="SELECT fname, lname, hire_date,
emp_id FROM dbo.employee"
OldValuesParameterFormatString="original_{0}" >
<UpdateParameters>
<asp:Parameter Name="original_emp_id" Type="String" />
<asp:ControlParameter ControlID="TextBox1" DefaultValue=""
Name="Par1" PropertyName="Text"
Type="String" />
</UpdateParameters>
</asp:SqlDataSource>
В приведенном выше примере можно обойтись и без поля ввода, а выполнять редактирование непосредственно в таблице отображения. Для этого изменим UpdateQuary:
UPDATE dbo.employee SET fname = @fname WHERE (emp_id = @original_emp_id)
И, кроме того, определим параметр fname аналогично original_emp_id с типом параметра String.
<UpdateParameters> <asp:Parameter Name="original_emp_id" Type="String" >> <asp:Parameter Name="fname" Type="String" /> </UpdateParameters>
Свойство ConflictDetection контрола SqlDataSource поставим в значение CompareAllValues. Выполним решение. Теперь можно выполнять редактирование поля fname непосредственно в таблице.
4.2. Редактирование данных с использованием команд прямого обращения к данным
Данную возможность покажем непосредственно на примере выполнения поиска строки с lname, значение которой задано в TextBox1 и замены для нее значения fname значением поля TextBox2. В решении используем отображение данных из предыдущих примеров. Данный пример, кроме того, показывает возможности динамической работы с SqlDataSource.
protected void Button1_Click(object sender, EventArgs e)
{
//Создадим SqlDataSource
SqlDataSource sqlds = new SqlDataSource("System.Data.SqlClient",
"Data Source=CK1;Initial Catalog=pubs;Integrated Security=True",
"SELECT fname, lname FROM dbo.employee");
//Определим ID SqlDataSource
sqlds.ID = "SqlDataSource2";
//Зададим команду Update с параметрами и определим параметры
sqlds.UpdateCommand =
"UPDATE dbo.employee SET fname = @Par2 WHERE (lname = @Par1)";
sqlds.UpdateParameters.Clear();
Parameter p1 = new Parameter("Par1",TypeCode.String,TextBox1.Text.Trim());
Parameter p2 = new Parameter("Par2",TypeCode.String,TextBox2.Text.Trim());
//Или так
//SqlDataSource sqlds = new SqlDataSource();
//sqlds.ConnectionString =
//"Data Source=CK1;Initial Catalog=pubs;Integrated Security=True";
//sqlds.SelectCommand = "SELECT fname, lname FROM dbo.employee";
sqlds.UpdateParameters.Add(p1);
sqlds.UpdateParameters.Add(p2);
//Тип возвращаемого значения метода Select
sqlds.DataSourceMode = SqlDataSourceMode.DataReader;
//Далее используем IDataReader
IDataReader dr =
(IDataReader)sqlds.Select(DataSourceSelectArguments.Empty);
string sS = string.Empty;
while (dr.Read())
{
//Получаем данные текущей строки 1го столбца
sS = dr["lname"].ToString();
if(sS == TextBox1.Text.Trim())
{
sqlds.Update();
break;
}
}
//Чтобы увидеть - надо выбрать, а чтобы выбрать - сменить комманду
SqlDataSource1.SelectCommand =
"SELECT fname, lname, hire_date, emp_id FROM dbo.employee";
SqlDataSource1.Select(DataSourceSelectArguments.Empty);
}
Результат работы кода показан на Рис.14.
Рис.14. Выполнение решения в браузере
Еще проще эту же задачу выполняет следующий код, который не использует операцию чтения данных для поиска строки, в которую должны вноситься изменения:
protected void Button1_Click(object sender, EventArgs e)
{
SqlDataSource sqlds = new SqlDataSource();
sqlds.ProviderName="System.Data.SqlClient";
sqlds.ConnectionString = "Data Source=CK1;
Initial Catalog=pubs;Integrated Security=True";
sqlds.ID = "SqlDataSource2";
sqlds.UpdateCommand =
"UPDATE dbo.employee SET fname = @Par2 WHERE (lname = @Par1)";
Parameter p1 = new Parameter("Par1", TypeCode.String,
TextBox1.Text.Trim());
Parameter p2 = new Parameter("Par2", TypeCode.String,
TextBox2.Text.Trim());
sqlds.UpdateParameters.Add(p1);
sqlds.UpdateParameters.Add(p2);
sqlds.DataSourceMode = SqlDataSourceMode.DataReader;
sqlds.Update();
//Чтобы увидеть надо выбрать, а чтобы выбрать сменить комманду
SqlDataSource1.SelectCommand =
"SELECT fname, lname, hire_date, emp_id FROM dbo.employee";
SqlDataSource1.Select(DataSourceSelectArguments.Empty);
}
Тот же пример с использованием DataSet и DataView:
protected void Button1_Click(object sender, EventArgs e)
{
//Создадим SqlDataSource
SqlDataSource sqlds = new SqlDataSource("System.Data.SqlClient",
"Data Source=CK1;Initial Catalog=pubs;Integrated Security=True",
"SELECT fname, lname FROM dbo.employee");
//Определим ID SqlDataSource
sqlds.ID = "SqlDataSource2";
//Зададим команду Update с параметрами и определим параметры
sqlds.UpdateCommand =
"UPDATE dbo.employee SET fname = @Par2 WHERE (lname = @Par1)";
sqlds.UpdateParameters.Clear();
Parameter p1 = new Parameter("Par1", TypeCode.String, TextBox1.Text.Trim());
Parameter p2 = new Parameter("Par2", TypeCode.String, TextBox2.Text.Trim());
sqlds.UpdateParameters.Add(p1);
sqlds.UpdateParameters.Add(p2);
//Тип возвращаемого значения метода Select
sqlds.DataSourceMode = SqlDataSourceMode.DataSet;
DataView dv = (DataView)SqlDataSource1.Select(new
DataSourceSelectArguments());
string sS = string.Empty;
for(int i = 0; i < dv.Count; i++)
{
sS = dv[i]["lname"].ToString();
if(sS == TextBox1.Text.Trim())
{
sqlds.Update();
break;
}
}
//Чтобы увидеть надо выбрать, а чтобы выбрать сменить комманду
SqlDataSource1.SelectCommand =
"SELECT fname, lname, hire_date, emp_id FROM dbo.employee";
SqlDataSource1.Select(DataSourceSelectArguments.Empty);
}
4.3. Аналог редактирования данных в табличном представлении с использованием динамически создаваемых контролов
Отметим, что нельзя сохранить или передать строки представления DataView, поскольку DataView не имеет собственной копии строк, он просто организует доступ к строкам DataTable из DataSet. Поэтому, в данном примере и выполняется Update строки непосредственно в БД. Следующие два примера позволяют создать динамический код, аналогичный работе с использованием визуальных контролов. В первом примере мы от DataView перейдем к использованию GridView, в котором можно непосредственно выполнять Update строк.
protected void Button1_Click(object sender, EventArgs e)
{
//Создадим SqlDataSource
SqlDataSource sqlds = new SqlDataSource("System.Data.SqlClient",
Data Source=CK1;Initial Catalog=pubs;Integrated Security=True",
"SELECT fname, lname FROM dbo.employee");
//Определим ID SqlDataSource
sqlds.ID = "SqlDataSource2";
//Зададим команду Update с параметрами и определим параметры
sqlds.UpdateCommand =
"UPDATE dbo.employee SET fname = @Par2 WHERE (lname = @Par1)";
sqlds.UpdateParameters.Clear();
Parameter p1 = new Parameter("Par1",
TypeCode.String, TextBox1.Text.Trim());
Parameter p2 = new Parameter("Par2",
TypeCode.String, TextBox2.Text.Trim());
sqlds.UpdateParameters.Add(p1);
sqlds.UpdateParameters.Add(p2);
sqlds.DataSourceMode = SqlDataSourceMode.DataSet;
GridView gv = new GridView();
gv.ID = "GridView2";
gv.AllowPaging = false;
gv.DataSourceID = sqlds.ID;
gv.AutoGenerateColumns = true;
//Выбираем данные и заполняем GridView
sqlds.Select(new DataSourceSelectArguments());
//Обязательно добавляем контролы к форме
form1.Controls.Add(sqlds);
form1.Controls.Add(gv);
string sS = string.Empty;
for (int i = 0; i < gv.Rows.Count; i++)
{
GridViewRow gvr = gv.Rows[i];
sS=Convert.ToString(gvr.Cells[1].Text);
if(sS == TextBox1.Text.Trim())
{
//Обновляем данные
sqlds.Update();
break;
}
}
//Удаляем уже ненужные контролы
form1.Controls.Remove(sqlds);
form1.Controls.Remove(gv);
//Чтобы увидеть надо выбрать, а чтобы выбрать сменить комманду
SqlDataSource1.SelectCommand =
"SELECT fname, lname, hire_date, emp_id FROM dbo.employee";
SqlDataSource1.Select(DataSourceSelectArguments.Empty);
}
Если в рассмотренном примере мы только заменили DataView на GridView, то следующий пример выполняет Update строки и затем, полностью SqlDataSource (это полный аналог рассмотренного выше примера использования связки визуальных контролов SqlDataSource и GridViev).
protected void Button1_Click(object sender, EventArgs e)
{
//Создадим SqlDataSource
SqlDataSource sqlds = new SqlDataSource("System.Data.SqlClient",
"Data Source=CK1;Initial Catalog=pubs;Integrated Security=True",
"SELECT emp_id,fname, lname FROM dbo.employee");
//Определим ID SqlDataSource
sqlds.ID = "SqlDataSource2";
//Зададим команду Update с параметрами и определим параметры
sqlds.UpdateCommand =
"UPDATE dbo.employee SET fname = @Par2 WHERE (emp_id = @original_emp_id)";
sqlds.UpdateParameters.Clear();
sqlds.OldValuesParameterFormatString = "original_{0}";
sqlds.ConflictDetection = ConflictOptions.OverwriteChanges;
Parameter p1 = new Parameter("original_emp_id",
TypeCode.String);
Parameter p2 = new Parameter("Par2",
TypeCode.String, TextBox2.Text.Trim());
sqlds.UpdateParameters.Add(p1);
sqlds.UpdateParameters.Add(p2);
sqlds.DataSourceMode = SqlDataSourceMode.DataSet;
GridView gv = new GridView();
gv.ID = "GridView2";
gv.AllowPaging = false;
gv.DataSourceID = sqlds.ID;
gv.AutoGenerateColumns = true;
gv.DataKeyNames = new string[] { "emp_id" };
//Выбираем данные и заполняем GridView
sqlds.Select(new DataSourceSelectArguments());
//Обязательно добавляем контролы к форме
form1.Controls.Add(sqlds);
form1.Controls.Add(gv);
string sS = string.Empty;
for (int i = 0; i < gv.Rows.Count; i++)
{
GridViewRow gvr = gv.Rows[i];
sS = Convert.ToString(gvr.Cells[2].Text);
if(sS == TextBox1.Text.Trim())
{
gv.UpdateRow(i,false);
gvr.Cells[1].Text = TextBox2.Text.Trim();
sqlds.Update();
//Обновляем данные
break;
}
}
//Удаляем уже ненужные контролы
form1.Controls.Remove(sqlds);
form1.Controls.Remove(gv);
//Чтобы увидеть надо выбрать, а чтобы выбрать сменить комманду
SqlDataSource1.SelectCommand =
"SELECT fname, lname, hire_date, emp_id FROM dbo.employee";
SqlDataSource1.Select(DataSourceSelectArguments.Empty);
}
Параграф 5. События и методы SqlDataSource и их использование при выборке и редактировании данных
В приведенных выше примерах мы уже использовали прямой вызов методов Select и Update. Аналогично вызываются и методы Insert и Delete. Кроме того, SqlDataSource имеет ряд методов, связанных с событиями.
Рассмотрим события SqlDataSource (вкладка Events окна Properties контрола)- Рис.15.
Рис.15. События SqlDataSource
Обратим внимание, что каждой операции редактирования данных (Select, Update, Delete, Insert) соответствует пара событий с префиксами -ing и -ed, которые происходят соответственно перед вызовом команды и после вызова. Создадим два обработчика событий, например, Inserting и Inserted. Каждое из них имеет переменную типа qlDataSourceCommandEventArgs, содержимое которой до выполнения команды это свойства Cancel (позволяющее отменить выполнение команды) и Command (позволяющее изменить или проконтролировать команду) и AffectedRows (число измененных или выбранных записей), Command (выполненная команда) и Exception (возникшее исключение) - после выполнения команды. Некоторые возможности показаны в следующем коде (для примера предыдущего параграфа):
protected void SqlDataSource1_Selecting(object sender, S
qlDataSourceCommandEventArgs e)
{
//Полностью прекратит выборку
e.Cancel = true;
//Изменить заданную в команде таблицу
e.Command.CommandText =
"SELECT dbo.authors.* FROM dbo.authors order by au_id";
//Можно изменить параметр или добавить
e.Command.CommandText =
"SELECT dbo.authors.* FROM dbo.authors where au_fname=@Par1";
System.Data.Common.DbParameter p = e.Command.CreateParameter();
p.ParameterName = "Par1";
p.Value = "Dean";
e.Command.Parameters.Add(p);
//Можно изменить Timeout выполнения команды
e.Command.CommandTimeout = 50;
//И Т.Д.
................................
}
protected void SqlDataSource1_Selectted(object sender,
SqlDataSourceStatusEventArgs e)
{
Label1.Text = e.Command.CommandText;
//Выведет если не изменили
//SELECT fname, lname, hire_date, emp_id FROM dbo.employe
//Или
//SELECT dbo.authors.* FROM dbo.authors
// where au_fname=@Par1 order by au_id
Label1.Text = e.Command.CommandTimeout.ToString();
//Выведет
//30 - Timeout по умолчанию или 50 если установим
if(e.Exception != null)
{
//Можно посмотреть исключение
Label1.Text += e.Exception.Message;
//Можно заблокировать исключение (вывести сообщение и идти дальше)
//Например, внеся ошибку в комманду Select в SqlDataSource1_Selecting
//SELECT dbo.authors.* FROM dbo.authors where r au_fname=@Par1
//Мы получим (Рис.16. слева)
//а при нижепреведенной строке (Рис.16. справа)
e.ExceptionHandled = true;
}
//И Т.Д.
................................
}
Рис.16. Использование блокировки исключений при работе с данными
Параграф 6. Создание отчетов master-detail
Используя элементы управления SqlDataSource и стандартные элементы отображения можно легко создать отчеты master-detail. Создадим такой отчет с использованием двух контролов SqlDataSource и контролов GridView и DetailsView. Для примера будем использовать туже таблицу сотрудников, а при выборе строки с именем сотрудника в GriedView поставим задачей отображение его специальности в таблице detail, как показано на Рис.17.
Рис.17. Отчет master/detail
В начале создадим таблицу master, как мы это делали в параграфе 3.2. "Отображение данных в контролах". Select Quary для SqlDataSource1 запишем как:
SELECT fname, lname, emp_id, job_id FROM dbo.employee
Чтобы не отображались лишние поля для emp_id и job_id после добавления всех полей в GridView1 установим значение их свойства View в false.
Важным для установления связки master/detail является значение свойства DataKeyNames, в поле которого должно присутствовать имя job_id.
Для qlDataSource2 сформируем Select Quary как показано на Рис.18.
Рис.18. Отчет master/detail. Select Quary для detail.
После установки свойства DataSourceID DetailViews1 в значение SqlDataSource1 и задания полей отображения в свойстве Fields DetailViews1 как показано на Рис.19. проект будет построен.
Рис.19. Установка значений полей Fields
Полный код примера создания и отображения связки master/detail показан ниже:
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="Data Source=
CK1;Initial Catalog=pubs;Integrated Security=True"
ProviderName="System.Data.SqlClient" SelectCommand=
"SELECT fname, lname, emp_id, job_id FROM dbo.employee">
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString=
"Data Source=CK1;Initial Catalog=pubs;Integrated Security=True"
ProviderName="System.Data.SqlClient" SelectCommand=
"SELECT job_id, job_desc FROM dbo.jobs where job_id=@job_id">
<SelectParameters>
<asp:ControlParameter ControlID="GridView1" Name="job_id"
PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
<table><tr><td>
<asp:GridView ID="GridView1" runat="server"
AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" BackColor="#DEBA84"
BorderColor="#DEBA84" BorderStyle="None"
BorderWidth="1px" CellPadding="3" CellSpacing="2"
DataKeyNames="job_id" DataSourceID="SqlDataSource1">
<FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
<Columns>
<asp:CommandField ShowSelectButton="True" />
<asp:BoundField DataField="emp_id" HeaderText="emp_id"
ReadOnly="True" SortExpression="emp_id"
Visible="False" />
<asp:BoundField DataField="fname" HeaderText="fname"
SortExpression="fname" />
<asp:BoundField DataField="lname" HeaderText="lname"
SortExpression="lname" />
<asp:BoundField DataField="job_id" HeaderText="job_id"
SortExpression="job_id" Visible="False" />
</Columns>
<RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True"
ForeColor="White" />
<PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
<HeaderStyle BackColor="#A55129" Font-Bold="True"
ForeColor="White" />
</asp:GridView>
</td>
<td>
<asp:DetailsView ID="DetailsView1" runat="server"
AutoGenerateRows="False" BackColor="LightGoldenrodYellow"
BorderColor="Tan" BorderWidth="1px" CellPadding="2" DataSourceID=
"SqlDataSource2"
ForeColor="Black" GridLines="None" Height="50px" Width="125px">
<FooterStyle BackColor="Tan" />
<EditRowStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" />
<PagerStyle BackColor="PaleGoldenrod" ForeColor="DarkSlateBlue"
HorizontalAlign="Center" />
<Fields>
<asp:BoundField DataField="job_id" HeaderText="job_id"
InsertVisible="False" ReadOnly="True"
SortExpression="job_id" Visible="False" />
<asp:BoundField DataField="job_desc"
SortExpression="job_desc" />
</Fields>
<HeaderStyle BackColor="Tan" Font-Bold="True" />
<AlternatingRowStyle BackColor="PaleGoldenrod" />
</asp:DetailsView>
</td></tr></table>
Параграф 7. Кэширование данных в SqlDataSource
Если свойство DataSourceMode (определяет тип возвращаемого набора данных и способ его получения) имеет значение DataSet, то данные могут быть кэшированы. Включение кэширования выполняется при значении свойства SqlDataSource EnableCaching равным true (по умолчанию отключено).
Кроме того, ряд свойств определяет политику кэширования:
CacheDuration - при политике абсолютного срока хранения - продолжительность кэширования в секундах. Ноль(по умолчанию) отменяет кэширование.
CacheExpirationPolicy - определение политики кэширования. Может иметь значение Absolute (кэширование на значение CacheDuration) и Sliding (переустановка срока на CacheDuration при каждом чтении). При работе с файлами БД Access или XML изменение временной метки файла приводит к принудительному завершению кэширования.
CacheKeyDependency - ключезависимое кэширование. Набор данных кэшируется на неограниченный промежуток времени. Перегрузка кэшированного набора данных происходит только после принудительного сброса кэш.
Рассмотрим пример, в котором в связке таблиц master/detail, нам надо сначала провести все обновления в таблице мастер и, лишь после этого, отобразить результат.
Создадим проект сайта, страничка которого имеет два контрола SqlDataSource, контролы GridView и DetailsView, а также контролы Button и TextBox (Рис.20.).
Рис.20. Проект решения для демонстрации использования CacheKeyDependency
Настроим SqlDataSource1 как и в предыдущем примере. Создадим SQL Query для SqlDataSource1:
SELECT fname, lname, emp_id, job_id FROM dbo.employee
Свойство CacheDuration должно быть Infinite. Update Quary создадим, как показано на Рис.21. Обратим внимание, что Sql предложение предназначено для внесения изменений в таблицу detail, а Select выполняет выборку для таблицы master.
Рис.21. Формирование UpdateQuery для SqlDataSource1
Свойство DataSourceID GriedView1 установим в SqlDataSource1. Свойство DataKeyNames зададим как job_id.
Чтобы не отображались лишние поля для неинформативных полей, после добавления всех полей в GridView1, установим значение свойства View в false.
Для SqlDataSource2 предложение Select создадим как
SELECT job_id, job_desc FROM dbo.jobs where job_id=@job_id
Параметр job_id зададим, как показано на Рис.18.
Свойство CacheKeyDependency запишем как MyKey (Рис.22.).
Рис.22. Настройка SqlDataSource2
DetailsView1 через DataSourceID свяжем с SqlDataSource2.
Создадим обработчики событий для нажатия кнопок и запишем следующий код:
protected void Page_Load(object sender, EventArgs e) { if(!IsPostBack) Cache[this.SqlDataSource2.CacheKeyDependency] = "MyData"; } protected void Button1_Click(object sender, EventArgs e) { SqlDataSource1.Update(); } protected void Button2_Click(object sender, EventArgs e) { Cache.Remove(this.SqlDataSource2.CacheKeyDependency); DetailsView1.DataBind(); }Теперь мы можем выполнить приложение (Рис.21). Пусть мы всех Designer решили повысить в статусе и стали называть Super Designer. Набрав старое и новое имя, нажмем кнопку Button1. Изменения будут внесены в БД, но в таблице detail не отобразятся до тех пор, пока мы не нажмем кнопку Button2 - не выполним Remove Cashe.
Рис.21 Демонстрация использования CacheKeyDependency
SqlCacheDependency - свойство позволяет удалять кэш при любом изменении в БД сервера. В SQL Server 7.0. Asp.Net опрашивает базу данных на предмет наличия изменений в таблице БД и при их обнаружении сбрасывает КЭШ. В SQL Server 2005 сам сервер рассылает уведомления ASP.NET при изменениях на уровне строк. Это, так называемый механизм SQL Cache Invalidation.
Слежение за изменениями сводится к организации подписки на событие. Для работы механизма требуется настройка SQL Server. Настройка выполняется утилитой aspnet_regsql.
Найдем на компьютере утилиту aspnet_regsql (примерный путь C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\). Из данной директории дважды запустим данную утилиту. Первый раз для подключения механизма SQL Cache Invalidation для БД.
aspnet_regsql.exe -S ИМЯ_СЕРВЕРА -E -d ИМЯ_БАЗЫ -ed
Второй раз для подключения механизма SQL Cache Invalidation для конкретной таблицы БД.
aspnet_regsql.exe -S ИМЯ_СЕРВЕРА -E -d ИМЯ_БАЗЫ -t ИМЯ_ТАБЛИЦЫ -et
Эти команды добавят в БД таблицу AspNet_SqlCacheTablesForChangeNotification, триггер Customers_AspNet_SqlCacheNotification_Trigger и набор хранимых процедур. После этого механизм SqlCacheDependency может быть использован в приложении. Однако и приложение должно быть настроено.
В Web.config разрешаем приложению использовать sqlCacheDependency, определяем имя базы, имя connectionStringName (то, которое было сформировано или задано при помещении SqlDataSource на форму) и время реакции на обновление (в миллисекундах).
<system.web> <caching> <sqlCacheDependency enabled="true"> <databases> <add name="ИМЯ_БД" connectionStringName="ConnectionString" pollTime="5000"/> </databases> </sqlCacheDependency> </caching> ......Теперь можно испытать любое приложения, отображающее данные из таблицы, указанной нами при запуске утилиты aspnet_regsql. Установив свойства SqlDataSource:
EnableCaching="True"
SelectCommand например "SELECT [au_id], [au_lname], [au_fname] FROM [authors]"
SqlCacheDependency="ИМЯ_БД:authors" - имена регистрозависимы.
CacheKeyDependency="25000"
Запустив приложение и принудительно изменив (например, командой update из любого SQL менеджера) любую строку таблицы authors, мы можем наблюдать, как через 5 секунд после Refresh страницы в приложении отображается измененная информация. Если очистить свойство SqlCacheDependency, то обновление в БД можно отобразить в приложении только через 25 секунд.
Параграф 8. Возможности фильтрации в SqlDataSource
Кэширование позволяет выполнять фильтрованную выборку из кэшированного набора данных. Для этого в SqlDataSource могут быть использованы свойства FilterExpression (выражения для фильтрации), и FilterParameters (значение параметров фильтра). Задание самих параметров (FilterParameters) полностью аналогично заданию других параметров для SqlDataSource. Здесь мы не будем повторяться, так как задание параметров мы подробно рассматривали в параграфе 4.1. "Редактирование данных в табличных представлениях". FilterExpression задается аналогично формированию параметров подстановки. Отличие в том, что здесь необходимо соблюдать установленный синтаксис, например FilterExpression="au_id={0}">.
Приведем пример, позволяющий отображать одну запись из кэшированного набора данных, по номеру id, вводимому в TextBox.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" EnableCaching="True" SelectCommand="SELECT [au_id],[au_lname],[au_fname] FROM [authors]" FilterExpression="au_id>={0}"> <FilterParameters> <asp:ControlParameter ControlID="TextBox1" DefaultValue="2" Name="au_id" PropertyName="Text" Type="String" /> </FilterParameters>Если параметр строковый, то номер параметра в коллекции параметров берется в кавычки.
FilterExpression="au_fname='{0}'"> <FilterParameters> <asp:ControlParameter ControlID="TextBox1" DefaultValue="Wlad" Name="Par1" PropertyName="Text" Type="String" /> </FilterParameters>Литература
Дмитрий Руденко. Новое в ASP.NET 2. Контролы DataSource. Часть 1 - SqlDataSource
Sergey Shapovalov "Новые возможности кэширования в ASP.NET 2.0"
Молчанов Владислав 20.04.2006г.
Еcли Вы пришли с поискового сервера - посетите мою главную страничку
На главной странице Вы найдете программы комплекса Veles - для тех кто готовится к экзаменам на право управления автомобилем или мотоциклом, программу NumberPhoto, созданную для работы с фото, сделанными цифровым фотоаппаратом, программу Bricks - игрушку для детей и взрослых, программу записную книжку, теоретический материал по программированию в среде Borland C++ builder, C# (C .Net).