Программирование ado.Net
Все провайдеры данных содержат классы соединений, адаптеров, команд. Схема типичной программы в ADO.NET такова.
Вначале создается соединение с базой данных – класс Connection, который обеспечивается необходимой информацией – строкой соединения.
Создается объект Command и задается команда, которую необходимо выполнить в данной СУБД. Эта команда может быть запросом SQL или исполняемой процедурой. Нужно задать параметры этой команды, если они имеются.
Если команда не возвращает данных, она просто выполняется с помощью одного из методов Execute. Например, это может быть удаление или обновление данных таблицы.
Если команда возвращает выборку данных, их необходимо куда-то поместить. Решите, нужно ли вам получить данные для последующего использования без связи с базой данных или же нужно просто быстро выполнить команду. В первом случае нужно создать класс DataAdapter и с его помощью сохранить данные в DataSet или в DataTable. Во втором случае создается класс DataReader, который требует сохранять соединение на все время работы, хранит выборку только для чтения и позволяет двигаться только вперед. Зато чтение с помощью DataReader выполняется в несколько раз быстрее, чем в DataAdapter.
Задать полученный 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.
sqlcmd -E -S (local)\SQLExpress -i InstNwnd.sql
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Работа с базой</title>
</head>
<body>
<form id="Form1" runat="server">
<asp:DropDownList ID="DropDownList1" runat="server">
</asp:DropDownList>
<asp:DataGrid id="DataGrid1" runat="server"></asp:DataGrid>
</form>
</body>
</html>
Файл с кодом:
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();
}
}