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