WebSurfer's Home

トップ > Blog 1   |   Login
Filter by APML

Multiple Active Result Sets (MARS)

by WebSurfer 15. September 2022 19:24

SQL Server の Multiple Active Result Sets (MARS) というのは何で、どのような場合に必要かという話を書きます。

MARS は SQL Server 2005 以降で利用できる機能で、Microsoft のドキュメント「複数のアクティブな結果セットの有効化」によると "複数のバッチを単一の接続で実行することができます" ということです。(分かりやすい説明を下の方に追記)

接続文字列で MultipleActiveResultSets を true に設定することにより利用できます。

.NET Framework 2.0 からサポートされていたのですが、昔はそのような設定を見かけることは無かったです。それが ADO.NET Entity Data Model ウィザードで自動生成される接続文字列などに設定されるのを見かけて、なぜだろうと疑問に思っていました。

接続文字列で MultipleActiveResultSets=True として「複数のアクティブな結果セットの有効化」を行った例を以下に書きます。

string connString1 = "Data Source=lpc:(local)\\sqlexpress;" +
                     "Initial Catalog=NORTHWIND;" +
                     "Integrated Security=True;" +
                     "MultipleActiveResultSets=True";

string query1 = "SELECT CategoryID, CategoryName FROM Categories;";
string query2 = "SELECT ProductID, ProductName FROM Products;";

using (var connection = new SqlConnection(connString1))
{
    connection.Open();
    using (var command = new SqlCommand(query1, connection))
    {
        SqlDataReader reader = command.ExecuteReader();

        while (reader.Read())
        {
            Console.WriteLine("\t{0}\t{1}",
                reader.GetInt32(0), reader.GetString(1));
        }
    }

    using (var command = new SqlCommand(query2, connection))
    {
        SqlDataReader reader = command.ExecuteReader();

        while (reader.Read())
        {
            Console.WriteLine("\t{0}\t{1}",
                reader.GetInt32(0), reader.GetString(1));
        }
    }
}

MARS はデフォルトでは無効になっており、その場合はアプリケーションはバッチごとにすべての結果セットを処理またはキャンセルしないと、同じ接続で他のバッチを実行できません。

どういうことかと言うと、接続文字列の MultipleActiveResultSets=True を削除すると 2 つ目の command.ExecuteReader() で以下のように例外がスローされます。

DataReader エラー

上のコードでは、MARS の設定なしでも、DataReader を閉じるコードを書けば例外は回避できるのですが、Entity Framework が問題です。

例えば、Entity Framework の「遅延読み込み」を行ったりすると、MARS の設定無しでは以下の画像のように例外がスローされます。

遅延読み込み時のエラー

ADO.NET Entity Data Model ウィザードなどで自動生成される接続文字列に MARS の設定が含まれているのはそういう理由であろうと思われます。


2022/9/28 追記

MSDN ライブラリ Visual Studio 2008 の SqlCommand.ExecuteReader メソッドの解説に MARS の分かりやすい説明があるのを見つけましたので以下に書いておきます。

"SQL Server 2005 より前のバージョンの SQL Server の場合、SqlDataReader が使用されている間、関連付けられている SqlConnection は SqlDataReader によって使用されるため、ビジー状態になります。この状態では、SqlConnection に対して、閉じる以外の操作を実行できません。SqlDataReader の Close メソッドを呼び出すまでこの状態が続きます。SQL Server 2005 では、MARS (Multiple Active Result Set) 機能がサポートされ、同一接続を使用して複数の処理を実行できるようになりました"

Tags: , ,

ADO.NET

SQL Server オブジェクトエクスプローラー

by WebSurfer 13. August 2022 14:09

自分の環境の Visual Studio 2022 では下の画像のように SQL Server オブジェクトエクスプローラーが表示されていますが、その中の各項目が何かを調べたので備忘録として書いておきます。

SQL Server オブジェクトエクスプローラー

まず、SQL Server オブジェクト エクスプローラーとは何かですが、CodeZin の記事 使わなきゃ損! SQL Serverの新たな開発ツール「SQL Server Data Tools」によると SQL Server Data Tools (SSDT) の機能で、"開発者がSQL Server Management Studioで実施していたテーブルの作成や変更などのデータベース関連タスクを、Visual Studioで完結することを目的としています" というものだそうです。

Visual Studio 2022 に SSDT をインストールするには、Microsoft のドキュメント SQL Server Data Tools (SSDT) for Visual Studio のダウンロードに書いてあるように、ワークロードの「データの保存と処理」を追加し、そのオプションの「SQL Server Data Tools」にチェックを入れます。

そして、Visual Studio 2022 のメニューバーから[表示(V)]⇒[SQL Server オブジェクト エクスプローラー(S)]をクリックすれば SQL Server オブジェクトエクスプローラーが表示されます。

SQL Server オブジェクトエクスプローラーを表示

前置きが長くなりましたが、上の SQL Server オブジェクトエクスプローラーの画像に表示されている各項目が何かを説明します。

(1) (local)\sqlexpress (SQL Server 11.0.2100 ...)

ローカルにインストールした SQL Server 2012 Express の名前付きインスタンスです。

SQL Server の Express 版をインストールすると、デフォルトでは「名前つきインスタンス」となり、インスタンス名は SQLEXPRESS になります。

(記憶にないですが、たぶん、自分で SQL Server オブジェクト エクスプローラーを操作して追加したものだと思います)

(2) (localdb)\MSSQLLocalDB (SQL Server 13.0.4001 ...)

SQL Server 2016 LocalDB の自動インスタンスです。13.0.4001 から SQL Server 2016 ベースであることが分かります。

他に名前付きインスタンスというのもあります。詳しくは Microsoft のドキュメント SQL Server Express LocalDB を見てください。

自動インスタンスとは SQL Server の既定のインスタンスに該当するもののようで、開発時にはそれに接続して使うようにします。

Microsoft のドキュメントに書いてある通り、自動インスタンスの名前は MSSQLLocalDB になります。(SQL Server 2014 で変更されたそうです。その前は、文字 v の後に LocalDB とバージョン番号を付けたものでした)

Visual Studio 2019 では SQL Server 2016 LocalDB が、Visual Studio 2022 では SQL Server 2019 LocalDB が一緒にインストールされます。

自分の PC には Visual Studio 2019 / 2022 両方をインストールしており、一緒にインストールされた LocalDB は以下のようになっています。(一番上の SQL Server 2014 Express LocalDB は Visual Studio 2015 と一緒にインストールされたもの) 

インストールされている LocalDB

それなのに、なぜ Visual Studio 2022 の SQL Server オブジェクトエクスプローラーに表示されている自動インスタンスが SQL Server 2019 LocalDB のものではないのでしょう? それはたぶん以下のような話ではないかと思います。

上に紹介した Microsoft のドキュメントに以下のように書いてあります。

"ユーザーのコンピューターにインストールされているどのバージョンの LocalDB についても、LocalDB の自動インスタンスが 1 つ存在します"(PC 内に複数の自動インスタンスは存在しないということ)

"あるコンピューター上でユーザーが初めて LocalDB への接続を試みるときは、自動インスタンスを作成し、なおかつ開始する必要があります"

ということで、先に Visual Studio 2019 で作業したとき SQL Server 2016 LocalDB で自動インスタンスが作成され、Visual Studio 2022 でも先に作成された自動インスタンスがそのまま使われているということだと思います。

その自動インスタンスを、コマンド ライン管理ツール: SqlLocalDB.exe を使って SQL Server 2019 LocalDB にアップグレードすることはできるようです。

ググって調べると、Upgrade Visual Studio 2019’s LocalDB to SQL 2019 とか Upgrading SQL Server LocalDb などの方法を書いた記事がヒットします。

その方法というのは、(a) 既存の自動インスタンスを削除、(b) 新たに SQL Server 2019 LocalDB の自動インスタンスを作成、(c) 既存のデーターベースを新たに作成した自動インスタンスにアタッチする・・・ということになるようです。

上の (c) にリスクがありそうです。SQL Server 2019 LocalDB の自動インスタンスが必須というわけではない現状では、アップグレードには手を出さない方が良さそうな感じです。

(3) (localdb)\ProjectModels (SQL Server 15.0.4153 ...)

これは自分で作った記憶がなくて、Visual Studio 2022 で SSDT 関係の操作をしたとき自動的に作られたもののようです。

Microsoft の Developer Community の記事 (localdb)\ProjectsV13 not setup when installing Visual Studio 2022 に説明がありました。

自分の環境では、Visual Studio 2019 で、一緒にインストールされた SQL Server 2016 LocalDB をベースに、(localdb)\ProjectsV13 という名前のインスタンスが作られて、それがそのまま残っている。その後 Visual Studio 2022 を使うようになって、一緒にインストールされた SQL Server 2019 LocalDB をベースに (localdb)\ProjectModels という名前のインスタンスが作られたということのようです。

Visual Studio にインストールした SSDT が使うもので、開発者がアプリで使うものではないようです。

(4) (localdb)\ProjectsV13 (SQL Server 13.0.4001 ...)

上にも書きましたが、Visual Studio 2019 で一緒にインストールされた SQL Server 2016 LocalDB をベースに (localdb)\ProjectsV13 という名前のインスタンスが作られたようです。

一体それは何かですが、Stsckoverflow の記事 Purpose of ProjectsV13 LocalDB instance に以下のように書いてありました。

"The primary reason is to avoid conflicts with any "production" databases on MSSQLLocalDB. SSDT creates a new database for every database project you open. If your project is called Adventureworks, this might conflict with an Adventureworks database created by web projects or that are used by local ASP.NET applications you are running / debugging. Since SSDT does this automatically, in the background, it was felt that there was too high a risk of conflict. Hence, a separate instance is used."

上の (3) の (localdb)\ProjectModels も同じだと思います。

Tags: , , ,

DevelopmentTools

null 許容許容参照型と EF Code First

by WebSurfer 12. May 2022 15:15

Visual Studio 2022 のテンプレートを使って .NET 6.0 アプリのプロジェクトを作ると、デフォルトで「Null 許容」オプションが有効にされています。その状態での EF Code First による SQL Server データベース生成に新発見 (自分が知らなかっただけですが) があったので備忘録として書いておきます。

「Null 許容」オプション

EF Code First でデータベースを生成すると、元となるコードのクラス定義の中のプロパティの型と付与する属性によって生成されるデータベースの列の型と NULL 可/不可が決まってきます。新発見というのは「Null 許容」オプションの有効化によって、生成される列の NULL 可/不可が以前と違ってくるということです。

値型の場合は「Null 許容」オプションの有効/無効は関係なく結果は以前と同じになります。すなわちデフォルトでデータベースの当該列は NULL 不可になります。NULL 可にしたい場合は Nullable<T> 型(例えば int? とか DateTime? など)をプロパティの型に使います。

違うのは参照型の場合です。「Null 許容」オプションが有効にされていると、例えばプロパティの型を string とすると当該データベースの列は NULL 不可に、string? とすると NULL 可になります。

以前 (null 許容参照型が使えない時代または「Null 許容」オプションが無効) は string 型は NULL 可になりました。NULL 不可にしたい場合は当該プロパティに RequiredAttribute 属性を付与していました。

実際にアプリを作って試してみましたので具体例を以下に書きます。

Visual Studio 2022 のテンプレートでフレームワークを .NET 6.0 としてコンソールアプリを作成します。その状態で上の画像のように「Null 許容」オプションが有効化されています。

NuGet パッケージ Microsoft.EntityFrameworkCore.SqlServer と Microsoft.EntityFrameworkCore.Tool をインストールします。前者は SQL Server 用の EF Core 本体、後者は Migration 操作を行うためのツールです。

NuGet パッケージ

Microsoft のドキュメント「新しいデータベースの Code First」と同様なコンテキストクラスとエンティティクラスを実装します。コードは以下の通りです。(null 許容参照型対応のため = null! を追加するなどしていますが基本は同じ)

public class Blog
{
    public int BlogId { get; set; }
    public string Name { get; set; } = null!;

    public virtual List<Post>? Posts { get; set; }
}

public class Post
{
    public int PostId { get; set; }
    public string Title { get; set; } = null!;

    public string? Content { get; set; }

    public int BlogId { get; set; }
    public virtual Blog? Blog { get; set; }
}

public class BloggingContext : DbContext
{
    public DbSet<Blog> Blogs { get; set; } = null!;
    public DbSet<Post> Posts { get; set; } = null!;

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseSqlServer("接続文字列");
        }
    }
}

上のコードの Blog クラスの Name プロパティ、Post クラスの Title, Content プロパティの型を string / string? と使い分けている点に注目してください。これをベースに Migration 操作によって SQL Server にデータベースを生成した結果が以下の画像です。

SQL Server にデータベース生成

プロパティが string 型になっている Name, Title プロパティに対応する SQL Server データベースの Name 列と Title 列は NULL 不可に、string? 型になっている Content プロパティに対応するデータベースの Content 列は NULL 可になっています。

以前 (null 許容参照型が使えない時代または「Null 許容」オプションが無効) は、上にも書きましたが、プロパティの型が参照型の場合はデータベースの当該列は NULL 可になります。NULL 不可にする場合は RequiredAttribute 属性を付与します。試しに、以下のように #nullable disable を付与したクラス定義を追加し、Migration 操作で SQL Server に Products テーブルを生成してみました。

#nullable disable
public class Product
{
    public int ProductId { get; set; }

    [Required]
    public string ProductName { get; set; }

    public string Decription { get; set; }

    [Column(TypeName = "decimal(18,4)")]
    public decimal UnitPrice { get; set; }
}

結果は以下の通りです。プロパティの型が string の ProductName, Description に該当する列の NULL 可/不可を見てください。プロパティに RequiredAttribute 属性を付与しないと NULL 可になります。

#nullable disable で生成

既存のデータベースからリバースエンジニアリングで生成したコンテキストクラス、エンティティクラスも「Null 許容」オプションが有効化されている場合は上の Blog, Post クラスと同様になります。

試しに Microsoft のサンプルデータベース Northwind の Categories テーブルからリバースエンジニアリングでコンテキストクラス、エンティティクラスを生成してみました。Categories テーブルは以下の内容になっています。CategoryName 列が NULL 不可、Description 列が NULL 可になっているところに注目してください。

Northwind の Categories テーブル

上の Categories テーブルからリバースエンジニアリングを使ってデータアノテーション属性を含めてエンティティクラスを生成すると以下の通りとなります。データベースのテーブルの各列の NULL 可/不可と、生成されたクラス定義の各プロパティの型を見てください。データベースの列が NULL 可の場合はプロパティの型は null 許容(? を付与されている)となっています。

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Microsoft.EntityFrameworkCore;

namespace MvcCore6App2.Models
{
    [Index("CategoryName", Name = "CategoryName")]
    public partial class Category
    {
        public Category()
        {
            Products = new HashSet<Product>();
        }

        [Key]
        [Column("CategoryID")]
        public int CategoryId { get; set; }
        [StringLength(15)]
        public string CategoryName { get; set; } = null!;
        [Column(TypeName = "ntext")]
        public string? Description { get; set; }
        [Column(TypeName = "image")]
        public byte[]? Picture { get; set; }

        [InverseProperty("Category")]
        public virtual ICollection<Product> Products { get; set; }
    }
}

この記事の本題の話は以上ですが、このクラス/プロパティ定義をそのまま ASP.NET MVC の Model として使った場合、ユーザー入力の検証がどうなるのかが気になります。それも調べましたので以下に書いておきます。

以前は string 型のプロパティに該当するテキストボックスへのユーザー入力を必須とする場合、その項目の当該プロパティに RequiredAttribute 属性を付与していました。それにより未入力の場合は検証 NG となってエラーメッセージが表示されます。

「Null 許容」オプションが有効化されている場合、string 型の項目は必須入力になるはずですが、上のコードではプロパティには RequiredAttribute 属性は付与されていません。そこはどうなるのかが疑問でした。

実際にアプリを動かして試してみると、RequiredAttribute 属性は付与されてなくても、未入力の場合は検証 NG となってエラーメッセージが表示されました。

View から生成される html ソースを調べてみると、当該 input 要素には data-val-required="The xxx field is required." という検証属性が付与され、未入力の場合は検証機能が働いてエラーメッセージが表示されるようになっていました。

エラーメッセージを任意のものに変えたい場合は、プロパティに RequiredAttribute を付与して ErrorMessage にメッセージを設定します。そうすると data-val-required 属性に設定される文字列が ErrorMessage に設定したものに置き換わります。

このあたりは先の記事「int 型プロパティの検証、エラーメッセージ」に書いた int 型の場合と同じになっているようです。

最後にもう一つ、こんなことをする人はいないかもしれませんが、string? 型のプロパティ(null 可)に RequiredAttribute 属性を付与(null 不可)するとどうなるかを書いておきます。

そのような設定をすると、EF Core を使って SQL Server からデータを取得する際当該列のデータに NULL が含まれていると、

System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.

・・・という例外がスローされます。下の画像を見てください。

SqlNullValueException

Microsoft のドキュメントによると、SqlNullValueException は「System.Data.SqlTypes 構造体の Value プロパティが null に設定されている場合にスローされる例外」ということだそうです。

メカニズムは不明ですが、string? 型のプロパティに RequiredAttribute 属性を付与し EF Core で SQL Server からデータを取得してくるときに、データに NULL があると「SqlTypes 構造体の Value プロパティが null に設定」ということになるようです。

SqlNullValueException の説明と RequiredAttribute 属性を設定したことが結びつかなくて、解決に悩んで無駄な時間を費やすことになるかもしれませんので注意してください。(実は自分がそうでした)

Tags: , , ,

ADO.NET

About this blog

2010年5月にこのブログを立ち上げました。その後ブログ2を追加し、ここはプログラミング関係、ブログ2はそれ以外のトピックスに分けました。

Calendar

<<  December 2022  >>
MoTuWeThFrSaSu
2829301234
567891011
12131415161718
19202122232425
2627282930311
2345678

View posts in large calendar