Fill in the database from datatable

Question

HI

I imported the data from an excel file. The file does not contain specified columns, but is variable, and has a maximum of 5 columns. But it can be from 3 columns or 2 to 5 columns. The five columns have names, but the position of the column and the accent changes according to the file. What I want is to transfer data from DataTable to database. The transfer is according to the comparison of the column name and the required value if the column name is in it to the database. If the column name is not present, a "-" symbol is taken and saved in the database

Blockquote

private void SurroundingSub()
{
string parta = null;
string partb = null;
string partc = null;
string partd = null;
int i = 0;
var loopTo = dt2.Rows.Count - 1;
var loopTo1 = loopTo;
for (i = 0; i <= loopTo1; i++)
{
if (dt2 is null)
{
break;
return;
}

    if (object.ReferenceEquals(dt2.Rows(0)("cola").ToString, "cola"))
    {
        parta = dt2.Rows(i)("cola");
    }

    if (object.ReferenceEquals(dt2.Rows(0)("colb").ToString, "colb"))
    {
        partb = dt2.Rows(i)("colb");
    }

    if (object.ReferenceEquals(dt2.Rows(0)("colc").ToString, "colc"))
    {
        partc = dt2.Rows(i)("colc");
    }

    if (object.ReferenceEquals(dt2.Rows(0)("cold").ToString, "cold"))
    {
        partd = dt2.Rows(i)("cold");
    }

    var com = new OleDbCommand("INSERT INTO EXPORT_TB(EXPORT_a,EXPORT_b,EXPORT_c,EXPORT_d) VALUES (@EXPORT_a,@EXPORT_b,@EXPORT_c,@EXPORT_d)", con);
    com.Parameters.AddWithValue("@EXPORT_a", OleDbType.VarChar).Value = parta;
    com.Parameters.AddWithValue("@EXPORT_b", OleDbType.VarChar).Value = partb;
    com.Parameters.AddWithValue("@EXPORT_c", OleDbType.VarChar).Value = partc;
    com.Parameters.AddWithValue("@EXPORT_d", OleDbType.VarChar).Value = partd;
    con.Open();
    com.ExecuteNonQuery();
    con.Close();
}

}

Answer 1

Try something like this:

DataColumn cola = dt2.Columns["cola"];
DataColumn colb = dt2.Columns["colb"];
DataColumn colc = dt2.Columns["colc"];
DataColumn cold = dt2.Columns["cold"];

foreach( var row in dt2.AsEnumerable( ) )
{
   string parta = cola == null ? "-" : Convert.ToString( row[cola] );
   string partb = colb == null ? "-" : Convert.ToString( row[colb] );
   string partc = colc == null ? "-" : Convert.ToString( row[colc] );
   string partd = cold == null ? "-" : Convert.ToString( row[cold] );

   // insert to database

   var com = new OleDbCommand . . .
   . . .
}