C#でストアドプロシージャから値を取得する。

ファイルNoをインクリメントするストアプロシージャ


ALTER PROCEDURE [dbo].[prFileNo_Hatuban]
    @User varchar(20)
AS
BEGIN
    DECLARE @FileNo bigint

    BEGIN TRANSACTION

    SELECT @FileNo = MAX(FileNo) FROM L_FileHistory WITH (TABLOCKX)
    INSERT INTO L_FileHistory WITH (TABLOCKX) (FileNo, RegistUser, UpdateUser) VALUES (@FileNo + 1, @User, @User )

    COMMIT TRANSACTION

    SELECT @FileNo as FileNo
END

ALTER PROCEDURE [dbo].[prFileNo_Hatuban]

@User varchar(20)

BEGIN

DECLARE @FileNo bigint

BEGIN TRANSACTION

SELECT @FileNo = MAX(FileNo) FROM L_FileHistory WITH (TABLOCKX)

INSERT INTO L_FileHistory WITH (TABLOCKX) (FileNo, RegistUser, UpdateUser) VALUES (@FileNo + 1, @User, @User )

COMMIT TRANSACTION

SELECT @FileNo as FileNo

END

C#コードでストアドを呼び出す


using System.Data;
using System.Data.OleDb;　

OleDbConnection objConnection2 = new OleDbConnection(GetDBConnString());
OleDbCommand objCmd2 = new OleDbCommand("prFileNo_Hatuban", objConnection2);
objCmd2.Connection.Open();
objCmd2.CommandType = CommandType.StoredProcedure;
objCmd2.Parameters.AddWithValue("@User", SqlDbType.VarChar);
objCmd2.Parameters["@User"].Value = this.Application.User.LoginName;

//com.ExecuteNonQuery();
OleDbDataReader myReader2 = objCmd2.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
long lngFileNo = 0;
while (myReader2.Read())
{
    lngFileNo = myReader2.GetInt64(0) + 1;
}
myReader2.Close();
objConnection2.Close();

using System.Data;

using System.Data.OleDb;　

OleDbConnection objConnection2 = new OleDbConnection(GetDBConnString());

OleDbCommand objCmd2 = new OleDbCommand("prFileNo_Hatuban", objConnection2);

objCmd2.Connection.Open();

objCmd2.CommandType = CommandType.StoredProcedure;

objCmd2.Parameters.AddWithValue("@User", SqlDbType.VarChar);

objCmd2.Parameters["@User"].Value = this.Application.User.LoginName;

//com.ExecuteNonQuery();

OleDbDataReader myReader2 = objCmd2.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

long lngFileNo = 0;

while (myReader2.Read())

{

lngFileNo = myReader2.GetInt64(0) + 1;

}

myReader2.Close();

objConnection2.Close();