[Mono-list] SqlClient update

[Tim Coleman]

Update on System.Data.SqlClient.

Many updates have happened in the last week, including
System.Data.SqlClient
and System.Data.SqlTypes.  Ville Palo has done a lot of work with the
SqlTypes, which will be used to provide native datatypes through the
SqlClient.

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

Things to do:
        * Testing, testing, testing.  A question is, how do we implement
          test suites when we don't know if there is even a database
          available?  Any ideas?
        * SqlDataAdapter work will be proceeding more soon.  This is my
          area of interest at the moment.
        * 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 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<----------------------------------------------------------

Compile as 
mcs -r System.Data test.cs

Output:
31337
1 row(s) affected by update.
123

Cheers,

-- 
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