mcs/class/System.Web/Test/System.Web.UI.WebControls/SqlDataSourceDerbyTest.cs

   1 //
   2 // Tests for System.Web.UI.WebControls.SqlDataSource
   3 // This test uses Derby, java embedded database.
   4 //
   5 // Author:
   6 //      Vladimir Krasnov (vladimirk@mainsoft.com)
   7 //
   8
   9 //
  10 //
  11 // Permission is hereby granted, free of charge, to any person obtaining
  12 // a copy of this software and associated documentation files (the
  13 // "Software"), to deal in the Software without restriction, including
  14 // without limitation the rights to use, copy, modify, merge, publish,
  15 // distribute, sublicense, and/or sell copies of the Software, and to
  16 // permit persons to whom the Software is furnished to do so, subject to
  17 // the following conditions:
  18 //
  19 // The above copyright notice and this permission notice shall be
  20 // included in all copies or substantial portions of the Software.
  21 //
  22 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  23 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  24 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  25 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  26 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  27 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  28 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  29 //
  30
  31 #if NET_2_0 && TARGET_JVM
  32
  33 using NUnit.Framework;
  34 using System;
  35 using System.Configuration;
  36 using System.Data;
  37 using System.Data.OleDb;
  38 using System.Data.Common;
  39 using System.IO;
  40 using System.Globalization;
  41 using System.Web;
  42 using System.Web.UI;
  43 using System.Web.UI.WebControls;
  44 using System.Collections.Specialized;
  45
  46 namespace MonoTests.System.Web.UI.WebControls
  47 {
  48         [TestFixture]
  49         public class SqlDataSourceDerbyTest {
  50                 [TestFixtureSetUp]
  51                 public void setup ()
  52                 {
  53                         if (Directory.Exists (_dataDir))
  54                                 Directory.Delete (_dataDir, true);
  55
  56                         string initSql = @"CREATE TABLE Table1 (
  57                                 UserId                                  int                 NOT NULL PRIMARY KEY,
  58                                 UserName                                varchar(256)        NOT NULL,
  59                                 Description                             varchar(256)
  60                         )";
  61
  62                         OleDbConnection connection = new OleDbConnection (_connectionString);
  63                         try {
  64                                 connection.Open ();
  65                                 DbCommand cmd = connection.CreateCommand ();
  66                                 cmd.CommandText = initSql;
  67                                 cmd.CommandType = CommandType.Text;
  68                                 cmd.ExecuteNonQuery ();
  69                         }
  70                         catch (Exception) {
  71                         }
  72                         finally {
  73                                 connection.Close ();
  74                         }
  75                 }
  76
  77                 [Test]
  78                 public void SelectTest1 ()
  79                 {
  80                         SqlDataSource ds = CreateDataSource ();
  81                         ds.SelectCommand = "SELECT * FROM Table1";
  82                         DataView dataView = (DataView) ds.Select (new DataSourceSelectArguments ());
  83
  84                         Assert.AreEqual (10, dataView.Count);
  85                 }
  86
  87                 [Test]
  88                 public void SelectTest2 ()
  89                 {
  90                         SqlDataSource ds = CreateDataSource ();
  91                         ds.SelectCommand = "SELECT * FROM Table1";
  92                         ds.FilterExpression = "UserId > 5";
  93
  94                         DataView dataView = (DataView) ds.Select (new DataSourceSelectArguments ());
  95                         Assert.AreEqual (4, dataView.Count);
  96                 }
  97
  98                 [Test]
  99                 public void SelectTest3 ()
 100                 {
 101                         SqlDataSource ds = CreateDataSource ();
 102                         ds.SelectCommand = "SELECT * FROM Table1";
 103
 104                         DataView dataView = (DataView) ds.Select (new DataSourceSelectArguments ("Description"));
 105                         Assert.AreEqual ("Description", dataView.Sort);
 106                 }
 107
 108                 [Test]
 109                 public void SelectTest4 ()
 110                 {
 111                         SqlDataSource ds = CreateDataSource ();
 112                         ds.SelectCommand = "SELECT * FROM Table1";
 113                         try {
 114                                 DataView dataView = (DataView) ds.Select (new DataSourceSelectArguments (1, 2));
 115                         }
 116                         catch (NotSupportedException) {
 117                                 Assert.AreEqual (true, true);
 118                         }
 119                 }
 120
 121                 [Test]
 122                 public void SelectTest5 ()
 123                 {
 124                         SqlDataSource ds = CreateDataSource ();
 125                         ds.SelectCommand = "SELECT * FROM Table1 WHERE UserId = ?";
 126                         ds.SelectParameters.Add (new Parameter ("UserId", TypeCode.Int32, "5"));
 127
 128                         DataView dataView = (DataView) ds.Select (new DataSourceSelectArguments ());
 129                         Assert.AreEqual (1, dataView.Count);
 130                 }
 131
 132                 [Test]
 133                 public void UpdateTest1 ()
 134                 {
 135                         SqlDataSource ds = CreateDataSource ();
 136                         ds.SelectCommand = "SELECT * FROM Table1 WHERE UserName = ?";
 137                         ds.SelectParameters.Add (new Parameter ("UserName", TypeCode.String, "superuser"));
 138
 139                         ds.UpdateCommand = "UPDATE Table1 SET UserName = ? WHERE UserId = ?";
 140                         ds.UpdateParameters.Add (new Parameter ("UserName", TypeCode.String, "superuser"));
 141                         ds.UpdateParameters.Add (new Parameter ("UserId", TypeCode.Int32, "5"));
 142
 143                         int records = ds.Update ();
 144                         DataView dataView = (DataView) ds.Select (new DataSourceSelectArguments ());
 145                         Assert.AreEqual (1, dataView.Count);
 146                         Assert.AreEqual (1, records);
 147
 148                 }
 149
 150                 [Test]
 151                 public void UpdateTest2 ()
 152                 {
 153                         SqlDataSource ds = CreateDataSource ();
 154                         ds.SelectCommand = "SELECT * FROM Table1 WHERE UserName = ?";
 155                         ds.SelectParameters.Add (new Parameter ("UserName", TypeCode.String, "SimpleUser"));
 156
 157                         ds.UpdateCommand = "UPDATE Table1 SET UserName = ? WHERE UserId = ?";
 158                         ds.UpdateParameters.Add (new Parameter ("UserName", TypeCode.String, "superuser"));
 159                         ds.UpdateParameters.Add (new Parameter ("UserId", TypeCode.Int32, "5"));
 160                         ds.OldValuesParameterFormatString = "original_{0}";
 161
 162                         SqlDataSourceView view = (SqlDataSourceView) ((IDataSource) ds).GetView ("");
 163
 164                         OrderedDictionary keys = new OrderedDictionary ();
 165                         keys.Add ("UserId", 7);
 166
 167                         OrderedDictionary values = new OrderedDictionary ();
 168                         values.Add ("UserName", "SimpleUser");
 169
 170                         OrderedDictionary oldvalues = new OrderedDictionary ();
 171                         oldvalues.Add ("UserName", "user7");
 172
 173                         int records = view.Update (keys, values, oldvalues);
 174                         DataView dataView = (DataView) ds.Select (new DataSourceSelectArguments ());
 175                         Assert.AreEqual (1, dataView.Count);
 176                         Assert.AreEqual (1, records);
 177
 178                 }
 179
 180                 [Test]
 181                 public void InsertTest1 ()
 182                 {
 183                         SqlDataSource ds = CreateDataSource ();
 184                         ds.SelectCommand = "SELECT * FROM Table1 WHERE UserName = ?";
 185                         ds.SelectParameters.Add (new Parameter ("UserName", TypeCode.String, "newuser"));
 186
 187                         ds.InsertCommand = "INSERT INTO Table1 (UserId, UserName, Description) VALUES (?, ?, ?)";
 188                         ds.InsertParameters.Add (new Parameter ("UserId", TypeCode.Int32, "15"));
 189                         ds.InsertParameters.Add (new Parameter ("UserName", TypeCode.String, "newuser"));
 190                         ds.InsertParameters.Add (new Parameter ("Description", TypeCode.String, "newuser"));
 191
 192                         int records = ds.Insert ();
 193                         DataView dataView = (DataView) ds.Select (new DataSourceSelectArguments ());
 194                         Assert.AreEqual (1, dataView.Count);
 195                         Assert.AreEqual (1, records);
 196
 197                 }
 198
 199                 [Test]
 200                 public void InsertTest2 ()
 201                 {
 202                         SqlDataSource ds = CreateDataSource ();
 203                         ds.SelectCommand = "SELECT * FROM Table1 WHERE UserName = ?";
 204                         ds.SelectParameters.Add (new Parameter ("UserName", TypeCode.String, "newuser2"));
 205
 206                         ds.InsertCommand = "INSERT INTO Table1 (UserId, UserName, Description) VALUES (?, ?, ?)";
 207                         ds.InsertParameters.Add (new Parameter ("UserId", TypeCode.Int32, "5"));
 208                         ds.InsertParameters.Add (new Parameter ("UserName", TypeCode.String, "newuser"));
 209                         ds.InsertParameters.Add (new Parameter ("Description", TypeCode.String, "newuser"));
 210
 211                         SqlDataSourceView view = (SqlDataSourceView) ((IDataSource) ds).GetView ("");
 212
 213                         OrderedDictionary values = new OrderedDictionary ();
 214                         values.Add ("UserId", "17");
 215                         values.Add ("UserName", "newuser2");
 216                         values.Add ("Description", "newuser2");
 217
 218                         int records = view.Insert (values);
 219                         DataView dataView = (DataView) ds.Select (new DataSourceSelectArguments ());
 220                         Assert.AreEqual (1, dataView.Count);
 221                         Assert.AreEqual (1, records);
 222
 223                 }
 224
 225
 226                 const string _dataDir = "DataDir";
 227                 const string _connectionString = "JdbcDriverClassName=org.apache.derby.jdbc.EmbeddedDriver;JdbcURL=jdbc:derby:" + _dataDir + ";create=true";
 228                 private SqlDataSource CreateDataSource ()
 229                 {
 230                         SqlDataSource ds = new SqlDataSource ();
 231                         ds.ConnectionString = _connectionString;
 232                         ds.ProviderName = "System.Data.OleDb";
 233                         ds.DataSourceMode = SqlDataSourceMode.DataSet;
 234                         return ds;
 235                 }
 236
 237                 [SetUp]
 238                 public void RestoreData ()
 239                 {
 240                         string insertSql = @"INSERT INTO Table1 VALUES ({0}, '{1}', '{2}')";
 241                         string deleteSql = @"DELETE FROM Table1";
 242
 243                         OleDbConnection connection = new OleDbConnection (_connectionString);
 244                         connection.Open ();
 245                         try {
 246                                 DbCommand dc = connection.CreateCommand ();
 247                                 dc.CommandText = deleteSql;
 248                                 dc.CommandType = CommandType.Text;
 249                                 dc.ExecuteNonQuery ();
 250
 251                                 for (int i = 0; i < 10; i++) {
 252                                         DbCommand ic = connection.CreateCommand ();
 253                                         ic.CommandText = string.Format (insertSql, i.ToString (), "user" + i.ToString (), (9 - i).ToString ());
 254                                         ic.CommandType = CommandType.Text;
 255                                         ic.ExecuteNonQuery ();
 256                                 }
 257                         }
 258                         catch (Exception) {
 259                         }
 260                         finally {
 261                                 connection.Close ();
 262                         }
 263                 }
 264         }
 265
 266 }
 267
 268 #endif