How to extract certain cell from DataTable and retrieve the value to be used in console application

Question

I want to retrieve some value from the dtSource and dtDestination before using it. But it seems that I cannot retrieve any value inside even though there is some values stored inside the database. Is the foreach useful to retrieve some value? I have debugged it but it seems that it dont retrieve the values in dtSource and dtDestination before passing as a string.

    static void Main(string[] args)
    {
        // Retrieve the connection string from Config table in master database
        string strConnFromConfig = getConfigDetails(SalesKey.DBConnection);

        DataTable dtSource = getSource(strConnFromConfig);
        DataTable dtDestination = getDestination(strConnFromConfig);
        DataTable dtRate = getRate(strConnFromConfig);
        DataTable dtCode = getCode(strConnFromConfig);

        //Having this issue here
        foreach (DataRow rwSrc in dtSource.Rows)
        {
            foreach (DataRow rwDest in dtDestination.Rows)
            {
                string src = rwSrc["Value"].ToString();
                string dest = rwDest["Value"].ToString();

                ExcelTemplate XLT = new ExcelTemplate();
                XLT.Open(src);
                DataBindingProperties dataProps = XLT.CreateDataBindingProperties();
                XLT.BindData(dtCode, "Code", dataProps);
                XLT.BindData(dtRate, "Rate", dataProps);

                XLT.Process();
                XLT.Save(dest);
            }
        }
    }

    // Retrieve the config details from master database
    static string getConfigDetails(SalesKey salesKey)
    {
        string strCon = ConfigurationManager.ConnectionStrings["Master"].ConnectionString;
        SqlConnection sqlConnection = new SqlConnection(strCon);

        if (sqlConnection.State == ConnectionState.Closed)
            sqlConnection.Open();

        string cSQL = "SELECT Value FROM dbo.COMMON_CONFIG WHERE Value = @value";

        SqlCommand sqlCommand = new SqlCommand(cSQL, sqlConnection);
        sqlCommand.Parameters.AddWithValue("@value", salesKey.ToString()); // Need to cast the enumeration value as string

        DataTable dataTable = new DataTable();
        dataTable.Load(sqlCommand.ExecuteReader());

        sqlCommand.Dispose();

        sqlConnection.Close();
        sqlConnection.Dispose();

        if (dataTable.Rows.Count > 0)
        {
            return dataTable.Rows[0]["Value"].ToString();
        }
        else
        {
            return string.Empty;
        }
    }

    // Generic method for sending in the SQL query to retrieve the specified dataset
    static DataTable getData(string connection, string query)
    {
        SqlConnection sqlConnection = new SqlConnection(connection);

        sqlConnection.Open();
        SqlCommand sqlCommand = new SqlCommand(query, sqlConnection);

        //SqlDataReader reader = sqlCommand.ExecuteReader();

        DataTable dataTable = new DataTable();
        dataTable.Load(sqlCommand.ExecuteReader());

        sqlCommand.Dispose();

        sqlConnection.Close();
        sqlConnection.Dispose();

        return dataTable;
    }

    static DataTable getSource(string connection)
    {
        connection = ConfigurationManager.ConnectionStrings["Master"].ConnectionString;
        string src = @"C:\Users\administrator.EBSDLAB\Desktop\Examples\Sales\currency.xlsx";
        string IdSQL = "SELECT VALUE FROM dbo.COMMON_CONFIG WHERE VALUE = '" + src + "'";
        return getData(connection, IdSQL);
    }

    static DataTable getDestination(string connection)
    {
        connection = ConfigurationManager.ConnectionStrings["Master"].ConnectionString;
        string dest = @"C:\Users\administrator.EBSDLAB\Desktop\Examples\Sales\output.xlsx";
        string IdSQL = "SELECT VALUE FROM dbo.COMMON_CONFIG WHERE VALUE = '" + dest + "'";
        return getData(connection, IdSQL);
    }

Answer 1

You may need to change the SQL

"SELECT VALUE FROM dbo.COMMON_CONFIG WHERE VALUE = '" + src + "'";

you send the value and again select the value!!!

I think you may need get the value by Name, something like below

"SELECT VALUE FROM dbo.COMMON_CONFIG WHERE Name= '" + src + "'";

change it accordingly, as your database column names, and this is apply to all your SQL strings

Note: Use SQL Parameters and using statements