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;
36 public class CommandTests
38 NpgsqlConnection _conn = null;
41 protected void SetUp()
43 //NpgsqlEventLog.Level = LogLevel.None;
44 //NpgsqlEventLog.Level = LogLevel.Debug;
45 //NpgsqlEventLog.LogName = "NpgsqlTests.LogFile";
46 _conn = new NpgsqlConnection (TestConfiguration.NpgsqlConnectionString);
50 protected void TearDown()
52 if (_conn != null && _conn.State != ConnectionState.Closed)
58 public void ParametersGetName()
60 NpgsqlCommand command = new NpgsqlCommand();
63 command.Parameters.Add(new NpgsqlParameter(":Parameter1", DbType.Boolean));
64 command.Parameters.Add(new NpgsqlParameter(":Parameter2", DbType.Int32));
65 command.Parameters.Add(new NpgsqlParameter(":Parameter3", DbType.DateTime));
70 Assert.AreEqual(":Parameter1", command.Parameters[":Parameter1"].ParameterName);
71 Assert.AreEqual(":Parameter2", command.Parameters[":Parameter2"].ParameterName);
72 Assert.AreEqual(":Parameter3", command.Parameters[":Parameter3"].ParameterName);
75 Assert.AreEqual(":Parameter1", command.Parameters[0].ParameterName);
76 Assert.AreEqual(":Parameter2", command.Parameters[1].ParameterName);
77 Assert.AreEqual(":Parameter3", command.Parameters[2].ParameterName);
84 public void EmptyQuery()
88 NpgsqlCommand command = new NpgsqlCommand(";", _conn);
89 command.ExecuteNonQuery();
93 [ExpectedException(typeof(ArgumentNullException))]
94 public void NoNameParameterAdd()
96 NpgsqlCommand command = new NpgsqlCommand();
98 command.Parameters.Add(new NpgsqlParameter());
103 public void FunctionCallFromSelect()
107 NpgsqlCommand command = new NpgsqlCommand("select * from funcB()", _conn);
109 NpgsqlDataReader reader = command.ExecuteReader();
111 Assertion.AssertNotNull(reader);
117 public void ExecuteScalar()
121 NpgsqlCommand command = new NpgsqlCommand("select count(*) from tablea", _conn);
123 Object result = command.ExecuteScalar();
125 Assert.AreEqual(5, result);
131 public void FunctionCallReturnSingleValue()
135 NpgsqlCommand command = new NpgsqlCommand("funcC()", _conn);
136 command.CommandType = CommandType.StoredProcedure;
138 Object result = command.ExecuteScalar();
140 Assert.AreEqual(5, result);
147 public void FunctionCallReturnSingleValueWithPrepare()
151 NpgsqlCommand command = new NpgsqlCommand("funcC()", _conn);
152 command.CommandType = CommandType.StoredProcedure;
155 Object result = command.ExecuteScalar();
157 Assert.AreEqual(5, result);
163 public void FunctionCallWithParametersReturnSingleValue()
167 NpgsqlCommand command = new NpgsqlCommand("funcC(:a)", _conn);
168 command.CommandType = CommandType.StoredProcedure;
170 command.Parameters.Add(new NpgsqlParameter("a", DbType.Int32));
172 command.Parameters[0].Value = 4;
174 Int64 result = (Int64) command.ExecuteScalar();
176 Assert.AreEqual(1, result);
182 public void FunctionCallWithParametersReturnSingleValueNpgsqlDbType()
186 NpgsqlCommand command = new NpgsqlCommand("funcC(:a)", _conn);
187 command.CommandType = CommandType.StoredProcedure;
189 command.Parameters.Add(new NpgsqlParameter("a", NpgsqlDbType.Integer));
191 command.Parameters[0].Value = 4;
193 Int64 result = (Int64) command.ExecuteScalar();
195 Assert.AreEqual(1, result);
203 public void FunctionCallWithParametersPrepareReturnSingleValue()
207 NpgsqlCommand command = new NpgsqlCommand("funcC(:a)", _conn);
208 command.CommandType = CommandType.StoredProcedure;
211 command.Parameters.Add(new NpgsqlParameter("a", DbType.Int32));
213 Assert.AreEqual(1, command.Parameters.Count);
217 command.Parameters[0].Value = 4;
219 Int64 result = (Int64) command.ExecuteScalar();
221 Assert.AreEqual(1, result);
227 public void FunctionCallWithParametersPrepareReturnSingleValueNpgsqlDbType()
231 NpgsqlCommand command = new NpgsqlCommand("funcC(:a)", _conn);
232 command.CommandType = CommandType.StoredProcedure;
235 command.Parameters.Add(new NpgsqlParameter("a", NpgsqlDbType.Integer));
237 Assert.AreEqual(1, command.Parameters.Count);
241 command.Parameters[0].Value = 4;
243 Int64 result = (Int64) command.ExecuteScalar();
245 Assert.AreEqual(1, result);
252 public void FunctionCallReturnResultSet()
256 NpgsqlCommand command = new NpgsqlCommand("funcB()", _conn);
257 command.CommandType = CommandType.StoredProcedure;
259 NpgsqlDataReader dr = command.ExecuteReader();
268 public void CursorStatement()
275 NpgsqlTransaction t = _conn.BeginTransaction();
277 NpgsqlCommand command = new NpgsqlCommand("declare te cursor for select * from tablea;", _conn);
279 command.ExecuteNonQuery();
281 command.CommandText = "fetch forward 3 in te;";
283 NpgsqlDataReader dr = command.ExecuteReader();
291 Assert.AreEqual(3, i);
296 command.CommandText = "fetch backward 1 in te;";
298 NpgsqlDataReader dr2 = command.ExecuteReader();
305 Assert.AreEqual(1, i);
307 command.CommandText = "close te;";
309 command.ExecuteNonQuery();
318 public void PreparedStatementNoParameters()
322 NpgsqlCommand command = new NpgsqlCommand("select * from tablea;", _conn);
328 NpgsqlDataReader dr = command.ExecuteReader();
334 public void PreparedStatementWithParameters()
338 NpgsqlCommand command = new NpgsqlCommand("select * from tablea where field_int4 = :a and field_int8 = :b;", _conn);
340 command.Parameters.Add(new NpgsqlParameter("a", DbType.Int32));
341 command.Parameters.Add(new NpgsqlParameter("b", DbType.Int64));
343 Assert.AreEqual(2, command.Parameters.Count);
345 Assert.AreEqual(DbType.Int32, command.Parameters[0].DbType);
349 command.Parameters[0].Value = 3;
350 command.Parameters[1].Value = 5;
352 NpgsqlDataReader dr = command.ExecuteReader();
360 public void PreparedStatementWithParametersNpgsqlDbType()
364 NpgsqlCommand command = new NpgsqlCommand("select * from tablea where field_int4 = :a and field_int8 = :b;", _conn);
366 command.Parameters.Add(new NpgsqlParameter("a", NpgsqlDbType.Integer));
367 command.Parameters.Add(new NpgsqlParameter("b", NpgsqlDbType.Bigint));
369 Assert.AreEqual(2, command.Parameters.Count);
371 Assert.AreEqual(DbType.Int32, command.Parameters[0].DbType);
375 command.Parameters[0].Value = 3;
376 command.Parameters[1].Value = 5;
378 NpgsqlDataReader dr = command.ExecuteReader();
387 [ExpectedException(typeof(InvalidOperationException))]
388 public void ListenNotifySupport()
393 NpgsqlCommand command = new NpgsqlCommand("listen notifytest;", _conn);
394 command.ExecuteNonQuery();
396 _conn.Notification += new NotificationEventHandler(NotificationSupportHelper);
399 command = new NpgsqlCommand("notify notifytest;", _conn);
400 command.ExecuteNonQuery();
406 private void NotificationSupportHelper(Object sender, NpgsqlNotificationEventArgs args)
408 throw new InvalidOperationException();
412 public void DateTimeSupport()
417 NpgsqlCommand command = new NpgsqlCommand("select field_timestamp from tableb where field_serial = 2;", _conn);
419 DateTime d = (DateTime)command.ExecuteScalar();
422 Assert.AreEqual("2002-02-02 09:00:23Z", d.ToString("u"));
424 DateTimeFormatInfo culture = new DateTimeFormatInfo();
425 culture.TimeSeparator = ":";
426 DateTime dt = System.DateTime.Parse("2004-06-04 09:48:00", culture);
428 command.CommandText = "insert into tableb(field_timestamp) values (:a);delete from tableb where field_serial > 4;";
429 command.Parameters.Add(new NpgsqlParameter("a", DbType.DateTime));
430 command.Parameters[0].Value = dt;
432 command.ExecuteScalar();
438 public void DateTimeSupportNpgsqlDbType()
443 NpgsqlCommand command = new NpgsqlCommand("select field_timestamp from tableb where field_serial = 2;", _conn);
445 DateTime d = (DateTime)command.ExecuteScalar();
448 Assert.AreEqual("2002-02-02 09:00:23Z", d.ToString("u"));
450 DateTimeFormatInfo culture = new DateTimeFormatInfo();
451 culture.TimeSeparator = ":";
452 DateTime dt = System.DateTime.Parse("2004-06-04 09:48:00", culture);
454 command.CommandText = "insert into tableb(field_timestamp) values (:a);delete from tableb where field_serial > 4;";
455 command.Parameters.Add(new NpgsqlParameter("a", NpgsqlDbType.Timestamp));
456 command.Parameters[0].Value = dt;
458 command.ExecuteScalar();
463 public void DateSupport()
467 NpgsqlCommand command = new NpgsqlCommand("select field_date from tablec where field_serial = 1;", _conn);
469 DateTime d = (DateTime)command.ExecuteScalar();
472 Assert.AreEqual("2002-03-04", d.ToString("yyyy-MM-dd"));
477 public void TimeSupport()
481 NpgsqlCommand command = new NpgsqlCommand("select field_time from tablec where field_serial = 2;", _conn);
483 DateTime d = (DateTime)command.ExecuteScalar();
486 Assert.AreEqual("10:03:45.345", d.ToString("HH:mm:ss.fff"));
491 public void NumericSupport()
496 NpgsqlCommand command = new NpgsqlCommand("insert into tableb(field_numeric) values (:a)", _conn);
497 command.Parameters.Add(new NpgsqlParameter("a", DbType.Decimal));
499 command.Parameters[0].Value = 7.4M;
501 Int32 rowsAdded = command.ExecuteNonQuery();
503 Assert.AreEqual(1, rowsAdded);
505 command.CommandText = "select * from tableb where field_numeric = :a";
508 NpgsqlDataReader dr = command.ExecuteReader();
511 Decimal result = dr.GetDecimal(3);
514 command.CommandText = "delete from tableb where field_serial = (select max(field_serial) from tableb) and field_serial != 3;";
515 command.Parameters.Clear();
516 command.ExecuteNonQuery();
519 Assert.AreEqual(7.4000000M, result);
527 public void NumericSupportNpgsqlDbType()
532 NpgsqlCommand command = new NpgsqlCommand("insert into tableb(field_numeric) values (:a)", _conn);
533 command.Parameters.Add(new NpgsqlParameter("a", NpgsqlDbType.Numeric));
535 command.Parameters[0].Value = 7.4M;
537 Int32 rowsAdded = command.ExecuteNonQuery();
539 Assert.AreEqual(1, rowsAdded);
541 command.CommandText = "select * from tableb where field_numeric = :a";
544 NpgsqlDataReader dr = command.ExecuteReader();
547 Decimal result = dr.GetDecimal(3);
550 command.CommandText = "delete from tableb where field_serial = (select max(field_serial) from tableb) and field_serial != 3;";
551 command.Parameters.Clear();
552 command.ExecuteNonQuery();
555 Assert.AreEqual(7.4000000M, result);
564 public void InsertSingleValue()
569 NpgsqlCommand command = new NpgsqlCommand("insert into tabled(field_float4) values (:a)", _conn);
570 command.Parameters.Add(new NpgsqlParameter(":a", DbType.Single));
572 command.Parameters[0].Value = 7.4F;
574 Int32 rowsAdded = command.ExecuteNonQuery();
576 Assert.AreEqual(1, rowsAdded);
578 command.CommandText = "select * from tabled where field_float4 = :a";
581 NpgsqlDataReader dr = command.ExecuteReader();
584 Single result = dr.GetFloat(1);
587 command.CommandText = "delete from tabled where field_serial > 2;";
588 command.Parameters.Clear();
589 command.ExecuteNonQuery();
592 Assert.AreEqual(7.4F, result);
598 public void InsertSingleValueNpgsqlDbType()
603 NpgsqlCommand command = new NpgsqlCommand("insert into tabled(field_float4) values (:a)", _conn);
604 command.Parameters.Add(new NpgsqlParameter(":a", NpgsqlDbType.Real));
606 command.Parameters[0].Value = 7.4F;
608 Int32 rowsAdded = command.ExecuteNonQuery();
610 Assert.AreEqual(1, rowsAdded);
612 command.CommandText = "select * from tabled where field_float4 = :a";
615 NpgsqlDataReader dr = command.ExecuteReader();
618 Single result = dr.GetFloat(1);
621 command.CommandText = "delete from tabled where field_serial > 2;";
622 command.Parameters.Clear();
623 command.ExecuteNonQuery();
626 Assert.AreEqual(7.4F, result);
631 public void InsertDoubleValue()
636 NpgsqlCommand command = new NpgsqlCommand("insert into tabled(field_float8) values (:a)", _conn);
637 command.Parameters.Add(new NpgsqlParameter(":a", DbType.Double));
639 command.Parameters[0].Value = 7.4D;
641 Int32 rowsAdded = command.ExecuteNonQuery();
643 Assert.AreEqual(1, rowsAdded);
645 command.CommandText = "select * from tabled where field_float8 = :a";
648 NpgsqlDataReader dr = command.ExecuteReader();
651 Double result = dr.GetDouble(2);
654 command.CommandText = "delete from tabled where field_serial > 2;";
655 command.Parameters.Clear();
656 //command.ExecuteNonQuery();
659 Assert.AreEqual(7.4D, result);
665 public void InsertDoubleValueNpgsqlDbType()
670 NpgsqlCommand command = new NpgsqlCommand("insert into tabled(field_float8) values (:a)", _conn);
671 command.Parameters.Add(new NpgsqlParameter(":a", NpgsqlDbType.Double));
673 command.Parameters[0].Value = 7.4D;
675 Int32 rowsAdded = command.ExecuteNonQuery();
677 Assert.AreEqual(1, rowsAdded);
679 command.CommandText = "select * from tabled where field_float8 = :a";
682 NpgsqlDataReader dr = command.ExecuteReader();
685 Double result = dr.GetDouble(2);
688 command.CommandText = "delete from tabled where field_serial > 2;";
689 command.Parameters.Clear();
690 //command.ExecuteNonQuery();
693 Assert.AreEqual(7.4D, result);
699 public void NegativeNumericSupport()
704 NpgsqlCommand command = new NpgsqlCommand("select * from tableb where field_serial = 4", _conn);
707 NpgsqlDataReader dr = command.ExecuteReader();
710 Decimal result = dr.GetDecimal(3);
712 Assert.AreEqual(-4.3000000M, result);
718 public void PrecisionScaleNumericSupport()
723 NpgsqlCommand command = new NpgsqlCommand("select * from tableb where field_serial = 4", _conn);
726 NpgsqlDataReader dr = command.ExecuteReader();
729 Decimal result = dr.GetDecimal(3);
731 Assert.AreEqual(-4.3000000M, (Decimal)result);
732 //Assert.AreEqual(11, result.Precision);
733 //Assert.AreEqual(7, result.Scale);
738 public void InsertNullString()
742 NpgsqlCommand command = new NpgsqlCommand("insert into tablea(field_text) values (:a)", _conn);
744 command.Parameters.Add(new NpgsqlParameter("a", DbType.String));
746 command.Parameters[0].Value = DBNull.Value;
748 Int32 rowsAdded = command.ExecuteNonQuery();
750 Assert.AreEqual(1, rowsAdded);
752 command.CommandText = "select count(*) from tablea where field_text is null";
753 command.Parameters.Clear();
755 Int64 result = (Int64)command.ExecuteScalar();
757 command.CommandText = "delete from tablea where field_serial = (select max(field_serial) from tablea) and field_serial != 4;";
758 command.ExecuteNonQuery();
760 Assert.AreEqual(4, result);
767 public void InsertNullStringNpgsqlDbType()
771 NpgsqlCommand command = new NpgsqlCommand("insert into tablea(field_text) values (:a)", _conn);
773 command.Parameters.Add(new NpgsqlParameter("a", NpgsqlDbType.Text));
775 command.Parameters[0].Value = DBNull.Value;
777 Int32 rowsAdded = command.ExecuteNonQuery();
779 Assert.AreEqual(1, rowsAdded);
781 command.CommandText = "select count(*) from tablea where field_text is null";
782 command.Parameters.Clear();
784 Int64 result = (Int64)command.ExecuteScalar();
786 command.CommandText = "delete from tablea where field_serial = (select max(field_serial) from tablea) and field_serial != 4;";
787 command.ExecuteNonQuery();
789 Assert.AreEqual(4, result);
798 public void InsertNullDateTime()
802 NpgsqlCommand command = new NpgsqlCommand("insert into tableb(field_timestamp) values (:a)", _conn);
804 command.Parameters.Add(new NpgsqlParameter("a", DbType.DateTime));
806 command.Parameters[0].Value = DBNull.Value;
808 Int32 rowsAdded = command.ExecuteNonQuery();
810 Assert.AreEqual(1, rowsAdded);
812 command.CommandText = "select count(*) from tableb where field_timestamp is null";
813 command.Parameters.Clear();
815 Object result = command.ExecuteScalar();
817 command.CommandText = "delete from tableb where field_serial = (select max(field_serial) from tableb) and field_serial != 3;";
818 command.ExecuteNonQuery();
820 Assert.AreEqual(4, result);
828 public void InsertNullDateTimeNpgsqlDbType()
832 NpgsqlCommand command = new NpgsqlCommand("insert into tableb(field_timestamp) values (:a)", _conn);
834 command.Parameters.Add(new NpgsqlParameter("a", NpgsqlDbType.Timestamp));
836 command.Parameters[0].Value = DBNull.Value;
838 Int32 rowsAdded = command.ExecuteNonQuery();
840 Assert.AreEqual(1, rowsAdded);
842 command.CommandText = "select count(*) from tableb where field_timestamp is null";
843 command.Parameters.Clear();
845 Object result = command.ExecuteScalar();
847 command.CommandText = "delete from tableb where field_serial = (select max(field_serial) from tableb) and field_serial != 3;";
848 command.ExecuteNonQuery();
850 Assert.AreEqual(4, result);
859 public void InsertNullInt16()
864 NpgsqlCommand command = new NpgsqlCommand("insert into tableb(field_int2) values (:a)", _conn);
866 command.Parameters.Add(new NpgsqlParameter("a", DbType.Int16));
868 command.Parameters[0].Value = DBNull.Value;
870 Int32 rowsAdded = command.ExecuteNonQuery();
872 Assert.AreEqual(1, rowsAdded);
874 command.CommandText = "select count(*) from tableb where field_int2 is null";
875 command.Parameters.Clear();
877 Object result = command.ExecuteScalar(); // The missed cast is needed as Server7.2 returns Int32 and Server7.3+ returns Int64
879 command.CommandText = "delete from tableb where field_serial = (select max(field_serial) from tableb);";
880 command.ExecuteNonQuery();
882 Assert.AreEqual(4, result);
889 public void InsertNullInt16NpgsqlDbType()
894 NpgsqlCommand command = new NpgsqlCommand("insert into tableb(field_int2) values (:a)", _conn);
896 command.Parameters.Add(new NpgsqlParameter("a", NpgsqlDbType.Smallint));
898 command.Parameters[0].Value = DBNull.Value;
900 Int32 rowsAdded = command.ExecuteNonQuery();
902 Assert.AreEqual(1, rowsAdded);
904 command.CommandText = "select count(*) from tableb where field_int2 is null";
905 command.Parameters.Clear();
907 Object result = command.ExecuteScalar(); // The missed cast is needed as Server7.2 returns Int32 and Server7.3+ returns Int64
909 command.CommandText = "delete from tableb where field_serial = (select max(field_serial) from tableb);";
910 command.ExecuteNonQuery();
912 Assert.AreEqual(4, result);
919 public void InsertNullInt32()
924 NpgsqlCommand command = new NpgsqlCommand("insert into tablea(field_int4) values (:a)", _conn);
926 command.Parameters.Add(new NpgsqlParameter("a", DbType.Int32));
928 command.Parameters[0].Value = DBNull.Value;
930 Int32 rowsAdded = command.ExecuteNonQuery();
932 Assert.AreEqual(1, rowsAdded);
934 command.CommandText = "select count(*) from tablea where field_int4 is null";
935 command.Parameters.Clear();
937 Object result = command.ExecuteScalar(); // The missed cast is needed as Server7.2 returns Int32 and Server7.3+ returns Int64
939 command.CommandText = "delete from tablea where field_serial = (select max(field_serial) from tablea);";
940 command.ExecuteNonQuery();
942 Assert.AreEqual(5, result);
948 public void InsertNullNumeric()
953 NpgsqlCommand command = new NpgsqlCommand("insert into tableb(field_numeric) values (:a)", _conn);
955 command.Parameters.Add(new NpgsqlParameter("a", DbType.Decimal));
957 command.Parameters[0].Value = DBNull.Value;
959 Int32 rowsAdded = command.ExecuteNonQuery();
961 Assert.AreEqual(1, rowsAdded);
963 command.CommandText = "select count(*) from tableb where field_numeric is null";
964 command.Parameters.Clear();
966 Object result = command.ExecuteScalar(); // The missed cast is needed as Server7.2 returns Int32 and Server7.3+ returns Int64
968 command.CommandText = "delete from tableb where field_serial = (select max(field_serial) from tableb);";
969 command.ExecuteNonQuery();
971 Assert.AreEqual(3, result);
976 public void InsertNullBoolean()
981 NpgsqlCommand command = new NpgsqlCommand("insert into tablea(field_bool) values (:a)", _conn);
983 command.Parameters.Add(new NpgsqlParameter("a", DbType.Boolean));
985 command.Parameters[0].Value = DBNull.Value;
987 Int32 rowsAdded = command.ExecuteNonQuery();
989 Assert.AreEqual(1, rowsAdded);
991 command.CommandText = "select count(*) from tablea where field_bool is null";
992 command.Parameters.Clear();
994 Object result = command.ExecuteScalar(); // The missed cast is needed as Server7.2 returns Int32 and Server7.3+ returns Int64
996 command.CommandText = "delete from tablea where field_serial = (select max(field_serial) from tablea);";
997 command.ExecuteNonQuery();
999 Assert.AreEqual(5, result);
1004 public void AnsiStringSupport()
1008 NpgsqlCommand command = new NpgsqlCommand("insert into tablea(field_text) values (:a)", _conn);
1010 command.Parameters.Add(new NpgsqlParameter("a", DbType.AnsiString));
1012 command.Parameters[0].Value = "TesteAnsiString";
1014 Int32 rowsAdded = command.ExecuteNonQuery();
1016 Assert.AreEqual(1, rowsAdded);
1018 command.CommandText = String.Format("select count(*) from tablea where field_text = '{0}'", command.Parameters[0].Value);
1019 command.Parameters.Clear();
1021 Object result = command.ExecuteScalar(); // The missed cast is needed as Server7.2 returns Int32 and Server7.3+ returns Int64
1023 command.CommandText = "delete from tablea where field_serial = (select max(field_serial) from tablea);";
1024 command.ExecuteNonQuery();
1026 Assert.AreEqual(1, result);
1032 public void MultipleQueriesFirstResultsetEmpty()
1036 NpgsqlCommand command = new NpgsqlCommand("insert into tablea(field_text) values ('a'); select count(*) from tablea;", _conn);
1038 Object result = command.ExecuteScalar();
1041 command.CommandText = "delete from tablea where field_serial > 5";
1042 command.ExecuteNonQuery();
1044 command.CommandText = "select * from tablea where field_serial = 0";
1045 command.ExecuteScalar();
1048 Assert.AreEqual(6, result);
1054 [ExpectedException(typeof(NpgsqlException))]
1055 public void ConnectionStringWithInvalidParameters()
1057 NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;User Id=npgsql_tests;Password=j");
1059 NpgsqlCommand command = new NpgsqlCommand("select * from tablea", conn);
1061 command.Connection.Open();
1062 command.ExecuteReader();
1063 command.Connection.Close();
1069 [ExpectedException(typeof(NpgsqlException))]
1070 public void InvalidConnectionString()
1072 NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;User Id=npgsql_tests");
1074 NpgsqlCommand command = new NpgsqlCommand("select * from tablea", conn);
1076 command.Connection.Open();
1077 command.ExecuteReader();
1078 command.Connection.Close();
1085 public void AmbiguousFunctionParameterType()
1087 NpgsqlConnection conn = new NpgsqlConnection("Server=127.0.0.1;User Id=npgsql_tests;Password=npgsql_tests");
1090 NpgsqlCommand command = new NpgsqlCommand("ambiguousParameterType(:a, :b, :c, :d, :e, :f)", conn);
1091 command.CommandType = CommandType.StoredProcedure;
1092 NpgsqlParameter p = new NpgsqlParameter("a", DbType.Int16);
1094 command.Parameters.Add(p);
1095 p = new NpgsqlParameter("b", DbType.Int32);
1097 command.Parameters.Add(p);
1098 p = new NpgsqlParameter("c", DbType.Int64);
1100 command.Parameters.Add(p);
1101 p = new NpgsqlParameter("d", DbType.String);
1103 command.Parameters.Add(p);
1104 p = new NpgsqlParameter("e", DbType.String);
1106 command.Parameters.Add(p);
1107 p = new NpgsqlParameter("f", DbType.String);
1109 command.Parameters.Add(p);
1112 command.Connection.Open();
1114 command.ExecuteScalar();
1115 command.Connection.Close();
1122 public void TestParameterReplace()
1126 String sql = @"select * from tablea where
1131 NpgsqlCommand command = new NpgsqlCommand(sql, _conn);
1133 command.Parameters.Add(new NpgsqlParameter("a", DbType.Int32));
1135 command.Parameters[0].Value = 2;
1137 Int32 rowsAdded = command.ExecuteNonQuery();
1142 public void TestPointSupport()
1147 NpgsqlCommand command = new NpgsqlCommand("select field_point from tablee where field_serial = 1", _conn);
1149 NpgsqlPoint p = (NpgsqlPoint) command.ExecuteScalar();
1151 Assert.AreEqual(4, p.X);
1152 Assert.AreEqual(3, p.Y);
1157 public void TestBoxSupport()
1162 NpgsqlCommand command = new NpgsqlCommand("select field_box from tablee where field_serial = 2", _conn);
1164 NpgsqlBox box = (NpgsqlBox) command.ExecuteScalar();
1166 Assert.AreEqual(5, box.UpperRight.X);
1167 Assert.AreEqual(4, box.UpperRight.Y);
1168 Assert.AreEqual(4, box.LowerLeft.X);
1169 Assert.AreEqual(3, box.LowerLeft.Y);
1175 public void TestLSegSupport()
1180 NpgsqlCommand command = new NpgsqlCommand("select field_lseg from tablee where field_serial = 3", _conn);
1182 NpgsqlLSeg lseg = (NpgsqlLSeg) command.ExecuteScalar();
1184 Assert.AreEqual(4, lseg.Start.X);
1185 Assert.AreEqual(3, lseg.Start.Y);
1186 Assert.AreEqual(5, lseg.End.X);
1187 Assert.AreEqual(4, lseg.End.Y);
1193 public void TestClosedPathSupport()
1198 NpgsqlCommand command = new NpgsqlCommand("select field_path from tablee where field_serial = 4", _conn);
1200 NpgsqlPath path = (NpgsqlPath) command.ExecuteScalar();
1202 Assert.AreEqual(false, path.Open);
1203 Assert.AreEqual(2, path.Count);
1204 Assert.AreEqual(4, path[0].X);
1205 Assert.AreEqual(3, path[0].Y);
1206 Assert.AreEqual(5, path[1].X);
1207 Assert.AreEqual(4, path[1].Y);
1213 public void TestOpenPathSupport()
1218 NpgsqlCommand command = new NpgsqlCommand("select field_path from tablee where field_serial = 5", _conn);
1220 NpgsqlPath path = (NpgsqlPath) command.ExecuteScalar();
1222 Assert.AreEqual(true, path.Open);
1223 Assert.AreEqual(2, path.Count);
1224 Assert.AreEqual(4, path[0].X);
1225 Assert.AreEqual(3, path[0].Y);
1226 Assert.AreEqual(5, path[1].X);
1227 Assert.AreEqual(4, path[1].Y);
1235 public void TestPolygonSupport()
1240 NpgsqlCommand command = new NpgsqlCommand("select field_polygon from tablee where field_serial = 6", _conn);
1242 NpgsqlPolygon polygon = (NpgsqlPolygon) command.ExecuteScalar();
1244 Assert.AreEqual(2, polygon.Count);
1245 Assert.AreEqual(4, polygon[0].X);
1246 Assert.AreEqual(3, polygon[0].Y);
1247 Assert.AreEqual(5, polygon[1].X);
1248 Assert.AreEqual(4, polygon[1].Y);
1255 public void TestCircleSupport()
1260 NpgsqlCommand command = new NpgsqlCommand("select field_circle from tablee where field_serial = 7", _conn);
1262 NpgsqlCircle circle = (NpgsqlCircle) command.ExecuteScalar();
1264 Assert.AreEqual(4, circle.Center.X);
1265 Assert.AreEqual(3, circle.Center.Y);
1266 Assert.AreEqual(5, circle.Radius);