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
andOleDbConnection
objects -
Running queries - Use the
SqlCommand
andOleDbCommand
objects - using theirExecuteNonQuery
,ExecuteScalar
andExecuteReader
methods -
Stored procedures - Just the same as above, but add appropriate
SqlParameter
objects to itsParameters
collection -
Fast forward-only data access - Use
SqlDataReader
andOleDbDataReader
-
Disconnected data access - Use a DataAdapter's
Fill
method to populate aDataSets
/DataTable
object, and to then reflect changes in the database by callingUpdate
Comments