VB.NET-TIPS などプログラミングについて

システムに必要なテーブルで、自動的に番号を振っていくものが必要なときがあります。 たとえば、各種の伝票データの伝票番号の様なものです。

プログラム処理上、データを登録した直後に、自動採番された値を取得し何かに使いたいことがよくあります。
SQL-Serverでは現在の自動採番の値を取得する方法が用意されています。

取敢えず自動採番を行える簡単なテーブルの例を示します。（前回ストアド・ファンクションで使用したテーブルに細工します。）

CREATE TABLE [dbo].[TABLE_B](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[DATA1] [nvarchar](50) COLLATE Japanese_CI_AS NULL,
	[DATA2] [nvarchar](50) COLLATE Japanese_CI_AS NULL,
 CONSTRAINT [PKEY_TW_MAG_ADDR] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

まず、テストテーブルＢに1件データを挿入します。 「ID」は自動採番の設定なので登録する値は設定しません。

INSERT INTO TABLE_B (DATA1, DATA2)
VALUES('A0001', 'B0001')

■自動採番された値を取得
この直後に、設定された「ID」値を取得するには以下のSELECT文を実行します。

SELECT IDENT_CURRENT('TABLE_B') AS LASTID

結果として「１」という値が返されます。

■自動採番された値をリセット
デバッグ途中でテーブルのデータを全て削除して、さらに自動採番が「１」からにしたい場合はよくあります。 そのときに以下の命令をクエリアナライザ等で実行します。

DBCC CHECKIDENT('TABLE_B', RESEED, 0)

自動採番の値を「０」にする命令ですが、実際INSERT実行時には＋１された値が「ID」に設定されます。

=====
2013/02/14：の時の情報

当然といえばそうなのかもしれませんが、SQL-Serverのストアド・ファンクションではUPDATEなどが実行できない。
TRY・・・CATCHも記述できないようです。関数は戻り値のみを返すもので、テーブル等に変更を与えてはいけないのでしょう。

さて、取敢えずストアド・ファンクションの簡単な例を示します。

前回使用したテストテーブルを利用して、ストアド・ファンクションを作成します。構造は以下の感じです。

CREATE TABLE [dbo].[TABLE_A](
	[ID] [int] NOT NULL,
	[DATA1] [nvarchar](50) COLLATE Japanese_CI_AS NULL,
	[DATA2] [nvarchar](50) COLLATE Japanese_CI_AS NULL,
 CONSTRAINT [PK_TABLE_A] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

■スカラ値関数

テストテーブルＡを利用した簡単な値を返すストアド・関数です。
与えられた検索用ＩＤでテーブルＡの「DATA1」カラムの内容を返す簡単な関数です。

IF OBJECT_ID('dbo.FuncTest2') IS NOT NULL
    DROP FUNCTION [dbo].[FuncTest2]
GO
CREATE FUNCTION [dbo].[FuncTest2](
  @SrcID NVARCHAR
)
RETURNS NVARCHAR(50)
BEGIN
    DECLARE @Des NVARCHAR(50)
    SELECT @Des = DATA1	FROM TABLE_A WHERE ID = @SrcID
    RETURN @Des
END

SELECT文で実行してやれば、以下のようにします。
======================================
SELECT [dbo].[FuncTest2](1)
======================================


■テーブル値関数その１（インラインテーブル値）

テストテーブルＡを利用した簡単な値を返すストアド・関数です。

IF OBJECT_ID('dbo.FuncTest3') IS NOT NULL
    DROP FUNCTION [dbo].[FuncTest3]
GO
CREATE FUNCTION [dbo].[FuncTest3](
  @SrcID NVARCHAR
)
RETURNS TABLE AS
RETURN
(
    SELECT ID, DATA1 FROM TABLE_A WHERE ID = @SrcID
)

SELECT文のFROM句で実行してやれば、以下のようにします。
======================================
SELECT * FROM [dbo].[FuncTest3](1)
======================================


■テーブル値関数その２（複数の行を返すテーブル値）

テストテーブルＡを利用した複数の行を返すストアド・関数です。
指定されたID以降のデータを全て返す様な動作を行います。
RETURNS句で指定されたTABLEにINSERTしていくことで、結果をテーブルの値として返します。

IF OBJECT_ID('dbo.FuncTest4') IS NOT NULL
    DROP FUNCTION [dbo].[FuncTest4]
GO
CREATE FUNCTION [dbo].[FuncTest4]( @SrcID NVARCHAR )
RETURNS @DesTbl TABLE 
(
    ID int primary key NOT NULL,
    DATA1 nvarchar(255) NOT NULL
)
AS
BEGIN
	INSERT @DesTbl
	SELECT ID, LTRIM( DATA1 ) FROM TABLE_A
	WHERE ID >= @SrcID
	/* INSERT @DesTbl VALUES(0, '0000') */
	RETURN
END

INSERTで追加してやればいろんなことができると思います。上の例ではコメント行になっていますが、 強制的にID「０」を作成することも可能です。
この関数の実行も「テーブル値関数その１」で行った方法と同様です。
■スカラ値関数にUPDATE文を記述

IF OBJECT_ID('dbo.FuncTest2') IS NOT NULL
    DROP FUNCTION [dbo].[FuncTest2]
GO
CREATE FUNCTION [dbo].[FuncTest2](
  @SrcID NVARCHAR
)
RETURNS NVARCHAR(50)
BEGIN
    DECLARE @Des NVARCHAR(50)
    SELECT @Des = DATA1	FROM TABLE_A WHERE ID = @SrcID
	UPDATE TABLE_A SET DATA2 = NULL WHERE ID = @SrcID
    RETURN @Des
END

SQL実行結果として以下のエラーメッセージが表示されます。
============================================================
メッセージ 443、レベル 16、状態 15、プロシージャ FuncTest2、行 8
副作用のある演算子または時間に依存する演算子を関数内の 'UPDATE' で使用することはできません。
============================================================


TRY、CATCHを入れてもエラーが表示され使用出来ないようです。

IF OBJECT_ID('dbo.FuncTest2') IS NOT NULL
    DROP FUNCTION [dbo].[FuncTest2]
GO
CREATE FUNCTION [dbo].[FuncTest2](
  @SrcID NVARCHAR
)
RETURNS NVARCHAR(50)
BEGIN
	BEGIN TRY
    DECLARE @Des NVARCHAR(50)
    SELECT @Des = DATA1	FROM TABLE_A WHERE ID = @SrcID
	END TRY
	BEGIN CATCH
	END CATCH
    RETURN @Des
END

SQL実行結果として以下のエラーメッセージが表示されます。
============================================================
メッセージ 443、レベル 16、状態 14、プロシージャ FuncTest2、行 6
副作用のある演算子または時間に依存する演算子を関数内の 'BEGIN TRY' で使用することはできません。
メッセージ 443、レベル 16、状態 14、プロシージャ FuncTest2、行 9
副作用のある演算子または時間に依存する演算子を関数内の 'END TRY' で使用することはできません。
メッセージ 443、レベル 16、状態 14、プロシージャ FuncTest2、行 10
副作用のある演算子または時間に依存する演算子を関数内の 'BEGIN CATCH' で使用することはできません。
メッセージ 443、レベル 16、状態 14、プロシージャ FuncTest2、行 11
副作用のある演算子または時間に依存する演算子を関数内の 'END CATCH' で使用することはできません。
============================================================


副作用のある処理をファンクションのなかでは記述できないので、 どうしても処理したいのであれば、ストアド・プロシージャとして作成するしかないようです。
そこで、結果として何かを返したいのであれば、引数に出力属性のものを持つしかありません。

また、プロシージャの中からファンクションを実行することはできますが、ファンクションの中から プロシージャを実行することはできないようです。
ファンクションのコールが入れ子になっていて、深いところでUPDATEなどを行いたい場合、 全てのファンクションをプロシージャにしないといけなくなってきます。
このあたりは、ファンクション、プロシージャの切り分けをしっかりしておく必要がありそうです。
=====
2013/02/14：の時の情報

SQL-Serverのテーブルの内容を簡単にインポート・エキスポートできる、ユーティリティ(BCP)の忘備録です。

BCPはコマンドラインで使用するEXEファイルで、MSSQLをインストールした時点でユーティリティとして "C:\Program Files\Microsoft SQL Server\90\Tools\Binn"の様なディレクトリに存在するはずです。
パスも通っているはずですから、DOS窓から「BCP」を実行すれば動作するはずです。
（引数無しで実行するとヘルプが表示されます。以下を参照）

C:\>bcp
使用法: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m 最大エラー数]        [-f フォーマット ファイル]   [-e エラー ファイル]
  [-F 先頭行]              [-L 最終行]                  [-b バッチ サイズ]
  [-n ネイティブ型]        [-c 文字型]                  [-w UNICODE 文字型]
  [-N text 以外のネイティブ型を保持] [-V ファイル フォーマットのバージョン] [-q
引用符で囲まれた識別子]
  [-C コード ページ指定子] [-t フィールド ターミネータ] [-r 行ターミネータ]
  [-i 入力ファイル]        [-o 出力ファイル]            [-a パケット サイズ]
  [-S サーバー名]          [-U ユーザー名]              [-P パスワード]
  [-T 信頼関係接続]        [-v バージョン]              [-R 地域別設定有効]
  [-k NULL 値を保持]       [-E ID 値を保持]
  [-h "読み込みヒント"]    [-x XML フォーマット ファイルを生成]

各種の引数があって、いろいろな使い方ができますが、私が基本的に使っている方法を記します。
尚、実行はMSSQLがインストールされているサーバ上で実行しています。
■テーブルをテキストファイルに出力する

テスト用のデータベースである「TEST」と、そのDB内のテスト用テーブル「TABLE_A」の内容全てを テキストファイル「table_a.txt」に出力する方法です。

C:\>bcp TEST.dbo.TABLE_A out "table_a.txt" -c -S localhost\SQLEXPRESS -T

コピーを開始しています...

2 行コピーされました。
ネットワーク パケット サイズ (バイト): 4096
クロック タイム (ミリ秒) 合計     : 1      平均 : (2000.00 行/秒)

出力結果はテーブルのカラムの区切り記号としてタブ文字が使われます。また、文字コードはShift-JISです。
（尚、実行はCドライブのルートで実行しています。） 最後の引数の「-T」は、Windows認証の指定ですが、SQL Server認証の場合は「-T」の代わりに「-U」「-P」を用います。
================================================================================
-U [ユーザ名] -P [パスワード]
================================================================================

■SQLの結果をテキストファイルに出力する

上記の説明で使用したテーブル「TABLE_A」を対象としたSQL文の結果をテキストファイル「table_b.txt」に出力する方法です。

C:\>bcp "SELECT * FROM TEST.dbo.TABLE_A ORDER BY ID" queryout "table_b.txt" -c -Slocalhost\SQLEXPRESS -T

コピーを開始しています...

2 行コピーされました。
ネットワーク パケット サイズ (バイト): 4096
クロック タイム (ミリ秒) 合計     : 1      平均 : (2000.00 行/秒)

最初のコマンドの引数と異なるところは、SQL文を指定するところと、処理区分を「queryout」とすることです。
出力結果は、「table_a.txt」とほぼ同じものが出力されます。
■テーブルにテキストファイルをインポートする

上記の説明で使用したテーブル「TABLE_A」にテキストファイル「table_c.txt」の内容をインポートする方法です。

C:\>bcp TEST.dbo.TABLE_A in "table_c.txt" -c -Slocalhost\SQLEXPRESS -T

コピーを開始しています...

2 行コピーされました。
ネットワーク パケット サイズ (バイト): 4096
クロック タイム (ミリ秒) 合計     : 63     平均 : (31.75 行/秒)

テキストファイルの内容は、インポートされるテーブルの構造と同じである必要があります。
また、キーの重複があればエラーが発生し、全てのインポート処理は破棄されます。

C:\>bcp TEST.dbo.TABLE_A in "table_c.txt" -c -Slocalhost\SQLEXPRESS -T

コピーを開始しています...
SQLState = 23000, NativeError = 2627
Error = [Microsoft][SQL Native Client][SQL Server]制約 'PK_TABLE_A' の PRIMARY K
EY 違反。オブジェクト 'dbo.TABLE_A' には重複したキーを挿入できません。
SQLState = 01000, NativeError = 3621
Warning = [Microsoft][SQL Native Client][SQL Server]ステートメントは終了されまし
た。

BCP コピー in が失敗しました

=====
2013/02/12：の時の情報

前回のSQL-Serverの関数およびプロシージャの生成のバッチファイルでの実行方法の忘備録です。

ホストがローカルホストでインスタンス名がSQLEXPRESSでwindows認証の場合、 以下のコマンドを記述したバッチをサーバー上で実行します。
「test.bat」

sqlcmd -S .\SQLEXPRESS -i ProcTest.sql
sqlcmd -S .\SQLEXPRESS -i FuncTest.sql

「-S」：サーバーの接続先
「-i」：入力ファイルの指定
（この例ではバッチファイルとSQLファイルは同じフォルダに在ります。）

バッチの実行結果です。

C:\test>sqlcmd -S .\SQLEXPRESS -i ProcTest.sql
データベース コンテキストが 'TEST' に変更されました。

C:\test>sqlcmd -S .\SQLEXPRESS -i FuncTest.sql
データベース コンテキストが 'TEST' に変更されました。

「ProcTest.sql」

USE [TEST]
GO
IF OBJECT_ID('dbo.ProcTest') IS NOT NULL
    DROP PROCEDURE [dbo].[ProcTest]
GO
-----------------------------------------
--  階乗を計算する(プロシージャ)
-----------------------------------------
CREATE PROCEDURE [dbo].[ProcTest]
  @SrcNumber    DECIMAL         --引数
 ,@DesNumber    DECIMAL OUTPUT  --戻り値
AS
BEGIN
    SET @DesNumber = 1

    WHILE @SrcNumber > 0
    BEGIN
        SET @DesNumber = @DesNumber * @SrcNumber
        SET @SrcNumber = @SrcNumber - 1
    END
END

「FuncTest.sql」

USE [TEST]
GO
IF OBJECT_ID('dbo.FuncTest') IS NOT NULL
    DROP FUNCTION [dbo].[FuncTest]
GO
-----------------------------------------
--  階乗を計算する(関数)
-----------------------------------------
CREATE FUNCTION [dbo].[FuncTest](
  @SrcNumber DECIMAL    --引数
)
RETURNS DECIMAL
BEGIN
    DECLARE @DesNumber   DECIMAL
    SET @DesNumber = 1

    WHILE @SrcNumber > 0
    BEGIN
        SET @DesNumber = @DesNumber * @SrcNumber
        SET @SrcNumber = @SrcNumber - 1
    END
    RETURN @DesNumber
END

データベース名を「TEST」として実行しています。
尚、関数・プロシージャとも内容の変更が有った場合を想定して、DROP命令で最初に削除しています。
=====
2012/06/13：の時の情報

最近、VB.NETでのSQL-Serverの関数およびプロシージャの実行をやったのでその忘備録です。

先ず、簡単な関数およびプロシージャをSQL-Serverに登録します。
FUNCTION、PROCEDURE共に階乗の計算を行います。VB.NETからの実行例ですので、 結果が桁数を超えるなどの制限は取り敢えず考えていません。
以下がそれぞれの内容です。

-----------------------------------------
--  階乗を計算する(関数)
-----------------------------------------
CREATE FUNCTION [dbo].[FuncTest](
  @SrcNumber DECIMAL    --引数
)
RETURNS DECIMAL
BEGIN
    DECLARE @DesNumber   DECIMAL
    SET @DesNumber = 1

    WHILE @SrcNumber > 0
    BEGIN
        SET @DesNumber = @DesNumber * @SrcNumber
        SET @SrcNumber = @SrcNumber - 1
    END
    RETURN @DesNumber
END

-----------------------------------------
--  階乗を計算する(プロシージャ)
-----------------------------------------
CREATE PROCEDURE [dbo].[ProcTest]
  @SrcNumber    DECIMAL         --引数
 ,@DesNumber    DECIMAL OUTPUT  --戻り値
AS
BEGIN
    SET @DesNumber = 1

    WHILE @SrcNumber > 0
    BEGIN
        SET @DesNumber = @DesNumber * @SrcNumber
        SET @SrcNumber = @SrcNumber - 1
    END
END

VB.NETでの実行は以下の様になります。関数の実行および、プロシージャの実行を別の関数にしてあります。

'関数の実行
Private Sub TestFunc()
    'コネクション・接続文字列
    Dim Con As New System.Data.SqlClient.SqlConnection( _
        "Data Source=(local)\SQLEXPRESS;" & _
        "Initial Catalog=TEST;Integrated Security=SSPI")
    Try
        'コネクションオープン
        Con.Open()
        '取得関数SQL
        Dim SQL As String = "SELECT [dbo].[FuncTest](@SrcNumber)"
        'SQLコマンド生成(コネクション指定)
        Dim sqlCommand As New System.Data.SqlClient.SqlCommand(SQL, Con)
        'SQLコマンドへ引数設定
        sqlCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@SrcNumber", 10))
        'SQLコマンド実行
        Dim obj As Object = sqlCommand.ExecuteScalar
        Console.Write(CDec(obj))
    Finally
        'コネクション破棄
        If Con.State <> System.Data.ConnectionState.Closed Then
            Con.Close()
            Con.Dispose()
        End If
    End Try
End Sub

'プロシージャの実行
Private Sub TestProc()
    'コネクション・接続文字列
    Dim Con As New System.Data.SqlClient.SqlConnection( _
        "Data Source=(local)\SQLEXPRESS;" & _
        "Initial Catalog=TEST;Integrated Security=SSPI")
    Try
        'コネクションオープン
        Con.Open()
        '取得関数SQL
        Dim SQL As String = "[dbo].[ProcTest]"
        'SQLコマンド生成
        Dim sqlCommand As New System.Data.SqlClient.SqlCommand(SQL, Con)
        'ストアド・プロシージャ設定
        sqlCommand.CommandType = System.Data.CommandType.StoredProcedure
        'SQLコマンドへ第1引数設定(SrcNumber)
        sqlCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@SrcNumber", 10))
        'SQLコマンドへ第2引数設定(DesNumber：出力指定)
        Dim prmDesNumber As New System.Data.SqlClient.SqlParameter
        With prmDesNumber
            .ParameterName = "@DesNumber"   'パラメータ名
            .DbType = Data.DbType.Decimal   'DBのデータ型
            .Direction = System.Data.ParameterDirection.Output  '出力
            .Precision = 18     'DECIMAL(18,0)
            .Scale = 0          '(小数点以下指定)
            .Value = 0          '取り敢えず値をクリア
        End With
        sqlCommand.Parameters.Add(prmDesNumber)
        'プロシージャ実行
        sqlCommand.ExecuteNonQuery()
        '戻り値を表示
        Console.Write(prmDesNumber.Value)
    Finally
        'コネクション破棄
        If Con.State <> System.Data.ConnectionState.Closed Then
            Con.Close()
            Con.Dispose()
        End If
    End Try
End Sub

=====
2012/06/13：の時の情報