Beispiel #1
0
 public async Task Scalar()
 {
     using (var conn = OpenConnection())
     using (var cmd = new NpgsqlCommand("SELECT 1", conn)) {
         Assert.That(await cmd.ExecuteScalarAsync(), Is.EqualTo(1));
     }
 }
 /// <summary>
 /// Execute a function
 /// </summary>
 internal async Task <T> ExecuteFunction <T>(string function, bool async, params object[] arguments)
 {
     using (var command = new NpgsqlCommand(function, _connection))
     {
         command.CommandType = CommandType.StoredProcedure;
         command.CommandText = function;
         foreach (var argument in arguments)
         {
             command.Parameters.Add(new NpgsqlParameter {
                 Value = argument
             });
         }
         return((T)(async ? await command.ExecuteScalarAsync() : command.ExecuteScalar()));
     }
 }
Beispiel #3
0
        /// <summary>
        /// Execute a function
        /// </summary>
        internal async Task <T> ExecuteFunction <T>(string function, bool async, CancellationToken cancellationToken, params object[] arguments)
        {
            using var command = new NpgsqlCommand(function, Connection)
                  {
                      CommandType = CommandType.StoredProcedure,
                      CommandText = function
                  };

            foreach (var argument in arguments)
            {
                command.Parameters.Add(new NpgsqlParameter {
                    Value = argument
                });
            }

            return((T)(async ? await command.ExecuteScalarAsync(cancellationToken) : command.ExecuteScalar()) !);
        }
Beispiel #4
0
        private static async Task ExecuteDBAction(Npgsql.NpgsqlConnection connection, int threadId, int value)
        {
            using (DbCommand command = new Npgsql.NpgsqlCommand(selectquery, connection))
            {
                command.Parameters.Add(new Npgsql.NpgsqlParameter("id", threadId));
                command.Parameters.Add(new Npgsql.NpgsqlParameter("value", value));
                var result = await command.ExecuteScalarAsync();
            }

            Thread.Sleep(new Random().Next(50, 250));

            using (DbCommand command = new Npgsql.NpgsqlCommand(updatequery, connection))
            {
                command.Parameters.Add(new Npgsql.NpgsqlParameter("id", threadId));
                command.Parameters.Add(new Npgsql.NpgsqlParameter("value", value));
                var result = await command.ExecuteScalarAsync();
            }
        }
Beispiel #5
0
        /// <summary>
        /// 在【主库】执行
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        async public Task <object> ExecuteScalarAsync(CommandType cmdType, string cmdText, params NpgsqlParameter[] cmdParms)
        {
            DateTime dt     = DateTime.Now;
            string   logtxt = "";
            Object <NpgsqlConnection> conn = null;
            NpgsqlCommand             cmd  = PrepareCommandAsync(cmdType, cmdText, cmdParms, ref logtxt);
            DateTime  logtxt_dt            = DateTime.Now;
            object    val = null;
            Exception ex  = null;

            try {
                if (cmd.Connection == null)
                {
                    cmd.Connection = (conn = await this.MasterPool.GetAsync()).Value;
                }
                val = await cmd.ExecuteScalarAsync();
            } catch (Exception ex2) {
                ex = ex2;
            }

            if (conn != null)
            {
                if (IsTracePerformance)
                {
                    logtxt_dt = DateTime.Now;
                }
                this.MasterPool.Return(conn, ex);
                if (IsTracePerformance)
                {
                    logtxt += $"ReleaseConnection: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms";
                }
            }
            LoggerException(this.MasterPool, cmd, ex, dt, logtxt);
            cmd.Parameters.Clear();
            return(val);
        }
Beispiel #6
0
 protected async Task<object> ExecuteScalarAsync(string sql, NpgsqlConnection conn = null)
 {
     if (conn == null)
         conn = Conn;
     using (var cmd = new NpgsqlCommand(sql, conn))
         return await cmd.ExecuteScalarAsync();
 }
Beispiel #7
0
 public async void Scalar()
 {
     using (var cmd = new NpgsqlCommand("SELECT 1", Conn)) {
         Assert.That(await cmd.ExecuteScalarAsync(), Is.EqualTo(1));
     }
 }
		public async Task<int> AddDriverAsync(Driver theDriver)
		{
			try
			{
				await Connection.OpenAsync().ConfigureAwait(false);

				var aCommand = new NpgsqlCommand(
					"Insert into driver (firstname, lastname, address, city, state, postalcode, country, licensenumber, licensestate, customerid) VALUES (:value1, :value2, :value3, :value4, :value5, :value6, :value7, :value8, :value9, :value10) RETURNING id", Connection);
				aCommand.Parameters.AddWithValue("value1", theDriver.FirstName);
				aCommand.Parameters.AddWithValue("value2", theDriver.LastName);
				aCommand.Parameters.AddWithValue("value3", theDriver.Address);
				aCommand.Parameters.AddWithValue("value4", theDriver.City);
				aCommand.Parameters.AddWithValue("value5", theDriver.State);
				aCommand.Parameters.AddWithValue("value6", theDriver.PostalCode);
				aCommand.Parameters.AddWithValue("value7", theDriver.Country);
				aCommand.Parameters.AddWithValue("value8", theDriver.LicenseNumber);
				aCommand.Parameters.AddWithValue("value9", theDriver.LicenseState);
				aCommand.Parameters.AddWithValue("value10", theDriver.CustomerId);

				// returns the id from the SELECT, RETURNING sql statement above
				return Convert.ToInt32(await aCommand.ExecuteScalarAsync().ConfigureAwait(false));
			}
			catch (NpgsqlException)
			{
				return 0;
			}
			catch (InvalidOperationException)
			{
				return 0;
			}
			catch (SqlException)
			{
				return 0;
			}
			catch (ConfigurationErrorsException)
			{
				return 0;
			}
			finally
			{
				if (Connection.State == ConnectionState.Open)
					Connection.Close();
			}
		}
		public async Task<int> AddCustomerAsync(Customer theCustomer)
		{
			try
			{
				await Connection.OpenAsync().ConfigureAwait(false);

				var aCommand = new NpgsqlCommand(
					"Insert into customer (name, allowsadditionaldrivers, allowsadditions, hasmaxrentaldays, maxrentaldays) VALUES (:value1, :value2, :value3, :value4, :value5) RETURNING id", Connection);
				aCommand.Parameters.AddWithValue("value1", theCustomer.Name);
				aCommand.Parameters.AddWithValue("value2", theCustomer.AllowsAdditionalDrivers);
				aCommand.Parameters.AddWithValue("value3", theCustomer.AllowsAdditions);
				aCommand.Parameters.AddWithValue("value4", theCustomer.HasMaxRentalDays);
				aCommand.Parameters.AddWithValue("value5", theCustomer.MaxRentalDays);

				// returns the id from the SELECT, RETURNING sql statement above
				return Convert.ToInt32(await aCommand.ExecuteScalarAsync().ConfigureAwait(false));
			}
			catch (NpgsqlException)
			{
				return 0;
			}
			catch (InvalidOperationException)
			{
				return 0;
			}
			catch (SqlException)
			{
				return 0;
			}
			catch (ConfigurationErrorsException)
			{
				return 0;
			}
			finally
			{
				if (Connection.State == ConnectionState.Open)
					Connection.Close();
			}
		}
Beispiel #10
0
 /// <summary>
 /// Tests the connection asynchronously.
 /// </summary>
 /// <returns></returns>
 public override async Task TestConnectionAsync()
 {
     using (var con = await CreateConnectionAsync())
     using (var cmd = new NpgsqlCommand("SELECT 1", con))
         await cmd.ExecuteScalarAsync();
 }
		public async Task<int> AddLocationAsync(Location theLocation)
		{
			try
			{
				await Connection.OpenAsync().ConfigureAwait(false);

				var aCommand = new NpgsqlCommand(
					"Insert into location (customerid, name, address, city, state, postalcode, country, latitude, longitude) VALUES (:value1, :value2, :value3, :value4, :value5, :value6, :value7, :value8, :value9) RETURNING id", Connection);
				aCommand.Parameters.AddWithValue("value1", theLocation.CustomerId);
				aCommand.Parameters.AddWithValue("value2", theLocation.Name);
				aCommand.Parameters.AddWithValue("value3", theLocation.Address);
				aCommand.Parameters.AddWithValue("value4", theLocation.City);
				aCommand.Parameters.AddWithValue("value5", theLocation.State);
				aCommand.Parameters.AddWithValue("value6", theLocation.PostalCode);
				aCommand.Parameters.AddWithValue("value7", theLocation.Country);
				aCommand.Parameters.AddWithValue("value8", theLocation.Latitude);
				aCommand.Parameters.AddWithValue("value9", theLocation.Longitude);

				// returns the id from the SELECT, RETURNING sql statement above
				return Convert.ToInt32(await aCommand.ExecuteScalarAsync().ConfigureAwait(false));
			}
			catch (NpgsqlException)
			{
				return 0;
			}
			catch (InvalidOperationException)
			{
				return 0;
			}
			catch (SqlException)
			{
				return 0;
			}
			catch (ConfigurationErrorsException)
			{
				return 0;
			}
			finally
			{
				if (Connection.State == ConnectionState.Open)
					Connection.Close();
			}
		}
		public async Task<int> AddUserAsync(User theUser)
		{
			try
			{
				await Connection.OpenAsync().ConfigureAwait(false);

				var aCommand = new NpgsqlCommand(
					"Insert into appuser (firstname, lastname, email, customerid, isemployee) VALUES (:value1, :value2, :value3, :value4, :value5) RETURNING id", Connection);
				aCommand.Parameters.AddWithValue("value1", theUser.FirstName);
				aCommand.Parameters.AddWithValue("value2", theUser.LastName);
				aCommand.Parameters.AddWithValue("value3", theUser.Email);
				aCommand.Parameters.AddWithValue("value4", theUser.CustomerId);
				aCommand.Parameters.AddWithValue("value5", theUser.IsEmployee);

				// returns the id from the SELECT, RETURNING sql statement above
				return Convert.ToInt32(await aCommand.ExecuteScalarAsync().ConfigureAwait(false));
			}
			catch (NpgsqlException)
			{
				return 0;
			}
			catch (InvalidOperationException)
			{
				return 0;
			}
			catch (SqlException)
			{
				return 0;
			}
			catch (ConfigurationErrorsException)
			{
				return 0;
			}
			finally
			{
				if (Connection.State == ConnectionState.Open)
					Connection.Close();
			}
		}
		public async Task<int> AddAutomobileAsync(Automobile theAutomobile)
		{
			try
			{
				await Connection.OpenAsync().ConfigureAwait(false);

				var aCommand = new NpgsqlCommand(
					"Insert into automobile (vin, vehiclenumber, name, class, style, color, manufacturer, model, code, locationid) VALUES (:value1, :value2, :value3, :value4, :value5, :value6, :value7, :value8, :value9, :value10) RETURNING id", Connection);
				aCommand.Parameters.AddWithValue("value1", theAutomobile.VIN);
				aCommand.Parameters.AddWithValue("value2", theAutomobile.VehicleNumber);
				aCommand.Parameters.AddWithValue("value3", theAutomobile.Name);
				aCommand.Parameters.AddWithValue("value4", theAutomobile.Class);
				aCommand.Parameters.AddWithValue("value5", theAutomobile.Style);
				aCommand.Parameters.AddWithValue("value6", theAutomobile.Color);
				aCommand.Parameters.AddWithValue("value7", theAutomobile.Manufacturer);
				aCommand.Parameters.AddWithValue("value8", theAutomobile.Model);
				aCommand.Parameters.AddWithValue("value9", theAutomobile.Code);
				aCommand.Parameters.AddWithValue("value10", theAutomobile.LocationId);

				// returns the id from the SELECT, RETURNING sql statement above
				return Convert.ToInt32(await aCommand.ExecuteScalarAsync().ConfigureAwait(false));
			}
			catch (NpgsqlException)
			{
				return 0;
			}
			catch (InvalidOperationException)
			{
				return 0;
			}
			catch (SqlException)
			{
				return 0;
			}
			catch (ConfigurationErrorsException)
			{
				return 0;
			}
			finally
			{
				if (Connection.State == ConnectionState.Open)
					Connection.Close();
			}
		}