I am getting this error in VS when attempting a SQL Server connection within C# Winforms application.

I am using Microsoft.Data.SqlClient

private void button1_Click(object sender, EventArgs e)
{
    try
    {
        SqlConnectionStringBuilder build = new SqlConnectionStringBuilder("Server = {ServerName},1433; Initial Catalog = {Database}; Persist Security Info = False; User ID = {Username}; Password = {Password}; MultipleActiveResultSets = False; Encrypt = True; TrustServerCertificate = False; Authentication = Active Directory Password");

        using (SqlConnection connection = new SqlConnection(build.ConnectionString))
        {
            string sql = $"SELECT ItemID, Barcode FROM dbo.TEST_Barcode WHERE Barcode = 9323503022494";
         
            using (SqlCommand command = new SqlCommand(sql, connection)) 
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    // This is where the error occurs
                    connection.Open(); 

                    while (reader.Read())
                    {
                        textBox1.Text = reader.GetValue(0).ToString(); 
                    }
                }
            }
        }
    }
    catch (SystemException)
    {
    }
}

The above will return an error wherever the connection is.

The problem is:

• When typing this into VSC the SQL lookup occurs without an issue.
• When typing this into a VS terminal app the SQL lookup occurs without an issue.

Code for successes in VSC and VS terminal app are as follows:

using System;
using Microsoft.Data.SqlClient;

public class Program
{
    public static void Main()
    {
        long barcode;
        string barcodeText;

        Console.WriteLine("Enter in a barcode to find: ");
        barcodeText = Console.ReadLine();
        barcode = Convert.ToInt64(barcodeText);

        try
        {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder("Server=tcp:{server},1433;Initial Catalog={database};Persist Security Info=False;User ID={username};Password={password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Authentication=Active Directory Password");

            using (SqlConnection connection = new SqlConnection(builder.ConnectionString))
            {
                string sql = $"SELECT ItemID, Barcode, SOH FROM dbo.TEST_Barcode WHERE Barcode = {barcode}";

                using (SqlCommand command = new SqlCommand(sql, connection))
                {
                    connection.Open();

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                    while (reader.Read())
                        {
                            Console.WriteLine("{0} {1} {2}", reader.GetString(0), reader.GetString(1), reader.GetInt32(2));
                        }
                    }
                }
            }   
        }
        catch (SqlException e)
        {
            Console.WriteLine(e.ToString());
        }
        Console.ReadLine();
    }
}

Output from the above:

Enter in a barcode to find: 
9323503022494

100003 9323503022494 5

From what I could find on the MS documentation this occurs when the connection is closed, but why would this work in a VSC VS terminal and fail in a Winforms app?

Any help would be appreciated

Thanks!

CodePudding user response：

Try this one advice from @AlwaysLearning

private void button1_Click(object sender, EventArgs e)
{
    try
    {
        SqlConnectionStringBuilder build = new SqlConnectionStringBuilder("Server = {ServerName},1433; Initial Catalog = {Database}; Persist Security Info = False; User ID = {Username}; Password = {Password}; MultipleActiveResultSets = False; Encrypt = True; TrustServerCertificate = False; Authentication = Active Directory Password");

        using (SqlConnection connection = new SqlConnection(build.ConnectionString))
        {
            string sql = $"SELECT ItemID, Barcode FROM dbo.TEST_Barcode WHERE Barcode = 9323503022494";
         
            using (SqlCommand command = new SqlCommand(sql, connection)) 
            {

                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        textBox1.Text = reader.GetValue(0).ToString(); 
                    }
                }
            }
        }
    }
    catch (SystemException)
    {
    }
}