- <p>For Step 3, we need someone to start the FreeTDS .NET Data Provider so
- we can have data access to Microsoft SQL Server and Sybase databases, or either
- add the support in libgda.
-
- <p>For Step 4, we need someone to start the unixODBC .NET Data Provider, or add
- the support in libgda.
-
- <p>For Step 5, we have just begun creating a Mono.Data .NET Provider - a MySQL
- .NET Provider that uses the MySQL C Client Library. This provider is
- found in Mono.Data.MySql. We can currently connect and do a SQL INSERT and insert
- a row into a MySQL database. However, it currently only works on Cygwin because
- the MySQL client library libmySQL.dll is different
- from the library on Linux libmysqlclient.dll. Another problem, mysql thread functions
- do not load for some reason. Also, the provider only runs if you use "mint" (the Mono
- runtime interpreter). It does not work on "mono" (the Mono Just-In-Time compiler).
- The C# Bindings to MySQL are thanks to Brad Meril.
-
- <p>We are able to do simple CREATE TABLE, DROP TABLE, UPDATE, INSERT, and
- DELETE SQL commands using the ExecuteNonQuery method in SqlCommand.
-
- <p>We can execute multiple queries and do a NextResult() in SqlDataReader()
- to get the next result set.
-
- <p>We are also able to do simple aggregate functions,
- ie, count(), sum(), min(), and max()
- in a simple SELECT SQL query using the ExecuteScalar() now.
-
- <p>We are also able to retrieve data with a simple SELECT SQL query
- using ExecuteReader() which returns a SqlDataReader. We are able to
- use GetSchemaTable() to get the meta data about the table columns.
- We are able to Read() to get each row from the result set.
-
- <p>Here is a sample of code that is based on PostgresTest.cs and
- TestSqlDataReader.cs tests:
-<pre>
-
- static void SelectData (IDbConnection cnc) {
-
- IDbCommand selectCommand = cnc.CreateCommand();
- IDataReader reader;
-
- selectCommand.CommandType = CommandType.Text;
- selectCommand.CommandText =
- "select * from pg_user;" + \r
- "select * from pg_tables;" + \r
- "select * from pg_database";\r
-
- reader = selectCommand.ExecuteReader ();
-
- do {\r
- // get the DataTable that holds\r
- // the schema\r
- DataTable dt = rdr.GetSchemaTable();\r
-\r
- if(rdr.RecordsAffected != -1) {\r
- // Results for \r
- // SQL INSERT, UPDATE, DELETE Commands \r
- // have RecordsAffected >= 0\r
- Console.WriteLine("Result is from a SQL Command (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);\r
- }\r
- else if (dt == null)\r
- Console.WriteLine("Result is from a SQL Command not (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);\r
- else {\r
- // Results for\r
- // SQL not INSERT, UPDATE, nor DELETE\r
- // have RecordsAffected = -1\r
- Console.WriteLine("Result is from a SQL SELECT Query. Records Affected: " + rdr.RecordsAffected);\r
- \r
- // Results for a SQL Command (CREATE TABLE, SET, etc)\r
- // will have a null reference returned from GetSchemaTable()\r
- // \r
- // Results for a SQL SELECT Query\r
- // will have a DataTable returned from GetSchemaTable()\r
-\r
- results++;\r
- Console.WriteLine("Result Set " + results + "...");\r
- \r
- // number of columns in the table\r
- Console.WriteLine(" Total Columns: " +\r
- dt.Columns.Count);\r
-\r
- // display the schema\r
- foreach (DataRow schemaRow in dt.Rows) {\r
- foreach (DataColumn schemaCol in dt.Columns)\r
- Console.WriteLine(schemaCol.ColumnName + \r
- " = " + \r
- schemaRow[schemaCol]);\r
- Console.WriteLine();\r
- }\r
-\r
- int nRows = 0;\r
- string output, metadataValue, dataValue;\r
- // Read and display the rows\r
- Console.WriteLine("Gonna do a Read() now...");\r
- while(rdr.Read()) {\r
- Console.WriteLine(" Row " + nRows + ": ");\r
- \r
- for(c = 0; c < rdr.FieldCount; c++) {\r
- // column meta data \r
- DataRow dr = dt.Rows[c];\r
- metadataValue = \r
- " Col " + \r
- c + ": " + \r
- dr["ColumnName"];\r
- \r
- // column data\r
- if(rdr.IsDBNull(c) == true)\r
- dataValue = " is NULL";\r
- else\r
- dataValue = \r
- ": " + \r
- rdr.GetValue(c);\r
- \r
- // display column meta data and data\r
- output = metadataValue + dataValue; \r
- Console.WriteLine(output);\r
- }\r
- nRows++;\r
- }\r
- Console.WriteLine(" Total Rows: " + \r
- nRows);\r
- } \r
- } while(rdr.NextResult());\r
- Console.WriteLine("Total Result sets: " + results);\r
- \r
- rdr.Close();\r
- }
-
-</pre>
-
- <p>We are able to get
- String data (char, character, text, varchar), Int16 (smallint),
- Int32 (integer), Int64 (bigint), DateTime (time, date, timestamp),
- Boolean (boolean), Single (float), and Double (double).
- More data types will come later. Note, the types that do work still
- need thorough testing.
-
- <p>Rows that are returned which contain columns that are NULL are handled now.
- The SqlDataReader method IsDBNull() needs to be called to determine
- if a field IS NULL before trying to read data from that field.
-
- <p>Calling PostgreSQL stored procedures works. It does not work perfectly. It may not
- even work to specification - yet. If you want to test it yourself, look at
- TestSqlDataReader.cs or PostgresTest.cs in
- mcs/class/System.Data/Test.
-
- <p>Below, I have some sample code you can
- use to call a PostgreSQL stored procedure named "version". This stored
- procedure returns a string containing the PostgreSQL server version. Notice
- the CommandType is StoredProcedure and the method ExecuteScalar() is called.
-
- <p>ExecuteScalar() is a lightweight method in class SqlCommand that only returns
- one row and one column as one object - even if there is more than row or column.
-
-<pre>
- static string GetDatabaseServerVersion (SqlConnection cnc)
- {
- SqlCommand cmd = cnc.CreateCommand ();
- string data;
-
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.CommandText = "version";
-
- data = (string) cmd.ExecuteScalar ();
-
- return data;
- }
-</pre>
-
- <p>We have the beginnings of Parameters support PostgreSQL. Only
- Input Parameters are currently supported. Output, Input/Output,
- and Return parameters still need to be done.
-
- <p>A lot of functionality in System.Data is missing, but the
- infrastructure is starting to come together.
-
- <p>A lot of Exceptions need to be thrown for various exceptions. However,
- SqlException, SqlErrorCollection, and SqlError have been partially
- implemented.
-
- <p>Tim Coleman and Rodrigo Moya got the beginnings of the
- SqlDataAdapter/DataSet/DataTable/DataRow to work. Currently,
- the SqlDataAdapter can Fill() relational data into a DataTable in a DataSet.
- See the test mcs/class/System.Data/Test/TestSqlDataAdapter.cs to see it in action.
- Below, I show a snippets from the test:
-
-<pre>
- string connectionString;
- string sqlQuery;
- SqlDataAdapter adapter;
- DataSet dataSet = null;
-
- connectionString =
- "host=localhost;" +
- "dbname=test;" +
- "user=postgres";
-
- sqlQuery = "select * from pg_tables";
-
- adapter = new SqlDataAdapter (sqlQuery,
- connectionString);
-
- dataSet = new DataSet ();
-
- adapter.Fill (dataSet);