Question How to take datatable values into postgresql

[Sketchup Galaxy]

I´m trying to take the entire datatable with its values to postgresql but I seem to do something wrong. Code is as follows:

foreach (DataRow row in dt_IS.Rows)
{
    using (NpgsqlCommand cmdCopy = new NpgsqlCommand("INSERT INTO " + Table + " VALUES(@num1));", Conn))
    {
        object num1 = row.Table.Rows[0][0].ToString();
        cmdCopy.Parameters.AddWithValue("@num1", num1);
        Conn.Open();
        cmdCopy.ExecuteNonQuery();
        Conn.Close();
    }
}

 

[Skydiver]

Welcome to the forums. In the future, please post your code in code tags. The easiest way to do that is to use the button on the toolbar that looks like </>.

Moving this question out of WinForms and into something more database oriented.

 

[Skydiver]

but I seem to do something wrong.

What error are you getting? If you aren't getting an error, what behavior were you expecting and what behavior are you actually seeing?

If you are getting an error, it's likely because you have an extra closing parenthesis in your SQL statement on line 3.

I´m trying to take the entire datatable with its values

The queries that you are doing in your code does not pull values. It inserts values. If you need to take values, you'll likely want to use some kind of SELECT statement, rather an INSERT statement.

 

[jmcilhinney]

You should be using a data adapter, calling Update and passing the DataTable as an argument. You'll need to assign an appropriate command to the InsertCommand property if you want rows to be inserted and the RowState of each row must be Added.

 

[Skydiver]

Ah! That's what the OP meant by "take datatable values to PostgreSQL"...

 

[jmcilhinney]

Here's an example I wrote some time ago for inserting rows into a database:

using (SqlConnection connection = new SqlConnection("connection string here"))
using (SqlDataAdapter adapter = new SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem",
                                                   connection))
{
    SqlCommand insert = new SqlCommand("INSERT INTO StockItem (Name, Quantity, Unit) VALUES (@Name, @Quantity, @Unit)",
                                       connection);
 
    insert.Parameters.Add("@Name", SqlDbType.VarChar, 100, "Name");
    insert.Parameters.Add("@Quantity", SqlDbType.Float, 8, "Quantity");
    insert.Parameters.Add("@Unit", SqlDbType.VarChar, 10, "Unit");
 
    adapter.InsertCommand = insert;
    adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
 
    DataTable table = new DataTable();
 
    // Generate the DataTable schema.
    adapter.FillSchema(table, SchemaType.Source);
 
    // Add the new rows to the DataTable, e.g.
    DataRow row = table.NewRow();
 
    row["Name"] = someName;
    row["Quantity"] = someQuantity;
    row["Unit"] = someUnit;
    table.Rows.Add(row);
 
    // Save the changes.
    adapter.Update(table);
}

That's for SQL server but the principles are the same for any database. You might vary some of the details, e.g. you might generate the DataTable schema manually or have it generated by another data adapter when retrieving data from another source. When you add rows yourself, they will be Added by default. If you use another data adapter to populate the DataTable, you need to set its AcceptChangesDuringFill property to false or else AcceptChanges will be called and all RowStates set to Unchanged.

 

[Sketchup Galaxy]

@jmcilhinney and @Skydiver Thank you for the suggestions. I´m totally new to coding and i´m self learning all of this. Seems like I have been unclear what i´m trying to do so here is hopefully a better explanation.
Basically I have a datatable that already has values inside of it. Those values need to be stored inside my postgresql server. The error that I get it that after I come to cmdCopy.Parameters.AddWithValue("@num1", num1); the code does not continue but goes back and takes a new Table that is available.

I have now tried to implement the code that @jmcilhinney shared to hopefully work for me but i still get the same problem, meaning when trying to take the value from my datatable to postgresql server it ignores it and takes a new table.
This is my try:

using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter("SELECT num1, num2, num3, num4, num5, num6 FROM " + TickerName + ";", Conn))
  {
    NpgsqlCommand insert = new NpgsqlCommand("INSERT INTO " + TickerName + " VALUES (@num1, @num2, @num3, @num4, @num5, @num6);", Conn);
    insert.Parameters.Add("@num1", NpgsqlDbType.Text, 200, "1");
    insert.Parameters.Add("@num2", NpgsqlDbType.Text, 50, "2");
    insert.Parameters.Add("@num3", NpgsqlDbType.Text, 50, "3");
    insert.Parameters.Add("@num4", NpgsqlDbType.Text, 50, "4");
    insert.Parameters.Add("@num5", NpgsqlDbType.Text, 50, "5");
    insert.Parameters.Add("@num6", NpgsqlDbType.Text, 50, "6");

    adapter.InsertCommand = insert;
    adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

    DataTable table = new DataTable();

    // Generate the DataTable schema.
    adapter.FillSchema(table, SchemaType.Source);

    // Add the new rows to the DataTable, e.g.
    DataRow row = table.NewRow();
    
    //Where I´m unsure what to do. This is obivously not the way to write the code but would appreciate it if I get some help to write this the right way
    row["1"] = row.Table.Rows[0][0].ToString(); //Does not continue after this line
    row["2"] = row.Table.Rows[0][1].ToString();
    row["3"] = row.Table.Rows[0][2].ToString();
    row["4"] = row.Table.Rows[0][3].ToString();
    row["5"] = row.Table.Rows[0][4].ToString();
    row["6"] = row.Table.Rows[0][5].ToString();
    
    table.Rows.Add(row);

    // Save the changes.
    adapter.Update(table);
  }
}

 

[cjard]

You might find it simpler to use a CommandBuilder, or even simpler, connect a new dataset type object to your database and add a tableadapter for the relevant table

It's quite unusual to use datatable for new work with a database; theyve largely been superceded by newer tech. Have you got a requirement to use one from somewhere specific?