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


'                             S P_ M A N A G E R  C L A S S
'                           Mangage SQL Stored Procedures

'                                    Created 12/15/05

'                                          RPKessler

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

'Updated 1/17/07


'The Shared Functions Run_SP is designed to run a designated _SP from and SQL DB
'By passing the name of the _SP, the DS, DS TableName, and command text for the SP, the function
'runs the _SP and fills the DS table.

'It gets connection string from app.config file

Option Strict On
Option Explicit On


Imports System.Data
Imports System.Data.SqlClient
Imports ADO_in_Code_with_SQL_Server.Declares


Public Class SP_Manager

    Public Shared Function Execute( _
        ByVal SP_Name As String, ByVal CmdText As String, ByVal TableName As String, _
        ByVal PlaceHolder As String, ByVal SrchString As String) As Boolean

        '---this sub takes:  name of a stored proc, the CommandText for that SP, the DS TableName to use,
        '                           the @parameter used in the SP, and the search string (Name, zip, State) from the
        '                           form where they type it in.
        ' and runs the _SP
        Try

            '---clear all existing records
            myDataSet.Clear()

            '---create new adapter and pass it the name of the _SP and connection info
            Dim DA As New SqlDataAdapter(SP_Name, connString)


            '---now add parameters/capabilites to the DA and tell it the command to run is a _SP
            DA.SelectCommand.CommandType = CommandType.StoredProcedure

            '---tell it to use the field called LName and the number they type in to find a match
            DA.SelectCommand.Parameters.Add(PlaceHolder, SqlDbType.VarChar).Value = SrchString

            '---name of the _SP
            DA.SelectCommand.CommandText = (CmdText)

            DA.Fill(myDataSet, TableName)

            '---tell Mr. Grid where to connect to

            My.Forms.frmStep3.dgCustomers.DataSource = myDataSet.Tables("customers")
            My.Forms.frmStep3.dgCustomers.Columns(0).Visible = False

            Return True

        Catch ex As Exception

            MessageBox.Show("There was an error running the stored procedure." & ex.Message, "Stored Procedure Manager Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return False

        End Try
    End Function




End Class