Read CSV value into SQL query string

Question

I am trying to take the vales (comma separated) from a text box and pass them to a query string in an ExecuteSQL statement. I am not sure how to do this correctly. The obvious issue is that it will break if there are more or less than the three exceptions. I need to use an array or list, but don't know how to read that back into the query string?

This is what I have so far:

Private Sub Button5_Click(sender As System.Object, e As System.EventArgs) Handles Button5.Click
        Dim db As New Database
        If txtEmpExceptions.Text.ToString() IsNot Nothing Then
            Dim EmpExcept1 As String = txtEmpExceptions.Text.ToString().Split(",")(0)
            Dim EmpExcept2 As String = txtEmpExceptions.Text.ToString().Split(",")(1)
            Dim EmpExcept3 As String = txtEmpExceptions.Text.ToString().Split(",")(2)
            db.ExecuteSQL("Delete dbo.Employee where employeeID <> '" & EmpExcept1 & "' and employeeID <> '" & EmpExcept2 & "' and employeeID <> " & EmpExcept3, prodString)
        Else
            db.ExecuteSQL("Delete dbo.Employee", prodString)
        End If
    End Sub

Answer 1

To check if you have all your inputs you could split before and then test the count of elements in the array produced by string.Split

Private Sub Button5_Click(sender As System.Object, e As System.EventArgs) Handles Button5.Click
    Dim db As New Database
    Dim parts = txtEmpExceptions.Text.Split(",")
    If parts.Length <> 3 Then
       MessageBox.Show("Text must be splitted in 3 substrings")
       return
    End If
    Dim EmpExcept1 As String = parts(0)
    Dim EmpExcept2 As String = parts(1)
    Dim EmpExcept3 As String = parts(2)       
    ....

Note that a TextBox.Text is never Nothing. You could remove that check.
(And not strictly related to your problem, but as I have said in comment, you really should try to use a different approach to your database query to avoid string concatenation at all costs)

Answer 2

This should provide a good balance between your experience level and preventing SQL Injection:

Dim empIds As String = "bob, sally, fred" 'This would come from your textbox
Dim emps() As String = empIds.Split(",")

Dim qry As String = "DELETE FROM dbo.Employee WHERE "
Dim whereClause As String = " employeeID <> @param{0} AND "

For i As Integer = 0 To emps.Length - 1
    'Add "employeeID <> .. for as many ids as were parsed
    qry = String.Concat(qry, String.Format(whereClause, i))
    Dim paramName As String = String.Concat("@param", i)
    Dim par As new SqlParameter(paramName, SqlDbType.VarChar, 50) 'Assumes your employee ids are text type and max 50 length - adjust accordingly
    par.Value = emps(i)
    'Now add this parameter to your SqlCommand, I don't know how your db library does that

Next i

'Get rid of the last "AND"
qry = qry.Remove(qry.Length - 4)

'Now set the CommandText of  your SqlCommand to qry
'Then execute the command

Answer 3

In sql, you can create a table type - here is an example from one of my databases. Modify as needed.

CREATE TYPE [dbo].[AddSample_Type] AS TABLE(
[JobID] [int] NOT NULL,
[SampleDescription] [nvarchar](1000) NOT NULL,
[Notes] [nvarchar](1000) NULL,
[SampleName] [nvarchar](200) NULL,
[StorageLocationID] [int] NULL,
[StorageDispositionID] [int] NULL,
[StorageNotes] [nvarchar](1000) NULL,
[EmployeeID] [int] NULL,
[CheckedOutToEmployee] [bit] NULL
)
GO

You can create a procedure which accepts this table as input.It's much easier and more efficient to work with rows and tables in sql, after all.

 CREATE PROCEDURE [dbo].[AddMultipleSamplesToJob]
@SamplesToAdd AddSample_Type Readonly
AS
BEGIN
select JobID,SampleDescription,Notes,SampleName,StorageLocationID,
StorageDispositionID,StorageNotes,EmployeeID,CheckedOutToEmployee, 0 as     SampleNumber
 from @SamplesToAdd
END

You can now send a table full of rows to your stored procedure from VB.

 Dim addSamplesTable As New EPI_JobBookDataSet.AddMultipleSamplesToJobDataTable
    Dim AddSamplesAdapter As New EPI_JobBookDataSetTableAdapters.AddMultipleSamplesToJobTableAdapter
'Datatable to be sent in the procedure
Dim TablesamplesToAdd As New EPI_JobBookDataSet.TableType_AddSampleTable
'Make a new row and load it with data
 Dim newSampleRow As EPI_JobBookDataSet.TableType_AddSampleRow =     TablesamplesToAdd.Rows.Add()
            newSampleRow.CheckedOutToEmployee = StorageActivityEntryTool1.WithEmployee
            newSampleRow.EmployeeID = StorageActivityEntryTool1.EmployeeID
            newSampleRow.JobID = JobID
            newSampleRow.Notes = r.Cells(ColumnNotes.Index).Value
            newSampleRow.SampleDescription = r.Cells(ColumnDescription.Index).Value
            newSampleRow.SampleName = r.Cells(ColumnSampleName.Index).Value
            newSampleRow.StorageDispositionID = StorageActivityEntryTool1.DispositionID
            newSampleRow.StorageLocationID = StorageActivityEntryTool1.LocationID
            newSampleRow.StorageNotes = "Some notes"
'Add more rows if desired
'
'Send the query
AddSamplesAdapter.Fill(addSamplesTable, TablesamplesToAdd)

I hope you find this useful. Please ignore the reference inside of my row as I copied it from my code and was too lazy to change those particular values for this example.