How to connect 2 databases inside a single Foreach loop for multiple rows of data without opening an-CodePudding

I have a scenario where i will be getting a datatable with multiple rows of data (sometimes in thousands) and i need to process the data row by row. While processing the data row by row, initially i need to call an ONprem sql DB where in i will call a stored procedure with input and output parameters and once the SP is executed i need to fetch the output data and update another table which is in another Azure sql DB as per the output parameter.

The issue is, when i use USING, i feel like it opens and closes the connection once for each row for each of the two databases and if there are 1000 rows of data then it might open and close DB1 10000 each and DB2 1000 each which takes much time and server hits.

How can i minimize this and may be keep the connection open until it does everything in the foreach loop.

Below is the sample code snippet-

public void Main()
{

    //Declare Variables
    int InputID;
    string OutputProcessedMsg;
    string SqlConn = "Data Source=xyx.com;Initial Catalog=ddddd;Provider=SQLNCLI11.1;Integrated Security=SSPI";
    string AzureSqlConn = "Data Source=vvvv.dev.com;Initial Catalog=yyyy;Provider=SQLNCLI11.1;Integrated Security=SSPI";
    Object AllData = Dts.Variables["User::VarAllPowerAppData"].Value;
    // this gets the data object and sets it to a data table
    OleDbDataAdapter A = new OleDbDataAdapter();
    System.Data.DataTable dt = new System.Data.DataTable();
    A.Fill(dt, AllData);
    //DataTable sourceTable = dt;

    foreach (DataRow dr in dt.Rows)
    {
        InputID = Convert.ToInt32(dr[0]);

        using (SqlConnection conn = new SqlConnection(SqlConn))
        {
            using (SqlCommand cmd = new SqlCommand("UpdateDataOnpremSQL", conn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                // set up the parameters and it's values
                cmd.Parameters.Add("@ID", SqlDbType.VarChar,15).Value = InputDealerID;
                cmd.Parameters.Add("@ProcessedMsg", SqlDbType.VarChar,-1).Direction = ParameterDirection.Output;
                conn.Open();
                cmd.ExecuteNonQuery();
                OutputProcessedMsg = Convert.ToString(cmd.Parameters["@ProcessedMsg"].Value);
                conn.Close();

            }
        }

        using (OleDbConnection Oconn = new OleDbConnection(AzureSqlConn))
        {
            using (OleDbCommand cmd = new OleDbCommand("UpdateDataAzureSQL", Oconn))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                // set up the parameters and it's values
                cmd.Parameters.AddWithValue("@ID", SqlDbType.VarChar).Value = InputDealerID;
                cmd.Parameters.AddWithValue("@ProcessedMsg", SqlDbType.VarChar).Value = OutputProcessedMsg;
                Oconn.Open();
                cmd.ExecuteNonQuery();
                Oconn.Close();
            }
        }
    }
}

I have tried this connection pooling but it seems like for every row it would hit the db , open and close connections which would affect DB server performance and hence wanted something where the connection will be open and the process is faster while switching to DB's until end of foreach loop of Databtable

CodePudding user response：

It does what you ask it to do. You declare your connections inside the foreach loop. In each iteration it creates a new connection, opens it, performs a command and closes the connection.

You should create the connection in your block Declare Variables, open it after creating, perform all commands in your loop, and close the connection after the loop finishes.

CodePudding user response：

You open your connection before the loop

And afterwards close them

public void Main()
{

    //Declare Variables
    int InputID;
    string OutputProcessedMsg;
    string SqlConn = "Data Source=xyx.com;Initial Catalog=ddddd;Provider=SQLNCLI11.1;Integrated Security=SSPI";
    SqlConnection conn = new SqlConnection(SqlConn);
    
    string AzureSqlConn = "Data Source=vvvv.dev.com;Initial Catalog=yyyy;Provider=SQLNCLI11.1;Integrated Security=SSPI";
    OleDbConnection Oconn = new OleDbConnection(AzureSqlConn);
    
    Object AllData = Dts.Variables["User::VarAllPowerAppData"].Value;
    // open all connections only once
    conn.Open();
    Oconn.Open();
    // this gets the data object and sets it to a data table
    OleDbDataAdapter A = new OleDbDataAdapter();
    System.Data.DataTable dt = new System.Data.DataTable();
    A.Fill(dt, AllData);
    //DataTable sourceTable = dt;

    foreach (DataRow dr in dt.Rows)
    {
        InputID = Convert.ToInt32(dr[0]);

        using (SqlCommand cmd = new SqlCommand("UpdateDataOnpremSQL", conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            // set up the parameters and it's values
            cmd.Parameters.Add("@ID", SqlDbType.VarChar, 15).Value = InputID;
            cmd.Parameters.Add("@ProcessedMsg", SqlDbType.VarChar, -1).Direction = ParameterDirection.Output;
            
            cmd.ExecuteNonQuery();
            OutputProcessedMsg = (string)cmd.Parameters["@ProcessedMsg"].Value;


        }


        using (OleDbCommand cmd = new OleDbCommand("UpdateDataAzureSQL", Oconn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            // set up the parameters and it's values
            cmd.Parameters.AddWithValue("@ID", SqlDbType.VarChar).Value = InputID;
            cmd.Parameters.AddWithValue("@ProcessedMsg", SqlDbType.VarChar).Value = OutputProcessedMsg;
                
            cmd.ExecuteNonQuery();
                
        }
    }
    conn.Close();
    Oconn.Close();
}