[Mono-list] Status update on SqlClient

[tim@timcoleman.com]

A lot of work has been going on with the System.Data.SqlClient this
week, and things are really coming together.

In addition to lots of updates happening in the System.Data.SqlClient
namespace, Ville Palo has been doing a lot of work with the SqlTypes
classes that are essential to working with native SQL Server datatypes.
Gonzalo has been pitching in here too, as he does everywhere else in
Mono.

I can't believe how many changes have been done in the last week to
this namespace under heavy development.  Some of the highlights:
	* SqlCommand is almost done
	* Added the ability to get key information from database
	* Lots of work on the SqlCommandBuilder
	* The SqlDataAdapter is now working, more or less.  If you
	  don't know what this is, and you're interested in ADO.NET,
	  you should really check it out.  It is very cool.
	* Many, many bugfixes and changes to bring the interface
	  in-line with .NET

Still left to do: 
	* There are many things to do with the data adapter.  This is 
	  my primary interest at the moment.  
	* Testing, testing, testing.  I'm not sure how we should create
	  test suites for databases, because not everyone has a database
	  to test against.  Any ideas?
	* Some of the classes in System.Data need to be more fully 
	  implemented.
	* Much, much more!

I'll leave you with a (slightly lengthy) code example.  It demonstrates
using the SqlDataAdapter to update a row in the database.  It touches
on a number of classes, so it's a pretty good example of how far we
have come.  If you have a SQL Server database, you can run it today!

--8<----------------------------------------------------------

string connectionString = "... Your SQL Server connection string ...";
SqlConnection connection = new SqlConnection (connectionString);
connection.Open ();

SqlCommand command = connection.CreateCommand ();

// Create a new table for testing
command.Transaction = connection.BeginTransaction ();
command.CommandText = "CREATE TABLE monotest (column1 INT); INSERT INTO monotest VALUES (31337)";
command.ExecuteNonQuery ();
command.Transaction.Commit ();

command.CommandText = "SELECT * FROM monotest";
SqlDataAdapter adapter = new SqlDataAdapter (command);
SqlCommandBuilder builder = new SqlCommandBuilder (adapter);
DataTable dataTable = new DataTable ("monotest");

adapter.Fill (dataTable);

// Should output "31337"
foreach (DataRow dataRow in dataTable.Rows) {
	Console.WriteLine (dataRow[0]);
}

// Change row 1, column 1 to "123".
dataTable.Rows[0][0] = 123;

// Commit changes back to database
int updateCount = adapter.Update (dataTable);

// Should be 1
Console.WriteLine ("{0} row(s) affected by update.", updateCount);

SqlDataReader reader = command.ExecuteReader ();
if (!reader.Read ())
	throw new Exception ("Could not read.");

// Should output "123"
Console.WriteLine (reader.GetInt32 (0));
reader.Close ();

// Clean up the test table
command.Transaction = connection.BeginTransaction ();
command.CommandText = "DROP TABLE monotest";
command.ExecuteNonQuery ();
command.Transaction.Commit ();
	  
--8<----------------------------------------------------------

I compiled this with 
mcs -r System.Data test.cs

And, when I run it, the output is
31337
1 row(s) affected by update.
123

-- 
Tim Coleman <tim@timcoleman.com>                       [43.43 N 80.45 W]
BMath, Honours Combinatorics and Optimization, University of Waterloo
Software Developer, Global Services, Open Text Corporation
"Under capitalism, man exploits man.  Under communism, it's just the
 opposite." -- J.K. Galbraith