question

AMERSAID-7084 avatar image
0 Votes"
AMERSAID-7084 asked Viorel-1 edited

Fill in the database from datatable


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

}

dotnet-csharp
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

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 . . .
    . . .
 }
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.