Select an entry from table 1 and copy to table 2 in C #

Question

I have a select statement that selects records from table1 and shows in datagridview,

how to copy data shown in datagridview to table2 after select statement. below is my select element code from table1

public void getdata(){ if (txt_Barcode.Text== "" ) { MessageBox.Show ("Please fill in something"); } for (int i = 0; i < dgv_Detail.Rows.Count +1; i++) { da = new SqlDataAdapter("select * from asset where asset_no='" + txt_Barcode.Text + "'", conn); } da.Fill(Assetdt); } #endregion public void fillds() { dgv_Detail.DataSource = Assetdt; } 

to trigger an event

  private void txt_Barcode_KeyDown(object sender, KeyEventArgs e) { if (e.KeyData == Keys.Enter) { getdata(); fillds(); insertbar(); } } 

Does anyone know how to do this?

Answer 1

First move your data from the Data Grid view to datatable as:

  DataTable dt = new DataTable(); DataColumn[] dcs = new DataColumn[]{}; foreach (DataGridViewColumn c in dgv.Columns) { DataColumn dc = new DataColumn(); dc.ColumnName = c.Name; dc.DataType = c.ValueType; dt.Columns.Add(dc); } foreach (DataGridViewRow r in dgv.Rows) { DataRow drow = dt.NewRow(); foreach (DataGridViewCell cell in r.Cells) { drow[cell.OwningColumn.Name] = cell.Value; } dt.Rows.Add(drow); } 

Then move the data from the datatable table to sql using the foreach loop on datable.rows as:

 foreach(DataRow dr in dt.Rows) { Product product = new Product(); // make seperate class with same feilds as sql table //Get the column values product.productId = dr["productId"]; product.productName = dr[ProductName"]; Product.Insert();//Insert will have inserting records into the dabtase } 

The product is a separate class like:

 public class Product { public int productId { get; set; } public string productName { get; set; } } 

Answer 2

One way to do this is to run a "SELECT INTO Table2" query after displaying the data.

In practice, after posting data to Datagridview, you do not need to read the data, but you can reuse your request.

 for (int i = 0; i < dgv_Detail.Rows.Count +1; i++) { string sqlQuery = "FROM asset where asset_no='" + txt_Barcode.Text + "'"; string sqlInsertQuery = "SELECT INTO Table2 " + sqlQuery; da = new SqlDataAdapter("SELECT * " + sqlQuery, conn); //Run ExecuteNonQuery using sqlInsertQuery } 

Learn more about SELECT INTO http://www.w3schools.com/sql/sql_select_into.asp

To learn more about Execute Non Query http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx

Answer 3

use SqlCommandBuilder to generate the insert command

  DataTable dtSource = null; //populate or assign dtSouurce here string connectionString = null; //assign your connection string here var tableName = "TABLE2"; using (var connection = new SqlConnection(connectionString)) { var adapter = new SqlDataAdapter(); adapter.SelectCommand = new SqlCommand(tableName, connection); adapter.SelectCommand.CommandType = CommandType.TableDirect; var builder = new SqlCommandBuilder(adapter); connection.Open(); var dataSet = new DataSet(); adapter.FillSchema(dataSet, SchemaType.Mapped); builder.GetInsertCommand(); foreach (DataRow row in dtSource.Rows) { dataSet.Tables[tableName].Rows.Add(row.ItemArray); //dataSet.Tables[tableName].Rows.Add(new object[]{ row.ItemArray[0], row.ItemArray[2], "Some Else Values" }); } adapter.Update(dataSet, tableName); connection.Close(); } 

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.aspx