datatable保存mysql,如何将数据从数据表保存到数据库

DataSet ds = new DataSet();

DataTable dt = new DataTable();

dt.TableName = "MyTable";

foreach (DataGridViewColumn col in dataGridView1.Columns)

{

dt.Columns.Add(col.DataPropertyName, col.ValueType);

}

foreach (DataGridViewRow gridRow in dataGridView1.Rows)

{

if (gridRow.IsNewRow)

continue;

DataRow dtRow = dt.NewRow();

for (int i1 = 0; i1 < dataGridView1.Columns.Count; i1++)

dtRow[i1] = (gridRow.Cells[i1].Value == null ? DBNull.Value : gridRow.Cells[i1].Value);

dt.Rows.Add(dtRow);

}

ds.Tables.Add(dt);

System.Diagnostics.Debugger.Break();

This is my code and I need to insert the datatable into "MyTable" and the Columns will be dynamic,but I cant be creating columns all the time.

Please can any one tell me how to do this?

解决方案

For any given requirement, you will need to specify the column definition. This will map to the corresponding table in the database. Else how will you specify which column's value in the datatable maps to which column in the physical table?

Ideally the mapping should be 1:1 based on exact names. Like the name of datatable corresponds to the name of physical table and each column in datatable named after same column in physical table.

You can iterate over your datasource and create columns by index, but then you must be sure that the insert query takes the columns in same order as you are inserting in the datatable.

The AutoGenerateColumns property of the grid works in this way only, although in reverse order