How do I take datatable values into postgresql
-
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();
}
}(One problem is that) You're creating a new connection for every row in the data table.
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
-
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();
}
}Well, first, you're creating a new command and parameter for every record in the table. You don't need to do that and not doing it will improve performance. Next, for every row in your Rows collection, you're treating each row as if it has a table itself, and only every looking at the first row and column of that table, no matter which record you're looking at. This makes no sense at all. Lastly, you're only every inserting a single value per row into your new table. Is this correct? What is the datatype of the values you're sending to the new table? What datatype does the new table expect for that value? For example, you cannot send a string to a column that is expecting numbers, either integers or decimal values.
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak -
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();
}
} -
(One problem is that) You're creating a new connection for every row in the data table.
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
-
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();
}
}Member 14103987 wrote:
but I seem to do something wrong.
It would help if you explained why it is 'wrong'. Is there an error? Or does the data just not show up. Your code is attempting to insert one value into a table one row at a time. That will only work if the table only has one column. You can insert just one value, depending on how the table is created, but in that case your 'Insert' must define that column. The form would look like the following
Insert into MyTable (MyRow1) values(1)
And your code does not have the '(MyRow1)' part. Second possibility is that because you say 'ToString()' that means the type of num1 is a String. Exactly as you requested. But because you named it 'num1' it seems likely that you actually want a number. And very possible that AddWithValue() is then translating that to a string value for the database. So if your 'MyRow1' is a database type of 'int' but your code is creating the following, because 'num1' is a string, it will not work. (Notice the ticks around the number.)
Insert into MyTable values('1')
-
Depends on your definition of "run" and "certain". Different standards obviously.
"Before entering on an understanding, I have meditated for a long time, and have foreseen what might happen. It is not genius which reveals to me suddenly, secretly, what I have to say or to do in a circumstance unexpected by other people; it is reflection, it is meditation." - Napoleon I
-
Member 14103987 wrote:
but I seem to do something wrong.
It would help if you explained why it is 'wrong'. Is there an error? Or does the data just not show up. Your code is attempting to insert one value into a table one row at a time. That will only work if the table only has one column. You can insert just one value, depending on how the table is created, but in that case your 'Insert' must define that column. The form would look like the following
Insert into MyTable (MyRow1) values(1)
And your code does not have the '(MyRow1)' part. Second possibility is that because you say 'ToString()' that means the type of num1 is a String. Exactly as you requested. But because you named it 'num1' it seems likely that you actually want a number. And very possible that AddWithValue() is then translating that to a string value for the database. So if your 'MyRow1' is a database type of 'int' but your code is creating the following, because 'num1' is a string, it will not work. (Notice the ticks around the number.)
Insert into MyTable values('1')
Thanks for the suggestions. Here is an update that would explain everything in more detail. I have a datatable with values, the first column will always be a string and the rest (5 columns) will have numbers. I have used the code that I shared to make it work for a
datagridviewbut now I want to make it work for adatatable. The way I though was best was to take each row into the postgresql server and store it until its needed later. It seems like asqladapterseems to be a better way to handle this and therefore I had some help trying to figure this out with the following code:using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter("SELECT num1, num2, num3, num4, num5, num6 FROM " + Table + ";", Conn))
{
NpgsqlCommand insert = new NpgsqlCommand("INSERT INTO " + Table + " 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);}
}The code seems to work until the actual strings/values need to be put inside the SQL server table(see inside of code to know where the code breaks out). If anyone knows how to make the code work or is willing to show whats missing I would gladly appreciate it.
-
Thanks for the suggestions. Here is an update that would explain everything in more detail. I have a datatable with values, the first column will always be a string and the rest (5 columns) will have numbers. I have used the code that I shared to make it work for a
datagridviewbut now I want to make it work for adatatable. The way I though was best was to take each row into the postgresql server and store it until its needed later. It seems like asqladapterseems to be a better way to handle this and therefore I had some help trying to figure this out with the following code:using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter("SELECT num1, num2, num3, num4, num5, num6 FROM " + Table + ";", Conn))
{
NpgsqlCommand insert = new NpgsqlCommand("INSERT INTO " + Table + " 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);}
}The code seems to work until the actual strings/values need to be put inside the SQL server table(see inside of code to know where the code breaks out). If anyone knows how to make the code work or is willing to show whats missing I would gladly appreciate it.
If the last 5 columns are numbers, why is your parameter code treating them all as strings?
Asking questions is a skill CodeProject Forum Guidelines Google: C# How to debug code Seriously, go read these articles.
Dave Kreskowiak -
Thanks for the suggestions. Here is an update that would explain everything in more detail. I have a datatable with values, the first column will always be a string and the rest (5 columns) will have numbers. I have used the code that I shared to make it work for a
datagridviewbut now I want to make it work for adatatable. The way I though was best was to take each row into the postgresql server and store it until its needed later. It seems like asqladapterseems to be a better way to handle this and therefore I had some help trying to figure this out with the following code:using (NpgsqlDataAdapter adapter = new NpgsqlDataAdapter("SELECT num1, num2, num3, num4, num5, num6 FROM " + Table + ";", Conn))
{
NpgsqlCommand insert = new NpgsqlCommand("INSERT INTO " + Table + " 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);}
}The code seems to work until the actual strings/values need to be put inside the SQL server table(see inside of code to know where the code breaks out). If anyone knows how to make the code work or is willing to show whats missing I would gladly appreciate it.
Providing the details of the exception would have helped!
Member 14103987 wrote:
DataTable table = new DataTable();
That creates a new empty
DataTable, with no rows.Member 14103987 wrote:
adapter.FillSchema(table, SchemaType.Source);
That configures the columns of the table. There are still no rows in the table.
Member 14103987 wrote:
DataRow row = table.NewRow();
That creates a new row with the same schema as the table. There are still no rows in the table.
Member 14103987 wrote:
row.Table.Rows[0][0]
This attempts to retrieve the value of the first column of the first row of a table with no rows. Unsurprisingly, this throws an
IndexOutOfRangeExceptionwith the message "There is no row at position 0." You cannot copy data that doesn't exist from an emptyDataTableto itself. You need to work out precisely where the source data is stored, and copy from that instead.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
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();
}
}This line seems very strange:
object num1 = row.Table.Rows[0][0].ToString();
as you're always using the first row of the underlying table (and thus will always get just the value of its first column inserted). IMHO this should just read
object num1 = row[0].ToString();
Would that help?