CLR Stored Procedure Unable to connect with SqlConnection Regular Connection

Question

I tried to create a CLR stored procedure in VS2017 but encountering error "NOT Connected." while executing that stored procedure.

I need to connect to other database server to grab some data. Therefore I cannot use context=true in SqlConnection.

1. Stored procedure will be created in serverA
2. This stored procedure will query data from serverB
3. Data will be stored back to serverA.

Is there anything I need to do in order to have regular connection in CLR stored procedure?

Please advise. Thanks!

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void udp_CLR_GetData()
    {
        string ConnStr = "server=MyServer; database=MyDB; user id=accabc; password=abc123";
        string sql = " select top 1 ID from [dbo].Table1 ";
        SqlDataReader dr = null;
        DataTable dt = new DataTable();

        try
        {
            using (SqlConnection fcon = new SqlConnection(ConnStr))
            {
                if (fcon.State == ConnectionState.Open)
                {
                    SqlContext.Pipe.Send("Connected.");

                    using (SqlCommand fcmd = new SqlCommand(sql, fcon))
                    {
                        SqlContext.Pipe.Send("Before executing reader...");
                        dr = fcmd.ExecuteReader();
                        SqlContext.Pipe.Send("After executing reader...");

                        SqlContext.Pipe.Send("Before send...");
                        SqlContext.Pipe.Send(dr);
                        SqlContext.Pipe.Send("After send...");
                    }
                }
                else
                {
                    SqlContext.Pipe.Send("NOT Connected.");
                }
            }
        }
        catch(Exception ex)
        {
            SqlContext.Pipe.Send("Exception error (udp_CLR_GetData): " + ex.Message);
        }
        finally
        {
            if(dr != null && !dr.IsClosed)
            {
                dr.Close();
            }
        }
    }
}

Answer 1

Creating a new instance of a SqlConnection in:

using (SqlConnection fcon = new SqlConnection(ConnStr))

does not create it in an "open" state. You need to actually open it for it to be "open". So, I would remove the if (fcon.State == ConnectionState.Open) and the associated else part of it. I would also remove the SqlContext.Pipe.Send("Connected."); line.

Then, just before the dr = fcmd.ExecuteReader(); line, add a line for:

fcon.Open();

This way you open the connection and immediately execute the command. No need to open the connection only to do other work getting the command ready.

For more info on working with SQLCLR in general, please visit: SQLCLR Info