一个数据集合的源代码

Imports System.ComponentModel
Imports System.Web.UI
Imports System.Data.SqlClient
Imports System.Data

<DefaultProperty("Text"), ToolboxData("<{0}:ResultPage runat=server></{0}:ResultPage>")> _
Public Class ResultPage
    Inherits System.Web.UI.WebControls.Panel
    Implements System.Web.UI.INamingContainer

    Dim _ds As Dataset                  'The InterFase of the DataSet
    Dim SqlConn As SqlConnection        'The Connection Database
    Dim ResultDataSet As New Dataset    'The DataSet of All Result
    Dim _PageId As String               'ページID

    <Bindable(True), Category("Appearance"), DefaultValue("")> Property Dataset() As DataSet

        Get
            Return _ds
        End Get

        Set(ByVal Value As Dataset)
            _ds = Value
        End Set

    End Property

    <Bindable(True), Category("Appearance"), DefaultValue("")> Property PageId() As String

        Get
            Return _PageId
        End Get

        Set(ByVal Value As String)
            _PageId = Value
        End Set

    End Property

    ReadOnly Property Conn() As String

        Get
            Return System.Configuration.ConfigurationSettings.AppSettings("SqlConn")
        End Get

    End Property

    Public Sub SqlDBConn()

        SqlConn = New SqlConnection(Conn())

    End Sub

    '***************************************************
    '[プロシージャID]  :001
    '[プロシージャ名]  :GetAllDataSet
    '[機能]            :Get all dataset result
    '[引数]            :
    '[戻り値]          :DataSet
    '[履歴]
    '変更日          担当者      内容
    '---------------------------------------------------
    '2004/08/03      WF      　 作成
    '***************************************************
    Public Function GetAllDataSet() As Dataset

        Dim theStrSql As String
        Dim theResult As Boolean
        Dim theStrReault As String
        Dim thePictArr As Array
        Dim theRecordCount As Integer
        Dim theString As String = ""
        Dim i As Integer

        'Get All Images
        theStrSql = "SELECT DISTINCT k.fid,k.fname,d.storage,a.acvalue,a.alid,at.atname,ag.agname"
        theStrSql = theStrSql + " FROM k"
        theStrSql = theStrSql + " INNER JOIN a ON k.fid =  a.fid"
        theStrSql = theStrSql + " INNER JOIN d ON k.did = d.did"
        theStrSql = theStrSql + " INNER JOIN at ON at.atid = a.atid"
        theStrSql = theStrSql + " INNER JOIN ag ON ag.agid=a.agid"
        theStrSql = theStrSql + " WHERE  a.fid in ("
        theStrSql = theStrSql + "SELECT a.fid FROM a WHERE a.agid = (SELECT agid  FROM ag WHERE agname = '製品紹介ページ')   AND a.acvalue ='gb_115')"
        theResult = funGetDataSet(theStrSql, "DsImage")
        If theResult = False Then
            'add log
        End If

        'Get All Mess
        theStrSql = "SELECT DISTINCT k.fid,k.fname,d.storage,a.acvalue,a.alid,at.atname,ag.agname"
        theStrSql = theStrSql + " FROM k"
        theStrSql = theStrSql + " INNER JOIN a ON k.fid =  a.fid"
        theStrSql = theStrSql + " INNER JOIN d ON k.did = d.did"
        theStrSql = theStrSql + " INNER JOIN at ON at.atid = a.atid"
        theStrSql = theStrSql + " INNER JOIN ag ON ag.agid=a.agid"
        theStrSql = theStrSql + " WHERE  a.fid in ("
        theStrSql = theStrSql + "SELECT a.fid FROM a WHERE a.agid = (SELECT agid  FROM ag WHERE agname = '機種情報')   AND a.acvalue ='gb_115')"
        theResult = funGetDataSet(theStrSql, "DsMess")
        If theResult = False Then
            'add log
        End If

        'Get The PictNumber
        theStrSql = "select acvalue from a"
        theStrSql = theStrSql + " where a.fid in (select a.fid from a where a.agid"
        theStrSql = theStrSql + " in ( select agid from ag where agname = '機種情報')  and a.acvalue ='gb_115')"
        theStrSql = theStrSql + "and a.atid in  ( select atid from at where atname='PICT情報')"
        theStrReault = funGetFieldValue(theStrSql, "acvalue")
        If theStrReault = "" Then
            'add log
        Else
            theString = Replace(theStrReault, "-", ",")

            'Get The PictImages
            theStrSql = "SELECT DISTINCT k.fid, k.fname, k.did,  d.storage"
            theStrSql = theStrSql + " FROM k"
            theStrSql = theStrSql + " INNER JOIN d ON k.did = d.did"
            theStrSql = theStrSql + " where k.fid in(" + theString + ")"

            theResult = funGetDataSet(theStrSql, "DsPict")
            If theResult = False Then
                'add log
            End If
        End If

        'Get the HorsePower
        theStrSql = "select acvalue from a"
        theStrSql = theStrSql + " where a.fid in (select a.fid from a where a.agid"
        theStrSql = theStrSql + " in ( select agid from ag where agname = '機種情報')  and a.acvalue ='gb_115')"
        theStrSql = theStrSql + "and a.atid in  ( select atid from at where atname='馬力帯')"
        theResult = funGetDataSet(theStrSql, "DsHorsePower")
        If theResult = False Then
            'add log
        End If

        Return ResultDataSet

    End Function

    '***************************************************
    '[プロシージャID]  :002
    '[プロシージャ名]  :GetDataSet
    '[機能]            :Execute The StrSql And Get the ResultSet
    '[引数]            :StrSql=The Content Of SQL
    '                  :StrTableName=The Name Of Table
    '                  :
    '[戻り値]          :TRUE =Success; FALSE = Failure
    '[履歴]
    '変更日          担当者      内容
    '---------------------------------------------------
    '2004/08/03      WF      　 作成
    '***************************************************
    Public Function funGetDataSet(ByVal inStrSql As String, ByVal inStrTableName As String) As Boolean

        Dim SqlCommand As SqlCommand
        Dim DapAdpater As SqlDataAdapter

        SqlCommand = New SqlCommand(inStrSql, SqlConn)
        DapAdpater = New SqlDataAdapter(SqlCommand)
        Try
            SqlConn.Open()
            DapAdpater.Fill(ResultDataSet, inStrTableName)
            Return True
        Catch ex As Exception
            'Add the log file
            Return False
        Finally
            SqlConn.Close()
        End Try

    End Function

    '***************************************************
    '[プロシージャID]  :003
    '[プロシージャ名]  :GetFieldValue
    '[機能]            :Execute The StrSql And Get the Value from a Field
    '[引数]            :StrSql=The Content Of SQL
    '                  :StrTableName=The Name Of Field
    '                  :
    '[戻り値]          :theReturn
    '[履歴]
    '変更日          担当者      内容
    '---------------------------------------------------
    '2004/08/03      WF      　 作成
    '***************************************************
    Public Function funGetFieldValue(ByVal inStrSql As String, ByVal inStrFieldName As String) As String

        Dim SqlCommand As SqlCommand
        Dim theDataReader As SqlDataReader
        Dim outReturn As String = ""

        SqlConn.Open()
        SqlCommand = New SqlCommand(inStrSql, SqlConn)
        theDataReader = SqlCommand.ExecuteReader()

        While theDataReader.Read()
            outReturn = theDataReader.Item(inStrFieldName)
        End While

        theDataReader.Close()
        SqlConn.Close()
        Return outReturn

    End Function

    Protected Overrides Sub OnLoad(ByVal e As System.EventArgs)

        'Get The Value From Web
        If MyBase.Parent.Page.Session("PageId") <> "" Then
            PageId = MyBase.Parent.Page.Session("PageId")
        End If

    End Sub
End Class