Inserting just a new record using DataSet (.net c# ado.net )

Question

I've a form containing more than 200 text boxes in .NET desktop application. I want to insert them into their respective 4 tables of Database. Now how should I go about it using Dataset & DataAdapter to do this?

I mean usually this will be the flow:

1. Populate the DataSet using DataAdapter.Fill(Dataset,"DataTable");
2. manipuate the data of DataSet
3. DataAdapter.Update(Dataset,"DataTable") updates the content back to Database.

Code:http://dev.mysql.com/doc/refman/5.1/en/connector-net-tutorials-intro.html#connector-net-tutorials-data-adapter

But here I just want to insert a new record in 3 different tables.

I think the way is to 1. Programatically Create A dataset with 3 Datatables. 2. Bind 200 Textboxes to respective columns of these Datatabes 3. dataAdapter.Update(dataSet, dataTable.TableName);

Am I right?

How does dataAdapter.Update(dataSet, dataTable.TableName); work? Because, my each dataTable will have only one record. (the new record that is to be inserted. binded with those 200 TextBoxes of the form) where as the Table of the Database will have thousands of records. If I do dataAdapter.Update(dataSet, dataTable.TableName); will it delete all the rest of the records and insert this one alone?

I just want to insert a new record (without fetching other 1000s of records into my Dataset) into Database.Table using Dataset.

Answer 1

Each row has a RowState Property, this will be used by the dataAdapter. So if your dataset has only one new row, the row state will be DataRowState.Added. The DataAdapter will insert the row and change the row state to DataRowState.Unchanged. All other rows in the database will be unchanged.

IDbTransaction dbTransaction = dbConnection.BeginTransaction();
try
{
  foreach (DataTable dataTable in dataSet.Tables)
  {
    string sql = "SELECT * FROM " + dataTable.TableName + " WHERE 0 = 1";
    SqlDataAdapter dataAdapter = new SqlDataAdapter (sql, dbConnection);
    dataAdapter.Update(dataSet, dataTable.TableName);
  }
}
catch
{
  dbTransaction.Rollback();
  throw;
}
dbTransaction.Commit();

Remark: Will not work if there are constraints in the database defined.

Answer 2

Simply make the SQL insert commands and execute them:

string cmd="insert into myTable(column1, column2, etc) values (@text1,@text2, etc)";
SqlCommand sqlcmd=new SqlCommand(cmd,mySqlConnection);
sqlcmd.Parameters.Add("@text1",SqlDbType.NChar,textbox1.Text);
sqlcmd.Parameters.Add("@text2",SqlDbType.NChar,textbox2.Text);
sqlcmd.ExecuteNonQuery();