Using ADO.NET with SQL Server

Wrapping Up

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

You might also like...

Comments

About the author

James Crowley

James Crowley United Kingdom

James first started this website when learning Visual Basic back in 1999 whilst studying his GCSEs. The site grew steadily over the years while being run as a hobby - to a regular monthly audien...

Interested in writing for us? Find out more.

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“It works on my machine.” - Anonymous