mcs/class/Npgsql/Test/DataReaderTests.cs

   1 // created on 27/12/2002 at 17:05
   2 //
   3 // Author:
   4 //      Francisco Figueiredo Jr. <fxjrlists@yahoo.com>
   5 //
   6 //      Copyright (C) 2002 The Npgsql Development Team
   7 //      npgsql-general@gborg.postgresql.org
   8 //      http://gborg.postgresql.org/project/npgsql/projdisplay.php
   9 //
  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.
  14 //
  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.
  19 //
  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
  23
  24 using System;
  25 using System.Data;
  26 using System.Web.UI.WebControls;
  27 using Npgsql;
  28
  29 using NUnit.Framework;
  30 using NUnit.Core;
  31
  32 namespace NpgsqlTests
  33 {
  34
  35         [TestFixture]
  36         public class DataReaderTests
  37         {
  38
  39                 private NpgsqlConnection        _conn = null;
  40                 private String                                          _connString = "Server=localhost;User ID=npgsql_tests;Password=npgsql_tests;Database=npgsql_tests;maxpoolsize=2;";
  41
  42                 [SetUp]
  43                 protected void SetUp()
  44                 {
  45                         //NpgsqlEventLog.Level = LogLevel.None;
  46                         //NpgsqlEventLog.Level = LogLevel.Debug;
  47                         //NpgsqlEventLog.LogName = "NpgsqlTests.LogFile";
  48                         _conn = new NpgsqlConnection(_connString);
  49                 }
  50
  51                 [TearDown]
  52                 protected void TearDown()
  53                 {
  54                         if (_conn.State != ConnectionState.Closed)
  55                                 _conn.Close();
  56                 }
  57
  58                 [Test]
  59                 public void GetBoolean()
  60                 {
  61                         _conn.Open();
  62
  63                         NpgsqlCommand command = new NpgsqlCommand("select * from tablea where field_serial = 4;", _conn);
  64
  65                         NpgsqlDataReader dr = command.ExecuteReader();
  66
  67                         dr.Read();
  68                         Boolean result = dr.GetBoolean(4);
  69                         Assert.AreEqual(true, result);
  70
  71                 }
  72
  73
  74                 [Test]
  75                 public void GetChars()
  76                 {
  77                         _conn.Open();
  78                         NpgsqlCommand command = new NpgsqlCommand("select * from tablea where field_serial = 1;", _conn);
  79
  80                         NpgsqlDataReader dr = command.ExecuteReader();
  81
  82                         dr.Read();
  83                         Char[] result = new Char[6];
  84
  85
  86                         Int64 a = dr.GetChars(1, 0, result, 0, 6);
  87
  88                         Assert.AreEqual("Random", new String(result));
  89                         /*ConsoleWriter cw = new ConsoleWriter(Console.Out);
  90
  91                         cw.WriteLine(result);*/
  92
  93
  94                 }
  95
  96                 [Test]
  97                 public void GetInt32()
  98                 {
  99                         _conn.Open();
 100                         NpgsqlCommand command = new NpgsqlCommand("select * from tablea where field_serial = 2;", _conn);
 101
 102                         NpgsqlDataReader dr = command.ExecuteReader();
 103
 104                         dr.Read();
 105
 106
 107                         Int32 result = dr.GetInt32(2);
 108
 109                         //ConsoleWriter cw = new ConsoleWriter(Console.Out);
 110
 111                         //cw.WriteLine(result.GetType().Name);
 112                         Assert.AreEqual(4, result);
 113
 114                 }
 115
 116
 117                 [Test]
 118                 public void GetInt16()
 119                 {
 120                         _conn.Open();
 121                         NpgsqlCommand command = new NpgsqlCommand("select * from tableb where field_serial = 1;", _conn);
 122
 123                         NpgsqlDataReader dr = command.ExecuteReader();
 124
 125                         dr.Read();
 126
 127                         Int16 result = dr.GetInt16(1);
 128
 129                         Assert.AreEqual(2, result);
 130
 131                 }
 132
 133
 134                 [Test]
 135                 public void GetDecimal()
 136                 {
 137                         _conn.Open();
 138                         NpgsqlCommand command = new NpgsqlCommand("select * from tableb where field_serial = 3;", _conn);
 139
 140                         NpgsqlDataReader dr = command.ExecuteReader();
 141
 142                         dr.Read();
 143
 144                         Decimal result = dr.GetDecimal(3);
 145
 146
 147                         Assert.AreEqual(4.2300000M, result);
 148
 149                 }
 150
 151
 152
 153
 154                 [Test]
 155                 public void GetDouble()
 156                 {
 157                         _conn.Open();
 158                         NpgsqlCommand command = new NpgsqlCommand("select * from tabled where field_serial = 2;", _conn);
 159
 160                         NpgsqlDataReader dr = command.ExecuteReader();
 161
 162                         dr.Read();
 163
 164                         //Double result = Double.Parse(dr.GetInt32(2).ToString());
 165                   Double result = dr.GetDouble(2);
 166
 167                         Assert.AreEqual(.123456789012345D, result);
 168
 169                 }
 170
 171
 172                 [Test]
 173                 public void GetFloat()
 174                 {
 175                         _conn.Open();
 176                         NpgsqlCommand command = new NpgsqlCommand("select * from tabled where field_serial = 1;", _conn);
 177
 178                         NpgsqlDataReader dr = command.ExecuteReader();
 179
 180                         dr.Read();
 181
 182                         //Single result = Single.Parse(dr.GetInt32(2).ToString());
 183                   Single result = dr.GetFloat(1);
 184
 185                         Assert.AreEqual(.123456F, result);
 186
 187                 }
 188
 189
 190                 [Test]
 191                 public void GetString()
 192                 {
 193                         _conn.Open();
 194                         NpgsqlCommand command = new NpgsqlCommand("select * from tablea where field_serial = 1;", _conn);
 195
 196                         NpgsqlDataReader dr = command.ExecuteReader();
 197
 198                         dr.Read();
 199
 200                         String result = dr.GetString(1);
 201
 202                         Assert.AreEqual("Random text", result);
 203
 204                 }
 205
 206
 207                 [Test]
 208                 public void GetStringWithParameter()
 209                 {
 210                         _conn.Open();
 211                         NpgsqlCommand command = new NpgsqlCommand("select * from tablea where field_text = :value;", _conn);
 212
 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;
 219                         param.Value = test;
 220                         command.Parameters.Add(param);
 221
 222                         NpgsqlDataReader dr = command.ExecuteReader();
 223
 224                         dr.Read();
 225
 226                         String result = dr.GetString(1);
 227
 228                         Assert.AreEqual(test, result);
 229
 230                 }
 231
 232                 [Test]
 233                 public void GetStringWithQuoteWithParameter()
 234                 {
 235                         _conn.Open();
 236                         NpgsqlCommand command = new NpgsqlCommand("select * from tablea where field_text = :value;", _conn);
 237
 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;
 244                         param.Value = test;
 245                         command.Parameters.Add(param);
 246
 247                         NpgsqlDataReader dr = command.ExecuteReader();
 248
 249                         dr.Read();
 250
 251                         String result = dr.GetString(1);
 252
 253                         Assert.AreEqual(test, result);
 254
 255                 }
 256
 257
 258                 [Test]
 259                 public void GetValueByName()
 260                 {
 261                         _conn.Open();
 262                         NpgsqlCommand command = new NpgsqlCommand("select * from tablea where field_serial = 1;", _conn);
 263
 264                         NpgsqlDataReader dr = command.ExecuteReader();
 265
 266                         dr.Read();
 267
 268                         String result = (String) dr["field_text"];
 269
 270                         Assert.AreEqual("Random text", result);
 271
 272                 }
 273
 274                 [Test]
 275                 [ExpectedException(typeof(InvalidOperationException))]
 276                 public void GetValueFromEmptyResultset()
 277                 {
 278                   _conn.Open();
 279                         NpgsqlCommand command = new NpgsqlCommand("select * from tablea where field_text = :value;", _conn);
 280
 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;
 287                         param.Value = test;
 288                         command.Parameters.Add(param);
 289
 290                         NpgsqlDataReader dr = command.ExecuteReader();
 291
 292                         dr.Read();
 293
 294
 295                         // This line should throw the invalid operation exception as the datareader will
 296                         // have an empty resultset.
 297                         Console.WriteLine(dr.IsDBNull(1));
 298
 299
 300                 }
 301
 302
 303                 [Test]
 304                 public void TestOverlappedParameterNames()
 305                 {
 306                   _conn.Open();
 307
 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"));
 311
 312                   command.Parameters[0].Value = 2;
 313                   command.Parameters[1].Value = 3;
 314
 315                   NpgsqlDataReader dr = command.ExecuteReader();
 316
 317                 }
 318
 319                 [Test]
 320                 [ExpectedException(typeof(IndexOutOfRangeException))]
 321                 public void TestNonExistentParameterName()
 322                 {
 323                   _conn.Open();
 324
 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"));
 328
 329                   command.Parameters[0].Value = 2;
 330                   command.Parameters[1].Value = 3;
 331
 332                   NpgsqlDataReader dr = command.ExecuteReader();
 333
 334
 335                 }
 336
 337
 338
 339
 340                 [Test]
 341                 public void UseDataAdapter()
 342                 {
 343
 344                         _conn.Open();
 345
 346                         NpgsqlCommand command = new NpgsqlCommand("select * from tablea", _conn);
 347
 348                         NpgsqlDataAdapter da = new NpgsqlDataAdapter();
 349
 350                         da.SelectCommand = command;
 351
 352                         DataSet ds = new DataSet();
 353
 354                         da.Fill(ds);
 355
 356                         //ds.WriteXml("TestUseDataAdapter.xml");
 357
 358
 359                 }
 360
 361                 [Test]
 362                 public void UseDataAdapterNpgsqlConnectionConstructor()
 363                 {
 364
 365                         _conn.Open();
 366
 367                         NpgsqlCommand command = new NpgsqlCommand("select * from tablea", _conn);
 368
 369                         command.Connection = _conn;
 370
 371                         NpgsqlDataAdapter da = new NpgsqlDataAdapter(command);
 372
 373                         DataSet ds = new DataSet();
 374
 375                         da.Fill(ds);
 376
 377                         //ds.WriteXml("TestUseDataAdapterNpgsqlConnectionConstructor.xml");
 378
 379
 380                 }
 381
 382                 [Test]
 383                 public void UseDataAdapterStringNpgsqlConnectionConstructor()
 384                 {
 385
 386                         _conn.Open();
 387
 388
 389                         NpgsqlDataAdapter da = new NpgsqlDataAdapter("select * from tablea", _conn);
 390
 391                         DataSet ds = new DataSet();
 392
 393                         da.Fill(ds);
 394
 395                         //ds.WriteXml("TestUseDataAdapterStringNpgsqlConnectionConstructor.xml");
 396
 397
 398                 }
 399
 400
 401                 [Test]
 402                 public void UseDataAdapterStringStringConstructor()
 403                 {
 404
 405                         _conn.Open();
 406
 407
 408                         NpgsqlDataAdapter da = new NpgsqlDataAdapter("select * from tablea", _connString);
 409
 410                         DataSet ds = new DataSet();
 411
 412                         da.Fill(ds);
 413
 414                         ds.WriteXml("TestUseDataAdapterStringStringConstructor.xml");
 415
 416
 417                 }
 418
 419                 [Test]
 420                 public void UseDataAdapterStringStringConstructor2()
 421                 {
 422
 423                         _conn.Open();
 424
 425
 426                         NpgsqlDataAdapter da = new NpgsqlDataAdapter("select * from tableb", _connString);
 427
 428                         DataSet ds = new DataSet();
 429
 430                         da.Fill(ds);
 431
 432                         ds.WriteXml("TestUseDataAdapterStringStringConstructor2.xml");
 433
 434
 435                 }
 436
 437                 [Test]
 438                 public void DataGridWebControlSupport()
 439                 {
 440
 441                         _conn.Open();
 442
 443                         NpgsqlCommand command = new NpgsqlCommand("select * from tablea;", _conn);
 444
 445                         NpgsqlDataReader dr = command.ExecuteReader();
 446
 447                         DataGrid dg = new DataGrid();
 448
 449                         dg.DataSource = dr;
 450                         dg.DataBind();
 451
 452
 453                 }
 454
 455
 456         [Test]
 457         [ExpectedException(typeof(InvalidOperationException))]
 458                 public void ReadPastDataReaderEnd()
 459                 {
 460             _conn.Open();
 461             NpgsqlCommand command = new NpgsqlCommand("select * from tablea;", _conn);
 462
 463                         NpgsqlDataReader dr = command.ExecuteReader();
 464
 465             while (dr.Read());
 466
 467             Object o = dr[0];
 468
 469         }
 470
 471         [Test]
 472         public void IsDBNull()
 473         {
 474             _conn.Open();
 475             NpgsqlCommand command = new NpgsqlCommand("select field_text from tablea;", _conn);
 476
 477                         NpgsqlDataReader dr = command.ExecuteReader();
 478
 479             dr.Read();
 480             Assert.AreEqual(false, dr.IsDBNull(0));
 481             dr.Read();
 482             Assert.AreEqual(true, dr.IsDBNull(0));
 483
 484
 485         }
 486
 487         [Test]
 488         public void IsDBNullFromScalar()
 489         {
 490             _conn.Open();
 491             NpgsqlCommand command = new NpgsqlCommand("select max(field_serial) from tablea;", _conn);
 492
 493                         NpgsqlDataReader dr = command.ExecuteReader();
 494
 495             dr.Read();
 496             Assert.AreEqual(false, dr.IsDBNull(0));
 497
 498         }
 499
 500
 501
 502         [Test]
 503         public void TypesNames()
 504         {
 505                 _conn.Open();
 506             NpgsqlCommand command = new NpgsqlCommand("select * from tablea where 1 = 2;", _conn);
 507
 508                         NpgsqlDataReader dr = command.ExecuteReader();
 509
 510             dr.Read();
 511
 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));
 517
 518             dr.Close();
 519
 520             command.CommandText = "select * from tableb where 1 = 2";
 521
 522             dr = command.ExecuteReader();
 523
 524             dr.Read();
 525
 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));
 530
 531
 532
 533         }
 534
 535
 536
 537         }
 538 }