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);
    }

`SELECT VALUE FROM dbo.COMMON_CONFIG WHERE VALUE = some_value` means you already know value of it, so no need to select it from DB. Like `SELECT ID FROM tbl WHERE ID = 1` result is `1`, you could do `SELECT 1` or `dest=1`, or have you changed field names :D — mirkobrankovic, Jul 24 '13 at 06:04
@mirkobrankovic did not change, if not I would have known the error. And if I change the index at return dataTable.Rows[0]["Value"].ToString(); to the actual index where the value is stored, it will return an error, saying there is no row at position 3. So do it mean that the data is not stored inside? — CHOCOx33, Jul 24 '13 at 06:19

score 1 · Accepted Answer · answered Jul 24 '13 at 06:15

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

answered Jul 24 '13 at 06:15

Damith

62,401
13
102
153

it works. But if I dont use the foreach array and replace it with dtSource.Rows[5].ToString(), it will say that there is no row at position 5. Does it mean that it dont have any data? Is it recommended to use foreach array? – CHOCOx33 Jul 24 '13 at 06:59

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

1 Answers1