1 // created on 30/11/2002 at 22:35
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 NUnit
.Framework
;
29 using System
.Globalization
;
37 public class CommandTests
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
)
60 public void ParametersGetName()
62 NpgsqlCommand command
= new NpgsqlCommand();
65 command
.Parameters
.Add(new NpgsqlParameter(":Parameter1", DbType
.Boolean
));
66 command
.Parameters
.Add(new NpgsqlParameter(":Parameter2", DbType
.Int32
));
67 command
.Parameters
.Add(new NpgsqlParameter(":Parameter3", DbType
.DateTime
));
72 Assert
.AreEqual(":Parameter1", command
.Parameters
[":Parameter1"].ParameterName
);
73 Assert
.AreEqual(":Parameter2", command
.Parameters
[":Parameter2"].ParameterName
);
74 Assert
.AreEqual(":Parameter3", command
.Parameters
[":Parameter3"].ParameterName
);
77 Assert
.AreEqual(":Parameter1", command
.Parameters
[0].ParameterName
);
78 Assert
.AreEqual(":Parameter2", command
.Parameters
[1].ParameterName
);
79 Assert
.AreEqual(":Parameter3", command
.Parameters
[2].ParameterName
);
86 public void EmptyQuery()
90 NpgsqlCommand command
= new NpgsqlCommand(";", _conn
);
91 command
.ExecuteNonQuery();
95 [ExpectedException(typeof(ArgumentNullException
))]
96 public void NoNameParameterAdd()
98 NpgsqlCommand command
= new NpgsqlCommand();
100 command
.Parameters
.Add(new NpgsqlParameter());
105 public void FunctionCallFromSelect()
109 NpgsqlCommand command
= new NpgsqlCommand("select * from funcB()", _conn
);
111 NpgsqlDataReader reader
= command
.ExecuteReader();
113 Assertion
.AssertNotNull(reader
);
119 public void ExecuteScalar()
123 NpgsqlCommand command
= new NpgsqlCommand("select count(*) from tablea", _conn
);
125 Object result
= command
.ExecuteScalar();
127 Assert
.AreEqual(5, result
);
133 public void FunctionCallReturnSingleValue()
137 NpgsqlCommand command
= new NpgsqlCommand("funcC()", _conn
);
138 command
.CommandType
= CommandType
.StoredProcedure
;
140 Object result
= command
.ExecuteScalar();
142 Assert
.AreEqual(5, result
);
149 public void FunctionCallReturnSingleValueWithPrepare()
153 NpgsqlCommand command
= new NpgsqlCommand("funcC()", _conn
);
154 command
.CommandType
= CommandType
.StoredProcedure
;
157 Object result
= command
.ExecuteScalar();
159 Assert
.AreEqual(5, result
);
165 public void FunctionCallWithParametersReturnSingleValue()
169 NpgsqlCommand command
= new NpgsqlCommand("funcC(:a)", _conn
);
170 command
.CommandType
= CommandType
.StoredProcedure
;
172 command
.Parameters
.Add(new NpgsqlParameter("a", DbType
.Int32
));
174 command
.Parameters
[0].Value
= 4;
176 Int64 result
= (Int64
) command
.ExecuteScalar();
178 Assert
.AreEqual(1, result
);
184 public void FunctionCallWithParametersReturnSingleValueNpgsqlDbType()
188 NpgsqlCommand command
= new NpgsqlCommand("funcC(:a)", _conn
);
189 command
.CommandType
= CommandType
.StoredProcedure
;
191 command
.Parameters
.Add(new NpgsqlParameter("a", NpgsqlDbType
.Integer
));
193 command
.Parameters
[0].Value
= 4;
195 Int64 result
= (Int64
) command
.ExecuteScalar();
197 Assert
.AreEqual(1, result
);
205 public void FunctionCallWithParametersPrepareReturnSingleValue()
209 NpgsqlCommand command
= new NpgsqlCommand("funcC(:a)", _conn
);
210 command
.CommandType
= CommandType
.StoredProcedure
;
213 command
.Parameters
.Add(new NpgsqlParameter("a", DbType
.Int32
));
215 Assert
.AreEqual(1, command
.Parameters
.Count
);
219 command
.Parameters
[0].Value
= 4;
221 Int64 result
= (Int64
) command
.ExecuteScalar();
223 Assert
.AreEqual(1, result
);
229 public void FunctionCallWithParametersPrepareReturnSingleValueNpgsqlDbType()
233 NpgsqlCommand command
= new NpgsqlCommand("funcC(:a)", _conn
);
234 command
.CommandType
= CommandType
.StoredProcedure
;
237 command
.Parameters
.Add(new NpgsqlParameter("a", NpgsqlDbType
.Integer
));
239 Assert
.AreEqual(1, command
.Parameters
.Count
);
243 command
.Parameters
[0].Value
= 4;
245 Int64 result
= (Int64
) command
.ExecuteScalar();
247 Assert
.AreEqual(1, result
);
254 public void FunctionCallReturnResultSet()
258 NpgsqlCommand command
= new NpgsqlCommand("funcB()", _conn
);
259 command
.CommandType
= CommandType
.StoredProcedure
;
261 NpgsqlDataReader dr
= command
.ExecuteReader();
270 public void CursorStatement()
277 NpgsqlTransaction t
= _conn
.BeginTransaction();
279 NpgsqlCommand command
= new NpgsqlCommand("declare te cursor for select * from tablea;", _conn
);
281 command
.ExecuteNonQuery();
283 command
.CommandText
= "fetch forward 3 in te;";
285 NpgsqlDataReader dr
= command
.ExecuteReader();
293 Assert
.AreEqual(3, i
);
298 command
.CommandText
= "fetch backward 1 in te;";
300 NpgsqlDataReader dr2
= command
.ExecuteReader();
307 Assert
.AreEqual(1, i
);
309 command
.CommandText
= "close te;";
311 command
.ExecuteNonQuery();
320 public void PreparedStatementNoParameters()
324 NpgsqlCommand command
= new NpgsqlCommand("select * from tablea;", _conn
);
330 NpgsqlDataReader dr
= command
.ExecuteReader();
336 public void PreparedStatementWithParameters()
340 NpgsqlCommand command
= new NpgsqlCommand("select * from tablea where field_int4 = :a and field_int8 = :b;", _conn
);
342 command
.Parameters
.Add(new NpgsqlParameter("a", DbType
.Int32
));
343 command
.Parameters
.Add(new NpgsqlParameter("b", DbType
.Int64
));
345 Assert
.AreEqual(2, command
.Parameters
.Count
);
347 Assert
.AreEqual(DbType
.Int32
, command
.Parameters
[0].DbType
);
351 command
.Parameters
[0].Value
= 3;
352 command
.Parameters
[1].Value
= 5;
354 NpgsqlDataReader dr
= command
.ExecuteReader();
362 public void PreparedStatementWithParametersNpgsqlDbType()
366 NpgsqlCommand command
= new NpgsqlCommand("select * from tablea where field_int4 = :a and field_int8 = :b;", _conn
);
368 command
.Parameters
.Add(new NpgsqlParameter("a", NpgsqlDbType
.Integer
));
369 command
.Parameters
.Add(new NpgsqlParameter("b", NpgsqlDbType
.Bigint
));
371 Assert
.AreEqual(2, command
.Parameters
.Count
);
373 Assert
.AreEqual(DbType
.Int32
, command
.Parameters
[0].DbType
);
377 command
.Parameters
[0].Value
= 3;
378 command
.Parameters
[1].Value
= 5;
380 NpgsqlDataReader dr
= command
.ExecuteReader();
389 [ExpectedException(typeof(InvalidOperationException
))]
390 public void ListenNotifySupport()
395 NpgsqlCommand command
= new NpgsqlCommand("listen notifytest;", _conn
);
396 command
.ExecuteNonQuery();
398 _conn
.Notification
+= new NotificationEventHandler(NotificationSupportHelper
);
401 command
= new NpgsqlCommand("notify notifytest;", _conn
);
402 command
.ExecuteNonQuery();
408 private void NotificationSupportHelper(Object sender
, NpgsqlNotificationEventArgs args
)
410 throw new InvalidOperationException();
414 public void DateTimeSupport()
419 NpgsqlCommand command
= new NpgsqlCommand("select field_timestamp from tableb where field_serial = 2;", _conn
);
421 DateTime d
= (DateTime
)command
.ExecuteScalar();
424 Assert
.AreEqual("2002-02-02 09:00:23Z", d
.ToString("u"));
426 DateTimeFormatInfo culture
= new DateTimeFormatInfo();
427 culture
.TimeSeparator
= ":";
428 DateTime dt
= System
.DateTime
.Parse("2004-06-04 09:48:00", culture
);
430 command
.CommandText
= "insert into tableb(field_timestamp) values (:a);delete from tableb where field_serial > 4;";
431 command
.Parameters
.Add(new NpgsqlParameter("a", DbType
.DateTime
));
432 command
.Parameters
[0].Value
= dt
;
434 command
.ExecuteScalar();
440 public void DateTimeSupportNpgsqlDbType()
445 NpgsqlCommand command
= new NpgsqlCommand("select field_timestamp from tableb where field_serial = 2;", _conn
);
447 DateTime d
= (DateTime
)command
.ExecuteScalar();
450 Assert
.AreEqual("2002-02-02 09:00:23Z", d
.ToString("u"));
452 DateTimeFormatInfo culture
= new DateTimeFormatInfo();
453 culture
.TimeSeparator
= ":";
454 DateTime dt
= System
.DateTime
.Parse("2004-06-04 09:48:00", culture
);
456 command
.CommandText
= "insert into tableb(field_timestamp) values (:a);delete from tableb where field_serial > 4;";
457 command
.Parameters
.Add(new NpgsqlParameter("a", NpgsqlDbType
.Timestamp
));
458 command
.Parameters
[0].Value
= dt
;
460 command
.ExecuteScalar();
465 public void DateSupport()
469 NpgsqlCommand command
= new NpgsqlCommand("select field_date from tablec where field_serial = 1;", _conn
);
471 DateTime d
= (DateTime
)command
.ExecuteScalar();
474 Assert
.AreEqual("2002-03-04", d
.ToString("yyyy-MM-dd"));
479 public void TimeSupport()
483 NpgsqlCommand command
= new NpgsqlCommand("select field_time from tablec where field_serial = 2;", _conn
);
485 DateTime d
= (DateTime
)command
.ExecuteScalar();
488 Assert
.AreEqual("10:03:45.345", d
.ToString("HH:mm:ss.fff"));
493 public void NumericSupport()
498 NpgsqlCommand command
= new NpgsqlCommand("insert into tableb(field_numeric) values (:a)", _conn
);
499 command
.Parameters
.Add(new NpgsqlParameter("a", DbType
.Decimal
));
501 command
.Parameters
[0].Value
= 7.4M
;
503 Int32 rowsAdded
= command
.ExecuteNonQuery();
505 Assert
.AreEqual(1, rowsAdded
);
507 command
.CommandText
= "select * from tableb where field_numeric = :a";
510 NpgsqlDataReader dr
= command
.ExecuteReader();
513 Decimal result
= dr
.GetDecimal(3);
516 command
.CommandText
= "delete from tableb where field_serial = (select max(field_serial) from tableb) and field_serial != 3;";
517 command
.Parameters
.Clear();
518 command
.ExecuteNonQuery();
521 Assert
.AreEqual(7.4000000M
, result
);
529 public void NumericSupportNpgsqlDbType()
534 NpgsqlCommand command
= new NpgsqlCommand("insert into tableb(field_numeric) values (:a)", _conn
);
535 command
.Parameters
.Add(new NpgsqlParameter("a", NpgsqlDbType
.Numeric
));
537 command
.Parameters
[0].Value
= 7.4M
;
539 Int32 rowsAdded
= command
.ExecuteNonQuery();
541 Assert
.AreEqual(1, rowsAdded
);
543 command
.CommandText
= "select * from tableb where field_numeric = :a";
546 NpgsqlDataReader dr
= command
.ExecuteReader();
549 Decimal result
= dr
.GetDecimal(3);
552 command
.CommandText
= "delete from tableb where field_serial = (select max(field_serial) from tableb) and field_serial != 3;";
553 command
.Parameters
.Clear();
554 command
.ExecuteNonQuery();
557 Assert
.AreEqual(7.4000000M
, result
);
566 public void InsertSingleValue()
571 NpgsqlCommand command
= new NpgsqlCommand("insert into tabled(field_float4) values (:a)", _conn
);
572 command
.Parameters
.Add(new NpgsqlParameter(":a", DbType
.Single
));
574 command
.Parameters
[0].Value
= 7.4F
;
576 Int32 rowsAdded
= command
.ExecuteNonQuery();
578 Assert
.AreEqual(1, rowsAdded
);
580 command
.CommandText
= "select * from tabled where field_float4 = :a";
583 NpgsqlDataReader dr
= command
.ExecuteReader();
586 Single result
= dr
.GetFloat(1);
589 command
.CommandText
= "delete from tabled where field_serial > 2;";
590 command
.Parameters
.Clear();
591 command
.ExecuteNonQuery();
594 Assert
.AreEqual(7.4F
, result
);
600 public void InsertSingleValueNpgsqlDbType()
605 NpgsqlCommand command
= new NpgsqlCommand("insert into tabled(field_float4) values (:a)", _conn
);
606 command
.Parameters
.Add(new NpgsqlParameter(":a", NpgsqlDbType
.Real
));
608 command
.Parameters
[0].Value
= 7.4F
;
610 Int32 rowsAdded
= command
.ExecuteNonQuery();
612 Assert
.AreEqual(1, rowsAdded
);
614 command
.CommandText
= "select * from tabled where field_float4 = :a";
617 NpgsqlDataReader dr
= command
.ExecuteReader();
620 Single result
= dr
.GetFloat(1);
623 command
.CommandText
= "delete from tabled where field_serial > 2;";
624 command
.Parameters
.Clear();
625 command
.ExecuteNonQuery();
628 Assert
.AreEqual(7.4F
, result
);
633 public void InsertDoubleValue()
638 NpgsqlCommand command
= new NpgsqlCommand("insert into tabled(field_float8) values (:a)", _conn
);
639 command
.Parameters
.Add(new NpgsqlParameter(":a", DbType
.Double
));
641 command
.Parameters
[0].Value
= 7.4D
;
643 Int32 rowsAdded
= command
.ExecuteNonQuery();
645 Assert
.AreEqual(1, rowsAdded
);
647 command
.CommandText
= "select * from tabled where field_float8 = :a";
650 NpgsqlDataReader dr
= command
.ExecuteReader();
653 Double result
= dr
.GetDouble(2);
656 command
.CommandText
= "delete from tabled where field_serial > 2;";
657 command
.Parameters
.Clear();
658 //command.ExecuteNonQuery();
661 Assert
.AreEqual(7.4D
, result
);
667 public void InsertDoubleValueNpgsqlDbType()
672 NpgsqlCommand command
= new NpgsqlCommand("insert into tabled(field_float8) values (:a)", _conn
);
673 command
.Parameters
.Add(new NpgsqlParameter(":a", NpgsqlDbType
.Double
));
675 command
.Parameters
[0].Value
= 7.4D
;
677 Int32 rowsAdded
= command
.ExecuteNonQuery();
679 Assert
.AreEqual(1, rowsAdded
);
681 command
.CommandText
= "select * from tabled where field_float8 = :a";
684 NpgsqlDataReader dr
= command
.ExecuteReader();
687 Double result
= dr
.GetDouble(2);
690 command
.CommandText
= "delete from tabled where field_serial > 2;";
691 command
.Parameters
.Clear();
692 //command.ExecuteNonQuery();
695 Assert
.AreEqual(7.4D
, result
);
701 public void NegativeNumericSupport()
706 NpgsqlCommand command
= new NpgsqlCommand("select * from tableb where field_serial = 4", _conn
);
709 NpgsqlDataReader dr
= command
.ExecuteReader();
712 Decimal result
= dr
.GetDecimal(3);
714 Assert
.AreEqual(-4.3000000M
, result
);
720 public void PrecisionScaleNumericSupport()
725 NpgsqlCommand command
= new NpgsqlCommand("select * from tableb where field_serial = 4", _conn
);
728 NpgsqlDataReader dr
= command
.ExecuteReader();
731 Decimal result
= dr
.GetDecimal(3);
733 Assert
.AreEqual(-4.3000000M
, (Decimal
)result
);
734 //Assert.AreEqual(11, result.Precision);
735 //Assert.AreEqual(7, result.Scale);
740 public void InsertNullString()
744 NpgsqlCommand command
= new NpgsqlCommand("insert into tablea(field_text) values (:a)", _conn
);
746 command
.Parameters
.Add(new NpgsqlParameter("a", DbType
.String
));
748 command
.Parameters
[0].Value
= DBNull
.Value
;
750 Int32 rowsAdded
= command
.ExecuteNonQuery();
752 Assert
.AreEqual(1, rowsAdded
);
754 command
.CommandText
= "select count(*) from tablea where field_text is null";
755 command
.Parameters
.Clear();
757 Int64 result
= (Int64
)command
.ExecuteScalar();
759 command
.CommandText
= "delete from tablea where field_serial = (select max(field_serial) from tablea) and field_serial != 4;";
760 command
.ExecuteNonQuery();
762 Assert
.AreEqual(4, result
);
769 public void InsertNullStringNpgsqlDbType()
773 NpgsqlCommand command
= new NpgsqlCommand("insert into tablea(field_text) values (:a)", _conn
);
775 command
.Parameters
.Add(new NpgsqlParameter("a", NpgsqlDbType
.Text
));
777 command
.Parameters
[0].Value
= DBNull
.Value
;
779 Int32 rowsAdded
= command
.ExecuteNonQuery();
781 Assert
.AreEqual(1, rowsAdded
);
783 command
.CommandText
= "select count(*) from tablea where field_text is null";
784 command
.Parameters
.Clear();
786 Int64 result
= (Int64
)command
.ExecuteScalar();
788 command
.CommandText
= "delete from tablea where field_serial = (select max(field_serial) from tablea) and field_serial != 4;";
789 command
.ExecuteNonQuery();
791 Assert
.AreEqual(4, result
);
800 public void InsertNullDateTime()
804 NpgsqlCommand command
= new NpgsqlCommand("insert into tableb(field_timestamp) values (:a)", _conn
);
806 command
.Parameters
.Add(new NpgsqlParameter("a", DbType
.DateTime
));
808 command
.Parameters
[0].Value
= DBNull
.Value
;
810 Int32 rowsAdded
= command
.ExecuteNonQuery();
812 Assert
.AreEqual(1, rowsAdded
);
814 command
.CommandText
= "select count(*) from tableb where field_timestamp is null";
815 command
.Parameters
.Clear();
817 Object result
= command
.ExecuteScalar();
819 command
.CommandText
= "delete from tableb where field_serial = (select max(field_serial) from tableb) and field_serial != 3;";
820 command
.ExecuteNonQuery();
822 Assert
.AreEqual(4, result
);
830 public void InsertNullDateTimeNpgsqlDbType()
834 NpgsqlCommand command
= new NpgsqlCommand("insert into tableb(field_timestamp) values (:a)", _conn
);
836 command
.Parameters
.Add(new NpgsqlParameter("a", NpgsqlDbType
.Timestamp
));
838 command
.Parameters
[0].Value
= DBNull
.Value
;
840 Int32 rowsAdded
= command
.ExecuteNonQuery();
842 Assert
.AreEqual(1, rowsAdded
);
844 command
.CommandText
= "select count(*) from tableb where field_timestamp is null";
845 command
.Parameters
.Clear();
847 Object result
= command
.ExecuteScalar();
849 command
.CommandText
= "delete from tableb where field_serial = (select max(field_serial) from tableb) and field_serial != 3;";
850 command
.ExecuteNonQuery();
852 Assert
.AreEqual(4, result
);
861 public void InsertNullInt16()
866 NpgsqlCommand command
= new NpgsqlCommand("insert into tableb(field_int2) values (:a)", _conn
);
868 command
.Parameters
.Add(new NpgsqlParameter("a", DbType
.Int16
));
870 command
.Parameters
[0].Value
= DBNull
.Value
;
872 Int32 rowsAdded
= command
.ExecuteNonQuery();
874 Assert
.AreEqual(1, rowsAdded
);
876 command
.CommandText
= "select count(*) from tableb where field_int2 is null";
877 command
.Parameters
.Clear();
879 Object result
= command
.ExecuteScalar(); // The missed cast is needed as Server7.2 returns Int32 and Server7.3+ returns Int64
881 command
.CommandText
= "delete from tableb where field_serial = (select max(field_serial) from tableb);";
882 command
.ExecuteNonQuery();
884 Assert
.AreEqual(4, result
);
891 public void InsertNullInt16NpgsqlDbType()
896 NpgsqlCommand command
= new NpgsqlCommand("insert into tableb(field_int2) values (:a)", _conn
);
898 command
.Parameters
.Add(new NpgsqlParameter("a", NpgsqlDbType
.Smallint
));
900 command
.Parameters
[0].Value
= DBNull
.Value
;
902 Int32 rowsAdded
= command
.ExecuteNonQuery();
904 Assert
.AreEqual(1, rowsAdded
);
906 command
.CommandText
= "select count(*) from tableb where field_int2 is null";
907 command
.Parameters
.Clear();
909 Object result
= command
.ExecuteScalar(); // The missed cast is needed as Server7.2 returns Int32 and Server7.3+ returns Int64
911 command
.CommandText
= "delete from tableb where field_serial = (select max(field_serial) from tableb);";
912 command
.ExecuteNonQuery();
914 Assert
.AreEqual(4, result
);
921 public void InsertNullInt32()
926 NpgsqlCommand command
= new NpgsqlCommand("insert into tablea(field_int4) values (:a)", _conn
);
928 command
.Parameters
.Add(new NpgsqlParameter("a", DbType
.Int32
));
930 command
.Parameters
[0].Value
= DBNull
.Value
;
932 Int32 rowsAdded
= command
.ExecuteNonQuery();
934 Assert
.AreEqual(1, rowsAdded
);
936 command
.CommandText
= "select count(*) from tablea where field_int4 is null";
937 command
.Parameters
.Clear();
939 Object result
= command
.ExecuteScalar(); // The missed cast is needed as Server7.2 returns Int32 and Server7.3+ returns Int64
941 command
.CommandText
= "delete from tablea where field_serial = (select max(field_serial) from tablea);";
942 command
.ExecuteNonQuery();
944 Assert
.AreEqual(5, result
);
950 public void InsertNullNumeric()
955 NpgsqlCommand command
= new NpgsqlCommand("insert into tableb(field_numeric) values (:a)", _conn
);
957 command
.Parameters
.Add(new NpgsqlParameter("a", DbType
.Decimal
));
959 command
.Parameters
[0].Value
= DBNull
.Value
;
961 Int32 rowsAdded
= command
.ExecuteNonQuery();
963 Assert
.AreEqual(1, rowsAdded
);
965 command
.CommandText
= "select count(*) from tableb where field_numeric is null";
966 command
.Parameters
.Clear();
968 Object result
= command
.ExecuteScalar(); // The missed cast is needed as Server7.2 returns Int32 and Server7.3+ returns Int64
970 command
.CommandText
= "delete from tableb where field_serial = (select max(field_serial) from tableb);";
971 command
.ExecuteNonQuery();
973 Assert
.AreEqual(3, result
);
978 public void InsertNullBoolean()
983 NpgsqlCommand command
= new NpgsqlCommand("insert into tablea(field_bool) values (:a)", _conn
);
985 command
.Parameters
.Add(new NpgsqlParameter("a", DbType
.Boolean
));
987 command
.Parameters
[0].Value
= DBNull
.Value
;
989 Int32 rowsAdded
= command
.ExecuteNonQuery();
991 Assert
.AreEqual(1, rowsAdded
);
993 command
.CommandText
= "select count(*) from tablea where field_bool is null";
994 command
.Parameters
.Clear();
996 Object result
= command
.ExecuteScalar(); // The missed cast is needed as Server7.2 returns Int32 and Server7.3+ returns Int64
998 command
.CommandText
= "delete from tablea where field_serial = (select max(field_serial) from tablea);";
999 command
.ExecuteNonQuery();
1001 Assert
.AreEqual(5, result
);
1006 public void AnsiStringSupport()
1010 NpgsqlCommand command
= new NpgsqlCommand("insert into tablea(field_text) values (:a)", _conn
);
1012 command
.Parameters
.Add(new NpgsqlParameter("a", DbType
.AnsiString
));
1014 command
.Parameters
[0].Value
= "TesteAnsiString";
1016 Int32 rowsAdded
= command
.ExecuteNonQuery();
1018 Assert
.AreEqual(1, rowsAdded
);
1020 command
.CommandText
= String
.Format("select count(*) from tablea where field_text = '{0}'", command
.Parameters
[0].Value
);
1021 command
.Parameters
.Clear();
1023 Object result
= command
.ExecuteScalar(); // The missed cast is needed as Server7.2 returns Int32 and Server7.3+ returns Int64
1025 command
.CommandText
= "delete from tablea where field_serial = (select max(field_serial) from tablea);";
1026 command
.ExecuteNonQuery();
1028 Assert
.AreEqual(1, result
);
1034 public void MultipleQueriesFirstResultsetEmpty()
1038 NpgsqlCommand command
= new NpgsqlCommand("insert into tablea(field_text) values ('a'); select count(*) from tablea;", _conn
);
1040 Object result
= command
.ExecuteScalar();
1043 command
.CommandText
= "delete from tablea where field_serial > 5";
1044 command
.ExecuteNonQuery();
1046 command
.CommandText
= "select * from tablea where field_serial = 0";
1047 command
.ExecuteScalar();
1050 Assert
.AreEqual(6, result
);
1056 [ExpectedException(typeof(NpgsqlException
))]
1057 public void ConnectionStringWithInvalidParameters()
1059 NpgsqlConnection conn
= new NpgsqlConnection("Server=127.0.0.1;User Id=npgsql_tests;Password=j");
1061 NpgsqlCommand command
= new NpgsqlCommand("select * from tablea", conn
);
1063 command
.Connection
.Open();
1064 command
.ExecuteReader();
1065 command
.Connection
.Close();
1071 [ExpectedException(typeof(NpgsqlException
))]
1072 public void InvalidConnectionString()
1074 NpgsqlConnection conn
= new NpgsqlConnection("Server=127.0.0.1;User Id=npgsql_tests");
1076 NpgsqlCommand command
= new NpgsqlCommand("select * from tablea", conn
);
1078 command
.Connection
.Open();
1079 command
.ExecuteReader();
1080 command
.Connection
.Close();
1087 public void AmbiguousFunctionParameterType()
1089 NpgsqlConnection conn
= new NpgsqlConnection("Server=127.0.0.1;User Id=npgsql_tests;Password=npgsql_tests");
1092 NpgsqlCommand command
= new NpgsqlCommand("ambiguousParameterType(:a, :b, :c, :d, :e, :f)", conn
);
1093 command
.CommandType
= CommandType
.StoredProcedure
;
1094 NpgsqlParameter p
= new NpgsqlParameter("a", DbType
.Int16
);
1096 command
.Parameters
.Add(p
);
1097 p
= new NpgsqlParameter("b", DbType
.Int32
);
1099 command
.Parameters
.Add(p
);
1100 p
= new NpgsqlParameter("c", DbType
.Int64
);
1102 command
.Parameters
.Add(p
);
1103 p
= new NpgsqlParameter("d", DbType
.String
);
1105 command
.Parameters
.Add(p
);
1106 p
= new NpgsqlParameter("e", DbType
.String
);
1108 command
.Parameters
.Add(p
);
1109 p
= new NpgsqlParameter("f", DbType
.String
);
1111 command
.Parameters
.Add(p
);
1114 command
.Connection
.Open();
1116 command
.ExecuteScalar();
1117 command
.Connection
.Close();
1124 public void TestParameterReplace()
1128 String sql
= @"select * from tablea where
1133 NpgsqlCommand command
= new NpgsqlCommand(sql
, _conn
);
1135 command
.Parameters
.Add(new NpgsqlParameter("a", DbType
.Int32
));
1137 command
.Parameters
[0].Value
= 2;
1139 Int32 rowsAdded
= command
.ExecuteNonQuery();
1144 public void TestPointSupport()
1149 NpgsqlCommand command
= new NpgsqlCommand("select field_point from tablee where field_serial = 1", _conn
);
1151 NpgsqlPoint p
= (NpgsqlPoint
) command
.ExecuteScalar();
1153 Assert
.AreEqual(4, p
.X
);
1154 Assert
.AreEqual(3, p
.Y
);
1159 public void TestBoxSupport()
1164 NpgsqlCommand command
= new NpgsqlCommand("select field_box from tablee where field_serial = 2", _conn
);
1166 NpgsqlBox box
= (NpgsqlBox
) command
.ExecuteScalar();
1168 Assert
.AreEqual(5, box
.UpperRight
.X
);
1169 Assert
.AreEqual(4, box
.UpperRight
.Y
);
1170 Assert
.AreEqual(4, box
.LowerLeft
.X
);
1171 Assert
.AreEqual(3, box
.LowerLeft
.Y
);
1177 public void TestLSegSupport()
1182 NpgsqlCommand command
= new NpgsqlCommand("select field_lseg from tablee where field_serial = 3", _conn
);
1184 NpgsqlLSeg lseg
= (NpgsqlLSeg
) command
.ExecuteScalar();
1186 Assert
.AreEqual(4, lseg
.Start
.X
);
1187 Assert
.AreEqual(3, lseg
.Start
.Y
);
1188 Assert
.AreEqual(5, lseg
.End
.X
);
1189 Assert
.AreEqual(4, lseg
.End
.Y
);
1195 public void TestClosedPathSupport()
1200 NpgsqlCommand command
= new NpgsqlCommand("select field_path from tablee where field_serial = 4", _conn
);
1202 NpgsqlPath path
= (NpgsqlPath
) command
.ExecuteScalar();
1204 Assert
.AreEqual(false, path
.Open
);
1205 Assert
.AreEqual(2, path
.Count
);
1206 Assert
.AreEqual(4, path
[0].X
);
1207 Assert
.AreEqual(3, path
[0].Y
);
1208 Assert
.AreEqual(5, path
[1].X
);
1209 Assert
.AreEqual(4, path
[1].Y
);
1215 public void TestOpenPathSupport()
1220 NpgsqlCommand command
= new NpgsqlCommand("select field_path from tablee where field_serial = 5", _conn
);
1222 NpgsqlPath path
= (NpgsqlPath
) command
.ExecuteScalar();
1224 Assert
.AreEqual(true, path
.Open
);
1225 Assert
.AreEqual(2, path
.Count
);
1226 Assert
.AreEqual(4, path
[0].X
);
1227 Assert
.AreEqual(3, path
[0].Y
);
1228 Assert
.AreEqual(5, path
[1].X
);
1229 Assert
.AreEqual(4, path
[1].Y
);
1237 public void TestPolygonSupport()
1242 NpgsqlCommand command
= new NpgsqlCommand("select field_polygon from tablee where field_serial = 6", _conn
);
1244 NpgsqlPolygon polygon
= (NpgsqlPolygon
) command
.ExecuteScalar();
1246 Assert
.AreEqual(2, polygon
.Count
);
1247 Assert
.AreEqual(4, polygon
[0].X
);
1248 Assert
.AreEqual(3, polygon
[0].Y
);
1249 Assert
.AreEqual(5, polygon
[1].X
);
1250 Assert
.AreEqual(4, polygon
[1].Y
);
1257 public void TestCircleSupport()
1262 NpgsqlCommand command
= new NpgsqlCommand("select field_circle from tablee where field_serial = 7", _conn
);
1264 NpgsqlCircle circle
= (NpgsqlCircle
) command
.ExecuteScalar();
1266 Assert
.AreEqual(4, circle
.Center
.X
);
1267 Assert
.AreEqual(3, circle
.Center
.Y
);
1268 Assert
.AreEqual(5, circle
.Radius
);