Save a DataTable into a database table

I am generating a Data Table from my application and I would like to save the whole Data Table into one database table.

DataTable ds = //add the info from the queue in the application

The DataTable is getting generated but What to do next.Show me some syntax.I dont really need the select statement there either, i just want to insert all the info from the DataTable into already created db table(update the table). I will use the ODBC connection to access the MYSQL database

i want to update the data into the database through dataset directly

public void update(DataTable ds)
{
  try
  {  
    lock (myLockHolder)
    {
      X1 = 1;
      OdbcConnection con = 
      new OdbcConnection(LocalConnection.GetLocalConnetionString());
      OdbcCommand cmd;
      OdbcDataAdapter da;
      DataSet ds1=new DataSet();
      string query = "";
      query = "update parameter" + Environment.NewLine;
      query += "set paramvalue=paramvalue,date_logged1=date_logged1," 
     开发者_开发知识库   + Environment.NewLine;
      query += "  Quality=Quality,date_logged=date_logged" 
        + Environment.NewLine;
      query += " where itemID=itemID";
      cmd = new OdbcCommand(query, con);
      da = new OdbcDataAdapter(cmd);
      ds1=new DataSet();
      ds1.Tables.Add(ds);
      da.Update(ds1);                   

    }
  }
  catch { }
  finally { }
}

It will be used like this method catch this exception "Update unable to find TableMapping['Table'] or DataTable 'Table'."

---

You should create insert sql string, loop thorugh all rows in your datatable, add parameters for each column in datatable, and execute this query.

string sql = "INSERT INTO T (A, B, C) VALUES (@A, @B, @C)"; 
using (SqlConnection conn = new SqlConnection(connectionString))
{   
   conn.Open();   
   foreach (DataRow r in myTable.Rows)   
   {      
       SqlCommand cmd = conn.CreateCommand();      
       cmd.CommandText = sql;      
       cmd.Parameters.AddWithValue("@A", r["A"]);      
       cmd.Parameters.AddWithValue("@B", r["B"]);      
       cmd.Parameters.AddWithValue("@C", r["C"]);      
       cmd.ExecuteNonQuery();   
   }
}

---

using (SqlConnection conn = new SqlConnection(connectionString))
{   
  conn.Open();  
  foreach (DataRow r in dataTable2.Rows)
  {
    string sql = "INSERT INTO BALREP (ACODE, ANAME, QTY) VALUES ('" +r["CODE"] + "', '" + r["NAME"] + "', '" + r["CELL"] + "')";
    SqlCommand cmd2 = con.CreateCommand();
    cmd.CommandText = sql;
    cmd.ExecuteNonQuery();
  }
}

---

You wrote: >i want without foreach loop

Try to use MySqlDataTable class in dotConnect for MySQL. Read more about the MySqlDataTable.Update() method and MySqlDataTable.CachedUpdates state.

---

dt.Rows.Clear();
        try { 
        SqlConnection con = new SqlConnection(ConnectDb.connectionstring);

        SqlDataAdapter adp = new SqlDataAdapter("SELECT * FROM Admission",con);
        con.Open();
        adp.Fill(dt);

        SqlConnection con2 = new SqlConnection(ConnectDb.onlinestring);
        con2.Open();


        foreach (DataRow r in dt.Rows)
        {
            SqlCommand check_User_Name = new SqlCommand("SELECT COUNT(*) FROM [Admission] WHERE ([studentName] = @SName)", con2);
            check_User_Name.Parameters.AddWithValue("@SName", r["studentName"]);
            int UserExist = (int)check_User_Name.ExecuteScalar();

            if (UserExist > 0)
            {
                //Username exist
                //MessageBox.Show("User Name is Already here");

            }
            else {
            SqlCommand cmd = new SqlCommand("INSERT INTO Admission(studentName, age, class, subject)VALUES(@name, @age, @class, @subject)", con2);
            cmd.Parameters.AddWithValue("@name", r["studentName"]);
            cmd.Parameters.AddWithValue("@age", r["age"]);
            cmd.Parameters.AddWithValue("@class", r["class"]);
            cmd.Parameters.AddWithValue("@subject", r["subject"]);
            cmd.ExecuteNonQuery(); 
        }

        }
            }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
    }