Imports System.Data.SqlClient

'******************************************************************
'Program:  Northwind data Demo                                    *
'Author: Daniel Hynes                                             *
'Terms of Use:  You are free to use this code to help develop     *
'your own programs providing you give a vote at                   *
'Planet Source code  :-)                                          *
'******************************************************************

Namespace Northwind


    Public Class NorthwindDataAccess

        'I put the Connection string in the WEB.CONFIG so I don't have to keep
        'defining it through the program.  This is how you would get it from 
        'the web.config

        Shared sDSN As String = System.Configuration.ConfigurationSettings.AppSettings("dsn")


#Region " Code for Data Grid "
        Public Function BindDataGrid(ByVal strSortField As String, ByVal strSortDir As String, ByVal strTitle As String) As DataSet
            Dim conStr As SqlConnection
            Dim dadEmployees As SqlDataAdapter
            Dim dstEmployees As DataSet
            Dim test As String


            Dim colArrayList As New ArrayList()

            conStr = New SqlConnection(sDSN)

            'Now the only reason I used SQL statements is because you will be running this off
            'your own database.  Now If this was a production app, I would have used Stored Procedures.

            dadEmployees = New SqlDataAdapter("Select * from Employees WHERE Title = '" & strTitle & "' ORDER BY " & strSortField & " " & strSortDir, conStr)
            dstEmployees = New DataSet()
            dadEmployees.Fill(dstEmployees)
            BindDataGrid = dstEmployees


        End Function

        Public Function GetTitleDropDownList() As DataSet
            'Code for the the Dropdown, this will return a Dataset
            'that I use for the databinding on the dropdown.

            Dim conStr As SqlConnection
            Dim dadTitleDDList As SqlDataAdapter
            Dim dstTitleData As DataSet

            conStr = New SqlConnection(sDSN)

            conStr.Open()
            dadTitleDDList = New SqlDataAdapter("Select DISTINCT Title from Employees ORDER BY Title", conStr)
            dstTitleData = New DataSet()
            dadTitleDDList.Fill(dstTitleData)
            GetTitleDropDownList = dstTitleData

        End Function

        Public Function GridUpdate(ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) As Boolean
            Dim intEmployeeID As TextBox
            Dim txtFirstName As TextBox
            Dim txtLastName As TextBox
            Dim txtCity As TextBox
            Dim txtBirthDate As TextBox
            Dim strFirstName As String
            Dim strLastName As String
            Dim strCity As String
            Dim strBirthDate As String
            Dim strDOB As Date
            Dim strEmployeeID As String
            Dim conEmployees As SqlConnection
            Dim cmdSQL As SqlCommand
            Dim strSQL As String

            conEmployees = New SqlConnection(sDSN)

            'Populated the textboxes I defined above with teh cells in the datagrid.

            With e.Item
                intEmployeeID = .Cells(1).Controls(1)
                txtLastName = .Cells(2).Controls(1)
                txtFirstName = .Cells(3).Controls(1)
                txtCity = .Cells(4).Controls(1)
                txtBirthDate = .Cells(5).Controls(1)
            End With

            'Take the textboxes and put them in vaiables that I will use to 
            'to populate the parameters.

            strFirstName = txtFirstName.Text
            strLastName = txtLastName.Text
            strCity = txtCity.Text
            strBirthDate = txtBirthDate.Text
            strEmployeeID = intEmployeeID.Text

            strSQL = "UPDATE Employees SET FirstName=@FirstName, " _
    & "Lastname = @LastName, City = @City, BirthDate = @BirthDate " _
    & "WHERE EmployeeID = @EmployeeID"

            cmdSQL = New SqlCommand(strSQL, conEmployees)
            'Populate the parameters.
            With cmdSQL.Parameters
                .Add("@FirstName", strFirstName)
                .Add("@LastName", strLastName)
                .Add("@City", strCity)
                .Add("@BirthDate", strBirthDate)
                .Add("@EmployeeID", strEmployeeID)
            End With

            conEmployees.Open()
            cmdSQL.ExecuteNonQuery()
            conEmployees.Close()

            GridUpdate = True

        End Function

        Public Function GetEmployeesData(ByVal intEmployeeID As Integer) As SqlDataReader

            Dim conEmployees As SqlConnection
            Dim strSelect As String
            Dim cmdSelect As SqlCommand
            Dim dtrEmployee As SqlDataReader
            Dim strAge As String

            conEmployees = New SqlConnection(sDSN)

            'Get the Member Detail Information

            strSelect = "Select * FROM Employees WHERE EmployeeID = @EmployeeID"
            cmdSelect = New SqlCommand(strSelect, conEmployees)
            cmdSelect.Parameters.Add("@EmployeeID", intEmployeeID)
            conEmployees.Open()
            dtrEmployee = cmdSelect.ExecuteReader(CommandBehavior.SingleRow)
            GetEmployeesData = dtrEmployee

        End Function

        Public Function AddEmployee(ByVal EmployeeData As Array)
            Dim strFirstName As String
            Dim strLastName As String
            Dim strCity As String
            Dim strBirthDate As String
            Dim strDOB As Date
            Dim strTitle As String
            Dim conEmployees As SqlConnection
            Dim cmdSQL As SqlCommand
            Dim strSQL As String

            conEmployees = New SqlConnection(sDSN)


            'set the Variables equal to the array I passed in.

            strFirstName = EmployeeData(0)
            strLastName = EmployeeData(1)
            strCity = EmployeeData(2)
            strBirthDate = EmployeeData(3)
            strTitle = EmployeeData(4)


            strSQL = "INSERT INTO Employees (FirstName, " _
                   & "Lastname, City, BirthDate, Title) " _
                   & "VALUES (@FirstName, @LastName, @City, @BirthDate, @Title)"

            cmdSQL = New SqlCommand(strSQL, conEmployees)
            With cmdSQL.Parameters
                .Add("@FirstName", strFirstName)
                .Add("@LastName", strLastName)
                .Add("@City", strCity)
                .Add("@BirthDate", strBirthDate)
                .Add("@Title", strTitle)
            End With

            conEmployees.Open()
            cmdSQL.ExecuteNonQuery()
            conEmployees.Close()


        End Function

        Function UpdateEmployee(ByVal EmployeeData As Array)
            Dim strFirstName As String
            Dim strLastName As String
            Dim strCity As String
            Dim strBirthDate As String
            Dim strDOB As String
            Dim strTitle As String
            Dim strEmployeeID As String
            Dim conEmployees As SqlConnection
            Dim cmdSQL As SqlCommand
            Dim strSQL As String

            conEmployees = New SqlConnection(sDSN)

            'set the Variables equal to the array I passed in.

            strEmployeeID = EmployeeData(0)
            strFirstName = EmployeeData(1)
            strLastName = EmployeeData(2)
            strCity = EmployeeData(3)
            strBirthDate = EmployeeData(4)
            strTitle = EmployeeData(5)


            strSQL = "UPDATE Employees SET FirstName = @FirstName, " _
                   & "Lastname = @Lastname, City = @City, BirthDate=@BirthDate, Title=@Title " _
                   & "WHERE EmployeeID = @EmployeeID"

            cmdSQL = New SqlCommand(strSQL, conEmployees)
            With cmdSQL.Parameters
                .Add("@EmployeeID", strEmployeeID)
                .Add("@FirstName", strFirstName)
                .Add("@LastName", strLastName)
                .Add("@City", strCity)
                .Add("@BirthDate", CDate(strBirthDate))
                .Add("@Title", strTitle)
            End With

            conEmployees.Open()
            cmdSQL.ExecuteNonQuery()
            conEmployees.Close()
        End Function
#End Region

    End Class

End Namespace