Connect to different connection strings in same "using"

Question

I have a model with 2 different lists like:

 public IList<PurchaseOrderPreliminaryDesignModel> DesignList { get; set; } = new List<PurchaseOrderPreliminaryDesignModel>();

        public IList<PurchaseOrderPreliminaryVendorModel> VendorList { get; set; } = new List<PurchaseOrderPreliminaryVendorModel>();
        public PurchaseOrderPreliminaryDesignViewModel AddDesignList(IEnumerable<PurchaseOrderPreliminaryDesignModel> model)
        {
            ((List<PurchaseOrderPreliminaryDesignModel>)DesignList).AddRange(model);
            return this;
        }

        public PurchaseOrderPreliminaryDesignViewModel AddVendorList (IEnumerable<PurchaseOrderPreliminaryVendorModel> model)
        {
            ((List<PurchaseOrderPreliminaryVendorModel>)VendorList).AddRange(model);
            return this;
        }

So I fill first list using method:

  public PurchaseOrderPreliminaryDesignViewModel GetPreliminaryDesignList(string jobNumber)
        {
            try
            {
                PurchaseOrderPreliminaryDesignViewModel DoGetDesigns()
                {
                    using (var connection = _connectionManager.GetOpenConnection(_configuration.GetConnectionString(FirstConnectionString)))
                    {
                        var rModel = new PurchaseOrderPreliminaryDesignViewModel();
                        var designList = connection.Query<PurchaseOrderPreliminaryDesignModel>("[dbo].[usp_PurchaseOrder_Preliminary_Design]", param: new
                        {
                            LegacyKey = jobNumber
                        }, commandType: CommandType.StoredProcedure);

                        rModel.AddDesignList(designList);
                        return rModel;
                    }
                }
                return DoGetDesigns();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

As you can see I'm using FirstConnectionString in my using method, so I connect to one database to fill first list. Now I want to fill the other list but with different connection.

My question is how can I connect to second string to fill other List? I mean in the same using do something like:

var vendorList = connection2.Query<PurchaseOrderPreliminaryVendorModel>("mystore", param: new
                    {
                        Parameter = jobNumber
                    }, commandType: CommandType.StoredProcedure);

then in return use like:

rModel.AddDesignList(designList).AddVendorList(vendorList);
                        return rModel;

How can I achieve that? Regards

Answer 1

• You can use double Using base on using statement - C# Reference | Microsoft Docs

---

public PurchaseOrderPreliminaryDesignViewModel GetPreliminaryDesignList(string jobNumber)
{
    try
    {
        PurchaseOrderPreliminaryDesignViewModel DoGetDesigns()
        {
            using (var connection = _connectionManager.GetOpenConnection(_configuration.GetConnectionString(FirstConnectionString)))
            using (var connection2 = _connectionManager.GetOpenConnection(_configuration.GetConnectionString(SecondConnectionString)))

            {
                var rModel = new PurchaseOrderPreliminaryDesignViewModel();
                var designList = connection.Query<PurchaseOrderPreliminaryDesignModel>("[dbo].[usp_PurchaseOrder_Preliminary_Design]", param: new
                {
                    LegacyKey = jobNumber
                }, commandType: CommandType.StoredProcedure);


                var vendorList = connection2.Query<PurchaseOrderPreliminaryVendorModel>("mystore", param: new
                {
                    Parameter = jobNumber
                }, commandType: CommandType.StoredProcedure);

                rModel.AddDesignList(designList).AddVendorList(vendorList);                 

                return rModel;
            }
        }
        return DoGetDesigns();
    }
    catch (Exception ex)
    {
        throw ex;
    }
}