Last active
September 6, 2018 17:04
-
-
Save daisukenishino2/a97e2017a0c683d2c858e39741b63949 to your computer and use it in GitHub Desktop.
Open棟梁の汎用Daoを使用したストアド実行サンプル
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- ユーザー定義テーブル型 | |
CREATE TYPE [dbo].[IntValueTableType] AS TABLE ([Value] INT NULL); | |
GO | |
-- ストアドプロシージャ | |
CREATE PROCEDURE [dbo].[SummaryValueProc] | |
-- 入力パラメタ | |
@InPut int, | |
-- 出力パラメタ | |
@OutPut int OUTPUT, | |
-- テーブル値パラメタ | |
@Values IntValueTableType READONLY | |
AS | |
BEGIN | |
-- 出力パラメタ | |
SELECT @OutPut = SUM(Value) FROM @Values; | |
-- 結果セット * 2 | |
SELECT * FROM [dbo].[Shippers]; | |
SELECT * FROM [dbo].[Suppliers]; | |
-- 戻り値 | |
RETURN @InPut * 2; | |
END ; | |
GO |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
TestReturnValue testReturn = new TestReturnValue(); | |
this.ReturnValue = testReturn; | |
// ↓業務処理----------------------------------------------------- | |
DataTable dt = null; | |
DataRow row = null; | |
CmnDao cmnDao = new CmnDao(this.GetDam(), CommandType.StoredProcedure); | |
cmnDao.SQLText = "dbo.SummaryValueProc"; | |
cmnDao.SetParameter("InPut", 7, null, 0, ParameterDirection.Input); | |
cmnDao.SetParameter("OutPut", null, SqlDbType.Int, 0, ParameterDirection.Output); | |
cmnDao.SetParameter("Return", null, SqlDbType.Int, 0, ParameterDirection.ReturnValue); | |
dt = new DataTable(); | |
dt.Columns.Add("Values", typeof(int)); | |
row = dt.NewRow(); | |
row[0] = 1; | |
dt.Rows.Add(row); | |
row = dt.NewRow(); | |
row[0] = 2; | |
dt.Rows.Add(row); | |
cmnDao.SetParameter("Values", dt, SqlDbType.Structured); | |
IDataReader idr = cmnDao.ExecSelect_DR(); | |
DataTable dt1 = new DataTable(); | |
dt1.Load(idr); | |
DataTable dt2 = new DataTable(); | |
dt2.Load(idr); | |
int i = (int)cmnDao.GetParameter("OutPut"); | |
int j = (int)cmnDao.GetParameter("Return"); |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
補足
SetParameter() メソッドの int size 引数 に -1 を指定すると、 null指定と同じ結果になる。
https://github.com/OpenTouryoProject/OpenTouryo/blob/master/root/programs/C%23/Frameworks/Infrastructure/Public/Db/DamSqlSvr.cs#L521