Для того, чтобы создавать интересные web-страницы, необходимо наполнить их динамичным, обновляемым содержанием. Особенно необходимо это в бизнес-приложениях – банковских, интернет-магазинах и аукционах. Важная часть работы, которую выполняет разработчик ASP.NET – это связывание своих страниц с источниками данных, отображение данных на странице, создание удобных средств взаимодействия в с ними.
Для хранения данных чаще всего используются СУБД(системы управления базами данных). Как уже говорилось, в ASP.NET 2.0 работа с данными происходит через ADO.NET 2.0– часть .NET, разработаная специально для доступа к базам данных или XML-файлам.
СУБД прошли долгий путь развития. В начале все данные хранили в простых(плоских) файлах. По мере увеличения объемов данных встал вопрос о том, как получить быстрый доступ к нужной информации. Для этого данные стали индексироваться. Другой вопрос – как избежать дублирования – когда одни и те же данные хранятся в разных местах. Чтобы ее решить, была разработана теория нормализации баз данных. Сегодня мощная промышленная СУБД немыслима без систем защиты информации, журналирования, транзакций и хранимых процедур.
Данные в СУБД хранятся в таблицах. Таблица состоит из полей и записей. Запись – единица хранения данных, строка таблицы. Например, в одной записи хранятся сведения об одном человеке. Поля – это столбцы таблицы для хранения конкретного вида информации. Базы данных называются реляционными, потому что таблицы в них связаны определенным образом.
Представления(View) создаются на основе одной или нескольких таблиц с помощью фильтрации, объединения, сортировки и группирования.
Для наглядности рассмотрим эти понятия на примере. В свое время я работала с базой данных кадров одного предприятия. Как создать такую базу? Прежде всего нам нужна таблица для хранения данных о сотрудниках.
ID Имя Фамилия Отчество Дата рождения Дата приема Должность 1 Петр Васечкин Иванович 1965 2001 Завхоз 2 Василий Петров Сидорович 1977 2003 Программист
и так далее. Таблица может иметь тысячи записей.
Отделу кадров нужна информация о перемещениях сотрудников. Для этого заведена отдельная таблица. Например, Петров получил должность старшего программиста. Прежде всего надо заметить, что названий должностей ограниченное количество. Чтобы избежать дублирования, лучше их хранить в отдельной таблице.
Должности
ID Название должности Минимальный оклад 1 Директор 1 2 Завхоз 10 3 Программист 20 4 Уборщица 100
Числа в левой колонке – это ключи. Они нужны, чтобы связать таблицы друг с другом. В таблице «Сотрудники» теперь будет храниться не название должности, а его ключ в таблице должностей. Для таблицы «Сотрудники» он называется внешним ключом, а для таблицы «Должности» - первичным ключом. Ключ также необходимо ввести в таблице сотрудников. Перемещение сотрудника на другую должность будет храниться в таблице перемещений так.
Перемещения
ID Сотрудник Должность Назначение Дата 1235 123 10 11 20.06.06
Таблица «Должности» связана как с таблицей сотрудников, так и с таблицей перемещений по своему уникальному ключу. База данных может генерировать первичные ключи сама, автоматически добавляя значения к предыдущему значению ключа. Это называется автоинкрементированием. Для полной уверенности в уникальности данных в таблицах могут держать точное время создания записи(Timestamp) и GUI(глобальный уникальный идентификатор).
Этот процесс называется нормализацией. Чтобы для отчета восстановить информацию о перемещении, в запросе нужно связать данные из разных таблиц.
В результате этого запроса будет создано представление, которое покажет данные из взаимосвязанных таблиц в удобной для чтения форме:
SELECT Employees.LastName, Employees.FirstName, Titles.Title, Titles_1.Title, Promotions.PromotionDate FROM Titles AS Titles_1 INNER JOIN ((Promotions INNER JOIN Titles ON Promotions.TitleBefore = Titles.id) INNER JOIN Employees ON Promotions.EmployeeID = Employees.EmployeeID) ON Titles_1.id = Promotions.TitleAfter;
Множество таблиц данных, связанных отношениями, составляют базу данных. На сервере СУБД может храниться множество баз данных.
Подробнее о теории баз данных можно прочитать в других курсах. Перейдем к конкретным примерам связывания с базами данных на веб-страницах.
У каждого пользователя Windows наверняка имеется программа Access. Это однопользовательская СУБД, в которой модель безопасности не так сильна. В одном файле Access хранятся как данные, так и интерфейс в виде форм и отчетов. Можно создавать модули на VBA (Visual Basic for Application). Профессиональные разработчики пользуются более мощными программами. По «серьезности» СУБД от Microsoft идут в порядке – Access – FoxPro – MS SQL. MS SQL не позволяет создавать формы, а занимается хранением и защитой данных на профессиональном уровне. Visual Studio 2005 (и VWD) при инсталляции устанавливает MS SQL Express. Он будет запускаться автоматически в виде сервиса Windows.
Для работы с базами данных используется язык структурированных запросов – SQL (Structured Query Language). Команды этого языка называются запросами. Запросы служат для получения данных, для создания и изменения структуры таблиц, добавления, удаления и обновления записей и многого другого. Последовательность команд может храниться прямо на сервере СУБД в виде хранимой процедуры. Нужно стараться всегда пользоваться хранимыми процедурами, а не писать команды самим. Главное их преимущество – скорость работы и инкапсуляция бизнес-логики. Хранятся они на сервере в уже откомпилированном виде, в то время как простой переданный набор команд SQL проходит через стадию компиляции.
Для обращения к базам данных из внешних программ существуют специальные механизмы. В Windows это ODBC - открытый интерфейс взаимодействия с базами данных. Он позволяет приложениям, работающим под Windows или другими ОС, общаться с различными серверами реляционных баз данных.
Для конфигурирования источников данных на вашем компьютере зайдите в Control Panel, Administrative Tools, Data Sources(ODBC).
Мы видим, что ODBC при наличии нужного драйвера позволяет связываться с различными базами данных – Access, FoxPro, Oracle, Microsoft SQL, MySQL, SAP, DB2. Если в файле Excel создать именованную таблицу, ODBC способен ее распознать и работать как с таблицей базы данных.
Веб-проект в Visual Studio 2005 содержит предопределенную папку App_Data. В ней могут храниться файлы с данными, которые используются в приложении. Это могут быть файлы .mdf(MS SQL), .mdb(Microsoft Access), .xml и другие. ADO.NET 2.0
ADO.NET – это набор классов для работы с внешними данными. В новой версии .NET 2.0 он был расширен новыми свойствами и тожет получил номер 2.0.
Соединение в ADO.NET может происходить с помощью различных провайдеров. В настоящее время рекомендуется работать с помощью провайдера MS SQL или Oracle. Эти провайдер сами написаны на управляемом коде .NET. Еще один провайдер OleDb, позволяет получить доступ к другим источникам данных – Access, Excel, MySql, SAP. Провайдер OleDb написан на неуправляемом коде, но может работать вместе с .NET.
Классы ADO.NET объединены в несколько пространств имен.
System.Data – это ядро ADO.NET. Оно содержит классы, необходимые для связи посредством любых провайдеров данных. Эти классы представляют таблицы, строки, столбцы, DataSet(множество взаимосвязанных таблиц). Там определены интерфейсы (в смысле языка C#) соединений с базами данных, команд, адаптеров данных.
System.Data.Common – базовые классы для всех провайдеров данных – DbConnection, DbCommand, DbDataAdapter.
В System.Data.OleDb находятся классы, позволяющие работать с источниками данных OleDb, в том числе с MS SQL версии 6.0 и ниже. Там находятся такие классы, как OleDbConnection, OleDbDataAdapter и OleDbCommand.
System.Data.Odbc содержит классы, которые работают с источниками данных ODBC посредством провайдера .NET ODBC. Классы имеют аналогичные имена с префиксом Odbc.
System.Data.SqlClient. Здесь определен провайдер данных для СУБД SQL Server версии 7.0 и выше. Содержатся классы SqlConnection, SqlTransaction, SqlCommand и другие.
В System.Data.SqlTypes находятся классы, представляющие типы данных СУБД SQL Server.
Классы ADO.NET делятся на 3 типа. Классы типа Disconnected определяют базовую структуру данных, например DataTable. Они независимы от каких-либо провайдеров данных и могут создаваться и заселяться данными непосредственно в программе. Классы Shared базовые и общие для всех провайдеров. Классы Data Provider специфические для разных провайдеров. Программирование ADO.NET
Все провайдеры данных содержат классы соединений, адаптеров, команд. Схема типичной программы в ADO.NET такова.
1. Вначале создается соединение с базой данных – класс Connection, который обеспечивается необходимой информацией – строкой соединения. 2. Создается объект Command и задается команда, которую необходимо выполнить в данной СУБД. Эта команда может быть запросом SQL или исполняемой процедурой. Нужно задать параметры этой команды, если они имеются. 3. Если команда не возвращает данных, она просто выполняется с помощью одного из методов Execute. Например, это может быть удаление или обновление данных таблицы. 4. Если команда возвращает выборку данных, их необходимо куда-то поместить. Решите, нужно ли вам получить данные для последующего использования без связи с базой данных или же нужно просто быстро выполнить команду. В первом случае нужно создать класс DataAdapter и с его помощью сохранить данные в DataSet или в DataTable. Во втором случае создается класс DataReader, который требует сохранять соединение на все время работы, хранит выборку только для чтения и позволяет двигаться только вперед. Зато чтение с помощью DataReader выполняется в несколько раз быстрее, чем в DataAdapter. 5. Задать полученный DataSet или DataReader как источник данных элемента управления или вывести их на страницу другим способом.
Объект Connection
Объект Connection для соединения с базой данных нуждается в строке соединения для указания пути к СУБД и входа в систему. Свойства класса Connection показаны в таблице. OleDbConnection, SqlConnection, OdbcConnection – наследники класса Connection, специфические для провайдеров OleDb, MS SQL ODBC соответственно. Свойство Описание DataSource Путь к базе данных в файловой системе при использовании Oledb, имя экземпляра базы сервера при использовании SqlConnection Database Возвращает имя базы данных, используемой в объекте Connection после открытия State Возвращает текущее состояние соединения. Возможные значения – Broken, Closed, Connecting, Executing, Fetching и Open. ConnectionString Строка соединения с СУБД
Все свойства, кроме ConnectionString, только для чтения. Использование объекта Command
Объект Command исполняет запрос SQL, который может быть в форме встроенного текста, процедуры сервера или прямого доступа к таблице. Если это запрос на выборку данных SELECT, то данные обычно помещаются в DataSet или в DataReader. Методы и свойства определены в абстрактном классе DbCommand(через интерфейс IDbCommand), и их реализуют частные ненаследуемые классы OleDbCommand, SqlCommand, OdbcCommand.
Свойство CommandType может принимать значения из перечисления CommandType. По умолчанию это Text, то есть выполняется непосредственно текст команды SQL, который записан в свойстве Command. TableDirect означает, что в результате выполнения команды будет возвращено все содержание таблицы. StoredProcedure означает, что в Command находится имя процедуры сервера, которая и будет выполняться.
Свойство CommandText хранит текст запроса SQL или имя серверной процедуры.
CommandTimeout задает время ожидания ответа, по умолчанию равное 30 секунд. Если команда не выполнится в течение этого времени, будет выброшено исключение.
Процедуры сервера нуждаются в параметрах. Они хранятся в коллекции Parameters и имеют тип SqlParameter. Текстовые команды также могут получать параметры, перед которыми ставится префикс @. Например:
SELECT * FROM CUSTOMERS WHERE CITY = @CITY AND CONTACTNAME = @CONTACT
Часто используется метод ExecuteNonQuery. С помощью него можно выполнить любую операцию с базами данных, которая не связана с запросом и получением данных, как то обновление, удаление записей, создание и изменение таблиц, создание процедур сервера. Она возвращает количество измененных записей в том случае, если выполняются команды Select, Update, Delete.
ExecuteScalar возвращает результат запроса в случае, если это одно-единственное значение. Например, нужно узнать количество заказов конкретного покупателя. Запрос выполняется с помощью команды “Select count * where customerid=1”. Ее результат – выборка из одной строки и одного столбца. Ее можно выполнить и с помощью метода ExecuteReader, но ExecuteScalar будет выполняться быстрее. Если запрос возвратит большее количество строк или столбцов, они будут проигнорированы.
ExecuteRow возвращает единственную запись.
ExecuteReader выполняется, если нужно получить табличные данные. Результат выполнения – курсор, в котором можно двигаться только от начала до конца.
В результате выполнения метода ExecuteReader объекта Command создается объект DataReader. Всегда закрывайте соединения после использования, иначе оно останется активным и будет занимать ресурсы. Это можно сделать двумя способами. Первый – вызвать перегруженный метод ExecuteReader, который принимает параметр типа CommandBehavior со значением CommandBehavior.CloseConnection. В таком случае необходимо перелистать полученную выборку от начала до конца, и соединение закроется, когда будет достигнут конец. Если вы не хотите прочитать все данные, можете самостоятельно закрыть соединение методом Close.
public void CreateMySqlDataReader(string mySelectQuery, string myConnectionString) { SqlConnection myConnection = new SqlConnection(myConnectionString); SqlCommand myCommand = new SqlCommand(mySelectQuery, myConnection); myCommand.CommandType = CommandType.Text; myCommand.Connection.Open(); SqlDataReader myReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection); while (myReader.Read()) { Response.Write(myReader.GetString(0) + <br>); } myReader.Close(); myConnection.Close(); }
Развитые СУБД (теперь и MS Access) поддерживают транзакции. Транзакция – это последовательность команд, которая выполняется как одно целое. Например, при переводе денег сумма вычитается с одного счета и добавляется к другому. Если произойдет только одна из этих операций, банк или его клиенты понесут потери. поэтому важно, чтобы произошли обе операции или бы ни одна не произошла. Если на одном из этапов транзакции произошла ошибка, происходит откат(Rollback), то есть отменяются все ранее сделанные операции и база возвращается к состоянию до начала транзакции. Если все успешно, транзакция подтверждается операцией Commit.
Для поддержки транзакций введен класс SqlTransaction и ему подобные. У объекта Command есть свойство Transaction. Метод BeginTransaction объекта Connection заставляет базу данных перейти в режим транзакции.
Кроме того, необходимо всегда заключать программный код, работающий с базами данных, в блоки try/catch, так как работа часто происходит с удаленными серверами, и могут происходить самые разные ошибки как в сети. так и при работе самого сервера.
При этом выбрасывается исключение SqlException или OleDbException.
public void RunTransaction(string[] Queries, string myConnectionString) { SqlConnection conn = null; SqlTransaction trans = null; try { conn = new SqlConnection(myConnectionString); conn.Open(); trans = conn.BeginTransaction(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.Transaction = trans; foreach (string Query in Queries) { cmd.CommandText = Query; cmd.ExecuteNonQuery(); } trans.Commit(); } catch (SqlException SqlEx) { if (trans != null) { trans.Rollback(); } throw new Exception(An error occurred while transaction, SqlEx); return; } finally { if (conn != null) { conn.Close(); } } }
DataAdapter
DbDataAdapter является родительским классом для SqlDataAdapter, OleDbDataAdapter, OdbcDataAdapter. Этот класс содержит 4 объекта типа Command. Классы DataAdapter обеспечивают двусторонний обмен информацией.
* SelectCommand – эта команда используется для выборки данных из базы. При этом класс DataTable заполняется данными. * UpdateCommand – обновляет данные(редактирование записей). * InsertCommand – добавление новых записей * InsertCommand – команда для удаления записей
Метод Fill класса DbDataAdapter заполняет объекты DataSet или DataTable данными, прочитанными в результате выполнения команды SelectCommand. Эта команда должна быть запросом SQL типа Select. Если таблицы уже существуют, в него добавляются новые таблицы. Вообще метод Fill перегружен 8 раз. Например, DbDataAdapter.Fill Method (DataSet, String) добавляет в DataSet таблицу с именем, указанным во втором параметре. Если такая таблица уже есть, она обновляется. Доступ к таблице можно получить с помощью его имени индексатором.
DataTable tblProducts = data.Tables[Products];
Метод DbDataAdapter.Update записывает в базу данных все изменения, которые произошли в связанном с ним объекте DataSet. DataSet
DataSet – это класс, содержащий в себе одну или несколько таблиц DataTable и связи между ними. Класс DataSet – это представление в памяти информации, считанной через ADO из баз данных или XML. Он позволяет манипулировать данными после отключения от источника данных.
Коллекция таблиц хранится в свойстве Tables, а отношений – в свойстве Relations.
Основываясь на таблицах датасета, можно создавать представления – DataView.
Напишем страницу, в которой будут использоваться представленные классы.
База Northwind входит в комплект SDK. Ее можно установить на сервере, запустив командную строку SQLExpress.
using System; using System.Data; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data.SqlClient; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { String strConnect; String strCommand; strConnect = @Data Source=.SQLExpress;Initial Catalog=Northwind;Integrated Security=True; SqlConnection myConn = new SqlConnection(strConnect); strCommand = SELECT CategoryName, CategoryID FROM Categories; SqlDataAdapter myData = new SqlDataAdapter(strCommand, myConn); DataSet DataSet1 = new DataSet(); myData.Fill(DataSet1, Categories); strCommand = SELECT ProductName, UnitPrice, CategoryID FROM Products; myData.SelectCommand.CommandText = strCommand; myData.Fill(DataSet1, Products); DataSet1.Relations.Add(DataSet1.Tables[0].Columns[CategoryID], DataSet1.Tables[1].Columns[CategoryID]); DataView myView = new DataView(DataSet1.Tables[Products], , ProductName, DataViewRowState.CurrentRows); DataGrid1.DataSource = myView; DataGrid1.DataBind(); DropDownList1.DataSource = DataSet1.Tables[0]; DropDownList1.DataTextField = CategoryName; DropDownList1.DataValueField = CategoryID; DropDownList1.DataBind(); myConn.Close(); } }
Окно внешних источников данных.
В Visual Studio 2005 существует 3 вкладки просмотра проектов: Solution Explorer, Class Explorer, Server Explorer. Первыми двумя активно пользовались все, кто писал программы на C#(или другом языке), а третий по умолчанию не виден, откройте его из меню View->Server Explorer. Это окно позволяет работать с соединениями баз данных, просматривать статистику работы сервера (В VWD Express нет пункта Servers).
Соединение можно установить как с MS SQL, так и с файлом Access и любым источником ODBC, а также Oracle. Можно также создать новую базу данных MS SQL.
Когда соединение создано, вы через пользовательский интерфейс можете добавлять таблицы, просматривать их содержание, писать процедуры сервера и многое другое.
