Методы ExecuteCommand и ExecuteQuery. Хранимые процедуры

Данное руководство устарело. Актуальное руководство: по ADO.NET и работе с базами данных в .NET 6

Для непосредственного выполнения кода sql в классе DataContext определены методы ExecuteCommand() и ExecuteQuery().

Метод ExecuteCommand() принимает выполняемое sql-выражение и список параметров:

string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True";
DataContext db = new DataContext(connectionString);

int modifedRowsNumber = db.ExecuteCommand("DELETE FROM Users WHERE Id={0}", 40);

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

Метод ExecuteQuery() имеет похожее применение, только в этом случае он возвращает результат выполнения запроса SELECT:

DataContext db = new DataContext(connectionString);
IEnumerable<User> users = db.ExecuteQuery<User>("SELECT * FROM Users WHERE Age>{0}", 23);

Хранимые процедуры

Допустим, у нас в базе данных определена следующая хранимая процедура:

CREATE PROCEDURE [dbo].[sp_GetAgeRange]
    @name nvarchar(50)='%',
	@minAge int out,
	@maxAge int out
AS
    SELECT @minAge = MIN(Age), @maxAge = MAX(Age) FROM Users WHERE Name LIKE @name
GO

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

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

using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Reflection;

namespace L2SApp
{
    public class UserDataContext : DataContext
    {
        public UserDataContext(string connectionString) 
            :base(connectionString)
        {
            
        }
        public Table<User> Users { get { return this.GetTable<User>(); } }

        [Function(Name = "sp_GetAgeRange")]
        [return: Parameter(DbType = "Int")]
        public int GetAgeRange([Parameter(Name = "name", DbType = "NVarChar(50)")] string name,
            [Parameter(Name = "minAge", DbType = "Int")] ref int minAge,
            [Parameter(Name = "maxAge", DbType = "Int")] ref int maxAge)
        {
            IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), name, minAge, maxAge);
            minAge = ((int)(result.GetParameterValue(1)));
            maxAge = ((int)(result.GetParameterValue(2)));
            return ((int)(result.ReturnValue));
        }
    }
}

Для выполнения хранимой процедуры определяется функция GetAgeRange. И чтобы она сопоставлялась с процедурой к ней применяется атрибут [Function] с указанием имени процедуры.

Функция GetAgeRange принимает ряд параметров. И к каждому параметру принимает атрибут Parameter, который позволяет сопоставить параметр функции с параметром в хранимой процедуре. Так как параметры minAge и maxAge являются выходными, то они определяются с ключевым словом ref.

Применим эту функцию в программе:

string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=usersdb;Integrated Security=True";
            
UserDataContext db = new UserDataContext(connectionString);
int minAge=0, maxAge = 0;
string name = "Tom";
db.GetAgeRange(name, ref minAge, ref maxAge);
Console.WriteLine("Для пользователей с именем {0} минимальный возраст: {1} максимальный возраст: {2}",name, minAge, maxAge);