MultipleActiveResultSets for SQL Server and VB.NET application-CodePudding

I am trying to get multiple data sets from SQL Server using a VB.NET application. The problem that every time I try to execute the query, I get this message:

Cannot change property 'ConnectionString'. The current state of the connection is open

Then I tried to fix it by enabling MARS

<connectionStrings>
    <add name="ConString" 
         providerName="System.Data.SqlClient" 
         connectionString="Data Source=my-PC;Initial Catalog=Project;Persist Security Info=True; MultipleActiveResultSets=true;User ID=user;Password=*****" />
</connectionStrings>

This is my code

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim obj, body
    obj = TextBox1.Text
    body = TextBox2.Text


    For Each mail In getemail()
        Send_mail(mail, obj, body, getattachment(mail))
    Next
    MsgBox("Traitement effectué")


End Sub
Function getemail() As List(Of String)

    Dim strMailTo As New List(Of String)
    Dim SQL As String = "Select EMail  FROM  [USER]  WHERE EMail Is Not NULL And MatriculeSalarie   Is Not NULL And [EMail] <> '' and EtatPaie = 3 and BulletinDematerialise = 1  "
    Dim cmd As New SqlCommand
    Dim sqLdr As SqlDataReader
    Dim dr As DataRow
    Try

        ConnServer()
        cmd.Connection = con
        cmd.CommandText = SQL
        Using sda As New SqlDataAdapter(cmd)
            Using ds As New DataTable()
                sda.Fill(ds)
                sqLdr = cmd.ExecuteReader()
                For i = 0 To ds.Rows.Count - 1
                    dr = ds.Rows(i)
                    strMailTo.Add(dr("EMail"))
                Next
            End Using
        End Using
        Return strMailTo
        sqLdr.Close()

    Catch ex As Exception
        MsgBox(ex.Message.ToString)

    End Try
    closeCon()

    Return strMailTo
End Function

Function getattachment(email) As String()
    Dim SQL As String = "Select MatriculeSalarie  FROM [USER] WHERE [EMail]='" & email & "'"
    Dim cmd As New SqlCommand
    Dim sqLdr As SqlDataReader
    ConnServer()
    cmd.Connection = con
    cmd.CommandText = SQL
    Dim mat As String
    mat = ""
    Dim Dir As String = ConfigurationManager.AppSettings("path1").ToString
    Dim file()
    sqLdr = cmd.ExecuteReader()

    While sqLdr.Read
        mat = sqLdr.GetValue(sqLdr.GetOrdinal("MatriculeSalarie"))
    End While
    file = IO.Directory.GetFiles(Dir, mat.Substring(1) & "*.pdf")
    sqLdr.Close()
    Return file
End Function

CodePudding user response：

If all you are going to do is show a message box in a Catch, don't do it in the database code. Let the error bubble up to the user interface code and put the Try around where the method is called.

Do not declare variables without a DataType. The button code with Option Infer on sets the type of obj and body.

Private ConStr As String = "Your connection string"

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim obj = TextBox1.Text
    Dim body = TextBox2.Text
    Dim emails As New List(Of String)
    Try
        emails = getemail()
    Catch ex As Exception
        MessageBox.Show(ex.Message.ToString, "Error retrieving email list")
        Exit Sub
    End Try
    For Each email In emails
        Try
            Send_mail(email, obj, body, getattachment(email))
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Error getting attachments")
        End Try
    Next
    MessageBox.Show("Traitement effectué")
End Sub

Parameters used by Sub and Function must have a DataType.

I don't know what you are doing here.

While sqLdr.Read
        mat = sqLdr.GetValue(sqLdr.GetOrdinal("MatriculeSalarie"))
 End While

Each iteration will overwrite the previous value of mat. I can only assume that you expect only a single value, in which case you can use ExecuteScalar to get the first column of the first row of the result set. Don't do anything with the data until after the connection is closed. Just get the raw data and close (End Using) the connection. Manipulate the data later.

Always use Parameters. Parameters are not treated as executable code by the database server. They are simply values. An example of executable code that could be inserted is "Drop table [USER];" where the value of a parameter belongs. Oops!

Function getemail() As List(Of String)
    Dim SQL As String = "Select EMail  FROM  [USER]  
                            WHERE EMail Is Not NULL 
                            And MatriculeSalarie Is Not NULL 
                            And [EMail] <> '' 
                            And EtatPaie = 3 
                            And BulletinDematerialise = 1;"
    Dim dt As New DataTable
    Using con As New SqlConnection("Your connection string"),
            cmd As New SqlCommand(SQL, con)
        con.Open()
        Using reader = cmd.ExecuteReader
            dt.Load(reader)
        End Using
    End Using
    Dim strMailTo As New List(Of String)
    strMailTo = (From row As DataRow In dt.AsEnumerable
                 Select row.Field(Of String)(0)).ToList
    Return strMailTo
End Function


Function getattachment(email As String) As String()
    Dim SQL As String = "Select MatriculeSalarie  FROM [USER] WHERE [EMail]='" & email & "'"
    Dim mat As String
    Using con As New SqlConnection(ConStr),
            cmd As New SqlCommand(SQL, con)
        cmd.Parameters.Add("@email", SqlDbType.VarChar).Value = email
        con.Open()
        mat = cmd.ExecuteScalar().ToString()
    End Using
    Dim Dir As String = ConfigurationManager.AppSettings("path1").ToString
    'Your original code was fine, no need for searchPattern.
    'I added this so you could see if your search pattern was what you expected.
    Dim searchPattern = mat.Substring(1) & "*.pdf"
    Debug.Print(searchPattern) 'Appears in the Immediate window
    Dim file = IO.Directory.GetFiles(Dir, searchPattern)
    Return file
End Function