Server Side Cursors

One of the things that I always found interesting when looking at someone elses code in Classic ADO 2.x was the number of developers that misused the all of the cursor and locking options with a Classic ADO RecordSet when running against Sql Server.  There are some good situations where there is a need for a scrollable updatable server-side cursor, but I would say about 50% of the time that I see one, it is not necessary.  Well, with .NET Whidbey, ADO.NET will have the a scrollable updatable server-side cursor in the framework.  The advantage to the .NET version will be that it is not directly associated with the SqlDataReader or the SqlDataAdapter, so it will be harder to misuse.  This is unlike the situation with Classic ADO 2.x where creating a scrollable updatable server-side cursor was one of several options within the recordset object. 

Warning: Ideally, you would only want to use scrollable updatable server-side cursors when doing programming directly within the database (such as Yukon).  So, don't try this at home without a trained expert standing by........

Here is an example of some code that I wrote to use the SqlResultSet, which is the name of the object that provides the scrollable updatable server-side cursors.  Note that the SqlResultSet is created by a call to the SqlCommand object.  I also thought it interesting that you can't get the number of records back, merely whether or not there are records.  The .HasRows property is good enough for me.

SqlConnection sqlCn = new SqlConnection(strCn);
SqlDataAdapter sqlDa = new SqlDataAdapter(strSql, sqlCn);
SqlCommand sqlCm = new SqlCommand();
SqlResultSet sqlRs;

sqlCm.Connection = sqlCn;
sqlCn.Open();
sqlCm.CommandText = strSql;
sqlCm.CommandType = CommandType.Text;
sqlRs = sqlCm.ExecuteResultSet(ResultSetOptions.Updatable);
if ( sqlRs.HasRows == true )
{
while(sqlRs.Read())
{
//do something.......
sqlRs.Update();
}
}
sqlRs.Close();

Additional info on the SqlResultSet object.

Enjoy.

Wally