Из этой главы вы узнали о фундаментальных вещах которые не попали в первую главу. В следующей главе описаны все члены трех основных классов: SqlConnection, SqlCommand и SqlDataReader.

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

Далее создать запрос

Базы данных.

В этом разделе рассказывается о том зачем нужны базы данных и описана общая схема взаимодействия с ними.

В своем небольшом настольном (не использующем интернет) приложении вы можете хранить данные в простом текстовом файле с определенной структурой, известной только вам. Но допустим ваше приложение получило достаточное развитие, и данные от вашей программы стали нужны другим программам. Для того что бы не объяснять всем по какому принципу вы составляли свой текстовый файл, вы можете прибегнуть к помоши XML, общеизвестного языка для организации информации. Ваш проект продолжает развиваться, и вот появляется необходимость, что бы к данным от вашей программы могли получить доступ пользователи со всех краев света. Однозначно надо как-то опубликовать эти данные в интернете. К тому же теперь надо защитить их от злоумышленников, которым делать больше нечего кроме как портить ваши данные.

Для решения всех эти задач (универсальность, безопасность, доступность через сеть) и придумали Базы Данных (Data Base) и все что с ними связано.

Информация в базах данных находится в таблицах. У таблицы есть колонки и строки. В колонке может находится информация одного типа(текст, число, картинка…).

База данных, в физическом плане, представляет собой один или множество файлов, находящихся на сервере (server) – компьютере имеющего подключение к интернет и специализированеное програмное обеспечение.

Програмное обеспечение на сервере нужно для того, чтобы

Предоставить доступ к базам данных, находящихся на данном сервере,

Обезопасить эти данные.

Примером может послужить Microsoft SQL Server. Все программы которым нужны данные обращаются к SQL Server предоставляя ей запрос (command) – строку на специальном языке MySQL (Structured Query Language — язык структурированных запросов), в которой говорится какие именно данные им нужны. SQL Server обрабатывает запрос, и отсылает требуемые данные этим программам. Для того что бы обезопасить данные, прежде чем посылать запрос к SQL Server программа должна открыть(open) соединение(connection). Для этого приложение состовляет строку подключения(connection string) – текстовая строка с информацией о базе данных и о себе (например пароль и имя пользователя), и отправляет эту строку SQL Server’у. Тот проверяет имеется ли такая бд(база данных), имеет ли программа доступ к ней и если все нормально запоминает ее и спокойно предоставляет доступ до закрытия(close) соединения (которое делает таже программа).

Итак, для того чтобы прочитать\изменить данные из базы данных нам надо:

Открыть соединение

Составить запрос(ы)

Отослать его(их) на сервер и при необходимости принять ответ(ы).

Закрыть соединение.

Для того, что бы помогать программисту делать все это и нужна ADO.NET – технология доступа к данным.

Глава 1

1 Соединение.

Соединение с базой данных олицетворяет класс SqlConnection (англ. Connection – Соединение).

Cоздать объект этого класса можно с помощью конструктора без параметров. После создания нужно задать строку подключения (св-во ConnectionString типа string). Как составить строку подключения см здесь. Далее надо открыть соединение. Для этого есть метод Open. Все, соединение готово для работы. Как только соединение станет не нужно, надо обязательно его закрыть. Это делает метод Close.

Итак:

//Cоздать: 
SqlConnection connection = new SqlConnection();
//задать строку подключения: 
connection.ConnectionString = “Data Source=.\SqlExpress; Initial Catalog=Northwind; User ID=Pfight; Pasword=123456” ;
//открыть
connection.Open();

//использовать…
//закрыть
connection.Close();

Для упрощения создания строк подключения можно использовать класс SqlConnectionStringBuilder. В нем имеются свойства, одноименные парметрам строки подключения. Вы задаете зхначения этих свойств и после получаете строку подключения из свойства ConnectionString.

2 Запрос.

Запрос олицетворяет класс SqlCommand.(англ. Command – команда). Его назанчение – передать текстовую строку(запрос) на сервер(к базе данных). Следовательно ему нужен текст запроса и соединение. Первое – свойство CommandText, второе – Connection. Как составить текст запроса см здесь. Посылку запроса осуществляет метод ExecuteReader.

Итак:

//создать
SqlCommand comand = new SqlCommand();
//задать текст запроса
command.CommandText = “SELECT CusstomerID FROM Customers WHERE Age=20”;
//задать соединение
command.Connection = new SqlConnection(“Data Source=.\SqlExpress; Initial Catalog=Northwind; User ID=Pfight; Pasword=123456”);

3 Чтение.

Что бы прочитать данные из бд, нужно открыть соединение, составить запрос, в котором указать какие данне вам нужны, отправить его на сервер и просмотреть то, что вернул сервер.

Просмотр результатов запроса осуществляет класс SqlDataReader (англ. Data – данные, Reader – читатель).

Метод ExecuteReader (англ. Execute – выполнять) выполняет запрос, и возваращет SqlDataReader настроенный на чтение результатов. Внимание, соединение, которое использует command, должно быть открыто к моменту вызова ExecuteReader.

Метод Read (при первом вызове) выбирает первую строку,и помещает ее в параметризированное свойство (индексатор). При следущем вызове выбирает следующую строку и тд. Если строк больше нет, метод возвращает false, а если выборка прошла успешно, true.

3.1 Просмотр

Как я уже сказал, метод Read помещает строку в индексатор. Вот, что представляет собой строка:

Допустим нужно получить Value2. Для этого надо указать имя столбца(Column2) или его порядковый номер (2). Вот как это сделать:

object Value2 = reader[“Column2”]; 

или

object Value2 = reader[2]; //этот способ более эффективен

Как видно из кода, индексатор возвращает объект типа оbject. Его можно привести к нужному типу. Допустим мы знаем, что во втором столбце хранятся значения типа int. Тогда напишем так:

int Value2 = (int) reader[2];

Итак:

//открыть соединение
command.Connection.Open();

//выполнить запрос
SqlDataReader reader = command.ExecuteReader();

//поместить первую запись(строку) в индексатор
if(reader.Read()) //если записи нет, метод вернет false
{
//получить Value3
string Value3 = (string) reader[3];    //если мы знаем, что в Column3 значения типа string
}

4 Внесение изменений.

Что бы внести изменение в базу данных, нужно послать ей соответствующий запрос с помощью класса SqlCommand. Понятно, что такой запрос не должен возвращать данные. Чтобы отправить запрос, который не возвращает данные, вызовите command.ExecuteNonQuery() (не обращайте внимание на перевод назавания этого метода). Метод вернет колличество записей, которые затронул запрос.

Итак:

//составим запрос
command.ComandText = “INSERT INTO Customers (CustomerID, Name) VALUES (‘PFight’, ‘Павел’)”;
//отошлем его на сервер
comand.ExecuteNonQuery();

5 Кэширвоание данных.

В бд информация хранится в виде набора таблиц. Каждая таблица имеет колонки и строки. Все тоже самое в модели ADO.NET: DataSet – база данных, DataTable – таблица, DataColumn – колонка, DataRow – строка. Главная задача DataSet хранить коллекцию таблиц (объектов DataTable). Таблица содержит набор строк (объектов DataRow) и колонок(объектов DataColumn). Все данные хранятся в строках, единственное что делают колонки – определяют структуру таблицы. DataRow предоствляют доступ к данным постредством индексатора, почти точно такому же как в SqlDataReader. Рассмотрим каждый из этих классов более подробно.

5.1 DataRow

Объект DataRow жестко привязан к таблице, тк таблица определяет его структуру. Поэтому создать объект этого класса можно только с помощью метода NewRow объекта DataTable. После создания, его можно добавить в таблицу вызовом метода Add коллекции Rows объекта DataTable, т.к. автоматически этого не просиходит.

DataRow myrow = table.NewRow();
table.Rows.Add(myrow);

Главная особенность DataRow – возможность отменять изменения. При изменении данных, DataRow сохраняет исходную версию данных. Мы можем применить изменения с помощью метода AcceptChanges, а можем отменить вызовом метода RejectChanges. Также можем узнать состояние строки и просмотреть любую версию данных. Первое делается с помощью совойства RowState возвращающее занчение из перечисления DataRowState: Detached(не в таблице), Unchanged(не изменена), Added (добавлена в таблицу), Deleted (удалена из таблицы), Modified (изменена). Для второго есть две перегруженные версии индексатора принимающие вторым параметром значение из перечисления DataRowVersion: Original (исходная версия) или Current (измененная версия). Вот как это сделать:

int OriginalValue2 = (int)row[Column2”, DataRowVersion.Original]; //row – объект DataRow

Индексатор может принимать имя стобца, его порядковый номер, а может объект DataColumn. Плюс эти три версии со вторым параметром DataRowState. То есть всего 6 перегруженных версий.

Кроме того, если вызвать метод BeginEdit, изменить строку, а потом вызвать ChanelEdit то строка останется такой же как была перед вызовом BeginEdit. Метод EndEdit применит изменения.

5.2 DataColumn

В этом классе храниться в основном информация о том, какие данные могут лежать в этом стобце. Наиболее примечательно свойство DataType типа Type, которое определяет какого типа данные могут храниться в стобце. Объект Type можно получить с помощью оператора typeof(TypeName) или методом GetType, который имеется у любого объекта. Вот как проинициализировать это свойство:

column2.DataType = typeof(int); //column2 – объект DataColumn

Имя стобца храниться в свойстве ColumnName.

Не менее важно свойство DefaulValue, опреедляющее значение по умолчанию и AllowDBNull определяющее возможность присваивания null.

5.3 DataTable

Таблица состоит из колонок (свойство Columns) и строк ( свойство Rows ). Колонки определяют структуру, а строки хранят данные. О добавлении строк было сказано в разделе DataRow, а добавление колонок почти аналогично. Создаем колонку, и добавлем в колекцию Columns:

DataColumn column = new DataColumn();
column.DataType = typeof(int);
column.ColumnName = “Column2”;

table.Columns.Add(column); //table объект DataTable

Метод Select отдаленно напоминает посылку запроса к базе данных. Он принимает строку – фильтр, и на основе этого фильтра формирует и возвращает массив строк. Фильтр состоит из набора равенств (A=B) и неравенств (A<>B) объединенных ключевыми словами AND и OR. Равенства(и неравентсва) состоят из имени столбца(A) и значения(B). Значение заключается в одинарные кавычки. В целом фильтр похож на выражение WHERE в обычном запросе к бд. Специализацией метода Select можно счиать метод GetChanges, возвращающий изменненые строки.

«Размножать» таблицы можно методами Copy и Clone. Первый создает точную копию исхдной таблицы, а второй копирует в новую таблицу только струрктуру исходной.

Кстати в этом классе много полезных событий. Их имена говорять сами за себя, обратитесь к MSDN за справкой.

6 Кэширование данных & взаимодействие с БД.

Эта задача лежит на классе SqlDataAdapter. Он использует «основы» для того, что бы заполнить таблицы и передать изменения в базу данных. Для работы ему нужно соединение (SqlConnection) и составленные заранее запросы (SqlCommand). Тут давайте подробнее.

6.1 Соединение

За это отвечает свойство Connection. Создаете соединение точно также как было описано раньше и полученный объект SqlConnection помещаете в данное свойство. При посылке запроса к бд SqlDataAdapter проверяет состояние соединения: если открыто, то ничего не делает, если закрыто открывает, а после окончания работы заркывает. То есть соединение остается в том же состоянии что и было.

6.1 Запросы.

Давайте посмотрим, что можно делать с записями (строками) в базе данных: читать, изменять, удалять, вставлять. SqlDataAdapter имеет 4 свойства: SelectCommand, UpdateCommand, DeleteComand, InsertCommand. В них нужно поместить запросы (SqlCommand), каждый из которых делает специфическое действие с одной строкой (как это сделать Гл 2 пункт 3.2). Какую команду применять к строке SqlDataAdapter определяет с помощью свойства RowState строки(см раздел DataRow).

6.3 Работа с DataSet, DataTable.

Что значит работа: заполнение данными из бд и прередача изменений. Первое делает метод Fill, второе Update (об этом методе позже см Глава 2 пункт 3.2).

Метод Fill

Когда вы вызываете этот метод, SqlDataAdapter посылает запрос из свойства SelectComand к базе данных и принимает ответ. Ответ может состоять из одной или нескольких таблиц заполненных строками и столбцами. SqlDataAdapter создает DataTable объекты соответсвующие результатам запроса и добавляет их в DataSet, переданный в параметре. Если вместо DataSet в параметре прередать DataTable, то SqlDataAdapter заполнит этот DataTable строками из первой таблицы возвращенной запросом.

Кстати, методы Fill и Update вызывают AcceptChanges для всех обработанных DataRow. Если вы не хотите чтобы они это делали то назначте false свойствам AcceptChangesDuringFill и AcceptChangesDuringUpdate соответсвенно.

Глава 2

1 Пакетные запросы.

Это запросы, содержашие в себе несколько простых запросов. Например:

SELECT Column1 FROM Table1; SELECT Column1 FROM Table2

Вся проблема в том, что такие запросы возвращают несколько резульатов одновременно. Что бы их обработать в ADO.NET есть специальные конструкции. Рассмотрим их.

1.1 Пакетные запросы & SqlDataReader

Сначала метод Read этого класса читает результат первого простого запроса. Чтобы перейти к следующему запросу нужно вызвать метод NextResult. Если запросов больше нет, метод вернет false.

do
{ /*читаем результат*/ }
while(reader.NextResult());

1.2 Пакетные запросы & SqlDataAdapter

Этот класс все делает автоматически. Если его методу Fill передан DataSet, то будут создано необходимое колличество DataTable чтобы сохранить все результаты зароса. Если передан DataTable, то сохраниться только первый результат запроса.

2 Транзакции.

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

2.1 Создание & удаление.

Единственный способ создать транзакцию (объект SqlTransaction) – вызвать метод BeginTransaction(англ. Начать Транзакцию) объекта SqlConnection. Закончить транзакцию – вызвать метод Dispose транзакции.

SqlTransaction transaction = connection.BeginTransaction();
//выполняем запросы в транзакции…
transaction.Dispose();

2.2 Выполнение запросов.

Чтобы запрос поместить в транзакцию, надо свойству Transaction объекта SqlCommand назаначить нужную транзакцию (объект SqlTransaction). Дальше запрос выполняется как обычно. При открытой транзакции, все запросы использующие это соединение должны выполняться в этой транзакции.

command.Transaction = transaction;

comand.ExecuteReader();
//…

2.3 Отмена & применение изменений.

Чтобы отменить все изменения вызовите RollBack, чтобы применить Commit. Заметте, если не вызвать Commit, то автоматически вызовется RollBack при закрытии транзакции. Кроме того, можно создавать «точки восстановления» методом Save(string PointName). После, можно отменить только те изменения, которые были сделаны с момента создания точки, воспользовавшись перегруженной версией метода RollBack(string PointName).

transaction.Save(“BeforeQueries”);
try{
command.ExecuteNonQuery();
command2.ExecuteNonQuery();
transaction.Commit();
}
catch
{ transaction.RollBack(“BeforeQueries”); }

2.4 SqlDataAdapter & Транзакции

Этот класс не предоставляет никакой автоматики для осуществелния транзакций. Вам придется использовать свойство Connection, чтобы открыть транзакцию, и помещать в нее запросы из свойств SelectCommand, InsertCommand… вручную.

SqlTransaction transaction = adapter.Connection.BeginTransaction();
adapter.SelectCommand.Transaction = transaction;
adapter.Fill(dataset);
transaction.Dispose();

3 Хранимые процедуры & параметризированные запросы

В структуру базы данных, кроме всего прочего входят хранимые процедуры. Это запросы, текст которых храниться на сервере. Что бы такой запрос выполнить, нужно послать специальный запрос. Как это сделать см ЗДЕСЬ. Синтаксис этого специального запроса похож на вызов обычной функции, отсюда название «хранимые процедуры». Часто, нужно харнимой процедуре передать информацию(входные(input) параметры) и принять от нее информацию (выходные(output) параметры). Для этого в ADO.NET предусмотрен механизм параметризированных запросов.

3.1 Параметризированные запросы.

У объекта SqlCommand есть свойство Prameters – коллекция объектов SqlParameter. В тексте запроса вы указываете имя параметра, а при посылке запроса, на место имени подставляется значение параметра. Это справедливао для входных параметров. Значение выходных параметров становится известым после выполнения запроса. Имя параметра(объекта SqlParameter) храниться в свойстве ParameterName (имя должно начинаться со знака «@» ), значение в Value. Направление (входной/выходной) определяет свойство Dirrection типа ParameterDirection и может принимать значения: Input(входной), Output(выходной), InputOutput.

SqlParameter parameter = new SqlParameter();
parameter.ParameterName = “@MyParameter”;
parameter.Value = stroka[“Column2”];
parameter.Dirrection = ParameterDirrection.Input;

comand.Parameters.Add(parameter);
comand.ComandText = “SELECT Column1 FROM MyTable WHERE Column2=@MyParameter”;

3.2 Параметризированные запросы & SqlDataAdapter

Задавать значение параметров перед каждым вызовом Update довольно скучно. Можно немного автоматизиовать этот процесс. SqlDataAdapter отправляет изменения построчно. Если присвоить свойству параметра SourceColumn имя стобца, то при вызове Update, SqlDataAdapter значению данного параметра присвоит stroka[SourceColumn], где stroka – обновляемая строка. А еще можно указать версию данных через свойство SourceVersion типа DataRowVersion. То есть значению параметра будет присвоено stroka[SourceColumn, SourceVersion].

parameter.ParameterName = “@MyParameter”;
parameter.SourceColumn = “Column2”;
parameter.SourceVersion = DataRowVersion.Curent;
adapter.InsertComand.Parameters.Add(parameter);
adapter.InsertCommand.ComandText = “INSERT INTO MyTable(Column2) VALUES (@MyParameter)”;
adapter.Update(dataset);

4 Реляционные данные и ограничения.

Таблицы в базе данных могут быть связаны между собой. Например в таблице с заказами, лучше расположить только ID пользователя, а подробную информацию о нем в другой таблице. Такая модель предполагает два условия: ID каждого пользователя уникально и в таблице с заказами нет ID несуществующих пользователей. Для строгого контроля соблюдения этих условий был создан механизм реляционных(связанных) данных.

4.1 Связи.

Связь между таблицами олицетворяет класс DataRelation. В объектах этого класса храниться два и более столбцов из разных таблиц, указывая, что эти столбцы связаны. Один из столбцов – родительский (например столбец с ID, в таблице пользователей), другой(другие) - дочерние (например столбец с ID пользователей в таблице заказов).

DataRelation relaton = new DataRelation(“MyRelation”, 
					          tableCustomers.Columns[“Column1”], //родительский
					          tableOrders.Columns[“Column1”]  ); //дочерний

4.2 Ограничения.

Осуществляют контроль соблюдения вышеупомянутых условий. Сщуествует два класса ограничений: UniqueConstraints и ForignKeyConstraints (оба производные от Constraint). Первое не дает в пределах одного столбца поместить два одинаковых значения. Второе не дает «в таблицу с заказами поместить ID несуществующих пользователей». То есть, если на столбец наложено это ограничение, то он может принимать только те значения, которые определены в родительском столбце.

Constraint uconstraint = new UniqueConstraint(tableCustomers.Columns[“Column1”]);
Constraint fkconstraint = new ForignKeyConstraint(tableOrders.Columns[“Column1”]);
//ограничение еще не наложено!!!

4.3 Наложение ограничений.

В DataTable есть свойство Constraints. Добавляя в эту коллекцию объекты мы накладываем ограничения. Добавить их можно методом Add этой же колекции. Варианта два, либо вручную создать Constraint объект и добавить, либо воспользоваться перегруженными версиями метода, похожими на конструкторы соответствующих ограничений. Если вы попытаетесь нарушить ограничение, возникнет исключение.

tableCustomers.Constraints.Add(uconstraint);
tableOrders.Constraints.Add(fkConstraint);

4.4 Добавление связей.

Связи между таблицами храняться в свойстве Relations объекта DataSet. При добавлении связи, родительскому стобцу автомтически назанчается ограничение UniqueConstrainst, а дочернему ForignKeyConstrainst.

dataset.Relations.Add(relation);

Глава 3

Соединение

Класс SqlConnection

Упощение синтаксиса

Создание SqlCommand

SqlCommand CreateCommand()

Создает SqlCommand и его свойству Connection назначает this.

Изменение строки подключения

string ChangePassword(string connectionString, string newPassword);

Заменяет в строке подключения пароль на newPassword.

Событие или исключение?

bool FireInfoMessageEventOnUserErrors{ get; set; }

Если true, то вместо возбуждения исключений при ошибках возаращаемых сервером, будет генерироватся событие InfoMessage.

Информация о бд

Получение структуры бд.

DataTable GetSchema(string shemaName, string[] restrictions);

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

DataTable GetSchema();

Метод вернет таблицу, первый столбец CollectionName которой будет содержать доступные значения для параметра shemaName. Подобный, список можно получить просмотрев поля класса SqlClientMetaDataCollectionNames.

Второй параметр позволяет ограничить возварщаемые данные. Например если вам нужны столбцы определенной таблицы следует передать в этом параметре следующее: new string[] {“dbName”, “dbo”, “TableName”, null}; Узнать что нужно передавать в этом параметре можно вызвав GetSchema(“Restrictions”). Если вы не собираетесь ограничивать возарщаемые данные, можно передать в этом параметре null или просто вызвать перегруженную версию метода с одним параметром:

DataTable GetSchema(string shemaName);

Пример.

using System;
using System.Data.SqlClient;
using System.Data;

// Программа позволяет проверить существует ли столбец в таблице

namespace DataBaseTrening
{
    class GetSchema
    {
        static void Main()
        {        
            
            //подключение
            SqlConnection connection = new SqlConnection("Data Source=.\\SqlExpress;AttachDbFilename=S:\\Документы\\Программирование\\Seppa_-_Programmirovanie_na_Microsoft_ADO_NET_20_djvu\\model.mdf;Integrated Security=True;Connect Timeout=5;User Instance=True");
            
            connection.Open();
            Console.WriteLine("Программа позволяет проверить существует ли столбец в таблице");
            

            string TableName;
            string ColumnName;
            //запрашиваем и проверяем имя таблицы
            while (true)
            {
                Console.Write("Введите имя таблицы: ");
                TableName = Console.ReadLine();
                if (TableExist(connection, TableName)) break;
                else
                {
                    Console.WriteLine("Таблицы с таким именем не существует");
                }
            }
            //запрашиваем и проверяем имя столбца
            while(true)
            {
                Console.Write("Введите имя столбца: ");
                ColumnName = Console.ReadLine();

                if (ColumnExist(connection, TableName, ColumnName))
                {
                    Console.WriteLine("Столбец с именем {0} в таблице {1} существует ", ColumnName, TableName);
                }
                else
                {
                    Console.WriteLine("Столбца с таким именем не существует");
                }
            }
            connection.Close();
            
        }

        //проверяет существует ли столбец с заданым именем в заданной таблице
        private static bool ColumnExist(SqlConnection connection, string TableName, string ColumnName)
        {
            //получим столбцы только заданной таблицы
            string[] restrictons = new string[] {connection.Database, "dbo", TableName, null};
            DataTable columns = connection.GetSchema("Columns", restrictons);
            foreach (DataRow row in columns.Rows)
            {
                if ((string)row["COLUMN_NAME"] == ColumnName) return true;
            }
            return false;
        }

        //проверяет существует ли таблица с заданным именем
        private static bool TableExist(SqlConnection connection, string TableName)
        {

            DataTable tables = connection.GetSchema("Tables");
            foreach (DataRow row in tables.Rows)
            {
                if( (string)row["TABLE_NAME"] == TableName)
                    return true;
            }

            return false;    
        }
    }
}

Имя бд.

string Database{ get; }

Свойство возваращает значенеи указанное в строке подключения как InitialCatalog.

Сервер.

string DataSource {get; }

В этом свойстве храниться информация о сервере. Чаще всего оно возвращает значение указанное в строке подключения как DataSource.

Версия SqlServer.

string ServerVersion{ get; }

Возможное значение: "08.00.0760"

ID локального компьютера

string WorkstationID{ get; }

Имя компьютера на котором выполняется программа.

Соединение

Строка подключения

string ConnectionString{ get; set; }

Можно автоматически задать при создании экземпляра, используя конструктор SqlConnection(string connectionString);

Открытие соединения

void Open();

Для выполнения запросов, соединение нужно открыть, что и делает данный метод. Не забудте заключить вызов этого метода в try/catch блок, тк. любая ошибка в настройке SqlConnection заставит этот метод сгененировать исключение.

Закрытие соединения

void Close();

Для снятия нагрузки с бд и для освобождения памяти следует закрывать соединение после использования. Метод порождает событие Disposed.

Время ожидания ответа сервера

ConnectionTimeout{ get; }

Из-за несовершенности связи с сервером, ответ может прийти через некоторое время. Сколько секунд следует ждать прежде чем возбудить исключение SqlException показывает это свойство. Задать его значение можно в строке подключения добавив ConnectTimeout=30;

Размер пакета

int PacketSize{ get; }

Обмен данными с сервером просиходит пакетами. Сколько байт в одном пакете показывает это свойство. Задать его значение можно в строке подключения добавив PacketSize=8000;

Состояние соединения

ConnectionState State{ get; }

В ADO.NET 2.0 свойство может принимать только значения Open и Closed – открыто и закрыто соответсвенно.

Статистика

По умолчанию статистика не ведется. Для ее включения нужно свойству StatisticsEnabled назначить true;

Получить статистику можно вызвав метод

IDictionary RetrieveStatistics();

Он возвращает набор пар ключ – значение. Ниже првиедены возмжные ключи:

BuffersReceived – пакетов получено

BuffersSent – пакетов послано

BytesReceived – байт получено

BytesSent – байт послано

ConnectionTime – время, прошедшее с момента открытия соединения

CursorOpens – курсоров открыто

ExecutionTime – время, которое сервер провел выполняя запросы

IduCount – колличество INSERT/DELETE/UPDATE запросов обработанно

IduRows – колличество строк затронутых INSERT/DELETE/UPDATE – запросами

NetworkServerTime – суммарное время ожидания ответов сервера

PreparedExecs – колличество выполненных подготовленных запросов (с помощью метода Prepare класса SqlCommand)

Prepares - колличество подготовленных запросов (с помощью метода Prepare класса SqlCommand)

SelectCount – количество SELECT-запросов

SelectRows – колличество строк, возвращенных SELECT-запросами

ServerRoundtrips – суммарное время посылки запросов и ожидания ответов

SumResultSets – колличество ответов принятых с сервера

Transactions – колличество открытых транзакций

UnpreparedExecs – колличество выполненных неподготовленных запросов

Для получения значения соответствующего ключу достаточно прердать индексатору объекта IDictionary строку – имя ключа:

IDictionary statistics = connection.RetrieveStatistics();
long BytesSent = (long) statistics[“BytesSent”];

Счетсчики статистики верны для промежутка времени с момента открытия соединения, если статистика была включена до его открытия, с момента включения статистики, если статистика была включена после открытия соединения или с момента последнего вызова метода

void ResetStatistics();

Этот метод сбрасывает все счетчики.

Пул соединений

Это средство повышения производительности. Оно может помоч, если ваш код последовательно открывает и закрывает одно соединение. Благодаря пулу соединений, вместо создания каждый раз нового соединения будет использоваться одно. Отключить пул можно добавив в строку подключения выражение Polling=false. Закрыть все неиспользуемые на данных момент содинения в пуле можно вызвав метод

void ClearAllPools();

Чтоб закрыть только те соединения в пуле которые связаны с конктреным соединением вызовите

void ClearPool(SqlcConnection connection) ;

Запрос

Класс SqlCommand

Создание запроса

Текст запроса

string CommandText{ get; set; }

Текста запроса на языке MySql

Соединение

SqlConnection Connection{ get; set; }

Соединение по которому будет производиться запрос.

Транзакция

SqlTransaction Transaction {get; set; }

Для выполнения запроса в определенной транзакции, назначьте свойству эту транзазкцию.

Параметры

SqlParameterCollection Parameters{ get; set; }

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

Обработка возвращаемых значений при обновлении записей

UpdateRowSource UpdatedRowSource{ get; set; }

Иногда требуется чтобы UPDATE запрос возваращал данные. Будут ли влиять эти данные на обновляемый DataRow (при обновлении методом Update класса DataAdapter) и определяет свойство.

Выполнение запроса

Выполнение и чтение результатов

SqlDataReader ExecuteReader();

Метод используется в основном для выполнения SELECT запросов. Возвращаемый SqlDataReader позволяет быстро просмотреть возвращенные данные.

Выполнение без возвращаемых данных

int ExecuteNonQuery();

Метод используется для выполнения UPDATE/DELETE/INSERT запросов. Возвращаемое значение – колличество затронутых записей.

Выполнение для получения одного объекта.

object ExecuteScalar();

Метод используется для простых SELECT запросов, возвращающих один объект. Если запрос возвращет больше одного объекта, то используется только первый объект первой возвращенной строки, а остальные значения отбрасываются.

Асинхронное выполнение

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

IAsyncResult BeginExecuteReader();

SqlDataReader EndExecuteReader(IAsyncResult result);

IAsyncResult BeginExecuteNonQuery();

int EndExecuteNonQuery(IAsyncResult result);

IAsyncResult BeginExecuteScalar();

object EndExecuteScalar(IAsyncResult result);

BeginXXX метод возвращает объект IAsyncResult, который позволяет проверять закончена ли операция. Чтобы получить результат нужно вызвать

EndXXX метод, передав в параметре объект возвращенный BeginXXX методом. Если к моменту вызова EndXXX метода опреация еще не завершилась, поток приостанавливается до завершения операции.

Отмена выполнения

void Canсel();

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

Время ожидания ответа сервера

int ComandTimeout{ get; set; }

Свойство определяет сколько секунд следует ждать ответ от сервера, прежде чем возбудить исключение. По умолчанию оно ранвно 30. При значении 0 ответ будет ожидаться бесконечно. Сбросить свойство в значение по умолчанию можно вызвав метод

void ResetCommandTimeout();

Чтение.

класс SqlDataReader

Информация о данных.

Информация о столбцах

DataTable GetSchemaTable();

Получение информации о характеристиках слобцов возвращенных запросом. Характеристики представлены в виде таблицы. Ее столбцы идут в следующем порядке:

ColumnName - Имя столбца

ColumnOrdinal - порядковый номер

ColumnSize – Максимальная величина значения в стобце.

NumericPrecision – максимальная точность для численных типов, и null для иных.

NumericScale – колличество цифр справа от запятой для типов поддерживающих ее, и null для остальных.

IsUnique – true только для timestamp столбцов.

IsKey – уникальны ли значения в пределах столбца.

BaseServerName – имя SqlServer.

BaseCatalogname - имя бд или null если неудастся определить таковое.

BaseColumnName – отличается от ColumnName при использовании псевдонимов.

BaseShemaName – имя схемы содержащей столбец.

BaseTableName – имя таблицы или представления содержащего столбец.

DataType - .NET-тип данных в столбце.

AllowDbNull – разрешено ли значение DbNull для столбца.

ProviderType – Sql-тип данных в столбце. (о Sql-типах см описание GetProviderSpecificFieldType).

IsAliased – true если имя столбца – псевдоним

IsExpression – true если столбец основан на выражении (вычисляется на основе значений других столбцов).

IsIdentity – является ли столбец автоинкрементным (при добавлении строки его значение в ней = предыдущее значение + шаг инкремента).

IsAutoIncrement - является ли столбец автоинкрементным (при добавлении строки его значение в ней = предыдущее значение + шаг инкремента).

IsRowVersion - true если столбец содержит индетификатор строки, который не может быть изменен.

IsHiden – true если столбец скрыт

IsLong – true елси столбец содержит BLOB (большие данные в двоичном формате).

IsReadOnly – определяет можно ли модифицировать значения столбца.

ProviderSpecificDataType – Sql-тип данных.(о Sql-типах см описание GetProviderSpecificFieldType).

DataTypeName – имя типа данных.

XmlSchemaCollectionDataBase – имя бд хранящей схему для столбцов с XML данными.

XmlSchemaCollectionOwningSchema

XmlSchemaCollectionName – имя схемы лдя столбцоы с XML данными.

Имя типа данных в столбце

string GetDataTypeName(int i);

Метод позволяет по порядковому номеру столбца, узнать имя типа данных, хранящихся в столбце. Первый столбец имеет номер 0.

Тип данных в столбце

Type GetFieldType(int i);

Аналогичен предыдущему, однако возвращает объект Type. Если в бд используется UDT(тип определенный пользователем), метод вернет null.

Sql-Тип данных

Type GetProviderSpecificFieldType(int i);

Аналогичен предыдущему, разве что возвращает Sql-Тип. Это тип определенный в пространстве имен System.Data.SqlTypes. Все эти типы дублируют стандартные .NET типы и добавляют некоторые специфичные для бд SqlServer. Названия типов аналогичны стандартным, с приставкой Sql. Все Sql-типы поддерживают значение DbNull характеризующее null в бд.

Порядковый номер

int GetOrdinal(string name);

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

Имя

string GetName(int order);

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

Колличество значений

int FieldCount();

Колличество возвращенных значений.

int VisibleFieldCount();

Коллечетсво значений во всех нескрытых столбцах.

Колличество затронутых строк

int RecordsAffected{ get; }

Если запрос изменял/вставлял/удалял строки, это свойство содержит колличество затронутых строк.

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

Чтение

bool Read();

Читает следующую строку. Возвращаемое значение – существует ли эта строка. Чтобы получить прочитанные данне следует обратиться к следующему разделу главы «Получение данных».

Следующий результат

bool NextResult();

При чтении результатов пакетного запроса, осуществляет переход к чтению результатов следующего простого запроса из пакета. Вохзвращеняемое значение – существует ли таковой.

Закрытие

void Сlose();

Освобождает все неуправляемые ресурсы.

Получение данных.

Получение данных неопределенного типа.

1) Индексаторы

object Item(string columnName);

object Item(int columnOrder);

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

2)

object GetValue(int i);

object GetSqlValue(int i);

Аналогично индексатору Item(int columnOrder);

3)

int GetValues(object[] bufer);

int GetSqlValues(object[] bufer);

Методы позволяют получить все прочитанные записи(из одной строки) в виде массива oбъектов object. Размер массива bufer должен быть достаточным чтобы вместить все записи. Узнать эту длинну можно через свойство FieldCount. Возвращаемое значение GetValues – колличество знасенный в bufer объектов.

Получение данных конкретного типа

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

bool GetBolean(int i);

byte GetByte(int i);

long GetBytes(int i, int dataIndex, byte[] bufer, int buferIndex, int length);

i – подрядковый номер столбца, dataIndex – позиция, с которой начать чтение, bufer – буфер, в который записывать результат, buferIndex – позиция с котрой писать в буфер, lenght – максимальное колличество байт для записи в буфер. Возвращаемое значение – колличество прочитанных байт.

char GetChar(int i);

long GetChars(int i, int dataIndex, byte[] bufer, int buferIndex, int length);

Сигнатура схожа с GetBytes.

DateTime GetDataTime(int I);

decimal GetDecimal(int i);

double GetDouble(int i);

float GetFloat(int i);

Guid GetGuid(int i);

Int16 GetInt16(int i);

Int32 GetInt32(int i);

Int64 GetInt64(int i);

string GetString(int i);

TimeSpan GetTimeSpan(int i);

SqlBinary GetSqlBinary(int i);

SqlBoolean GetSqlBoolean(int i);

SqlByte GetSqlByte(int i);

SqlBytes GetSqlBytes(int i);

SqlChars GetSqlChars(int i);

SqlDateTime GetSqlDateTime(int i);

SqlDecimal GetSqlDecimal(int i);

SqlDouble GetSqlDouble(int i);

SqlGuid GetSqlGuid(int i);

SqlInt16 GetSqlInt16(int i);

SqlInt32 GetSqlInt32(int i);

SqlInt64 GetSqlInt64(int i);

SqlMoney GetSqlMoney(int i);

SqlSingle GetSqlSingle(int i);

SqlString GetSqlString((int i);

SqlXml GetSqlXml(int i);

Получение DbDataReader

DbDataReader GetData(int i);

Члены DbDataReader во многом схожи с SqlDataReader, тк оба релизуют интерфейс IDataReader.

получение IEnumerator

IEnumerator GetEnumerator();

С помощью объекта IEnumerator можно последовательно пересмотреть все прочитанные записи(из одной строки). Данный интерфейс определяет свойство Current типа object, возварщающее текущую запись, и метод MoveNext для премещения к следующей записи. Кроме того есть метод Reset, возвращающий указатель в начало.

string ConnectionString = “…”;
SqlConnection connection = new SqlConnection(ConnectionString);
connection.Open();

string CommandText = “...”
SqlCommand comand = connection.CreateCommand();
comand.CommandText = CommandText;

SqlDataReader reader = command.ExecuteReader();
while(reader.Read())
{
Console.WriteLine(reader[“ColumnName”]);
}

Отослать его на сервер и при необходимости принять результат

Прочитанные данные можно кэшировать в объекты DataRow, DataTable и DataSet и синхронизировать изменения этих данных с базой данных с помощью класса SqlDataAdapter.