Adding Rows to Database Tables Using ADO.NET
The process of adding data to a table is closely related. You generally start by getting the current version of the table from the database. If it is very large, you can get only the empty table by getting just its schema. We follow these steps.
1. Create a DataTable with the name of the table in the database.
2. Add it to a dataset.
3. Fill the dataset from the database.
4. Get a new row object from the DataTable.
5. Fill in its columns.
6. Add the row to the table.
7. When you have added all the rows, update the database from the modified DataTable object.
The process looks like this.
DataSet dset = new DataSet(tableName); //create the data set
dtable = new DataTable(tableName); //and a datatable
dset.Tables.Add(dtable); //add to collection
conn = db.getConnection();
openConn(); //open the connection
OleDbDataAdapter adcmd = new OleDbDataAdapter();
//open the table
adcmd.SelectCommand =
new OleDbCommand("Select * from " + tableName, conn);
OleDbCommandBuilder olecb = new OleDbCommandBuilder(adcmd);
adcmd.TableMappings.Add("Table", tableName);
//load current data into the local table copy
adcmd.Fill(dset, tableName);
//get the Enumerator from the Hashtable
IEnumerator ienum = names.Keys.GetEnumerator();
//move through the table, adding the names to new rows
while (ienum.MoveNext()) {
string name = (string)ienum.Current;
row = dtable.NewRow(); //get new rows
row[columnName] = name;
dtable.Rows.Add(row); //add into table
}
//Now update the database with this table
try {
adcmd.Update(dset);
closeConn();
filled = true;
}
catch (Exception e) {
Console.WriteLine (e.Message);
}
It is this table editing and update process that is central to the ADO style of programming. You get the table, modify the table, and update the changes back to the database. You use this same process to edit or delete rows, and updating the database makes these changes as well.
Shashi Ray