T-SQLの小技(メモ)

T-SQLの小技?を一つ。

MySQLでは、以下のようにSELECT文にLIMIT句を指定して、クエリ結果から指定オフセットから指定件数分取得することが可能。

SELECT ・・・ FROM TABLE_NAME ・・・ LIMIT 10,5 -- クエリ結果の5行目から10行分
-- または
SELECT ・・・ FROM TABLE_NAME ・・・ LIMIT 10 OFFSET 5 -- クエリ結果の5行目から10行分

ではT-SQLで同じ事を行なう場合はどうすれば良いのか?

昔はスマートなやり方はなかったが、SQL Server 2012からは以下の構文で取得が可能となったようだ。(但し、ORDER BY句の一部だが・・・)

-- クエリ結果の@START_OFFSET行目から@FETCH_NUMBER行分取得
SELECT COLUMN_1[,COLUMN_2[,...]] FROM TABLE_NAME
  ・・・
  ORDER BY KEY_COLUMN
  OFFSET @START_OFFSET ROWS
  FETCH NEXT @FETCH_NUMBER ROWS ONLY

※↓EFでSQL Serverに対して.Skipや.Takeを使用すると、上記と同様なSQLが作成された。

var q = ctx.TABLE_NAME.OrderBy(v=>v.KEY_COLUMN).Skip(n).Take(m);
SELECT ・・・
      FROM TABLE_NAME AS [T]
      ORDER BY [T].[KEY_COLUMN]
      OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY

これって結構使う機能だと思うのだけど、T-SQLでサポートされていたとは知らなかった(^^;

カテゴリー: T-SQL, 技術系 | 2件のコメント

Microsoft Graph Powershell Moduleを使用したライセンス付与

MSOnline PowershellモジュールのSet-MsolUserLicenseコマンドレットが非推奨になった。

使用することは未だ可能だが、スクリプトなどで連続処理を行なうと、3~4件ぐらい処理した後に、下記のエラーが発生して、次の処理を行えるまでにかなりの時間(分のオーダー)が掛かり実用的ではない。

You have exceeded the maximum number of allowable transactions. Please try again later

仕方が無いので、Microsoft Graph APIを使用した方法を試してみた。

ただ、面倒なのはライセンスを追加するにしろ削除するにしろ、Microsoft Graph APIと同じ形で、追加,削除双方のパラメータが必須なところ。

一々、手で打ち込むのは大変なので、下記のようなfunctionを作成してみた。

# MS Graphを使用してライセンスの付与
# skunameはEXCHANGESTANDARDやENTERPRISEPACK等
function global:AddLicense($user,$skuname) {
	# 追加するサブスクリプションライセンスのSkuId(GUID)を取得
	$local:sku = get-mgsubscribedsku -all | where-object {$_.SkuPartNumber -eq $skuname}
	$lic = @(
		@{
			SkuId = $sku.SkuId
			DisabledPlans = @()
		}
	)
	# UserIDを取得
	$local:uid = get-mguser -userid $user
	# ライセンス付与(-RemoveLicensesパラメータも必須-空のリストを渡す)
	set-mguserlicense -userid $uid.Id -addlicenses $lic -removelicenses @()
}
# MS Graphを使用してライセンス削除
# skunameはEXCHANGESTANDARDやENTERPRISEPACK等
function global:RemoveLicense($user,$skuname) {
	# 削除するサブスクリプションライセンスのSkuId(GUID)を取得
	$local:sku = get-mgsubscribedsku -all | where-object {$_.SkuPartNumber -eq $skuname}
	# UserIDを取得
	$local:uid = get-mguser -userid $user
	# ライセンス削除(-AddLicensesパラメータも必須-空のリストを渡す)
	set-mguserlicense -userid $uid.Id -AddLicenses @() -RemoveLicenses @($sku.SkuId)
}

ちなみに、Microsoft Graph Powershell Moduleのコマンドレットを使用する前には、Connect-MgGraphコマンドを実行しておく必要がある。

Connect-MgGraphコマンドには、-Scopesパラメータで、User.Read.All,User.ReadWrite.All,Directory.Read.All,Directory.ReadWrite.All等、コマンド実行に必要な権限を指定する必要がある。

実際に試してみて、単体では問題無く処理ができた。また、連続処理も(同じアカウントにだが)追加,削除をfor文で10回繰り返してもエラーが出なかったので、多分、問題は無いと思う。

カテゴリー: Microsoft Graph, PowerShell, 保守・運用, 技術系 | コメントする

MS Graph APIを使用したTeamsへのメッセージ投稿

今回は、MS Graph APIを使用して、TeamsのChannelへメッセージを投稿する方法について試してみた。

前提として、投稿を行うには、TeamのIDとChannelのIDが必要となる

また、メンション付きメッセージにする場合は、Channel全体へのメンションはChannelのID,個人向けのメンションはADユーザーのObject IDが必要。ユーザーの場合はUserPrincipalNameでは無いので注意が必要。

投稿には、下記の権限が必要。

  • ChannelMessage.Send
  • Group.ReadWrite.All(下位互換性のためにのみサポート)

まず、投稿用のメッセージを作成する。

var msg = new ChatMessage
{
    Subject = "テストメッセージ",
	Body = new ItemBody
	{
		// メンションありの場合はHTMLにする必要あり
        ContentType = BodyType.Html,
		Content = """
        <at id="0">メンション先</at><br/>
        !!!テストです!!!<br/>
        """,
	},
	// 重要度
    Importance = ChatMessageImportance.High,
	// メンションの対象指定
    Mentions = new List<ChatMessageMention>() {
        new ChatMessageMention() {
            Id = 0,	// <at id="0">
            MentionText = "<メンション先の表示名>",
            Mentioned = new ChatMessageMentionedIdentitySet() {
				/* Channelにメンションする場合
                Conversation = new TeamworkConversationIdentity() {
                    Id = "<Channel ID>",
                    DisplayName = "<表示名>",
                    ConversationIdentityType = TeamworkConversationIdentityType.Channel
                }
				*/
                // ユーザーにメンションする場合
                User = new Identity() {
                    DisplayName = "<表示名>",
                    Id = "<ユーザーID>",    // UserPrincipalNameではなく、ObjectIdが必要!!
                }
            }
        }
    }
};

次にメッセージを投稿する。

※メッセージの送信元はGraphにログインしたユーザーとなる。

// Chanelにメッセージ投稿
await cli.Teams[<Team ID>]
        .Channels[<Channel ID>]
        .Messages
        .PostAsync(msg);
結果

ちなみに、メンション先の指定である、ChatMessageMention中のMentionTextと本文中の
<at id="・・・">[表示名]</at>[表示名]が合っていないと、例外が発生するようだ。この[表示名]は実際のユーザーの表示名やChannel名と異なっていても、メッセージ上には[表示名]が表示される。(ChatMessageMentionedIdentitySet中のDisplayNameは無視されるようだ・・・)

カテゴリー: .NET, C#, Microsoft Graph, 技術系 | コメントする

blazorでの画面遷移方法など

最近、Blazor(Server Side)でアプリを作っていて、チョットばかり調べたものがあるので、備忘録として。

まず、リンクを使用せずに、ボタンイベント等でコードを使用して、他ページに画面遷移させる方法。

これには、NavigationManagerクラスを使用する。NavigationManagerクラスはデフォルトで、サービス登録されているので、injectして使用する。

@page "/"
・・・
@inject NavigationManager Navi
・・・
<button class="btn btn-primary" @onclick="GotoNext">次ページ</button>
@code {
	・・・
	protected void GotoNext() {
		// Nextページへ遷移
		Navi.NavigateTo("./Next");
	}
	・・・
}

お次は、BlazorコンポーネントへURLベースでパラメータを渡す方法。

URLの一部をパラメータとして渡すには、pageディレクティブを以下のように定義し、コード部でパラメータとして定義する。

@page "/Edit/{UserID?}"
・・・
@code {
	・・・
	[Parameter]
	public string? UserID { get; set; }
	・・・
}

./Edit/User001のような感じで呼び出すと、UserIDに”User001″がセットされる。

クエリパラメータをコンポーネントに渡す場合は以下のような感じ。

@page "/Edit"
・・・
@code {
	[Parameter]
	[SupplyParameterFromQuery]
	public string? UserID {get; set;}
}

./Edit?UserID=User001のような感じで呼び出す。

意外と忘れそうなので、メモしとく。

カテゴリー: .NET, asp.net core, Blazor, C#, 技術系 | コメントする

blazor serversideでのセッション変数

asp.net blazor server sideでセッション変数を使おうとして、チョット嵌ったので、メモ。

asp.net webappなどでは、Program.cs中でServiceにSessionを追加して、UseSessionメソッドを呼び出し、プログラム中からは、HttpContext.Sessionを介して、セッション変数を扱えるが、blazor server sideの場合、HttpContextにアクセスできないようなので、どうすればよいのか調べてみると、以下のような感じでセッション変数を保存,参照,削除できるようだ。

@using Microsoft.AspNetCore.Components.Server.ProtectedBrowserStorage
@inject ProtectedSessionStorage Session
・・・
// セッション変数操作(キーは当然string)
// セッション変数へ値を保存(このメソッドのValueはobject型)
await Session.SetAsync(Key,Value);

// セッション変数から値を取得
// valの型はProtectedBrowserStorageResult<T>となる
var val = await Session.GetAsync<T>(Key);
if (!val.Success) {
	// 取得失敗
}
T? v = val.Value;

// セッション変数を削除
await Session.DeleteAsync(Key);

ただ、このメソッド群はJavascriptを使用しているようで、OnInitializedAsync内で呼び出すと、例外が発生する。

初期化時にセッション変数の値を得たい場合は、OnAfterRenderAsync中に記述する必要がある。

普通にHttpContextが取れればいいのに・・・まぁ、Blazorの仕組み上難しいのかな。

カテゴリー: .NET, asp.net core, Blazor, C#, 技術系 | コメントする

MS Graphでユーザー追加とライセンス付与

またまた、MS Graphの話で申し訳ないのだが、チョット嵌った点があったので、メモ。

MS Graphを使用して、Office365のユーザーを追加するプログラムを書いてみた。MSのドキュメントにはユーザー作成時にライセンスを付与できるとも、できないとも書いていないので、一応、ライセンス付きで試してみたが、NG。(Bing AI君に聞いてみたら、「ユーザー作成時にはライセンス付与できません。」と言われた。「ドキュメントにはハッキリとは書いてありませんが・・・」だと。AI君が正しいかどうかは分からんが。)

仕方がないので、ユーザーを追加してから、ライセンスを付与する形にしてみた。

以下動作したプログラム

//
// ユーザーの作成とライセンス付与
// どうも、ユーザー作成時にライセンスを付与することはできないようだ。
//	(ドキュメントには明記されていないようだが・・・)
// 仕方ないので、ユーザーを作成して、その後、ライセンスを付与することとする。
// ちなみに、こちらもドキュメントには書かれていないが、ユーザー作成時にはUsageLocationと
// PreferredLanguageは必須!
// これがないと、ユーザーをEnableにできないし、ライセンス付与もできない。
//
var cred = new UsernamePasswordCredential(ManageUser,ManagePass,TenantID,AppID,options);
var cli = new GraphServiceClient(cred);

var request = new User() {
    UserPrincipalName = "boo@foo.com",
    MailNickname = "boo",
    AccountEnabled = true,
    DisplayName = "Boo.Foo(Mr.Boo)",
    Surname = "Foo",
    GivenName = "Boo",
    PasswordProfile = new PasswordProfile() {
        Password = MakePassword(),
        ForceChangePasswordNextSignIn = true
    },
    UsageLocation = "JP",	// 必須!
    PreferredLanguage = "ja-JP",	// 必須!
};
var res = await cli.Users.PostAsync(request);

// ユーザー追加要求後すぐにライセンス付与を行おうとするとエラーになったので、
// とりあえず、1秒程まってから、ライセンスを追加する。
Thread.Sleep(1000);
var licreq = new Microsoft.Graph.Users.Item.AssignLicense.AssignLicensePostRequestBody() {
    AddLicenses = new List<AssignedLicense>() {
        new AssignedLicense() {
            DisabledPlans = new(),  // ←これは無くても大丈夫
            SkuId = Guid.Parse("XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX")
        }
    },
    RemoveLicenses = new()  // ←削除するライセンスが無くても空配列が必要なようだ。
};
await cli.Users["boo@foo.com"].AssignLicense.PostAsync(licreq);

プログラムコメントにも書いているが、ユーザーの属性として、UsageLocationとPreferredLanguageは必須。(MSのGraph APIドキュメントには書いてない)

ライセンス付与の方だが、MSのサンプルはMe.AssignLicense.PostAsyncを使用した例のみが載っていて、ユーザー指定のライセンス付与の例は載っていなかったのだが、使用するRequestBodyのクラス名が異なるだけで、内容は同じようだ。

AddLicensesはAssignedLicenseクラスのリストで、ここに付与するライセンスを列挙する。多分、DisabledPlansは使用することはほぼないだろうと思われる。SkuIdはライセンスのSkuId(以前の記事で取得できる、SubscribedSkuクラスのメンバー)を指定する。

もちろん、複数のライセンスを付与することが可能。

ちなみに、RemoveLicenseは付与されているライセンスを削除するためのものだが、もちろん、今回は使用しない(というか削除するライセンスが無い)。削除するライセンスが無くても、空配列(C#上はListだが)を指定しないと実行時エラーとなる。(ここも嵌まった点)

後はcli.Users[UserPrincipalName].AssignLicense.PostAsync()に作成したインスタンスを渡せば、ライセンスが付与される。

ちなみに、プログラムコメントに書いたように、ユーザー作成⇒ライセンス付与をDelay無しで行なうと、実行時エラーとなってしまった。とりあえず、1秒置いてからライセンス付与をしているが、実際の所、どの程度の時間が必要なのかは不明。作成したユーザーを別スレッドで検索して検索OKまで待つような処理が必要かも。

ちなみに、必要な特権はドキュメント上は下記となる。使用しているAzureアプリには色々と特権付けているので、本当にこれだけで大丈夫か分からんが・・・

  • User.ReadWrite.All
  • Directory.ReadWriteAll

カテゴリー: .NET, C#, Microsoft Graph, 技術系 | 1件のコメント

MS Graphでメール送信

この所、MS Graphの話ばかりとなってしまっているが、今回もMS Graphのお話。

今回は、メール送信について。今まで、いくつかメール送信のプログラムを書いてきたが、大抵、MailKit.Net.Smtpを使用し作成していた。

MailKit.Net.Smtpを使用して、Office365経由でメールを送信する場合は、Access Tokenを取得後SaslMechanismOAuth2を作成して、認証するわけだが、結構面倒だ。

調べてみると、MS Graphだけでもメール送信ができるようなので、試してみた。

Azureアプリケーションに必要な権限は

  • Mail.Send

のみ。

メッセージを作成後、いつものごとく、MS Graph Clientインスタンスを作成して、Me.SendMail.PostAsyncメソッドを呼び出すだけ。

// MS Graph Clientインスタンスの作成
var cli = new GraphServiceClient(cred);
// メッセージの作成
var mesg = new Microsoft.Graph.Me.SendMail.SendMailPostRequestBody() {
    Message = new Message() {
        Subject = "メール送信テスト",
        Body = new ItemBody() {
            ContentType = BodyType.Text,
            Content = "メール送信てすとだよぅ"
        },
		// To
        ToRecipients = new List<Recipient>() {
            new Recipient() {
                EmailAddress = new EmailAddress() {
                    Address = "boo@foo.com"
                }
            }
        },
		// Cc
        CcRecipients = new List<Recipient>() {
            new Recipient() {
                EmailAddress = new EmailAddress() {
                    Address = "foo@woo.com"
                }
            }
        },
		// 添付
        Attachments = new List<Attachment>() {
            new FileAttachment() {
                Name = "hoge.png",
                ContentType = "image/png",
                ContentBytes = buff
            }
        }
    },
    SaveToSentItems = true
};
// メール送信
await cli.Me.SendMail.PostAsync(mesg);

カテゴリー: .NET, C#, Microsoft Graph, 技術系 | コメントする

Office365個人に割当てられているライセンス取得

前回の記事では、テナントに割当てられている、ライセンス一覧を取得してみたが、今回は個人に割当てられている、ライセンスを取得してみる。

取得するには、アプリケーションに下記の権限が必要。

  • User.Read
  • User.Read.All
  • User.ReadWrite.All
  • Directory.Read.All
  • Directory.ReadWrite.All

※これも何故Writeが必要なのだろう??

個人に割当てられているライセンスを取得するには、下記のようなコードを書けばよい。

// Graph Client
var cli = new GraphServiceClient(cred);

// サインインしているユーザーのライセンス取得
var lic = await cli.Me.LicenseDetails.GetAsync();

// 指定ユーザーのライセンス取得(権限が必要)
var lic = await cli.Users[UserPrincipalName].LicenseDetails.GetAsync();

List<LicenseDetails?>がlic.Valueに返される。LicenseDetailsの内容だが、必要なのはSkuPartNumberだろう。これは、前回の記事と同様の文字列である。

以下実行例

// Graph Client
var cli = new GraphServiceClient(cred);
// 指定ユーザーのライセンス取得(権限が必要)
var lic = await cli.Users[UserPrincipalName].LicenseDetails.GetAsync();
if (lic?.Value == null) {
	Console.WriteLine("取得失敗");
	return;
}

Console.WriteLine($"{UserPrincipalName}には以下のライセンスがアサインされている");
foreach(var v in lic!.Value!) {
    Console.Write($"{v.SkuPartNumber},");
}
Console.WriteLine();
/* 実行例
boo@foo.comには以下のライセンスがアサインされている
OFFICESUBSCRIPTION,EXCHANGESTANDARD,
*/
カテゴリー: .NET, C#, Microsoft Graph, 技術系 | コメントする

MS Graphでテナントのライセンスを取得

Powershell Office365モジュールでは、簡単にテナントのライセンス情報を取得できるのだが、プログラムからMS Graphで取得するにはどうすればよいか、調べてみた。

調べ方が悪いのか、あまり例がヒットしなかったが、以下のような感じで取得できることを確認した。

まず、Azure AD上のアプリケーションに必要な、アクセス許可は以下の通り。

  • Origanization.Read.All
  • Directory.ReadAll
  • Organization.ReadWrite.All
  • Directory.ReadWrite.ALl

なぜ、Writeが必要なのかは不明・・・

取得方法だが、GraphへアクセスするためのServiceClientを作成して、.SubscribedSkus.GetAsync()メソッドを呼び出すだけ。

戻り値として、契約しているライセンス情報一覧が返される。

// GraphServiceClientの作成(Credentialの作成方法は省略)
var cli = new GraphServiceClient(cred);
// 契約しているライセンス情報一覧の取得
var lic = await cli.SubscribedSkus.GetAsync();

上記の場合、lic.ValueにList<SubscribedSku>?が入ってくる。SubscribedSkuには様々な情報が入ってくるが、特によく使うのは下記ぐらいだと思われる。

SkuPartNumberライセンス名ENTERPRISEPACKのような表記
ConsumedUnits使用ライセンス数
PrepaidUnits.Enabled購入数
主なプロパティ

この情報が取れれば、どのライセンスを何ライセンス契約していて、使用数が何ライセンスか分かり、計算すれば割当可能数も取得できるので、結構便利。

※SkuPartNumberはここに一覧がある。

以下実行例

GraphServiceClient cli = new GraphServiceClient(cred);
var lic = await cli.SubscribedSkus.GetAsync();
if (lic == null) {
    Console.WriteLine("Get License Fail");
    return;
}
if (lic.Value == null) {
    Console.WriteLine("Get License Fail");
    return;
}
foreach(var itm in lic.Value) {
    int all = (int)itm.PrepaidUnits.Enabled!;
    int use = (int)itm.ConsumedUnits!;
    Console.WriteLine($"{itm.SkuPartNumber},{all},{use},{all-use}");
}
/* 実行例
STREAM,XXXX,YY,MMMM
Dynamics_365_Sales_Premium_Viral_Trial,XXXX,YY,ZZZZ
WINDOWS_STORE,XXXX,Y,ZZZZ
ENTERPRISEPACK,XX,YY,ZZ
FLOW_FREE,XXXX,YY,ZZZZ
MICROSOFT_BUSINESS_CENTER,XXXX,Y,ZZZZ
POWERAPPS_VIRAL,XXXX,Y,ZZZZ
EXCHANGESTANDARD,XXX,YY,ZZ
POWER_BI_STANDARD,XXXX,Y,ZZZZ
OFFICESUBSCRIPTION,XXX,YYY,ZZ
Power_Pages_vTrial_for_Makers,XXXX,Y,ZZZZ
STANDARDPACK,XXX,YYY,ZZ
*/
カテゴリー: .NET, C#, Microsoft Graph, 技術系 | コメントする

Javascriptを使用して数値を通貨フォーマット化

Webアプリを作っていると、UIの作りが面倒な事がままある。UIライブラリ等を使用すれば良いのだろうが、調べたり、慣れたりするまでには意外と手間が掛かってしまう。

今回はあえてJavascript標準ライブラリのみを使用して、入力した数字を通貨フォーマットに変換してみたいと思う。

試してみるのは、<input type=”number”/>のフィールドに入力中に、横に千円単位で区切られた文字列を表示すること。

ちょっとググって(と言っても、bingなのだが・・・)みると、いくつかの方法が出て来た。その中で、1番使い勝手が良さそうなものをチョイスした。

以下のような感じで入力値を通貨形式に変換できるようだ。

var formattedval = Intl.NumberFormat('ja-JP', {style: 'currency', currency: 'JPY', minimumFractionDigits: 0}).format(val);

パラメータにはロケールと変換オプションを指定する。
ロケールはja-JPやen-US等の文字列。
オプションは

  • style
    • ‘decimal’ 10進数
    • ‘currency’ 通貨表示
    • ‘percent’ パーセント
  • currency 通貨の種類’JPY’,’USD’など
  • minimumFractionDigits 小数点以下の表示桁数

これを使用して、入力した値をリアルタイムで表示するようにコードを書いてみた。

<form>
    金額:
    <input type="number" style="text-align:right" id="Price"/>  
    <span style="font-weight:bold" id="FormattedPrice"></span><br/>
</form>
<script>
    $(document).ready(
        ()=>{
            $('#Price').on("input",
                (e)=>{
                    if (e.target.value.length != 0) {
                        var val = parseInt(e.target.value);
                        var formattedval = Intl.NumberFormat('ja-JP', {style: 'currency', currency: 'JPY', minimumFractionDigits: 0}).format(val);Intl.NumberFormat('ja-JP', {style: 'decimal', minimumFractionDigits: 0}).format(val);
                        $('#FormattedPrice').text(formattedval);
                    } else {
                        $('#FormattedPrice').text('');
                    }
                }
            );
        }
    );
</script>

実行結果はこんな感じ

何かに使えそうなのでメモ。

カテゴリー: javascript, Web, 技術系 | コメントする