'--------------------------------------------------------------

'                            ADO_SQL CLASS MODULE
'                                     Ron Kessler
'                                Created 11/25/05
'--------------------------------------------------------------

'Updated 11/27/05

'11/25/05   Tested with final configurations
'11/26/05   Added changes in .Execute to handle a Windows Application as well.
'11/27/05   Added contructors to handle two different signatures.

'_______________________________________________________________________________
'FEATURES:

'This is a re-useable module that will handle ADO connectivity for MSSQL databases
' in an ASP or Windows App.
'You CAN pass it two properties: the SQL query string and the name of your dataset's table name.
'YOU MAY ALSO PASS THE PARAMETERS WHEN YOU INSTANTIATE THE OBJECT

'To run it, you use the .Execute method of the named instance of ADO_SQL.
'The SQL and tablename properties can be read if desired.

'BE SURE TO COMMENT OUT THE CORRECT LINE IN THE EXECUTE FUNCTION BELOW. YOU NEED
'TO SELECT THE CONNECTION STRING FOR A WINDOWS OR WEB APPLICATION.

'_______________________________________________________________________________

'=====================CONSTRUCTOR DEFINITIONS=====================

'OPTION 1:

'Dim MyADO As New ADO_SQL
'MyADO.SQL = "Select * FROM Customers ORDER BY LName"
'MyADO.TableName = "customers"

'OPTION 2:

'Dim SQL As String = "Select * FROM Customers ORDER BY LName"
'Dim MyTableName As String = "customers"
'Dim MyADO As New ADO_SQL(SQL, MyTableName)
'===============END OF CONSTRUCTOR DEFINITIONS====================



Imports System.Data
Imports System.Data.SqlClient
Public Class ADO_SQL

    Private m_SQL As String
    Private m_TableName As String

    Sub New()
        'This constructor is used when they do not pass parameters when the class
        'is instantiated.
        'C# uses this one but I didn't have it do anything so there is no code.

    End Sub

    Sub New(ByVal SQLQuery As String, ByVal DSTableName As String)
        '---this constructor is used if they pass the SQL string and name of the DS table when they
        'instantiate the object
        'This is a cool way to pass values into your class when it is created!

        m_SQL = SQLQuery
        m_TableName = DSTableName

    End Sub


    Public Property SQL()

        '---this is the SQL query you created to Select records

        Get
            SQL = m_SQL
        End Get
        Set(ByVal Value)
            m_SQL = Value
        End Set
    End Property
    Public Property TableName()

        '---this is the Dataset table name you want to use
        Get
            TableName = m_TableName
        End Get
        Set(ByVal Value)
            m_TableName = Value
        End Set

    End Property

    Public Function Execute(ByVal DS As DataSet)

        '---this executes your SQL query and fills the DA

        Dim ConnString As String


        '---web app
        ConnString = ConfigurationSettings.AppSettings("ConnectionString")

        '---windows app
        'ConnString = "server=sony-1; database=customers;uid=aspuser;pwd=123456"

        Dim Conn As New SqlConnection(ConnString)
        Dim DA As New SqlDataAdapter(m_SQL, Conn)
        DA.Fill(DS, m_TableName)
    End Function

End Class

'web config conn string

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    	<appSettings>
    	
	  <add key="ConnectionString" value="server=(local);database=customers;uid=aspuser;pwd=123456" />
    
		</appSettings>    
  <system.web>