・モデル実装例
''' <summary> ''' Daoを取得するクラスの基底クラス ''' </summary> Public MustInherit Class DaoAccessBase Protected FAdoConnection As AdoConnection Protected FAdoHelper As ADODriverHelper ''' <summary> ''' コンストラクタ ''' </summary> Public Sub New() FAdoConnection = New AdoConnection(gsCnn1) FAdoHelper = New ADODriverHelper(gsCnn1) End Sub ''' <summary> ''' コンストラクタ・AdoConnectionを継承 ''' (トランザクションを通しで使う場合など) ''' </summary> Public Sub New(ByVal aConnection As AdoConnection) FAdoConnection = aConnection FAdoHelper = New ADODriverHelper(FAdoConnection.ConnectionString) End Sub Protected Overridable Function getDao(ByVal aOption As SearchCriteriaBase, Optional ByVal aMultiLine As Boolean = True) As DataTable Dim paramList As New List(Of OleDb.OleDbParameter) Dim sSelect As String Dim sWhere As String Dim sOrder As String sSelect = doMakeSelect(aOption, paramList) sWhere = doMakeWhere(aOption, paramList) sOrder = doMakeOrder(aOption, paramList) ' SQLの連結 Dim sSQL As String = sSelect sSQL = sSQL + " " + sWhere sSQL = sSQL + " " + sOrder ' 1行のみ取得の場合、DBに合わせたlimitを追加する If Not aMultiLine Then sSQL = FAdoHelper.getSelectLimit(sSQL, 1) End If ' データセットの取得 FAdoConnection.Connect() Try Using dtWK As DataTable = fDataSet_Fill_withParam(sSQL, paramList, FAdoConnection).Tables(0) ' データテーブルのマッピング Dim dtDest As DataTable = makeEmptyDataTable() mappingDataSet(dtWK, dtDest) Return dtDest End Using Finally FAdoConnection.Disconnect() End Try End Function End Class
・具象クラス
''' <summary> ''' メンバーマスタのDaoを取得するクラス ''' </summary> Public Class MemberMasterDao Inherits DaoAccessBase Public Sub New() MyBase.New() End Sub Public Sub New(ByVal aConnection As AdoConnection) MyBase.New(aConnection) End Sub ''' <summary> ''' DaoのDataTable列定義 ''' </summary> Protected Overrides Function doMakeEmptyDataTable(ByVal dtWK As DataTable) As Boolean ' データベースとマッピングするカラム dtWK.Columns.Add("MEMBER_ID", Type.GetType("System.Int32")) dtWK.Columns.Add("MEMBER_NAME", Type.GetType("System.String")) dtWK.Columns.Add("MEMBER_SHORT_NAME", Type.GetType("System.String")) dtWK.Columns.Add("MEMBER_ROLL", Type.GetType("System.String")) ' 作業用のカラム dtWK.Columns.Add("MODIFIED", Type.GetType("System.Boolean")) Return True End Function ''' <summary> ''' 検索条件よりSELECT文を生成 ''' </summary> Protected Overrides Function doMakeSelect(ByVal aOption As SearchCriteriaBase, ByVal aParamList As List(Of OleDb.OleDbParameter)) As String Return "SELECT M_MEMBER.*, M_USER.LOGIN_ID as UPDATE_USER_NAME " + _ "FROM M_MEMBER LEFT JOIN M_USER ON M_MEMBER.UPDATE_USER_ID = M_USER.USER_ID " End Function ''' <summary> ''' 検索条件よりWHERE文を生成 ''' </summary> Protected Overrides Function doMakeWhere(ByVal aOption As SearchCriteriaBase, ByVal aParamList As List(Of OleDb.OleDbParameter)) As String Dim scMember As SC_Member = DirectCast(aOption, SC_Member) Dim oWhere As New SqlHelper If Not scMember.memberID.Void Then oWhere.addAnd("MEMBER_ID = ?") aParamList.Add(makeParam("MEMBER_ID", scMember.memberID.IntValue)) End If If Not scMember.memberRoll.Void Then oWhere.addAnd("MEMBER_ROLL = ?") aParamList.Add(makeParam("MEMBER_ROLL", scMember.memberRoll.StrValue.ToLower)) End If Return oWhere.makeWhere("WHERE") End Function ''' <summary> ''' 検索条件よりORDER文を生成 ''' </summary> Protected Overrides Function doMakeOrder(ByVal aOption As SearchCriteriaBase, ByVal aParamList As List(Of OleDb.OleDbParameter)) As String Return "ORDER BY MEMBER_ROLL, MEMBER_ID" End Function ''' <summary> ''' SQLを発行した結果からDaoにマッピングする ''' </summary> Protected Overrides Function doMappingDataRow(ByVal adrSource As System.Data.DataRow, ByVal adrDest As System.Data.DataRow) As Boolean adrDest("MEMBER_ID") = adrSource("MEMBER_ID") adrDest("MEMBER_NAME") = adrSource("MEMBER_NAME") adrDest("MEMBER_SHORT_NAME") = adrSource("MEMBER_SHORT_NAME") adrDest("MEMBER_ROLL") = adrSource("MEMBER_ROLL") adrDest("MODIFIED") = False Return True End Function End Class
どっかで見たようなSqlHelperだなww
makeWhereにヘッダを指定すると、条件が一つでも存在した場合にヘッダを付ける機能を追加したくらいか。