How to use Two SqlConnection in single TransactionScope in asp.net c#

Question

I have installed sql server 2008 R2 in two systems, from this one system act as a server and another is client.

I need to copy product from server system database to client system database

In my web.config

 <connectionStrings>
<add name="DBConnection" connectionString="Data Source=SERVER-PC\SQLEXPRESS2008;Initial Catalog=POS;Integrated Security=False;User Id=sa;Password=sql2008;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />
<add name="DBConnection1" connectionString="Data Source=CLIENT-PC\SQLEXPRESS2008;Initial Catalog=POS;Integrated Security=False;User Id=sa;Password=sql2008;Connection Timeout=1;MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />

Here My Coding

            using (TransactionScope trnsScope = new TransactionScope())
        {
            try
            {
                List<Master_ProductBLL> lstProduct = new List<Master_ProductBLL>();
                //My First SQL Connection For Server
                using (SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString))
                {
                    connection.Open();
                    //Here I can get All Products from Server Database
                    lstProduct = Master_ProductBLL.GetMaster_ProductBLLs(DBAction.Status.Active, "");
                    connection.Dispose();
                }

                //My Second SQL Connection For Client
                using (SqlConnection connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection1"].ConnectionString))
                {
                    connection.Open();

                    //Here I have save my Server Product into Client SQL Server
                    foreach (Master_ProductBLL item in lstProduct)
                    {
                        item.Save(true);
                    }
                    connection.Dispose();
                }
                trnsScope.Complete();
                trnsScope.Dispose();
            }
            catch (TransactionException ex)
            {
                trnsScope.Dispose();
                throw ex;
            }
        }

It shows an Error like MSDTC on server 'CLIENT-PC\SQLEXPRESS2008' is unavailable

Unable to get the address of the distributed transaction coordinator for the server, from the server. Is DTC enabled on the server?

I have google it and find the following details

go to Services. (START > SETTINGS > CONTROL PANEL > ADMINISTRATIVE TOOLS > SERVICES)
Find the service called 'Distributed Transaction Coordinator' and RIGHT CLICK (on it and select) > Start.
make this service to run Automatically for solving this issue permanently

I have done the above steps both server and client.

But Still have an error

https://stackoverflow.com/questions/29414250/msdtc-on-server-server-is-unavailable — Sir Rufo, Dec 26 '17 at 08:32

score 1 · Answer 1 · answered Dec 26 '17 at 10:37

I think for two (or more) SQL servers to be transactionnaly 'synced', you have to configure network DTC as MSDTC service is in charge of a lot of stuff about transactions... (your client should use the server's one).

To do this :

type dcomcnfg in run...
open Component services | Computers
Right click "Local Computer" and go to MSDTC tab
uncheck Use a local coordinator and type in your server name (or IP)
make sure you followed the steps mentionned in MSDTC on server 'server is unavailable on both the client and the server.

Not really sure it'll work, but pretty sure it won't if you don't do that.

score -1 · Answer 2 · edited Jul 29 '18 at 09:54

-1

Which database you want to use create object of that database.it is simple

SqlConnection connection1 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString();

SqlConnection connection1 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection1"].ConnectionString

edited Jul 29 '18 at 09:54

Cœur

37,241
25
195
267

answered Dec 26 '17 at 13:09

khushbu

1
1

How to use Two SqlConnection in single TransactionScope in asp.net c#

2 Answers2