ストアドプロシージャクラス
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
namespace LibrarySample
{
<summary>
</summary>
public class DbStoredProcedure : IDisposable
{
#region 変数
<summary>
</summary>
private SqlConnection connection = null;
<summary>
</summary>
private SqlCommand command = null;
<summary>
</summary>
private string storedProcedureName = string.Empty;
<summary>
</summary>
private SqlTransaction transaction = null;
<summary>
</summary>
private string connectionString = string.Empty;
<summary>
</summary>
private string serverName = string.Empty;
<summary>
</summary>
private string databaseName = string.Empty;
<summary>
</summary>
private string userId = string.Empty;
<summary>
</summary>
private string password = string.Empty;
<summary>
</summary>
private int connectionTimeout = 600;
<summary>
</summary>
private int commandTimeout = 60;
#endregion
#region プロパティ
<summary>
</summary>
public SqlConnection Connection
{
get
{
return this.connection;
}
}
<summary>
</summary>
public SqlCommand Command
{
get
{
return this.command;
}
set
{
this.command = value;
}
}
<summary>
</summary>
public string StoredProcedureName
{
get
{
return this.storedProcedureName;
}
set
{
this.storedProcedureName = value;
}
}
<summary>
</summary>
public int CommandTimeout
{
get
{
return this.commandTimeout;
}
set
{
this.commandTimeout = value;
}
}
#endregion
#region コンストラクタ
<summary>
</summary>
public DbStoredProcedure()
{
TODO
this.serverName = @"SUZUKI-XP\SQLEXPRESS";
this.databaseName = "pubs";
this.userId = "sa";
this.password = "sa";
this.connectionTimeout = 600;
this.commandTimeout = 60;
this.connectionString = this.CreateConnectionString(this.serverName, this.databaseName,
this.userId, this.password, this.connectionTimeout);
}
#endregion
#region 接続文字列を取得する
<summary>
</summary>
<param name="serverName"></param>
<param name="databaseName"></param>
<param name="userId"></param>
<param name="password"></param>
<param name="connectTimeout"></param>
<returns></returns>
private string CreateConnectionString(string serverName, string databaseName, string userId, string password, int connectTimeout)
{
return string.Format(@"Data Source={0};Initial Catalog={1};Persist Security Info=false;User ID={2};Password={3};Connect Timeout={4}",
serverName, databaseName, userId, password, connectionTimeout);
}
#endregion
#region DBに接続する
<summary>
</summary>
public void Connect()
{
this.connection = new SqlConnection(this.connectionString);
this.connection.Open();
}
#endregion
#region DBを切断する
<summary>
</summary>
public void Close()
{
if (this.connection != null)
{
if (ConnectionState.Open == this.connection.State)
{
this.connection.Close();
this.connection.Dispose();
this.connection = null;
}
}
}
#endregion
#region コマンドを生成する
<summary>
</summary>
<param name="storedProcedureName"></param>
public void CreateCommand(string storedProcedureName)
{
this.storedProcedureName = storedProcedureName;
this.command = new SqlCommand(this.storedProcedureName, this.connection);
this.command.CommandType = CommandType.StoredProcedure;
this.command.CommandTimeout = this.commandTimeout;
}
#endregion
#region 参照系ストアドプロシージャを実行する
<summary>
</summary>
<returns></returns>
public DataTable ExecuteQuery()
{
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = this.command;
adapter.Fill(ds);
if (ds.Tables[0] != null)
{
return ds.Tables[0];
}
else
{
return null;
}
}
#endregion
#region 更新系ストアドプロシージャを実行する
<summary>
</summary>
<returns></returns>
public int ExecuteNonQuery()
{
this.command.Transaction = this.transaction;
return this.command.ExecuteNonQuery();
}
#endregion
#region 単一の値を取得するストアドプロシージャを実行する
<summary>
</summary>
<returns></returns>
public T ExecuteScalar<T>()
{
return (T)(this.command.ExecuteScalar());
}
#endregion
#region トランザクションを開始する
<summary>
</summary>
public void BeginTransaction()
{
if (this.connection != null)
{
if (this.transaction != null)
{
this.transaction.Dispose();
this.transaction = null;
}
this.transaction = this.connection.BeginTransaction();
}
}
#endregion
#region コミットを実行する
<summary>
</summary>
public void Commit()
{
if (this.transaction != null)
{
this.transaction.Commit();
this.transaction.Dispose();
this.transaction = null;
}
}
#endregion
#region ロールバックを実行する
<summary>
</summary>
public void Rollback()
{
if (this.transaction != null)
{
this.transaction.Rollback();
this.transaction.Dispose();
this.transaction = null;
}
}
#endregion
#region Disposeを実行する
<summary>
</summary>
public void Dispose()
{
this.Rollback();
this.Close();
}
#endregion
TODO
#region SqlDataReader用の処理
<summary>
</summary>
public event Func<SqlDataReader, object> SqlDataReaderEventHandler;
<summary>
</summary>
<returns></returns>
public object ExecuteQueryForSqlDataReader()
{
object obj = null;
using (SqlDataReader reader = this.command.ExecuteReader())
{
obj = this.SqlDataReaderEventHandler(reader);
}
return obj;
}
#endregion
}
}
テストストアドプロシージャ
USE [pubs]
GO
/****** オブジェクト: StoredProcedure [dbo].[TestProcedure] スクリプト日付: 07/01/2009 20:17:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[TestProcedure]
(
@au_idIN varchar (11),
@numTitlesOUT Integer OUTPUT
)
AS
select
A.au_fname, A.au_lname, T.title
from
authors as A join titleauthor as TA
on A.au_id=TA.au_id join titles as T
on T.title_id=TA.title_id
where
A.au_id=@au_idIN
set
@numTitlesOUT = @@Rowcount
return (5)
USE [pubs]
GO
/****** オブジェクト: StoredProcedure [dbo].[TestProcedure2] スクリプト日付: 07/01/2009 20:18:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-
- =============================================
- Author:
- Create date:
- Description:
- =============================================
Create Procedure [dbo].[TestProcedure2]
(
@au_idIN varchar (11)
)
As
/* set nocount on */
select
count (T.title)
from
authors as A join titleauthor as TA
on A.au_id=TA.au_id join titles as T
on T.title_id=TA.title_id
where
A.au_id=@au_idIN
Return(5)
USE [pubs]
GO
/****** オブジェクト: StoredProcedure [dbo].[TestProcedure3] スクリプト日付: 07/01/2009 20:19:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-
- =============================================
- Author:
- Create date:
- Description:
- =============================================
Create Procedure [dbo].[TestProcedure3]
(
@au_idIN varchar (11),
@au_fnam varchar (30)
)
As
/* set nocount on */
Update authors set au_fname = @au_fnam where au_id = @au_idin
return (5)
ストアドプロシージャクラス使用例
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
namespace StoredProcedureTest
{
class Program
{
static void Main(string[] args)
{
{
using (DbStoredProcedure dbsp = new DbStoredProcedure())
{
dbsp.Connect();
dbsp.CreateCommand("TestProcedure");
SqlParameter RetVal = dbsp.Command.Parameters.Add("RetVal", SqlDbType.Int);
RetVal.Direction = ParameterDirection.ReturnValue;
SqlParameter IdIn = dbsp.Command.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11);
IdIn.Direction = ParameterDirection.Input;
IdIn.Value = "213-46-8915";
SqlParameter NumTitles = dbsp.Command.Parameters.Add("@numtitlesout", SqlDbType.VarChar, 11);
NumTitles.Direction = ParameterDirection.Output;
DataTable dt = dbsp.ExecuteQuery();
Console.WriteLine("Number of Rows: " + NumTitles.Value);
Console.WriteLine("Return Value: " + RetVal.Value);
Console.ReadLine();
}
}
{
using (DbStoredProcedure dbsp = new DbStoredProcedure())
{
string strCount;
dbsp.Connect();
dbsp.CreateCommand("TestProcedure2");
SqlParameter RetVal = dbsp.Command.Parameters.Add("RetVal", SqlDbType.Int);
RetVal.Direction = ParameterDirection.ReturnValue;
SqlParameter IdIn = dbsp.Command.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11);
IdIn.Direction = ParameterDirection.Input;
IdIn.Value = "213-46-8915";
strCount = dbsp.ExecuteScalar<int>().ToString();
Console.WriteLine("Number of Rows: " + strCount);
Console.WriteLine("Return Value: " + RetVal.Value);
Console.ReadLine();
}
}
{
using (SqlConnection PubsConn = new SqlConnection(@"server=VAIO-RA50\SQLEXPRESS;uid=sa;pwd=sa;database=pubs"))
{
string strRowAffect;
SqlCommand testCMD = new SqlCommand("TestProcedure3", PubsConn);
testCMD.CommandType = CommandType.StoredProcedure;
SqlParameter RetVal = testCMD.Parameters.Add("RetVal", SqlDbType.Int);
RetVal.Direction = ParameterDirection.ReturnValue;
SqlParameter IdIn = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11);
IdIn.Direction = ParameterDirection.Input;
IdIn.Value = "213-46-8915";
SqlParameter FnameIn = testCMD.Parameters.Add("@au_fnam", SqlDbType.VarChar, 30);
FnameIn.Direction = ParameterDirection.Input;
FnameIn.Value = "Marjorie";
PubsConn.Open();
strRowAffect = testCMD.ExecuteNonQuery().ToString();
Console.WriteLine("Number of Rows: " + strRowAffect);
Console.WriteLine("Return Value: " + RetVal.Value);
Console.ReadLine();
}
using (DbStoredProcedure dbsp = new DbStoredProcedure())
{
string strRowAffect = string.Empty; ;
dbsp.Connect();
dbsp.CreateCommand("TestProcedure3");
SqlParameter RetVal = dbsp.Command.Parameters.Add("RetVal", SqlDbType.Int);
RetVal.Direction = ParameterDirection.ReturnValue;
SqlParameter IdIn = dbsp.Command.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11);
IdIn.Direction = ParameterDirection.Input;
IdIn.Value = "213-46-8915";
SqlParameter FnameIn = dbsp.Command.Parameters.Add("@au_fnam", SqlDbType.VarChar, 30);
FnameIn.Direction = ParameterDirection.Input;
FnameIn.Value = "Marjorie";
try
{
dbsp.BeginTransaction();
strRowAffect = dbsp.ExecuteNonQuery().ToString();
dbsp.Commit();
}
catch (Exception)
{
dbsp.Rollback();
}
Console.WriteLine("Number of Rows: " + strRowAffect);
Console.WriteLine("Return Value: " + RetVal.Value);
Console.ReadLine();
}
}
}
}
}