public void ParameterizedPrepareManyFields() { using (var command = Conn.CreateCommand()) { StringWriter sql = new StringWriter(); sql.WriteLine("SELECT"); sql.WriteLine(":p01, :p02, :p03, :p04, :p05, :p06, :p07, :p08, :p09, :p10,"); sql.WriteLine(":p11, :p12, :p13, :p14, :p15, :p16, :p17, :p18, :p19, :p20"); command.CommandText = sql.ToString(); for (int i = 0; i < 20; i++) { command.Parameters.AddWithValue(string.Format("p{0:00}", i + 1), NpgsqlDbType.Text, string.Format("String parameter value {0}", i + 1)); } using (var metrics = TestMetrics.Start(TestRunTime, true)) { while (!metrics.TimesUp) { command.Prepare(); metrics.IncrementIterations(); } } } }
private void PerformanceWithNParameters(int n, bool differentCase) { var command = new NpgsqlCommand(); var collection = command.Parameters; for (int i = 0; i < n; i++) { collection.AddWithValue("value" + i, i); } using (var metrics = TestMetrics.Start(TestRunTime, true)) { while (!metrics.TimesUp) { for (int i = 0; i < n && !metrics.TimesUp; i++) { NpgsqlParameter param; if (!collection.TryGetValue((differentCase ? "VALUE" : "value") + i, out param)) { throw new Exception(); } metrics.IncrementIterations(); } } } }
public void ParameterizedSelectByteaRoundTrip() { using (var command = Conn.CreateCommand()) { command.CommandType = CommandType.Text; command.CommandText = "SELECT :data"; byte[] data = new byte[100000]; for (int i = 0; i < data.Length; i++) { data[i] = (byte)(i % 255); } NpgsqlParameter dataParameter = command.CreateParameter(); dataParameter.Direction = ParameterDirection.Input; dataParameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Bytea; dataParameter.ParameterName = "data"; command.Parameters.Add(dataParameter); dataParameter.Value = data; command.Prepare(); using (var metrics = TestMetrics.Start(TestRunTime, true)) { while (!metrics.TimesUp) { var data2 = (byte[])command.ExecuteScalar(); metrics.IncrementIterations(); } } } }
public void ParameterizedSelectMoneyArrayRoundTrip() { using (var command = Conn.CreateCommand()) { command.CommandType = CommandType.Text; command.CommandText = "SELECT :data"; decimal[] data = new decimal[1000]; for (int i = 0; i < 1000; i++) { data[i] = i; } NpgsqlParameter dataParameter = command.CreateParameter(); dataParameter.Direction = ParameterDirection.Input; dataParameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Money | NpgsqlTypes.NpgsqlDbType.Array; dataParameter.ParameterName = "data"; command.Parameters.Add(dataParameter); dataParameter.Value = data; command.Prepare(); using (var metrics = TestMetrics.Start(TestRunTime, true)) { while (!metrics.TimesUp) { command.ExecuteScalar(); metrics.IncrementIterations(); } } } }
public void ParameterizedSelectBigIntRoundTrip() { using (var command = Conn.CreateCommand()) { command.CommandType = CommandType.Text; command.CommandText = "SELECT :data1, :data2, :data3, :data4, :data5, :data6, :data7, :data8, :data9, :data10"; for (int i = 0; i < 10; i++) { NpgsqlParameter dataParameter = command.CreateParameter(); dataParameter.Direction = ParameterDirection.Input; dataParameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Bigint; dataParameter.ParameterName = string.Format("data{0}", i + 1); command.Parameters.Add(dataParameter); dataParameter.Value = 0xFFFFFFFFFFFFFFF; } command.Prepare(); using (var metrics = TestMetrics.Start(TestRunTime, true)) { while (!metrics.TimesUp) { using (IDataReader r = command.ExecuteReader()) { r.Read(); Int64 r10 = (Int64)r[9]; } metrics.IncrementIterations(); } } } }
public void ParameterizedInsert() { using (var command = Conn.CreateCommand()) { command.CommandType = CommandType.Text; command.CommandText = "INSERT INTO data (field_text) values (:data)"; IDbDataParameter dataParameter = command.CreateParameter(); dataParameter.Direction = ParameterDirection.Input; dataParameter.DbType = DbType.String; dataParameter.ParameterName = "data"; command.Parameters.Add(dataParameter); command.Prepare(); using (var metrics = TestMetrics.Start(TestRunTime, true)) { while (!metrics.TimesUp) { dataParameter.Value = "yo"; command.ExecuteScalar(); metrics.IncrementIterations(); } } } }
public void ParameterizedSelectDecimalRoundTrip() { using (var command = Conn.CreateCommand()) { command.CommandType = CommandType.Text; command.CommandText = "SELECT :data"; NpgsqlParameter dataParameter = command.CreateParameter(); dataParameter.Direction = ParameterDirection.Input; dataParameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Numeric; dataParameter.ParameterName = "data"; command.Parameters.Add(dataParameter); dataParameter.Value = 12345678.12345678; using (var metrics = TestMetrics.Start(TestRunTime, true)) { while (!metrics.TimesUp) { command.Prepare(); var data2 = (decimal)command.ExecuteScalar(); metrics.IncrementIterations(); } } } }
public void ExecuteScalar() { using (var metrics = TestMetrics.Start(TestRunTime, true)) { while (!metrics.TimesUp) { ExecuteScalar("SELECT 1 + 1"); metrics.IncrementIterations(); } } }
public void ExecuteUpdateNonQuery() { using (var metrics = TestMetrics.Start(TestRunTime, true)) { while (!metrics.TimesUp) { ExecuteNonQuery("set lock_timeout = 1000"); metrics.IncrementIterations(); } } }
public async void ExecuteScalarAsync() { using (var conn = OpenConnection()) using (var metrics = TestMetrics.Start(TestRunTime, true)) { while (!metrics.TimesUp) { await conn.ExecuteScalarAsync("SELECT 1 + 1"); metrics.IncrementIterations(); } } }
public async void ExecuteUpdateNonQueryAsync() { using (var conn = OpenConnection()) using (var metrics = TestMetrics.Start(TestRunTime, true)) { while (!metrics.TimesUp) { await conn.ExecuteNonQueryAsync("set lock_timeout = 1000"); metrics.IncrementIterations(); } } }
public void ParameterizedSelectByteaArrayRoundTrip() { using (var conn = OpenConnection()) using (var command = conn.CreateCommand()) { command.CommandType = CommandType.Text; command.CommandText = "SELECT :data"; byte[] bytes = new byte[50000]; for (int i = 0; i < bytes.Length; i++) { bytes[i] = (byte)(i % 255); } byte[][] data = new byte[][] { bytes, bytes }; NpgsqlParameter dataParameter = command.CreateParameter(); dataParameter.Direction = ParameterDirection.Input; dataParameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Bytea | NpgsqlTypes.NpgsqlDbType.Array; dataParameter.ParameterName = "data"; command.Parameters.Add(dataParameter); dataParameter.Value = data; command.Prepare(); using (var metrics = TestMetrics.Start(TestRunTime, true)) { while (!metrics.TimesUp) { try { command.ExecuteScalar(); } catch (PostgresException e) { if (e.Message.Length > 500) { Console.WriteLine(string.Format("Error: {0}", e.Message.Substring(0, 100))); throw new Exception(e.Message.Substring(0, 500)); } else { throw; } } metrics.IncrementIterations(); } } } }
public void ParameterizedSelectArrayRoundTrip() { using (var command = Conn.CreateCommand()) { command.CommandType = CommandType.Text; command.CommandText = "SELECT :data"; string[] data = new string[1000]; for (int i = 0; i < 1000; i++) { data[i] = string.Format("A string with the number {0}, a ', a \", and a \\.", i); } NpgsqlParameter dataParameter = command.CreateParameter(); dataParameter.Direction = ParameterDirection.Input; dataParameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Text | NpgsqlTypes.NpgsqlDbType.Array; dataParameter.ParameterName = "data"; command.Parameters.Add(dataParameter); dataParameter.Value = data; command.Prepare(); using (var metrics = TestMetrics.Start(TestRunTime, true)) { while (!metrics.TimesUp) { try { command.ExecuteScalar(); } catch (NpgsqlException e) { if (e.Message.Length > 500) { Console.WriteLine(string.Format("Error: {0}", e.Message.Substring(0, 100))); throw new Exception(e.Message.Substring(0, 500)); } else { throw; } } metrics.IncrementIterations(); } } } }
public void ConnectWithPool(bool withPool) { NpgsqlConnectionStringBuilder csb = new NpgsqlConnectionStringBuilder(Conn.ConnectionString); csb.Pooling = withPool; String conStr = csb.ConnectionString; using (var metrics = TestMetrics.Start(TestRunTime, true)) { while (!metrics.TimesUp) { var con = new NpgsqlConnection(conStr); con.Open(); con.Dispose(); metrics.IncrementIterations(); } } }
public void ExecuteReader(int rows) { for (var i = 0; i < rows; i++) { ExecuteNonQuery("INSERT INTO DATA (field_int4) VALUES (10)"); } using (var metrics = TestMetrics.Start(TestRunTime, true)) { while (!metrics.TimesUp) { using (var cmd = new NpgsqlCommand("SELECT field_int4 FROM data", Conn)) using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { } } metrics.IncrementIterations(); } } }
public async void ExecuteReaderAsync(int rows) { using (var conn = OpenConnection()) { for (var i = 0; i < rows; i++) { conn.ExecuteNonQuery("INSERT INTO DATA (field_int4) VALUES (10)"); } using (var metrics = TestMetrics.Start(TestRunTime, true)) { while (!metrics.TimesUp) { using (var cmd = new NpgsqlCommand("SELECT field_int4 FROM data", conn)) using (var reader = await cmd.ExecuteReaderAsync()) while (await reader.ReadAsync()) { } metrics.IncrementIterations(); } } } }