1 // created on 27/12/2002 at 17:05
4 // Francisco Figueiredo Jr. <fxjrlists@yahoo.com>
6 // Copyright (C) 2002 The Npgsql Development Team
7 // npgsql-general@gborg.postgresql.org
8 // http://gborg.postgresql.org/project/npgsql/projdisplay.php
10 // This library is free software; you can redistribute it and/or
11 // modify it under the terms of the GNU Lesser General Public
12 // License as published by the Free Software Foundation; either
13 // version 2.1 of the License, or (at your option) any later version.
15 // This library is distributed in the hope that it will be useful,
16 // but WITHOUT ANY WARRANTY; without even the implied warranty of
17 // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
18 // Lesser General Public License for more details.
20 // You should have received a copy of the GNU Lesser General Public
21 // License along with this library; if not, write to the Free Software
22 // Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
26 using System.Web.UI.WebControls;
29 using NUnit.Framework;
36 public class DataReaderTests
39 private NpgsqlConnection _conn = null;
40 private String _connString = "Server=localhost;User ID=npgsql_tests;Password=npgsql_tests;Database=npgsql_tests;maxpoolsize=2;";
43 protected void SetUp()
45 //NpgsqlEventLog.Level = LogLevel.None;
46 //NpgsqlEventLog.Level = LogLevel.Debug;
47 //NpgsqlEventLog.LogName = "NpgsqlTests.LogFile";
48 _conn = new NpgsqlConnection(_connString);
52 protected void TearDown()
54 if (_conn.State != ConnectionState.Closed)
59 public void GetBoolean()
63 NpgsqlCommand command = new NpgsqlCommand("select * from tablea where field_serial = 4;", _conn);
65 NpgsqlDataReader dr = command.ExecuteReader();
68 Boolean result = dr.GetBoolean(4);
69 Assert.AreEqual(true, result);
75 public void GetChars()
78 NpgsqlCommand command = new NpgsqlCommand("select * from tablea where field_serial = 1;", _conn);
80 NpgsqlDataReader dr = command.ExecuteReader();
83 Char[] result = new Char[6];
86 Int64 a = dr.GetChars(1, 0, result, 0, 6);
88 Assert.AreEqual("Random", new String(result));
89 /*ConsoleWriter cw = new ConsoleWriter(Console.Out);
91 cw.WriteLine(result);*/
97 public void GetInt32()
100 NpgsqlCommand command = new NpgsqlCommand("select * from tablea where field_serial = 2;", _conn);
102 NpgsqlDataReader dr = command.ExecuteReader();
107 Int32 result = dr.GetInt32(2);
109 //ConsoleWriter cw = new ConsoleWriter(Console.Out);
111 //cw.WriteLine(result.GetType().Name);
112 Assert.AreEqual(4, result);
118 public void GetInt16()
121 NpgsqlCommand command = new NpgsqlCommand("select * from tableb where field_serial = 1;", _conn);
123 NpgsqlDataReader dr = command.ExecuteReader();
127 Int16 result = dr.GetInt16(1);
129 Assert.AreEqual(2, result);
135 public void GetDecimal()
138 NpgsqlCommand command = new NpgsqlCommand("select * from tableb where field_serial = 3;", _conn);
140 NpgsqlDataReader dr = command.ExecuteReader();
144 Decimal result = dr.GetDecimal(3);
147 Assert.AreEqual(4.2300000M, result);
155 public void GetDouble()
158 NpgsqlCommand command = new NpgsqlCommand("select * from tabled where field_serial = 2;", _conn);
160 NpgsqlDataReader dr = command.ExecuteReader();
164 //Double result = Double.Parse(dr.GetInt32(2).ToString());
165 Double result = dr.GetDouble(2);
167 Assert.AreEqual(.123456789012345D, result);
173 public void GetFloat()
176 NpgsqlCommand command = new NpgsqlCommand("select * from tabled where field_serial = 1;", _conn);
178 NpgsqlDataReader dr = command.ExecuteReader();
182 //Single result = Single.Parse(dr.GetInt32(2).ToString());
183 Single result = dr.GetFloat(1);
185 Assert.AreEqual(.123456F, result);
191 public void GetString()
194 NpgsqlCommand command = new NpgsqlCommand("select * from tablea where field_serial = 1;", _conn);
196 NpgsqlDataReader dr = command.ExecuteReader();
200 String result = dr.GetString(1);
202 Assert.AreEqual("Random text", result);
208 public void GetStringWithParameter()
211 NpgsqlCommand command = new NpgsqlCommand("select * from tablea where field_text = :value;", _conn);
213 String test = "Random text";
214 NpgsqlParameter param = new NpgsqlParameter();
215 param.ParameterName = "value";
216 param.DbType = DbType.String;
217 //param.NpgsqlDbType = NpgsqlDbType.Text;
218 param.Size = test.Length;
220 command.Parameters.Add(param);
222 NpgsqlDataReader dr = command.ExecuteReader();
226 String result = dr.GetString(1);
228 Assert.AreEqual(test, result);
233 public void GetStringWithQuoteWithParameter()
236 NpgsqlCommand command = new NpgsqlCommand("select * from tablea where field_text = :value;", _conn);
238 String test = "Text with ' single quote";
239 NpgsqlParameter param = new NpgsqlParameter();
240 param.ParameterName = "value";
241 param.DbType = DbType.String;
242 //param.NpgsqlDbType = NpgsqlDbType.Text;
243 param.Size = test.Length;
245 command.Parameters.Add(param);
247 NpgsqlDataReader dr = command.ExecuteReader();
251 String result = dr.GetString(1);
253 Assert.AreEqual(test, result);
259 public void GetValueByName()
262 NpgsqlCommand command = new NpgsqlCommand("select * from tablea where field_serial = 1;", _conn);
264 NpgsqlDataReader dr = command.ExecuteReader();
268 String result = (String) dr["field_text"];
270 Assert.AreEqual("Random text", result);
275 [ExpectedException(typeof(InvalidOperationException))]
276 public void GetValueFromEmptyResultset()
279 NpgsqlCommand command = new NpgsqlCommand("select * from tablea where field_text = :value;", _conn);
281 String test = "Text single quote";
282 NpgsqlParameter param = new NpgsqlParameter();
283 param.ParameterName = "value";
284 param.DbType = DbType.String;
285 //param.NpgsqlDbType = NpgsqlDbType.Text;
286 param.Size = test.Length;
288 command.Parameters.Add(param);
290 NpgsqlDataReader dr = command.ExecuteReader();
295 // This line should throw the invalid operation exception as the datareader will
296 // have an empty resultset.
297 Console.WriteLine(dr.IsDBNull(1));
304 public void TestOverlappedParameterNames()
308 NpgsqlCommand command = new NpgsqlCommand("select * from tablea where field_serial = :a or field_serial = :aa", _conn);
309 command.Parameters.Add(new NpgsqlParameter("a", DbType.Int32, 4, "a"));
310 command.Parameters.Add(new NpgsqlParameter("aa", DbType.Int32, 4, "aa"));
312 command.Parameters[0].Value = 2;
313 command.Parameters[1].Value = 3;
315 NpgsqlDataReader dr = command.ExecuteReader();
320 [ExpectedException(typeof(IndexOutOfRangeException))]
321 public void TestNonExistentParameterName()
325 NpgsqlCommand command = new NpgsqlCommand("select * from tablea where field_serial = :a or field_serial = :aa", _conn);
326 command.Parameters.Add(new NpgsqlParameter(":b", DbType.Int32, 4, "b"));
327 command.Parameters.Add(new NpgsqlParameter(":aa", DbType.Int32, 4, "aa"));
329 command.Parameters[0].Value = 2;
330 command.Parameters[1].Value = 3;
332 NpgsqlDataReader dr = command.ExecuteReader();
341 public void UseDataAdapter()
346 NpgsqlCommand command = new NpgsqlCommand("select * from tablea", _conn);
348 NpgsqlDataAdapter da = new NpgsqlDataAdapter();
350 da.SelectCommand = command;
352 DataSet ds = new DataSet();
356 //ds.WriteXml("TestUseDataAdapter.xml");
362 public void UseDataAdapterNpgsqlConnectionConstructor()
367 NpgsqlCommand command = new NpgsqlCommand("select * from tablea", _conn);
369 command.Connection = _conn;
371 NpgsqlDataAdapter da = new NpgsqlDataAdapter(command);
373 DataSet ds = new DataSet();
377 //ds.WriteXml("TestUseDataAdapterNpgsqlConnectionConstructor.xml");
383 public void UseDataAdapterStringNpgsqlConnectionConstructor()
389 NpgsqlDataAdapter da = new NpgsqlDataAdapter("select * from tablea", _conn);
391 DataSet ds = new DataSet();
395 //ds.WriteXml("TestUseDataAdapterStringNpgsqlConnectionConstructor.xml");
402 public void UseDataAdapterStringStringConstructor()
408 NpgsqlDataAdapter da = new NpgsqlDataAdapter("select * from tablea", _connString);
410 DataSet ds = new DataSet();
414 ds.WriteXml("TestUseDataAdapterStringStringConstructor.xml");
420 public void UseDataAdapterStringStringConstructor2()
426 NpgsqlDataAdapter da = new NpgsqlDataAdapter("select * from tableb", _connString);
428 DataSet ds = new DataSet();
432 ds.WriteXml("TestUseDataAdapterStringStringConstructor2.xml");
438 public void DataGridWebControlSupport()
443 NpgsqlCommand command = new NpgsqlCommand("select * from tablea;", _conn);
445 NpgsqlDataReader dr = command.ExecuteReader();
447 DataGrid dg = new DataGrid();
457 [ExpectedException(typeof(InvalidOperationException))]
458 public void ReadPastDataReaderEnd()
461 NpgsqlCommand command = new NpgsqlCommand("select * from tablea;", _conn);
463 NpgsqlDataReader dr = command.ExecuteReader();
472 public void IsDBNull()
475 NpgsqlCommand command = new NpgsqlCommand("select field_text from tablea;", _conn);
477 NpgsqlDataReader dr = command.ExecuteReader();
480 Assert.AreEqual(false, dr.IsDBNull(0));
482 Assert.AreEqual(true, dr.IsDBNull(0));
488 public void IsDBNullFromScalar()
491 NpgsqlCommand command = new NpgsqlCommand("select max(field_serial) from tablea;", _conn);
493 NpgsqlDataReader dr = command.ExecuteReader();
496 Assert.AreEqual(false, dr.IsDBNull(0));
503 public void TypesNames()
506 NpgsqlCommand command = new NpgsqlCommand("select * from tablea where 1 = 2;", _conn);
508 NpgsqlDataReader dr = command.ExecuteReader();
512 Assert.AreEqual("int4", dr.GetDataTypeName(0));
513 Assert.AreEqual("text", dr.GetDataTypeName(1));
514 Assert.AreEqual("int4", dr.GetDataTypeName(2));
515 Assert.AreEqual("int8", dr.GetDataTypeName(3));
516 Assert.AreEqual("bool", dr.GetDataTypeName(4));
520 command.CommandText = "select * from tableb where 1 = 2";
522 dr = command.ExecuteReader();
526 Assert.AreEqual("int4", dr.GetDataTypeName(0));
527 Assert.AreEqual("int2", dr.GetDataTypeName(1));
528 Assert.AreEqual("timestamp", dr.GetDataTypeName(2));
529 Assert.AreEqual("numeric", dr.GetDataTypeName(3));