2 // Tests for System.Web.UI.WebControls.SqlDataSource
3 // This test uses Derby, java embedded database.
6 // Vladimir Krasnov (vladimirk@mainsoft.com)
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:
19 // The above copyright notice and this permission notice shall be
20 // included in all copies or substantial portions of the Software.
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.
31 #if NET_2_0 && TARGET_JVM
33 using NUnit.Framework;
35 using System.Configuration;
37 using System.Data.OleDb;
38 using System.Data.Common;
40 using System.Globalization;
43 using System.Web.UI.WebControls;
44 using System.Collections.Specialized;
46 namespace MonoTests.System.Web.UI.WebControls
49 public class SqlDataSourceDerbyTest {
53 if (Directory.Exists (_dataDir))
54 Directory.Delete (_dataDir, true);
56 string initSql = @"CREATE TABLE Table1 (
57 UserId int NOT NULL PRIMARY KEY,
58 UserName varchar(256) NOT NULL,
59 Description varchar(256)
62 OleDbConnection connection = new OleDbConnection (_connectionString);
65 DbCommand cmd = connection.CreateCommand ();
66 cmd.CommandText = initSql;
67 cmd.CommandType = CommandType.Text;
68 cmd.ExecuteNonQuery ();
78 public void SelectTest1 ()
80 SqlDataSource ds = CreateDataSource ();
81 ds.SelectCommand = "SELECT * FROM Table1";
82 DataView dataView = (DataView) ds.Select (new DataSourceSelectArguments ());
84 Assert.AreEqual (10, dataView.Count);
88 public void SelectTest2 ()
90 SqlDataSource ds = CreateDataSource ();
91 ds.SelectCommand = "SELECT * FROM Table1";
92 ds.FilterExpression = "UserId > 5";
94 DataView dataView = (DataView) ds.Select (new DataSourceSelectArguments ());
95 Assert.AreEqual (4, dataView.Count);
99 public void SelectTest3 ()
101 SqlDataSource ds = CreateDataSource ();
102 ds.SelectCommand = "SELECT * FROM Table1";
104 DataView dataView = (DataView) ds.Select (new DataSourceSelectArguments ("Description"));
105 Assert.AreEqual ("Description", dataView.Sort);
109 public void SelectTest4 ()
111 SqlDataSource ds = CreateDataSource ();
112 ds.SelectCommand = "SELECT * FROM Table1";
114 DataView dataView = (DataView) ds.Select (new DataSourceSelectArguments (1, 2));
116 catch (NotSupportedException) {
117 Assert.AreEqual (true, true);
122 public void SelectTest5 ()
124 SqlDataSource ds = CreateDataSource ();
125 ds.SelectCommand = "SELECT * FROM Table1 WHERE UserId = ?";
126 ds.SelectParameters.Add (new Parameter ("UserId", TypeCode.Int32, "5"));
128 DataView dataView = (DataView) ds.Select (new DataSourceSelectArguments ());
129 Assert.AreEqual (1, dataView.Count);
133 public void UpdateTest1 ()
135 SqlDataSource ds = CreateDataSource ();
136 ds.SelectCommand = "SELECT * FROM Table1 WHERE UserName = ?";
137 ds.SelectParameters.Add (new Parameter ("UserName", TypeCode.String, "superuser"));
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"));
143 int records = ds.Update ();
144 DataView dataView = (DataView) ds.Select (new DataSourceSelectArguments ());
145 Assert.AreEqual (1, dataView.Count);
146 Assert.AreEqual (1, records);
151 public void UpdateTest2 ()
153 SqlDataSource ds = CreateDataSource ();
154 ds.SelectCommand = "SELECT * FROM Table1 WHERE UserName = ?";
155 ds.SelectParameters.Add (new Parameter ("UserName", TypeCode.String, "SimpleUser"));
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}";
162 SqlDataSourceView view = (SqlDataSourceView) ((IDataSource) ds).GetView ("");
164 OrderedDictionary keys = new OrderedDictionary ();
165 keys.Add ("UserId", 7);
167 OrderedDictionary values = new OrderedDictionary ();
168 values.Add ("UserName", "SimpleUser");
170 OrderedDictionary oldvalues = new OrderedDictionary ();
171 oldvalues.Add ("UserName", "user7");
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);
181 public void InsertTest1 ()
183 SqlDataSource ds = CreateDataSource ();
184 ds.SelectCommand = "SELECT * FROM Table1 WHERE UserName = ?";
185 ds.SelectParameters.Add (new Parameter ("UserName", TypeCode.String, "newuser"));
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"));
192 int records = ds.Insert ();
193 DataView dataView = (DataView) ds.Select (new DataSourceSelectArguments ());
194 Assert.AreEqual (1, dataView.Count);
195 Assert.AreEqual (1, records);
200 public void InsertTest2 ()
202 SqlDataSource ds = CreateDataSource ();
203 ds.SelectCommand = "SELECT * FROM Table1 WHERE UserName = ?";
204 ds.SelectParameters.Add (new Parameter ("UserName", TypeCode.String, "newuser2"));
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"));
211 SqlDataSourceView view = (SqlDataSourceView) ((IDataSource) ds).GetView ("");
213 OrderedDictionary values = new OrderedDictionary ();
214 values.Add ("UserId", "17");
215 values.Add ("UserName", "newuser2");
216 values.Add ("Description", "newuser2");
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);
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 ()
230 SqlDataSource ds = new SqlDataSource ();
231 ds.ConnectionString = _connectionString;
232 ds.ProviderName = "System.Data.OleDb";
233 ds.DataSourceMode = SqlDataSourceMode.DataSet;
238 public void RestoreData ()
240 string insertSql = @"INSERT INTO Table1 VALUES ({0}, '{1}', '{2}')";
241 string deleteSql = @"DELETE FROM Table1";
243 OleDbConnection connection = new OleDbConnection (_connectionString);
246 DbCommand dc = connection.CreateCommand ();
247 dc.CommandText = deleteSql;
248 dc.CommandType = CommandType.Text;
249 dc.ExecuteNonQuery ();
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 ();