Методы ExecuteQuery(), Translate() и ExecuteCommand()

LINQ --- LINQ to DataSet и SQL --- Методы ExecuteQuery(), Translate() и ExecuteCommand()

»» В ДАННОЙ СТАТЬЕ ИСПОЛЬЗУЕТСЯ ИСХОДНЫЙ КОД ДЛЯ ПРИМЕРОВ

ExecuteQuery()

Нет сомнений, что API-интерфейс LINQ to SQL великолепен. Использование стандартной точечной нотации LINQ или синтаксиса выражений превращает построение запросов LINQ в пару пустяков. Но рано или поздно возникает потребность просто выполнить запрос SQL. С помощью LINQ to SQL можно делать и это. На самом деле так можно поступить и получить в результате те же сущностные объекты.

Метод ExecuteQuery позволяет указать запрос SQL как строку и даже включать в нее параметры для подстановки, как это делается при вызове метода String.Format, причем полученный результат будет транслирован в последовательность сущностных объектов.

Это очень просто. Возможно, вы спросите, а как насчет ошибок внедрения SQL? Разве соответствующий способ делать это требует использования параметров? Да, требует. И метод ExecuteQuery позаботится обо всем этом.

Метод ExecuteQuery имеет один прототип, описанный ниже:

IEnumerable<T> ExecuteQuery<T>(string query, params object[] parameters)

Этот метод принимает, как минимум, один аргумент — запрос SQL и ноль или более параметров. Строка запроса и необязательные параметры работают как метод String.Format. Метод возвращает последовательность типа Т, где T — сущностный класс.

Следует помнить, что если указывается значение столбца в конструкции where самой строки запроса, то столбцы символьного типа должны заключаться в одиночные кавычки, как это принято в нормальном запросе SQL. Но если значение столбца подставляется как параметр, то нет необходимости заключать спецификатор параметра, такой как {0}, в одиночные кавычки.

Чтобы столбец в запросе распространился на сущностный объект, имя столбца должно соответствовать одному из отображенных полей сущностного объекта. Разумеется, этого можно достичь, добавляя "as <имя_столбца>" к действительному имени столбца, где <имя__столбца> — отображенный в сущностном объекте столбец.

Каждое отображенное поле не обязательно должно быть возвращено запросом, но первичные ключи — безусловно, должны. В запросе можно извлекать поля, которые не отображаются ни на одно поле сущностного объекта, но они не будут распространяться на сущностный объект.

Для демонстрации простого примера вызова метода ExecuteQuery в нижеуказанном примере обратимся к таблице Customers:

// Используйте свое подключение
            Northwind db = new Northwind(@"Data Source=MICROSOF-1EA29E\SQLEXPRESS;
                       Initial Catalog=C:\NORTHWIND.MDF;
                       Integrated Security=True");

            IEnumerable<Customer> custs = db.ExecuteQuery<Customer>(
                @"select CustomerID, CompanyName, ContactName, ContactTitle
                from Customers where Region = {0}", "WA");

            foreach (Customer c in custs)
            {
                Console.WriteLine("ID = {0} : Name = {1} : Contact = {2}",
                  c.CustomerID, c.CompanyName, c.ContactName);
            }

Этот пример очень прост. Обратите внимание, что поскольку используется средство подстановки параметров метода за счет указания в качестве параметра "WA" вместо жесткого кодирования его в запросе, спецификатор формата незачем заключать в одиночные кавычки. Вот результат:

Если бы нужно было выполнить тот же запрос, но без подстановки параметров, понадобилось бы заключить часть "WA" в одиночные кавычки, как это делается в нормальном SQL-запросе:

...
IEnumerable<Customer> custs = db.ExecuteQuery<Customer>(
                @"select CustomerID, CompanyName, ContactName, ContactTitle
                from Customers where Region = 'WA'");

В случае если вы сразу не заметили — WA теперь заключено в одиночные кавычки в строке запроса. Результат выполнения этого кода такой же, как и предыдущего.

К этому еще можно добавить указанное имя столбца, если реальное его имя не соответствует имени столбца в базе данных. Поскольку в строке запроса допускается выполнять соединения, можно опрашивать столбцы с разными именами из разных таблиц, но указывать их имя как одно из отображенных полей сущностного класса:

// Используйте свое подключение
            Northwind db = new Northwind(@"Data Source=MICROSOF-1EA29E\SQLEXPRESS;
                       Initial Catalog=C:\NORTHWIND.MDF;
                       Integrated Security=True");

            IEnumerable<Customer> custs = db.ExecuteQuery<Customer>(
                   @"select CustomerID, Address + ', ' + City + ', ' + Region as Address
                   from Customers where Region = 'WA'");

            foreach (Customer c in custs)
            {
                Console.WriteLine("Id = {0} : Address = {1}",
                    c.CustomerID, c.Address);
            }

Интересная часть этого примера касается соединения нескольких столбцов базы данных и строковых литералов с указанием имени отображенного поля, чтобы получить адрес, город и регион в единственном члене Address сущностного объекта. В этом случае все поля взяты из одной таблицы, но они вполне могли поступать из соединения с другой таблицей. Вот результат:

Конечно, применяя такой трюк, не забывайте о том, что если один из возвращенных сущностных объектов будет модифицирован, а затем будет вызван метод SubmitChanges, могут быть получены некоторые записи базы данных с сомнительной информацией. Но при правильном применении такой прием может быть весьма удобным.

Translate()

Метод Translate подобен ExecuteQuery в том, что он транслирует результаты SQL-запроса в последовательность сущностных объектов. Отличие заключается в том, что вместо передачи строки, содержащей оператор SQL, ему передается объект типа System.Data.Common.DbDataReader, такой как SqlDataReader. Этот метод удобен для интеграции кода LINQ to SQL с существующим кодом ADO.NET.

Метод Translate имеет один прототип, описанный ниже:

IEnumerable<T> Translate<T>(System.Data.Common.DbDataReader reader)

Методу Translate передается объект типа System.Data.Common.DbDataReader, a возвращает он последовательность сущностных объектов.

В следующем примере создается запрос с помощью ADO.NET. Затем применяется метод Translate для трансляции результатов запроса в последовательность сущностных объектов Customer:

System.Data.SqlClient.SqlConnection sqlConn =
                new System.Data.SqlClient.SqlConnection(
                    @"Data Source=MICROSOF-1EA29E\SQLEXPRESS;Initial Catalog=C:\NORTHWIND.MDF;Integrated Security=True");

            string cmd = @"select CustomerID, CompanyName, ContactName, ContactTitle
                     from Customers where Region = 'WA'";

            System.Data.SqlClient.SqlCommand sqlComm =
              new System.Data.SqlClient.SqlCommand(cmd);

            sqlComm.Connection = sqlConn;
            try
            {
                sqlConn.Open();
                System.Data.SqlClient.SqlDataReader reader = sqlComm.ExecuteReader();

                Northwind db = new Northwind(sqlConn);
                IEnumerable<Customer> custs = db.Translate<Customer>(reader);

                foreach (Customer c in custs)
                {
                    Console.WriteLine("ID = {0} : Name = {1} : Contact = {2}",
                        c.CustomerID, c.CompanyName, c.ContactName);
                }
            }
            finally
            {
                sqlComm.Connection.Close();
            }

Как видите, в предыдущем коде изначально нет никаких ссылок на LINQ. Подключение SqlConnection установлено, запрос сформирован, команда SqlCommand создана, соединение открыто, а запрос выполнен все так, как в самом обычном запросе ADO.NET. Далее добавляется некоторый код LINQ - создается экземпляр DataContext по имени Northwind с использованием объекта SqlConnection из ADO.NET. Затем вызывается метод Translate с передачей ему уже созданного объекта reader, чтобы результаты запроса могли быть преобразованы в сущностные объекты, которые можно перечислять и отображать результаты.

Обычно, поскольку этот код унаследован, в нем присутствует еще что-то, что делается с результатами, но в рассматриваемом примере это не обязательно. Все, что остается добавить — код закрытия соединения.

Этот пример демонстрирует, насколько изящно LINQ to SQL может взаимодействовать с ADO.NET. Взглянем на результаты работы кода:

ExecuteCommand()

Подобно ExecuteQuery, метод ExecuteCommand позволяет указывать действительный оператор SQL, чтобы выполнить его в базе данных. Это значит, что его можно использовать для выполнения операторов вставки, обновления или удаления, а также для вызова хранимых процедур. Кроме того, как и методу ExecuteQuery, ему можно передавать параметры.

При вызове метода ExecuteCommand следует помнить об одной вещи: он выполняется немедленно, потому вызывать метод SubmitChanges незачем.

Метод ExecuteCommand имеет единственный прототип, описанный ниже:

int ExecuteCommand(string command, params object [] parameters)

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

Имейте в виду, что если указывается значение столбца в конструкции where самой строки запроса, то столбцы символьного типа должны быть заключены в одиночные кавычки, как это принято в нормальном запросе SQL. Но если подставляется значение столбца как параметр, то нет необходимости заключать спецификатор параметра, такой как {0}, в одиночные кавычки.

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

// Используйте свое подключение
            Northwind db = new Northwind(@"Data Source=MICROSOF-1EA29E\SQLEXPRESS;
                       Initial Catalog=C:\NORTHWIND.MDF;
                       Integrated Security=True");

            //System.Data.SqlClient.SqlConnection sqlConn =
              //  new System.Data.SqlClient.SqlConnection(
                //    @"Data Source=MICROSOF-1EA29E\SQLEXPRESS;Initial Catalog=C:\NORTHWIND.MDF;Integrated Security=True");

            Console.WriteLine("Вставить заказчика ...");
            int rowsAffected = db.ExecuteCommand(@"insert into Customers values ({0}, 'Lawn Wranglers', 
                   'Mr. Abe Henry', 'Owner', '1017 Maple Leaf Way', 'Ft. Worth', 'TX', 
                   '76104', 'USA', '(800) MOW-LAWN', '(800) MOW-LAWO')","LAWN");
            Console.WriteLine("Вставка завершена.\n");

            Console.WriteLine("Оказано влияние на {0} строк. Находится ли заказчик в базе данных?",
              rowsAffected);

            Customer cust = (from c in db.Customers
                             where c.CustomerID == "LAWN"
                             select c).DefaultIfEmpty<Customer>().Single<Customer>();

            Console.WriteLine("{0}\n",
              cust != null ?
                "Да, находится." : "Нет, не находится.");

            Console.WriteLine("Удаление заказчика ...");
            rowsAffected =
              db.ExecuteCommand(@"delete from Customers where CustomerID = {0}", "LAWN");

            Console.WriteLine("Удаление завершено.{0}", System.Environment.NewLine);

Как видите, в этом примере нет ничего особенного. Здесь вызывается метод ExecuteCommand с передачей строки команды, а также некоторых параметров. Затем выполняется запрос с использованием LINQ to SQL — просто для проверки, что запись действительно вставлена в базу данных. Результаты запроса выводятся на консоль. Для возврата базы в исходное состояние снова вызывается метод ExecuteCommand, чтобы удалить только что вставленную запись.

Ниже показаны результаты выполнения этого кода: