Get data from SQL along with read excel in C#

Question

I have a program that are able to import an excel file to datatable binding with datagridview. Currently I need to add another column name InvtID and get that InvtID data from sql server based on Barcode column (column '3' as header) that I have been imported.

How am I able to achieve this one as right now my coding are able to get the data AFTER import an excel, so the InvtID column data are not in side-by-side with Barcode column. This is the only problem I encounter to finish this task, please assist me. Thank you

Here how my datatable looks like

public void filldatagridview(ExcelWorksheet workSheet)
       {
           DataTable dt = new DataTable();

           //Create the data column
           for (int col = workSheet.Dimension.Start.Column; col <= workSheet.Dimension.End.Column; col++)
           {
               dt.Columns.Add(col.ToString());
           }

          for (int row = 12; row <= 26; row++)
           {
               DataRow newRow = dt.NewRow(); //Create a row
               int i = 0;
               for (int col = workSheet.Dimension.Start.Column; col <= workSheet.Dimension.End.Column; col++)
               {
                   newRow[i++] = workSheet.Cells[row, col].Text;
               }
               dt.Rows.Add(newRow);
           }

           dt.Columns.RemoveAt(0); //remove No
           dt.Columns.RemoveAt(0); //remove article

      //Get BookCode
      using (SqlConnection conn = new SqlConnection("Server"))
      using (SqlCommand cmd = new SqlCommand(null, conn))
     {
    StringBuilder sb = new StringBuilder("WITH cte AS(SELECT case WHEN InvtID IS NULL OR InvtID='' THEN 'No Bookcode Found' ELSE InvtID END AS InvtID,Barcode,ROW_NUMBER() OVER(PARTITION BY Barcode ORDER BY InvtID Asc) rid FROM InventoryCustomer) SELECT InvtID AS BOOKCODE FROM cte WHERE rid=1 and Barcode In (");
             for (int i = 0; i < dt.Rows.Count; i++)
              {
                 if (i != 0) sb.Append(",");
                  string name = "@P" + i;
                  cmd.Parameters.AddWithValue(name, dt.Rows[i]["3"]); //"3" is barcode column
                  sb.Append(name);
              }
              sb.Append(")");
              cmd.CommandText = sb.ToString();
              SqlDataAdapter da = new SqlDataAdapter(cmd);
              da.Fill(dt);

              dt.Columns["BOOKCODE"].SetOrdinal(0);
              dataGridView2.DataSource = dt;
           }
      }

Answer 1

I am not sure if this is work.

Avoid adding new row when row is already exists.

Add column first from your dt DataTable

Just take a look at this code:

 public void filldatagridview(ExcelWorksheet workSheet)
        {
            DataTable dt = new DataTable();
            DataTable dtInvtID = new DataTable();
            dt.Columns.Add("dtInvtID"); //ADDING NEW COLUMN FIRST FOR YOUR dtInvtID
            //Create the data column
            for (int col = workSheet.Dimension.Start.Column; col <= workSheet.Dimension.End.Column; col++)
            {
                dt.Columns.Add(col.ToString());
            }

            for (int row = 12; row <= 26; row++)
            {
                DataRow newRow = dt.NewRow(); //Create a row
                int i = 0;
                for (int col = workSheet.Dimension.Start.Column; col <= workSheet.Dimension.End.Column; col++)
                {
                    newRow[i++] = workSheet.Cells[row, col].Text;
                }
                dt.Rows.Add(newRow);
            }

            dt.Columns.RemoveAt(0); //remove No
            dt.Columns.RemoveAt(0); //remove article

            //Get BookCode
            using (SqlConnection conn = new SqlConnection("Server"))
            using (SqlCommand cmd = new SqlCommand(null, conn))
            {
                StringBuilder sb = new StringBuilder("WITH cte AS(SELECT case WHEN InvtID IS NULL OR InvtID='' THEN 'No Bookcode Found' ELSE InvtID END AS InvtID,Barcode,ROW_NUMBER() OVER(PARTITION BY Barcode ORDER BY InvtID Asc) rid FROM InventoryCustomer) SELECT InvtID AS BOOKCODE FROM cte WHERE rid=1 and Barcode In (");
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (i != 0) sb.Append(",");
                    string name = "@P" + i;
                    cmd.Parameters.AddWithValue(name, dt.Rows[i]["3"]); //"3" is barcode column
                    sb.Append(name);
                }
                sb.Append(")");
                cmd.CommandText = sb.ToString();
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dtInvtID);

                dt.Columns["BOOKCODE"].SetOrdinal(0);

            }


            int dtctr = 0;
            int ctr = 0;
            foreach (DataRow dr in dt.Rows)//inserting the value of your InvtID to dt.Rows
            {
                dtctr += 1;
                ctr = 1;
                foreach (DataRow InvtID in dtInvtID.Rows) //Getting the value of dtInvtID from database
                {
                    if (ctr == dtctr)//Condition when the row position is equal (dt.Rows==dtInvtID.Rows) IF THIS NOT RETURN A REAL POSITION THEN YOU CAN RUN IT IN DEBUG MODE T CHECK
                    {
                        dr["dtInvtID"] = InvtID[0];
                        ctr += 1;
                        break;
                    }
                    ctr += 1;
                }
            }

            dataGridView2.DataSource = dt;
        }

Reminders:

This code has not tested yet. Any wrong result you can check it and run in Debug Mode.

I've seen some interesting problem:

for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (i != 0) sb.Append(",");
                    string name = "@P" + i;
                    cmd.Parameters.AddWithValue(name, dt.Rows[i]["3"]); //"3" is barcode column
                    sb.Append(name);
                }
                sb.Append(")");

Try to Modify it because my answer from above is getting the value of your database. It may cause a redundancy.

Answer 2

you are first inserting new rows into data table with this code.

 dt.Rows.Add(newRow);

the second time you are using an adapter to fill the same data table with below code.

da.Fill(dt);

which insert the rows at the end instead of updating the existing rows in the data table. To align the data into the data table you need to first fill another data table with an adapter then write code using for loop and matching data to update existing or original data table.