Although the CommandBuilder does a great job on simple tables, when we start using more complex SELECT statements - or simply want more control over what's going on - then its time to specify our own commands for each of the Command properties in the DataAdapter. First, we can specify the SelectCommand as usual, perhaps adding parameters for a Stored Procedure or WHERE clause.

[C#]
// create the data adapter - we'll specify the connection through our SqlCommand object
SqlDataAdapter dataAdapter = new SqlDataAdapter();
SqlCommand sqlSelectCommand = new SqlCommand("SELECT userid,username FROM users",sqlConn);
// assign to the SelectCommand property
dataAdapter.SelectCommand = sqlSelectCommand;

Now, for the INSERT, DELETE and UPDATE statements we write a generalized query with parameters - and then take advantage of another constructor for the SqlParameter object that lets us effectively bind a parameter to a column name (as obviously we won't actually know its value ourselves);

public SqlParameter (System.String parameterName, System.Data.SqlDbType dbType, System.Int32 size, System.String sourceColumn )

So, we can do the following:

[C#]
// create a new Command for our UPDATE statement
SqlCommand sqlUpdateCommand = new SqlCommand("UPDATE users SET username=@username WHERE userid=@id",sqlConn);
// add parameter to this command for @username, and bind it to the column "username"
sqlUpdateCommand.Parameters.Add("@username",SqlDbType.VarChar, 30, "username");
// create another parameter
SqlParameter sqlParam = new SqlParameter("@id",SqlDbType.Integer, 8, "userid");
sqlParam.SourceVersion = DataRowVersion.Original;
// add parameter
sqlUpdateCommand.Parameters.Add(sqlParam);
// assign to UpdateCommand
dataAdapter.UpdateCommand = sqlUpdateCommand;

Now, when the DataAdapter needs to perform an update, it will run the above command - setting @username to the value of the new username in the row that was modified. @userid, however, will be set to the original version of its value, as we set the SourceVersion property of this parameter to DataRowVersion.Original. This is generally a good idea - otherwise if we had for some reason modified the user id then we'd lose our "handle" on the row assuming userid was a primary key, and not update the correct row. If we wanted to access the new version, we would set SourceVersion to DataRowVersion.Current (its default value).

The DeleteCommand and InsertCommand properties of the DataAdapter can be set in a similar manner. Weaning Developers from the CommandBuilder on MSDN provides a good insight into further alternatives to the CommandBuilder.

Adding, Updating & Deleting Rows in a DataSet/DataTable

When we started examining the DataSet object, we skipped over the issue of actually modifying the data in our DataTable object - instead allowing the DataGrid to do the work for us. For sake of completeness, I shall give a brief summary as to how to do it "manually" here.

To add a new row to a DataTable, use the NewRow() method:

[C#]
// get a new row
DataRow newRow = myDataSet.Tables["users"].NewRow();
// set the appropriate fields here... for example
newRow["username"] = "myNewUsername";
// add to the DataTable:
myDataSet.Tables["users"].Rows.Add(newRow);

To update a row in the DataTable, you can simply modify a columns value:

myDataSet.Tables["users"].Rows[rowIndex]["realName"] = "James Crowley";

And finally, to delete one, use the Remove or RemoveAt method:

myDataSet.Tables["users"].Rows.RemoveAt(rowIndex)

or

myDataSet.Tables["users"].Rows.Remove(dataRowObject)

For more information on these, I suggest you take a look at the MSDN article Manipulating Data in a Data Table.

Conclusion

Well, that's pretty much it folks, so I'll just give a very quick overview!

• Establishing a Connection - Use the SqlConnection and OleDbConnection objects
• Running queries - Use the SqlCommand and OleDbCommand objects - using theirExecuteNonQuery, ExecuteScalar and ExecuteReader methods
• Stored procedures - Just the same as above, but add appropriate SqlParameter objects to its Parameters collection
• Fast forward-only data access - Use SqlDataReader and OleDbDataReader
• Disconnected data access - Use a DataAdapter's Fill method to populate a DataSets/DataTable object, and to then reflect changes in the database by calling Update