github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/acceptance/testdata/csharp/Program.cs (about)

     1  using System;
     2  using System.Data;
     3  using System.Linq;
     4  using Npgsql;
     5  using System.Security.Cryptography.X509Certificates;
     6  using System.Security.Cryptography;
     7  
     8  namespace CockroachDrivers
     9  {
    10      class MainClass
    11      {
    12          static void Main(string[] args)
    13          {
    14              var connStringBuilder = new NpgsqlConnectionStringBuilder();
    15              connStringBuilder.Port = int.Parse(Environment.GetEnvironmentVariable("PGPORT"));
    16              connStringBuilder.Host = Environment.GetEnvironmentVariable("PGHOST");
    17              connStringBuilder.Username = Environment.GetEnvironmentVariable("PGUSER");
    18              connStringBuilder.SslMode = SslMode.Require;
    19              connStringBuilder.TrustServerCertificate = true;
    20              // Npgsql needs to connect to a database that already exists.
    21              connStringBuilder.Database = "system";
    22              Simple(connStringBuilder.ConnectionString);
    23              ArrayTest(connStringBuilder.ConnectionString);
    24              TxnSample(connStringBuilder.ConnectionString);
    25              UnitTest(connStringBuilder.ConnectionString);
    26          }
    27  
    28          static void ProvideClientCertificatesCallback(X509CertificateCollection clientCerts)
    29          {
    30                clientCerts.Add(new X509Certificate2("/certs/client.root.pk12"));
    31          }
    32  
    33          static void Simple(string connString)
    34          {
    35              using (var conn = new NpgsqlConnection(connString))
    36              {
    37                  conn.ProvideClientCertificatesCallback += new ProvideClientCertificatesCallback(ProvideClientCertificatesCallback);
    38                  conn.Open();
    39  
    40                  // Create the database if it doesn't exist.
    41                  new NpgsqlCommand("CREATE DATABASE IF NOT EXISTS bank", conn).ExecuteNonQuery();
    42  
    43                  // Create the "accounts" table.
    44                  new NpgsqlCommand("CREATE TABLE IF NOT EXISTS bank.accounts (id INT PRIMARY KEY, balance INT)", conn).ExecuteNonQuery();
    45  
    46                  // Insert two rows into the "accounts" table.
    47                  using (var cmd = new NpgsqlCommand())
    48                  {
    49                      cmd.Connection = conn;
    50                      cmd.CommandText = "UPSERT INTO bank.accounts(id, balance) VALUES(@id1, @val1), (@id2, @val2)";
    51                      cmd.Parameters.AddWithValue("id1", 1);
    52                      cmd.Parameters.AddWithValue("val1", 1000);
    53                      cmd.Parameters.AddWithValue("id2", 2);
    54                      cmd.Parameters.AddWithValue("val2", 250);
    55                      cmd.ExecuteNonQuery();
    56                  }
    57  
    58                  // Print out the balances.
    59                  System.Console.WriteLine("Initial balances:");
    60                  using (var cmd = new NpgsqlCommand("SELECT id, balance FROM bank.accounts", conn))
    61                  using (var reader = cmd.ExecuteReader())
    62                      while (reader.Read())
    63                          Console.Write("\taccount {0}: {1}\n", reader.GetString(0), reader.GetString(1));
    64              }
    65          }
    66  
    67          static void TransferFunds(NpgsqlConnection conn, NpgsqlTransaction tran, int from, int to, int amount)
    68          {
    69              int balance = 0;
    70              using (var cmd = new NpgsqlCommand(String.Format("SELECT balance FROM bank.accounts WHERE id = {0}", from), conn, tran))
    71              using (var reader = cmd.ExecuteReader())
    72              {
    73                  if (reader.Read())
    74                  {
    75                      balance = reader.GetInt32(0);
    76                  }
    77                  else
    78                  {
    79                      throw new DataException(String.Format("Account id={0} not found", from));
    80                  }
    81              }
    82              if (balance < amount)
    83              {
    84                  throw new DataException(String.Format("Insufficient balance in account id={0}", from));
    85              }
    86              using (var cmd = new NpgsqlCommand(String.Format("UPDATE bank.accounts SET balance = balance - {0} where id = {1}", amount, from), conn, tran))
    87              {
    88                  cmd.ExecuteNonQuery();
    89              }
    90              using (var cmd = new NpgsqlCommand(String.Format("UPDATE bank.accounts SET balance = balance + {0} where id = {1}", amount, to), conn, tran))
    91              {
    92                  cmd.ExecuteNonQuery();
    93              }
    94          }
    95  
    96          static void TxnSample(string connString)
    97          {
    98              using (var conn = new NpgsqlConnection(connString))
    99              {
   100                  conn.ProvideClientCertificatesCallback += new ProvideClientCertificatesCallback(ProvideClientCertificatesCallback);
   101                  conn.Open();
   102                  try
   103                  {
   104                      using (var tran = conn.BeginTransaction())
   105                      {
   106                          tran.Save("cockroach_restart");
   107                          while (true)
   108                          {
   109                              try
   110                              {
   111                                  TransferFunds(conn, tran, 1, 2, 100);
   112                                  tran.Commit();
   113                                  break;
   114                              }
   115                              catch (NpgsqlException e)
   116                              {
   117                                  // Check if the error code indicates a SERIALIZATION_FAILURE.
   118                                  if (e.ErrorCode == 40001)
   119                                  {
   120                                      // Signal the database that we will attempt a retry.
   121                                      tran.Rollback("cockroach_restart");
   122                                  }
   123                                  else
   124                                  {
   125                                      throw;
   126                                  }
   127                              }
   128                          }
   129                      }
   130                  }
   131                  catch (DataException e)
   132                  {
   133                      Console.WriteLine(e.Message);
   134                  }
   135  
   136                  // Now printout the results.
   137                  Console.WriteLine("Final balances:");
   138                  using (var cmd = new NpgsqlCommand("SELECT id, balance FROM bank.accounts", conn))
   139                  using (var reader = cmd.ExecuteReader())
   140                      while (reader.Read())
   141                          Console.Write("\taccount {0}: {1}\n", reader.GetString(0), reader.GetString(1));
   142              }
   143          }
   144  
   145          static void UnitTest(string connString)
   146          {
   147              using (var conn = new NpgsqlConnection(connString))
   148              {
   149                  conn.ProvideClientCertificatesCallback += new ProvideClientCertificatesCallback(ProvideClientCertificatesCallback);
   150                  conn.Open();
   151  
   152                  using (var cmd = new NpgsqlCommand("DROP DATABASE IF EXISTS test CASCADE", conn))
   153                  {
   154                      cmd.Prepare();
   155                      var rows = cmd.ExecuteNonQuery();
   156                      if (rows != -1)
   157                      {
   158                          throw new DataException(String.Format("DROP DATABASE reports {0} rows changed, expecting -1", rows));
   159                      }
   160                  }
   161  
   162                  using (var cmd = new NpgsqlCommand("CREATE DATABASE test", conn))
   163                  {
   164                      cmd.Prepare();
   165                      var rows = cmd.ExecuteNonQuery();
   166                      if (rows != -1)
   167                      {
   168                          throw new DataException(String.Format("CREATE DATABASE reports {0} rows changed, expecting -1", rows));
   169                      }
   170                  }
   171  
   172                  using (var cmd = new NpgsqlCommand("CREATE TABLE test.f(x INT, ts TIMESTAMP)", conn))
   173                  {
   174                      cmd.Prepare();
   175                      var rows = cmd.ExecuteNonQuery();
   176                      if (rows != -1)
   177                      {
   178                          throw new DataException(String.Format("CREATE TABLE reports {0} rows changed, expecting -1", rows));
   179                      }
   180                  }
   181  
   182                  using (var cmd = new NpgsqlCommand("INSERT INTO test.f VALUES (42, timestamp '2015-05-07 18:20:00')", conn))
   183                  {
   184                      cmd.Prepare();
   185                      var rows = cmd.ExecuteNonQuery();
   186                      if (rows != 1)
   187                      {
   188                          throw new DataException(String.Format("INSERT reports {0} rows changed, expecting 1", rows));
   189                      }
   190                  }
   191  
   192                  using (var cmd = new NpgsqlCommand("SELECT * FROM test.f", conn))
   193                  {
   194                      cmd.Prepare();
   195                      using (var reader = cmd.ExecuteReader())
   196                      {
   197                          reader.Read();
   198                          var a = reader.GetInt32(0);
   199                          if (a != 42)
   200                          {
   201                              throw new DataException(String.Format("SELECT can't find inserted value: read {0}, expecting 42", a));
   202                          }
   203                          var ts = reader.GetTimeStamp(1);
   204                          var expectedTs = new NpgsqlTypes.NpgsqlDateTime(2015, 5, 7, 18, 20, 0, DateTimeKind.Unspecified);
   205                          if (ts != expectedTs)
   206                          {
   207                              throw new DataException(String.Format("SELECT unexpected value for ts: read {0}, expecting {1}", ts, expectedTs));
   208                          }
   209                      }
   210                  }
   211  
   212                  using (var cmd = new NpgsqlCommand("INSERT INTO test.f VALUES (@x, @ts)", conn))
   213                  {
   214                      cmd.Parameters.Add("x", NpgsqlTypes.NpgsqlDbType.Integer).Value = 1;
   215                      cmd.Parameters.Add("ts", NpgsqlTypes.NpgsqlDbType.Timestamp).Value = new NpgsqlTypes.NpgsqlDateTime(DateTime.Now);
   216                      cmd.Prepare();
   217                      var rows = cmd.ExecuteNonQuery();
   218                      if (rows != 1)
   219                      {
   220                          throw new DataException(String.Format("INSERT reports {0} rows changed, expecting 1", rows));
   221                      }
   222                  }
   223  
   224                  using (var cmd = new NpgsqlCommand("DROP TABLE test.f", conn))
   225                  {
   226                      cmd.Prepare();
   227                      var rows = cmd.ExecuteNonQuery();
   228                      if (rows != -1)
   229                      {
   230                          throw new DataException(String.Format("DROP TABLE reports {0} rows changed, expecting -1", rows));
   231                      }
   232                  }
   233  
   234                  using (var cmd = new NpgsqlCommand("SELECT 1, 2 > @comp, @int::int, @bool::string, @long::string, @real::string, @double::string, @short::string", conn))
   235                  {
   236                      cmd.Parameters.Add("comp", NpgsqlTypes.NpgsqlDbType.Integer).Value = 3;
   237                      cmd.Parameters.Add("int", NpgsqlTypes.NpgsqlDbType.Integer).Value = 3;
   238                      cmd.Parameters.Add("bool", NpgsqlTypes.NpgsqlDbType.Boolean).Value = true;
   239                      cmd.Parameters.Add("long", NpgsqlTypes.NpgsqlDbType.Bigint).Value = -4L;
   240                      cmd.Parameters.Add("real", NpgsqlTypes.NpgsqlDbType.Real).Value = 5.31f;
   241                      cmd.Parameters.Add("double", NpgsqlTypes.NpgsqlDbType.Double).Value = -6.21d;
   242                      cmd.Parameters.Add("short", NpgsqlTypes.NpgsqlDbType.Smallint).Value = (short)7;
   243                      cmd.Prepare();
   244                      using (var reader = cmd.ExecuteReader())
   245                      {
   246                          reader.Read();
   247                          var a = reader.GetInt32(0);
   248                          if (a != 1)
   249                          {
   250                              throw new DataException(String.Format("SELECT returns {0}, expected 1", a));
   251                          }
   252                          var b = reader.GetBoolean(1);
   253                          if (b)
   254                          {
   255                              throw new DataException(String.Format("SELECT returns {0}, expected false", b));
   256                          }
   257                          var c = reader.GetInt32(2);
   258                          if (c != 3)
   259                          {
   260                              throw new DataException(String.Format("SELECT returns {0}, expected 3", c));
   261                          }
   262                          var d = reader.GetString(3);
   263                          if (!d.Equals("true"))
   264                          {
   265                              throw new DataException(String.Format("SELECT returns {0}, expected true", d));
   266                          }
   267                          var e = reader.GetString(4);
   268                          if (!e.Equals("-4"))
   269                          {
   270                              throw new DataException(String.Format("SELECT returns {0}, expected -4", e));
   271                          }
   272                          var f = reader.GetString(5);
   273                          if (!f.StartsWith("5.3", StringComparison.Ordinal))
   274                          {
   275                              throw new DataException(String.Format("SELECT returns {0}, expected that it starts with 5.3", f));
   276                          }
   277                          var g = reader.GetString(6);
   278                          if (!g.StartsWith("-6.2", StringComparison.Ordinal))
   279                          {
   280                              throw new DataException(String.Format("SELECT returns {0}, expected that it starts with -6.2", g));
   281                          }
   282                          var h = reader.GetString(7);
   283                          if (!h.Equals("7"))
   284                          {
   285                              throw new DataException(String.Format("SELECT returns {0}, expected 7", h));
   286                          }
   287                      }
   288                  }
   289  
   290                  using (var cmd = new NpgsqlCommand("USE test", conn))
   291                  {
   292                      cmd.Prepare();
   293                      var rows = cmd.ExecuteNonQuery();
   294                      if (rows != -1)
   295                      {
   296                          throw new DataException(String.Format("USE reports {0} rows changed, expecting -1", rows));
   297                      }
   298                  }
   299  
   300                  using (var cmd = new NpgsqlCommand("CREATE TABLE accounts (id INT PRIMARY KEY, balance INT, cdate DATE)", conn))
   301                  {
   302                      cmd.Prepare();
   303                      var rows = cmd.ExecuteNonQuery();
   304                      if (rows != -1)
   305                      {
   306                          throw new DataException(String.Format("CREATE TABLE reports {0} rows changed, expecting -1", rows));
   307                      }
   308                  }
   309  
   310                  using (var cmd = new NpgsqlCommand("INSERT INTO accounts (id, balance, cdate) VALUES ( @id, @balance, @cdate )", conn))
   311                  {
   312                      cmd.Parameters.Add("id", NpgsqlTypes.NpgsqlDbType.Integer).Value = 1;
   313                      cmd.Parameters.Add("balance", NpgsqlTypes.NpgsqlDbType.Integer).Value = 1000;
   314                      cmd.Parameters.Add("cdate", NpgsqlTypes.NpgsqlDbType.Date).Value = new NpgsqlTypes.NpgsqlDate(DateTime.Now);
   315                      cmd.Prepare();
   316                      var rows = cmd.ExecuteNonQuery();
   317                      if (rows != 1)
   318                      {
   319                          throw new DataException(String.Format("INSERT reports {0} rows changed, expecting 1", rows));
   320                      }
   321                  }
   322  
   323                  using (var cmd = new NpgsqlCommand("CREATE TABLE empty()", conn))
   324                  {
   325                      cmd.Prepare();
   326                      var rows = cmd.ExecuteNonQuery();
   327                      if (rows != -1)
   328                      {
   329                          throw new DataException(String.Format("CREATE TABLE reports {0} rows changed, expecting -1", rows));
   330                      }
   331                  }
   332  
   333                  using (var cmd = new NpgsqlCommand("SELECT * from empty", conn))
   334                  {
   335                      cmd.Prepare();
   336                      using (var reader = cmd.ExecuteReader())
   337                      {
   338                          if (reader.FieldCount != 0)
   339                          {
   340                              throw new DataException(String.Format("SELECT returns {0} columns, expected 0", reader.FieldCount));
   341                          }
   342                      }
   343                  }
   344  
   345                  using (var cmd = new NpgsqlCommand("CREATE TABLE str (s STRING)", conn))
   346                  {
   347                      cmd.Prepare();
   348                      var rows = cmd.ExecuteNonQuery();
   349                      if (rows != -1)
   350                      {
   351                          throw new DataException(String.Format("CREATE TABLE reports {0} rows changed, expecting -1", rows));
   352                      }
   353                  }
   354  
   355                  using (var cmd = new NpgsqlCommand("UPDATE str SET s = @s", conn))
   356                  {
   357                      cmd.Parameters.Add("s", NpgsqlTypes.NpgsqlDbType.Varchar).Value = "hello";
   358                      cmd.Prepare();
   359                      var rows = cmd.ExecuteNonQuery();
   360                      if (rows != 0)
   361                      {
   362                          throw new DataException(String.Format("UPDATE reports {0} rows changed, expecting 0", rows));
   363                      }
   364                  }
   365  
   366                  using (var cmd = new NpgsqlCommand("SELECT @x", conn))
   367                  {
   368                      var uuid = new Guid();
   369                      cmd.Parameters.Add("x", NpgsqlTypes.NpgsqlDbType.Uuid).Value = uuid;
   370                      cmd.Prepare();
   371                      using (var reader = cmd.ExecuteReader())
   372                      {
   373                          reader.Read();
   374                          var actualUUID = reader.GetGuid(0);
   375                          if (actualUUID != uuid)
   376                          {
   377                              throw new DataException(String.Format("SELECT returns {0}, expected {1}", actualUUID, uuid));
   378                          }
   379                      }
   380                  }
   381  
   382                  // Check that imprecise placeholder typing works correctly. See issues
   383                  // #14245 and #14311 for more detail.
   384                  using (var cmd = new NpgsqlCommand("CREATE TABLE t (price decimal(5,2) NOT NULL)", conn))
   385                  {
   386                      cmd.Prepare();
   387                      var rows = cmd.ExecuteNonQuery();
   388                      if (rows != -1)
   389                      {
   390                          throw new DataException(String.Format("CREATE TABLE reports {0} rows changed, expecting -1", rows));
   391                      }
   392                  }
   393  
   394                  using (var cmd = new NpgsqlCommand("INSERT INTO test.t VALUES(@f)", conn))
   395                  {
   396                      cmd.Parameters.Add("f", NpgsqlTypes.NpgsqlDbType.Real).Value = 3.3f;
   397                      cmd.Prepare();
   398                      var rows = cmd.ExecuteNonQuery();
   399                      if (rows != 1)
   400                      {
   401                          throw new DataException(String.Format("INSERT reports {0} rows changed, expecting 1", rows));
   402                      }
   403                  }
   404              }
   405          }
   406  
   407          static void ArrayTest(string connString)
   408          {
   409              using (var conn = new NpgsqlConnection(connString))
   410              {
   411                  conn.ProvideClientCertificatesCallback += new ProvideClientCertificatesCallback(ProvideClientCertificatesCallback);
   412                  conn.Open();
   413  
   414                  new NpgsqlCommand("CREATE DATABASE IF NOT EXISTS test", conn).ExecuteNonQuery();
   415                  new NpgsqlCommand("CREATE TABLE IF NOT EXISTS test.arrays (a INT[])", conn).ExecuteNonQuery();
   416                  using (var cmd = new NpgsqlCommand())
   417                  {
   418                      cmd.Connection = conn;
   419                      cmd.CommandText = "INSERT INTO test.arrays(a) VALUES(@val)";
   420                      cmd.Parameters.AddWithValue("val", new int[] {1, 2, 3});
   421                      cmd.ExecuteNonQuery();
   422                  }
   423                  using (var cmd = new NpgsqlCommand("SELECT a FROM test.arrays", conn))
   424                  using (var reader = cmd.ExecuteReader())
   425                      while (reader.Read()) {
   426                        var ary = reader["a"] as long[];
   427                        if (!ary.SequenceEqual(new long[] {1, 2, 3})) {
   428                          throw new DataException(String.Format("Expected result to be [1, 2, 3], was {0}", String.Join(", ", ary)));
   429                        }
   430                      }
   431              }
   432          }
   433      }
   434  }