Member Avatar for jithusdani

hi folks..

In my application am storing some textbox values to a datatable inside a dataset.Now I want to save the multiple rows of datatable to my sqltable..ll anybody plz help me..

with thanks and regards.
jithesh.P.

  • 3 Contributors
  • 2 Replies
  • 109 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by vuyiswamb

Dani AI

Generated

Two simple, reliable ways to push the rows in a DataTable (inside a DataSet) into a MySQL table: use a DataAdapter (letting ADO.NET generate/execute the INSERTs) or run prepared INSERTs in a loop inside a transaction. asked for code, so minimal examples are below. ’s suggestion to study a book is fine, but these snippets and the linked docs should get a working result quickly.

DataAdapter + CommandBuilder (quick, minimal code):

  • Create a MySqlDataAdapter with a SELECT that includes the table’s primary key.
  • Create a MySqlCommandBuilder bound to that adapter so Insert/Update/Delete commands are generated.
  • Call adapter.Update(dataset, "TableName") — it sends rows whose RowState is Added/Modified/Deleted.
  • Do not call AcceptChanges() before Update (that would clear RowState).
using MySql.Data.MySqlClient;
using System.Data;

var connStr = "server=...;uid=...;pwd=...;database=...;";
using (var conn = new MySqlConnection(connStr))
{
    conn.Open();
    var adapter = new MySqlDataAdapter("SELECT id, col1, col2 FROM MyTable", conn);
    var builder = new MySqlCommandBuilder(adapter);
    adapter.Update(myDataSet, "MyDataTable");
}

Manual prepared INSERTs in a transaction (more control, better for tuning large batches):

  • Prepare one MySqlCommand with parameters, set CommandText once, call Prepare(), then reuse for each Added row.
  • Use a transaction and commit after all inserts.
  • Check each row’s RowState == DataRowState.Added (or call row.SetAdded()).
using (var conn = new MySqlConnection(connStr))
{
    conn.Open();
    using (var tx = conn.BeginTransaction())
    using (var cmd = conn.CreateCommand())
    {
        cmd.Transaction = tx;
        cmd.CommandText = "INSERT INTO MyTable (col1, col2) VALUES (@c1, @c2)";
        cmd.Parameters.Add("@c1", MySqlDbType.VarChar);
        cmd.Parameters.Add("@c2", MySqlDbType.Int32);
        cmd.Prepare();

        foreach (DataRow r in myDataSet.Tables["MyDataTable"].Rows)
        {
            if (r.RowState == DataRowState.Added)
            {
                cmd.Parameters["@c1"].Value = r.IsNull("col1") ? DBNull.Value : r["col1"];
                cmd.Parameters["@c2"].Value = r.IsNull("col2") ? DBNull.Value : r["col2"];
                cmd.ExecuteNonQuery();
            }
        }
        tx.Commit();
    }
}

Quick tips: ensure dataset rows are actually Added (don’t call AcceptChanges prematurely); SELECT used by an auto-command builder must include the PK; use parameters to avoid injection; for very large imports consider LOAD DATA INFILE or MySqlBulkLoader. Connector/NET docs: MySQL Connector/NET documentation. ADO.NET Update reference: DataAdapter.Update Method.

Recommended Answers

All 2 Replies

Member Avatar for LizR

What code do you have to save it so far?

Member Avatar for vuyiswamb

hi

I think it will be important to buy a book on introduction to programming

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.