diff options
11 files changed, 2583 insertions, 53 deletions
diff --git a/mcs/class/System.Data/Test/ProviderTests/ChangeLog b/mcs/class/System.Data/Test/ProviderTests/ChangeLog index a6d6fc22085..3509e2a29df 100644 --- a/mcs/class/System.Data/Test/ProviderTests/ChangeLog +++ b/mcs/class/System.Data/Test/ProviderTests/ChangeLog @@ -1,3 +1,7 @@ +2005-09-16 Senganal T <tsenganal@novell.com> + + * System.Data.Connected.Tests.dll.sources: added more source files + 2005-09-13 Sureshkumar T <tsureshkumar@novell.com> * Makefile: Added reference to Sybase db. diff --git a/mcs/class/System.Data/Test/ProviderTests/Common/ChangeLog b/mcs/class/System.Data/Test/ProviderTests/Common/ChangeLog index 63d82ce2712..c24b8564794 100644 --- a/mcs/class/System.Data/Test/ProviderTests/Common/ChangeLog +++ b/mcs/class/System.Data/Test/ProviderTests/Common/ChangeLog @@ -1,3 +1,12 @@ +2005-09-16 Senganal T <tsenganal@novell.com> + + * DataProvider.cs : Provides data for validation + + DataProvider provides the data that is used for + validating Sq[DataReader. Currently it uses a dataset + to fill data from the database. Will be modified later to + get the values from a config file. + 2005-09-01 Sureshkumar T <tsureshkumar@novell.com> * DBHelper.cs: Added. Class to provide helper methods for other tests. diff --git a/mcs/class/System.Data/Test/ProviderTests/Common/DataProvider.cs b/mcs/class/System.Data/Test/ProviderTests/Common/DataProvider.cs new file mode 100644 index 00000000000..592130b1061 --- /dev/null +++ b/mcs/class/System.Data/Test/ProviderTests/Common/DataProvider.cs @@ -0,0 +1,70 @@ +// +// DataProvider.cs - Holds the data used for Validating Reader Classes +// Author: +// Senganal T (tsenganal@novell.com) +// +// Copyright (c) 2004 Novell Inc., and the individuals listed +// on the ChangeLog entries. +// +// Permission is hereby granted, free of charge, to any person obtaining +// a copy of this software and associated documentation files (the +// "Software"), to deal in the Software without restriction, including +// without limitation the rights to use, copy, modify, merge, publish, +// distribute, sublicense, and/or sell copies of the Software, and to +// permit persons to whom the Software is furnished to do so, subject to +// the following conditions: +// +// The above copyright notice and this permission notice shall be +// included in all copies or substantial portions of the Software. +// +// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, +// EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF +// MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND +// NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE +// LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION +// OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION +// WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. +// + +using System; +using System.Collections; +using System.Data; +using System.Data.SqlClient; + +namespace MonoTests.System.Data +{ + public class DataProvider { + + private DataSet data = null ; + + // TODO : The Data is now got from the Database. + // Needs to be modified to get the data from a config file + public DataProvider () + { + data = new DataSet (); + string query = "Select * from numeric_family order by id ASC;"; + query += "Select * from string_family order by id ASC;"; + query += "Select * from binary_family order by id ASC;"; + query += "Select * from datetime_family order by id ASC;"; + + SqlDataAdapter adapter = new SqlDataAdapter (query, + ConnectionManager.Singleton.ConnectionString); + adapter.TableMappings.Add ("Table", "numeric_family"); + adapter.TableMappings.Add ("Table1", "string_family"); + adapter.TableMappings.Add ("Table2", "binary_family"); + adapter.TableMappings.Add ("Table3", "datetime_family"); + + data.Tables.Add ("numeric_family"); + data.Tables.Add ("string_family"); + data.Tables.Add ("binary_family"); + data.Tables.Add ("datetime_family"); + adapter.Fill (data); + } + + public DataSet GetDataSet () + { + return data ; + } + } +} + diff --git a/mcs/class/System.Data/Test/ProviderTests/System.Data.Connected.Tests.dll.sources b/mcs/class/System.Data/Test/ProviderTests/System.Data.Connected.Tests.dll.sources index 1b414512c56..04ea16a08cc 100644 --- a/mcs/class/System.Data/Test/ProviderTests/System.Data.Connected.Tests.dll.sources +++ b/mcs/class/System.Data/Test/ProviderTests/System.Data.Connected.Tests.dll.sources @@ -1,5 +1,6 @@ Common/ConnectionManager.cs Common/DBHelper.cs +Common/DataProvider.cs System.Data.Odbc/OdbcCommandBuilderTest.cs System.Data.Odbc/OdbcCommandTest.cs System.Data.Odbc/OdbcDataReaderTest.cs @@ -12,3 +13,5 @@ System.Data.SqlClient/SqlCommandTest.cs System.Data.SqlClient/SqlDataReaderTest.cs System.Data.SqlClient/SqlDataAdapterTest.cs System.Data.SqlClient/SqlTransactionTest.cs +System.Data.SqlClient/SqlConnectionTest.cs + diff --git a/mcs/class/System.Data/Test/ProviderTests/System.Data.SqlClient/ChangeLog b/mcs/class/System.Data/Test/ProviderTests/System.Data.SqlClient/ChangeLog index 155e8884489..4c8da99165b 100644 --- a/mcs/class/System.Data/Test/ProviderTests/System.Data.SqlClient/ChangeLog +++ b/mcs/class/System.Data/Test/ProviderTests/System.Data.SqlClient/ChangeLog @@ -1,3 +1,10 @@ +2005-09-16 Senganal T <tsenganal@novell.com> + + * SqlCommandTest.cs: added more testcases for connected mode + * SqlConnectionTest.cs: added more testcases for connected mode + * SqlDataAdapterTest.cs: added more testcases for connected mode + * SqlDataReaderTest.cs: added more testcases for connected mode + 2005-09-01 Sureshkumar T <tsureshkumar@novell.com> * SqlCommandBuilderTest.cs: added tests for SqlCommandBuilder @@ -10,4 +17,3 @@ Added attribute Category ("sqlserver") to all these test fixtures. - diff --git a/mcs/class/System.Data/Test/ProviderTests/System.Data.SqlClient/SqlCommandTest.cs b/mcs/class/System.Data/Test/ProviderTests/System.Data.SqlClient/SqlCommandTest.cs index 7180bd2f038..5aebd703098 100644 --- a/mcs/class/System.Data/Test/ProviderTests/System.Data.SqlClient/SqlCommandTest.cs +++ b/mcs/class/System.Data/Test/ProviderTests/System.Data.SqlClient/SqlCommandTest.cs @@ -4,6 +4,7 @@ // Author: // Umadevi S (sumadevi@novell.com) // Sureshkumar T (tsureshkumar@novell.com) +// Senganal T (tsenganal@novell.com) // // Copyright (c) 2004 Novell Inc., and the individuals listed // on the ChangeLog entries. @@ -42,7 +43,515 @@ namespace MonoTests.System.Data.SqlClient public class SqlCommandTest { - public SqlConnection conn; + public SqlConnection conn = null ; + SqlCommand cmd = null; + string connectionString = ConnectionManager.Singleton.ConnectionString; + + [SetUp] + public void Setup () + { + } + + [TearDown] + public void TearDown () + { + if (conn != null) + conn.Close (); + } + + [Test] + public void ConstructorTest () + { + // Test Default Constructor + cmd = new SqlCommand (); + Assert.AreEqual (String.Empty, cmd.CommandText, + "#1 Command Test should be empty"); + Assert.AreEqual (30, cmd.CommandTimeout, + "#2 CommandTimeout should be 30"); + Assert.AreEqual (CommandType.Text, cmd.CommandType, + "#3 CommandType should be text"); + Assert.IsNull (cmd.Connection, "#4 Connection Should be null"); + Assert.AreEqual (0, cmd.Parameters.Count, + "#5 Parameter shud be empty"); + + // Test Overloaded Constructor + String cmdText = "select * from tbl1" ; + cmd = new SqlCommand (cmdText); + Assert.AreEqual (cmdText, cmd.CommandText, + "#5 CommandText should be the same as passed"); + Assert.AreEqual (30, cmd.CommandTimeout, + "#6 CommandTimeout should be 30"); + Assert.AreEqual (CommandType.Text, cmd.CommandType, + "#7 CommandType should be text"); + Assert.IsNull (cmd.Connection , "#8 Connection Should be null"); + + // Test Overloaded Constructor + SqlConnection conn = new SqlConnection (); + cmd = new SqlCommand (cmdText , conn); + Assert.AreEqual (cmdText, cmd.CommandText, + "#9 CommandText should be the same as passed"); + Assert.AreEqual (30, cmd.CommandTimeout, + "#10 CommandTimeout should be 30"); + Assert.AreEqual (CommandType.Text, cmd.CommandType, + "#11 CommandType should be text"); + Assert.AreSame (cmd.Connection, conn, "#12 Connection Should be same"); + + // Test Overloaded Constructor + SqlTransaction trans = null ; + try { + conn = new SqlConnection (connectionString); + conn.Open (); + trans = conn.BeginTransaction (); + cmd = new SqlCommand (cmdText, conn, trans); + Assert.AreEqual (cmdText, cmd.CommandText, + "#9 CommandText should be the same as passed"); + Assert.AreEqual (30, cmd.CommandTimeout, + "#10 CommandTimeout should be 30"); + Assert.AreEqual (CommandType.Text, cmd.CommandType, + "#11 CommandType should be text"); + Assert.AreEqual (cmd.Connection, conn, + "#12 Connection Should be null"); + Assert.AreEqual (cmd.Transaction, trans, + "#13 Transaction Property should be set"); + + // Test if parameters are reset to Default Values + cmd = new SqlCommand (); + Assert.AreEqual (String.Empty, cmd.CommandText, + "#1 Command Test should be empty"); + Assert.AreEqual (30, cmd.CommandTimeout, + "#2 CommandTimeout should be 30"); + Assert.AreEqual (CommandType.Text, cmd.CommandType, + "#3 CommandType should be text"); + Assert.IsNull (cmd.Connection, "#4 Connection Should be null"); + }finally { + trans.Rollback (); + } + } + + [Test] + public void ExecuteScalarTest () + { + conn = new SqlConnection (connectionString); + cmd = new SqlCommand ("" , conn); + cmd.CommandText = "Select count(*) from numeric_family where id<=4"; + + //Check Exception is thrown when executed on a closed connection + try { + cmd.ExecuteScalar (); + Assert.Fail ("#1 InvalidOperation Exception must be thrown"); + }catch (AssertionException e) { + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof(InvalidOperationException), e.GetType(), + "#2 Incorrect Exception : " + e.StackTrace); + } + + // Check the Return value for a Correct Query + object result = 0; + conn.Open (); + result = cmd.ExecuteScalar (); + Assert.AreEqual (4, (int)result, "#3 Query Result returned is incorrect"); + + cmd.CommandText = "select id , type_bit from numeric_family order by id asc" ; + result = Convert.ToInt32 (cmd.ExecuteScalar ()); + Assert.AreEqual (1, result, + "#4 ExecuteScalar Should return (1,1) the result set" ); + + cmd.CommandText = "select id from numeric_family where id=-1"; + result = cmd.ExecuteScalar (); + Assert.IsNull (result, "#5 Null shud be returned if result set is empty"); + + // Check SqlException is thrown for Invalid Query + cmd.CommandText = "select count* from numeric_family"; + try { + result = cmd.ExecuteScalar (); + Assert.Fail ("#6 InCorrect Query should cause an SqlException"); + }catch (AssertionException e) { + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof(SqlException), e.GetType(), + "#7 Incorrect Exception : " + e.StackTrace); + } + } + + [Test] + public void ExecuteNonQuery () + { + conn = new SqlConnection (connectionString); + cmd = new SqlCommand ("", conn); + int result = 0; + + // Test for exceptions + // Test exception is thrown if connection is closed + cmd.CommandText = "Select id from numeric_family where id=1"; + try { + cmd.ExecuteNonQuery (); + Assert.Fail ("#1 Connextion shud be open"); + }catch (AssertionException e) { + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof(InvalidOperationException), e.GetType(), + "#2 Incorrect Exception : " + e); + } + + // Test Exception is thrown if Query is incorrect + conn.Open (); + cmd.CommandText = "Select id1 from numeric_family"; + try { + cmd.ExecuteNonQuery (); + Assert.Fail ("#1 invalid Query"); + }catch (AssertionException e) { + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof(SqlException), e.GetType(), + "#2 Incorrect Exception : " + e); + } + + // Test Select/Insert/Update/Delete Statements + SqlTransaction trans = conn.BeginTransaction (); + cmd.Transaction = trans; + + try { + cmd.CommandText = "Select id from numeric_family where id=1"; + result = cmd.ExecuteNonQuery (); + Assert.AreEqual (-1, result, "#1"); + + cmd.CommandText = "Insert into numeric_family (id,type_int) values (100,200)"; + result = cmd.ExecuteNonQuery (); + Assert.AreEqual (1, result, "#2 One row shud be inserted"); + + cmd.CommandText = "Update numeric_family set type_int=300 where id=100"; + result = cmd.ExecuteNonQuery (); + Assert.AreEqual (1, result, "#3 One row shud be updated"); + + cmd.CommandText = "Delete from numeric_family where id=100"; + result = cmd.ExecuteNonQuery (); + Assert.AreEqual (1, result, "#4 One row shud be deleted"); + + }finally { + trans.Rollback (); + } + } + + [Test] + public void ExecuteReaderTest () + { + SqlDataReader reader = null; + conn = new SqlConnection (connectionString); + + // Test exception is thrown if conn is closed + cmd = new SqlCommand ("Select count(*) from numeric_family"); + try { + reader = cmd.ExecuteReader (); + }catch (AssertionException e) { + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof(InvalidOperationException), e.GetType(), + "#1 Incorrect Exception"); + } + + conn.Open (); + // Test exception is thrown for Invalid Query + cmd = new SqlCommand ("InvalidQuery", conn); + try { + reader = cmd.ExecuteReader (); + Assert.Fail ("#1 Exception shud be thrown"); + }catch (AssertionException e) { + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof(SqlException), e.GetType (), + "#2 Incorrect Exception : " + e); + } + + // NOTE + // Test SqlException is thrown if a row is locked + // should lock a particular row and then modify it + /* + */ + + // Test Connection cannot be modified when reader is in use + // NOTE : msdotnet contradicts documented behavior + cmd.CommandText = "select * from numeric_family where id=1"; + reader = cmd.ExecuteReader (); + reader.Read (); + conn.Close (); // valid operation + conn = new SqlConnection (connectionString); + + /* + // NOTE msdotnet contradcits documented behavior + // If the above testcase fails, then this shud be tested + // Test connection can be modified once reader is closed + conn.Close (); + reader.Close (); + conn = new SqlConnection (connectionString); // valid operation + */ + } + + [Test] + public void ExecuteReaderCommandBehaviorTest () + { + // Test for command behaviors + DataTable schemaTable = null; + SqlDataReader reader = null; + + conn = new SqlConnection (connectionString); + conn.Open (); + cmd = new SqlCommand ("", conn); + cmd.CommandText = "Select id from numeric_family where id <=4 order by id asc;"; + cmd.CommandText += "Select type_bit from numeric_family where id <=4 order by id asc"; + + // Test for default command behavior + reader = cmd.ExecuteReader (); + int rows = 0; + int results = 0; + do { + while (reader.Read ()) + rows++ ; + Assert.AreEqual (4, rows, "#1 Multiple rows shud be returned"); + results++; + rows = 0; + }while (reader.NextResult()); + Assert.AreEqual (2, results, "#2 Multiple result sets shud be returned"); + reader.Close (); + + // Test if closing reader, closes the connection + reader = cmd.ExecuteReader (CommandBehavior.CloseConnection); + reader.Close (); + Assert.AreEqual (ConnectionState.Closed, conn.State, + "#3 Command Behavior is not followed"); + conn.Open(); + + // Test if row info and primary Key info is returned + reader = cmd.ExecuteReader (CommandBehavior.KeyInfo); + schemaTable = reader.GetSchemaTable (); + Assert.IsTrue(reader.HasRows, "#4 Data Rows shud also be returned"); + Assert.IsTrue ((bool)schemaTable.Rows[0]["IsKey"], + "#5 Primary Key info shud be returned"); + reader.Close (); + + // Test only column information is returned + reader = cmd.ExecuteReader (CommandBehavior.SchemaOnly); + schemaTable = reader.GetSchemaTable (); + Assert.IsFalse (reader.HasRows, "#6 row data shud not be returned"); + Assert.AreEqual(DBNull.Value, schemaTable.Rows[0]["IsKey"], + "#7 Primary Key info shud not be returned"); + Assert.AreEqual ("id", schemaTable.Rows[0]["ColumnName"], + "#8 Schema Data is Incorrect"); + reader.Close (); + + // Test only one result set (first) is returned + reader = cmd.ExecuteReader (CommandBehavior.SingleResult); + schemaTable = reader.GetSchemaTable (); + Assert.IsFalse (reader.NextResult(), + "#9 Only one result set shud be returned"); + Assert.AreEqual ("id", schemaTable.Rows[0]["ColumnName"], + "#10 The result set returned shud be the first result set"); + reader.Close (); + + // Test only one row is returned for all result sets + // msdotnet doesnt work correctly.. returns only one result set + reader = cmd.ExecuteReader (CommandBehavior.SingleRow); + rows=0; + results=0; + do { + while (reader.Read ()) + rows++ ; + Assert.AreEqual (1, rows, "#11 Only one row shud be returned"); + results++; + rows = 0; + }while (reader.NextResult()); + // NOTE msdotnet contradicts documented behavior. + // Multiple result sets shud be returned , and in this case : 2 + //Assert.AreEqual (2, results, "# Multiple result sets shud be returned"); + Assert.AreEqual (2, results, "#12 Multiple result sets shud be returned"); + reader.Close (); + } + + [Test] + public void PrepareTest_CheckValidStatement () + { + cmd = new SqlCommand (); + conn = new SqlConnection (connectionString); + conn.Open (); + + cmd.CommandText = "Select id from numeric_family where id=@ID" ; + cmd.Connection = conn ; + + // Test if Parameters are correctly populated + cmd.Parameters.Clear (); + cmd.Parameters.Add ("@ID", SqlDbType.TinyInt); + cmd.Parameters["@ID"].Value = 2 ; + cmd.Prepare (); + Assert.AreEqual (2, cmd.ExecuteScalar (), "#3 Prepared Stmt not working"); + + cmd.Parameters[0].Value = 3; + Assert.AreEqual (3, cmd.ExecuteScalar (), "#4 Prep Stmt not working"); + conn.Close (); + } + + [Test] + public void PrepareTest () + { + cmd = new SqlCommand (); + conn = new SqlConnection (connectionString); + conn.Open (); + + cmd.CommandText = "Select id from numeric_family where id=@ID" ; + cmd.Connection = conn ; + + // Test InvalidOperation Exception is thrown if Parameter Type + // is not explicitly set + cmd.Parameters.Add ("@ID", 2); + try { + cmd.Prepare (); + Assert.Fail ("#1 Parameter Type shud be explicitly Set"); + }catch (AssertionException e) { + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof(InvalidOperationException), e.GetType (), + "#2 Incorrect Exception : " + e.StackTrace); + } + + // Test Exception is thrown for variable size data if precision/scale + // is not set + cmd.CommandText = "select type_varchar from string_family where type_varchar=@p1"; + cmd.Parameters.Clear (); + cmd.Parameters.Add ("@p1", SqlDbType.VarChar); + cmd.Parameters["@p1"].Value = "afasasadadada"; + try { + cmd.Prepare (); + Assert.Fail ("#5 Exception shud be thrown"); + }catch (AssertionException e) { + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof(InvalidOperationException), e.GetType(), + "#6 Incorrect Exception " + e.StackTrace); + } + + // Test Exception is not thrown for Stored Procs + try { + cmd.CommandType = CommandType.StoredProcedure; + cmd.CommandText = "ABFSDSFSF" ; + cmd.Prepare (); + }catch (Exception e) { + Assert.Fail ("#7 Exception shud not be thrown for Stored Procs"); + } + cmd.CommandType = CommandType.Text; + conn.Close (); + + //Test InvalidOperation Exception is thrown if connection is not set + cmd.Connection = null; + try { + cmd.Prepare (); + Assert.Fail ("#8 InvalidOperation Exception shud be thrown"); + }catch (AssertionException e) { + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof(InvalidOperationException), e.GetType(), + "#9 Incorrect Exception : " + e.StackTrace); + } + + //Test InvalidOperation Exception is thrown if connection is closed + cmd.Connection = conn ; + try{ + cmd.Prepare (); + Assert.Fail ("#4 InvalidOperation Exception shud be thrown"); + }catch (AssertionException e) { + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof(InvalidOperationException), e.GetType(), + "Incorrect Exception : " + e.StackTrace); + } + } + + [Test] + public void ResetTimeOut () + { + SqlCommand cmd = new SqlCommand (); + cmd.CommandTimeout = 50 ; + Assert.AreEqual ( cmd.CommandTimeout, 50, + "#1 CommandTimeout should be modfiable"); + cmd.ResetCommandTimeout (); + Assert.AreEqual (cmd.CommandTimeout, 30, + "#2 Reset Should set the Timeout to default value"); + } + + [Test] + [ExpectedException (typeof(ArgumentException))] + public void CommandTimeout () + { + cmd = new SqlCommand (); + cmd.CommandTimeout = 10; + Assert.AreEqual (10, cmd.CommandTimeout, "#1"); + cmd.CommandTimeout = -1; + } + + [Test] + [ExpectedException (typeof(ArgumentException))] + public void CommandTypeTest () + { + cmd = new SqlCommand (); + Assert.AreEqual (CommandType.Text ,cmd.CommandType, + "Default CommandType is text"); + cmd.CommandType = (CommandType)(-1); + } + + [Test] + [Ignore ("msdotnet contradicts documented behavior")] + [ExpectedException (typeof(InvalidOperationException))] + public void ConnectionTest () + { + SqlTransaction trans = null; + try { + conn = new SqlConnection (connectionString); + conn.Open (); + trans = conn.BeginTransaction (); + cmd = new SqlCommand ("", conn,trans); + cmd.CommandText = "Select id from numeric_family where id=1"; + cmd.Connection = new SqlConnection (); + }finally { + trans.Rollback(); + conn.Close (); + } + } + + [Test] + public void TransactionTest () + { + conn = new SqlConnection (connectionString); + cmd = new SqlCommand ("", conn); + Assert.IsNull (cmd.Transaction, "#1 Default value is null"); + + SqlConnection conn1 = new SqlConnection (connectionString); + conn1.Open (); + SqlTransaction trans1 = conn1.BeginTransaction (); + cmd.Transaction = trans1 ; + try { + cmd.ExecuteNonQuery (); + Assert.Fail ("#2 Connection cannot be different"); + }catch (Exception e) { + Assert.AreEqual (typeof(InvalidOperationException), e.GetType(), + "#3 Incorrect Exception : " + e); + }finally { + conn1.Close (); + conn.Close (); + } + } + + // Need to add more tests + [Test] + [ExpectedException (typeof(ArgumentException))] + public void UpdatedRowSourceTest () + { + cmd = new SqlCommand (); + Assert.AreEqual (UpdateRowSource.Both, cmd.UpdatedRowSource, + "#1 Default value is both"); + cmd.UpdatedRowSource = UpdateRowSource.None; + Assert.AreEqual (UpdateRowSource.None, cmd.UpdatedRowSource, + "#2"); + + cmd.UpdatedRowSource = (UpdateRowSource) (-1); + } [Test] public void ExecuteNonQueryTempProcedureTest () { @@ -146,7 +655,7 @@ namespace MonoTests.System.Data.SqlClient } private readonly string CREATE_TMP_SP_TEMP_INSERT_PERSON = ("create procedure #sp_temp_insert_employee ( " + Environment.NewLine + - "@fname varchar (20), " + Environment.NewLine + + "@fname varchar (20)) " + Environment.NewLine + "as " + Environment.NewLine + "begin" + Environment.NewLine + "declare @id int;" + Environment.NewLine + @@ -161,3 +670,4 @@ namespace MonoTests.System.Data.SqlClient "drop procedure #sp_temp_insert_employee; "); } } + diff --git a/mcs/class/System.Data/Test/ProviderTests/System.Data.SqlClient/SqlConnectionTest.cs b/mcs/class/System.Data/Test/ProviderTests/System.Data.SqlClient/SqlConnectionTest.cs new file mode 100644 index 00000000000..7f22718c132 --- /dev/null +++ b/mcs/class/System.Data/Test/ProviderTests/System.Data.SqlClient/SqlConnectionTest.cs @@ -0,0 +1,549 @@ +// +// SqlDataAdapterTest.cs - NUnit Test Cases for testing the +// SqlDataAdapter class +// Author: +// Senganal T (tsenganal@novell.com) +// +// Copyright (c) 2004 Novell Inc., and the individuals listed +// on the ChangeLog entries. +// +// Permission is hereby granted, free of charge, to any person obtaining +// a copy of this software and associated documentation files (the +// "Software"), to deal in the Software without restriction, including +// without limitation the rights to use, copy, modify, merge, publish, +// distribute, sublicense, and/or sell copies of the Software, and to +// permit persons to whom the Software is furnished to do so, subject to +// the following conditions: +// +// The above copyright notice and this permission notice shall be +// included in all copies or substantial portions of the Software. +// +// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, +// EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF +// MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND +// NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE +// LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION +// OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION +// WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. +// + +using System; +using System.Data; +using System.Data.SqlClient; +using System.Net; +using NUnit.Framework; +using System.Collections; + +namespace MonoTests.System.Data +{ + [TestFixture] + [Category ("sqlserver")] + public class SqlConnectionTest + { + SqlConnection conn = null ; + String connectionString = ConnectionManager.Singleton.ConnectionString; + + ArrayList invalidConnectionStrings = null; + int stateChangeEventCount = 0; + int disposedEventCount = 0; + int infoMessageEventCount = 0; + + void populateTestData () + { + invalidConnectionStrings = new ArrayList (); + // shud be got from a config file .. + //list of invalid and valid conn strings; + invalidConnectionStrings.Add ("InvalidConnectionString"); + invalidConnectionStrings.Add ("invalidKeyword=10"); + invalidConnectionStrings.Add ("Packet Size=511"); + invalidConnectionStrings.Add ("Packet Size=32768"); + invalidConnectionStrings.Add ("Connect Timeout=-1"); + invalidConnectionStrings.Add ("Max Pool Size=-1"); + invalidConnectionStrings.Add ("Min Pool Size=-1"); + } + + [SetUp] + public void SetUp () + { + } + + [TearDown] + public void TearDown () + { + if (conn != null) + conn.Dispose (); + } + + [Test] + public void DefaultConstructorTest () + { + SqlConnection conn = new SqlConnection (); + Assert.AreEqual ("", conn.ConnectionString, + "#1 Default Connection String should be empty"); + Assert.AreEqual (15, conn.ConnectionTimeout, + "#2 Default ConnectionTimeout should be 15" ); + Assert.AreEqual ("", conn.Database, + "#3 Default Database should be empty"); + Assert.AreEqual ("", conn.DataSource, + "#4 Default DataSource should be empty"); + Assert.AreEqual (8192, conn.PacketSize,"#5 Default Packet Size is 8192"); + Assert.AreEqual (Dns.GetHostName(), conn.WorkstationId, + "#6 Default Workstationid shud be hostname"); + Assert.AreEqual (ConnectionState.Closed, conn.State, + "#7 Connection State shud be closed by default"); + } + + [Test] + public void OverloadedConstructorTest () + { + // Test Exceptions are thrown for Invalid Connection Strings + int count=0 ; + populateTestData (); + foreach (String invalidConnString in invalidConnectionStrings) { + count++; + try { + conn = new SqlConnection ((string)invalidConnString); + Assert.Fail ("#1 Exception must be thrown"); + }catch (AssertionException e) { + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof(ArgumentException), e.GetType(), + "Incorrect Exception" + e.StackTrace); + } + } + + //check synonyms.. + //do i need to check for all the synonyms.. + conn = new SqlConnection ( + "Timeout=10;Connect Timeout=20;Connection Timeout=30"); + Assert.AreEqual (30, conn.ConnectionTimeout, + "## The last set value shud be taken"); + conn = new SqlConnection ( + "Connect Timeout=100;Connection Timeout=200;Timeout=300"); + Assert.AreEqual (300, conn.ConnectionTimeout, + "## The last set value shud be taken"); + conn = new SqlConnection ( + "Connection Timeout=1000;Timeout=200$0;Connect Timeout=3000"); + Assert.AreEqual (3000, conn.ConnectionTimeout, + "## The last set value shud be taken"); + + // Test if properties are set correctly + + //'==' doesent work correctly in both msdotnet and mono + /* + conn = new SqlConnection ("server=local==host;database=tmp;"); + Assert.AreEqual ("local==host", conn.DataSource, + "# Datasource name is set incorrectly"); + */ + string connStr = "Server='loca\"lhost';Database='''Db'; packet Size=\"512\";"; + connStr += "connect Timeout=20;Workstation Id=\"'\"\"desktop\";"; + conn = new SqlConnection (connStr); + Assert.AreEqual (connStr , conn.ConnectionString , "#1"); + Assert.AreEqual ("loca\"lhost" , conn.DataSource , "#2"); + Assert.AreEqual ("'Db" , conn.Database , "#3"); + Assert.AreEqual (512 , conn.PacketSize , "#4"); + Assert.AreEqual (20 , conn.ConnectionTimeout , "#5"); + Assert.AreEqual ("'\"desktop" , conn.WorkstationId , "#6"); + Assert.AreEqual (ConnectionState.Closed , conn.State , "#7"); + } + + [Test] + public void OpenTest () + { + conn = new SqlConnection (connectionString); + ArrayList validIncorrectConnStrings = new ArrayList(); + string validConnString = connectionString; + + validIncorrectConnStrings.Add ( + validConnString+"user id=invalidLogin"); + validIncorrectConnStrings.Add ( + validConnString+"database=invalidDB"); + validIncorrectConnStrings.Add ( + validConnString+";password=invalidPassword"); + validIncorrectConnStrings.Add ( + validConnString+";server=invalidServerName"); + + int count=0; + foreach (string connString in validIncorrectConnStrings) { + count++; + try { + conn.ConnectionString = connString; + conn.Open(); + Assert.AreEqual (typeof (SqlException), null, + string.Format ( + "#1_{0} Incorrect Connection String",count)); + }catch (AssertionException e) { + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof (SqlException), e.GetType (), + "#2 Incorrect Exception" + e.StackTrace); + } + } + + // Test connection is Opened for a valid Connection String + conn.ConnectionString = connectionString; + conn.Open (); + Assert.AreEqual (ConnectionState.Open, conn.State, + "#3 Connection State Should be OPEN"); + + // Test Exception is thrown on opening an OPEN Connection + try { + conn.Open (); + Assert.AreEqual (typeof (InvalidOperationException), null, + "#1 Connection is Already Open"); + }catch (AssertionException e) { + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof(InvalidOperationException), e.GetType (), + "#2 Incorect Exception."); + } + conn.Close(); + + /* + // Test if localhost is assumed when servername is empty/missing + // NOTE : msdotnet contradicts doc + + Assumes the server is localhost .. need to test this with mono on windows + conn.ConnectionString = connectionString + "server=;"; + try { + conn.Open (); + }catch (Exception e) { + Assert.Fail ("## If server name is not given or empty ,localhost shud be tried"); + } + ex = null; + conn.Close (); + */ + } + + [Test] + public void CreateCommandTest () + { + conn = new SqlConnection (connectionString); + IDbCommand cmd = conn.CreateCommand (); + Assert.AreSame (conn, cmd.Connection, + "#1 Connection instance should be the same"); + } + + [Test] + public void CloseTest () + { + conn = new SqlConnection (connectionString); + conn.Open (); + Assert.AreEqual (ConnectionState.Open, conn.State, + "#1 Connection State should be : Open"); + conn.Close (); + Assert.AreEqual (ConnectionState.Closed, conn.State, + "#1 Connection State Should : Closed"); + // Test Closing an already closed connection is Valid.. + conn.Close (); + } + + [Test] + public void DisposeTest () + { + SqlConnection conn = new SqlConnection (connectionString); + conn.Dispose (); + Assert.AreEqual ("", conn.ConnectionString, + "#1 Dispose shud make the Connection String empty"); + Assert.AreEqual (15, conn.ConnectionTimeout, + "#2 Default ConnectionTimeout : 15" ); + Assert.AreEqual ("", conn.Database, + "#3 Default Database : empty"); + Assert.AreEqual ("", conn.DataSource, + "#4 Default DataSource : empty"); + Assert.AreEqual (8192, conn.PacketSize, + "#5 Default Packet Size : 8192"); + Assert.AreEqual (Dns.GetHostName(), conn.WorkstationId, + "#6 Default Workstationid : hostname"); + Assert.AreEqual (ConnectionState.Closed, conn.State, + "#7 Default State : CLOSED "); + } + + [Test] + public void ChangeDatabaseTest () + { + conn = new SqlConnection (connectionString); + String database = conn.Database; + + //Test if exception is thrown if connection is closed + try { + conn.ChangeDatabase ("database"); + Assert.AreEqual (typeof (InvalidOperationException), null, + "#1 Connection is Closed"); + }catch (AssertionException e){ + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof (InvalidOperationException), e.GetType(), + "#2 Incorrect Exception : " + e.StackTrace); + } + + //Test if exception is thrown for invalid Database Names + //need to add more to the list + conn.Open (); + String[] InvalidDatabaseNames = {"", null, " "}; + for (int i = 0; i < InvalidDatabaseNames.Length ; ++i) { + try { + conn.ChangeDatabase (InvalidDatabaseNames[i]); + Assert.AreEqual (typeof (ArgumentException), null, + string.Format ("#3_{0} Exception not thrown",i)); + }catch (AssertionException e) { + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof(ArgumentException), e.GetType (), + string.Format( "#4_{0} Incorrect Exception : {1}", + i, e.StackTrace)); + } + Assert.AreEqual (database, conn.Database, + "#4 The Database shouldnt get changed if Operation Failed"); + } + + //Test if exception is thrown if database name is non-existent + try { + conn.ChangeDatabase ("invalidDB"); + Assert.Fail ("#5 Exception must be thrown if database doesent exist"); + }catch (AssertionException e) { + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof(SqlException), e.GetType (), + "#6 Incorrect Exception" + e.StackTrace); + } + conn.Close (); + + //Test if '-' is a valid character in a database name + //TODO : Check for database names that have more special Characters.. + conn.ConnectionString = connectionString; + conn.Open (); + try { + conn.ChangeDatabase ("mono-test"); + Assert.AreEqual ("mono-test", conn.Database, + "#7 Database name should be mono-test"); + }catch (AssertionException e) { + throw e; + }catch (Exception e){ + Assert.Fail ("#8 Unexpected Exception : DB Name can have a '-' : " + + e); + } + } + + [Test] + public void BeginTransactionTest() + { + conn = new SqlConnection (connectionString); + SqlTransaction trans = null ; + + try { + trans = conn.BeginTransaction (); + Assert.Fail ("#1 Connection must be Open to Begin a Transaction"); + }catch (AssertionException e) { + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof (InvalidOperationException), e.GetType(), + "#2 Incorrect Exception" + e.StackTrace); + } + + conn.Open (); + trans = conn.BeginTransaction (); + Assert.AreSame (conn, trans.Connection, + "#3 Transaction should reference the same connection"); + Assert.AreEqual (IsolationLevel.ReadCommitted, trans.IsolationLevel, + "#4 Isolation Level shud be ReadCommitted"); + trans.Rollback (); + + try { + trans = conn.BeginTransaction (); + trans = conn.BeginTransaction (); + conn.BeginTransaction (); + Assert.Fail ("#5 Parallel Transactions are not supported"); + }catch (AssertionException e) { + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof(InvalidOperationException), e.GetType(), + "#6 Incorrect Exception" + e.StackTrace); + }finally { + trans.Rollback(); + } + + try { + trans = conn.BeginTransaction (); + trans.Rollback (); + trans = conn.BeginTransaction (); + trans.Commit(); + trans = conn.BeginTransaction (); + }catch (Exception e) { + Assert.Fail ("#7 Transaction can be opened after a rollback/commit"); + }finally { + trans.Rollback (); + } + } + + [Test] + public void ConnectionStringPropertyTest () + { + conn = new SqlConnection (connectionString) ; + // Test Repeated Keyoword ..Should take the latest value + conn.ConnectionString = conn.ConnectionString + ";server=RepeatedServer;" ; + Assert.AreEqual ("RepeatedServer", ((SqlConnection)conn).DataSource, + "#1 if keyword is repeated, the latest value should be taken"); + conn.ConnectionString += ";database=gen;Initial Catalog=gen1"; + Assert.AreEqual ("gen1", conn.Database, + "#2 database and initial catalog are synonyms .. "); + + // Test if properties are set correctly + string str = "server=localhost1;database=db;user id=user;"; + str += "password=pwd;Workstation ID=workstation;Packet Size=512;"; + str += "Connect Timeout=10"; + conn.ConnectionString = str; + + Assert.AreEqual ("localhost1", conn.DataSource, + "#3 DataSource name should be same as passed"); + Assert.AreEqual ("db", conn.Database, + "#4 Database name shud be same as passed"); + Assert.AreEqual (ConnectionState.Closed, conn.State, + "#5 Connection shud be in closed state"); + Assert.AreEqual ("workstation", conn.WorkstationId, + "#6 Workstation Id shud be same as passed"); + Assert.AreEqual (512, conn.PacketSize, + "#7 Packetsize shud be same as passed"); + Assert.AreEqual (10, conn.ConnectionTimeout, + "#8 ConnectionTimeout shud be same as passed"); + + // Test if any leftover values exist from previous invocation. + conn.ConnectionString = connectionString; + conn.ConnectionString = ""; + Assert.AreEqual ("", conn.DataSource, + "#9 Datasource shud be reset to Default : Empty"); + Assert.AreEqual ("", conn.Database, + "#10 Database shud reset to Default : Empty"); + Assert.AreEqual (8192, conn.PacketSize, + "#11 Packetsize shud be reset to Default : 8192"); + Assert.AreEqual (15, conn.ConnectionTimeout, + "#12 ConnectionTimeour shud be reset to Default : 15"); + Assert.AreEqual (Dns.GetHostName (), conn.WorkstationId, + "#13 WorkstationId shud be reset to Default : Hostname"); + + // Test Argument Exception is thrown for Invalid Connection Strings + foreach (string connString in invalidConnectionStrings) { + try { + conn.ConnectionString = connString; + Assert.Fail ( + "#14 Exception should be thrown"); + }catch (AssertionException e){ + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof (ArgumentException), e.GetType(), + "#15 Incorrect Exception" + e.StackTrace); + } + } + + // Test if ConnectionString is read-only when Connection is OPEN + conn.ConnectionString = connectionString; + conn.Open() ; + try { + Assert.AreEqual (conn.State, ConnectionState.Open, + "#16 Connection shud be open"); + conn.ConnectionString = "server=localhost;database=tmp;" ; + Assert.Fail ( + "#17 ConnectionString should Read-Only when Connection is Open"); + }catch (AssertionException e){ + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof(InvalidOperationException), e.GetType(), + "#18 Incorrect Exception" + e.StackTrace); + } + conn.Close (); + } + + [Test] + public void ServerVersionTest () + { + conn = new SqlConnection (connectionString); + + // Test InvalidOperation Exception is thrown if Connection is CLOSED + try{ + string s = conn.ServerVersion; + Assert.Fail ( + "#1 InvalidOperation Exception Must be thrown if conn is closed"); + }catch (AssertionException e){ + throw e; + }catch (Exception e){ + Assert.AreEqual(typeof (InvalidOperationException), e.GetType (), + "#2 Incorrect Exception" + e.StackTrace); + } + + // Test if Release Version is as per specification. + conn.Open (); + String[] version = conn.ServerVersion.Split ('.') ; + Assert.AreEqual (2, version[0].Length, + "#2 The Major release shud be exactly 2 characters"); + Assert.AreEqual (2, version[1].Length, + "#3 The Minor release shud be exactly 2 characters"); + Assert.AreEqual (4, version[2].Length, + "#4 The Release version should be exactly 4 digits"); + } + + [Test] + public void DatabasePropertyTest () + { + conn = new SqlConnection (connectionString); + + // Test if database property is updated when a query changes database + conn.Open (); + SqlCommand cmd = new SqlCommand ("use [mono-test]" , conn); + cmd.ExecuteNonQuery (); + Assert.AreEqual ("mono-test", conn.Database, + "#1 DATABASE name shud change if query changes the db"); + conn.Close (); + Assert.AreEqual ("monotest", conn.Database, + "#2 Shud be back to default value"); + + // Test if the database property is reset on re-opening the connection + conn.ConnectionString = connectionString; + conn.Open (); + Assert.AreEqual ("monotest", conn.Database, + "#3 Shud be back to original value"); + conn.Close (); + } + + [Test] + public void StateChangeEventTest () + { + conn = new SqlConnection (connectionString); + conn.StateChange += new StateChangeEventHandler ( + StateChangeHandlerTest1); + using (conn) { + conn.Open (); + } + Assert.AreEqual (2, stateChangeEventCount, + "#1 The handler shud be called twice"); + stateChangeEventCount =0 ; + conn.StateChange -= new StateChangeEventHandler ( + StateChangeHandlerTest1); + // NOTE : Need to check the behavior if an exception is raised + // in a handler + } + + [Test] + public void DisposedEventTest () + { + conn = new SqlConnection (connectionString); + conn.Disposed += new EventHandler (DisposedEventHandlerTest1); + conn.Dispose (); + Assert.AreEqual (1, disposedEventCount, + "#1 Disposed eventhandler shud be called"); + } + + void StateChangeHandlerTest1 (object sender , StateChangeEventArgs e) + { + Assert.IsTrue ((e.CurrentState != e.OriginalState), + "#1 Current and Original state shud be different"); + Assert.AreEqual (e.CurrentState, conn.State, + "The conn state and the arg received in event shud be same"); + stateChangeEventCount++ ; + } + + void DisposedEventHandlerTest1 (object sender , EventArgs e) + { + disposedEventCount++; + } + } +} diff --git a/mcs/class/System.Data/Test/ProviderTests/System.Data.SqlClient/SqlDataAdapterTest.cs b/mcs/class/System.Data/Test/ProviderTests/System.Data.SqlClient/SqlDataAdapterTest.cs index 6121b4a924a..1b1d7e248a8 100644 --- a/mcs/class/System.Data/Test/ProviderTests/System.Data.SqlClient/SqlDataAdapterTest.cs +++ b/mcs/class/System.Data/Test/ProviderTests/System.Data.SqlClient/SqlDataAdapterTest.cs @@ -4,6 +4,7 @@ // Author: // Umadevi S (sumadevi@novell.com) // Sureshkumar T (tsureshkumar@novell.com) +// Senganal T (tsenganal@novell.com) // // Copyright (c) 2004 Novell Inc., and the individuals listed // on the ChangeLog entries. @@ -42,7 +43,10 @@ namespace MonoTests.System.Data.SqlClient [Category ("sqlserver")] public class SqlDataAdapterTest { - SqlConnection conn; + SqlDataAdapter adapter =null; + DataSet data = null ; + string connectionString = ConnectionManager.Singleton.ConnectionString; + SqlConnection conn = null; [Test] /** @@ -72,25 +76,6 @@ namespace MonoTests.System.Data.SqlClient } } - [Test] - public void FillSchemaTest() - { - conn = (SqlConnection) ConnectionManager.Singleton.Connection; - try { - ConnectionManager.Singleton.OpenConnection (); - string sql = "select * from employee;"; - SqlCommand c = conn.CreateCommand(); - c.CommandText = sql; - SqlDataReader dr = c.ExecuteReader(CommandBehavior.KeyInfo|CommandBehavior.SchemaOnly); - DataTable schema = dr.GetSchemaTable(); - DataRowCollection drc = schema.Rows; - DataRow r = drc[0]; - Assert.AreEqual("id",r["ColumnName"].ToString()); - } finally { - ConnectionManager.Singleton.CloseConnection (); - } - } - /** This needs a errortable created as follows id uniqueidentifier,name char(10) , with values @@ -121,10 +106,527 @@ namespace MonoTests.System.Data.SqlClient Assert.AreEqual("Done","Done"); } + + [Test] + public void DefaultConstructorTest () + { + adapter = new SqlDataAdapter (); + Assert.AreEqual (MissingMappingAction.Passthrough, + adapter.MissingMappingAction, + "#1 Missing Mapping acttion default to Passthrough"); + Assert.AreEqual (MissingSchemaAction.Add, + adapter.MissingSchemaAction, + "#2 Missing Schme action default to Add"); + } + + [Test] + public void OverloadedConstructorsTest () + { + SqlCommand selCmd = new SqlCommand ("Select * from numeric_family"); + adapter = new SqlDataAdapter (selCmd); + Assert.AreEqual (MissingMappingAction.Passthrough, + adapter.MissingMappingAction, + "#1 Missing Mapping acttion default to Passthrough"); + Assert.AreEqual (MissingSchemaAction.Add, + adapter.MissingSchemaAction, + "#2 Missing Schme action default to Add"); + Assert.AreSame (selCmd, adapter.SelectCommand, + "#3 Select Command shud be a ref to the arg passed"); + + conn = new SqlConnection (connectionString); + String selStr = "Select * from numeric_family"; + adapter = new SqlDataAdapter (selStr, conn); + Assert.AreEqual (MissingMappingAction.Passthrough, + adapter.MissingMappingAction, + "#4 Missing Mapping acttion default to Passthrough"); + Assert.AreEqual (MissingSchemaAction.Add, + adapter.MissingSchemaAction, + "#5 Missing Schme action default to Add"); + Assert.AreSame (selStr, adapter.SelectCommand.CommandText, + "#6 Select Command shud be a ref to the arg passed"); + Assert.AreSame (conn, adapter.SelectCommand.Connection, + "#7 cmd.connection shud be t ref to connection obj"); + + selStr = "Select * from numeric_family"; + adapter = new SqlDataAdapter (selStr, connectionString); + Assert.AreEqual (MissingMappingAction.Passthrough, + adapter.MissingMappingAction, + "#8 Missing Mapping action shud default to Passthrough"); + Assert.AreEqual (MissingSchemaAction.Add, + adapter.MissingSchemaAction, + "#9 Missing Schema action shud default to Add"); + Assert.AreSame (selStr, + adapter.SelectCommand.CommandText, + "#10"); + Assert.AreEqual (connectionString, + adapter.SelectCommand.Connection.ConnectionString, + "#11 "); + } + [Test] + public void Fill_Test_ConnState () + { + //Check if Connection State is maintained correctly .. + data = new DataSet ("test1"); + adapter = new SqlDataAdapter ("select id from numeric_family where id=1", + connectionString); + SqlCommand cmd = adapter.SelectCommand ; + + Assert.AreEqual (ConnectionState.Closed, + cmd.Connection.State, "#1 Connection shud be in closed state"); + adapter.Fill (data); + Assert.AreEqual (1, data.Tables.Count, "#2 One table shud be populated"); + Assert.AreEqual (ConnectionState.Closed, cmd.Connection.State, + "#3 Connection shud be closed state"); + + data = new DataSet ("test2"); + cmd.Connection.Open (); + Assert.AreEqual (ConnectionState.Open, cmd.Connection.State, + "#3 Connection shud be open"); + adapter.Fill (data); + Assert.AreEqual (1, data.Tables.Count, "#4 One table shud be populated"); + Assert.AreEqual (ConnectionState.Open, cmd.Connection.State, + "#5 Connection shud be open"); + cmd.Connection.Close (); - - + // Test if connection is closed when exception occurs + cmd.CommandText = "select id1 from numeric_family"; + try { + adapter.Fill (data); + }catch (Exception e) { + if (cmd.Connection.State == ConnectionState.Open) { + cmd.Connection.Close (); + Assert.Fail ("# Connection Shud be Closed"); + } + } + } + + [Test] + public void Fill_Test_Data () + { + //Check if a table is created for each resultset + String batchQuery = "Select id,type_bit,type_int from numeric_family;"; + batchQuery += "Select type_bit,type_bigint from numeric_family"; + adapter = new SqlDataAdapter (batchQuery, connectionString); + data = new DataSet ("test1"); + adapter.Fill (data); + Assert.AreEqual (2, data.Tables.Count,"#1 2 Table shud be created"); + + //Check if Table and Col are named correctly for unnamed columns + string query = "Select 10,20 from numeric_family;" ; + query += "Select 10,20 from numeric_family"; + adapter = new SqlDataAdapter (query, connectionString); + data = new DataSet ("test2"); + adapter.Fill (data); + Assert.AreEqual (2, data.Tables.Count, + "#2 2 Tables shud be created"); + Assert.AreEqual ("Table", data.Tables[0].TableName, "#3"); + Assert.AreEqual ("Table1", data.Tables[1].TableName, "#4"); + Assert.AreEqual ("Column1", data.Tables[0].Columns[0].ColumnName, "#5"); + Assert.AreEqual ("Column2", data.Tables[0].Columns[1].ColumnName, "#6"); + Assert.AreEqual ("Column1", data.Tables[1].Columns[0].ColumnName, "#7"); + Assert.AreEqual ("Column2", data.Tables[1].Columns[1].ColumnName, "#8"); + + //Check if dup columns are named correctly + query = "select A.id ,B.id , C.id from numeric_family A, "; + query += "numeric_family B , numeric_family C"; + adapter = new SqlDataAdapter (query, connectionString); + data = new DataSet ("test3"); + adapter.Fill (data); + + // NOTE msdotnet contradicts documented behavior + // as per documentation the column names should be + // id1,id2,id3 .. but msdotnet returns id,id1,id2 + Assert.AreEqual ("id", data.Tables[0].Columns[0].ColumnName, + "#9 if colname is duplicated ,shud be col,col1,col2 etc"); + Assert.AreEqual ("id1", data.Tables[0].Columns[1].ColumnName, + "#10 if colname is duplicated ,shud be col,col1,col2 etc"); + Assert.AreEqual ("id2", data.Tables[0].Columns[2].ColumnName, + "#11 if colname is duplicated ,shud be col,col1,col2 etc"); + // Test if tables are created and named accordingly , + // but only for those queries returning result sets + query = "update numeric_family set id=100 where id=50;"; + query += "select * from numeric_family"; + adapter = new SqlDataAdapter (query, connectionString); + data = new DataSet ("test4"); + adapter.Fill (data); + Assert.AreEqual (1 ,data.Tables.Count, + "#12 Tables shud be named only for queries returning a resultset"); + Assert.AreEqual ("Table", data.Tables[0].TableName, + "#13 The first resutlset shud have 'Table' as its name"); + + // Test behavior with an outerjoin + query = "select A.id,B.type_bit from numeric_family A LEFT OUTER JOIN "; + query += "numeric_family B on A.id = B.type_bit"; + adapter = new SqlDataAdapter (query, connectionString); + data = new DataSet ("test5"); + adapter.Fill (data); + Assert.AreEqual (0, data.Tables[0].PrimaryKey.Length, + "#14 Primary Key shudnt be set if an outer join is performed"); + Assert.AreEqual (0, data.Tables[0].Constraints.Count, + "#15 Constraints shudnt be set if an outer join is performed"); + adapter = new SqlDataAdapter ("select id from numeric_family", + connectionString); + data = new DataSet ("test6"); + adapter.Fill (data, 1, 1, "numeric_family"); + Assert.AreEqual (1, data.Tables[0].Rows.Count, "#16"); + Assert.AreEqual (2, data.Tables[0].Rows[0][0], "#17"); + + // only one test for DataTable.. DataSet tests covers others + adapter = new SqlDataAdapter ("select id from numeric_family", + connectionString); + DataTable table = new DataTable ("table1"); + adapter.Fill (table); + Assert.AreEqual (4, table.Rows.Count , "#18"); + } + + [Test] + public void Fill_Test_PriKey () + { + // Test if Primary Key & Constraints Collection is correct + adapter = new SqlDataAdapter ("select id,type_bit from numeric_family", + connectionString); + adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; + data = new DataSet ("test1"); + adapter.Fill (data); + Assert.AreEqual (1, data.Tables[0].PrimaryKey.Length, + "#1 Primary Key shud be set"); + Assert.AreEqual (1, data.Tables[0].Constraints.Count, + "#2 Constraints shud be set"); + Assert.AreEqual (4, data.Tables[0].Rows.Count, + "#3 No Of Rows shud be 4"); + + // Test if data is correctly merged + adapter.Fill (data); + Assert.AreEqual (4, data.Tables[0].Rows.Count, + "#4 No of Row shud still be 4"); + + // Test if rows are appended and not merged + // when primary key is not returned in the result-set + string query = "Select type_int,type_bigint from numeric_family"; + adapter.SelectCommand.CommandText = query; + data = new DataSet ("test2"); + adapter.Fill (data); + Assert.AreEqual (4, data.Tables[0].Rows.Count, + "#5 No of Rows shud be 4"); + adapter.Fill (data); + Assert.AreEqual (8, data.Tables[0].Rows.Count, + "#6 No of Rows shud double now"); + } + + [Test] + public void Fill_Test_Exceptions () + { + adapter = new SqlDataAdapter ("select * from numeric_family", + connectionString); + data = new DataSet ("test1"); + try { + adapter.Fill (data, -1, 0, "numeric_family"); + Assert.Fail ("#1 Exception shud be thrown:Incorrect Arguments"); + }catch (AssertionException e){ + throw e; + }catch (Exception e){ + Assert.AreEqual (typeof(ArgumentException), e.GetType(), + "#2 Incorrect Exception : " + e); + } + + // conn is not closed due to a bug.. + // can be removed later + adapter.SelectCommand.Connection.Close (); + + try { + adapter.Fill (data , 0 , -1 , "numeric_family"); + Assert.Fail ("#3 Exception shud be thrown:Incorrect Arguments"); + }catch (AssertionException e){ + throw e; + }catch (Exception e){ + Assert.AreEqual (typeof(ArgumentException), e.GetType(), + "#4 Incorrect Exception : " + e); + } + // conn is curr not closed.. can be removed later + adapter.SelectCommand.Connection.Close (); + + /* + // NOTE msdotnet contradicts documented behavior + // InvalidOperationException is expected if table is not valid + try { + adapter.Fill (data , 0 , 0 , "invalid_talbe_name"); + }catch (InvalidOperationException e) { + ex= e; + }catch (Exception e){ + Assert.Fail ("#5 Exception shud be thrown : incorrect arugments "); + } + Assert.IsNotNull (ex , "#6 Exception shud be thrown : incorrect args "); + adapter.SelectCommand.Connection.Close (); // tmp .. can be removed once the bug if fixed + ex=null; + */ + + // NOTE msdotnet contradicts documented behavior + // SystemException is expected when Dataset is invalid + // but msdotnet throws ArgumentNullException + try { + adapter.Fill ( null , 0 , 0 , "numeric_family"); + Assert.Fail ( "#7 Exception shud be thrown : Invalid Dataset"); + }catch (AssertionException e){ + throw e ; + }catch (Exception e) { + Assert.AreEqual (typeof(SystemException), e.GetType(), + "#8 Incorrect Exception : " + e); + } + // conn is currently not being closed.. + //need to be removed once behavior is fixed + adapter.SelectCommand.Connection.Close (); + + adapter.SelectCommand.Connection = null; + try { + adapter.Fill (data); + Assert.Fail ("#9 Exception shud be thrown : Invalid Connection"); + }catch (AssertionException e){ + throw e; + }catch (Exception e){ + Assert.AreEqual (typeof(InvalidOperationException), e.GetType(), + "#10 Incorrect Exception : " + e); + } + } + + [Test] + public void GetFillParametersTest () + { + string query = "select id, type_bit from numeric_family where id > @param1"; + adapter = new SqlDataAdapter (query, connectionString); + IDataParameter[] param = adapter.GetFillParameters (); + Assert.AreEqual (0, param.Length, "#1 size shud be 0"); + + SqlParameter param1 = new SqlParameter (); + param1.ParameterName = "@param1"; + param1.Value = 2; + adapter.SelectCommand.Parameters.Add (param1); + + param = adapter.GetFillParameters (); + Assert.AreEqual (1, param.Length, "#2 count shud be 1"); + Assert.AreEqual (param1, param[0], "#3 Params shud be equal"); + } + + [Test] + public void FillSchemaTest () + { + string query = ""; + + // Test if connection is closed if excepton occurs during fill schema + query = "select * from invalid_table"; + adapter = new SqlDataAdapter (query, connectionString); + data = new DataSet ("test"); + try { + adapter.FillSchema (data , SchemaType.Source); + }catch (Exception e){ + if ( adapter.SelectCommand.Connection.State != ConnectionState.Closed) + { + Assert.Fail ("#0 Conn shud be closed if exception occurs"); + adapter.SelectCommand.Connection.Close(); + } + } + + // Test Primary Key is set (since primary key column returned) + query = "select id, type_int from numeric_family where id=1"; + adapter = new SqlDataAdapter (query, connectionString); + data = new DataSet ("test1"); + adapter.FillSchema (data , SchemaType.Source); + + Assert.AreEqual (1, data.Tables[0].PrimaryKey.Length, + "#1 Primary Key property must be set"); + // Test Primary Key is not set (since primary key column is returned) + query = "select type_bit, type_int from numeric_family where id=1"; + adapter = new SqlDataAdapter (query, connectionString); + data = new DataSet ("test2"); + adapter.FillSchema (data, SchemaType.Source); + Assert.AreEqual (0, data.Tables[0].PrimaryKey.Length, + "#2 Primary Key property should not be set"); + + // Test multiple tables are created for a batch query + query = "Select id ,type_bit from numeric_family;" ; + query += "Select id,type_bit,type_int from numeric_family;"; + data = new DataSet ("test3"); + adapter = new SqlDataAdapter (query, connectionString); + adapter.FillSchema (data , SchemaType.Source); + Assert.AreEqual (2 , data.Tables.Count , "#3 A table shud be created for each Result Set"); + Assert.AreEqual (2 , data.Tables[0].Columns.Count , "#4 should have 2 columns"); + Assert.AreEqual (3 , data.Tables[1].Columns.Count , "#5 Should have 3 columns"); + + // Test if table names and column names are filled correctly + query = "select 10,20 from numeric_family;" ; + query += "select 10,20 from numeric_family;"; + adapter = new SqlDataAdapter (query, connectionString); + data = new DataSet ("test4"); + try { + adapter.FillSchema (data , SchemaType.Source); + }catch (Exception e){ + Assert.Fail ("#3 Unexpected Exception : " + e); + } + Assert.AreEqual ( "Table", data.Tables[0].TableName); + Assert.AreEqual ( "Table1", data.Tables[1].TableName); + Assert.AreEqual ( "Column1", data.Tables[0].Columns[0].ColumnName, + "#6 Unnamed col shud be named as 'ColumnN'"); + Assert.AreEqual ( "Column2", data.Tables[0].Columns[1].ColumnName, + "#7 Unnamed col shud be named as 'ColumnN'"); + Assert.AreEqual ( "Column1", data.Tables[1].Columns[0].ColumnName, + "#8 Unnamed col shud be named as 'ColumnN'"); + Assert.AreEqual ( "Column2", data.Tables[1].Columns[1].ColumnName, + "#9 Unnamed col shud be named as 'ColumnN'"); + Assert.AreEqual (ConnectionState.Closed, adapter.SelectCommand.Connection.State, + "#10 Connection shud be closed"); + + // Test if mapping works correctly + // doesent work in both mono and msdotnet + // gotto check if something is wrong + /* + query = "select id,type_bit from numeric_family"; + adapter = new SqlDataAdapter (query, connectionString); + data = new DataSet ("test"); + DataTable table = data.Tables.Add ("numeric_family_1"); + table.Columns.Add ("id"); + table.Columns.Add ("type_bit"); + DataTableMapping map = adapter.TableMappings.Add("numeric_family_1", + "numeric_family"); + map.ColumnMappings.Add ("id", "id_1"); + map.ColumnMappings.Add ("type_bit", "type_bit_1"); + adapter.FillSchema (data, SchemaType.Source, "numeric_family"); + foreach (DataTable tab in data.Tables){ + Console.WriteLine ("Table == {0}",tab.TableName); + foreach (DataColumn col in tab.Columns) + Console.WriteLine (" Col = {0} " , col.ColumnName); + } + */ + } + + [Test] + public void MissingSchemaActionTest () + { + adapter = new SqlDataAdapter ( + "select id,type_bit,type_int from numeric_family where id<=4", + connectionString); + data = new DataSet (); + Assert.AreEqual (MissingSchemaAction.Add, adapter.MissingSchemaAction, + "#1 Default Value"); + + adapter.Fill (data); + Assert.AreEqual (1, data.Tables.Count , "#1 One table shud be populated"); + Assert.AreEqual (3, data.Tables[0].Columns.Count, "#2 Missing cols are added"); + Assert.AreEqual (0, data.Tables[0].PrimaryKey.Length, "#3 Default Value"); + + adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey; + data.Reset(); + adapter.Fill (data); + Assert.AreEqual (3, data.Tables[0].Columns.Count, + "#4 Missing cols are added"); + Assert.AreEqual (1, data.Tables[0].PrimaryKey.Length, "#5 Default Value"); + + adapter.MissingSchemaAction = MissingSchemaAction.Ignore ; + data.Reset (); + adapter.Fill (data); + Assert.AreEqual (0, data.Tables.Count, "#6 Data shud be ignored"); + + adapter.MissingSchemaAction = MissingSchemaAction.Error ; + data.Reset(); + try { + adapter.Fill (data); + Assert.Fail ("#8 Exception shud be thrown: Schema Mismatch"); + }catch (AssertionException e) { + throw e; + }catch (Exception e){ + Assert.AreEqual (typeof(InvalidOperationException), e.GetType(), + "#9 Incorrect Exception : "+e); + } + + // Test for invalid MissingSchema Value + try { + adapter.MissingSchemaAction = (MissingSchemaAction)(-5000); + Assert.Fail ("#10 Exception shud be thrown: Invalid Value"); + }catch (AssertionException e){ + throw e; + }catch (Exception e){ + Assert.AreEqual (typeof(ArgumentException), e.GetType(), + "#11 Incorrect Exception : " +e); + } + + // Tests if Data is filled correctly if schema is defined + // manually and MissingSchemaAction.Error is set + adapter.MissingSchemaAction = MissingSchemaAction.Error; + data.Reset(); + DataTable table = data.Tables.Add ("Table"); + table.Columns.Add ("id"); + table.Columns.Add ("type_bit"); + table.Columns.Add ("type_int"); + try { + adapter.Fill (data); + Assert.AreEqual (1, data.Tables.Count, "#12"); + Assert.AreEqual (4, data.Tables[0].Rows.Count, "#13"); + }catch (Exception e) { + Assert.Fail ("#12 Unexpected Exception : " + e); + } + } + + [Test] + public void MissingMappingActionTest () + { + adapter = new SqlDataAdapter ("select id,type_bit from numeric_family where id=1", + connectionString); + data = new DataSet (); + Assert.AreEqual (adapter.MissingMappingAction, + MissingMappingAction.Passthrough, + "#1 Default Value"); + adapter.Fill(data); + Assert.AreEqual (1, data.Tables.Count, + "#2 One Table shud be created"); + Assert.AreEqual (2, data.Tables[0].Columns.Count, + "#3 Two Cols shud be created"); + + adapter.MissingMappingAction = MissingMappingAction.Ignore; + data.Reset (); + adapter.Fill (data); + Assert.AreEqual (0, data.Tables.Count, "#4 No table shud be created"); + + adapter.MissingMappingAction = MissingMappingAction.Error; + data.Reset (); + try { + adapter.Fill (data); + Assert.Fail ("#5 Exception shud be thrown : Mapping is missing"); + }catch (AssertionException e){ + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof(InvalidOperationException), e.GetType(), + "#6 Incorrect Exception : " + e); + } + + try { + adapter.MissingMappingAction = (MissingMappingAction)(-5000); + Assert.Fail ("#7 Exception shud be thrown : Invalid Value"); + }catch (AssertionException e){ + throw e; + }catch (Exception e){ + Assert.AreEqual (typeof(ArgumentException), e.GetType(), + "#8 Incorrect Exception : " +e); + } + + // Test if mapping the column and table names works correctly + adapter.MissingMappingAction = MissingMappingAction.Error; + data.Reset (); + DataTable table = data.Tables.Add ("numeric_family_1"); + table.Columns.Add ("id_1"); + table.Columns.Add ("type_bit_1"); + table.Columns.Add ("type_int_1"); + DataTableMapping tableMap = adapter.TableMappings.Add ("numeric_family", + "numeric_family_1"); + tableMap.ColumnMappings.Add ("id", "id_1"); + tableMap.ColumnMappings.Add ("type_bit", "type_bit_1"); + tableMap.ColumnMappings.Add ("type_int", "type_int_1"); + adapter.Fill (data,"numeric_family"); + Assert.AreEqual (1, data.Tables.Count , + "#8 The DataTable shud be correctly mapped"); + Assert.AreEqual (3, data.Tables[0].Columns.Count, + "#9 The DataColumns shud be corectly mapped"); + Assert.AreEqual (1, data.Tables[0].Rows.Count, + "#10 Data shud be populated if mapping is correct"); + } } } diff --git a/mcs/class/System.Data/Test/ProviderTests/System.Data.SqlClient/SqlDataReaderTest.cs b/mcs/class/System.Data/Test/ProviderTests/System.Data.SqlClient/SqlDataReaderTest.cs index fb786536128..dc041c00c3c 100644 --- a/mcs/class/System.Data/Test/ProviderTests/System.Data.SqlClient/SqlDataReaderTest.cs +++ b/mcs/class/System.Data/Test/ProviderTests/System.Data.SqlClient/SqlDataReaderTest.cs @@ -5,6 +5,7 @@ // Umadevi S (sumadevi@novell.com) // Kornél Pál <http://www.kornelpal.hu/> // Sureshkumar T (tsureshkumar@novell.com) +// Senganal T (tsenganal@novell.com) // // Copyright (c) 2004 Novell Inc., and the individuals listed // on the ChangeLog entries. @@ -31,6 +32,8 @@ using System; using System.Data; +using System.Text; +using System.Data.SqlTypes; using System.Data.Common; using System.Data.SqlClient; @@ -43,7 +46,21 @@ namespace MonoTests.System.Data.SqlClient [Category ("sqlserver")] public class SqlDataReaderTest { - SqlConnection conn; + SqlConnection conn = null; + SqlCommand cmd = null; + SqlDataReader reader = null; + String query = "Select type_{0},type_{1},convert({0},null) from numeric_family where id=1"; + DataSet sqlDataset = null; + + DataTable numericDataTable =null; + DataTable stringDataTable =null; + DataTable binaryDataTable =null; + DataTable datetimeDataTable =null; + + DataRow numericRow = null; + DataRow stringRow = null; + DataRow binaryRow = null; + DataRow datetimeRow = null; [Test] public void ReadEmptyNTextFieldTest () { @@ -87,5 +104,799 @@ namespace MonoTests.System.Data.SqlClient ConnectionManager.Singleton.CloseConnection (); } } + + [TestFixtureSetUp] + public void init () + { + conn = new SqlConnection (ConnectionManager.Singleton.ConnectionString); + cmd = conn.CreateCommand (); + + sqlDataset = (new DataProvider()).GetDataSet (); + + numericDataTable = sqlDataset.Tables["numeric_family"]; + stringDataTable = sqlDataset.Tables["string_family"]; + binaryDataTable = sqlDataset.Tables["binary_family"]; + datetimeDataTable = sqlDataset.Tables["datetime_family"]; + + numericRow = numericDataTable.Select ("id=1")[0]; + stringRow = stringDataTable.Select ("id=1")[0]; + binaryRow = binaryDataTable.Select ("id=1")[0]; + datetimeRow = datetimeDataTable.Select ("id=1")[0]; + } + + [SetUp] + public void Setup () + { + conn.Open (); + } + [TearDown] + public void TearDown () + { + if (reader != null) + reader.Close (); + + conn.Close (); + } + + // This method just helps in Calling common tests among all the Get* Methods + // without replicating code + + void CallGetMethod (string s, int i) + { + switch (s) { + case "Boolean" : reader.GetBoolean (i) ; break; + case "SqlBoolean": reader.GetSqlBoolean (i); break; + case "Int16" : reader.GetInt16 (i); break; + case "SqlInt16" : reader.GetSqlInt16 (i); break; + case "Int32" : reader.GetInt32 (i);break; + case "SqlInt32" : reader.GetSqlInt32(i);break; + case "Int64" : reader.GetInt64 (i);break; + case "SqlInt64" : reader.GetSqlInt64(i); break; + case "Decimal" : reader.GetDecimal(i);break; + case "SqlDecimal" : reader.GetSqlDecimal (i);break; + case "SqlMoney" : reader.GetSqlMoney (i);break; + case "Float" : reader.GetFloat (i);break; + case "SqlSingle" : reader.GetSqlSingle(i);break; + case "Double" : reader.GetDouble (i);break; + case "SqlDouble" : reader.GetSqlDouble(i);break; + case "Guid" : reader.GetGuid(i);break; + case "SqlGuid" : reader.GetSqlGuid(i);break; + case "String" : reader.GetString(i);break; + case "SqlString" : reader.GetSqlString(i);break; + case "Char" : reader.GetChar(i);break; + case "Byte" : reader.GetByte (i);break; + case "SqlByte" : reader.GetSqlByte(i); break; + case "DateTime" : reader.GetDateTime(i); break; + case "SqlDateTime" : reader.GetSqlDateTime(i); break; + case "SqlBinary" : reader.GetSqlBinary(i); break; + default : Console.WriteLine ("OOOOPSSSSSS {0}",s);break; + } + } + + // This method just helps in Calling common tests among all the Get* Methods + // without replicating code + void GetMethodTests (string s) + { + + try { + CallGetMethod (s, 1); + Assert.Fail ("#1[Get"+s+"] InvalidCastException must be thrown"); + }catch (AssertionException e) { + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof(InvalidCastException), e.GetType(), + "#2[Get"+s+"] Incorrect Exception : " + e); + } + + // GetSql* Methods do not throw SqlNullValueException + // So, Testimg only for Get* Methods + if (!s.StartsWith("Sql")) { + try { + CallGetMethod (s, 2); + Assert.Fail ("#3[Get"+s+"] Exception must be thrown"); + }catch (AssertionException e) { + throw e; + }catch (Exception e){ + Assert.AreEqual (typeof(SqlNullValueException),e.GetType(), + "#4[Get"+s+"] Incorrect Exception : " + e); + } + } + + try { + CallGetMethod (s, 3); + Assert.Fail ("#5[Get"+s+"] IndexOutOfRangeException must be thrown"); + }catch (AssertionException e) { + throw e; + }catch (Exception e){ + Assert.AreEqual (typeof(IndexOutOfRangeException), e.GetType(), + "#6[Get"+s+"] Incorrect Exception : " + e); + } + } + + [Test] + public void GetBooleanTest () + { + cmd.CommandText = string.Format (query, "bit", "int"); + reader = cmd.ExecuteReader (); + reader.Read (); + // Test for standard exceptions + GetMethodTests("Boolean"); + + // Test if data is returned correctly + Assert.AreEqual (numericRow["type_bit"], reader.GetBoolean(0), + "#2 DataValidation Failed"); + + // Test for standard exceptions + GetMethodTests("SqlBoolean"); + + // Test if data is returned correctly + Assert.AreEqual (numericRow["type_bit"], reader.GetSqlBoolean(0).Value, + "#4 DataValidation Failed"); + reader.Close (); + } + + [Test] + public void GetByteTest () + { + cmd.CommandText = string.Format (query, "tinyint", "int"); + reader = cmd.ExecuteReader (); + reader.Read (); + // Test for standard exceptions + GetMethodTests("Byte"); + + // Test if data is returned correctly + Assert.AreEqual (numericRow["type_tinyint"], reader.GetByte(0), + "#2 DataValidation Failed"); + + // Test for standard exceptions + GetMethodTests("SqlByte"); + + // Test if data is returned correctly + Assert.AreEqual (numericRow["type_tinyint"], reader.GetSqlByte(0).Value, + "#4 DataValidation Failed"); + reader.Close (); + } + + [Test] + public void GetInt16Test () + { + cmd.CommandText = string.Format (query, "smallint", "int"); + reader = cmd.ExecuteReader(); + reader.Read (); + // Test for standard exceptions + GetMethodTests("Int16"); + + // Test if data is returned correctly + Assert.AreEqual (numericRow["type_smallint"], reader.GetInt16(0), + "#2 DataValidation Failed"); + + // Test for standard exceptions + GetMethodTests("SqlInt16"); + + // Test if data is returned correctly + Assert.AreEqual (numericRow["type_smallint"], reader.GetSqlInt16(0).Value, + "#4 DataValidation Failed"); + reader.Close (); + } + + [Test] + public void GetInt32Test () + { + cmd.CommandText = string.Format (query, "int", "bigint"); + reader = cmd.ExecuteReader (); + reader.Read (); + // Test for standard exceptions + GetMethodTests("Int32"); + + // Test if data is returned correctly + Assert.AreEqual (numericRow["type_int"], reader.GetInt32(0), + "#2 DataValidation Failed"); + + // Test for standard exceptions + GetMethodTests("SqlInt32"); + + // Test if data is returned correctly + Assert.AreEqual (numericRow["type_int"], reader.GetSqlInt32(0).Value, + "#4 DataValidation Failed"); + reader.Close (); + } + + [Test] + public void GetInt64Test () + { + cmd.CommandText = string.Format (query, "bigint", "int"); + reader = cmd.ExecuteReader (); + reader.Read (); + + // Test for standard exceptions + GetMethodTests("Int64"); + + // Test if data is returned correctly + Assert.AreEqual (numericRow["type_bigint"], reader.GetInt64(0), + "#2 DataValidation Failed"); + + // Test for standard exceptions + GetMethodTests("SqlInt64"); + + // Test if data is returned correctly + Assert.AreEqual (numericRow["type_bigint"], reader.GetSqlInt64(0).Value, + "#4 DataValidation Failed"); + reader.Close (); + } + + [Test] + public void GetDecimalTest () + { + cmd.CommandText = string.Format (query, "decimal", "int"); + reader = cmd.ExecuteReader (); + reader.Read (); + // Test for standard exceptions + GetMethodTests("Decimal"); + + // Test if data is returned correctly + Assert.AreEqual (numericRow["type_decimal"], reader.GetDecimal(0), + "#2 DataValidation Failed"); + + // Test for standard exceptions + GetMethodTests("SqlDecimal"); + + // Test if data is returned correctly + Assert.AreEqual (numericRow["type_decimal"], reader.GetSqlDecimal(0).Value, + "#4 DataValidation Failed"); + reader.Close (); + } + + [Test] + public void GetSqlMoneyTest () + { + cmd.CommandText = string.Format (query, "money", "int"); + reader = cmd.ExecuteReader (); + reader.Read (); + // Test for standard exceptions + GetMethodTests("SqlMoney"); + + // Test if data is returned correctly + Assert.AreEqual (numericRow["type_money"], reader.GetSqlMoney(0).Value, + "#2 DataValidation Failed"); + reader.Close (); + } + + [Test] + public void GetFloatTest () + { + cmd.CommandText = "select type_float,type_double,convert(real,null)"; + cmd.CommandText += "from numeric_family where id=1"; + reader = cmd.ExecuteReader (); + reader.Read (); + // Test for standard exceptions + GetMethodTests("Float"); + + // Test if data is returned correctly + Assert.AreEqual (numericRow["type_float"], reader.GetFloat(0), + "#2 DataValidation Failed"); + + // Test for standard exceptions + GetMethodTests("SqlSingle"); + + // Test if data is returned correctly + Assert.AreEqual (numericRow["type_float"], reader.GetSqlSingle(0).Value, + "#2 DataValidation Failed"); + reader.Close (); + } + + [Test] + public void GetDoubleTest () + { + cmd.CommandText = "select type_double,type_float,convert(float,null)"; + cmd.CommandText += " from numeric_family where id=1"; + reader = cmd.ExecuteReader (); + reader.Read (); + // Test for standard exceptions + GetMethodTests("Double"); + + // Test if data is returned correctly + Assert.AreEqual (numericRow["type_double"], reader.GetDouble(0), + "#2 DataValidation Failed"); + + // Test for standard exceptions + GetMethodTests("SqlDouble"); + + // Test if data is returned correctly + Assert.AreEqual (numericRow["type_double"], reader.GetSqlDouble(0).Value, + "#4 DataValidation Failed"); + reader.Close (); + } + + [Test] + public void GetBytesTest () + { + cmd.CommandText = "Select type_text,type_ntext,convert(text,null) "; + cmd.CommandText += "from string_family where id=1"; + reader = cmd.ExecuteReader (); + reader.Read (); + try { + reader.GetBytes (0,0,null,0,0); + Assert.Fail ("#1 GetBytes shud be used only wth Sequential Access"); + }catch (AssertionException e) { + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof(InvalidCastException), e.GetType (), + "#2 Incorrect Exception : " + e); + } + reader.Close (); + + byte[] asciiArray = (new ASCIIEncoding ()).GetBytes ("text"); + byte[] unicodeArray = (new UnicodeEncoding ()).GetBytes ("ntext"); + byte[] buffer = null ; + long size = 0; + + reader = cmd.ExecuteReader (CommandBehavior.SequentialAccess); + reader.Read (); + size = reader.GetBytes (0,0,null,0,0); + Assert.AreEqual (asciiArray.Length, size, "#3 Data Incorrect"); + + buffer = new byte[size]; + size = reader.GetBytes (0,0,buffer,0,(int)size); + for (int i=0;i<size; i++) + Assert.AreEqual (asciiArray[i], buffer[i], "#4 Data Incorrect"); + + size = reader.GetBytes (1,0,null,0,0); + Assert.AreEqual (unicodeArray.Length, size, "#5 Data Incorrect"); + buffer = new byte[size]; + size = reader.GetBytes (1,0,buffer,0,(int)size); + for (int i=0;i<size; i++) + Assert.AreEqual (unicodeArray[i], buffer[i], "#6 Data Incorrect"); + + // Test if msdotnet behavior s followed when null value + // is read using GetBytes + Assert.AreEqual (0, reader.GetBytes (2,0,null,0,0), "#7"); + reader.GetBytes (2,0,buffer,0,10); + + reader.Close (); + // do i need to test for image/binary values also ??? + } + + [Test] + public void GetStringTest () + { + cmd.CommandText = "Select type_varchar,10,convert(varchar,null)"; + cmd.CommandText += "from string_family where id=1"; + reader = cmd.ExecuteReader (); + reader.Read (); + // Test for standard exceptions + GetMethodTests("String"); + + // Test if data is returned correctly + Assert.AreEqual (stringRow["type_varchar"], reader.GetString(0), + "#2 DataValidation Failed"); + + // Test for standard exceptions + GetMethodTests("SqlString"); + + // Test if data is returned correctly + Assert.AreEqual (stringRow["type_varchar"], reader.GetSqlString(0).Value, + "#4 DataValidation Failed"); + reader.Close(); + } + + [Test] + public void GetSqlBinaryTest () + { + cmd.CommandText = "Select type_binary ,10 ,convert(binary,null)"; + cmd.CommandText += "from binary_family where id=1"; + reader = cmd.ExecuteReader (); + reader.Read (); + // Test for standard exceptions + GetMethodTests ("SqlBinary"); + + // Test if data is returned correctly + Assert.AreEqual (binaryRow["type_binary"], reader.GetSqlBinary(0).Value, + "#2 DataValidation Failed"); + reader.Close (); + } + + [Test] + public void GetGuidTest () + { + cmd.CommandText = "Select type_guid,id,convert(uniqueidentifier,null)"; + cmd.CommandText += "from string_family where id=1"; + reader = cmd.ExecuteReader (); + reader.Read (); + + // Test for standard exceptions + GetMethodTests("Guid"); + + // Test if data is returned correctly + Assert.AreEqual (stringRow["type_guid"], reader.GetGuid(0), + "#2 DataValidation Failed"); + + // Test for standard exceptions + GetMethodTests("SqlGuid"); + + // Test if data is returned correctly + Assert.AreEqual (stringRow["type_guid"], reader.GetSqlGuid(0).Value, + "#4 DataValidation Failed"); + reader.Close (); + } + + [Test] + public void GetDateTimeTest () + { + cmd.CommandText = "Select type_datetime,10,convert(datetime,null)"; + cmd.CommandText += "from datetime_family where id=1"; + reader = cmd.ExecuteReader (); + reader.Read (); + + // Test for standard exceptions + GetMethodTests("DateTime"); + + // Test if data is returned correctly + Assert.AreEqual (datetimeRow["type_datetime"], reader.GetDateTime(0), + "#2 DataValidation Failed"); + + // Test for standard exceptions + GetMethodTests("SqlDateTime"); + + // Test if data is returned correctly + Assert.AreEqual (datetimeRow["type_datetime"], reader.GetSqlDateTime(0).Value, + "#2 DataValidation Failed"); + reader.Close (); + } + + [Test] + [Ignore ("Not Supported by msdotnet")] + public void GetCharTest () + { + cmd.CommandText = "Select type_char,type_guid,convert(char,null)"; + cmd.CommandText += "from string_family where id=1"; + reader = cmd.ExecuteReader (); + reader.Read (); + // Test for standard exceptions + GetMethodTests ("Char"); + reader.Close (); + } + + [Test] + public void GetValueTest () + { + cmd.CommandText = "Select id, null from numeric_family where id=1"; + reader = cmd.ExecuteReader (); + reader.Read (); + + object obj = null; + obj = reader.GetValue (0); + Assert.AreEqual ((byte)1, obj, "#1 Shud return the value of id"); + obj = reader.GetValue (1); + Assert.AreEqual (DBNull.Value, obj, "#2 shud return DBNull"); + reader.Close (); + } + + [Test] + public void GetSqlValueTest () + { + cmd.CommandText = "Select id,null from numeric_family where id=1"; + reader = cmd.ExecuteReader (); + reader.Read (); + + Assert.AreEqual ((byte)1, ((SqlByte)reader.GetSqlValue(0)).Value, "#1"); + //Assert.AreEqual (DBNull.Value, reader.GetSqlValue(1), "#2"); + + reader.Close (); + } + + [Test] + public void GetValuesTest () + { + cmd.CommandText = "Select 10,20,30 from numeric_family where id=1"; + reader = cmd.ExecuteReader (); + reader.Read (); + object[] arr = null; + int count = 0; + + arr = new object[1]; + count = reader.GetValues (arr); + Assert.AreEqual (10, (int)arr[0], "#1 Only first object shud be copied"); + Assert.AreEqual (1, count, "#1 return value shud equal objects copied"); + + arr = new object[3]; + count = reader.GetValues (arr); + Assert.AreEqual (3, count, "#2 return value shud equal objects copied"); + + arr = new object[5]; + count = reader.GetValues (arr); + Assert.AreEqual (3, count, "#3 return value shud equal objects copied"); + Assert.IsNull (arr[3], "#4 Only 3 objects shud be copied"); + + reader.Close (); + } + + [Test] + public void GetSqlValuesTest () + { + cmd.CommandText = "Select 10,20,30 from numeric_family where id=1"; + reader = cmd.ExecuteReader (); + reader.Read (); + object[] arr = null; + int count = 0; + + arr = new object[1]; + count = reader.GetSqlValues (arr); + // Something is wrong with types ... gotta figure it out + //Assert.AreEqual (10, arr[0], "#1 Only first object shud be copied"); + Assert.AreEqual (1, count, "#1 return value shud equal objects copied"); + + arr = new object[3]; + count = reader.GetSqlValues (arr); + Assert.AreEqual (3, count, "#2 return value shud equal objects copied"); + + arr = new object[5]; + count = reader.GetSqlValues (arr); + Assert.AreEqual (3, count, "#3 return value shud equal objects copied"); + Assert.IsNull (arr[3], "#4 Only 3 objects shud be copied"); + + reader.Close (); + } + + [Test] + public void isDBNullTest () + { + cmd.CommandText = "select id , null from numeric_family where id=1"; + reader = cmd.ExecuteReader (); + reader.Read (); + + Assert.IsFalse (reader.IsDBNull (0), "#1"); + Assert.IsTrue (reader.IsDBNull (1) , "#2"); + + try { + reader.IsDBNull (10); + Assert.Fail ("#1 Invalid Argument"); + }catch (AssertionException e) { + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof(IndexOutOfRangeException), e.GetType(), + "#1 Incorrect Exception : " + e); + } + } + + [Test] + public void ReadTest () + { + cmd.CommandText = "select id, type_bit from numeric_family where id=1" ; + reader = cmd.ExecuteReader (); + Assert.IsTrue (reader.Read () , "#1"); + Assert.IsFalse (reader.Read (), "#2"); + reader.Close (); + + try { + reader.Read (); + Assert.Fail ("#3 Exception shud be thrown : Reader is closed"); + }catch (AssertionException e) { + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof(InvalidOperationException), e.GetType (), + "#4 Incorrect Exception : " + e); + } + } + + [Test] + public void NextResultTest () + { + cmd.CommandText = "Select id from numeric_family where id=1"; + reader = cmd.ExecuteReader (); + Assert.IsFalse (reader.NextResult (), "#1"); + reader.Close (); + + cmd.CommandText = "select id from numeric_family where id=1;"; + cmd.CommandText += "select type_bit from numeric_family where id=2;"; + reader = cmd.ExecuteReader (); + Assert.IsTrue (reader.NextResult (), "#2"); + Assert.IsFalse (reader.NextResult (), "#3"); + reader.Close (); + + try { + reader.NextResult (); + Assert.Fail ("#4 Exception shud be thrown : Reader is closed"); + }catch (AssertionException e) { + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof(InvalidOperationException), e.GetType (), + "#5 Incorrect Exception : " + e); + } + } + + [Test] + public void GetNameTest () + { + cmd.CommandText = "Select id,10 as gen,20 from numeric_family where id=1"; + reader = cmd.ExecuteReader (); + + Assert.AreEqual ("id" , reader.GetName(0) , "#1"); + Assert.AreEqual ("gen" , reader.GetName(1) , "#2"); + + try { + reader.GetName (3); + Assert.Fail ("#4 Exception shud be thrown"); + }catch (AssertionException e) { + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof(IndexOutOfRangeException), e.GetType(), + "#5 Incorrect Exception : " + e); + } + } + + [Test] + public void GetOrdinalTest () + { + //what is kana-width insensitive ????? + cmd.CommandText = "Select id,10 as gen,20 from numeric_family where id=1"; + reader = cmd.ExecuteReader (); + + Assert.AreEqual (0, reader.GetOrdinal ("id"), "#1"); + Assert.AreEqual (0, reader.GetOrdinal ("ID"), "#2"); + Assert.AreEqual (1, reader.GetOrdinal ("gen"), "#3"); + // Would expect column1,columnn2 etc for unnamed columns, + // but msdotnet return empty string for unnamed columns + Assert.AreEqual (2, reader.GetOrdinal (""), "#4"); + + try { + reader.GetOrdinal ("invalidname"); + }catch (AssertionException e) { + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof (IndexOutOfRangeException), + e.GetType(), "#4 Incorrect Exception : " + e); + } + } + + [Test] + public void GetSchemaTableTest () + { + cmd.CommandText = "Select type_decimal as decimal,id,10 "; + cmd.CommandText += "from numeric_family where id=1"; + reader = cmd.ExecuteReader (CommandBehavior.KeyInfo); + DataTable schemaTable = reader.GetSchemaTable (); + DataRow row0 = schemaTable.Rows[0]; + DataRow row1 = schemaTable.Rows[1]; + + Assert.AreEqual ("decimal", row0["ColumnName"], "#1"); + Assert.AreEqual ("", schemaTable.Rows[2]["ColumnName"], "#2"); + + Assert.AreEqual (0, row0["ColumnOrdinal"], "#2"); + Assert.AreEqual (17, row0["ColumnSize"], "#3"); + Assert.AreEqual (38, row0["NumericPrecision"], "#4"); + Assert.AreEqual (0, row0["NumericScale"], "#5"); + + Assert.AreEqual (false, row0["IsUnique"], "#6"); + // msdotnet returns IsUnique as false for Primary key + // even though table consists of a single Primary Key + //Assert.AreEqual (true, row1["IsUnique"], "#7"); + Assert.AreEqual (false, row0["IsKey"], "#8"); + Assert.AreEqual (true, row1["IsKey"], "#9"); + + //Assert.AreEqual ("servername", row0["BaseServerName"], "#10"); + //Assert.AreEqual ("monotest", row0["BaseCatalogName"], "#11"); + Assert.AreEqual ("type_decimal", row0["BaseColumnName"], "#12"); + //Assert.IsNull(row0["BaseSchemaName"], "#13"); + Assert.AreEqual ("numeric_family", row0["BaseTableName"], "#14"); + Assert.AreEqual (typeof (Decimal), row0["DataType"], "#15"); + Assert.AreEqual (true, row0["AllowDBNull"], "#16"); + Assert.AreEqual (false, row1["AllowDBNull"], "#17"); + //Assert.IsNull(row0["ProviderType"], "#18"); + Assert.AreEqual (true, row0["IsAliased"], "#19"); + Assert.AreEqual (false, row1["IsAliased"], "#20"); + + Assert.AreEqual (false, row0["IsExpression"], "#21"); + Assert.AreEqual (false, row0["IsIdentity"], "#22"); + Assert.AreEqual (false, row0["IsAutoIncrement"], "#23"); + Assert.AreEqual (false, row0["IsRowVersion"], "#24"); + Assert.AreEqual (false, row0["IsHidden"], "#25"); + Assert.AreEqual (false, row0["IsLong"], "#26"); + Assert.AreEqual (false, row0["IsReadOnly"], "#27"); + Assert.AreEqual (true, schemaTable.Rows[2]["IsReadOnly"], "#27"); + + // Test Exception is thrown when reader is closed + reader.Close (); + try { + reader.GetSchemaTable (); + Assert.Fail ("#28 Exception shud be thrown" ); + }catch (AssertionException e) { + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof(InvalidOperationException), e.GetType(), + "#29 Incorrect Exception"); + } + } + + [Test] + public void GetDataTypeNameTest () + { + cmd.CommandText = "Select id,10,null from numeric_family where id=1"; + reader = cmd.ExecuteReader (); + + Assert.AreEqual ("tinyint", reader.GetDataTypeName(0), "#1"); + Assert.AreEqual ("int", reader.GetDataTypeName(1), "#2"); + //need check on windows + Assert.AreEqual ("int", reader.GetDataTypeName(2), "#3"); + try { + reader.GetDataTypeName (10); + Assert.Fail ("#4 Exception shud be thrown"); + }catch (AssertionException e) { + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof(IndexOutOfRangeException), e.GetType(), + "#5 Incorrect Exception : " + e); + } + } + + [Test] + public void GetFieldType () + { + cmd.CommandText = "Select id ,10 , null from numeric_family where id=1"; + reader = cmd.ExecuteReader (); + + Assert.AreEqual ("tinyint", reader.GetDataTypeName(0), "#1"); + Assert.AreEqual ("int", reader.GetDataTypeName(1) , "#2"); + Assert.AreEqual ("int", reader.GetDataTypeName(2), "#3"); + try { + reader.GetDataTypeName (10); + Assert.Fail ("#4 Exception shud be thrown"); + }catch (AssertionException e) { + throw e; + }catch (Exception e) { + Assert.AreEqual (typeof(IndexOutOfRangeException), e.GetType(), + "#5 Incorrect Exception : " + e); + } + } + + // Need to populate the data from a config file + // Will be replaced later + void validateData(string sqlQuery, DataTable table) + { + string fmt = "#TAB[{0}] ROW[{1}] COL[{2}] Data Mismatch"; + + int noOfColumns = table.Columns.Count ; + int i=0; + + cmd.CommandText = sqlQuery ; + reader = cmd.ExecuteReader (); + + while (reader.Read ()){ + for (int j=1; j< noOfColumns ; ++j) + Assert.AreEqual (table.Rows[i][j], reader[j], + String.Format (fmt, table.TableName, i+1, j)); + + i++; + } + reader.Close (); + } + + [Test] + public void NumericDataValidation () + { + validateData ("select * from numeric_family order by id ASC", + numericDataTable); + } + + [Test] + public void StringDataValidation () + { + validateData ("select * from string_family order by id ASC", + stringDataTable); + } + + [Test] + public void BinaryDataValidation () + { + validateData ("select * from binary_family order by id ASC", + binaryDataTable); + } + + [Test] + public void DatetimeDataValidation () + { + validateData ("select * from datetime_family order by id ASC", + datetimeDataTable); + } + } } diff --git a/mcs/class/System.Data/Test/ProviderTests/sql/ChangeLog b/mcs/class/System.Data/Test/ProviderTests/sql/ChangeLog index 1c4a62b06cd..b16ad3e8f4d 100644 --- a/mcs/class/System.Data/Test/ProviderTests/sql/ChangeLog +++ b/mcs/class/System.Data/Test/ProviderTests/sql/ChangeLog @@ -1,3 +1,9 @@ +2005-09-16 Senganal T <tsenganal@novell.com> + + * sqlserver.sql: modified. added string_family, datetime_family, + modified numeric_family values. + created new database mono-test for checking -. + 2005-09-13 Sureshkumar T <tsureshkumar@novell.com> * sybase.sql: added. script file to create test database on diff --git a/mcs/class/System.Data/Test/ProviderTests/sql/sqlserver.sql b/mcs/class/System.Data/Test/ProviderTests/sql/sqlserver.sql index 3761ae87a55..117b41ae1d3 100644 --- a/mcs/class/System.Data/Test/ProviderTests/sql/sqlserver.sql +++ b/mcs/class/System.Data/Test/ProviderTests/sql/sqlserver.sql @@ -1,7 +1,12 @@ -use monotest -go +if exists (select name from sysdatabases where + name = 'mono-test') + drop database [mono-test]; +create database [mono-test]; +grant all privileges on [mono-test] to monotester; + +use monotest; --- =================================== OBJECT NUMERIC_FAMILY ============================ +-- =================================== OBJECT NUMERIC_FAMILY============================ -- TABLE : NUMERIC_FAMILY -- data with id > 6000 is not gaurenteed to be read-only. if exists (select name from sysobjects where @@ -9,25 +14,28 @@ if exists (select name from sysobjects where drop table numeric_family; go -create table numeric_family ( - id int PRIMARY KEY NOT NULL, - type_bit bit NULL, - type_tinyint tinyint NULL, - type_smallint smallint NULL, - type_int int NULL, - type_bigint bigint NULL, - type_decimal decimal (38, 0) NULL, - type_numeric numeric (38, 0) NULL, - type_money money NULL, - type_smallmoney smallmoney NULL); +create table numeric_family(id tinyint PRIMARY KEY NOT NULL, + type_bit bit NULL, + type_tinyint tinyint NULL, + type_smallint smallint NULL, + type_int int NULL, + type_bigint bigint NULL, + type_decimal decimal(38,0) NULL, + type_numeric numeric(38,0) NULL, + type_money money NULL, + type_smallmoney smallmoney NULL, + type_float real NULL, + type_double float NULL); go -insert into numeric_family values (1,1,255,32767,2147483647,9223372036854775807,1000,1000,922337203685477.5807,214748.3647); -insert into numeric_family values (2,0,0,-32768,-2147483648,-9223372036854775808,-1000,-1000,-922337203685477.5808,-214748.3648); -insert into numeric_family values (3,0,0,0,0,0,0,0,0,0); -insert into numeric_family values (4,null,null,null,null,null,null,null,null,null); +grant all privileges on numeric_family to monotester; go +insert into numeric_family values (1,1,255,32767,2147483647,9223372036854775807,1000,1000,922337203685477.5807,214748.3647,3.40E+38,1.79E+308); +insert into numeric_family values (2,0,0,-32768,-2147483648,-9223372036854775808,-1000,-1000,-922337203685477.5808,-214748.3648,-3.40E+38,-1.79E+308); +insert into numeric_family values (3,0,0,0,0,0,0,0,0,0,0,0); +insert into numeric_family values (4,null,null,null,null,null,null,null,null,null,null,null); +go -- =================================== END OBJECT NUMERIC_FAMILY ======================== -- =================================== OBJECT BINARY_FAMILY ========================= @@ -39,13 +47,16 @@ if exists (select name from sysobjects where go create table binary_family ( - id int PRIMARY KEY NOT NULL, - type_binary binary NULL, - type_varbinary varbinary (255) NULL, - type_blob image NULL, - type_tinyblob image NULL, - type_mediumblob image NULL, - type_longblob_image image NULL); + id tinyint PRIMARY KEY NOT NULL, + type_binary binary NULL, + type_varbinary varbinary (255) NULL, + type_blob image NULL, + type_tinyblob image NULL, + type_mediumblob image NULL, + type_longblob_image image NULL); +go + +grant all privileges on binary_family to monotester; go insert into binary_family values (1, convert (binary, '555555'), convert (varbinary, '0123456789012345678901234567890123456789012345678901234567890123456789'), @@ -59,6 +70,53 @@ go -- =================================== END OBJECT BINARY_FAMILY ======================== +-- =================================== OBJECT STRING_FAMILY============================ +-- TABLE : string_family +-- data with id above 6000 is not gaurenteed to be read-only. +if exists (select name from sysobjects where + name = 'string_family' and type = 'U') + drop table string_family; +go + +create table string_family(id tinyint PRIMARY KEY NOT NULL, + type_guid uniqueidentifier NULL, + type_char char(10) NULL, + type_varchar varchar(10) NULL, + type_text text NULL, + type_ntext ntext NULL); +go + +grant all privileges on string_family to monotester; +go + +insert into string_family values (1,newid(),"char","varchar","text","ntext"); +insert into string_family values (4,null,null,null,null,null); +go +-- =================================== END OBJECT STRING_FAMILY ======================== + + +-- =================================== OBJECT DATETIME_FAMILY============================ +-- TABLE : datetime_family +-- data with id above 6000 is not gaurenteed to be read-only. + +if exists (select name from sysobjects where + name = 'datetime_family' and type = 'U') + drop table datetime_family; +go + +create table datetime_family ( + id tinyint PRIMARY KEY NOT NULL, + type_smalldatetime smalldatetime NULL, + type_datetime datetime NULL); + +grant all privileges on datetime_family to monotester; +go +insert into datetime_family values (1,'2079-06-06 23:59:00','9999-12-31 23:59:59.997'); +insert into datetime_family values (4,null,null); +go + +-- =================================== END OBJECT DATETIME_FAMILY======================== + -- =================================== OBJECT EMPLOYEE ============================ -- TABLE : EMPLOYEE -- data with id above 6000 is not gaurenteed to be read-only. @@ -74,13 +132,17 @@ create table employee ( dob datetime NOT NULL, doj datetime NOT NULL, email varchar (50) NULL); +go grant all privileges on employee to monotester; +go + insert into employee values (1, 'suresh', 'kumar', '1978-08-22', '2001-03-12', 'suresh@gmail.com'); insert into employee values (2, 'ramesh', 'rajendran', '1977-02-15', '2005-02-11', 'ramesh@yahoo.com'); insert into employee values (3, 'venkat', 'ramakrishnan', '1977-06-12', '2003-12-11', 'ramesh@yahoo.com'); insert into employee values (4, 'ramu', 'dhasarath', '1977-02-15', '2005-02-11', 'ramesh@yahoo.com'); + go -- STORED PROCEDURES @@ -114,6 +176,4 @@ begin end go - - -- =================================== END OBJECT EMPLOYEE ============================ |